mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-08-16 07:21:32 +02:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/intersect_all.result
Aleksey Midenkov 0b2434d2e9 MDEV-25158 Segfault on INTERSECT ALL with UNION in Oracle mode 
Oracle mode has different set operator precedence and handling (not by
standard). In Oracle mode the below test case is handled as-is, in
plain order from left to right. In MariaDB default mode follows SQL
standard and makes INTERSECT prioritized, so UNION is taken from
derived table which is INTERSECT result (here and below the same
applies for EXCEPT).

Non-distinct set operator (UNION ALL/INTERSECT ALL) works via unique
key release but it can be done only once. We cannot add index to
non-empty heap table (see heap_enable_indexes()). So every UNION ALL
before rightmost UNION DISTINCT works as UNION DISTINCT. That is
common syntax, MySQL, MSSQL and Oracle work that way.

There is union_distinct property which indicates the rightmost
distinct UNION (at least, so the algorithm works simple: it releases
the unique key after union_distinct in the loop
(st_select_lex_unit::exec()).

INTERSECT ALL code (implemented by MDEV-18844 in a896beb) does not
know about Oracle mode and treats union_distinct as the last
operation, that's why it releases unique key on union_distinct
operation. INTERSECT ALL requires unique key for it to work, so before
any INTERSECT ALL unique key must not be released (see
select_unit_ext::send_data()).

The patch tweaks INTERSECT ALL code for Oracle mode. In
disable_index_if_needed() it does not allow unique key release before
the last operation and it allows unfold on the last operation. Test
case with UNION DISTINCT following INTERSECT ALL at least does not
include invalid data, but in fact the whole INTERSECT ALL code could
be refactored for better semantical triggers.

The patch fixes typo in st_select_lex_unit::prepare() where
have_except_all_or_intersect_all masked eponymous data member which
wrongly triggered unique key release in st_select_lex_unit::prepare().

The patch fixes unknown error in case ha_disable_indexes() fails.

Note: optimize_bag_operation() does some operator substitutions, but
it does not run under PS. So if there is difference in test with --ps
that means non-optimized (have_except_all_or_intersect_all == true)
code path is not good.

Note 2: VIEW is stored and executed in normal mode (see
Sql_mode_save_for_frm_handling) hence when SELECT order is different
in Oracle mode (defined by parsed_select_expr_cont()) it must be
covered by --disable_view_protocol.
2025-05-29 12:34:06 +03:00

1032 lines
34 KiB
Text
Raw Permalink Blame History

create table t1 (a int, b int) engine=MyISAM;
create table t2 (c int, d int) engine=MyISAM;
insert into t1 values (1,1),(2,2),(3,3),(2,2);
insert into t2 values (2,2),(2,2),(5,5);
select * from t1 intersect all select * from t2;
a b
2 2
2 2
(select a,b from t1) intersect all (select c,d from t2);
a b
2 2
2 2
select * from ((select a,b from t1) intersect all (select c,d from t2)) t;
a b
2 2
2 2
select * from ((select a from t1) intersect all (select c from t2)) t;
a
2
2
drop tables t1,t2;
create table t1 (a int, b int) engine=MyISAM;
create table t2 (c int, d int) engine=MyISAM;
create table t3 (e int, f int) engine=MyISAM;
insert into t1 values (1,1),(2,2),(3,3),(2,2);
insert into t2 values (2,2),(3,3),(4,4),(2,2);
insert into t3 values (1,1),(2,2),(5,5),(2,2);
(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);
a b
2 2
2 2
EXPLAIN (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 4
2 INTERSECT t2 ALL NULL NULL NULL NULL 4
3 INTERSECT t3 ALL NULL NULL NULL NULL 4
NULL INTERSECT RESULT <intersect1,2,3> ALL NULL NULL NULL NULL NULL
EXPLAIN extended (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00
2 INTERSECT t2 ALL NULL NULL NULL NULL 4 100.00
3 INTERSECT t3 ALL NULL NULL NULL NULL 4 100.00
NULL INTERSECT RESULT <intersect1,2,3> 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`) intersect all (/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect all (/* select#3 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)
EXPLAIN extended select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) a;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00
2 DERIVED t1 ALL NULL NULL NULL NULL 4 100.00
3 INTERSECT t2 ALL NULL NULL NULL NULL 4 100.00
4 INTERSECT t3 ALL NULL NULL NULL NULL 4 100.00
NULL INTERSECT RESULT <intersect2,3,4> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003 /* select#1 */ select `a`.`a` AS `a`,`a`.`b` AS `b` from ((/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) intersect all (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect all (/* select#4 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `a`
EXPLAIN format=json (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);
EXPLAIN
{
"query_block": {
"union_result": {
"table_name": "<intersect1,2,3>",
"access_type": "ALL",
"query_specifications": [
{
"query_block": {
"select_id": 1,
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows": 4,
"filtered": 100
}
}
},
{
"query_block": {
"select_id": 2,
"operation": "INTERSECT",
"table": {
"table_name": "t2",
"access_type": "ALL",
"rows": 4,
"filtered": 100
}
}
},
{
"query_block": {
"select_id": 3,
"operation": "INTERSECT",
"table": {
"table_name": "t3",
"access_type": "ALL",
"rows": 4,
"filtered": 100
}
}
}
]
}
}
}
ANALYZE format=json (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);
ANALYZE
{
"query_block": {
"union_result": {
"table_name": "<intersect1,2,3>",
"access_type": "ALL",
"r_loops": 1,
"r_rows": 2,
"query_specifications": [
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": "REPLACED",
"table": {
"table_name": "t1",
"access_type": "ALL",
"r_loops": 1,
"rows": 4,
"r_rows": 4,
"r_table_time_ms": "REPLACED",
"r_other_time_ms": "REPLACED",
"r_engine_stats": REPLACED,
"filtered": 100,
"r_filtered": 100
}
}
},
{
"query_block": {
"select_id": 2,
"operation": "INTERSECT",
"r_loops": 1,
"r_total_time_ms": "REPLACED",
"table": {
"table_name": "t2",
"access_type": "ALL",
"r_loops": 1,
"rows": 4,
"r_rows": 4,
"r_table_time_ms": "REPLACED",
"r_other_time_ms": "REPLACED",
"r_engine_stats": REPLACED,
"filtered": 100,
"r_filtered": 100
}
}
},
{
"query_block": {
"select_id": 3,
"operation": "INTERSECT",
"r_loops": 1,
"r_total_time_ms": "REPLACED",
"table": {
"table_name": "t3",
"access_type": "ALL",
"r_loops": 1,
"rows": 4,
"r_rows": 4,
"r_table_time_ms": "REPLACED",
"r_other_time_ms": "REPLACED",
"r_engine_stats": REPLACED,
"filtered": 100,
"r_filtered": 100
}
}
}
]
}
}
}
ANALYZE format=json select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) a;
ANALYZE
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": "REPLACED",
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"r_loops": 1,
"rows": 4,
"r_rows": 2,
"r_table_time_ms": "REPLACED",
"r_other_time_ms": "REPLACED",
"filtered": 100,
"r_filtered": 100,
"materialized": {
"query_block": {
"union_result": {
"table_name": "<intersect2,3,4>",
"access_type": "ALL",
"r_loops": 1,
"r_rows": 2,
"query_specifications": [
{
"query_block": {
"select_id": 2,
"r_loops": 1,
"r_total_time_ms": "REPLACED",
"table": {
"table_name": "t1",
"access_type": "ALL",
"r_loops": 1,
"rows": 4,
"r_rows": 4,
"r_table_time_ms": "REPLACED",
"r_other_time_ms": "REPLACED",
"r_engine_stats": REPLACED,
"filtered": 100,
"r_filtered": 100
}
}
},
{
"query_block": {
"select_id": 3,
"operation": "INTERSECT",
"r_loops": 1,
"r_total_time_ms": "REPLACED",
"table": {
"table_name": "t2",
"access_type": "ALL",
"r_loops": 1,
"rows": 4,
"r_rows": 4,
"r_table_time_ms": "REPLACED",
"r_other_time_ms": "REPLACED",
"r_engine_stats": REPLACED,
"filtered": 100,
"r_filtered": 100
}
}
},
{
"query_block": {
"select_id": 4,
"operation": "INTERSECT",
"r_loops": 1,
"r_total_time_ms": "REPLACED",
"table": {
"table_name": "t3",
"access_type": "ALL",
"r_loops": 1,
"rows": 4,
"r_rows": 4,
"r_table_time_ms": "REPLACED",
"r_other_time_ms": "REPLACED",
"r_engine_stats": REPLACED,
"filtered": 100,
"r_filtered": 100
}
}
}
]
}
}
}
}
}
}
select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) a;
a b
2 2
2 2
prepare stmt from "(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);";
execute stmt;
a b
2 2
2 2
execute stmt;
a b
2 2
2 2
prepare stmt from "select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) a";
execute stmt;
a b
2 2
2 2
execute stmt;
a b
2 2
2 2
insert into t1 values (2,2),(3,3);
insert into t2 values (2,2),(2,2),(2,2);
(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);
a b
2 2
2 2
(select a,b from t1) intersect (select c,d from t2) intersect all (select e,f from t3);
a b
2 2
insert into t3 values (2,2);
(select a,b from t1) intersect all (select c,d from t2) intersect (select e,f from t3);
a b
2 2
(select a,b from t1) intersect all (select c,e from t2,t3);
a b
2 2
2 2
2 2
EXPLAIN (select a,b from t1) intersect all (select c,e from t2,t3);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 6
2 INTERSECT t3 ALL NULL NULL NULL NULL 5
2 INTERSECT t2 ALL NULL NULL NULL NULL 7 Using join buffer (flat, BNL join)
NULL INTERSECT RESULT <intersect1,2> ALL NULL NULL NULL NULL NULL
EXPLAIN extended (select a,b from t1) intersect all (select c,e from t2,t3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00
2 INTERSECT t3 ALL NULL NULL NULL NULL 5 100.00
2 INTERSECT t2 ALL NULL NULL NULL NULL 7 100.00 Using join buffer (flat, BNL join)
NULL INTERSECT RESULT <intersect1,2> 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`) intersect all (/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t3`.`e` AS `e` from `test`.`t2` join `test`.`t3`)
EXPLAIN extended select * from ((select a,b from t1) intersect all (select c,e from t2,t3)) a;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00
2 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00
3 INTERSECT t3 ALL NULL NULL NULL NULL 5 100.00
3 INTERSECT t2 ALL NULL NULL NULL NULL 7 100.00 Using join buffer (flat, BNL join)
NULL INTERSECT RESULT <intersect2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003 /* select#1 */ select `a`.`a` AS `a`,`a`.`b` AS `b` from ((/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) intersect all (/* select#3 */ select `test`.`t2`.`c` AS `c`,`test`.`t3`.`e` AS `e` from `test`.`t2` join `test`.`t3`)) `a`
EXPLAIN format=json (select a,b from t1) intersect all (select c,e from t2,t3);
EXPLAIN
{
"query_block": {
"union_result": {
"table_name": "<intersect1,2>",
"access_type": "ALL",
"query_specifications": [
{
"query_block": {
"select_id": 1,
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows": 6,
"filtered": 100
}
}
},
{
"query_block": {
"select_id": 2,
"operation": "INTERSECT",
"table": {
"table_name": "t3",
"access_type": "ALL",
"rows": 5,
"filtered": 100
},
"block-nl-join": {
"table": {
"table_name": "t2",
"access_type": "ALL",
"rows": 7,
"filtered": 100
},
"buffer_type": "flat",
"buffer_size": "65",
"join_type": "BNL"
}
}
}
]
}
}
}
ANALYZE format=json (select a,b from t1) intersect all (select c,e from t2,t3);
ANALYZE
{
"query_block": {
"union_result": {
"table_name": "<intersect1,2>",
"access_type": "ALL",
"r_loops": 1,
"r_rows": 3,
"query_specifications": [
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": "REPLACED",
"table": {
"table_name": "t1",
"access_type": "ALL",
"r_loops": 1,
"rows": 6,
"r_rows": 6,
"r_table_time_ms": "REPLACED",
"r_other_time_ms": "REPLACED",
"r_engine_stats": REPLACED,
"filtered": 100,
"r_filtered": 100
}
}
},
{
"query_block": {
"select_id": 2,
"operation": "INTERSECT",
"r_loops": 1,
"r_total_time_ms": "REPLACED",
"table": {
"table_name": "t3",
"access_type": "ALL",
"r_loops": 1,
"rows": 5,
"r_rows": 5,
"r_table_time_ms": "REPLACED",
"r_other_time_ms": "REPLACED",
"r_engine_stats": REPLACED,
"filtered": 100,
"r_filtered": 100
},
"block-nl-join": {
"table": {
"table_name": "t2",
"access_type": "ALL",
"r_loops": 1,
"rows": 7,
"r_rows": 7,
"r_table_time_ms": "REPLACED",
"r_other_time_ms": "REPLACED",
"r_engine_stats": REPLACED,
"filtered": 100,
"r_filtered": 100
},
"buffer_type": "flat",
"buffer_size": "65",
"join_type": "BNL",
"r_loops": 5,
"r_filtered": 100,
"r_unpack_time_ms": "REPLACED",
"r_other_time_ms": "REPLACED",
"r_effective_rows": 7
}
}
}
]
}
}
}
ANALYZE format=json select * from ((select a,b from t1) intersect all (select c,e from t2,t3)) a;
ANALYZE
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": "REPLACED",
"table": {
"table_name": "<derived2>",
"access_type": "ALL",
"r_loops": 1,
"rows": 6,
"r_rows": 3,
"r_table_time_ms": "REPLACED",
"r_other_time_ms": "REPLACED",
"filtered": 100,
"r_filtered": 100,
"materialized": {
"query_block": {
"union_result": {
"table_name": "<intersect2,3>",
"access_type": "ALL",
"r_loops": 1,
"r_rows": 3,
"query_specifications": [
{
"query_block": {
"select_id": 2,
"r_loops": 1,
"r_total_time_ms": "REPLACED",
"table": {
"table_name": "t1",
"access_type": "ALL",
"r_loops": 1,
"rows": 6,
"r_rows": 6,
"r_table_time_ms": "REPLACED",
"r_other_time_ms": "REPLACED",
"r_engine_stats": REPLACED,
"filtered": 100,
"r_filtered": 100
}
}
},
{
"query_block": {
"select_id": 3,
"operation": "INTERSECT",
"r_loops": 1,
"r_total_time_ms": "REPLACED",
"table": {
"table_name": "t3",
"access_type": "ALL",
"r_loops": 1,
"rows": 5,
"r_rows": 5,
"r_table_time_ms": "REPLACED",
"r_other_time_ms": "REPLACED",
"r_engine_stats": REPLACED,
"filtered": 100,
"r_filtered": 100
},
"block-nl-join": {
"table": {
"table_name": "t2",
"access_type": "ALL",
"r_loops": 1,
"rows": 7,
"r_rows": 7,
"r_table_time_ms": "REPLACED",
"r_other_time_ms": "REPLACED",
"r_engine_stats": REPLACED,
"filtered": 100,
"r_filtered": 100
},
"buffer_type": "flat",
"buffer_size": "65",
"join_type": "BNL",
"r_loops": 5,
"r_filtered": 100,
"r_unpack_time_ms": "REPLACED",
"r_other_time_ms": "REPLACED",
"r_effective_rows": 7
}
}
}
]
}
}
}
}
}
}
select * from ((select a,b from t1) intersect all (select c,e from t2,t3)) a;
a b
2 2
2 2
2 2
prepare stmt from "(select a,b from t1) intersect all (select c,e from t2,t3);";
execute stmt;
a b
2 2
2 2
2 2
execute stmt;
a b
2 2
2 2
2 2
prepare stmt from "select * from ((select a,b from t1) intersect all (select c,e from t2,t3)) a";
execute stmt;
a b
2 2
2 2
2 2
execute stmt;
a b
2 2
2 2
2 2
drop tables t1,t2,t3;
select 1 as a from dual intersect all select 1 from dual;
a
1
(select 1 from dual) intersect all (select 1 from dual);
1
1
(select 1 from dual into @v) intersect all (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 'into @v) intersect all (select 1 from dual)' at line 1
select 1 from dual ORDER BY 1 intersect all 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 all select 1 from dual' at line 1
select 1 as a from dual union all select 1 from dual;
a
1
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"),(2, "fgh", 2, "dffggtt");
insert into t2 values (2, "fgh", 2, "dffggtt"),(3, "ffggddd", 3, "dfgg"),(2, "fgh", 2, "dffggtt");
(select a,b,b1 from t1) intersect all (select c,d,d1 from t2);
a b b1
2 fgh dffggtt
2 fgh dffggtt
drop tables t1,t2;
create table t1 (a int, b blob) engine=MyISAM;
create table t2 (c int, d blob) engine=MyISAM;
create table t3 (e int, f blob) engine=MyISAM;
insert into t1 values (1,1),(2,2),(3,3),(2,2),(3,3);
insert into t2 values (2,2),(3,3),(4,4),(2,2),(2,2),(2,2);
insert into t3 values (1,1),(2,2),(5,5),(2,2),(5,5);
(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);
a b
2 2
2 2
select * from ((select a,b from t1) intersect all (select c,d from t2) intersect (select e,f from t3)) a;
a b
2 2
prepare stmt from "(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);";
execute stmt;
a b
2 2
2 2
execute stmt;
a b
2 2
2 2
prepare stmt from "select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) a";
execute stmt;
a b
2 2
2 2
execute stmt;
a b
2 2
2 2
create table t4 (select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3);
show create table t4;
Table Create Table
t4 CREATE TABLE `t4` (
`a` int(11) DEFAULT NULL,
`b` blob DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
drop tables t4;
(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4);
a b
4 4
2 2
2 2
(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4) except all (select 2,2);
a b
4 4
2 2
drop tables t1,t2,t3;
create table t1 (a int, b int);
create table t2 (c int, d int);
create table t3 (e int, f int);
insert into t1 values (1,1),(2,2),(3,3),(2,2),(3,3);
insert into t2 values (2,2),(3,3),(4,4),(2,2),(2,2),(2,2);
insert into t3 values (1,1),(2,2),(5,5),(2,2),(5,5);
(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4);
a b
4 4
2 2
2 2
(select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4) except all (select 2,2);
a b
4 4
2 2
drop tables t1,t2,t3;
#
# INTERSECT precedence
#
create table t1 (a int, b blob) engine=MyISAM;
create table t2 (c int, d blob) engine=MyISAM;
create table t3 (e int, f blob) engine=MyISAM;
insert into t1 values (5,5),(6,6);
insert into t2 values (2,2),(3,3);
insert into t3 values (1,1),(3,3);
(select a,b from t1) union all (select c,d from t2) intersect (select e,f from t3) union all (select 4,4);
a b
5 5
6 6
3 3
4 4
(select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4);
a b
5 5
6 6
3 3
4 4
explain extended (select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (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
5 UNION <derived2> ALL NULL NULL NULL NULL 2 100.00
2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00
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
Warnings:
Note 1003 (/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union all /* 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 all (/* select#3 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__5` union all (/* select#4 */ select 4 AS `4`,4 AS `4`)
insert into t2 values (3,3);
insert into t3 values (3,3);
(select e,f from t3) intersect all (select c,d from t2) union all (select a,b from t1) union all (select 4,4);
e f
3 3
3 3
5 5
6 6
4 4
explain extended (select e,f from t3) intersect all (select c,d from t2) union all (select a,b from t1) union all (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 3 100.00
2 INTERSECT t2 ALL NULL NULL NULL NULL 3 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 all (/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) union all (/* select#3 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union all (/* select#4 */ select 4 AS `4`,4 AS `4`)
(/* 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 all (/* 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
5 5
6 6
3 3
4 4
prepare stmt from "(select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4)";
execute stmt;
a b
5 5
6 6
3 3
3 3
4 4
execute stmt;
a b
5 5
6 6
3 3
3 3
4 4
create view v1 as (select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4);
select b,a,b+1 from v1;
b a b+1
5 5 6
6 6 7
3 3 4
3 3 4
4 4 5
select b,a,b+1 from v1 where a > 3;
b a b+1
5 5 6
6 6 7
4 4 5
create procedure p1()
select * from v1;
call p1();
a b
5 5
6 6
3 3
3 3
4 4
call p1();
a b
5 5
6 6
3 3
3 3
4 4
drop procedure p1;
create procedure p1()
(select a,b from t1) union all (select c,d from t2) intersect all (select e,f from t3) union all (select 4,4);
call p1();
a b
5 5
6 6
3 3
3 3
4 4
call p1();
a b
5 5
6 6
3 3
3 3
4 4
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 `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1`) union all select `__6`.`c` AS `c`,`__6`.`d` AS `d` from ((select `t2`.`c` AS `c`,`t2`.`d` AS `d` from `t2`) intersect all (select `t3`.`e` AS `e`,`t3`.`f` AS `f` from `t3`)) `__6` union all (select 4 AS `4`,4 AS `4`) latin1 latin1_swedish_ci
drop view v1;
drop tables t1,t2,t3;
CREATE TABLE t (i INT);
INSERT INTO t VALUES (1),(2);
SELECT * FROM t WHERE i != ANY ( SELECT 6 INTERSECT ALL SELECT 3 );
i
select i from t where
exists ((select 6 as r from dual having t.i <> 6)
intersect all
(select 3 from dual having t.i <> 3));
i
drop table t;
CREATE TABLE t1 (a varchar(32)) ENGINE=MyISAM;
INSERT INTO t1 VALUES
('Jakarta'),('Lisbon'),('Honolulu'),('Lusaka'),('Barcelona'),('Taipei'),
('Brussels'),('Orlando'),('Osaka'),('Quito'),('Lima'),('Tunis'),
('Unalaska'),('Rotterdam'),('Zagreb'),('Ufa'),('Ryazan'),('Xiamen'),
('London'),('Izmir'),('Samara'),('Bern'),('Zhengzhou'),('Vladivostok'),
('Yangon'),('Victoria'),('Warsaw'),('Luanda'),('Leon'),('Bangkok'),
('Wellington'),('Zibo'),('Qiqihar'),('Delhi'),('Hamburg'),('Ottawa'),
('Vaduz');
CREATE TABLE t2 (b varchar(32)) ENGINE=MyISAM;
INSERT INTO t2 VALUES
('Gaza'),('Jeddah'),('Beirut'),('Incheon'),('Tbilisi'),('Izmir'),
('Quito'),('Riga'),('Freetown'),('Zagreb'),('Caracas'),('Orlando'),
('Kingston'),('Turin'),('Xinyang'),('Osaka'),('Albany'),('Geneva'),
('Omsk'),('Kazan'),('Quezon'),('Indore'),('Odessa'),('Xiamen'),
('Winnipeg'),('Yakutsk'),('Nairobi'),('Ufa'),('Helsinki'),('Vilnius'),
('Aden'),('Liverpool'),('Honolulu'),('Frankfurt'),('Glasgow'),
('Vienna'),('Jackson'),('Jakarta'),('Sydney'),('Oslo'),('Novgorod'),
('Norilsk'),('Izhevsk'),('Istanbul'),('Nice');
CREATE TABLE t3 (c varchar(32)) ENGINE=MyISAM;
INSERT INTO t3 VALUES
('Nicosia'),('Istanbul'),('Richmond'),('Stockholm'),('Dublin'),
('Wichita'),('Warsaw'),('Glasgow'),('Winnipeg'),('Irkutsk'),('Quito'),
('Xiamen'),('Berlin'),('Rome'),('Denver'),('Dallas'),('Kabul'),
('Prague'),('Izhevsk'),('Tirana'),('Sofia'),('Detroit'),('Sorbonne');
select count(*) from (
SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b
INTERSECT
SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b
) a;
count(*)
14848
select count(*) from (
SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b
INTERSECT ALL
SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b
) a;
count(*)
14848
insert into t1 values ('Xiamen');
insert into t2 values ('Xiamen'),('Xiamen');
insert into t3 values ('Xiamen');
select count(*) from (
SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b
INTERSECT ALL
SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b
) a;
count(*)
16430
drop table t1,t2,t3;
CREATE TABLE t1 (a varchar(32) not null) ENGINE=MyISAM;
INSERT INTO t1 VALUES
('Jakarta'),('Lisbon'),('Honolulu'),('Lusaka'),('Barcelona'),('Taipei'),
('Brussels'),('Orlando'),('Osaka'),('Quito'),('Lima'),('Tunis'),
('Unalaska'),('Rotterdam'),('Zagreb'),('Ufa'),('Ryazan'),('Xiamen'),
('London'),('Izmir'),('Samara'),('Bern'),('Zhengzhou'),('Vladivostok'),
('Yangon'),('Victoria'),('Warsaw'),('Luanda'),('Leon'),('Bangkok'),
('Wellington'),('Zibo'),('Qiqihar'),('Delhi'),('Hamburg'),('Ottawa'),
('Vaduz'),('Detroit'),('Detroit');
CREATE TABLE t2 (b varchar(32) not null) ENGINE=MyISAM;
INSERT INTO t2 VALUES
('Gaza'),('Jeddah'),('Beirut'),('Incheon'),('Tbilisi'),('Izmir'),
('Quito'),('Riga'),('Freetown'),('Zagreb'),('Caracas'),('Orlando'),
('Kingston'),('Turin'),('Xinyang'),('Osaka'),('Albany'),('Geneva'),
('Omsk'),('Kazan'),('Quezon'),('Indore'),('Odessa'),('Xiamen'),
('Winnipeg'),('Yakutsk'),('Nairobi'),('Ufa'),('Helsinki'),('Vilnius'),
('Aden'),('Liverpool'),('Honolulu'),('Frankfurt'),('Glasgow'),
('Vienna'),('Jackson'),('Jakarta'),('Sydney'),('Oslo'),('Novgorod'),
('Norilsk'),('Izhevsk'),('Istanbul'),('Nice'),('Detroit'),('Detroit');
CREATE TABLE t3 (c varchar(32) not null) ENGINE=MyISAM;
INSERT INTO t3 VALUES
('Nicosia'),('Istanbul'),('Richmond'),('Stockholm'),('Dublin'),
('Wichita'),('Warsaw'),('Glasgow'),('Winnipeg'),('Irkutsk'),('Quito'),
('Xiamen'),('Berlin'),('Rome'),('Denver'),('Dallas'),('Kabul'),
('Prague'),('Izhevsk'),('Tirana'),('Sofia'),('Detroit'),('Sorbonne'),
('Detroit');
select count(*) from (
SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b
INTERSECT
SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b
) a;
count(*)
15547
drop table t1,t2,t3;
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);
select * from t13 union select * from t234 intersect all select * from t12;
c1
1
3
2
drop table t12,t13,t234;
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), (2), (2);
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 all
select * from t3 where a < 5;
a
7
7
2
1
2
explain extended
select * from t1 where a > 4
union all
select * from t2 where a < 5
intersect all
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 7 100.00
2 DERIVED t2 ALL NULL NULL NULL NULL 8 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 all /* select#3 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where `test`.`t3`.`a` < 5) `__4`
drop table t1,t2,t3;
#
# MDEV-25158 Segfault on INTERSECT ALL with UNION in Oracle mode
#
create table t3 (x int);
create table u3 (x int);
create table i3 (x int);
explain SELECT * from t3 union select * from u3 intersect all select * from i3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 system NULL NULL NULL NULL 0 Const row not found
4 UNION <derived2> ALL NULL NULL NULL NULL 2
2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
3 INTERSECT NULL NULL NULL NULL NULL NULL NULL no matching row in const table
NULL INTERSECT RESULT <intersect2,3> ALL NULL NULL NULL NULL NULL
NULL UNION RESULT <union1,4> ALL NULL NULL NULL NULL NULL
set sql_mode= 'oracle';
explain SELECT * from t3 union select * from u3 intersect all select * from i3;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 system NULL NULL NULL NULL 0 Const row not found
2 UNION u3 system NULL NULL NULL NULL 0 Const row not found
3 INTERSECT i3 system NULL NULL NULL NULL 0 Const row not found
NULL UNIT RESULT <unit1,2,3> ALL NULL NULL NULL NULL NULL
select * from t3 union select * from u3 intersect select * from i3;
x
SELECT * from t3 union select * from u3 intersect all select * from i3;
x
insert into t3 values (0);
insert into i3 values (0);
Select * from t3 union select * from u3 intersect select * from i3;
x
0
SELECT * FROM t3 UNION SELECT * FROM u3 INTERSECT ALL SELECT * FROM i3;
x
0
drop tables t3, u3, i3;
# First line of these results is column names, not the result
# (pay attention to "affected rows")
values (1, 2) union all values (1, 2);
1 2
1 2
1 2
affected rows: 2
values (1, 2) union all values (1, 2) union values (4, 3) union all values (4, 3);
1 2
1 2
4 3
4 3
affected rows: 3
values (1, 2) union all values (1, 2) union values (4, 3) union all values (4, 3) union all values (1, 2);
1 2
1 2
4 3
4 3
1 2
affected rows: 4
values (1, 2) union all values (1, 2) union values (4, 3) union all values (4, 3) union all values (1, 2) union values (1, 2);
1 2
1 2
4 3
affected rows: 2
create table t1 (a int, b int);
create table t2 like t1;
insert t1 values (1, 2), (1, 2), (1, 2), (2, 3), (2, 3), (3, 4), (3, 4);
insert t2 values (1, 2), (1, 2), (2, 3), (2, 3), (2, 3), (2, 3), (4, 5);
select * from t1 intersect select * from t2;
a b
1 2
2 3
select * from t1 intersect all select * from t2;
a b
1 2
2 3
1 2
2 3
# Default: first INTERSECT ALL, then UNION
# Oracle: first UNION, then INTERSECT ALL
select * from t1 union values (1, 2) intersect all select * from t2;
a b
1 2
2 3
select * from t1 union (values (1, 2) intersect all select * from t2);
a b
1 2
2 3
3 4
(select * from t1 union values (1, 2)) intersect all select * from t2;
a b
1 2
2 3
select * from t1 intersect all select * from t2 union values (1, 2);
a b
1 2
2 3
1 2
2 3
select * from t1 intersect all (select * from t2 union values (1, 2));
a b
1 2
2 3
(select * from t1 intersect all select * from t2) union values (1, 2);
a b
1 2
2 3
explain select * from t1 intersect all select * from t2 union values (1, 2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 7
2 INTERSECT t2 ALL NULL NULL NULL NULL 7
3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNIT RESULT <unit1,2,3> ALL NULL NULL NULL NULL NULL
drop tables t1, t2;
set sql_mode= default;
Reference in a new issue View git blame Copy permalink