From 88961a28e2694db18acb6d355089e52ac2b9a769 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Sat, 4 May 2019 23:32:45 +0200 Subject: MDEV-17710 "unknown error" with FLUSH LOGS if log directory is not writeable --- mysql-test/main/flush_logs_not_windows.result | 3 +++ mysql-test/main/flush_logs_not_windows.test | 13 +++++++++++++ 2 files changed, 16 insertions(+) create mode 100644 mysql-test/main/flush_logs_not_windows.result create mode 100644 mysql-test/main/flush_logs_not_windows.test (limited to 'mysql-test/main') diff --git a/mysql-test/main/flush_logs_not_windows.result b/mysql-test/main/flush_logs_not_windows.result new file mode 100644 index 00000000000..920fddb8f97 --- /dev/null +++ b/mysql-test/main/flush_logs_not_windows.result @@ -0,0 +1,3 @@ +flush logs; +ERROR HY000: Can't create file 'MYSQLTEST_VARDIR/log/mysqld.1.err' (errno: 13 "Permission denied") +flush logs; diff --git a/mysql-test/main/flush_logs_not_windows.test b/mysql-test/main/flush_logs_not_windows.test new file mode 100644 index 00000000000..af50e4bb9e8 --- /dev/null +++ b/mysql-test/main/flush_logs_not_windows.test @@ -0,0 +1,13 @@ +source include/not_windows.inc; +source include/not_embedded.inc; +# +# MDEV-17710 "unknown error" with FLUSH LOGS if log directory is not writeable +# +move_file $MYSQLTEST_VARDIR/log/mysqld.1.err $MYSQLTEST_VARDIR/log/mysqld.1.err-bak; +system chmod u-w $MYSQLTEST_VARDIR/log; +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +error ER_CANT_CREATE_FILE; +flush logs; +system chmod u+w $MYSQLTEST_VARDIR/log; +move_file $MYSQLTEST_VARDIR/log/mysqld.1.err-bak $MYSQLTEST_VARDIR/log/mysqld.1.err; +flush logs; -- cgit v1.2.1 From 09aa5d3f6988e92f869adfd13a925bfead6f040d Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Wed, 8 May 2019 00:08:09 -0700 Subject: MDEV-17894 Assertion `(thd->lex)->current_select' failed in MYSQLparse(), query with VALUES() A table value constructor can be used in all contexts where a select can be used. In particular an ORDER BY clause or a LIMIT clause or both of them can be attached to a table value constructor to produce a new query. Unfortunately execution of such queries was not supported. This patch fixes the problem. --- mysql-test/main/table_value_constr.result | 397 ++++++++++++++++++++++++++++++ mysql-test/main/table_value_constr.test | 193 +++++++++++++++ 2 files changed, 590 insertions(+) (limited to 'mysql-test/main') diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 1d485af4a4d..318d0a76663 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -2189,3 +2189,400 @@ EXECUTE stmt; 1 + 1 2 abc 2 2 abc DEALLOCATE PREPARE stmt; +# +# MDEV-17894: tvc with ORDER BY ... LIMIT +# +values (5), (7), (1), (3), (4) limit 2; +5 +5 +7 +explain extended values (5), (7), (1), (3), (4) limit 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 values (5),(7),(1),(3),(4) limit 2 +values (5), (7), (1), (3), (4) limit 2 offset 1; +5 +7 +1 +explain extended values (5), (7), (1), (3), (4) limit 2 offset 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 values (5),(7),(1),(3),(4) limit 1,2 +values (5), (7), (1), (3), (4) order by 1 limit 2; +5 +1 +3 +explain extended values (5), (7), (1), (3), (4) order by 1 limit 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNIT RESULT ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 values (5),(7),(1),(3),(4) order by 1 limit 2 +values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1; +5 +3 +4 +explain extended values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNIT RESULT ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 values (5),(7),(1),(3),(4) order by 1 limit 1,2 +values (5), (7), (1), (3), (4) order by 1; +5 +1 +3 +4 +5 +7 +explain extended values (5), (7), (1), (3), (4) order by 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNIT RESULT ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 values (5),(7),(1),(3),(4) order by 1 +values (5,90), (7,20), (1,70), (3,50), (4,10) order by 2; +5 90 +4 10 +7 20 +3 50 +1 70 +5 90 +explain extended values (5,90), (7,20), (1,70), (3,50), (4,10) order by 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNIT RESULT ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 values (5,90),(7,20),(1,70),(3,50),(4,10) order by 2 +select 2 union (values (5), (7), (1), (3), (4) limit 2); +2 +2 +5 +7 +explain extended select 2 union (values (5), (7), (1), (3), (4) limit 2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 2 AS `2` union (values (5),(7),(1),(3),(4) limit 2) +select 2 union (values (5), (7), (1), (3), (4) limit 2 offset 1); +2 +2 +7 +1 +explain extended select 2 union (values (5), (7), (1), (3), (4) limit 2 offset 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 2 AS `2` union (values (5),(7),(1),(3),(4) limit 1,2) +select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2); +2 +2 +1 +3 +explain extended select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION ALL NULL NULL NULL NULL 5 100.00 Using filesort +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 2 AS `2` union (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) +select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1); +2 +2 +3 +4 +explain extended select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION ALL NULL NULL NULL NULL 5 100.00 Using filesort +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 2 AS `2` union (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 1,2) +(values (5), (7), (1), (3), (4) limit 2) union select 2; +5 +5 +7 +2 +explain extended (values (5), (7), (1), (3), (4) limit 2) union select 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (values (5),(7),(1),(3),(4) limit 2) union /* select#2 */ select 2 AS `2` +(values (5), (7), (1), (3), (4) limit 2 offset 1) union select 2; +5 +7 +1 +2 +explain extended (values (5), (7), (1), (3), (4) limit 2 offset 1) union select 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (values (5),(7),(1),(3),(4) limit 1,2) union /* select#2 */ select 2 AS `2` +(values (5), (7), (1), (3), (4) order by 1 limit 2) union select 2; +5 +1 +3 +2 +explain extended (values (5), (7), (1), (3), (4) order by 1 limit 2) union select 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SUBQUERY ALL NULL NULL NULL NULL 5 100.00 Using filesort +3 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) union /* select#2 */ select 2 AS `2` +(values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1) union select 2; +5 +3 +4 +2 +explain extended (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1) union select 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SUBQUERY ALL NULL NULL NULL NULL 5 100.00 Using filesort +3 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 1,2) union /* select#2 */ select 2 AS `2` +select 3 union all (values (5), (7), (1), (3), (4) limit 2 offset 3); +3 +3 +3 +4 +explain extended select 3 union all (values (5), (7), (1), (3), (4) limit 2 offset 3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select 3 AS `3` union all (values (5),(7),(1),(3),(4) limit 3,2) +(values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3; +5 +3 +4 +3 +explain extended (values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 (values (5),(7),(1),(3),(4) limit 3,2) union all /* select#2 */ select 3 AS `3` +select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2); +3 +3 +1 +3 +explain extended select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION ALL NULL NULL NULL NULL 5 100.00 Using filesort +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 3 AS `3` union all (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) +(values (5), (7), (1), (3), (4) order by 1 limit 2) union all select 3; +5 +1 +3 +3 +explain extended (values (5), (7), (1), (3), (4) order by 1 limit 2) union all select 3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SUBQUERY ALL NULL NULL NULL NULL 5 100.00 Using filesort +3 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) union all /* select#2 */ select 3 AS `3` +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) +union +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +5 +7 +1 +3 +explain extended ( values (5), (7), (1), (3), (4) limit 2 offset 1 ) +union +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION ALL NULL NULL NULL NULL 5 100.00 Using filesort +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (values (5),(7),(1),(3),(4) limit 1,2) union (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) +union all +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +5 +7 +1 +1 +3 +explain extended ( values (5), (7), (1), (3), (4) limit 2 offset 1 ) +union all +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION ALL NULL NULL NULL NULL 5 100.00 Using filesort +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (values (5),(7),(1),(3),(4) limit 1,2) union all (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) +(values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3 order by 1; +5 +3 +3 +4 +explain extended (values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3 order by 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 (values (5),(7),(1),(3),(4) limit 3,2) union all /* select#2 */ select 3 AS `3` order by 1 +(values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 order by 1; +5 +3 +3 +4 +5 +explain extended (values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 order by 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SUBQUERY ALL NULL NULL NULL NULL 5 100.00 Using filesort +3 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 (/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 1,3) union all /* select#2 */ select 3 AS `3` order by 1 +(values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 +order by 1 limit 2 offset 1; +5 +3 +4 +explain extended (values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 +order by 1 limit 2 offset 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SUBQUERY ALL NULL NULL NULL NULL 5 100.00 Using filesort +3 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 (/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 1,3) union all /* select#2 */ select 3 AS `3` order by 1 limit 1,2 +values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3; +ERROR 42S22: Unknown column '3' in 'order clause' +prepare stmt from " +select 2 union (values (5), (7), (1), (3), (4) limit 2) +"; +execute stmt; +2 +2 +5 +7 +execute stmt; +2 +2 +5 +7 +deallocate prepare stmt; +prepare stmt from " +select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2) +"; +execute stmt; +2 +2 +1 +3 +execute stmt; +2 +2 +1 +3 +deallocate prepare stmt; +prepare stmt from " +select 3 union all (values (5), (7), (1), (3), (4) limit 2) +"; +execute stmt; +3 +3 +5 +7 +execute stmt; +3 +3 +5 +7 +deallocate prepare stmt; +prepare stmt from " +select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2) +"; +execute stmt; +3 +3 +1 +3 +execute stmt; +3 +3 +1 +3 +deallocate prepare stmt; +prepare stmt from " +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) + union +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +"; +execute stmt; +5 +7 +1 +3 +execute stmt; +5 +7 +1 +3 +deallocate prepare stmt; +prepare stmt from " +values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3; +"; +ERROR 42S22: Unknown column '3' in 'order clause' +create view v1 as values (5), (7), (1), (3), (4) order by 1 limit 2; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS values (5),(7),(1),(3),(4) order by 1 limit 2 latin1 latin1_swedish_ci +select * from v1; +5 +1 +3 +drop view v1; +create view v1 as +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) +union +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (values (5),(7),(1),(3),(4) limit 1,2) union (values (5),(7),(1),(3),(4) order by 1 limit 2) latin1 latin1_swedish_ci +select * from v1; +5 +7 +1 +3 +drop view v1; +create view v1 as values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3; +ERROR 42S22: Unknown column '3' in 'order clause' +create view v1 as +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) +union +( values (5), (7), (1), (3), (4) order by 2 limit 2 ); +ERROR 42S22: Unknown column '2' in 'order clause' diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index 0dd0a7a04b0..6b89816cc0c 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1123,3 +1123,196 @@ PREPARE stmt FROM "SELECT * FROM (VALUES(1 + 1,2,'abc')) t"; EXECUTE stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt; + +--echo # +--echo # MDEV-17894: tvc with ORDER BY ... LIMIT +--echo # + +let $q= +values (5), (7), (1), (3), (4) limit 2; +eval $q; +eval explain extended $q; + +let $q= +values (5), (7), (1), (3), (4) limit 2 offset 1; +eval $q; +eval explain extended $q; + +let $q= +values (5), (7), (1), (3), (4) order by 1 limit 2; +eval $q; +eval explain extended $q; + +let $q= +values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1; +eval $q; +eval explain extended $q; + +let $q= +values (5), (7), (1), (3), (4) order by 1; +eval $q; +eval explain extended $q; + +let $q= +values (5,90), (7,20), (1,70), (3,50), (4,10) order by 2; +eval $q; +eval explain extended $q; + +let $q= +select 2 union (values (5), (7), (1), (3), (4) limit 2); +eval $q; +eval explain extended $q; + +let $q= +select 2 union (values (5), (7), (1), (3), (4) limit 2 offset 1); +eval $q; +eval explain extended $q; + +let $q= +select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2); +eval $q; +eval explain extended $q; + +let $q= +select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1); +eval $q; +eval explain extended $q; + + +let $q= +(values (5), (7), (1), (3), (4) limit 2) union select 2; +eval $q; +eval explain extended $q; + +let $q= +(values (5), (7), (1), (3), (4) limit 2 offset 1) union select 2; +eval $q; +eval explain extended $q; + +let $q= +(values (5), (7), (1), (3), (4) order by 1 limit 2) union select 2; +eval $q; +eval explain extended $q; + +let $q= +(values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1) union select 2; +eval $q; +eval explain extended $q; + + +let $q= +select 3 union all (values (5), (7), (1), (3), (4) limit 2 offset 3); +eval $q; +eval explain extended $q; + +let $q= +(values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3; +eval $q; +eval explain extended $q; + +let $q= +select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2); +eval $q; +eval explain extended $q; + +let $q= +(values (5), (7), (1), (3), (4) order by 1 limit 2) union all select 3; +eval $q; +eval explain extended $q; + +let $q= +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) + union +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +eval $q; +eval explain extended $q; + +let $q= +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) + union all +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +eval $q; +eval explain extended $q; + +let $q= +(values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3 order by 1; +eval $q; +eval explain extended $q; + +let $q= +(values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 order by 1; +eval $q; +eval explain extended $q; + +let $q= +(values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 + order by 1 limit 2 offset 1; +eval $q; +eval explain extended $q; + +--error ER_BAD_FIELD_ERROR +values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3; + +prepare stmt from " +select 2 union (values (5), (7), (1), (3), (4) limit 2) +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2) +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +select 3 union all (values (5), (7), (1), (3), (4) limit 2) +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2) +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) + union +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +--error ER_BAD_FIELD_ERROR +prepare stmt from " +values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3; +"; + +create view v1 as values (5), (7), (1), (3), (4) order by 1 limit 2; +show create view v1; +select * from v1; +drop view v1; + +create view v1 as +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) + union +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +show create view v1; +select * from v1; +drop view v1; + +--error ER_BAD_FIELD_ERROR +create view v1 as values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3; + +--error ER_BAD_FIELD_ERROR +create view v1 as +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) + union +( values (5), (7), (1), (3), (4) order by 2 limit 2 ); -- cgit v1.2.1 From 462d6893977f31ffd4cb35f7eb76b521acfd1fb4 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Tue, 14 May 2019 21:47:38 +0400 Subject: MDEV-19468 Hybrid type expressions return wrong format for FLOAT --- mysql-test/main/derived_cond_pushdown.result | 2 +- mysql-test/main/select.result | 14 +++++------ mysql-test/main/select_jcl6.result | 14 +++++------ mysql-test/main/select_pkeycache.result | 14 +++++------ mysql-test/main/type_float.result | 37 ++++++++++++++++++++++++++++ mysql-test/main/type_float.test | 37 ++++++++++++++++++++++++++++ 6 files changed, 96 insertions(+), 22 deletions(-) (limited to 'mysql-test/main') diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index fa1cf8c0373..4474cb8c2d9 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -7884,7 +7884,7 @@ EXPLAIN "access_type": "ALL", "rows": 3, "filtered": 100, - "attached_condition": "sq.i = 2.7100000381469727", + "attached_condition": "sq.i = 2.71", "materialized": { "query_block": { "select_id": 2, diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result index 9b6a570717b..352ab4ddef4 100644 --- a/mysql-test/main/select.result +++ b/mysql-test/main/select.result @@ -2789,26 +2789,26 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range key1 key1 5 NULL 3 Using where; Using index select max(key1) from t1 where key1 <= 0.6158; max(key1) -0.6158000230789185 +0.6158 select max(key2) from t2 where key2 <= 1.6158; max(key2) -1.6158000230789185 +1.6158 select min(key1) from t1 where key1 >= 0.3762; min(key1) -0.37619999051094055 +0.3762 select min(key2) from t2 where key2 >= 1.3762; min(key2) -1.3761999607086182 +1.3762 select max(key1), min(key2) from t1, t2 where key1 <= 0.6158 and key2 >= 1.3762; max(key1) min(key2) -0.6158000230789185 1.3761999607086182 +0.6158 1.3762 select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5; max(key1) -0.38449999690055847 +0.3845 select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5; min(key1) -0.38449999690055847 +0.3845 DROP TABLE t1,t2; CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL); INSERT INTO t1 VALUES (10); diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result index d78fad15da1..1c7192b75b6 100644 --- a/mysql-test/main/select_jcl6.result +++ b/mysql-test/main/select_jcl6.result @@ -2800,26 +2800,26 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range key1 key1 5 NULL 3 Using where; Using index select max(key1) from t1 where key1 <= 0.6158; max(key1) -0.6158000230789185 +0.6158 select max(key2) from t2 where key2 <= 1.6158; max(key2) -1.6158000230789185 +1.6158 select min(key1) from t1 where key1 >= 0.3762; min(key1) -0.37619999051094055 +0.3762 select min(key2) from t2 where key2 >= 1.3762; min(key2) -1.3761999607086182 +1.3762 select max(key1), min(key2) from t1, t2 where key1 <= 0.6158 and key2 >= 1.3762; max(key1) min(key2) -0.6158000230789185 1.3761999607086182 +0.6158 1.3762 select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5; max(key1) -0.38449999690055847 +0.3845 select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5; min(key1) -0.38449999690055847 +0.3845 DROP TABLE t1,t2; CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL); INSERT INTO t1 VALUES (10); diff --git a/mysql-test/main/select_pkeycache.result b/mysql-test/main/select_pkeycache.result index 9b6a570717b..352ab4ddef4 100644 --- a/mysql-test/main/select_pkeycache.result +++ b/mysql-test/main/select_pkeycache.result @@ -2789,26 +2789,26 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range key1 key1 5 NULL 3 Using where; Using index select max(key1) from t1 where key1 <= 0.6158; max(key1) -0.6158000230789185 +0.6158 select max(key2) from t2 where key2 <= 1.6158; max(key2) -1.6158000230789185 +1.6158 select min(key1) from t1 where key1 >= 0.3762; min(key1) -0.37619999051094055 +0.3762 select min(key2) from t2 where key2 >= 1.3762; min(key2) -1.3761999607086182 +1.3762 select max(key1), min(key2) from t1, t2 where key1 <= 0.6158 and key2 >= 1.3762; max(key1) min(key2) -0.6158000230789185 1.3761999607086182 +0.6158 1.3762 select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5; max(key1) -0.38449999690055847 +0.3845 select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5; min(key1) -0.38449999690055847 +0.3845 DROP TABLE t1,t2; CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL); INSERT INTO t1 VALUES (10); diff --git a/mysql-test/main/type_float.result b/mysql-test/main/type_float.result index be7c639ddd3..8e97ab497ea 100644 --- a/mysql-test/main/type_float.result +++ b/mysql-test/main/type_float.result @@ -840,3 +840,40 @@ DROP TABLE t1; # # End of 10.2 tests # +# +# Start of 10.3 tests +# +# +# MDEV-19468 Hybrid type expressions return wrong format for FLOAT +# +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES (0.671437); +SELECT a, COALESCE(a), MAX(a), LEAST(a,a), (SELECT a FROM t1) AS c FROM t1; +a COALESCE(a) MAX(a) LEAST(a,a) c +0.671437 0.671437 0.671437 0.671437 0.671437 +DROP TABLE t1; +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES (0.671437); +SELECT +CONCAT(a), +CONCAT(COALESCE(a)), +CONCAT(LEAST(a,a)), +CONCAT(MAX(a)), +CONCAT((SELECT a FROM t1)) AS c +FROM t1; +CONCAT(a) CONCAT(COALESCE(a)) CONCAT(LEAST(a,a)) CONCAT(MAX(a)) c +0.671437 0.671437 0.671437 0.671437 0.671437 +CREATE TABLE t2 AS SELECT +CONCAT(a), +CONCAT(COALESCE(a)), +CONCAT(LEAST(a,a)), +CONCAT(MAX(a)), +CONCAT((SELECT a FROM t1)) AS c +FROM t1; +SELECT * FROM t2; +CONCAT(a) CONCAT(COALESCE(a)) CONCAT(LEAST(a,a)) CONCAT(MAX(a)) c +0.671437 0.671437 0.671437 0.671437 0.671437 +DROP TABLE t1, t2; +# +# End of 10.3 tests +# diff --git a/mysql-test/main/type_float.test b/mysql-test/main/type_float.test index 9dba1c709d5..5f9958dbe2b 100644 --- a/mysql-test/main/type_float.test +++ b/mysql-test/main/type_float.test @@ -581,3 +581,40 @@ DROP TABLE t1; --echo # --echo # End of 10.2 tests --echo # + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-19468 Hybrid type expressions return wrong format for FLOAT +--echo # + +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES (0.671437); +SELECT a, COALESCE(a), MAX(a), LEAST(a,a), (SELECT a FROM t1) AS c FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES (0.671437); +SELECT + CONCAT(a), + CONCAT(COALESCE(a)), + CONCAT(LEAST(a,a)), + CONCAT(MAX(a)), + CONCAT((SELECT a FROM t1)) AS c +FROM t1; +CREATE TABLE t2 AS SELECT + CONCAT(a), + CONCAT(COALESCE(a)), + CONCAT(LEAST(a,a)), + CONCAT(MAX(a)), + CONCAT((SELECT a FROM t1)) AS c +FROM t1; +SELECT * FROM t2; +DROP TABLE t1, t2; + + +--echo # +--echo # End of 10.3 tests +--echo # -- cgit v1.2.1 From b7d22a843e36cd5b8695f8ac2b92789d1cf50e4f Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Thu, 16 May 2019 10:16:09 +0400 Subject: MDEV-16872 Add CAST(expr AS FLOAT) --- mysql-test/main/cast.result | 4 +-- mysql-test/main/dyncol.result | 4 +-- mysql-test/main/gis.result | 6 ++++ mysql-test/main/gis.test | 6 ++++ mysql-test/main/type_float.result | 68 +++++++++++++++++++++++++++++++++++++++ mysql-test/main/type_float.test | 39 ++++++++++++++++++++++ 6 files changed, 123 insertions(+), 4 deletions(-) (limited to 'mysql-test/main') diff --git a/mysql-test/main/cast.result b/mysql-test/main/cast.result index 1dc6bbbf9e7..d500ef4fa1d 100644 --- a/mysql-test/main/cast.result +++ b/mysql-test/main/cast.result @@ -271,12 +271,12 @@ select cast(1000 as double(5,2)); cast(1000 as double(5,2)) 999.99 Warnings: -Warning 1264 Out of range value for column 'cast(1000 as double(5,2))' at row 1 +Note 1264 Out of range value for column 'cast(1000 as double(5,2))' at row 1 select cast(-1000 as double(5,2)); cast(-1000 as double(5,2)) -999.99 Warnings: -Warning 1264 Out of range value for column 'cast(-1000 as double(5,2))' at row 1 +Note 1264 Out of range value for column 'cast(-1000 as double(5,2))' at row 1 select cast(010203101112.121314 as datetime); cast(010203101112.121314 as datetime) 2001-02-03 10:11:12 diff --git a/mysql-test/main/dyncol.result b/mysql-test/main/dyncol.result index 7a5eeac67cc..d233d0d3be2 100644 --- a/mysql-test/main/dyncol.result +++ b/mysql-test/main/dyncol.result @@ -501,12 +501,12 @@ select column_get(column_create(1, "1223.5555" AS double), 1 as double(5,2)); column_get(column_create(1, "1223.5555" AS double), 1 as double(5,2)) 999.99 Warnings: -Warning 1264 Out of range value for column 'column_get(column_create(1, "1223.5555" AS double), 1 as double(5,2))' at row 1 +Note 1264 Out of range value for column 'column_get(column_create(1, "1223.5555" AS double), 1 as double(5,2))' at row 1 select column_get(column_create(1, "1223.5555" AS double), 1 as double(3,2)); column_get(column_create(1, "1223.5555" AS double), 1 as double(3,2)) 9.99 Warnings: -Warning 1264 Out of range value for column 'column_get(column_create(1, "1223.5555" AS double), 1 as double(3,2))' at row 1 +Note 1264 Out of range value for column 'column_get(column_create(1, "1223.5555" AS double), 1 as double(3,2))' at row 1 # # column get decimal # diff --git a/mysql-test/main/gis.result b/mysql-test/main/gis.result index fb8919ef061..b154df24585 100644 --- a/mysql-test/main/gis.result +++ b/mysql-test/main/gis.result @@ -4038,6 +4038,8 @@ SELECT CAST(POINT(1,1) AS SIGNED) FROM t1; ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_signed' SELECT CAST(POINT(1,1) AS UNSIGNED) FROM t1; ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_unsigned' +SELECT CAST(POINT(1,1) AS FLOAT) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'float_typecast' SELECT CAST(POINT(1,1) AS DOUBLE) FROM t1; ERROR HY000: Illegal parameter data type geometry for operation 'double_typecast' SELECT CAST(POINT(1,1) AS DECIMAL(10,1)) FROM t1; @@ -4054,6 +4056,8 @@ SELECT CAST(a AS SIGNED) FROM t1; ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_signed' SELECT CAST(a AS UNSIGNED) FROM t1; ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_unsigned' +SELECT CAST(a AS FLOAT) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'float_typecast' SELECT CAST(a AS DOUBLE) FROM t1; ERROR HY000: Illegal parameter data type geometry for operation 'double_typecast' SELECT CAST(a AS DECIMAL(10,1)) FROM t1; @@ -4070,6 +4074,8 @@ SELECT CAST(COALESCE(a) AS SIGNED) FROM t1; ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_signed' SELECT CAST(COALESCE(a) AS UNSIGNED) FROM t1; ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_unsigned' +SELECT CAST(COALESCE(a) AS FLOAT) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'float_typecast' SELECT CAST(COALESCE(a) AS DOUBLE) FROM t1; ERROR HY000: Illegal parameter data type geometry for operation 'double_typecast' SELECT CAST(COALESCE(a) AS DECIMAL(10,1)) FROM t1; diff --git a/mysql-test/main/gis.test b/mysql-test/main/gis.test index c4115f7ed54..ff09a098107 100644 --- a/mysql-test/main/gis.test +++ b/mysql-test/main/gis.test @@ -2109,6 +2109,8 @@ SELECT CAST(POINT(1,1) AS SIGNED) FROM t1; --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION SELECT CAST(POINT(1,1) AS UNSIGNED) FROM t1; --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(POINT(1,1) AS FLOAT) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION SELECT CAST(POINT(1,1) AS DOUBLE) FROM t1; --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION SELECT CAST(POINT(1,1) AS DECIMAL(10,1)) FROM t1; @@ -2126,6 +2128,8 @@ SELECT CAST(a AS SIGNED) FROM t1; --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION SELECT CAST(a AS UNSIGNED) FROM t1; --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(a AS FLOAT) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION SELECT CAST(a AS DOUBLE) FROM t1; --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION SELECT CAST(a AS DECIMAL(10,1)) FROM t1; @@ -2143,6 +2147,8 @@ SELECT CAST(COALESCE(a) AS SIGNED) FROM t1; --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION SELECT CAST(COALESCE(a) AS UNSIGNED) FROM t1; --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION +SELECT CAST(COALESCE(a) AS FLOAT) FROM t1; +--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION SELECT CAST(COALESCE(a) AS DOUBLE) FROM t1; --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION SELECT CAST(COALESCE(a) AS DECIMAL(10,1)) FROM t1; diff --git a/mysql-test/main/type_float.result b/mysql-test/main/type_float.result index 8e97ab497ea..217fa3aff2a 100644 --- a/mysql-test/main/type_float.result +++ b/mysql-test/main/type_float.result @@ -875,5 +875,73 @@ CONCAT(a) CONCAT(COALESCE(a)) CONCAT(LEAST(a,a)) CONCAT(MAX(a)) c 0.671437 0.671437 0.671437 0.671437 0.671437 DROP TABLE t1, t2; # +# MDEV-16872 Add CAST(expr AS FLOAT) +# +SELECT CAST(0.671437 AS FLOAT), CONCAT(CAST(0.671437 AS FLOAT)); +CAST(0.671437 AS FLOAT) CONCAT(CAST(0.671437 AS FLOAT)) +0.671437 0.671437 +SELECT CAST(1e40 AS FLOAT), CONCAT(CAST(1e40 AS FLOAT)); +CAST(1e40 AS FLOAT) CONCAT(CAST(1e40 AS FLOAT)) +3.40282e38 3.40282e38 +Warnings: +Note 1264 Out of range value for column 'CAST(1e40 AS FLOAT)' at row 1 +Note 1264 Out of range value for column 'CAST(1e40 AS FLOAT)' at row 1 +SELECT CAST(-1e40 AS FLOAT), CONCAT(CAST(-1e40 AS FLOAT)); +CAST(-1e40 AS FLOAT) CONCAT(CAST(-1e40 AS FLOAT)) +-3.40282e38 -3.40282e38 +Warnings: +Note 1264 Out of range value for column 'CAST(-1e40 AS FLOAT)' at row 1 +Note 1264 Out of range value for column 'CAST(-1e40 AS FLOAT)' at row 1 +SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES'; +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES (CAST(1e40 AS FLOAT)); +Warnings: +Note 1264 Out of range value for column 'CAST(1e40 AS FLOAT)' at row 1 +SELECT * FROM t1; +a +3.40282e38 +DROP TABLE t1; +SET sql_mode=DEFAULT; +EXPLAIN EXTENDED SELECT CAST(0.671437 AS FLOAT); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select cast(0.671437 as float) AS `CAST(0.671437 AS FLOAT)` +CREATE TABLE t1 AS SELECT CAST(0.671437 AS FLOAT) AS c1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` float DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 +0.671437 +DROP TABLE t1; +CREATE TABLE t1 (a FLOAT); +CREATE TABLE t2 AS SELECT CONCAT(a) AS c1, CONCAT(CAST(a AS FLOAT)) AS c2 FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `c1` varchar(12) DEFAULT NULL, + `c2` varchar(12) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1, t2; +CREATE TABLE t1 (a FLOAT DEFAULT CAST(0.671437 AS FLOAT)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` float DEFAULT (cast(0.671437 as float)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, a FLOAT); +INSERT INTO t1 VALUES (1, 0.671437),(2, 0.671437); +DELETE FROM t1 WHERE a=0.671437; +SELECT * FROM t1; +id a +1 0.671437 +2 0.671437 +DELETE FROM t1 WHERE a=CAST(0.671437 AS FLOAT); +DROP TABLE t1; +# # End of 10.3 tests # diff --git a/mysql-test/main/type_float.test b/mysql-test/main/type_float.test index 5f9958dbe2b..65c8130b5db 100644 --- a/mysql-test/main/type_float.test +++ b/mysql-test/main/type_float.test @@ -615,6 +615,45 @@ SELECT * FROM t2; DROP TABLE t1, t2; +--echo # +--echo # MDEV-16872 Add CAST(expr AS FLOAT) +--echo # + +SELECT CAST(0.671437 AS FLOAT), CONCAT(CAST(0.671437 AS FLOAT)); +SELECT CAST(1e40 AS FLOAT), CONCAT(CAST(1e40 AS FLOAT)); +SELECT CAST(-1e40 AS FLOAT), CONCAT(CAST(-1e40 AS FLOAT)); + +SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES'; +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES (CAST(1e40 AS FLOAT)); +SELECT * FROM t1; +DROP TABLE t1; +SET sql_mode=DEFAULT; + +EXPLAIN EXTENDED SELECT CAST(0.671437 AS FLOAT); + +CREATE TABLE t1 AS SELECT CAST(0.671437 AS FLOAT) AS c1; +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a FLOAT); +CREATE TABLE t2 AS SELECT CONCAT(a) AS c1, CONCAT(CAST(a AS FLOAT)) AS c2 FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t1, t2; + +CREATE TABLE t1 (a FLOAT DEFAULT CAST(0.671437 AS FLOAT)); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, a FLOAT); +INSERT INTO t1 VALUES (1, 0.671437),(2, 0.671437); +DELETE FROM t1 WHERE a=0.671437; +SELECT * FROM t1; +DELETE FROM t1 WHERE a=CAST(0.671437 AS FLOAT); +DROP TABLE t1; + + --echo # --echo # End of 10.3 tests --echo # -- cgit v1.2.1