diff options
23 files changed, 265 insertions, 111 deletions
diff --git a/mysql-test/main/brackets.result b/mysql-test/main/brackets.result index 869afe56c9c..e14bef956a9 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 cf1dcc56acc..9ca86b87032 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 3bd23a49cdb..8c8c034c7cc 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 dd80b789516..82823b418ef 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 482833dc967..8ffe6643670 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 bacba844deb..1bcc1a5ecb3 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 a5b1d95cae1..2876e190e68 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 0ea16d89849..47f554a78ef 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 196af2dc372..70968786fd0 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 c590a5d3450..3265a4f81bb 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 2907536df02..a4dd6601f48 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 22c9b2bf0cf..d735d852a5e 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 697a2ae36b6..36cca52b534 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 3fc8f9afd3e..e28a1c57efc 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 ac0baee3728..c66ca575de7 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 a0421bae922..559eafc2282 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 9d25708611e..ce52ebaa6e5 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 83ea0e38992..fdaf0baa02a 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 2b7b9d1f161..ce8ac8dec48 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 39130049c33..e2937fe2073 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 0062ba72add..ab12cac5ea1 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 d09aa85d402..23faa57ad61 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 333bca4af86..88b40b915d9 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 ')' |