diff options
Diffstat (limited to 'mysql-test/suite/storage_engine/type_float_indexes.test')
| -rw-r--r-- | mysql-test/suite/storage_engine/type_float_indexes.test | 203 |
1 files changed, 203 insertions, 0 deletions
diff --git a/mysql-test/suite/storage_engine/type_float_indexes.test b/mysql-test/suite/storage_engine/type_float_indexes.test new file mode 100644 index 00000000000..0f1afc600a1 --- /dev/null +++ b/mysql-test/suite/storage_engine/type_float_indexes.test @@ -0,0 +1,203 @@ +# +# 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 + |