#
# Float type columns with indexes
#

--source have_engine.inc
--source have_default_index.inc

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

# Default index as set in define_engine.inc

let $create_definition =
  f FLOAT $col_indexed_opts,
  r REAL(20,3) $col_opts,
  d DOUBLE $col_opts,
  dp DOUBLE PRECISION (64,10) $col_opts,
  $default_index f (f)
;
--source create_table.inc
if ($mysql_errname)
{
  --let $my_last_stmt = $create_statement
  --let $functionality = Float point types or PK
  --source unexpected_result.inc
}
if (!$mysql_errname)
{
  --replace_column 3 # 6 # 7 # 10 # 11 #
  SHOW INDEX IN t1;

  INSERT INTO t1 (f,r,d,dp) VALUES
  (1.2345,1422.22,1.2345,1234567.89),
  (0,0,0,0),
  (-1,-1,-1,-1),
  (17.5843,4953453454.44,29229114.0,1111111.23),
  (4644,1422.22,466664.999,0.5);

  SELECT f FROM t1 ORDER BY f;

  DROP TABLE t1;
}


# PK, UNIQUE INDEX, INDEX

let $create_definition =
  f FLOAT $col_indexed_opts PRIMARY KEY,
  r REAL(20,3) $col_opts,
  d DOUBLE $col_opts,
  dp DOUBLE PRECISION (64,10) $col_opts
;
--source create_table.inc
if ($mysql_errname)
{
  --let $my_last_stmt = $create_statement
  --let $functionality = Float point types or PK
  --source unexpected_result.inc
}
if (!$mysql_errname)
{
  --replace_column 6 # 7 # 10 # 11 #
  SHOW INDEX IN t1;

  INSERT INTO t1 (f,r,d,dp) VALUES
  (1.2345,1422.22,1.2345,1234567.89),
  (0,0,0,0),
  (-1,-1,-1,-1),
  (17.5843,4953453454.44,29229114.0,1111111.23),
  (4644,1422.22,466664.999,0.5);

  --replace_column 1 # 2 # 3 # 4 # 5 # 7 # 8 # 9 # 10 #
  EXPLAIN SELECT f FROM t1 ORDER BY f;
  SELECT f FROM t1 ORDER BY f;

  --replace_column 1 # 2 # 3 # 4 # 5 # 7 # 8 # 9 # 10 #
  EXPLAIN SELECT f FROM t1 IGNORE INDEX (PRIMARY) ORDER BY f;
  SELECT f FROM t1 IGNORE INDEX (PRIMARY) ORDER BY f;


  --let $alter_definition = DROP PRIMARY KEY
  --source alter_table.inc
  if ($mysql_errname)
  {
    --let $my_last_stmt = $alter_statement
    --source unexpected_result.inc
  }
  if (!$mysql_errname)
  {
    --let $alter_definition = ADD UNIQUE KEY(d)
    --source alter_table.inc

    --replace_column 1 # 2 # 3 # 4 # 5 # 7 # 8 # 9 # 10 #
    EXPLAIN SELECT d FROM t1 WHERE r > 0 and d > 0 ORDER BY d;
    SELECT d FROM t1 WHERE r > 0 and d > 0 ORDER BY d;
  }
  DROP TABLE t1;
}

let $create_definition =
  f FLOAT $col_opts,
  r REAL(20,3) $col_indexed_opts,
  d DOUBLE $col_opts,
  dp DOUBLE PRECISION (64,10) $col_indexed_opts,
  UNIQUE KEY r_dp (r,dp)
;
--source create_table.inc
if ($mysql_errname)
{
  --let $my_last_stmt = $create_statement
  --let $functionality = Float point types or unique keys or multi-part keys
  --source unexpected_result.inc
}
if (!$mysql_errname)
{
  --replace_column 6 # 7 # 10 # 11 #
  SHOW INDEX IN t1;
  
  INSERT INTO t1 (f,r,d,dp) VALUES
  (1.2345,1422.22,1.2345,1234567.89),
  (0,0,0,0),
  (-1,-1,-1,-1),
  (17.5843,4953453454.44,29229114.0,1111111.23),
  (4644,1422.22,466664.999,0.5);

  --replace_column 1 # 2 # 3 # 4 # 5 # 7 # 8 # 9 # 10 #
  EXPLAIN SELECT r, dp FROM t1 WHERE r > 0 or dp > 0;
  --sorted_result
  SELECT r, dp FROM t1 WHERE r > 0 or dp > 0;

  DROP TABLE t1;
}

let $create_definition =
  f FLOAT $col_opts,
  r REAL(20,3) $col_opts,
  d DOUBLE $col_indexed_opts,
  dp DOUBLE PRECISION (64,10) $col_opts,
  UNIQUE KEY(d)
;
--source create_table.inc
if ($mysql_errname)
{
  --let $my_last_stmt = $create_statement
  --let $functionality = Float point types or unique keys
  --source unexpected_result.inc
}
if (!$mysql_errname)
{
  --replace_column 6 # 7 # 10 # 11 #
  SHOW INDEX IN t1;
  
  INSERT INTO t1 (f,r,d,dp) VALUES
  (1.2345,1422.22,1.2345,1234567.89),
  (0,0,0,0),
  (-1,-1,-1,-1),
  (17.5843,4953453454.44,29229114.0,1111111.23),
  (4644,1422.22,466664.999,0.5);

  --replace_column 1 # 2 # 3 # 4 # 5 # 7 # 8 # 9 # 10 #
  EXPLAIN SELECT DISTINCT d FROM t1 ORDER BY d;
  SELECT DISTINCT d FROM t1 ORDER BY d;

  DROP TABLE t1;
}


let $create_definition =
  f FLOAT $col_opts,
  r REAL(20,3) $col_opts,
  d DOUBLE $col_indexed_opts,
  dp DOUBLE PRECISION (64,10) $col_opts,
  KEY(d)
;
--source create_table.inc
if ($mysql_errname)
{
  --let $my_last_stmt = $create_statement
  --let $functionality = Float point types or non-unique keys
  --source unexpected_result.inc
}
if (!$mysql_errname)
{
  --replace_column 6 # 7 # 10 # 11 #
  SHOW INDEX IN t1;
  
  INSERT INTO t1 (f,r,d,dp) VALUES
  (1.2345,1422.22,1.2345,1234567.89),
  (0,0,0,0),
  (-1,-1,-1,-1),
  (17.5843,4953453454.44,29229114.0,1111111.23),
  (4644,1422.22,466664.999,0.5);

  --replace_column 1 # 2 # 3 # 4 # 5 # 7 # 8 # 9 # 10 #
  EXPLAIN SELECT DISTINCT d FROM t1 ORDER BY d;
  SELECT DISTINCT d FROM t1 ORDER BY d;

  DROP TABLE t1;
}

--source cleanup_engine.inc