revision-id: cb6aade130b75f3dc4b7c3a1dd4d33864adffd90 (mariadb-10.3.6-43-gcb6aade130b) parent(s): f79c5a658cc33a10d7744a748a4328254e2cbaf7 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-04-24 12:04:59 +0200 message: MDEV-13695: INTERSECT precedence is not in line with Oracle even in SQL_MODE=Oracle Switch off automatic INTERSECT priority for ORACLE MODE --- mysql-test/main/intersect.result | 60 ++++++++++++++++++++++++++++++++++++++++ mysql-test/main/intersect.test | 38 +++++++++++++++++++++++++ sql/sql_yacc.yy | 3 +- 3 files changed, 100 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/intersect.result b/mysql-test/main/intersect.result index b589e8bd17e..66c7addfd36 100644 --- a/mysql-test/main/intersect.result +++ b/mysql-test/main/intersect.result @@ -607,6 +607,22 @@ NULL INTERSECT RESULT <intersect2,4> ALL NULL NULL NULL NULL NULL NULL NULL UNION RESULT <union1,3,5> ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 (/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union /* select#3 */ select `__3`.`c` AS `c`,`__3`.`d` AS `d` from ((/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect (/* select#4 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__3` union (/* select#5 */ select 4 AS `4`,4 AS `4`) +set SQL_MODE=ORACLE; +(select a,b from t1) union (select c,d from t2) intersect (select e,f from t3) union (select 4,4); +a b +3 3 +4 4 +explain extended +(select a,b from t1) union (select c,d from t2) intersect (select e,f from t3) union (select 4,4); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 +2 UNION t2 ALL NULL NULL NULL NULL 2 100.00 +3 INTERSECT t3 ALL NULL NULL NULL NULL 2 100.00 +4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNIT RESULT <unit1,2,3,4> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1") union (/* select#2 */ select "test"."t2"."c" AS "c","test"."t2"."d" AS "d" from "test"."t2") intersect (/* select#3 */ select "test"."t3"."e" AS "e","test"."t3"."f" AS "f" from "test"."t3") union (/* select#4 */ select 4 AS "4",4 AS "4") +set SQL_MODE=default; (select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4); e f 3 3 @@ -623,6 +639,24 @@ id select_type table type possible_keys key key_len ref rows filtered Extra NULL UNIT RESULT <unit1,2,3,4> ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 (/* select#1 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`) intersect (/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) union (/* select#3 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union (/* select#4 */ select 4 AS `4`,4 AS `4`) +set SQL_MODE=ORACLE; +(select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4); +e f +3 3 +4 4 +5 5 +6 6 +explain extended +(select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 +2 INTERSECT t2 ALL NULL NULL NULL NULL 2 100.00 +3 UNION t1 ALL NULL NULL NULL NULL 2 100.00 +4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNIT RESULT <unit1,2,3,4> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (/* select#1 */ select "test"."t3"."e" AS "e","test"."t3"."f" AS "f" from "test"."t3") intersect (/* select#2 */ select "test"."t2"."c" AS "c","test"."t2"."d" AS "d" from "test"."t2") union (/* select#3 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1") union (/* select#4 */ select 4 AS "4",4 AS "4") +set SQL_MODE=default; (/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union /* select#3 */ select `__3`.`c` AS `c`,`__3`.`d` AS `d` from ((/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect (/* select#4 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__3` union (/* select#5 */ select 4 AS `4`,4 AS `4`); a b 3 3 @@ -772,4 +806,30 @@ SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b count(*) 14848 drop table t1,t2,t3; +# +# MDEV-13695: INTERSECT precedence is not in line with Oracle even +# in SQL_MODE=Oracle +# +create table t12(c1 int); +insert into t12 values(1); +insert into t12 values(2); +create table t13(c1 int); +insert into t13 values(1); +insert into t13 values(3); +create table t234(c1 int); +insert into t234 values(2); +insert into t234 values(3); +insert into t234 values(4); +set SQL_MODE=oracle; +select * from t13 union select * from t234 intersect select * from t12; +c1 +1 +2 +set SQL_MODE=default; +select * from t13 union select * from t234 intersect select * from t12; +c1 +1 +2 +3 +drop table t12,t13,t234; # End of 10.3 tests diff --git a/mysql-test/main/intersect.test b/mysql-test/main/intersect.test index d9d420c786b..fb5e991a24c 100644 --- a/mysql-test/main/intersect.test +++ b/mysql-test/main/intersect.test @@ -147,12 +147,25 @@ insert into t3 values (1,1),(3,3); (select a,b from t1) union (select c,d from t2) intersect (select e,f from t3) union (select 4,4); explain extended (select a,b from t1) union (select c,d from t2) intersect (select e,f from t3) union (select 4,4); +set SQL_MODE=ORACLE; +--sorted_result +(select a,b from t1) union (select c,d from t2) intersect (select e,f from t3) union (select 4,4); +explain extended +(select a,b from t1) union (select c,d from t2) intersect (select e,f from t3) union (select 4,4); +set SQL_MODE=default; + # test result of linear mix operation --sorted_result (select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4); explain extended (select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4); +set SQL_MODE=ORACLE; +--sorted_result +(select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4); +explain extended +(select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4); +set SQL_MODE=default; --sorted_result (/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union /* select#3 */ select `__3`.`c` AS `c`,`__3`.`d` AS `d` from ((/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect (/* select#4 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__3` union (/* select#5 */ select 4 AS `4`,4 AS `4`); @@ -282,4 +295,29 @@ select count(*) from ( drop table t1,t2,t3; +--echo # +--echo # MDEV-13695: INTERSECT precedence is not in line with Oracle even +--echo # in SQL_MODE=Oracle +--echo # + +create table t12(c1 int); +insert into t12 values(1); +insert into t12 values(2); +create table t13(c1 int); +insert into t13 values(1); +insert into t13 values(3); +create table t234(c1 int); +insert into t234 values(2); +insert into t234 values(3); +insert into t234 values(4); + +set SQL_MODE=oracle; +--sorted_result +select * from t13 union select * from t234 intersect select * from t12; +set SQL_MODE=default; +--sorted_result +select * from t13 union select * from t234 intersect select * from t12; + +drop table t12,t13,t234; + --echo # End of 10.3 tests diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 93704cda06d..e1f3d15e514 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -546,7 +546,8 @@ bool LEX::add_select_to_union_list(bool is_union_distinct, as possible */ if (type == INTERSECT_TYPE && (current_select->linkage != INTERSECT_TYPE && - current_select != current_select->master_unit()->first_select())) + current_select != current_select->master_unit()->first_select()) + && !(thd->variables.sql_mode & MODE_ORACLE)) { /* This and previous SELECTs should go one level down because of