- commits - lists.mariadb.org

[Commits] 45e2f89: MDEV-17894 Assertion `(thd->lex)->current_select' failed in MYSQLparse(),
by IgorBabaev 06 May '19

06 May '19

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); }

1 0

[Commits] a5cfa416b4f: Let us close library and only then free defaults.
by Oleksandr Byelkin 06 May '19

06 May '19

revision-id: a5cfa416b4f5d888db856739c69fbb0124fdd8a3 (mariadb-10.2.23-119-ga5cfa416b4f) parent(s): 5496df8e5daabcbd3eca8d96a3d6caa450913acc author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-05-06 22:31:46 +0200 message: Let us close library and only then free defaults. --- client/mysqlimport.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/client/mysqlimport.c b/client/mysqlimport.c index 02caf2df198..8f42e6f5a8f 100644 --- a/client/mysqlimport.c +++ b/client/mysqlimport.c @@ -514,11 +514,11 @@ static void safe_exit(int error, MYSQL *mysql) if (mysql) mysql_close(mysql); + mysql_library_end(); #ifdef HAVE_SMEM my_free(shared_memory_base_name); #endif free_defaults(argv_to_free); - mysql_library_end(); my_free(opt_password); if (error) sf_leaking_memory= 1; /* dirty exit, some threads are still running */

1 0

[Commits] fd386e3: MDEV-18689 Simple query with extra brackets stopped working
by IgorBabaev 06 May '19

06 May '19

revision-id: fd386e39cd384cd738231c0c65f7d3dee4eac7c9 (mariadb-10.4.4-74-gfd386e3) parent(s): b8259e4b599e0968ebe8c9ae11e8855f42b188f5 author: Igor Babaev committer: Igor Babaev timestamp: 2019-05-06 11:14:39 -0700 message: MDEV-18689 Simple query with extra brackets stopped working Parenthesis around table names and derived tables should be allowed in FROM clauses and some other context as it was in earlier versions. Returned test queries that used such parenthesis in 10.3 to their original form. Adjusted test results accordingly. --- mysql-test/main/brackets.result | 42 +++++++++++++++++++++ mysql-test/main/brackets.test | 22 +++++++++++ mysql-test/main/subselect.result | 24 +++++++++--- mysql-test/main/subselect.test | 4 -- mysql-test/main/subselect_mat.result | 4 +- mysql-test/main/subselect_no_exists_to_in.result | 24 +++++++++--- mysql-test/main/subselect_no_mat.result | 24 +++++++++--- mysql-test/main/subselect_no_opts.result | 24 +++++++++--- mysql-test/main/subselect_no_scache.result | 24 +++++++++--- mysql-test/main/subselect_no_semijoin.result | 24 +++++++++--- mysql-test/main/subselect_sj.result | 2 +- mysql-test/main/subselect_sj.test | 2 +- mysql-test/main/subselect_sj_jcl6.result | 2 +- mysql-test/main/subselect_sj_mat.result | 4 +- mysql-test/main/subselect_sj_mat.test | 4 +- mysql-test/main/union.result | 6 +-- mysql-test/main/union.test | 6 +-- mysql-test/main/view.result | 48 ++++++++++++------------ mysql-test/main/view.test | 48 ++++++++++++------------ mysql-test/suite/innodb/r/innodb.result | 2 +- mysql-test/suite/innodb/t/innodb.test | 2 +- sql/sql_yacc.yy | 17 +++++++-- sql/sql_yacc_ora.yy | 17 +++++++-- 23 files changed, 265 insertions(+), 111 deletions(-) diff --git a/mysql-test/main/brackets.result b/mysql-test/main/brackets.result index 869afe5..e14bef9 100644 --- a/mysql-test/main/brackets.result +++ b/mysql-test/main/brackets.result @@ -452,4 +452,46 @@ EXPLAIN } } drop table t1; +# +# MDEV-18689: parenthesis around table names and derived tables +# +select * from ( mysql.db ); +Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv Delete_history_priv +% test Y Y Y Y Y Y N Y Y Y Y Y Y Y Y N N Y Y Y +% test\_% Y Y Y Y Y Y N Y Y Y Y Y Y Y Y N N Y Y Y +create table t1 (a int); +insert into t1 values (7), (2), (7); +select * from (t1); +a +7 +2 +7 +select * from ((t1)); +a +7 +2 +7 +select * from (t1 t) where t.a > 5; +a +7 +7 +select * from ((t1 t)) where t.a > 5; +a +7 +7 +select * from ((select a, sum(a) from t1 group by a) t); +a sum(a) +2 2 +7 14 +select * from (((select a, sum(a) from t1 group by a) t)); +a sum(a) +2 2 +7 14 +update (t1 t) set t.a=t.a+1; +select * from t1; +a +8 +3 +8 +drop table t1; # End of 10.4 tests diff --git a/mysql-test/main/brackets.test b/mysql-test/main/brackets.test index cf1dcc5..9ca86b8 100644 --- a/mysql-test/main/brackets.test +++ b/mysql-test/main/brackets.test @@ -154,5 +154,27 @@ eval explain format=json $q; drop table t1; +--echo # +--echo # MDEV-18689: parenthesis around table names and derived tables +--echo # + +select * from ( mysql.db ); + +create table t1 (a int); +insert into t1 values (7), (2), (7); + +select * from (t1); +select * from ((t1)); +select * from (t1 t) where t.a > 5; +select * from ((t1 t)) where t.a > 5; + +select * from ((select a, sum(a) from t1 group by a) t); +select * from (((select a, sum(a) from t1 group by a) t)); + +update (t1 t) set t.a=t.a+1; +select * from t1; + +drop table t1; + --echo # End of 10.4 tests diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index 3bd23a4..8c8c034 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -5198,17 +5198,29 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1)) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM t1 JOIN ((t1 t1a)) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM (t1 t1a); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 +a +1 +2 SELECT * FROM ((t1 t1a)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 1 +a +1 +2 SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1; a t1a 1 1 diff --git a/mysql-test/main/subselect.test b/mysql-test/main/subselect.test index dd80b78..82823b4 100644 --- a/mysql-test/main/subselect.test +++ b/mysql-test/main/subselect.test @@ -4325,14 +4325,10 @@ SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; --error ER_PARSE_ERROR SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; ---error ER_PARSE_ERROR SELECT * FROM t1 JOIN (t1 t1a) ON 1; ---error ER_PARSE_ERROR SELECT * FROM t1 JOIN ((t1 t1a)) ON 1; ---error ER_PARSE_ERROR SELECT * FROM (t1 t1a); ---error ER_PARSE_ERROR SELECT * FROM ((t1 t1a)); SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1; diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result index 482833d..8ffe664 100644 --- a/mysql-test/main/subselect_mat.result +++ b/mysql-test/main/subselect_mat.result @@ -2212,11 +2212,11 @@ drop database mysqltest4; # (both 1st and further executions) CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM; INSERT INTO t1 VALUES (0),(8); -SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM t1 AS t2); +SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM (t1 AS t2)); a 0 PREPARE stmt FROM " -SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM t1 AS t2) +SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM (t1 AS t2)) "; execute stmt; a diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result index bacba84..1bcc1a5 100644 --- a/mysql-test/main/subselect_no_exists_to_in.result +++ b/mysql-test/main/subselect_no_exists_to_in.result @@ -5200,17 +5200,29 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1)) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM t1 JOIN ((t1 t1a)) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM (t1 t1a); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 +a +1 +2 SELECT * FROM ((t1 t1a)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 1 +a +1 +2 SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1; a t1a 1 1 diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index a5b1d95..2876e19 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -5198,17 +5198,29 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1)) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM t1 JOIN ((t1 t1a)) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM (t1 t1a); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 +a +1 +2 SELECT * FROM ((t1 t1a)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 1 +a +1 +2 SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1; a t1a 1 1 diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result index 0ea16d8..47f554a 100644 --- a/mysql-test/main/subselect_no_opts.result +++ b/mysql-test/main/subselect_no_opts.result @@ -5194,17 +5194,29 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1)) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM t1 JOIN ((t1 t1a)) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM (t1 t1a); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 +a +1 +2 SELECT * FROM ((t1 t1a)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 1 +a +1 +2 SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1; a t1a 1 1 diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index 196af2d..7096878 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -5204,17 +5204,29 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1)) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM t1 JOIN ((t1 t1a)) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM (t1 t1a); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 +a +1 +2 SELECT * FROM ((t1 t1a)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 1 +a +1 +2 SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1; a t1a 1 1 diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result index c590a5d..3265a4f 100644 --- a/mysql-test/main/subselect_no_semijoin.result +++ b/mysql-test/main/subselect_no_semijoin.result @@ -5194,17 +5194,29 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1)) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM t1 JOIN ((t1 t1a)) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM (t1 t1a); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 +a +1 +2 SELECT * FROM ((t1 t1a)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 1 +a +1 +2 SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1; a t1a 1 1 diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result index 2907536..a4dd660 100644 --- a/mysql-test/main/subselect_sj.result +++ b/mysql-test/main/subselect_sj.result @@ -1675,7 +1675,7 @@ CREATE TABLE t3 ( f11 int) ; INSERT IGNORE INTO t3 VALUES (0); SELECT alias1.f11 AS field2 FROM ( t3 AS alias2 JOIN t1 AS alias3 ON alias3.f10 = 1) -LEFT JOIN t2 AS alias1 ON alias3.f11 = 1 +LEFT JOIN ( t2 AS alias1 ) ON alias3.f11 = 1 WHERE alias2.f11 IN ( SELECT f11 FROM t2 ) GROUP BY field2 ; field2 diff --git a/mysql-test/main/subselect_sj.test b/mysql-test/main/subselect_sj.test index 22c9b2b..d735d85 100644 --- a/mysql-test/main/subselect_sj.test +++ b/mysql-test/main/subselect_sj.test @@ -1462,7 +1462,7 @@ INSERT IGNORE INTO t3 VALUES (0); SELECT alias1.f11 AS field2 FROM ( t3 AS alias2 JOIN t1 AS alias3 ON alias3.f10 = 1) -LEFT JOIN t2 AS alias1 ON alias3.f11 = 1 +LEFT JOIN ( t2 AS alias1 ) ON alias3.f11 = 1 WHERE alias2.f11 IN ( SELECT f11 FROM t2 ) GROUP BY field2 ; diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result index 697a2ae..36cca52 100644 --- a/mysql-test/main/subselect_sj_jcl6.result +++ b/mysql-test/main/subselect_sj_jcl6.result @@ -1688,7 +1688,7 @@ CREATE TABLE t3 ( f11 int) ; INSERT IGNORE INTO t3 VALUES (0); SELECT alias1.f11 AS field2 FROM ( t3 AS alias2 JOIN t1 AS alias3 ON alias3.f10 = 1) -LEFT JOIN t2 AS alias1 ON alias3.f11 = 1 +LEFT JOIN ( t2 AS alias1 ) ON alias3.f11 = 1 WHERE alias2.f11 IN ( SELECT f11 FROM t2 ) GROUP BY field2 ; field2 diff --git a/mysql-test/main/subselect_sj_mat.result b/mysql-test/main/subselect_sj_mat.result index 3fc8f9a..e28a1c5 100644 --- a/mysql-test/main/subselect_sj_mat.result +++ b/mysql-test/main/subselect_sj_mat.result @@ -2252,11 +2252,11 @@ drop database mysqltest4; # (both 1st and further executions) CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM; INSERT INTO t1 VALUES (0),(8); -SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM t1 AS t2); +SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM (t1 AS t2)); a 0 PREPARE stmt FROM " -SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM t1 AS t2) +SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM (t1 AS t2)) "; execute stmt; a diff --git a/mysql-test/main/subselect_sj_mat.test b/mysql-test/main/subselect_sj_mat.test index ac0baee..c66ca57 100644 --- a/mysql-test/main/subselect_sj_mat.test +++ b/mysql-test/main/subselect_sj_mat.test @@ -1859,9 +1859,9 @@ drop database mysqltest4; CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM; INSERT INTO t1 VALUES (0),(8); -SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM t1 AS t2); +SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM (t1 AS t2)); PREPARE stmt FROM " -SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM t1 AS t2) +SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM (t1 AS t2)) "; execute stmt; execute stmt; diff --git a/mysql-test/main/union.result b/mysql-test/main/union.result index a0421ba..559eafc 100644 --- a/mysql-test/main/union.result +++ b/mysql-test/main/union.result @@ -2034,14 +2034,14 @@ SET @@global.slow_query_log= @old_slow_query_log; CREATE TABLE t1 (a int); CREATE TABLE t2 (b int); CREATE TABLE t3 (c int); -SELECT a FROM t1 UNION SELECT b FROM t2 JOIN t3 ON ( t2.b = t3.c ); +SELECT a FROM t1 UNION SELECT b FROM t2 JOIN (t3) ON ( t2.b = t3.c ); a DROP TABLE t1, t2, t3; CREATE TABLE t1 (pk int NOT NULL); CREATE TABLE t2 (pk int NOT NULL, fk int NOT NULL); -SELECT t1.pk FROM t1 LEFT JOIN t2 ON (t1.pk = t2.fk) +SELECT t1.pk FROM t1 LEFT JOIN (t2) ON (t1.pk = t2.fk) UNION -SELECT t1.pk FROM t1 LEFT JOIN t2 ON (t1.pk = t2.fk); +SELECT t1.pk FROM t1 LEFT JOIN (t2) ON (t1.pk = t2.fk); pk DROP TABLE t1,t2; create table t1 (a int); diff --git a/mysql-test/main/union.test b/mysql-test/main/union.test index 9d25708..ce52eba 100644 --- a/mysql-test/main/union.test +++ b/mysql-test/main/union.test @@ -1371,15 +1371,15 @@ SET @@global.slow_query_log= @old_slow_query_log; CREATE TABLE t1 (a int); CREATE TABLE t2 (b int); CREATE TABLE t3 (c int); -SELECT a FROM t1 UNION SELECT b FROM t2 JOIN t3 ON ( t2.b = t3.c ); +SELECT a FROM t1 UNION SELECT b FROM t2 JOIN (t3) ON ( t2.b = t3.c ); DROP TABLE t1, t2, t3; CREATE TABLE t1 (pk int NOT NULL); CREATE TABLE t2 (pk int NOT NULL, fk int NOT NULL); -SELECT t1.pk FROM t1 LEFT JOIN t2 ON (t1.pk = t2.fk) +SELECT t1.pk FROM t1 LEFT JOIN (t2) ON (t1.pk = t2.fk) UNION -SELECT t1.pk FROM t1 LEFT JOIN t2 ON (t1.pk = t2.fk); +SELECT t1.pk FROM t1 LEFT JOIN (t2) ON (t1.pk = t2.fk); DROP TABLE t1,t2; diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index 83ea0e3..fdaf0ba 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -4104,7 +4104,7 @@ LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 ; SELECT 1 -FROM ( SELECT 1 +FROM (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4112,8 +4112,8 @@ LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t1 -LEFT OUTER JOIN ( SELECT 1 +) t1) +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4121,8 +4121,8 @@ LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t2 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t2) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4130,8 +4130,8 @@ LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t3 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t3) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4139,8 +4139,8 @@ LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t4 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t4) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4148,8 +4148,8 @@ LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t5 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t5) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4157,8 +4157,8 @@ LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t6 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t6) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4166,8 +4166,8 @@ LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t7 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t7) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4175,18 +4175,18 @@ LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t8 ON 1=1 +) t8) ON 1=1 ; 1 SELECT 1 -FROM v1 t1 -LEFT OUTER JOIN v1 t2 ON 1=1 -LEFT OUTER JOIN v1 t3 ON 1=1 -LEFT OUTER JOIN v1 t4 ON 1=1 -LEFT OUTER JOIN v1 t5 ON 1=1 -LEFT OUTER JOIN v1 t6 ON 1=1 -LEFT OUTER JOIN v1 t7 ON 1=1 -LEFT OUTER JOIN v1 t8 ON 1=1 +FROM (v1 t1) +LEFT OUTER JOIN (v1 t2) ON 1=1 +LEFT OUTER JOIN (v1 t3) ON 1=1 +LEFT OUTER JOIN (v1 t4) ON 1=1 +LEFT OUTER JOIN (v1 t5) ON 1=1 +LEFT OUTER JOIN (v1 t6) ON 1=1 +LEFT OUTER JOIN (v1 t7) ON 1=1 +LEFT OUTER JOIN (v1 t8) ON 1=1 ; 1 drop view v1; diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test index 2b7b9d1..ce8ac8d 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -4050,7 +4050,7 @@ CREATE OR REPLACE view v1 AS ; SELECT 1 -FROM ( SELECT 1 +FROM (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4058,8 +4058,8 @@ FROM ( SELECT 1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t1 -LEFT OUTER JOIN ( SELECT 1 +) t1) +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4067,8 +4067,8 @@ LEFT OUTER JOIN ( SELECT 1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t2 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t2) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4076,8 +4076,8 @@ LEFT OUTER JOIN ( SELECT 1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t3 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t3) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4085,8 +4085,8 @@ LEFT OUTER JOIN ( SELECT 1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t4 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t4) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4094,8 +4094,8 @@ LEFT OUTER JOIN ( SELECT 1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t5 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t5) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4103,8 +4103,8 @@ LEFT OUTER JOIN ( SELECT 1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t6 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t6) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4112,8 +4112,8 @@ LEFT OUTER JOIN ( SELECT 1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t7 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t7) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4121,18 +4121,18 @@ LEFT OUTER JOIN ( SELECT 1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t8 ON 1=1 +) t8) ON 1=1 ; SELECT 1 -FROM v1 t1 -LEFT OUTER JOIN v1 t2 ON 1=1 -LEFT OUTER JOIN v1 t3 ON 1=1 -LEFT OUTER JOIN v1 t4 ON 1=1 -LEFT OUTER JOIN v1 t5 ON 1=1 -LEFT OUTER JOIN v1 t6 ON 1=1 -LEFT OUTER JOIN v1 t7 ON 1=1 -LEFT OUTER JOIN v1 t8 ON 1=1 +FROM (v1 t1) +LEFT OUTER JOIN (v1 t2) ON 1=1 +LEFT OUTER JOIN (v1 t3) ON 1=1 +LEFT OUTER JOIN (v1 t4) ON 1=1 +LEFT OUTER JOIN (v1 t5) ON 1=1 +LEFT OUTER JOIN (v1 t6) ON 1=1 +LEFT OUTER JOIN (v1 t7) ON 1=1 +LEFT OUTER JOIN (v1 t8) ON 1=1 ; drop view v1; diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result index 3913004..e2937fe 100644 --- a/mysql-test/suite/innodb/r/innodb.result +++ b/mysql-test/suite/innodb/r/innodb.result @@ -1609,7 +1609,7 @@ INSERT INTO t1 VALUES (1),(2),(3); CREATE TABLE t2 (b_id tinyint(4) NOT NULL default '0',b_a tinyint(4) NOT NULL default '0', PRIMARY KEY (b_id), KEY (b_a), CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2); -SELECT * FROM (SELECT t1.*,GROUP_CONCAT(t2.b_id SEPARATOR ',') as b_list FROM (t1 LEFT JOIN t2 on t1.a_id = t2.b_a) GROUP BY t1.a_id ) AS xyz; +SELECT * FROM (SELECT t1.*,GROUP_CONCAT(t2.b_id SEPARATOR ',') as b_list FROM (t1 LEFT JOIN (t2) on t1.a_id = t2.b_a) GROUP BY t1.a_id ) AS xyz; a_id b_list 1 1,2,3 2 4,5 diff --git a/mysql-test/suite/innodb/t/innodb.test b/mysql-test/suite/innodb/t/innodb.test index 0062ba7..ab12cac 100644 --- a/mysql-test/suite/innodb/t/innodb.test +++ b/mysql-test/suite/innodb/t/innodb.test @@ -1253,7 +1253,7 @@ CREATE TABLE t2 (b_id tinyint(4) NOT NULL default '0',b_a tinyint(4) NOT NULL de CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=latin1; --enable_warnings INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2); -SELECT * FROM (SELECT t1.*,GROUP_CONCAT(t2.b_id SEPARATOR ',') as b_list FROM (t1 LEFT JOIN t2 on t1.a_id = t2.b_a) GROUP BY t1.a_id ) AS xyz; +SELECT * FROM (SELECT t1.*,GROUP_CONCAT(t2.b_id SEPARATOR ',') as b_list FROM (t1 LEFT JOIN (t2) on t1.a_id = t2.b_a) GROUP BY t1.a_id ) AS xyz; DROP TABLE t2; DROP TABLE t1; diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index d09aa85..23faa57 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -1932,7 +1932,8 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %type <table_list> join_table_list join_table table_factor table_ref esc_table_ref - table_primary_ident table_primary_derived + table_primary_ident table_primary_ident_opt_parens + table_primary_derived table_primary_derived_opt_parens derived_table_list table_reference_list_parens nested_table_reference_list join_table_parens update_table_list @@ -12059,12 +12060,22 @@ use_partition: ; table_factor: - table_primary_ident { $$= $1; } - | table_primary_derived { $$= $1; } + table_primary_ident_opt_parens { $$= $1; } + | table_primary_derived_opt_parens { $$= $1; } | join_table_parens { $$= $1; } | table_reference_list_parens { $$= $1; } ; +table_primary_ident_opt_parens: + table_primary_ident { $$= $1; } + | '(' table_primary_ident_opt_parens ')' { $$= $2; } + ; + +table_primary_derived_opt_parens: + table_primary_derived { $$= $1; } + | '(' table_primary_derived_opt_parens ')' { $$= $2; } + ; + table_reference_list_parens: '(' table_reference_list_parens ')' { $$= $2; } | '(' nested_table_reference_list ')' diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index 333bca4..88b40b9 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -1433,7 +1433,8 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %type <table_list> join_table_list join_table table_factor table_ref esc_table_ref - table_primary_ident table_primary_derived + table_primary_ident table_primary_ident_opt_parens + table_primary_derived table_primary_derived_opt_parens derived_table_list table_reference_list_parens nested_table_reference_list join_table_parens update_table_list @@ -12181,12 +12182,22 @@ use_partition: ; table_factor: - table_primary_ident { $$= $1; } - | table_primary_derived { $$= $1; } + table_primary_ident_opt_parens { $$= $1; } + | table_primary_derived_opt_parens { $$= $1; } | join_table_parens { $$= $1; } | table_reference_list_parens { $$= $1; } ; +table_primary_ident_opt_parens: + table_primary_ident { $$= $1; } + | '(' table_primary_ident_opt_parens ')' { $$= $2; } + ; + +table_primary_derived_opt_parens: + table_primary_derived { $$= $1; } + | '(' table_primary_derived_opt_parens ')' { $$= $2; } + ; + table_reference_list_parens: '(' table_reference_list_parens ')' { $$= $2; } | '(' nested_table_reference_list ')'

1 0

[Commits] 5496df8e5da: changes in innodb has influence on this test in rocksdb test suite
by Oleksandr Byelkin 06 May '19

06 May '19

revision-id: 5496df8e5daabcbd3eca8d96a3d6caa450913acc (mariadb-10.2.23-118-g5496df8e5da) parent(s): 633946fb63f6957b49554ce6f6d33862951d810e author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-05-06 19:34:06 +0200 message: changes in innodb has influence on this test in rocksdb test suite --- .../rocksdb/mysql-test/rocksdb/r/innodb_i_s_tables_disabled.result | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) diff --git a/storage/rocksdb/mysql-test/rocksdb/r/innodb_i_s_tables_disabled.result b/storage/rocksdb/mysql-test/rocksdb/r/innodb_i_s_tables_disabled.result index c49dbba751a..55599a1268a 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/innodb_i_s_tables_disabled.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/innodb_i_s_tables_disabled.result @@ -186,9 +186,9 @@ log_lsn_checkpoint_age recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL log_lsn_buf_pool_oldest recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value The oldest modified block LSN in the buffer pool log_max_modified_age_async recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Maximum LSN difference; when exceeded, start asynchronous preflush log_max_modified_age_sync recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Maximum LSN difference; when exceeded, start synchronous preflush -log_pending_log_flushes recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Pending log flushes -log_pending_checkpoint_writes recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Pending checkpoints -log_num_log_io recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of log I/Os +log_pending_log_flushes recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Pending log flushes +log_pending_checkpoint_writes recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Pending checkpoints +log_num_log_io recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Number of log I/Os log_waits recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of log waits due to small log buffer (innodb_log_waits) log_write_requests recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of log write requests (innodb_log_write_requests) log_writes recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of log writes (innodb_log_writes)

1 0

[Commits] 633946fb63f: Merge branch '10.1' into 10.2
by Oleksandr Byelkin 06 May '19

06 May '19

revision-id: 633946fb63f6957b49554ce6f6d33862951d810e (mariadb-10.2.23-117-g633946fb63f) parent(s): 54d0a55adf6fbfc92c5473bbcad2b459f14ef038 0573744a83ad89958c4e48a9299cd9274db1b355 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-05-06 18:07:40 +0200 message: Merge branch '10.1' into 10.2 .../innodb/r/innodb_skip_innodb_is_tables.result | 6 ++--- sql/sql_base.cc | 9 +++---- storage/innobase/log/log0log.cc | 6 ----- storage/innobase/srv/srv0mon.cc | 28 +++++++++++++++++++--- storage/xtradb/log/log0log.cc | 21 ---------------- storage/xtradb/srv/srv0mon.cc | 28 +++++++++++++++++++--- support-files/rpm/server-postin.sh | 3 +++ 7 files changed, 61 insertions(+), 40 deletions(-) diff --cc mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result index 5bac43badb3,65e063979c4..6ce63a5744b --- a/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result +++ b/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result @@@ -204,9 -193,9 +204,9 @@@ log_lsn_checkpoint_age recovery 0 NULL log_lsn_buf_pool_oldest recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value The oldest modified block LSN in the buffer pool log_max_modified_age_async recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Maximum LSN difference; when exceeded, start asynchronous preflush log_max_modified_age_sync recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Maximum LSN difference; when exceeded, start synchronous preflush - log_pending_log_flushes recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Pending log flushes - log_pending_checkpoint_writes recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Pending checkpoints - log_num_log_io recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of log I/Os -log_pending_log_writes recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Pending log writes ++log_pending_log_flushes recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Pending log flushes + log_pending_checkpoint_writes recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Pending checkpoints + log_num_log_io recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Number of log I/Os log_waits recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of log waits due to small log buffer (innodb_log_waits) log_write_requests recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of log write requests (innodb_log_write_requests) log_writes recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of log writes (innodb_log_writes) diff --cc storage/innobase/log/log0log.cc index da301a3b4f1,234fabcd860..316e337ec4e --- a/storage/innobase/log/log0log.cc +++ b/storage/innobase/log/log0log.cc @@@ -800,105 -977,284 +800,103 @@@ log_init(ulint n_files group->state = LOG_GROUP_OK; group->lsn = LOG_START_LSN; group->lsn_offset = LOG_FILE_HDR_SIZE; - group->n_pending_writes = 0; - - group->file_header_bufs_ptr = static_cast<byte**>( - mem_zalloc(sizeof(byte*) * n_files)); - group->file_header_bufs = static_cast<byte**>( - mem_zalloc(sizeof(byte**) * n_files)); + group->checkpoint_buf_ptr = static_cast<byte*>( + ut_zalloc_nokey(2 * OS_FILE_LOG_BLOCK_SIZE)); -#ifdef UNIV_LOG_ARCHIVE - group->archive_file_header_bufs_ptr = static_cast<byte*>( - mem_zalloc( sizeof(byte*) * n_files)); + group->checkpoint_buf = static_cast<byte*>( + ut_align(group->checkpoint_buf_ptr,OS_FILE_LOG_BLOCK_SIZE)); +} - group->archive_file_header_bufs = static_cast<byte*>( - mem_zalloc(sizeof(byte*) * n_files)); -#endif /* UNIV_LOG_ARCHIVE */ +/******************************************************//** +Completes an i/o to a log file. */ +void +log_io_complete( +/*============*/ + log_group_t* group) /*!< in: log group or a dummy pointer */ +{ + if ((ulint) group & 0x1UL) { + /* It was a checkpoint write */ + group = (log_group_t*)((ulint) group - 1); - for (i = 0; i < n_files; i++) { - group->file_header_bufs_ptr[i] = static_cast<byte*>( - mem_zalloc(LOG_FILE_HDR_SIZE + OS_FILE_LOG_BLOCK_SIZE)); + switch (srv_file_flush_method) { + case SRV_O_DSYNC: + case SRV_NOSYNC: + break; + case SRV_FSYNC: + case SRV_LITTLESYNC: + case SRV_O_DIRECT: + case SRV_O_DIRECT_NO_FSYNC: + case SRV_ALL_O_DIRECT_FSYNC: + fil_flush(SRV_LOG_SPACE_FIRST_ID); + } - group->file_header_bufs[i] = static_cast<byte*>( - ut_align(group->file_header_bufs_ptr[i], - OS_FILE_LOG_BLOCK_SIZE)); -#ifdef UNIV_LOG_ARCHIVE - group->archive_file_header_bufs_ptr[i] = static_cast<byte*>( - mem_zalloc(LOG_FILE_HDR_SIZE + OS_FILE_LOG_BLOCK_SIZE)); + DBUG_PRINT("ib_log", ("checkpoint info written")); + log_io_complete_checkpoint(); - group->archive_file_header_bufs[i] = static_cast<byte*>( - ut_align(group->archive_file_header_bufs_ptr[i], - OS_FILE_LOG_BLOCK_SIZE)); -#endif /* UNIV_LOG_ARCHIVE */ + return; } -#ifdef UNIV_LOG_ARCHIVE - group->archive_space_id = archive_space_id; - - group->archived_file_no = 0; - group->archived_offset = 0; -#endif /* UNIV_LOG_ARCHIVE */ - - group->checkpoint_buf_ptr = static_cast<byte*>( - mem_zalloc(2 * OS_FILE_LOG_BLOCK_SIZE)); - - group->checkpoint_buf = static_cast<byte*>( - ut_align(group->checkpoint_buf_ptr,OS_FILE_LOG_BLOCK_SIZE)); - - UT_LIST_ADD_LAST(log_groups, log_sys->log_groups, group); - - ut_a(log_calc_max_ages()); + ut_error; /*!< We currently use synchronous writing of the + logs and cannot end up here! */ } -/******************************************************************//** -Does the unlockings needed in flush i/o completion. */ -UNIV_INLINE +/******************************************************//** +Writes a log file header to a log file space. */ +static void -log_flush_do_unlocks( -/*=================*/ - ulint code) /*!< in: any ORed combination of LOG_UNLOCK_FLUSH_LOCK - and LOG_UNLOCK_NONE_FLUSHED_LOCK */ +log_group_file_header_flush( +/*========================*/ + log_group_t* group, /*!< in: log group */ + ulint nth_file, /*!< in: header to the nth file in the + log file space */ + lsn_t start_lsn) /*!< in: log file data starts at this + lsn */ { - ut_ad(mutex_own(&(log_sys->mutex))); + lsn_t dest_offset; - /* NOTE that we must own the log mutex when doing the setting of the - events: this is because transactions will wait for these events to - be set, and at that moment the log flush they were waiting for must - have ended. If the log mutex were not reserved here, the i/o-thread - calling this function might be preempted for a while, and when it - resumed execution, it might be that a new flush had been started, and - this function would erroneously signal the NEW flush as completed. - Thus, the changes in the state of these events are performed - atomically in conjunction with the changes in the state of - log_sys->n_pending_writes etc. */ + ut_ad(log_write_mutex_own()); + ut_ad(!recv_no_log_write); + ut_a(nth_file < group->n_files); + ut_ad((group->format & ~LOG_HEADER_FORMAT_ENCRYPTED) + == (srv_safe_truncate + ? LOG_HEADER_FORMAT_10_3 + : LOG_HEADER_FORMAT_10_2)); + + // man 2 open suggests this buffer to be aligned by 512 for O_DIRECT + MY_ALIGNED(OS_FILE_LOG_BLOCK_SIZE) + byte buf[OS_FILE_LOG_BLOCK_SIZE] = {0}; + + mach_write_to_4(buf + LOG_HEADER_FORMAT, group->format); + mach_write_to_4(buf + LOG_HEADER_SUBFORMAT, srv_safe_truncate); + mach_write_to_8(buf + LOG_HEADER_START_LSN, start_lsn); + strcpy(reinterpret_cast<char*>(buf) + LOG_HEADER_CREATOR, + LOG_HEADER_CREATOR_CURRENT); + ut_ad(LOG_HEADER_CREATOR_END - LOG_HEADER_CREATOR + >= sizeof LOG_HEADER_CREATOR_CURRENT); + log_block_set_checksum(buf, log_block_calc_checksum_crc32(buf)); - if (code & LOG_UNLOCK_NONE_FLUSHED_LOCK) { - os_event_set(log_sys->one_flushed_event); - } + dest_offset = nth_file * group->file_size; - if (code & LOG_UNLOCK_FLUSH_LOCK) { - os_event_set(log_sys->no_flush_event); - } -} + DBUG_PRINT("ib_log", ("write " LSN_PF + " file " ULINTPF " header", + start_lsn, nth_file)); -/******************************************************************//** -Checks if a flush is completed for a log group and does the completion -routine if yes. -@return LOG_UNLOCK_NONE_FLUSHED_LOCK or 0 */ -UNIV_INLINE -ulint -log_group_check_flush_completion( -/*=============================*/ - log_group_t* group) /*!< in: log group */ -{ - ut_ad(mutex_own(&(log_sys->mutex))); + log_sys->n_log_ios++; - MONITOR_INC(MONITOR_LOG_IO); - - if (!log_sys->one_flushed && group->n_pending_writes == 0) { -#ifdef UNIV_DEBUG - if (log_debug_writes) { - fprintf(stderr, - "Log flushed first to group %lu\n", - (ulong) group->id); - } -#endif /* UNIV_DEBUG */ - log_sys->written_to_some_lsn = log_sys->write_lsn; - log_sys->one_flushed = TRUE; + srv_stats.os_log_pending_writes.inc(); - return(LOG_UNLOCK_NONE_FLUSHED_LOCK); - } + const ulint page_no + = (ulint) (dest_offset / univ_page_size.physical()); -#ifdef UNIV_DEBUG - if (log_debug_writes && (group->n_pending_writes == 0)) { + fil_io(IORequestLogWrite, true, + page_id_t(SRV_LOG_SPACE_FIRST_ID, page_no), + univ_page_size, + (ulint) (dest_offset % univ_page_size.physical()), + OS_FILE_LOG_BLOCK_SIZE, buf, group); - fprintf(stderr, "Log flushed to group %lu\n", - (ulong) group->id); - } -#endif /* UNIV_DEBUG */ - return(0); -} - -/******************************************************//** -Checks if a flush is completed and does the completion routine if yes. -@return LOG_UNLOCK_FLUSH_LOCK or 0 */ -static -ulint -log_sys_check_flush_completion(void) -/*================================*/ -{ - ulint move_start; - ulint move_end; - - ut_ad(mutex_own(&(log_sys->mutex))); - - if (log_sys->n_pending_writes == 0) { - - log_sys->written_to_all_lsn = log_sys->write_lsn; - log_sys->buf_next_to_write = log_sys->write_end_offset; - - if (log_sys->write_end_offset > log_sys->max_buf_free / 2) { - /* Move the log buffer content to the start of the - buffer */ - - move_start = ut_calc_align_down( - log_sys->write_end_offset, - OS_FILE_LOG_BLOCK_SIZE); - move_end = ut_calc_align(log_sys->buf_free, - OS_FILE_LOG_BLOCK_SIZE); - - ut_memmove(log_sys->buf, log_sys->buf + move_start, - move_end - move_start); - log_sys->buf_free -= move_start; - - log_sys->buf_next_to_write -= move_start; - } - - return(LOG_UNLOCK_FLUSH_LOCK); - } - - return(0); -} - -/******************************************************//** -Completes an i/o to a log file. */ -UNIV_INTERN -void -log_io_complete( -/*============*/ - log_group_t* group) /*!< in: log group or a dummy pointer */ -{ - ulint unlock; - -#ifdef UNIV_LOG_ARCHIVE - if ((byte*) group == &log_archive_io) { - /* It was an archive write */ - - log_io_complete_archive(); - - return; - } -#endif /* UNIV_LOG_ARCHIVE */ - - if ((ulint) group & 0x1UL) { - /* It was a checkpoint write */ - group = (log_group_t*)((ulint) group - 1); - - if (srv_unix_file_flush_method != SRV_UNIX_O_DSYNC - && srv_unix_file_flush_method != SRV_UNIX_NOSYNC) { - - fil_flush(group->space_id); - } - -#ifdef UNIV_DEBUG - if (log_debug_writes) { - fprintf(stderr, - "Checkpoint info written to group %lu\n", - group->id); - } -#endif /* UNIV_DEBUG */ - log_io_complete_checkpoint(); - - return; - } - - ut_error; /*!< We currently use synchronous writing of the - logs and cannot end up here! */ - - if (srv_unix_file_flush_method != SRV_UNIX_O_DSYNC - && srv_unix_file_flush_method != SRV_UNIX_NOSYNC - && srv_flush_log_at_trx_commit != 2) { - - fil_flush(group->space_id); - } - - mutex_enter(&(log_sys->mutex)); - ut_ad(!recv_no_log_write); - - ut_a(group->n_pending_writes > 0); - ut_a(log_sys->n_pending_writes > 0); - - group->n_pending_writes--; - log_sys->n_pending_writes--; - - unlock = log_group_check_flush_completion(group); - unlock = unlock | log_sys_check_flush_completion(); - - log_flush_do_unlocks(unlock); - - mutex_exit(&(log_sys->mutex)); -} - -/******************************************************//** -Writes a log file header to a log file space. */ -static -void -log_group_file_header_flush( -/*========================*/ - log_group_t* group, /*!< in: log group */ - ulint nth_file, /*!< in: header to the nth file in the - log file space */ - lsn_t start_lsn) /*!< in: log file data starts at this - lsn */ -{ - byte* buf; - lsn_t dest_offset; - - ut_ad(mutex_own(&(log_sys->mutex))); - ut_ad(!recv_no_log_write); - ut_a(nth_file < group->n_files); - - buf = *(group->file_header_bufs + nth_file); - - mach_write_to_4(buf + LOG_GROUP_ID, group->id); - mach_write_to_8(buf + LOG_FILE_START_LSN, start_lsn); - - /* Wipe over possible label of mysqlbackup --restore */ - memcpy(buf + LOG_FILE_WAS_CREATED_BY_HOT_BACKUP, " ", 4); - - dest_offset = nth_file * group->file_size; - -#ifdef UNIV_DEBUG - if (log_debug_writes) { - fprintf(stderr, - "Writing log file header to group %lu file %lu\n", - (ulong) group->id, (ulong) nth_file); - } -#endif /* UNIV_DEBUG */ - if (log_do_write) { - log_sys->n_log_ios++; - - srv_stats.os_log_pending_writes.inc(); - - fil_io(OS_FILE_WRITE | OS_FILE_LOG, true, group->space_id, 0, - (ulint) (dest_offset / UNIV_PAGE_SIZE), - (ulint) (dest_offset % UNIV_PAGE_SIZE), - OS_FILE_LOG_BLOCK_SIZE, - buf, group, 0); - - srv_stats.os_log_pending_writes.dec(); - } + srv_stats.os_log_pending_writes.dec(); } /******************************************************//** @@@ -1004,27 -1367,26 +1002,25 @@@ loop log_block_store_checksum(buf + i * OS_FILE_LOG_BLOCK_SIZE); } - if (log_do_write) { - log_sys->n_log_ios++; + log_sys->n_log_ios++; - MONITOR_INC(MONITOR_LOG_IO); - - srv_stats.os_log_pending_writes.inc(); + srv_stats.os_log_pending_writes.inc(); - ut_a(next_offset / UNIV_PAGE_SIZE <= ULINT_MAX); + ut_a(next_offset / UNIV_PAGE_SIZE <= ULINT_MAX); - log_encrypt_before_write(log_sys->next_checkpoint_no, - buf, start_lsn, write_len); + const ulint page_no + = (ulint) (next_offset / univ_page_size.physical()); - fil_io(OS_FILE_WRITE | OS_FILE_LOG, true, group->space_id, 0, - (ulint) (next_offset / UNIV_PAGE_SIZE), - (ulint) (next_offset % UNIV_PAGE_SIZE), write_len, buf, - group, 0); + fil_io(IORequestLogWrite, true, + page_id_t(SRV_LOG_SPACE_FIRST_ID, page_no), + univ_page_size, + (ulint) (next_offset % UNIV_PAGE_SIZE), write_len, buf, + group); - srv_stats.os_log_pending_writes.dec(); + srv_stats.os_log_pending_writes.dec(); - srv_stats.os_log_written.add(write_len); - srv_stats.log_writes.inc(); - } + srv_stats.os_log_written.add(write_len); + srv_stats.log_writes.inc(); if (write_len < len) { start_lsn += write_len; @@@ -1037,81 -1399,29 +1033,80 @@@ } } -/******************************************************//** -This function is called, e.g., when a transaction wants to commit. It checks -that the log has been written to the log file up to the last log entry written -by the transaction. If there is a flush running, it waits and checks if the -flush flushed enough. If not, starts a new flush. */ -UNIV_INTERN +/** Flush the recently written changes to the log file. +and invoke log_mutex_enter(). */ +static +void +log_write_flush_to_disk_low() +{ + /* FIXME: This is not holding log_sys->mutex while + calling os_event_set()! */ + ut_a(log_sys->n_pending_flushes == 1); /* No other threads here */ + + bool do_flush = srv_file_flush_method != SRV_O_DSYNC; + + if (do_flush) { + fil_flush(SRV_LOG_SPACE_FIRST_ID); + } + - MONITOR_DEC(MONITOR_PENDING_LOG_FLUSH); + + log_mutex_enter(); + if (do_flush) { + log_sys->flushed_to_disk_lsn = log_sys->current_flush_lsn; + } + + log_sys->n_pending_flushes--; + + os_event_set(log_sys->flush_event); +} + +/** Switch the log buffer in use, and copy the content of last block +from old log buffer to the head of the to be used one. Thus, buf_free and +buf_next_to_write would be changed accordingly */ +static inline +void +log_buffer_switch() +{ + ut_ad(log_mutex_own()); + ut_ad(log_write_mutex_own()); + + const byte* old_buf = log_sys->buf; + ulint area_end = ut_calc_align( + log_sys->buf_free, ulint(OS_FILE_LOG_BLOCK_SIZE)); + + if (log_sys->first_in_use) { + log_sys->first_in_use = false; + ut_ad(log_sys->buf == ut_align(log_sys->buf_ptr, + OS_FILE_LOG_BLOCK_SIZE)); + log_sys->buf += log_sys->buf_size; + } else { + log_sys->first_in_use = true; + log_sys->buf -= log_sys->buf_size; + ut_ad(log_sys->buf == ut_align(log_sys->buf_ptr, + OS_FILE_LOG_BLOCK_SIZE)); + } + + /* Copy the last block to new buf */ + ut_memcpy(log_sys->buf, + old_buf + area_end - OS_FILE_LOG_BLOCK_SIZE, + OS_FILE_LOG_BLOCK_SIZE); + + log_sys->buf_free %= OS_FILE_LOG_BLOCK_SIZE; + log_sys->buf_next_to_write = log_sys->buf_free; +} + +/** Ensure that the log has been written to the log file up to a given +log entry (such as that of a transaction commit). Start a new write, or +wait and check if an already running write is covering the request. +@param[in] lsn log sequence number that should be +included in the redo log file write +@param[in] flush_to_disk whether the written log should also +be flushed to the file system */ void log_write_up_to( -/*============*/ - lsn_t lsn, /*!< in: log sequence number up to which - the log should be written, - LSN_MAX if not specified */ - ulint wait, /*!< in: LOG_NO_WAIT, LOG_WAIT_ONE_GROUP, - or LOG_WAIT_ALL_GROUPS */ - ibool flush_to_disk) - /*!< in: TRUE if we want the written log - also to be flushed to disk */ + lsn_t lsn, + bool flush_to_disk) { - log_group_t* group; - ulint start_offset; - ulint end_offset; - ulint area_start; - ulint area_end; #ifdef UNIV_DEBUG ulint loop_count = 0; #endif /* UNIV_DEBUG */ @@@ -1184,30 -1500,22 +1179,29 @@@ loop return; } -#ifdef UNIV_DEBUG - if (log_debug_writes) { - fprintf(stderr, - "Writing log from " LSN_PF " up to lsn " LSN_PF "\n", - log_sys->written_to_all_lsn, - log_sys->lsn); - } -#endif /* UNIV_DEBUG */ - log_sys->n_pending_writes++; + ulint start_offset; + ulint end_offset; + ulint area_start; + ulint area_end; + ulong write_ahead_size = srv_log_write_ahead_size; + ulint pad_size; - group = UT_LIST_GET_FIRST(log_sys->log_groups); - group->n_pending_writes++; /*!< We assume here that we have only - one log group! */ + DBUG_PRINT("ib_log", ("write " LSN_PF " to " LSN_PF, + log_sys->write_lsn, + log_sys->lsn)); + if (flush_to_disk) { + log_sys->n_pending_flushes++; + log_sys->current_flush_lsn = log_sys->lsn; - MONITOR_INC(MONITOR_PENDING_LOG_FLUSH); + os_event_reset(log_sys->flush_event); - os_event_reset(log_sys->no_flush_event); - os_event_reset(log_sys->one_flushed_event); + if (log_sys->buf_free == log_sys->buf_next_to_write) { + /* Nothing to write, flush only */ + log_mutex_exit_all(); + log_write_flush_to_disk_low(); + log_mutex_exit(); + return; + } + } start_offset = log_sys->buf_next_to_write; end_offset = log_sys->buf_free; diff --cc storage/innobase/srv/srv0mon.cc index de1c0d27efe,8f5f774df89..ff965989ce4 --- a/storage/innobase/srv/srv0mon.cc +++ b/storage/innobase/srv/srv0mon.cc @@@ -921,12 -872,14 +921,14 @@@ static monitor_info_t innodb_counter_in MONITOR_EXISTING | MONITOR_DISPLAY_CURRENT), MONITOR_DEFAULT_START, MONITOR_OVLD_MAX_AGE_SYNC}, - {"log_pending_log_writes", "recovery", "Pending log writes", + {"log_pending_log_flushes", "recovery", "Pending log flushes", - MONITOR_NONE, + static_cast<monitor_type_t>( + MONITOR_EXISTING | MONITOR_DISPLAY_CURRENT), - MONITOR_DEFAULT_START, MONITOR_PENDING_LOG_WRITE}, + MONITOR_DEFAULT_START, MONITOR_PENDING_LOG_FLUSH}, {"log_pending_checkpoint_writes", "recovery", "Pending checkpoints", - MONITOR_NONE, + static_cast<monitor_type_t>( + MONITOR_EXISTING | MONITOR_DISPLAY_CURRENT), MONITOR_DEFAULT_START, MONITOR_PENDING_CHECKPOINT_WRITE}, {"log_num_log_io", "recovery", "Number of log I/Os", @@@ -2007,6 -1973,25 +2010,25 @@@ srv_mon_process_existing_counter value = (mon_type_t) log_sys->lsn; break; - case MONITOR_PENDING_LOG_WRITE: ++ case MONITOR_PENDING_LOG_FLUSH: + mutex_enter(&log_sys->mutex); - value = static_cast<mon_type_t>(log_sys->n_pending_writes); ++ value = static_cast<mon_type_t>(log_sys->n_pending_flushes); + mutex_exit(&log_sys->mutex); + break; + + case MONITOR_PENDING_CHECKPOINT_WRITE: + mutex_enter(&log_sys->mutex); + value = static_cast<mon_type_t>( + log_sys->n_pending_checkpoint_writes); + mutex_exit(&log_sys->mutex); + break; + + case MONITOR_LOG_IO: + mutex_enter(&log_sys->mutex); + value = static_cast<mon_type_t>(log_sys->n_log_ios); + mutex_exit(&log_sys->mutex); + break; + case MONITOR_OVLD_BUF_OLDEST_LSN: value = (mon_type_t) buf_pool_get_oldest_modification(); break;

1 0

[Commits] 7e6986d917d: Let us close library and only then free defaults.
by Oleksandr Byelkin 06 May '19

06 May '19

revision-id: 7e6986d917d6587fe05b75501fca039e232f2be4 (mariadb-10.2.23-117-g7e6986d917d) parent(s): 54d0a55adf6fbfc92c5473bbcad2b459f14ef038 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-05-06 14:15:58 +0200 message: Let us close library and only then free defaults. --- client/mysqlimport.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/client/mysqlimport.c b/client/mysqlimport.c index 02caf2df198..8f42e6f5a8f 100644 --- a/client/mysqlimport.c +++ b/client/mysqlimport.c @@ -514,11 +514,11 @@ static void safe_exit(int error, MYSQL *mysql) if (mysql) mysql_close(mysql); + mysql_library_end(); #ifdef HAVE_SMEM my_free(shared_memory_base_name); #endif free_defaults(argv_to_free); - mysql_library_end(); my_free(opt_password); if (error) sf_leaking_memory= 1; /* dirty exit, some threads are still running */

1 0

[Commits] 0ff8b73b60f: MDEV-15777:Support Early NULLs filtering-like restrictions in the range optimizer
by varunraiko1803＠gmail.com 06 May '19

06 May '19

revision-id: 0ff8b73b60fae40556f91d3959f20bf3cf182b28 (mariadb-10.3.0-947-g0ff8b73b60f) parent(s): 15419a558370aeed9521b498c34d50f20a8d47a5 author: Varun Gupta committer: Varun Gupta timestamp: 2018-05-15 13:53:11 +0530 message: MDEV-15777:Support Early NULLs filtering-like restrictions in the range optimizer --- mysql-test/main/mdev15777.result | 455 +++++++++++++++++++++++++++++++++++++++ mysql-test/main/mdev15777.test | 138 ++++++++++++ sql/opt_range.cc | 125 ++++++++++- sql/opt_range.h | 2 + sql/sql_select.cc | 22 +- sql/table.cc | 1 + sql/table.h | 6 + 7 files changed, 745 insertions(+), 4 deletions(-) diff --git a/mysql-test/main/mdev15777.result b/mysql-test/main/mdev15777.result new file mode 100644 index 00000000000..549ac08b91f --- /dev/null +++ b/mysql-test/main/mdev15777.result @@ -0,0 +1,455 @@ +# Test added to check that null filtering is used by range optimizer +create table ten(a int); +insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table one_k(a int); +insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; +create table one_m(a int); +insert into one_m select A.a + B.a* 1000 from one_k A, one_k B; +delete from one_m where a=0 limit 1; +create table t1 ( +id int(10) unsigned NOT NULL AUTO_INCREMENT, +filler varchar(100), +subset_id int(11) DEFAULT NULL, +PRIMARY KEY (id), +KEY t1_subset_id (subset_id) +); +create table t1_subsets ( +id int(10) unsigned NOT NULL AUTO_INCREMENT, +filler1 varchar(100), +filler2 varchar(100), +filler3 varchar(100), +PRIMARY KEY (id) +); +insert into t1 select a,a, NULL from one_m where a < 50*1000; +insert into t1_subsets select a,a,a,a from one_m where a < 500*1000 limit 499000; +analyze format=json +SELECT * FROM t1 WHERE t1.subset_id IN (SELECT t1_subsets.id FROM t1_subsets); +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t1", + "access_type": "range", + "possible_keys": ["t1_subset_id"], + "key": "t1_subset_id", + "key_length": "5", + "used_key_parts": ["subset_id"], + "r_loops": 1, + "rows": 3, + "r_rows": 0, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "index_condition": "t1.subset_id is not null" + }, + "table": { + "table_name": "t1_subsets", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["id"], + "ref": ["test.t1.subset_id"], + "r_loops": 0, + "rows": 1, + "r_rows": null, + "filtered": 100, + "r_filtered": null, + "attached_condition": "t1.subset_id = t1_subsets.`id`", + "using_index": true + } + } +} +drop table t1,t1_subsets,ten,one_k,one_m; +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t2 ( +pk int primary key, +a int, b int, +filler char(200), +key(a) +); +insert into t2 select a, 1000-a, 1000-a, repeat('abc-',50) from t1 where a<200 limit 200; +create table t3 ( +pk int primary key, +a int, b int, +filler char(200), +key(a) +); +insert into t3 select a, 1000-a, 1000-a, repeat('abc-',50) from t1; +insert into t3 select a+1000, 1000+a, 1000+a, repeat('abc-',50) from t1; +analyze format=json +select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b +from t2, t3 where t2.a=t3.a order by t2.a limit 5; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t2", + "access_type": "range", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "r_loops": 1, + "rows": 200, + "r_rows": 5, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "index_condition": "t2.a is not null" + }, + "table": { + "table_name": "t3", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t2.a"], + "r_loops": 5, + "rows": 1, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } +} +drop table t1,t2,t3,t0; +CREATE TABLE t1 ( a varchar(1)) ; +INSERT INTO t1 VALUES ('c'),('b'); +CREATE TABLE t3 ( a int NOT NULL , b varchar(1)) ; +INSERT INTO t3 VALUES (29,'c'); +INSERT INTO t3 VALUES (2,'c'); +alter table t1 add index aa (a); +alter table t3 add index bb (b); +SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); +a +c +explain extended +SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t3 range bb bb 4 NULL 2 100.00 Using index condition; LooseScan +1 PRIMARY t1 ref aa aa 4 test.t3.b 2 100.00 Using index +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t3`) where `test`.`t1`.`a` = `test`.`t3`.`b` +analyze format=json +SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t3", + "access_type": "range", + "possible_keys": ["bb"], + "key": "bb", + "key_length": "4", + "used_key_parts": ["b"], + "r_loops": 1, + "rows": 2, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "index_condition": "t3.b is not null", + "loose_scan": true + }, + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["aa"], + "key": "aa", + "key_length": "4", + "used_key_parts": ["a"], + "ref": ["test.t3.b"], + "r_loops": 1, + "rows": 2, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "using_index": true + } + } +} +drop table t1,t3; +create table t1 (d int, id1 int, index idx1 (d, id1)); +insert into t1 values +(3, 20), (2, 40), (3, 10), (1, 10), (3, 20), (1, 40), (2, 30), (3, 30); +create table t2 (id1 int, id2 int, index idx2 (id1)); +insert into t2 values +(20, 100), (30, 400), (20, 400), (30, 200), (10, 300), (10, 200), (40, 100), +(40, 200), (30, 300), (10, 400), (20, 200), (20, 300); +insert into t2 values +(21, 10), (31, 400), (21, 400), (31, 200), (11, 300), (11, 200), (41, 100), +(41, 200), (31, 300), (11, 400), (21, 200), (21, 300); +set join_cache_level=6; +explain +select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 +where t1.d=3 group by t1.id1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx1 idx1 10 NULL 4 Using where; Using index +1 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 +analyze format=json +select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 +where t1.d=3 group by t1.id1; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t1", + "access_type": "range", + "possible_keys": ["idx1"], + "key": "idx1", + "key_length": "10", + "used_key_parts": ["d", "id1"], + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_total_time_ms": "REPLACED", + "filtered": 25, + "r_filtered": 100, + "attached_condition": "t1.d = 3 and t1.id1 is not null", + "using_index": true + }, + "table": { + "table_name": "t2", + "access_type": "ref", + "possible_keys": ["idx2"], + "key": "idx2", + "key_length": "5", + "used_key_parts": ["id1"], + "ref": ["test.t1.id1"], + "r_loops": 4, + "rows": 2, + "r_rows": 3.5, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } +} +drop table t1,t2; +set @save_optimizer_switch= @@optimizer_switch; +set @@optimizer_switch= 'materialization=off'; +CREATE TABLE t1 (a CHAR(1), b VARCHAR(10), key(a) , key(b)); +INSERT INTO t1 VALUES ('a', 'aa'); +INSERT INTO t1 VALUES ('a', 'aaa'); +analyze format=json SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t1", + "access_type": "range", + "possible_keys": ["b"], + "key": "b", + "key_length": "13", + "used_key_parts": ["b"], + "r_loops": 1, + "rows": 2, + "r_rows": 2, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "index_condition": "t1.b is not null" + }, + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "2", + "used_key_parts": ["a"], + "ref": ["test.t1.b"], + "r_loops": 2, + "rows": 2, + "r_rows": 2, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 0, + "attached_condition": "t1.b = t1.a", + "using_index": true, + "first_match": "t1" + } + } +} +SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); +a b +CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10), key(a), key(b)); +INSERT INTO t2 SELECT * FROM t1; +analyze format=json SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t2", + "access_type": "index", + "possible_keys": ["a"], + "key": "a", + "key_length": "4", + "used_key_parts": ["a"], + "r_loops": 1, + "rows": 2, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 50, + "r_filtered": 100, + "attached_condition": "t2.a is not null", + "using_index": true, + "loose_scan": true + }, + "table": { + "table_name": "t2", + "access_type": "ref", + "possible_keys": ["b"], + "key": "b", + "key_length": "13", + "used_key_parts": ["b"], + "ref": ["test.t2.a"], + "r_loops": 1, + "rows": 1, + "r_rows": 0, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "index_condition": "t2.b = t2.a" + } + } +} +SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); +a b +analyze format=json SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t1", + "access_type": "range", + "possible_keys": ["b"], + "key": "b", + "key_length": "13", + "used_key_parts": ["b"], + "r_loops": 1, + "rows": 2, + "r_rows": 2, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "index_condition": "t1.b is not null" + }, + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "2", + "used_key_parts": ["a"], + "ref": ["test.t1.b"], + "r_loops": 2, + "rows": 2, + "r_rows": 2, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 0, + "attached_condition": "octet_length(t1.a) < 500 and t1.b = t1.a", + "using_index": true, + "first_match": "t1" + } + } +} +SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); +a b +DROP TABLE t1,t2; +set @@optimizer_switch= @save_optimizer_switch; +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='exists_to_in=on'; +set optimizer_switch='exists_to_in=on,in_to_exists=on,semijoin=on,materialization=off,subquery_cache=off'; +CREATE TABLE t1 ( a varchar(1)) ; +INSERT INTO t1 VALUES ('c'),('b'); +CREATE TABLE t2 ( b varchar(1)) ; +INSERT INTO t2 VALUES ('v'),('v'),('c'),(NULL),('x'),('i'),('e'),('p'),('s'),('j'),('z'),('c'),('a'),('q'),('y'),(NULL),('r'),('v'),(NULL),('r'); +CREATE TABLE t3 ( a int NOT NULL , b varchar(1)) ; +INSERT INTO t3 VALUES (29,'c'); +INSERT INTO t3 VALUES (2,'c'); +alter table t1 add index aa (a); +alter table t3 add index bb (b); +SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); +a +c +explain extended +SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t3 range bb bb 4 NULL 2 100.00 Using index condition; LooseScan +1 PRIMARY t1 ref aa aa 4 test.t3.b 2 100.00 Using index +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t3`) where `test`.`t1`.`a` = `test`.`t3`.`b` +analyze format=json SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t3", + "access_type": "range", + "possible_keys": ["bb"], + "key": "bb", + "key_length": "4", + "used_key_parts": ["b"], + "r_loops": 1, + "rows": 2, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "index_condition": "t3.b is not null", + "loose_scan": true + }, + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["aa"], + "key": "aa", + "key_length": "4", + "used_key_parts": ["a"], + "ref": ["test.t3.b"], + "r_loops": 1, + "rows": 2, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "using_index": true + } + } +} +drop table t1,t2,t3; +set @@optimizer_switch= @save_optimizer_switch; diff --git a/mysql-test/main/mdev15777.test b/mysql-test/main/mdev15777.test new file mode 100644 index 00000000000..a7f21f6b6a2 --- /dev/null +++ b/mysql-test/main/mdev15777.test @@ -0,0 +1,138 @@ +--echo # Test added to check that null filtering is used by range optimizer + +create table ten(a int); +insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table one_k(a int); +insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; + +create table one_m(a int); +insert into one_m select A.a + B.a* 1000 from one_k A, one_k B; +delete from one_m where a=0 limit 1; + +create table t1 ( + id int(10) unsigned NOT NULL AUTO_INCREMENT, + filler varchar(100), + subset_id int(11) DEFAULT NULL, + PRIMARY KEY (id), + KEY t1_subset_id (subset_id) +); + +create table t1_subsets ( + id int(10) unsigned NOT NULL AUTO_INCREMENT, + filler1 varchar(100), + filler2 varchar(100), + filler3 varchar(100), + PRIMARY KEY (id) +); + +insert into t1 select a,a, NULL from one_m where a < 50*1000; +insert into t1_subsets select a,a,a,a from one_m where a < 500*1000 limit 499000; + +--source include/analyze-format.inc +analyze format=json +SELECT * FROM t1 WHERE t1.subset_id IN (SELECT t1_subsets.id FROM t1_subsets); +drop table t1,t1_subsets,ten,one_k,one_m; + +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t2 ( + pk int primary key, + a int, b int, + filler char(200), + key(a) +); +insert into t2 select a, 1000-a, 1000-a, repeat('abc-',50) from t1 where a<200 limit 200; + +create table t3 ( + pk int primary key, + a int, b int, + filler char(200), + key(a) +); +insert into t3 select a, 1000-a, 1000-a, repeat('abc-',50) from t1; +insert into t3 select a+1000, 1000+a, 1000+a, repeat('abc-',50) from t1; + +--source include/analyze-format.inc +analyze format=json +select t2.pk,t2.a,t2.b,t3.pk,t3.a,t3.b +from t2, t3 where t2.a=t3.a order by t2.a limit 5; +drop table t1,t2,t3,t0; + +CREATE TABLE t1 ( a varchar(1)) ; +INSERT INTO t1 VALUES ('c'),('b'); +CREATE TABLE t3 ( a int NOT NULL , b varchar(1)) ; +INSERT INTO t3 VALUES (29,'c'); +INSERT INTO t3 VALUES (2,'c'); +alter table t1 add index aa (a); +alter table t3 add index bb (b); + +SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); +explain extended +SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); +--source include/analyze-format.inc +analyze format=json +SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); +drop table t1,t3; + +create table t1 (d int, id1 int, index idx1 (d, id1)); +insert into t1 values +(3, 20), (2, 40), (3, 10), (1, 10), (3, 20), (1, 40), (2, 30), (3, 30); +create table t2 (id1 int, id2 int, index idx2 (id1)); +insert into t2 values +(20, 100), (30, 400), (20, 400), (30, 200), (10, 300), (10, 200), (40, 100), +(40, 200), (30, 300), (10, 400), (20, 200), (20, 300); +insert into t2 values +(21, 10), (31, 400), (21, 400), (31, 200), (11, 300), (11, 200), (41, 100), +(41, 200), (31, 300), (11, 400), (21, 200), (21, 300); +set join_cache_level=6; +explain +select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 +where t1.d=3 group by t1.id1; +--source include/analyze-format.inc +analyze format=json +select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 +where t1.d=3 group by t1.id1; +drop table t1,t2; + +#### Test 4 ###### +set @save_optimizer_switch= @@optimizer_switch; +set @@optimizer_switch= 'materialization=off'; +CREATE TABLE t1 (a CHAR(1), b VARCHAR(10), key(a) , key(b)); +INSERT INTO t1 VALUES ('a', 'aa'); +INSERT INTO t1 VALUES ('a', 'aaa'); +--source include/analyze-format.inc +analyze format=json SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); +SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1); +CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10), key(a), key(b)); +INSERT INTO t2 SELECT * FROM t1; +--source include/analyze-format.inc +analyze format=json SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); +SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2); +--source include/analyze-format.inc +analyze format=json SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); +SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500); +DROP TABLE t1,t2; +set @@optimizer_switch= @save_optimizer_switch; + +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='exists_to_in=on'; +set optimizer_switch='exists_to_in=on,in_to_exists=on,semijoin=on,materialization=off,subquery_cache=off'; +CREATE TABLE t1 ( a varchar(1)) ; +INSERT INTO t1 VALUES ('c'),('b'); +CREATE TABLE t2 ( b varchar(1)) ; +INSERT INTO t2 VALUES ('v'),('v'),('c'),(NULL),('x'),('i'),('e'),('p'),('s'),('j'),('z'),('c'),('a'),('q'),('y'),(NULL),('r'),('v'),(NULL),('r'); +CREATE TABLE t3 ( a int NOT NULL , b varchar(1)) ; +INSERT INTO t3 VALUES (29,'c'); +INSERT INTO t3 VALUES (2,'c'); +alter table t1 add index aa (a); +alter table t3 add index bb (b); +SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); +explain extended +SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); +--source include/analyze-format.inc +analyze format=json SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t3 WHERE t3.b = t1.a); +drop table t1,t2,t3; +set @@optimizer_switch= @save_optimizer_switch; \ No newline at end of file diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 8422917065f..ae06b899ac9 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -155,6 +155,7 @@ class SEL_IMERGE; #define CLONE_KEY1_MAYBE 1 #define CLONE_KEY2_MAYBE 2 #define swap_clone_flag(A) ((A & 1) << 1) | ((A & 2) >> 1) +#define FT_KEYPART (MAX_FIELDS+10) /* @@ -2400,6 +2401,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, { uint idx; double scan_time; + Item *null_rejecting_conds= NULL; DBUG_ENTER("SQL_SELECT::test_quick_select"); DBUG_PRINT("enter",("keys_to_use: %lu prev_tables: %lu const_tables: %lu", (ulong) keys_to_use.to_ulonglong(), (ulong) prev_tables, @@ -2423,6 +2425,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, read_time= (double) records + scan_time + 1; // Force to use index possible_keys.clear_all(); + null_rejecting_conds= head->null_rejecting_conds; DBUG_PRINT("info",("Time to scan table: %g", read_time)); @@ -2431,7 +2434,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, { uchar buff[STACK_BUFF_ALLOC]; MEM_ROOT alloc; - SEL_TREE *tree= NULL; + SEL_TREE *tree= NULL, *not_null_cond_tree= NULL; KEY_PART *key_parts; KEY *key_info; PARAM param; @@ -2540,6 +2543,12 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, TRP_GROUP_MIN_MAX *group_trp; double best_read_time= read_time; + if (null_rejecting_conds) + { + not_null_cond_tree= null_rejecting_conds->get_mm_tree(&param, + &null_rejecting_conds); + } + if (cond) { if ((tree= cond->get_mm_tree(&param, &cond))) @@ -2558,6 +2567,13 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, tree= NULL; } } + if (not_null_cond_tree) + { + if (!tree) + tree= not_null_cond_tree; + else + tree= tree_and(&param, tree, not_null_cond_tree); + } /* Try to construct a QUICK_GROUP_MIN_MAX_SELECT. @@ -14643,6 +14659,113 @@ void QUICK_GROUP_MIN_MAX_SELECT::add_keys_and_lengths(String *key_names, add_key_and_length(key_names, used_lengths, &first); } +inline void add_cond(THD *thd, Item **e1, Item *e2) +{ + if (*e1) + { + if (!e2) + return; + Item *res; + if ((res= new (thd->mem_root) Item_cond_and(thd, *e1, e2))) + { + res->fix_fields(thd, 0); + res->update_used_tables(); + *e1= res; + } + } + else + *e1= e2; +} + +/* + Create null rejecting conditions for a table, for all the equalites + present in the WHERE clause of a query. + + SYNOPSIS + make_null_rejecting_conds() + @param TABLE - Keys of this table will participate in null + rejecting conditions + @param keyuse_array - array that has all the equalites of the + WHERE clasuse + + DESCRIPTION + This function creates null rejecting conditions for a table. These + conditions are created to do range analysis on them , the conditions + are of the form tbl.key.keypart IS NOT NULL. + + IMPLEMENTATION + Lookup in the keyuse array to check if it has equalites that belong + to the given table. If yes then find out if the conditions are null + rejecting and accordingly create all the condition for the keys of a + given table and AND them. + + + RETURN + NOT NULL - Found null rejecting conditions for the given table + NULL - No null rejecting conditions for the given table +*/ + +void make_null_rejecting_conds(THD *thd, TABLE *table, + DYNAMIC_ARRAY *keyuse_array, key_map *const_keys) +{ + KEY *keyinfo; + Item *cond= NULL; + KEYUSE* keyuse; + + /* + The null rejecting conds added will be on the keypart of a key, so for + that we need the table to atleast have a key. + */ + if (!table->s->keys) + return ; + if (table->null_rejecting_conds) + return; + + for(uint i=0; i < keyuse_array->elements; i++) + { + keyuse= (KEYUSE*)dynamic_array_ptr(keyuse_array, i); + if (keyuse->table == table) + { + /* + No null rejecting conds for a hash key orr full-text keys + */ + if (keyuse->key == MAX_KEY || keyuse->keypart == FT_KEYPART) + continue; + keyinfo= keyuse->table->key_info+keyuse->key; + Field *field= keyinfo->key_part[keyuse->keypart].field; + + /* + No need to add null-rejecting condition if we have a + keyuse element as + - table.key.keypart= const + - (table.key.keypart= tbl.otherfield or table.key.keypart IS NULL) + - table.key.keypart IS NOT NULLABLE + */ + + if (keyuse->val->const_item() + || !(keyuse->null_rejecting && field->maybe_null()) + || keyuse->optimize & KEY_OPTIMIZE_REF_OR_NULL) + continue; + + Item_field *field_item= new (thd->mem_root)Item_field(thd, field); + Item* not_null_item= new (thd->mem_root)Item_func_isnotnull(thd, + field_item); + + /* + adding the key to const keys as we have the condition + as key.keypart IS NOT NULL + */ + + const_keys->set_bit(keyuse->key); + not_null_item->fix_fields(thd, 0); + not_null_item->update_used_tables(); + add_cond(thd, &cond, not_null_item); + } + } + table->null_rejecting_conds= cond; + return; +} + #ifndef DBUG_OFF diff --git a/sql/opt_range.h b/sql/opt_range.h index bd85a12d4a1..894d46a892c 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -1728,6 +1728,8 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item **cond); bool prune_partitions(THD *thd, TABLE *table, Item *pprune_cond); #endif void store_key_image_to_rec(Field *field, uchar *ptr, uint len); +void make_null_rejecting_conds(THD *thd, TABLE *table, + DYNAMIC_ARRAY *keyuse_array, key_map *const_keys); extern String null_string; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index f658b78c33c..4aceb333340 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4805,6 +4805,9 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, add_group_and_distinct_keys(join, s); s->table->cond_selectivity= 1.0; + + make_null_rejecting_conds(join->thd, s->table, + keyuse_array, &s->const_keys); /* Perform range analysis if there are keys it could use (1). @@ -4834,6 +4837,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, 1, &error); if (!select) goto error; + records= get_quick_record_count(join->thd, select, s->table, &s->const_keys, join->row_limit); /* Range analyzer could modify the condition. */ @@ -5352,15 +5356,24 @@ add_key_field(JOIN *join, If the condition has form "tbl.keypart = othertbl.field" and othertbl.field can be NULL, there will be no matches if othertbl.field has NULL value. + + The field KEY_FIELD::null_rejecting is set to TRUE if we have both + the left and right hand side of the equality are NULLABLE + We use null_rejecting in add_not_null_conds() to add 'othertbl.field IS NOT NULL' to tab->select_cond. + + We use null_rejecting in make_null_rejecting_conds() to add + tbl.keypart IS NOT NULL so we can do range analysis on this condition + */ { Item *real= (*value)->real_item(); if (((cond->functype() == Item_func::EQ_FUNC) || (cond->functype() == Item_func::MULT_EQUAL_FUNC)) && - (real->type() == Item::FIELD_ITEM) && + (((real->type() == Item::FIELD_ITEM) && ((Item_field*)real)->field->maybe_null()) + ||(field->maybe_null()))) (*key_fields)->null_rejecting= true; else (*key_fields)->null_rejecting= false; @@ -9813,7 +9826,10 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, uint maybe_null= MY_TEST(keyinfo->key_part[i].null_bit); j->ref.items[i]=keyuse->val; // Save for cond removal j->ref.cond_guards[i]= keyuse->cond_guard; - if (keyuse->null_rejecting) + Item *real= (keyuse->val)->real_item(); + if (keyuse->null_rejecting && + (real->type() == Item::FIELD_ITEM) && + ((Item_field*)real)->field->maybe_null()) j->ref.null_rejecting|= (key_part_map)1 << i; keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables; /* @@ -18538,7 +18554,7 @@ free_tmp_table(THD *thd, TABLE *entry) DBUG_ASSERT(entry->pos_in_table_list->table == entry); entry->pos_in_table_list->table= NULL; } - + entry->null_rejecting_conds= NULL; free_root(&own_root, MYF(0)); /* the table is allocated in its own root */ thd_proc_info(thd, save_proc_info); diff --git a/sql/table.cc b/sql/table.cc index a6e445d0d2e..097fee465de 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -4597,6 +4597,7 @@ void TABLE::init(THD *thd, TABLE_LIST *tl) created= TRUE; cond_selectivity= 1.0; cond_selectivity_sampling_explain= NULL; + null_rejecting_conds= NULL; #ifdef HAVE_REPLICATION /* used in RBR Triggers */ master_had_triggers= 0; diff --git a/sql/table.h b/sql/table.h index 6fd3f219914..50d47899b49 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1356,6 +1356,12 @@ struct TABLE SplM_opt_info *spl_opt_info; key_map keys_usable_for_splitting; + /* + Null rejecting conds added for all tables so we can do range analysis + on these conditions + */ + Item* null_rejecting_conds; + void init(THD *thd, TABLE_LIST *tl); bool fill_item_list(List<Item> *item_list) const; void reset_item_list(List<Item> *item_list, uint skip) const;

2 1

[Commits] 81b1a74f60d: MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE
by Varun 06 May '19

06 May '19

revision-id: 81b1a74f60d8816e06be154ee5028356221b0f76 (mariadb-10.4.4-66-g81b1a74f60d) parent(s): d18ef804bb5d9d473055a2fdc04f74e175a8e9cd author: Varun Gupta committer: Varun Gupta timestamp: 2019-05-06 09:12:20 +0530 message: MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE So to push index condition for each join tab we have calculate the index condition that can be pushed and then remove this index condition from the original condition. This is done through the function make_cond_remainder. The problem is the function make_cond_remainder does not remove index condition when there is an OR operator. Fixed this by making the function make_cond_remainder to keep in mind of the OR operator. Also updated results for multiple test files which were incorrectly updated by the commit e0c1b3f24246d22e6785315f9a8448bd9a590422 code which was supposed to remove the condition present in the index condition was not getting executed when the condition had OR operator, with AND the pushed index condition was getting removed from where. This problem affects all versions starting from 5.5 but this is a performance improvement, so fixing it in 10.4 --- mysql-test/main/index_merge_myisam.result | 2 +- mysql-test/main/innodb_icp.result | 2 +- mysql-test/main/mrr_icp_extra.result | 6 +++--- mysql-test/main/myisam_icp.result | 23 ++++++++++++++++++++-- mysql-test/main/myisam_icp.test | 15 ++++++++++++++ mysql-test/main/order_by.result | 4 ++-- mysql-test/main/range.result | 8 ++++---- mysql-test/main/range_mrr_icp.result | 8 ++++---- mysql-test/main/range_vs_index_merge.result | 14 ++++++------- mysql-test/main/range_vs_index_merge_innodb.result | 8 ++++---- mysql-test/main/select.result | 2 +- mysql-test/main/select_jcl6.result | 2 +- mysql-test/main/select_pkeycache.result | 2 +- mysql-test/main/subselect_mat_cost.result | 2 +- mysql-test/main/xtradb_mrr.result | 4 ++-- sql/opt_index_cond_pushdown.cc | 15 ++++++-------- 16 files changed, 74 insertions(+), 43 deletions(-) diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result index 3d31f8d3dfa..484ee626b98 100644 --- a/mysql-test/main/index_merge_myisam.result +++ b/mysql-test/main/index_merge_myisam.result @@ -23,7 +23,7 @@ test.t0 analyze status Engine-independent statistics collected test.t0 analyze status OK explain select * from t0 where key1 < 3 or key1 > 920 and key1 < 924; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 range i1 i1 4 NULL 5 Using index condition; Using where +1 SIMPLE t0 range i1 i1 4 NULL 5 Using index condition explain select * from t0 where key1 < 3 or key2 > 920 and key2 < 924; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/main/innodb_icp.result b/mysql-test/main/innodb_icp.result index 07d317925cd..d65acd5a48d 100644 --- a/mysql-test/main/innodb_icp.result +++ b/mysql-test/main/innodb_icp.result @@ -670,7 +670,7 @@ SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using filesort +1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Rowid-ordered scan; Using filesort SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; diff --git a/mysql-test/main/mrr_icp_extra.result b/mysql-test/main/mrr_icp_extra.result index 49acd7bde20..176df5cf9d5 100644 --- a/mysql-test/main/mrr_icp_extra.result +++ b/mysql-test/main/mrr_icp_extra.result @@ -74,7 +74,7 @@ insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2); insert into t1 values (2, 11), (1, 11), (4, 14), (3, 14), (6, 12), (5, 12); explain select * from t1 where b=1 or b is null order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using where; Using filesort +1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort select * from t1 where b=1 or b is null order by a; a b 1 1 @@ -83,7 +83,7 @@ a b 4 NULL explain select * from t1 where b=2 or b is null order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using where; Using filesort +1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort select * from t1 where b=2 or b is null order by a; a b 3 NULL @@ -125,7 +125,7 @@ Table Op Msg_type Msg_text test.t1 optimize status OK explain select * from t1 force index (a) where a=0 or a=2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 4 NULL 5 Using index condition; Using where; Rowid-ordered scan +1 SIMPLE t1 range a a 4 NULL 5 Using index condition; Rowid-ordered scan select * from t1 force index (a) where a=0 or a=2; a b c 0 NULL 0 diff --git a/mysql-test/main/myisam_icp.result b/mysql-test/main/myisam_icp.result index 577a0df12b0..0fdc3f11627 100644 --- a/mysql-test/main/myisam_icp.result +++ b/mysql-test/main/myisam_icp.result @@ -505,7 +505,7 @@ SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1 WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR (t1.pk > 1 AND t2.pk BETWEEN 6 AND 6); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using where; Rowid-ordered scan +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Rowid-ordered scan 1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1 WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR @@ -663,7 +663,7 @@ SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using filesort +1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Rowid-ordered scan; Using filesort SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; @@ -995,4 +995,23 @@ explain select * from t1 where a=3 and col > 500 order by a desc, col desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 10 NULL 10 Using where drop table t0, t1; +# +# MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE +# +create table ten(a int); +insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table one_k(a int); +insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; +create table t1 (key1 int not null, filler char(100)); +insert into t1 select A.a + 1000 *B.a, 'filler-data' from one_k A, ten B; +alter table t1 add key(key1); +explain select * from t1 where key1 < 3 or key1 > 99999; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range key1 key1 4 NULL 22 Using index condition; Rowid-ordered scan +select * from t1 where key1 < 3 or key1 > 99999; +key1 filler +0 filler-data +1 filler-data +2 filler-data +drop table ten,one_k,t1; set optimizer_switch=@myisam_icp_tmp; diff --git a/mysql-test/main/myisam_icp.test b/mysql-test/main/myisam_icp.test index 508c282b1dc..b6d35968b1c 100644 --- a/mysql-test/main/myisam_icp.test +++ b/mysql-test/main/myisam_icp.test @@ -282,5 +282,20 @@ explain select * from t1 where a=3 and col > 500 order by a desc, col desc; drop table t0, t1; +--echo # +--echo # MDEV-13628: ORed condition in pushed index condition is not removed from the WHERE +--echo # + +create table ten(a int); +insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table one_k(a int); +insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; +create table t1 (key1 int not null, filler char(100)); +insert into t1 select A.a + 1000 *B.a, 'filler-data' from one_k A, ten B; +alter table t1 add key(key1); +explain select * from t1 where key1 < 3 or key1 > 99999; +select * from t1 where key1 < 3 or key1 > 99999; +drop table ten,one_k,t1; + set optimizer_switch=@myisam_icp_tmp; diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index 4e8f8bfb17d..a053b6060c4 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -736,7 +736,7 @@ insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2); insert into t1 values (12, 11), (11, 11), (14, 3), (13, 5), (16, 12), (15, 12); explain select * from t1 where b=1 or b is null order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using where; Using filesort +1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort select * from t1 where b=1 or b is null order by a; a b 1 1 @@ -745,7 +745,7 @@ a b 4 NULL explain select * from t1 where b=2 or b is null order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using where; Using filesort +1 SIMPLE t1 ref_or_null b b 5 const 4 Using index condition; Using filesort select * from t1 where b=2 or b is null order by a; a b 3 NULL diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index 9a2d99e2f82..fe0e3ef024d 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -953,7 +953,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range status status 23 NULL 17 Using index condition EXPLAIN SELECT * FROM t1 WHERE status < 'A' OR status > 'B'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range status status 23 NULL 17 Using index condition; Using where +1 SIMPLE t1 range status status 23 NULL 17 Using index condition SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B'; id status 53 C @@ -1073,13 +1073,13 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 13 NULL # Using index condition explain select * from t1 where a = 'a' or a='a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 13 NULL # Using index condition; Using where +1 SIMPLE t1 range a a 13 NULL # Using index condition explain select * from t2 where a between 'a' and 'a '; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref a a 13 const # Using index condition explain select * from t2 where a = 'a' or a='a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref a a 13 const # Using index condition; Using where +1 SIMPLE t2 ref a a 13 const # Using index condition update t1 set a='b' where a<>'a'; explain select * from t1 where a not between 'b' and 'b'; id select_type table type possible_keys key key_len ref rows Extra @@ -2008,7 +2008,7 @@ INSERT INTO t100(I,J) VALUES(8,26); EXPLAIN SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t100 range I I 10 NULL 3 Using index condition; Using where +1 SIMPLE t100 range I I 10 NULL 3 Using index condition SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5); K I J diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result index 5cda4111b6d..fbcee9247a2 100644 --- a/mysql-test/main/range_mrr_icp.result +++ b/mysql-test/main/range_mrr_icp.result @@ -956,7 +956,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range status status 23 NULL 17 Using index condition; Rowid-ordered scan EXPLAIN SELECT * FROM t1 WHERE status < 'A' OR status > 'B'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range status status 23 NULL 17 Using index condition; Using where; Rowid-ordered scan +1 SIMPLE t1 range status status 23 NULL 17 Using index condition; Rowid-ordered scan SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B'; id status 53 C @@ -1076,13 +1076,13 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 13 NULL # Using index condition; Rowid-ordered scan explain select * from t1 where a = 'a' or a='a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 13 NULL # Using index condition; Using where; Rowid-ordered scan +1 SIMPLE t1 range a a 13 NULL # Using index condition; Rowid-ordered scan explain select * from t2 where a between 'a' and 'a '; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref a a 13 const # Using index condition explain select * from t2 where a = 'a' or a='a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref a a 13 const # Using index condition; Using where +1 SIMPLE t2 ref a a 13 const # Using index condition update t1 set a='b' where a<>'a'; explain select * from t1 where a not between 'b' and 'b'; id select_type table type possible_keys key key_len ref rows Extra @@ -2011,7 +2011,7 @@ INSERT INTO t100(I,J) VALUES(8,26); EXPLAIN SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t100 range I I 10 NULL 3 Using index condition; Using where; Rowid-ordered scan +1 SIMPLE t100 range I I 10 NULL 3 Using index condition; Rowid-ordered scan SELECT * FROM t100 WHERE I <> 6 OR (I <> 8 AND J = 5); K I J diff --git a/mysql-test/main/range_vs_index_merge.result b/mysql-test/main/range_vs_index_merge.result index 65ac003b427..5c3e5441b8b 100644 --- a/mysql-test/main/range_vs_index_merge.result +++ b/mysql-test/main/range_vs_index_merge.result @@ -327,11 +327,11 @@ ID Name Country Population EXPLAIN SELECT * FROM City WHERE (ID < 10) OR (ID BETWEEN 100 AND 110); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 20 Using index condition; Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 20 Using index condition EXPLAIN SELECT * FROM City WHERE (ID < 200) OR (ID BETWEEN 100 AND 200); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 200 Using index condition; Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 200 Using index condition EXPLAIN SELECT * FROM City WHERE (ID < 600) OR (ID BETWEEN 900 AND 1500); id select_type table type possible_keys key key_len ref rows Extra @@ -343,11 +343,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM City WHERE Name LIKE 'H%' OR Name LIKE 'P%' ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 223 Using index condition; Using where +1 SIMPLE City range Name Name 35 NULL 223 Using index condition EXPLAIN SELECT * FROM City WHERE Name LIKE 'Ha%' OR Name LIKE 'Pa%' ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 72 Using index condition; Using where +1 SIMPLE City range Name Name 35 NULL 72 Using index condition EXPLAIN SELECT * FROM City WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG'))) @@ -1079,7 +1079,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE (Name='Samara' AND Country='RUS') OR (Name='Seattle' AND Country='USA'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 28 Using index condition; Using where +1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 28 Using index condition SELECT Name, Country, Population FROM City WHERE (Name='Manila' AND Country='PHL') OR (Name='Addis Abeba' AND Country='ETH') OR @@ -1166,7 +1166,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE (Name='Samara' AND Country='RUS') OR (Name='Seattle' AND Country='USA'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 28 Using index condition; Using where +1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 28 Using index condition SELECT Name, Country, Population FROM City WHERE (Name='Manila' AND Country='PHL') OR (Name='Addis Abeba' AND Country='ETH') OR @@ -1938,6 +1938,6 @@ Country='POL' AND Name IN ('Warszawa', 'Wroclaw') OR Country='NOR' AND Name IN ('Oslo', 'Bergen') OR Country='ITA' AND Name IN ('Napoli', 'Venezia'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range CountryName,Name CountryName 38 NULL 20 Using index condition; Using where +1 SIMPLE City range CountryName,Name CountryName 38 NULL 20 Using index condition DROP DATABASE world; set session optimizer_switch='index_merge_sort_intersection=default'; diff --git a/mysql-test/main/range_vs_index_merge_innodb.result b/mysql-test/main/range_vs_index_merge_innodb.result index 061fcab15b4..e2fd8020049 100644 --- a/mysql-test/main/range_vs_index_merge_innodb.result +++ b/mysql-test/main/range_vs_index_merge_innodb.result @@ -349,11 +349,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM City WHERE Name LIKE 'H%' OR Name LIKE 'P%' ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 395 Using index condition; Using where +1 SIMPLE City range Name Name 35 NULL 395 Using index condition EXPLAIN SELECT * FROM City WHERE Name LIKE 'Ha%' OR Name LIKE 'Pa%' ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 133 Using index condition; Using where +1 SIMPLE City range Name Name 35 NULL 133 Using index condition EXPLAIN SELECT * FROM City WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG'))) @@ -957,7 +957,7 @@ WHERE ((Population > 101000 AND Population < 11000) OR ID BETWEEN 3500 AND 3800) AND Country='USA' AND (Name LIKE 'P%' OR ID BETWEEN 4000 AND 4300); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY,Population,Country,Name,CountryPopulation,CountryName CountryName 38 NULL 18 Using index condition; Using where +1 SIMPLE City range PRIMARY,Population,Country,Name,CountryPopulation,CountryName CountryName 38 NULL 18 Using index condition EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 11000) OR @@ -1944,7 +1944,7 @@ Country='POL' AND Name IN ('Warszawa', 'Wroclaw') OR Country='NOR' AND Name IN ('Oslo', 'Bergen') OR Country='ITA' AND Name IN ('Napoli', 'Venezia'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range CountryName,Name CountryName 38 NULL 20 Using index condition; Using where +1 SIMPLE City range CountryName,Name CountryName 38 NULL 20 Using index condition DROP DATABASE world; set session optimizer_switch='index_merge_sort_intersection=default'; set global innodb_stats_persistent= @innodb_stats_persistent_save; diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result index a527459657a..804830c48df 100644 --- a/mysql-test/main/select.result +++ b/mysql-test/main/select.result @@ -3422,7 +3422,7 @@ SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition DROP TABLE t1,t2; SET SQL_MODE='NO_UNSIGNED_SUBTRACTION'; CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL); diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result index c1e9e9d3ad5..31856279ed5 100644 --- a/mysql-test/main/select_jcl6.result +++ b/mysql-test/main/select_jcl6.result @@ -3433,7 +3433,7 @@ SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Rowid-ordered scan +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Rowid-ordered scan DROP TABLE t1,t2; SET SQL_MODE='NO_UNSIGNED_SUBTRACTION'; CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL); diff --git a/mysql-test/main/select_pkeycache.result b/mysql-test/main/select_pkeycache.result index a527459657a..804830c48df 100644 --- a/mysql-test/main/select_pkeycache.result +++ b/mysql-test/main/select_pkeycache.result @@ -3422,7 +3422,7 @@ SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition DROP TABLE t1,t2; SET SQL_MODE='NO_UNSIGNED_SUBTRACTION'; CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL); diff --git a/mysql-test/main/subselect_mat_cost.result b/mysql-test/main/subselect_mat_cost.result index 9b0578b62b1..21ab292e7e5 100644 --- a/mysql-test/main/subselect_mat_cost.result +++ b/mysql-test/main/subselect_mat_cost.result @@ -288,7 +288,7 @@ WHERE Code NOT IN (SELECT Country FROM CountryLanguage WHERE Language = 'English AND (CountryLanguage.Language = 'French' OR CountryLanguage.Language = 'Spanish') AND Code = Country; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY CountryLanguage range PRIMARY,Language Language 30 NULL 44 Using index condition; Using where; Rowid-ordered scan +1 PRIMARY CountryLanguage range PRIMARY,Language Language 30 NULL 44 Using index condition; Rowid-ordered scan 1 PRIMARY Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where 3 MATERIALIZED CountryLanguage ref PRIMARY,Language Language 30 const 48 Using index condition set statement optimizer_switch='rowid_filter=off' for diff --git a/mysql-test/main/xtradb_mrr.result b/mysql-test/main/xtradb_mrr.result index f49207c0e41..383d04207af 100644 --- a/mysql-test/main/xtradb_mrr.result +++ b/mysql-test/main/xtradb_mrr.result @@ -186,7 +186,7 @@ explain select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' or c='no-such-row2'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 range idx1 idx1 29 NULL 16 Using index condition; Using where; Rowid-ordered scan +1 SIMPLE t4 range idx1 idx1 29 NULL 16 Using index condition; Rowid-ordered scan select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' or c='no-such-row2'); a b c filler @@ -208,7 +208,7 @@ NULL NULL NULL NULL-1 explain select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 range idx1 idx1 29 NULL 32 Using index condition; Using where; Rowid-ordered scan +1 SIMPLE t4 range idx1 idx1 29 NULL 32 Using index condition; Rowid-ordered scan select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); a b c filler b-1 NULL c-1 NULL-15 diff --git a/sql/opt_index_cond_pushdown.cc b/sql/opt_index_cond_pushdown.cc index b21cbb33c64..60312b470b3 100644 --- a/sql/opt_index_cond_pushdown.cc +++ b/sql/opt_index_cond_pushdown.cc @@ -264,6 +264,10 @@ static Item *make_cond_for_index(THD *thd, Item *cond, TABLE *table, uint keyno, static Item *make_cond_remainder(THD *thd, Item *cond, TABLE *table, uint keyno, bool other_tbls_ok, bool exclude_index) { + if (exclude_index && + uses_index_fields_only(cond, table, keyno, other_tbls_ok)) + return 0; + if (cond->type() == Item::COND_ITEM) { table_map tbl_map= 0; @@ -272,7 +276,7 @@ static Item *make_cond_remainder(THD *thd, Item *cond, TABLE *table, uint keyno, /* Create new top level AND item */ Item_cond_and *new_cond= new (thd->mem_root) Item_cond_and(thd); if (!new_cond) - return (COND*) 0; + return (COND*) 0; List_iterator<Item> li(*((Item_cond*) cond)->argument_list()); Item *item; while ((item=li++)) @@ -318,14 +322,7 @@ static Item *make_cond_remainder(THD *thd, Item *cond, TABLE *table, uint keyno, return new_cond; } } - else - { - if (exclude_index && - uses_index_fields_only(cond, table, keyno, other_tbls_ok)) - return 0; - else - return cond; - } + return cond; }

1 0

[Commits] 2756dff: MDEV-18689 Simple query with extra brackets stopped working
by IgorBabaev 06 May '19

06 May '19

revision-id: 2756dff22dbbe8b9674c1c552ba5b3f1fe7eb268 (mariadb-10.4.4-66-g2756dff) parent(s): d18ef804bb5d9d473055a2fdc04f74e175a8e9cd author: Igor Babaev committer: Igor Babaev timestamp: 2019-05-05 20:30:10 -0700 message: MDEV-18689 Simple query with extra brackets stopped working Parenthesis around table names and derived tables should be allowed in FROM clauses and some other context as it was in earlier versions. Returned test queries that used such parenthesis in 10.3 to their original form. Adjusted test results accordingly. --- mysql-test/main/brackets.result | 42 +++++++++++++++++++++ mysql-test/main/brackets.test | 22 +++++++++++ mysql-test/main/subselect.result | 24 +++++++++--- mysql-test/main/subselect.test | 4 -- mysql-test/main/subselect_mat.result | 4 +- mysql-test/main/subselect_no_exists_to_in.result | 24 +++++++++--- mysql-test/main/subselect_no_mat.result | 24 +++++++++--- mysql-test/main/subselect_no_opts.result | 24 +++++++++--- mysql-test/main/subselect_no_scache.result | 24 +++++++++--- mysql-test/main/subselect_no_semijoin.result | 24 +++++++++--- mysql-test/main/subselect_sj.result | 2 +- mysql-test/main/subselect_sj.test | 2 +- mysql-test/main/subselect_sj_jcl6.result | 2 +- mysql-test/main/subselect_sj_mat.result | 4 +- mysql-test/main/subselect_sj_mat.test | 4 +- mysql-test/main/union.result | 6 +-- mysql-test/main/union.test | 6 +-- mysql-test/main/view.result | 48 ++++++++++++------------ mysql-test/main/view.test | 48 ++++++++++++------------ mysql-test/suite/innodb/t/innodb.test | 2 +- sql/sql_yacc.yy | 17 +++++++-- sql/sql_yacc_ora.yy | 17 +++++++-- 22 files changed, 264 insertions(+), 110 deletions(-) diff --git a/mysql-test/main/brackets.result b/mysql-test/main/brackets.result index 869afe5..e14bef9 100644 --- a/mysql-test/main/brackets.result +++ b/mysql-test/main/brackets.result @@ -452,4 +452,46 @@ EXPLAIN } } drop table t1; +# +# MDEV-18689: parenthesis around table names and derived tables +# +select * from ( mysql.db ); +Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv Delete_history_priv +% test Y Y Y Y Y Y N Y Y Y Y Y Y Y Y N N Y Y Y +% test\_% Y Y Y Y Y Y N Y Y Y Y Y Y Y Y N N Y Y Y +create table t1 (a int); +insert into t1 values (7), (2), (7); +select * from (t1); +a +7 +2 +7 +select * from ((t1)); +a +7 +2 +7 +select * from (t1 t) where t.a > 5; +a +7 +7 +select * from ((t1 t)) where t.a > 5; +a +7 +7 +select * from ((select a, sum(a) from t1 group by a) t); +a sum(a) +2 2 +7 14 +select * from (((select a, sum(a) from t1 group by a) t)); +a sum(a) +2 2 +7 14 +update (t1 t) set t.a=t.a+1; +select * from t1; +a +8 +3 +8 +drop table t1; # End of 10.4 tests diff --git a/mysql-test/main/brackets.test b/mysql-test/main/brackets.test index cf1dcc5..9ca86b8 100644 --- a/mysql-test/main/brackets.test +++ b/mysql-test/main/brackets.test @@ -154,5 +154,27 @@ eval explain format=json $q; drop table t1; +--echo # +--echo # MDEV-18689: parenthesis around table names and derived tables +--echo # + +select * from ( mysql.db ); + +create table t1 (a int); +insert into t1 values (7), (2), (7); + +select * from (t1); +select * from ((t1)); +select * from (t1 t) where t.a > 5; +select * from ((t1 t)) where t.a > 5; + +select * from ((select a, sum(a) from t1 group by a) t); +select * from (((select a, sum(a) from t1 group by a) t)); + +update (t1 t) set t.a=t.a+1; +select * from t1; + +drop table t1; + --echo # End of 10.4 tests diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index 3bd23a4..8c8c034 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -5198,17 +5198,29 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1)) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM t1 JOIN ((t1 t1a)) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM (t1 t1a); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 +a +1 +2 SELECT * FROM ((t1 t1a)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 1 +a +1 +2 SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1; a t1a 1 1 diff --git a/mysql-test/main/subselect.test b/mysql-test/main/subselect.test index dd80b78..82823b4 100644 --- a/mysql-test/main/subselect.test +++ b/mysql-test/main/subselect.test @@ -4325,14 +4325,10 @@ SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; --error ER_PARSE_ERROR SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; ---error ER_PARSE_ERROR SELECT * FROM t1 JOIN (t1 t1a) ON 1; ---error ER_PARSE_ERROR SELECT * FROM t1 JOIN ((t1 t1a)) ON 1; ---error ER_PARSE_ERROR SELECT * FROM (t1 t1a); ---error ER_PARSE_ERROR SELECT * FROM ((t1 t1a)); SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1; diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result index 482833d..8ffe664 100644 --- a/mysql-test/main/subselect_mat.result +++ b/mysql-test/main/subselect_mat.result @@ -2212,11 +2212,11 @@ drop database mysqltest4; # (both 1st and further executions) CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM; INSERT INTO t1 VALUES (0),(8); -SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM t1 AS t2); +SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM (t1 AS t2)); a 0 PREPARE stmt FROM " -SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM t1 AS t2) +SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM (t1 AS t2)) "; execute stmt; a diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result index bacba84..1bcc1a5 100644 --- a/mysql-test/main/subselect_no_exists_to_in.result +++ b/mysql-test/main/subselect_no_exists_to_in.result @@ -5200,17 +5200,29 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1)) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM t1 JOIN ((t1 t1a)) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM (t1 t1a); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 +a +1 +2 SELECT * FROM ((t1 t1a)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 1 +a +1 +2 SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1; a t1a 1 1 diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index a5b1d95..2876e19 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -5198,17 +5198,29 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1)) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM t1 JOIN ((t1 t1a)) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM (t1 t1a); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 +a +1 +2 SELECT * FROM ((t1 t1a)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 1 +a +1 +2 SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1; a t1a 1 1 diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result index 0ea16d8..47f554a 100644 --- a/mysql-test/main/subselect_no_opts.result +++ b/mysql-test/main/subselect_no_opts.result @@ -5194,17 +5194,29 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1)) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM t1 JOIN ((t1 t1a)) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM (t1 t1a); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 +a +1 +2 SELECT * FROM ((t1 t1a)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 1 +a +1 +2 SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1; a t1a 1 1 diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index 196af2d..7096878 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -5204,17 +5204,29 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1)) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM t1 JOIN ((t1 t1a)) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM (t1 t1a); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 +a +1 +2 SELECT * FROM ((t1 t1a)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 1 +a +1 +2 SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1; a t1a 1 1 diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result index c590a5d..3265a4f 100644 --- a/mysql-test/main/subselect_no_semijoin.result +++ b/mysql-test/main/subselect_no_semijoin.result @@ -5194,17 +5194,29 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1)) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM t1 JOIN ((t1 t1a)) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM (t1 t1a); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 +a +1 +2 SELECT * FROM ((t1 t1a)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 1 +a +1 +2 SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1; a t1a 1 1 diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result index 2907536..a4dd660 100644 --- a/mysql-test/main/subselect_sj.result +++ b/mysql-test/main/subselect_sj.result @@ -1675,7 +1675,7 @@ CREATE TABLE t3 ( f11 int) ; INSERT IGNORE INTO t3 VALUES (0); SELECT alias1.f11 AS field2 FROM ( t3 AS alias2 JOIN t1 AS alias3 ON alias3.f10 = 1) -LEFT JOIN t2 AS alias1 ON alias3.f11 = 1 +LEFT JOIN ( t2 AS alias1 ) ON alias3.f11 = 1 WHERE alias2.f11 IN ( SELECT f11 FROM t2 ) GROUP BY field2 ; field2 diff --git a/mysql-test/main/subselect_sj.test b/mysql-test/main/subselect_sj.test index 22c9b2b..d735d85 100644 --- a/mysql-test/main/subselect_sj.test +++ b/mysql-test/main/subselect_sj.test @@ -1462,7 +1462,7 @@ INSERT IGNORE INTO t3 VALUES (0); SELECT alias1.f11 AS field2 FROM ( t3 AS alias2 JOIN t1 AS alias3 ON alias3.f10 = 1) -LEFT JOIN t2 AS alias1 ON alias3.f11 = 1 +LEFT JOIN ( t2 AS alias1 ) ON alias3.f11 = 1 WHERE alias2.f11 IN ( SELECT f11 FROM t2 ) GROUP BY field2 ; diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result index 697a2ae..36cca52 100644 --- a/mysql-test/main/subselect_sj_jcl6.result +++ b/mysql-test/main/subselect_sj_jcl6.result @@ -1688,7 +1688,7 @@ CREATE TABLE t3 ( f11 int) ; INSERT IGNORE INTO t3 VALUES (0); SELECT alias1.f11 AS field2 FROM ( t3 AS alias2 JOIN t1 AS alias3 ON alias3.f10 = 1) -LEFT JOIN t2 AS alias1 ON alias3.f11 = 1 +LEFT JOIN ( t2 AS alias1 ) ON alias3.f11 = 1 WHERE alias2.f11 IN ( SELECT f11 FROM t2 ) GROUP BY field2 ; field2 diff --git a/mysql-test/main/subselect_sj_mat.result b/mysql-test/main/subselect_sj_mat.result index 3fc8f9a..e28a1c5 100644 --- a/mysql-test/main/subselect_sj_mat.result +++ b/mysql-test/main/subselect_sj_mat.result @@ -2252,11 +2252,11 @@ drop database mysqltest4; # (both 1st and further executions) CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM; INSERT INTO t1 VALUES (0),(8); -SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM t1 AS t2); +SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM (t1 AS t2)); a 0 PREPARE stmt FROM " -SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM t1 AS t2) +SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM (t1 AS t2)) "; execute stmt; a diff --git a/mysql-test/main/subselect_sj_mat.test b/mysql-test/main/subselect_sj_mat.test index ac0baee..c66ca57 100644 --- a/mysql-test/main/subselect_sj_mat.test +++ b/mysql-test/main/subselect_sj_mat.test @@ -1859,9 +1859,9 @@ drop database mysqltest4; CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM; INSERT INTO t1 VALUES (0),(8); -SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM t1 AS t2); +SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM (t1 AS t2)); PREPARE stmt FROM " -SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM t1 AS t2) +SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM (t1 AS t2)) "; execute stmt; execute stmt; diff --git a/mysql-test/main/union.result b/mysql-test/main/union.result index a0421ba..559eafc 100644 --- a/mysql-test/main/union.result +++ b/mysql-test/main/union.result @@ -2034,14 +2034,14 @@ SET @@global.slow_query_log= @old_slow_query_log; CREATE TABLE t1 (a int); CREATE TABLE t2 (b int); CREATE TABLE t3 (c int); -SELECT a FROM t1 UNION SELECT b FROM t2 JOIN t3 ON ( t2.b = t3.c ); +SELECT a FROM t1 UNION SELECT b FROM t2 JOIN (t3) ON ( t2.b = t3.c ); a DROP TABLE t1, t2, t3; CREATE TABLE t1 (pk int NOT NULL); CREATE TABLE t2 (pk int NOT NULL, fk int NOT NULL); -SELECT t1.pk FROM t1 LEFT JOIN t2 ON (t1.pk = t2.fk) +SELECT t1.pk FROM t1 LEFT JOIN (t2) ON (t1.pk = t2.fk) UNION -SELECT t1.pk FROM t1 LEFT JOIN t2 ON (t1.pk = t2.fk); +SELECT t1.pk FROM t1 LEFT JOIN (t2) ON (t1.pk = t2.fk); pk DROP TABLE t1,t2; create table t1 (a int); diff --git a/mysql-test/main/union.test b/mysql-test/main/union.test index 9d25708..ce52eba 100644 --- a/mysql-test/main/union.test +++ b/mysql-test/main/union.test @@ -1371,15 +1371,15 @@ SET @@global.slow_query_log= @old_slow_query_log; CREATE TABLE t1 (a int); CREATE TABLE t2 (b int); CREATE TABLE t3 (c int); -SELECT a FROM t1 UNION SELECT b FROM t2 JOIN t3 ON ( t2.b = t3.c ); +SELECT a FROM t1 UNION SELECT b FROM t2 JOIN (t3) ON ( t2.b = t3.c ); DROP TABLE t1, t2, t3; CREATE TABLE t1 (pk int NOT NULL); CREATE TABLE t2 (pk int NOT NULL, fk int NOT NULL); -SELECT t1.pk FROM t1 LEFT JOIN t2 ON (t1.pk = t2.fk) +SELECT t1.pk FROM t1 LEFT JOIN (t2) ON (t1.pk = t2.fk) UNION -SELECT t1.pk FROM t1 LEFT JOIN t2 ON (t1.pk = t2.fk); +SELECT t1.pk FROM t1 LEFT JOIN (t2) ON (t1.pk = t2.fk); DROP TABLE t1,t2; diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index 83ea0e3..fdaf0ba 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -4104,7 +4104,7 @@ LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 ; SELECT 1 -FROM ( SELECT 1 +FROM (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4112,8 +4112,8 @@ LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t1 -LEFT OUTER JOIN ( SELECT 1 +) t1) +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4121,8 +4121,8 @@ LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t2 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t2) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4130,8 +4130,8 @@ LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t3 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t3) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4139,8 +4139,8 @@ LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t4 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t4) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4148,8 +4148,8 @@ LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t5 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t5) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4157,8 +4157,8 @@ LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t6 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t6) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4166,8 +4166,8 @@ LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t7 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t7) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4175,18 +4175,18 @@ LEFT JOIN t4 d_alias_1 ON d_alias_1.d1 = a_alias_1.a1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t8 ON 1=1 +) t8) ON 1=1 ; 1 SELECT 1 -FROM v1 t1 -LEFT OUTER JOIN v1 t2 ON 1=1 -LEFT OUTER JOIN v1 t3 ON 1=1 -LEFT OUTER JOIN v1 t4 ON 1=1 -LEFT OUTER JOIN v1 t5 ON 1=1 -LEFT OUTER JOIN v1 t6 ON 1=1 -LEFT OUTER JOIN v1 t7 ON 1=1 -LEFT OUTER JOIN v1 t8 ON 1=1 +FROM (v1 t1) +LEFT OUTER JOIN (v1 t2) ON 1=1 +LEFT OUTER JOIN (v1 t3) ON 1=1 +LEFT OUTER JOIN (v1 t4) ON 1=1 +LEFT OUTER JOIN (v1 t5) ON 1=1 +LEFT OUTER JOIN (v1 t6) ON 1=1 +LEFT OUTER JOIN (v1 t7) ON 1=1 +LEFT OUTER JOIN (v1 t8) ON 1=1 ; 1 drop view v1; diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test index 2b7b9d1..ce8ac8d 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -4050,7 +4050,7 @@ CREATE OR REPLACE view v1 AS ; SELECT 1 -FROM ( SELECT 1 +FROM (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4058,8 +4058,8 @@ FROM ( SELECT 1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t1 -LEFT OUTER JOIN ( SELECT 1 +) t1) +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4067,8 +4067,8 @@ LEFT OUTER JOIN ( SELECT 1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t2 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t2) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4076,8 +4076,8 @@ LEFT OUTER JOIN ( SELECT 1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t3 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t3) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4085,8 +4085,8 @@ LEFT OUTER JOIN ( SELECT 1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t4 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t4) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4094,8 +4094,8 @@ LEFT OUTER JOIN ( SELECT 1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t5 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t5) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4103,8 +4103,8 @@ LEFT OUTER JOIN ( SELECT 1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t6 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t6) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4112,8 +4112,8 @@ LEFT OUTER JOIN ( SELECT 1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t7 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t7) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4121,18 +4121,18 @@ LEFT OUTER JOIN ( SELECT 1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t8 ON 1=1 +) t8) ON 1=1 ; SELECT 1 -FROM v1 t1 -LEFT OUTER JOIN v1 t2 ON 1=1 -LEFT OUTER JOIN v1 t3 ON 1=1 -LEFT OUTER JOIN v1 t4 ON 1=1 -LEFT OUTER JOIN v1 t5 ON 1=1 -LEFT OUTER JOIN v1 t6 ON 1=1 -LEFT OUTER JOIN v1 t7 ON 1=1 -LEFT OUTER JOIN v1 t8 ON 1=1 +FROM (v1 t1) +LEFT OUTER JOIN (v1 t2) ON 1=1 +LEFT OUTER JOIN (v1 t3) ON 1=1 +LEFT OUTER JOIN (v1 t4) ON 1=1 +LEFT OUTER JOIN (v1 t5) ON 1=1 +LEFT OUTER JOIN (v1 t6) ON 1=1 +LEFT OUTER JOIN (v1 t7) ON 1=1 +LEFT OUTER JOIN (v1 t8) ON 1=1 ; drop view v1; diff --git a/mysql-test/suite/innodb/t/innodb.test b/mysql-test/suite/innodb/t/innodb.test index 0062ba7..ab12cac 100644 --- a/mysql-test/suite/innodb/t/innodb.test +++ b/mysql-test/suite/innodb/t/innodb.test @@ -1253,7 +1253,7 @@ CREATE TABLE t2 (b_id tinyint(4) NOT NULL default '0',b_a tinyint(4) NOT NULL de CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=latin1; --enable_warnings INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2); -SELECT * FROM (SELECT t1.*,GROUP_CONCAT(t2.b_id SEPARATOR ',') as b_list FROM (t1 LEFT JOIN t2 on t1.a_id = t2.b_a) GROUP BY t1.a_id ) AS xyz; +SELECT * FROM (SELECT t1.*,GROUP_CONCAT(t2.b_id SEPARATOR ',') as b_list FROM (t1 LEFT JOIN (t2) on t1.a_id = t2.b_a) GROUP BY t1.a_id ) AS xyz; DROP TABLE t2; DROP TABLE t1; diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index d09aa85..23faa57 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -1932,7 +1932,8 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %type <table_list> join_table_list join_table table_factor table_ref esc_table_ref - table_primary_ident table_primary_derived + table_primary_ident table_primary_ident_opt_parens + table_primary_derived table_primary_derived_opt_parens derived_table_list table_reference_list_parens nested_table_reference_list join_table_parens update_table_list @@ -12059,12 +12060,22 @@ use_partition: ; table_factor: - table_primary_ident { $$= $1; } - | table_primary_derived { $$= $1; } + table_primary_ident_opt_parens { $$= $1; } + | table_primary_derived_opt_parens { $$= $1; } | join_table_parens { $$= $1; } | table_reference_list_parens { $$= $1; } ; +table_primary_ident_opt_parens: + table_primary_ident { $$= $1; } + | '(' table_primary_ident_opt_parens ')' { $$= $2; } + ; + +table_primary_derived_opt_parens: + table_primary_derived { $$= $1; } + | '(' table_primary_derived_opt_parens ')' { $$= $2; } + ; + table_reference_list_parens: '(' table_reference_list_parens ')' { $$= $2; } | '(' nested_table_reference_list ')' diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index 333bca4..88b40b9 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -1433,7 +1433,8 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %type <table_list> join_table_list join_table table_factor table_ref esc_table_ref - table_primary_ident table_primary_derived + table_primary_ident table_primary_ident_opt_parens + table_primary_derived table_primary_derived_opt_parens derived_table_list table_reference_list_parens nested_table_reference_list join_table_parens update_table_list @@ -12181,12 +12182,22 @@ use_partition: ; table_factor: - table_primary_ident { $$= $1; } - | table_primary_derived { $$= $1; } + table_primary_ident_opt_parens { $$= $1; } + | table_primary_derived_opt_parens { $$= $1; } | join_table_parens { $$= $1; } | table_reference_list_parens { $$= $1; } ; +table_primary_ident_opt_parens: + table_primary_ident { $$= $1; } + | '(' table_primary_ident_opt_parens ')' { $$= $2; } + ; + +table_primary_derived_opt_parens: + table_primary_derived { $$= $1; } + | '(' table_primary_derived_opt_parens ')' { $$= $2; } + ; + table_reference_list_parens: '(' table_reference_list_parens ')' { $$= $2; } | '(' nested_table_reference_list ')'

1 0

[Commits] 9d2b25b: MDEV-18689 Simple query with extra brackets stopped working
by IgorBabaev 06 May '19

06 May '19

revision-id: 9d2b25b90d95c825fa74ca94c5157e2f2c66ea1e (mariadb-10.4.4-66-g9d2b25b) parent(s): d18ef804bb5d9d473055a2fdc04f74e175a8e9cd author: Igor Babaev committer: Igor Babaev timestamp: 2019-05-05 20:10:23 -0700 message: MDEV-18689 Simple query with extra brackets stopped working Parenthesis around table names and derived tables should be allowed in FROM clauses and some other context as it was in earlier versions. Returned test queries that used such parenthesis in 10.3 to their original form. Adjusted test results accordingly. --- mysql-test/main/brackets.result | 42 +++++++++++++++++++++ mysql-test/main/brackets.test | 22 +++++++++++ mysql-test/main/subselect.result | 24 +++++++++--- mysql-test/main/subselect.test | 4 -- mysql-test/main/subselect_no_exists_to_in.result | 24 +++++++++--- mysql-test/main/subselect_no_mat.result | 24 +++++++++--- mysql-test/main/subselect_no_opts.result | 24 +++++++++--- mysql-test/main/subselect_no_scache.result | 24 +++++++++--- mysql-test/main/subselect_no_semijoin.result | 24 +++++++++--- mysql-test/main/subselect_sj.test | 2 +- mysql-test/main/subselect_sj_mat.test | 4 +- mysql-test/main/union.test | 6 +-- mysql-test/main/view.test | 48 ++++++++++++------------ mysql-test/suite/innodb/t/innodb.test | 2 +- sql/sql_yacc.yy | 17 +++++++-- sql/sql_yacc_ora.yy | 17 +++++++-- 16 files changed, 231 insertions(+), 77 deletions(-) diff --git a/mysql-test/main/brackets.result b/mysql-test/main/brackets.result index 869afe5..e14bef9 100644 --- a/mysql-test/main/brackets.result +++ b/mysql-test/main/brackets.result @@ -452,4 +452,46 @@ EXPLAIN } } drop table t1; +# +# MDEV-18689: parenthesis around table names and derived tables +# +select * from ( mysql.db ); +Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv Delete_history_priv +% test Y Y Y Y Y Y N Y Y Y Y Y Y Y Y N N Y Y Y +% test\_% Y Y Y Y Y Y N Y Y Y Y Y Y Y Y N N Y Y Y +create table t1 (a int); +insert into t1 values (7), (2), (7); +select * from (t1); +a +7 +2 +7 +select * from ((t1)); +a +7 +2 +7 +select * from (t1 t) where t.a > 5; +a +7 +7 +select * from ((t1 t)) where t.a > 5; +a +7 +7 +select * from ((select a, sum(a) from t1 group by a) t); +a sum(a) +2 2 +7 14 +select * from (((select a, sum(a) from t1 group by a) t)); +a sum(a) +2 2 +7 14 +update (t1 t) set t.a=t.a+1; +select * from t1; +a +8 +3 +8 +drop table t1; # End of 10.4 tests diff --git a/mysql-test/main/brackets.test b/mysql-test/main/brackets.test index cf1dcc5..9ca86b8 100644 --- a/mysql-test/main/brackets.test +++ b/mysql-test/main/brackets.test @@ -154,5 +154,27 @@ eval explain format=json $q; drop table t1; +--echo # +--echo # MDEV-18689: parenthesis around table names and derived tables +--echo # + +select * from ( mysql.db ); + +create table t1 (a int); +insert into t1 values (7), (2), (7); + +select * from (t1); +select * from ((t1)); +select * from (t1 t) where t.a > 5; +select * from ((t1 t)) where t.a > 5; + +select * from ((select a, sum(a) from t1 group by a) t); +select * from (((select a, sum(a) from t1 group by a) t)); + +update (t1 t) set t.a=t.a+1; +select * from t1; + +drop table t1; + --echo # End of 10.4 tests diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index 3bd23a4..8c8c034 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -5198,17 +5198,29 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1)) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM t1 JOIN ((t1 t1a)) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM (t1 t1a); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 +a +1 +2 SELECT * FROM ((t1 t1a)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 1 +a +1 +2 SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1; a t1a 1 1 diff --git a/mysql-test/main/subselect.test b/mysql-test/main/subselect.test index dd80b78..82823b4 100644 --- a/mysql-test/main/subselect.test +++ b/mysql-test/main/subselect.test @@ -4325,14 +4325,10 @@ SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; --error ER_PARSE_ERROR SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; ---error ER_PARSE_ERROR SELECT * FROM t1 JOIN (t1 t1a) ON 1; ---error ER_PARSE_ERROR SELECT * FROM t1 JOIN ((t1 t1a)) ON 1; ---error ER_PARSE_ERROR SELECT * FROM (t1 t1a); ---error ER_PARSE_ERROR SELECT * FROM ((t1 t1a)); SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1; diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result index bacba84..1bcc1a5 100644 --- a/mysql-test/main/subselect_no_exists_to_in.result +++ b/mysql-test/main/subselect_no_exists_to_in.result @@ -5200,17 +5200,29 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1)) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM t1 JOIN ((t1 t1a)) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM (t1 t1a); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 +a +1 +2 SELECT * FROM ((t1 t1a)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 1 +a +1 +2 SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1; a t1a 1 1 diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index a5b1d95..2876e19 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -5198,17 +5198,29 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1)) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM t1 JOIN ((t1 t1a)) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM (t1 t1a); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 +a +1 +2 SELECT * FROM ((t1 t1a)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 1 +a +1 +2 SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1; a t1a 1 1 diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result index 0ea16d8..47f554a 100644 --- a/mysql-test/main/subselect_no_opts.result +++ b/mysql-test/main/subselect_no_opts.result @@ -5194,17 +5194,29 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1)) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM t1 JOIN ((t1 t1a)) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM (t1 t1a); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 +a +1 +2 SELECT * FROM ((t1 t1a)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 1 +a +1 +2 SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1; a t1a 1 1 diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index 196af2d..7096878 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -5204,17 +5204,29 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1)) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM t1 JOIN ((t1 t1a)) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM (t1 t1a); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 +a +1 +2 SELECT * FROM ((t1 t1a)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 1 +a +1 +2 SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1; a t1a 1 1 diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result index c590a5d..3265a4f 100644 --- a/mysql-test/main/subselect_no_semijoin.result +++ b/mysql-test/main/subselect_no_semijoin.result @@ -5194,17 +5194,29 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp SELECT * FROM t1 JOIN ((t1 t1a UNION SELECT 1)) ON 1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION SELECT 1)) ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN ((t1 t1a)) t1a ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) t1a ON 1' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't1a ON 1' at line 1 SELECT * FROM t1 JOIN (t1 t1a) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM t1 JOIN ((t1 t1a)) ON 1; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) ON 1' at line 1 +a a +1 1 +2 1 +1 2 +2 2 SELECT * FROM (t1 t1a); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 +a +1 +2 SELECT * FROM ((t1 t1a)); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 1 +a +1 +2 SELECT * FROM t1 JOIN (SELECT 1 t1a) alias ON 1; a t1a 1 1 diff --git a/mysql-test/main/subselect_sj.test b/mysql-test/main/subselect_sj.test index 22c9b2b..d735d85 100644 --- a/mysql-test/main/subselect_sj.test +++ b/mysql-test/main/subselect_sj.test @@ -1462,7 +1462,7 @@ INSERT IGNORE INTO t3 VALUES (0); SELECT alias1.f11 AS field2 FROM ( t3 AS alias2 JOIN t1 AS alias3 ON alias3.f10 = 1) -LEFT JOIN t2 AS alias1 ON alias3.f11 = 1 +LEFT JOIN ( t2 AS alias1 ) ON alias3.f11 = 1 WHERE alias2.f11 IN ( SELECT f11 FROM t2 ) GROUP BY field2 ; diff --git a/mysql-test/main/subselect_sj_mat.test b/mysql-test/main/subselect_sj_mat.test index ac0baee..c66ca57 100644 --- a/mysql-test/main/subselect_sj_mat.test +++ b/mysql-test/main/subselect_sj_mat.test @@ -1859,9 +1859,9 @@ drop database mysqltest4; CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM; INSERT INTO t1 VALUES (0),(8); -SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM t1 AS t2); +SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM (t1 AS t2)); PREPARE stmt FROM " -SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM t1 AS t2) +SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM (t1 AS t2)) "; execute stmt; execute stmt; diff --git a/mysql-test/main/union.test b/mysql-test/main/union.test index 9d25708..ce52eba 100644 --- a/mysql-test/main/union.test +++ b/mysql-test/main/union.test @@ -1371,15 +1371,15 @@ SET @@global.slow_query_log= @old_slow_query_log; CREATE TABLE t1 (a int); CREATE TABLE t2 (b int); CREATE TABLE t3 (c int); -SELECT a FROM t1 UNION SELECT b FROM t2 JOIN t3 ON ( t2.b = t3.c ); +SELECT a FROM t1 UNION SELECT b FROM t2 JOIN (t3) ON ( t2.b = t3.c ); DROP TABLE t1, t2, t3; CREATE TABLE t1 (pk int NOT NULL); CREATE TABLE t2 (pk int NOT NULL, fk int NOT NULL); -SELECT t1.pk FROM t1 LEFT JOIN t2 ON (t1.pk = t2.fk) +SELECT t1.pk FROM t1 LEFT JOIN (t2) ON (t1.pk = t2.fk) UNION -SELECT t1.pk FROM t1 LEFT JOIN t2 ON (t1.pk = t2.fk); +SELECT t1.pk FROM t1 LEFT JOIN (t2) ON (t1.pk = t2.fk); DROP TABLE t1,t2; diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test index 2b7b9d1..ce8ac8d 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -4050,7 +4050,7 @@ CREATE OR REPLACE view v1 AS ; SELECT 1 -FROM ( SELECT 1 +FROM (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4058,8 +4058,8 @@ FROM ( SELECT 1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t1 -LEFT OUTER JOIN ( SELECT 1 +) t1) +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4067,8 +4067,8 @@ LEFT OUTER JOIN ( SELECT 1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t2 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t2) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4076,8 +4076,8 @@ LEFT OUTER JOIN ( SELECT 1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t3 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t3) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4085,8 +4085,8 @@ LEFT OUTER JOIN ( SELECT 1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t4 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t4) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4094,8 +4094,8 @@ LEFT OUTER JOIN ( SELECT 1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t5 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t5) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4103,8 +4103,8 @@ LEFT OUTER JOIN ( SELECT 1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t6 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t6) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4112,8 +4112,8 @@ LEFT OUTER JOIN ( SELECT 1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t7 ON 1=1 -LEFT OUTER JOIN ( SELECT 1 +) t7) ON 1=1 +LEFT OUTER JOIN (( SELECT 1 FROM t1 a_alias_1 LEFT JOIN (t2 b_alias_1 JOIN t1 a_alias_2) ON b_alias_1.b1 = a_alias_1.a1 AND a_alias_2.a1 = a_alias_1.a1 LEFT JOIN t3 c_alias_1 ON c_alias_1.c1 = a_alias_1.a1 @@ -4121,18 +4121,18 @@ LEFT OUTER JOIN ( SELECT 1 LEFT JOIN t3 c_alias_2 ON c_alias_2.c1 = a_alias_1.a1 LEFT JOIN t5 e_alias_1 ON e_alias_1.e1 = a_alias_1.a1 LEFT JOIN t6 f_alias_1 ON f_alias_1.f1 = a_alias_1.a1 -) t8 ON 1=1 +) t8) ON 1=1 ; SELECT 1 -FROM v1 t1 -LEFT OUTER JOIN v1 t2 ON 1=1 -LEFT OUTER JOIN v1 t3 ON 1=1 -LEFT OUTER JOIN v1 t4 ON 1=1 -LEFT OUTER JOIN v1 t5 ON 1=1 -LEFT OUTER JOIN v1 t6 ON 1=1 -LEFT OUTER JOIN v1 t7 ON 1=1 -LEFT OUTER JOIN v1 t8 ON 1=1 +FROM (v1 t1) +LEFT OUTER JOIN (v1 t2) ON 1=1 +LEFT OUTER JOIN (v1 t3) ON 1=1 +LEFT OUTER JOIN (v1 t4) ON 1=1 +LEFT OUTER JOIN (v1 t5) ON 1=1 +LEFT OUTER JOIN (v1 t6) ON 1=1 +LEFT OUTER JOIN (v1 t7) ON 1=1 +LEFT OUTER JOIN (v1 t8) ON 1=1 ; drop view v1; diff --git a/mysql-test/suite/innodb/t/innodb.test b/mysql-test/suite/innodb/t/innodb.test index 0062ba7..ab12cac 100644 --- a/mysql-test/suite/innodb/t/innodb.test +++ b/mysql-test/suite/innodb/t/innodb.test @@ -1253,7 +1253,7 @@ CREATE TABLE t2 (b_id tinyint(4) NOT NULL default '0',b_a tinyint(4) NOT NULL de CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=latin1; --enable_warnings INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2); -SELECT * FROM (SELECT t1.*,GROUP_CONCAT(t2.b_id SEPARATOR ',') as b_list FROM (t1 LEFT JOIN t2 on t1.a_id = t2.b_a) GROUP BY t1.a_id ) AS xyz; +SELECT * FROM (SELECT t1.*,GROUP_CONCAT(t2.b_id SEPARATOR ',') as b_list FROM (t1 LEFT JOIN (t2) on t1.a_id = t2.b_a) GROUP BY t1.a_id ) AS xyz; DROP TABLE t2; DROP TABLE t1; diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index d09aa85..23faa57 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -1932,7 +1932,8 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %type <table_list> join_table_list join_table table_factor table_ref esc_table_ref - table_primary_ident table_primary_derived + table_primary_ident table_primary_ident_opt_parens + table_primary_derived table_primary_derived_opt_parens derived_table_list table_reference_list_parens nested_table_reference_list join_table_parens update_table_list @@ -12059,12 +12060,22 @@ use_partition: ; table_factor: - table_primary_ident { $$= $1; } - | table_primary_derived { $$= $1; } + table_primary_ident_opt_parens { $$= $1; } + | table_primary_derived_opt_parens { $$= $1; } | join_table_parens { $$= $1; } | table_reference_list_parens { $$= $1; } ; +table_primary_ident_opt_parens: + table_primary_ident { $$= $1; } + | '(' table_primary_ident_opt_parens ')' { $$= $2; } + ; + +table_primary_derived_opt_parens: + table_primary_derived { $$= $1; } + | '(' table_primary_derived_opt_parens ')' { $$= $2; } + ; + table_reference_list_parens: '(' table_reference_list_parens ')' { $$= $2; } | '(' nested_table_reference_list ')' diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index 333bca4..88b40b9 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -1433,7 +1433,8 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %type <table_list> join_table_list join_table table_factor table_ref esc_table_ref - table_primary_ident table_primary_derived + table_primary_ident table_primary_ident_opt_parens + table_primary_derived table_primary_derived_opt_parens derived_table_list table_reference_list_parens nested_table_reference_list join_table_parens update_table_list @@ -12181,12 +12182,22 @@ use_partition: ; table_factor: - table_primary_ident { $$= $1; } - | table_primary_derived { $$= $1; } + table_primary_ident_opt_parens { $$= $1; } + | table_primary_derived_opt_parens { $$= $1; } | join_table_parens { $$= $1; } | table_reference_list_parens { $$= $1; } ; +table_primary_ident_opt_parens: + table_primary_ident { $$= $1; } + | '(' table_primary_ident_opt_parens ')' { $$= $2; } + ; + +table_primary_derived_opt_parens: + table_primary_derived { $$= $1; } + | '(' table_primary_derived_opt_parens ')' { $$= $2; } + ; + table_reference_list_parens: '(' table_reference_list_parens ')' { $$= $2; } | '(' nested_table_reference_list ')'

1 0