diff options
Diffstat (limited to 'mysql-test/main/intersect.result')
| -rw-r--r-- | mysql-test/main/intersect.result | 51 |
1 files changed, 42 insertions, 9 deletions
diff --git a/mysql-test/main/intersect.result b/mysql-test/main/intersect.result index 62ab0b7e5de..5990fcce110 100644 --- a/mysql-test/main/intersect.result +++ b/mysql-test/main/intersect.result @@ -501,7 +501,7 @@ a 1 1 (select 1 from dual into @v) intersect (select 1 from dual); -ERROR HY000: Incorrect usage of INTERSECT and INTO +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 'into @v) intersect (select 1 from dual)' at line 1 select 1 from dual ORDER BY 1 intersect select 1 from dual; 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 'intersect select 1 from dual' at line 1 select 1 as a from dual union all select 1 from dual; @@ -509,7 +509,7 @@ a 1 1 select 1 from dual intersect all select 1 from dual; -ERROR HY000: Incorrect usage of INTERSECT and ALL +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 'all select 1 from dual' at line 1 create table t1 (a int, b blob, a1 int, b1 blob); create table t2 (c int, d blob, c1 int, d1 blob); insert into t1 values (1,"ddd", 1, "sdfrrwwww"),(2, "fgh", 2, "dffggtt"); @@ -603,14 +603,14 @@ 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 -3 UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 +5 UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 -4 INTERSECT t3 ALL NULL NULL NULL NULL 2 100.00 -NULL INTERSECT RESULT <intersect2,4> ALL NULL NULL NULL NULL NULL NULL -5 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used -NULL UNION RESULT <union1,3,5> ALL NULL NULL NULL NULL NULL NULL +3 INTERSECT t3 ALL NULL NULL NULL NULL 2 100.00 +NULL INTERSECT RESULT <intersect2,3> ALL NULL NULL NULL NULL NULL NULL +4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,5,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#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`) +Note 1003 (/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union /* select#5 */ select `__5`.`c` AS `c`,`__5`.`d` AS `d` from ((/* 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`)) `__5` union (/* select#4 */ 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 @@ -724,7 +724,7 @@ a b drop procedure p1; 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 (select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union select `__3`.`c` AS `c`,`__3`.`d` AS `d` from ((select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect (select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__3` union (select 4 AS `4`,4 AS `4`) latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union select `__6`.`c` AS `c`,`__6`.`d` AS `d` from ((select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect (select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__6` union (select 4 AS `4`,4 AS `4`) latin1 latin1_swedish_ci drop view v1; drop tables t1,t2,t3; # @@ -837,3 +837,36 @@ c1 3 drop table t12,t13,t234; # End of 10.3 tests +# +# MDEV-18701: Wrong result from query that uses INTERSECT after UNION ALL +# +create table t1 (a int); +insert into t1 values (3), (1), (7), (3), (2), (7), (4); +create table t2 (a int); +insert into t2 values (4), (5), (9), (1), (8), (9); +create table t3 (a int); +insert into t3 values (8), (1), (8), (2), (3), (7), (2); +select * from t1 where a > 4 +union all +select * from t2 where a < 5 +intersect +select * from t3 where a < 5; +a +7 +7 +1 +explain extended +select * from t1 where a > 4 +union all +select * from t2 where a < 5 +intersect +select * from t3 where a < 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using where +4 UNION <derived2> ALL NULL NULL NULL NULL 6 100.00 +2 DERIVED t2 ALL NULL NULL NULL NULL 6 100.00 Using where +3 INTERSECT t3 ALL NULL NULL NULL NULL 7 100.00 Using where +NULL INTERSECT RESULT <intersect2,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 union all /* select#4 */ select `__4`.`a` AS `a` from (/* select#2 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where `test`.`t2`.`a` < 5 intersect /* select#3 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where `test`.`t3`.`a` < 5) `__4` +drop table t1,t2,t3; |