[Commits] 45e2f89: MDEV-17894 Assertion `(thd->lex)->current_select' failed in MYSQLparse(),
revision-id: 45e2f8906d5e8e4e6c6085c32d8d6245d0016b1f (mariadb-10.3.12-86-g45e2f89) parent(s): 51e48b9f8981986257a1cfbdf75e4fc29a5959c1 author: Igor Babaev committer: Igor Babaev timestamp: 2019-05-06 14:58:41 -0700 message: 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 | 355 +++++++++++++++++++++ mysql-test/main/table_value_constr.test | 162 ++++++++++ .../compat/oracle/r/table_value_constr.result | 299 +++++++++++++++++ .../suite/compat/oracle/t/table_value_constr.test | 134 ++++++++ sql/item_subselect.cc | 2 +- sql/item_subselect.h | 2 +- sql/sql_lex.cc | 16 + sql/sql_lex.h | 6 + sql/sql_tvc.cc | 203 ++++++++++-- sql/sql_tvc.h | 5 + sql/sql_union.cc | 19 +- sql/sql_yacc.yy | 20 +- sql/sql_yacc_ora.yy | 20 +- 13 files changed, 1196 insertions(+), 47 deletions(-) diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 1d485af..e5d43d4 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -2189,3 +2189,358 @@ 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 <unit1> 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 <unit1> 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 <unit1> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 values (5),(7),(1),(3),(4) order by 1 +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 <union1,2> 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 <union1,2> 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 <derived2> 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 <union1,3> 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 <derived2> 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 <union1,3> 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 <union1,2> 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 <union1,2> 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 <derived3> 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 <union1,2> 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 <derived3> 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 <union1,2> 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 <derived2> 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 <union1,3> 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 <derived3> 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 <union1,2> 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 <derived2> 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 <union1,3> 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 <derived2> 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 <union1,3> 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 <union1,2> 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 <derived3> 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 <union1,2> 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 <derived3> 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 <union1,2> 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 +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; +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 (select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) latin1 latin1_swedish_ci +select * from v1; +5 +7 +1 +3 +drop view v1; diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index 0dd0a7a..3980774 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1123,3 +1123,165 @@ 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= +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; + +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; + +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; diff --git a/mysql-test/suite/compat/oracle/r/table_value_constr.result b/mysql-test/suite/compat/oracle/r/table_value_constr.result index 18fce08..9c881c3 100644 --- a/mysql-test/suite/compat/oracle/r/table_value_constr.result +++ b/mysql-test/suite/compat/oracle/r/table_value_constr.result @@ -2183,3 +2183,302 @@ VALUES(1 + 1,2,'abc'); SELECT * FROM (VALUES(1 + 1,2,'abc')) t; 1 + 1 2 abc 2 2 abc +# +# 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 <unit1> 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 <unit1> 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 <unit1> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 values (5),(7),(1),(3),(4) order by 1 +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 <union1,2> 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 <union1,2> 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 <derived2> 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 <union1,3> 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 <derived2> 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 <union1,3> 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 <union1,2> 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 <union1,2> 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 <derived3> 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 <union1,2> 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 <derived3> 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 <union1,2> 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 <derived2> 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 <union1,3> 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 <derived3> 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 <union1,2> 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 <derived2> 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 <union1,3> 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 <derived2> 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 <union1,3> 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 <union1,2> 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 <derived3> 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 <union1,2> 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 <derived3> 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 <union1,2> 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 +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 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 VIEW "v1" AS (values (5),(7),(1),(3),(4) limit 1,2) union (select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 2) latin1 latin1_swedish_ci +select * from v1; +5 +7 +1 +3 +drop view v1; diff --git a/mysql-test/suite/compat/oracle/t/table_value_constr.test b/mysql-test/suite/compat/oracle/t/table_value_constr.test index 37d2521..808ce58 100644 --- a/mysql-test/suite/compat/oracle/t/table_value_constr.test +++ b/mysql-test/suite/compat/oracle/t/table_value_constr.test @@ -1125,3 +1125,137 @@ DROP VIEW v1; VALUES(1 + 1,2,'abc'); SELECT * FROM (VALUES(1 + 1,2,'abc')) t; + +--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= +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; + +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; diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 207aa9a..475e74d 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -269,7 +269,7 @@ bool Item_subselect::fix_fields(THD *thd_param, Item **ref) { if (sl->tvc) { - wrap_tvc_in_derived_table(thd, sl); + wrap_tvc_into_select(thd, sl); } } diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 363dbba..5a9968b 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -267,7 +267,7 @@ class Item_subselect :public Item_result_field, Item* build_clone(THD *thd) { return 0; } Item* get_copy(THD *thd) { return 0; } - bool wrap_tvc_in_derived_table(THD *thd, st_select_lex *tvc_sl); + bool wrap_tvc_into_select(THD *thd, st_select_lex *tvc_sl); friend class select_result_interceptor; friend class Item_in_optimizer; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index d6cc62c..638530d 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2275,6 +2275,7 @@ void st_select_lex_unit::init_query() with_element= 0; columns_are_renamed= false; intersect_mark= NULL; + with_wrapped_tvc= false; } void st_select_lex::init_query() @@ -3411,6 +3412,19 @@ bool st_select_lex_unit::union_needs_tmp_table() { if (with_element && with_element->is_recursive) return true; + if (!with_wrapped_tvc) + { + for (st_select_lex *sl= first_select(); sl; sl=sl->next_select()) + { + if (sl->tvc && sl->tvc->to_be_wrapped_as_with_tail()) + { + with_wrapped_tvc= true; + break; + } + } + } + if (with_wrapped_tvc) + return true; return union_distinct != NULL || global_parameters()->order_list.elements != 0 || thd->lex->sql_command == SQLCOM_INSERT_SELECT || @@ -8236,6 +8250,8 @@ bool LEX::tvc_finalize() current_select->options)))) return true; many_values.empty(); + if (!current_select->master_unit()->fake_select_lex) + current_select->master_unit()->add_fake_select_lex(thd); return false; } diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 926b09e..4123275 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -806,6 +806,12 @@ class st_select_lex_unit: public st_select_lex_node { */ Item_int *intersect_mark; /** + TRUE if the unit contained TVC at the top level that has been wrapped + into SELECT: + VALUES (v1) ... (vn) => SELECT * FROM (VALUES (v1) ... (vn)) as tvc + */ + bool with_wrapped_tvc; + /** Pointer to 'last' select, or pointer to select where we stored global parameters for union. diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index a5085fd..c400264 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -344,6 +344,7 @@ bool table_value_constr::exec(SELECT_LEX *sl) DBUG_ENTER("table_value_constr::exec"); List_iterator_fast<List_item> li(lists_of_values); List_item *elem; + ha_rows send_records= 0; if (select_options & SELECT_DESCRIBE) DBUG_RETURN(false); @@ -357,7 +358,13 @@ bool table_value_constr::exec(SELECT_LEX *sl) while ((elem= li++)) { - result->send_data(*elem); + if (send_records >= sl->master_unit()->select_limit_cnt) + break; + int rc= result->send_data(*elem); + if (!rc) + send_records++; + else if (rc > 0) + DBUG_RETURN(true); } if (result->send_eof()) @@ -436,6 +443,12 @@ void table_value_constr::print(THD *thd, String *str, print_list_item(str, list, query_type); } + if (select_lex->order_list.elements) + { + str->append(STRING_WITH_LEN(" order by ")); + select_lex->print_order(str, select_lex->order_list.first, query_type); + } + select_lex->print_limit(thd, str, query_type); } @@ -533,7 +546,8 @@ static bool create_tvc_name(THD *thd, st_select_lex *parent_select, char buff[6]; alias->length= my_snprintf(buff, sizeof(buff), - "tvc_%u", parent_select->curr_tvc_name); + "tvc_%u", + parent_select ? parent_select->curr_tvc_name : 0); alias->str= thd->strmake(buff, alias->length); if (!alias->str) return true; @@ -542,19 +556,57 @@ static bool create_tvc_name(THD *thd, st_select_lex *parent_select, } -bool Item_subselect::wrap_tvc_in_derived_table(THD *thd, - st_select_lex *tvc_sl) +/** + @brief + Check whether TVC used in unit is to be wrapped into select + + @details + TVC used in unit that contains more than one members is to be wrapped + into select if it is tailed with ORDER BY ... LIMIT n [OFFSET m] + + @retval + true if TVC is to be wrapped + false otherwise +*/ + +bool table_value_constr::to_be_wrapped_as_with_tail() +{ + return select_lex->master_unit()->first_select()->next_select() && + select_lex->order_list.elements && select_lex->explicit_limit; +} + + +/** + @brief + Wrap table value constructor into a select + + @param thd The context handler + @param tvc_sl The TVC to wrap + @parent_select The parent select if tvc_sl used in a subquery + + @details + The function wraps the TVC tvc_sl into a select: + the function transforms the TVC of the form VALUES (v1), ... (vn) into + the select of the form + SELECT * FROM (VALUES (v1), ... (vn)) tvc_x + + @retval pointer to the result of of the transformation if successful + NULL - otherwise +*/ + +static +st_select_lex *wrap_tvc(THD *thd, st_select_lex *tvc_sl, + st_select_lex *parent_select) { LEX *lex= thd->lex; - /* SELECT_LEX object where the transformation is performed */ - SELECT_LEX *parent_select= lex->current_select; + select_result *save_result= thd->lex->result; uint8 save_derived_tables= lex->derived_tables; + thd->lex->result= NULL; Query_arena backup; Query_arena *arena= thd->activate_stmt_arena_if_needed(&backup); - /* - Create SELECT_LEX of the subquery SQ used in the result of transformation + Create SELECT_LEX of the select used in the result of transformation */ lex->current_select= tvc_sl; if (mysql_new_select(lex, 0, NULL)) @@ -562,15 +614,15 @@ bool Item_subselect::wrap_tvc_in_derived_table(THD *thd, mysql_init_select(lex); /* Create item list as '*' for the subquery SQ */ Item *item; - SELECT_LEX *sq_select; // select for IN subquery; - sq_select= lex->current_select; - sq_select->linkage= tvc_sl->linkage; - sq_select->parsing_place= SELECT_LIST; - item= new (thd->mem_root) Item_field(thd, &sq_select->context, + SELECT_LEX *wrapper_sl; + wrapper_sl= lex->current_select; + wrapper_sl->linkage= tvc_sl->linkage; + wrapper_sl->parsing_place= SELECT_LIST; + item= new (thd->mem_root) Item_field(thd, &wrapper_sl->context, NULL, NULL, &star_clex_str); if (item == NULL || add_item_to_list(thd, item)) goto err; - (sq_select->with_wild)++; + (wrapper_sl->with_wild)++; /* Exclude SELECT with TVC */ tvc_sl->exclude(); @@ -585,11 +637,11 @@ bool Item_subselect::wrap_tvc_in_derived_table(THD *thd, derived_unit= tvc_select->master_unit(); tvc_select->linkage= DERIVED_TABLE_TYPE; - lex->current_select= sq_select; + lex->current_select= wrapper_sl; /* Create the name of the wrapping derived table and - add it to the FROM list of the subquery SQ + add it to the FROM list of the wrapper */ Table_ident *ti; LEX_CSTRING alias; @@ -598,35 +650,120 @@ bool Item_subselect::wrap_tvc_in_derived_table(THD *thd, create_tvc_name(thd, parent_select, &alias)) goto err; if (!(derived_tab= - sq_select->add_table_to_list(thd, - ti, &alias, 0, - TL_READ, MDL_SHARED_READ))) + wrapper_sl->add_table_to_list(thd, + ti, &alias, 0, + TL_READ, MDL_SHARED_READ))) goto err; - sq_select->add_joined_table(derived_tab); - sq_select->add_where_field(derived_unit->first_select()); - sq_select->context.table_list= sq_select->table_list.first; - sq_select->context.first_name_resolution_table= sq_select->table_list.first; - sq_select->table_list.first->derived_type= DTYPE_TABLE | DTYPE_MATERIALIZE; + wrapper_sl->add_joined_table(derived_tab); + wrapper_sl->add_where_field(derived_unit->first_select()); + wrapper_sl->context.table_list= wrapper_sl->table_list.first; + wrapper_sl->context.first_name_resolution_table= wrapper_sl->table_list.first; + wrapper_sl->table_list.first->derived_type= DTYPE_TABLE | DTYPE_MATERIALIZE; lex->derived_tables|= DERIVED_SUBQUERY; - sq_select->where= 0; - sq_select->set_braces(false); + wrapper_sl->where= 0; + wrapper_sl->set_braces(false); derived_unit->set_with_clause(0); - if (engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE) - ((subselect_single_select_engine *) engine)->change_select(sq_select); - if (arena) thd->restore_active_arena(arena, &backup); - lex->current_select= sq_select; - return false; + thd->lex->result= save_result; + return wrapper_sl; err: if (arena) thd->restore_active_arena(arena, &backup); + thd->lex->result= save_result; lex->derived_tables= save_derived_tables; - lex->current_select= parent_select; - return true; + return 0; +} + + +/** + @brief + Wrap TVC with ORDER BY ... LIMIT tail into a select + + @param thd The context handler + @param tvc_sl The TVC to wrap + + @details + The function wraps the TVC tvc_sl into a select: + the function transforms the TVC with tail of the form + VALUES (v1), ... (vn) ORDER BY ... LIMIT n [OFFSET m] + into the select with the same tail of the form + SELECT * FROM (VALUES (v1), ... (vn)) tvc_x + ORDER BY ... LIMIT n [OFFSET m] + + @retval pointer to the result of of the transformation if successful + NULL - otherwise +*/ + +st_select_lex *wrap_tvc_with_tail(THD *thd, st_select_lex *tvc_sl) +{ + st_select_lex *wrapper_sl= wrap_tvc(thd, tvc_sl, NULL); + if (!wrapper_sl) + return NULL; + + wrapper_sl->order_list= tvc_sl->order_list; + wrapper_sl->select_limit= tvc_sl->select_limit; + wrapper_sl->offset_limit= tvc_sl->offset_limit; + wrapper_sl->braces= tvc_sl->braces; + wrapper_sl->explicit_limit= tvc_sl->explicit_limit; + tvc_sl->order_list.empty(); + tvc_sl->select_limit= NULL; + tvc_sl->offset_limit= NULL; + tvc_sl->braces= 0; + tvc_sl->explicit_limit= false; + if (tvc_sl->select_number == 1) + { + tvc_sl->select_number= wrapper_sl->select_number; + wrapper_sl->select_number= 1; + } + if (tvc_sl->master_unit()->union_distinct == tvc_sl) + { + wrapper_sl->master_unit()->union_distinct= wrapper_sl; + } + thd->lex->current_select= wrapper_sl; + return wrapper_sl; +} + + +/** + @brief + Wrap TVC in a subselect into a select + + @param thd The context handler + @param tvc_sl The TVC to wrap + + @details + The function wraps the TVC tvc_sl used in a subselect into a select + the function transforms the TVC of the form VALUES (v1), ... (vn) + into the select the form + SELECT * FROM (VALUES (v1), ... (vn)) tvc_x + and replaces the subselect with the result of the transformation. + + @retval false if successfull + true otherwise +*/ + +bool Item_subselect::wrap_tvc_into_select(THD *thd, st_select_lex *tvc_sl) +{ + LEX *lex= thd->lex; + /* SELECT_LEX object where the transformation is performed */ + SELECT_LEX *parent_select= lex->current_select; + SELECT_LEX *wrapper_sl= wrap_tvc(thd, tvc_sl, parent_select); + if (wrapper_sl) + { + if (engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE) + ((subselect_single_select_engine *) engine)->change_select(wrapper_sl); + lex->current_select= wrapper_sl; + return false; + } + else + { + lex->current_select= parent_select; + return true; + } } diff --git a/sql/sql_tvc.h b/sql/sql_tvc.h index 128cc88..594a77a 100644 --- a/sql/sql_tvc.h +++ b/sql/sql_tvc.h @@ -57,6 +57,8 @@ class table_value_constr : public Sql_alloc select_result *tmp_result, st_select_lex_unit *unit_arg); + bool to_be_wrapped_as_with_tail(); + int save_explain_data_intern(THD *thd_arg, Explain_query *output); bool optimize(THD *thd_arg); @@ -64,4 +66,7 @@ class table_value_constr : public Sql_alloc void print(THD *thd_arg, String *str, enum_query_type query_type); }; + +st_select_lex *wrap_tvc_with_tail(THD *thd, st_select_lex *tvc_sl); + #endif /* SQL_TVC_INCLUDED */ diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 7b0e796..bcca27c 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -831,7 +831,8 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, bool is_union_select; bool have_except= FALSE, have_intersect= FALSE; bool instantiate_tmp_table= false; - bool single_tvc= !first_sl->next_select() && first_sl->tvc; + bool single_tvc= !first_sl->next_select() && first_sl->tvc && + !fake_select_lex; DBUG_ENTER("st_select_lex_unit::prepare"); DBUG_ASSERT(thd == current_thd); @@ -986,7 +987,21 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, { if (sl->tvc) { - if (sl->tvc->prepare(thd, sl, tmp_result, this)) + if (sl->tvc->to_be_wrapped_as_with_tail()) + { + st_select_lex *wrapper_sl= wrap_tvc_with_tail(thd, sl); + if (!wrapper_sl) + goto err; + + if (sl == first_sl) + first_sl= wrapper_sl; + sl= wrapper_sl; + + if (prepare_join(thd, sl, tmp_result, additional_options, + is_union_select)) + goto err; + } + else if (sl->tvc->prepare(thd, sl, tmp_result, this)) goto err; } else if (prepare_join(thd, sl, tmp_result, additional_options, diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 98ead67..b2f597c 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -9192,7 +9192,7 @@ select_paren: { Lex->current_select->set_braces(true); } - table_value_constructor + table_value_constructor select_part3 { DBUG_ASSERT(Lex->current_select->braces); } @@ -9212,6 +9212,12 @@ select_paren: | '(' select_paren ')' ; +select_parent_union_query_term_proper: + SELECT_SYM select_options_and_item_list select_part3_union_query_term + opt_select_lock_type + | table_value_constructor select_part3_union_query_term + ; + select_paren_union_query_term: { /* @@ -9220,14 +9226,19 @@ select_paren_union_query_term: */ Lex->current_select->set_braces(true); } - SELECT_SYM select_options_and_item_list select_part3_union_query_term - opt_select_lock_type + select_parent_union_query_term_proper { DBUG_ASSERT(Lex->current_select->braces); } | '(' select_paren_union_query_term ')' ; +select_parent_view_proper: + SELECT_SYM select_options_and_item_list select_part3_view + opt_select_lock_type + | table_value_constructor select_part3_view + ; + select_paren_view: { /* @@ -9236,8 +9247,7 @@ select_paren_view: */ Lex->current_select->set_braces(true); } - SELECT_SYM select_options_and_item_list select_part3_view - opt_select_lock_type + select_parent_view_proper { DBUG_ASSERT(Lex->current_select->braces); } diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index f7aa1c9..233882e 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -9129,7 +9129,7 @@ select_paren: { Lex->current_select->set_braces(true); } - table_value_constructor + table_value_constructor select_part3 { DBUG_ASSERT(Lex->current_select->braces); } @@ -9149,6 +9149,12 @@ select_paren: | '(' select_paren ')' ; +select_parent_union_query_term_proper: + SELECT_SYM select_options_and_item_list select_part3_union_query_term + opt_select_lock_type + | table_value_constructor select_part3_union_query_term + ; + select_paren_union_query_term: { /* @@ -9157,14 +9163,19 @@ select_paren_union_query_term: */ Lex->current_select->set_braces(true); } - SELECT_SYM select_options_and_item_list select_part3_union_query_term - opt_select_lock_type + select_parent_union_query_term_proper { DBUG_ASSERT(Lex->current_select->braces); } | '(' select_paren_union_query_term ')' ; +select_parent_view_proper: + SELECT_SYM select_options_and_item_list select_part3_view + opt_select_lock_type + | table_value_constructor select_part3_view + ; + select_paren_view: { /* @@ -9173,8 +9184,7 @@ select_paren_view: */ Lex->current_select->set_braces(true); } - SELECT_SYM select_options_and_item_list select_part3_view - opt_select_lock_type + select_parent_view_proper { DBUG_ASSERT(Lex->current_select->braces); }
participants (1)
-
IgorBabaev