mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-11-03 20:36:16 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/set_operation.test
Marko Mäkelä 17f01186f5 Merge 10.11 into 11.4
2025-01-09 07:58:08 +02:00

555 lines
12 KiB
Text
Raw Permalink Blame History

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;
create table t4 (g int, h int) engine=MyISAM;
insert into t1 values (1,1),(2,2),(3,3),(2,2),(3,3);
insert into t2 values (2,2),(3,3),(5,5),(2,2),(2,2),(3,3);
insert into t3 values (4,4),(2,2),(2,2),(1,1),(3,3);
insert into t4 values (2,2),(4,4),(1,1);
create view v0(g, h) as select a,c from t1,t2;
--echo # test optimization
let $q=
select * from t1
INTERSECT ALL
select * from t2
INTERSECT ALL
select * from t3;
eval $q;
eval EXPLAIN EXTENDED $q;
let $q=
select * from t1
INTERSECT ALL
select * from t2
INTERSECT ALL
select * from t3
INTERSECT
select * from t1;
eval $q;
eval EXPLAIN EXTENDED $q;
let $q=
select * from t1
INTERSECT ALL
select * from t2
INTERSECT ALL
select * from t3
EXCEPT ALL
select * from t4;
eval $q;
eval EXPLAIN EXTENDED $q;
let $q=
select * from t1
INTERSECT
select * from t2
EXCEPT ALL
select * from t4;
eval $q;
eval EXPLAIN EXTENDED $q;
insert into t4 values (1,1),(9,9);
let $q=
select * from t1
UNION ALL
select * from t2
UNION ALL
select * from t3
EXCEPT
select * from t4;
eval $q;
eval EXPLAIN EXTENDED $q;
delete from t4;
insert into t4 values (3,3),(3,3);
let $q=
select * from t1
INTERSECT ALL
select * from t2
UNION ALL
select * from t3
EXCEPT ALL
select * from t1
UNION
select * from t4
EXCEPT
select * from t3
UNION ALL
select * from t1;
eval $q;
eval EXPLAIN EXTENDED $q;
drop table t4;
--echo # test optimization with brackets
let $q=
(
(select 1 except select 5 union all select 6)
union
(select 2 intersect all select 3 intersect all select 4)
except
(select 7 intersect all select 8)
)
union all
(select 9 union all select 10)
except all
select 11;
eval $q;
eval EXPLAIN EXTENDED $q;
let $q=
(select 1 union all select 2)
union
(select 3 union all select 4);
eval $q;
eval EXPLAIN EXTENDED $q;
let $q=
(select 1 intersect all select 2)
except
select 3;
eval $q;
eval EXPLAIN EXTENDED $q;
let $q=
(select 1 intersect all select 2 intersect all select 3)
intersect
(select 4 intersect all select 5);
eval $q;
eval EXPLAIN EXTENDED $q;
--echo # test set operations with table value constructor
(values (1,1),(1,1),(1,1),(2,2),(2,2),(3,3),(9,9))
INTERSECT ALL
(values (1,1),(2,2),(2,2),(3,3),(3,3),(3,3),(8,8))
EXCEPT ALL
(values (7,7),(1,1));
delete from t1;
insert into t1 values(1,1),(1,1),(2,2),(4,4),(9,9);
select * from t1
UNION ALL
(values (11,12),(3,3),(2,2),(3,3),(4,4),(8,8))
INTERSECT
(values (13,14),(7,7),(2,2),(3,3),(1,1))
INTERSECT ALL
(values (15,16),(2,2),(1,1))
EXCEPT
(values (17,18),(1,1));
--echo # test set operations with derived table
select * from (
select * from t1
UNION ALL
select * from t2
)dt1
INTERSECT ALL
select * from (
select * from t2
EXCEPT ALL
select * from t3
)dt2;
select * from (
select * from t1
UNION ALL
select * from t3
)dt1
EXCEPT ALL
select * from (
select * from t2
INTERSECT ALL
select * from t2
)dt2;
SELECT * from(
select * from (
select * from t1
UNION ALL
select * from t2
)dt1
INTERSECT ALL
select * from (
select * from t2
EXCEPT ALL
select * from t3
)dt2
)dt3;
--echo # integration test
select * from t1
UNION ALL
select * from t2
INTERSECT ALL
(values (1,1), (2,2), (2,2), (5,5), (2,2))
INTERSECT ALL
select * from (select * from t1 union all select * from t1) sq
EXCEPT ALL
select * from t3
UNION ALL
select * from t2
UNION
select * from t3
EXCEPT
select a,c from t1,t2
UNION ALL
select * from v0 where g < 4
UNION ALL
select * from t3;
--sorted_result
select * from t1
UNION ALL
select * from t2
INTERSECT ALL
(values (1,1), (2,2), (2,2), (5,5), (2,2))
INTERSECT ALL
select * from (select * from t1 union all select * from t1) sq
EXCEPT ALL
select * from t3
UNION ALL
select * from t2
UNION
select * from t3
EXCEPT
select a,c from t1,t2
UNION ALL
select * from v0 where g < 4
UNION ALL
select * from t3
ORDER BY a;
select * from (
select * from t1
UNION ALL
select * from t2
INTERSECT ALL
(values (1,1), (2,2), (2,2), (5,5), (2,2) )
INTERSECT ALL
select * from (select * from t1 union all select * from t1) sq
EXCEPT ALL
select * from t3
UNION ALL
select * from t2
UNION
select * from t3
EXCEPT
select a,c from t1,t2
UNION ALL
select * from v0 where g < 4
UNION ALL
select * from t3
) dt;
EXPLAIN
select * from t1
UNION ALL
select * from t2
INTERSECT ALL
(values (1,1), (2,2), (2,2), (5,5), (2,2) )
INTERSECT ALL
select * from (select * from t1 union all select * from t1) sq
EXCEPT ALL
select * from t3
UNION ALL
select * from t2
UNION
select * from t3
EXCEPT
select a,c from t1,t2
UNION ALL
select * from v0 where g < 4
UNION ALL
select * from t3;
--source include/analyze-format.inc
EXPLAIN format=json
select * from t1
UNION ALL
select * from t2
INTERSECT ALL
(values (1,1), (2,2), (2,2), (5,5), (2,2) )
INTERSECT ALL
select * from (select * from t1 union all select * from t1) sq
EXCEPT ALL
select * from t3
UNION ALL
select * from t2
UNION
select * from t3
EXCEPT
select a,c from t1,t2
UNION ALL
select * from v0 where g < 4
UNION ALL
select * from t3;
EXPLAIN EXTENDED
select * from t1
UNION ALL
select * from t2
INTERSECT ALL
(values (1,1), (2,2), (2,2), (5,5), (2,2) )
INTERSECT ALL
select * from (select * from t1 union all select * from t1) sq
EXCEPT ALL
select * from t3
UNION ALL
select * from t2
UNION
select * from t3
EXCEPT
select a,c from t1,t2
UNION ALL
select * from v0 where g < 4
UNION ALL
select * from t3;
PREPARE stmt from"
select * from t1
UNION ALL
select * from t2
INTERSECT ALL
(values (1,1), (2,2), (2,2), (5,5), (2,2) )
INTERSECT ALL
select * from (select * from t1 union all select * from t1) sq
EXCEPT ALL
select * from t3
UNION ALL
select * from t2
UNION
select * from t3
EXCEPT
select a,c from t1,t2
UNION ALL
select * from v0 where g < 4
UNION ALL
select * from t3
";
EXECUTE stmt;
EXECUTE stmt;
deallocate prepare stmt;
create view v1(i1, i2) as
select * from t1
UNION ALL
select * from t2
INTERSECT ALL
(values (1,1), (2,2), (2,2), (5,5), (2,2) )
INTERSECT ALL
select * from (select * from t1 union all select * from t1) sq
EXCEPT ALL
select * from t3
UNION ALL
select * from t2
UNION
select * from t3
EXCEPT
select a,c from t1,t2
UNION ALL
select * from v0 where g < 4
UNION ALL
select * from t3;
show create view v1;
select * from v1 limit 14;
--sorted_result
select * from v1 order by i1 limit 14;
drop table t1,t2,t3;
drop view v0,v1;
--echo # compare result
create table t1 (a int, b int);
create table t2 (c int, d int);
create table t3 (e int, f int);
create table t4 (g int, h int);
insert into t1 values (1,1),(1,1),(2,2);
insert into t2 values (1,1),(1,1),(2,2),(3,3);
insert into t3 values (1,1);
insert into t4 values (4,4);
select * from t1 intersect all select * from t2 except select * from t3 union select * from t4;
select * from t1 intersect all select * from t2 except ALL select * from t3 union select * from t4;
select * from t1 intersect DISTINCT select * from t2 except select * from t3 union select * from t4;
select * from t1 intersect DISTINCT select * from t2 except ALL select * from t3 union select * from t4;
delete from t1;
delete from t2;
delete from t3;
delete from t4;
insert into t1 values (1,1),(1,1),(1,1),(2,2),(2,2),(4,4),(5,5);
insert into t2 values (1,1),(1,1),(1,1),(2,2),(2,2),(3,3);
insert into t3 values (1,1),(2,2),(2,2);
select * from t1 intersect all select * from t2 intersect all select * from t3;
select * from t1 intersect all select * from t2 intersect select * from t3;
select * from t1 intersect all select * from t1 intersect all select * from t2 intersect select * from t3;
delete from t1;
delete from t2;
delete from t3;
insert into t1 values (1,1),(1,1),(2,2);
insert into t2 values (1,1),(1,1),(2,2),(3,3);
insert into t3 values (1,1),(5,5);
insert into t4 values (4,4),(4,4),(4,4);
select * from t1 intersect all select * from t2 union all select * from t3 union select * from t4;
select * from t1 intersect DISTINCT select * from t2 union DISTINCT select * from t3 union select * from t4;
select * from t1 intersect all select * from t2 intersect all select * from t3 union select * from t4;
select * from t1 intersect all select * from t2 intersect DISTINCT select * from t3 union select * from t4;
select * from t1 intersect DISTINCT select * from t2 intersect DISTINCT select * from t3 union select * from t4;
select * from t1 intersect all select * from t2 EXCEPT select * from t3 union select * from t4;
select * from t1 intersect DISTINCT select * from t2 EXCEPT select * from t3 union select * from t4;
select * from t1 intersect all select * from t2 EXCEPT ALL select * from t3 union select * from t4;
select * from t1 EXCEPT select * from t2 union all select * from t3 union select * from t4;
select * from t1 EXCEPT select * from t2 union DISTINCT select * from t3 union select * from t4;
delete from t1;
delete from t2;
delete from t3;
delete from t4;
insert into t1 values (1,1),(2,2);
insert into t2 values (1,1),(2,2);
insert into t3 values (1,1),(3,3);
select * from t1 union all select * from t2 except all select * from t3;
select * from t1 union all select * from t2 except DISTINCT select * from t3;
select * from t1 union DISTINCT select * from t2 except all select * from t3;
select * from t1 union DISTINCT select * from t2 except DISTINCT select * from t3;
drop table t1;
drop table t2;
drop table t3;
drop table t4;
select 1 intersect all select 2 intersect all select 3 intersect select 4 union select 5;
select 1 intersect all select 2 intersect all select 3 union select 4 except select 5;
select 1 union select 2 except all select 3 union select 4;
select 1 union all select 2 union all select 3 union select 4;
--echo # test with limited resource
set @@max_heap_table_size= 16384;
set @@tmp_table_size= 16384;
create table t1 (a int, b int);
insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select a+100, b+100 from t1;
create table t2 (a int, b int);
insert into t2 values (10,10),(11,11),(12,12),(13,13),(14,14),(5,5),(6,6),(7,7),(8,8),(9,9);
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select a+100, b+100 from t2;
select count(*) from
(
select * from t1
INTERSECT ALL
select * from t2
) c;
select count(*) from
(
select * from t1
EXCEPT ALL
select * from t2
) c;
select count(*) from
(
select * from t1
INTERSECT ALL
select * from t2
UNION ALL
select * from t1
EXCEPT ALL
select * from t2
) c;
delete from t1;
delete from t2;
insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);
insert into t1 select a+10, b+10 from t1;
insert into t1 select a+20, b+20 from t1;
insert into t1 select a+40, b+40 from t1;
insert into t1 select a+80, b+80 from t1;
insert into t2 values (1110,1110),(1111,1111),(1112,1112),(1113,1113),(1114,1114),(1105,1105),(1106,1106),(1107,1107),(1108,1108),(1109,1109);
insert into t2 select a+10, b+10 from t2;
insert into t2 select a+20, b+20 from t2;
insert into t2 select a+40, b+40 from t2;
insert into t2 select a+80, b+80 from t2;
select count(*) from
(
select * from t1
UNION ALL
select * from t2
EXCEPT ALL
values (1,1)
) c;
drop table t1;
drop table t2;
--echo #
--echo # MDEV-24242: set expression with empty intermediate result
--echo # when tmp_memory_table_size is set to 0
--echo #
create table t1 (a int, b int) engine=MyISAM;
insert into t1 values (1,1), (2,2);
create table t2 (a int, b int) engine=MyISAM;
insert into t2 values (11,11), (12,12), (13,13);
let $q=
select * from t1
except all
select * from t1
except
select * from t1
union all
select * from t2;
eval $q;
set tmp_memory_table_size=0;
eval $q;
set tmp_memory_table_size=default;
drop table t1,t2;
--echo # End of 10.4 tests
Reference in a new issue View git blame Copy permalink