database/mariadb-columnstore-engine
1
0
Fork 0
mirror of https://github.com/mariadb-corporation/mariadb-columnstore-engine.git synced 2025-07-02 17:22:27 +03:00
Code Activity
Files
test-debug-fix
mariadb-columnstore-engine/mysql-test/columnstore/basic/t/mcol_4617.test
Gagan Goel f167a6e505 MCOL-4617 Move in-to-exists predicate creation and injection into the engine. 
We earlier leveraged the server functionality provided by

Item_in_subselect::create_in_to_exists_cond and
Item_in_subselect::inject_in_to_exists_cond

to create and inject the in-to-exists predicate into an IN
subquery's JOIN struct. With this patch, we leave the IN subquery's
JOIN unaltered and instead directly perform this predicate creation
and injection into ColumnStore's select execution plan.
2021-04-30 07:57:00 +00:00

337 lines
16 KiB
Plaintext
Raw Permalink Blame History

#
# Test IN subquery
#
# Note that some queries in the tests below are commented out
# due to differing behaviour from InnoDB. MCOL-4641 tracks this
# issue. Uncomment the queries again once the issue is fixed.
#
--source ../include/have_columnstore.inc
set default_storage_engine=columnstore;
--disable_warnings
drop database if exists test_mcol4617;
--enable_warnings
create database test_mcol4617;
use test_mcol4617;
create table cs1 (a int);
insert into cs1 values (1), (2), (3), (4), (null);
create table cs2 (b int, c int);
insert into cs2 values (1, 100), (1, 101), (2, 200),
(3, 300), (3, 301), (3, 302), (null, null);
# Single column case
## IN subquery
### Basic tests
select * from cs1 where a in (select b from cs2);
select * from cs1 where a in (select c from cs2);
select * from cs1 where (a+a) in (select (b+b) from cs2);
select * from cs1 where (a+1) in (select b from cs2);
select * from cs1 where hex(a*10) in (select hex(b*10) from cs2);
### Correlated IN subquery
select * from cs1 where a in (select b from cs2 where cs1.a=cs2.c-299);
### Outer query containing additional WHERE predicates
select * from cs1 where a is not null and a in (select b from cs2);
select * from cs1 where a in (select b from cs2) and a is null;
select * from cs1 where a in (select 2 from cs2) and a in (select b from cs2);
select * from cs1 where a in (1,3) and a in (select b from cs2);
### Nested IN predicates
select * from cs1 where a in (select b from cs2 where b in (select a from cs1));
select * from cs1 where a in (select b from cs2 where b in (select a from cs1 where a in (2,3)));
select * from cs1 where a in (select b from cs2 where b in (select a from cs1 where a not in (2,3)));
### WHERE predicates in the IN subquery
select * from cs1 where a in (select b from cs2 where b=3);
select * from cs1 where a in (select b from cs2 where b=3 or c=200);
select * from cs1 where a in (select b from cs2 where b is not null);
### GROUP BY and HAVING predicates in the IN subquery
select * from cs1 where a in (select b from cs2 group by b);
select * from cs1 where a in (select count(*) from cs2 group by b);
select * from cs1 where a in (select count(*) from cs2 group by b having count(*) < 3);
select * from cs1 where a in (select count(*) from cs2 where b <> 2 group by b having count(*) < 3);
### Window functions in the SELECT clause of IN subquery
select * from cs1 where a in (select count(c) over (partition by b) from cs2);
select * from cs1 where a in (select count(*) over (partition by b) from cs2 where b is null);
select * from cs1 where a in (select count(*) over (partition by b) from cs2 where b <> 2);
### IN subquery containing joins
select * from cs1 where a in (select t1.b from cs2 t1, cs2 t2 where t1.b=t2.b);
select * from cs1 where a in (select t1.b from cs2 t1, cs2 t2 where t1.b=t2.b and t1.b <> 3);
select * from cs1 where a in (select t1.b from cs2 t1 join cs2 t2 on t1.b=t2.b and t1.c=t2.c);
### Outer query containing joins
select * from cs1 join cs2 on cs1.a=cs2.b and cs1.a in (select b from cs2) order by 1,2,3;
select * from cs1 join cs2 on cs1.a=cs2.b and cs1.a in (select b from cs2) and cs1.a=1;
### Both IN subquery and outer queries containing joins
select * from cs1 join cs2 on cs1.a=cs2.b and cs1.a in (select t1.b from cs2 t1 join cs2 t2 on t1.b=t2.b and t1.b=1) order by 1,2,3;
## NOT IN subquery
### Basic tests
select * from cs1 where a not in (select b from cs2);
select * from cs1 where a not in (select c from cs2);
select * from cs1 where (a+a) not in (select (b+b) from cs2);
select * from cs1 where (a+1) not in (select b from cs2);
#select * from cs1 where hex(a*10) not in (select hex(b*10) from cs2);
### Outer query containing additional WHERE predicates
select * from cs1 where a is not null and a not in (select b from cs2);
select * from cs1 where a not in (select b from cs2) and a is null;
select * from cs1 where a not in (select 2 from cs2) and a not in (select b from cs2);
select * from cs1 where a in (1,3) and a not in (select b from cs2);
### Nested IN predicates
select * from cs1 where a not in (select b from cs2 where b not in (select a from cs1));
#select * from cs1 where a not in (select b from cs2 where b not in (select a from cs1 where a in (2,3)));
#select * from cs1 where a not in (select b from cs2 where b not in (select a from cs1 where a not in (2,3)));
### WHERE predicates in the IN subquery
#select * from cs1 where a not in (select b from cs2 where b=3);
select * from cs1 where a not in (select b from cs2 where b=3 or c=200);
select * from cs1 where a not in (select b from cs2 where b is not null);
### GROUP BY and HAVING predicates in the IN subquery
select * from cs1 where a not in (select b from cs2 group by b);
select * from cs1 where a not in (select count(*) from cs2 group by b);
select * from cs1 where a not in (select count(*) from cs2 group by b having count(*) < 3);
select * from cs1 where a not in (select count(*) from cs2 where b <> 2 group by b having count(*) < 3);
### Window functions in the SELECT clause of IN subquery
select * from cs1 where a not in (select count(c) over (partition by b) from cs2);
#select * from cs1 where a not in (select count(*) over (partition by b) from cs2 where b is null);
select * from cs1 where a not in (select count(*) over (partition by b) from cs2 where b <> 2);
### IN subquery containing joins
select * from cs1 where a not in (select t1.b from cs2 t1, cs2 t2 where t1.b=t2.b);
select * from cs1 where a not in (select t1.b from cs2 t1, cs2 t2 where t1.b=t2.b and t1.b <> 3);
select * from cs1 where a not in (select t1.b from cs2 t1 join cs2 t2 on t1.b=t2.b and t1.c=t2.c);
### Outer query containing joins
select * from cs1 join cs2 on cs1.a=cs2.b and cs1.a not in (select b from cs2);
select * from cs1 join cs2 on cs1.a=cs2.b and cs1.a not in (select b from cs2) and cs1.a=1;
### Both IN subquery and outer queries containing joins
select * from cs1 join cs2 on cs1.a=cs2.b and cs1.a not in (select t1.b from cs2 t1 join cs2 t2 on t1.b=t2.b and t1.b=1) order by 1,2,3;
## NOT IN subquery without NULLs
### Basic tests
select * from cs1 where a not in (select b from cs2 where b is not null);
#select * from cs1 where a not in (select c from cs2 where b is not null);
#select * from cs1 where (a+a) not in (select (b+b) from cs2 where b is not null);
#select * from cs1 where (a+1) not in (select b from cs2 where b is not null);
select * from cs1 where hex(a*10) not in (select hex(b*10) from cs2 where b is not null);
### Outer query containing additional WHERE predicates
select * from cs1 where a is not null and a not in (select b from cs2 where b is not null);
select * from cs1 where a not in (select b from cs2 where b is not null) and a is null;
select * from cs1 where a not in (select 2 from cs2) and a not in (select b from cs2 where b is not null);
select * from cs1 where a in (1,3) and a not in (select b from cs2 where b is not null);
### Nested IN predicates
select * from cs1 where a not in (select b from cs2 where b not in (select a from cs1 where a is not null) and b is not null);
#select * from cs1 where a not in (select b from cs2 where b not in (select a from cs1 where a in (2,3) and a is not null) and b is not null);
select * from cs1 where a not in (select b from cs2 where b not in (select a from cs1 where a not in (2,3) and a is not null) and b is not null);
### GROUP BY and HAVING predicates in the IN subquery
select * from cs1 where a not in (select b from cs2 where b is not null group by b);
select * from cs1 where a not in (select count(*) from cs2 where b is not null group by b);
select * from cs1 where a not in (select count(*) from cs2 where b is not null group by b having count(*) < 3);
### Window functions in the SELECT clause of IN subquery
select * from cs1 where a not in (select count(c) over (partition by b) from cs2 where b is not null);
### IN subquery containing joins
select * from cs1 where a not in (select t1.b from cs2 t1, cs2 t2 where t1.b=t2.b and t1.b is not null);
select * from cs1 where a not in (select t1.b from cs2 t1 join cs2 t2 on t1.b=t2.b and t1.c=t2.c where t1.b is not null);
### Outer query containing joins
select * from cs1 join cs2 on cs1.a=cs2.b and cs1.a not in (select b from cs2 where b is not null);
select * from cs1 join cs2 on cs1.a=cs2.b and cs1.a not in (select b from cs2 where b is not null) and cs1.a=1;
### Both IN subquery and outer queries containing joins
select * from cs1 join cs2 on cs1.a=cs2.b and cs1.a not in (select t1.b from (select b from cs2 where b is not null) t1 join cs2 t2 on t1.b=t2.b and t1.b=1) order by 1,2,3;
# Special cases involving NULLs
select * from cs1 where a in (select b from cs2 where b is null);
select * from cs1 where a in (select b from cs2 where b is not null);
select * from cs1 where a not in (select b from cs2 where b is null);
# Row column case
drop table cs1;
create table cs1 (a int, d int);
insert into cs1 values (1,100), (2,201), (3,302), (4,4000), (null,null);
## IN subquery
### Basic tests
select * from cs1 where (a,d) in (select b,c from cs2);
select * from cs1 where ((a+a),(d+d)) in (select (b+b),(c+c) from cs2);
select * from cs1 where ((a+1),(d+1)) in (select b,c from cs2);
select * from cs1 where (hex(a*10),hex(d*10)) in (select hex(b*10),hex(c*10) from cs2);
### Correlated IN subquery
select * from cs1 where (a,d) in (select b,c from cs2 where cs1.a=cs2.c-299);
### Outer query containing additional WHERE predicates
select * from cs1 where a is not null and (a,d) in (select b,c from cs2);
select * from cs1 where (a,d) in (select b,c from cs2) and a is null;
select * from cs1 where (a,d) in (select 2,201 from cs2) and (a,d) in (select b,c from cs2);
select * from cs1 where a in (1,3) and (a,d) in (select b,c from cs2);
### Nested IN predicates
select * from cs1 where (a,d) in (select b,c from cs2 where (b,c) in (select a,d from cs1));
select * from cs1 where (a,d) in (select b,c from cs2 where (b,c) in (select a,d from cs1 where a in (2,3)));
select * from cs1 where (a,d) in (select b,c from cs2 where (b,c) in (select a,d from cs1 where a not in (2,3)));
### WHERE predicates in the IN subquery
select * from cs1 where (a,d) in (select b,c from cs2 where b=3);
select * from cs1 where (a,d) in (select b,c from cs2 where b=3 or c=200);
select * from cs1 where (a,d) in (select b,c from cs2 where b is not null);
### GROUP BY and HAVING predicates in the IN subquery
select * from cs1 where (a,d) in (select b,c from cs2 group by b,c);
select * from cs1 where (a,d) in (select count(*),c from cs2 group by c);
select * from cs1 where (a,d) in (select count(*),c from cs2 group by c having count(*) < 3);
select * from cs1 where (a,d) in (select count(*),c from cs2 where b <> 2 group by c having count(*) < 3);
### Window functions in the SELECT clause of IN subquery
select * from cs1 where (a,d) in (select count(c) over (partition by b), (count(b) over (partition by c))*100 from cs2);
select * from cs1 where (a,d) in (select count(*) over (partition by b), (count(*) over (partition by c))*100 from cs2 where b is null);
select * from cs1 where (a,d) in (select count(*) over (partition by b), (count(*) over (partition by c))*100 from cs2 where b <> 2);
### IN subquery containing joins
select * from cs1 where (a,d) in (select t1.b,t1.c from cs2 t1, cs2 t2 where t1.b=t2.b);
select * from cs1 where (a,d) in (select t1.b,t1.c from cs2 t1, cs2 t2 where t1.b=t2.b and t1.b <> 3);
select * from cs1 where (a,d) in (select t1.b,t1.c from cs2 t1 join cs2 t2 on t1.b=t2.b and t1.c=t2.c);
### Outer query containing joins
select * from cs1 join cs2 on cs1.a=cs2.b and (cs1.a,cs1.d) in (select b,c from cs2) order by 1,2,3,4;
select * from cs1 join cs2 on cs1.a=cs2.b and (cs1.a,cs1.d) in (select b,c from cs2) and cs1.a=1 order by 1,2,3,4;
### Both IN subquery and outer queries containing joins
select * from cs1 join cs2 on cs1.a=cs2.b and (cs1.a,cs1.d) in (select t1.b,t1.c from cs2 t1 join cs2 t2 on t1.b=t2.b and t1.b=1);
# Prepared statement tests
drop table cs1;
create table cs1 (a int);
insert into cs1 values (1), (2), (3), (4), (null);
### Basic tests
prepare stmt from "select * from cs1 where a in (select b from cs2)";
execute stmt;
execute stmt;
prepare stmt from "select * from cs1 where a in (select c from cs2)";
execute stmt;
execute stmt;
prepare stmt from "select * from cs1 where (a+a) in (select (b+b) from cs2)";
execute stmt;
execute stmt;
prepare stmt from "select * from cs1 where (a+1) in (select b from cs2)";
execute stmt;
execute stmt;
prepare stmt from "select * from cs1 where hex(a*10) in (select hex(b*10) from cs2)";
execute stmt;
execute stmt;
### Correlated IN subquery
prepare stmt from "select * from cs1 where a in (select b from cs2 where cs1.a=cs2.c-299)";
execute stmt;
execute stmt;
### Outer query containing additional WHERE predicates
prepare stmt from "select * from cs1 where a is not null and a in (select b from cs2)";
execute stmt;
execute stmt;
prepare stmt from "select * from cs1 where a in (select b from cs2) and a is null";
execute stmt;
execute stmt;
prepare stmt from "select * from cs1 where a in (select 2 from cs2) and a in (select b from cs2)";
execute stmt;
execute stmt;
prepare stmt from "select * from cs1 where a in (1,3) and a in (select b from cs2)";
execute stmt;
execute stmt;
### Nested IN predicates
prepare stmt from "select * from cs1 where a in (select b from cs2 where b in (select a from cs1))";
execute stmt;
execute stmt;
prepare stmt from "select * from cs1 where a in (select b from cs2 where b in (select a from cs1 where a in (2,3)))";
execute stmt;
execute stmt;
prepare stmt from "select * from cs1 where a in (select b from cs2 where b in (select a from cs1 where a not in (2,3)))";
execute stmt;
execute stmt;
### WHERE predicates in the IN subquery
prepare stmt from "select * from cs1 where a in (select b from cs2 where b=3)";
execute stmt;
execute stmt;
prepare stmt from "select * from cs1 where a in (select b from cs2 where b=3 or c=200)";
execute stmt;
execute stmt;
prepare stmt from "select * from cs1 where a in (select b from cs2 where b is not null)";
execute stmt;
execute stmt;
### GROUP BY and HAVING predicates in the IN subquery
prepare stmt from "select * from cs1 where a in (select b from cs2 group by b)";
execute stmt;
execute stmt;
prepare stmt from "select * from cs1 where a in (select count(*) from cs2 group by b)";
execute stmt;
execute stmt;
prepare stmt from "select * from cs1 where a in (select count(*) from cs2 group by b having count(*) < 3)";
execute stmt;
execute stmt;
prepare stmt from "select * from cs1 where a in (select count(*) from cs2 where b <> 2 group by b having count(*) < 3)";
execute stmt;
execute stmt;
### Window functions in the SELECT clause of IN subquery
prepare stmt from "select * from cs1 where a in (select count(c) over (partition by b) from cs2)";
execute stmt;
execute stmt;
prepare stmt from "select * from cs1 where a in (select count(*) over (partition by b) from cs2 where b is null)";
execute stmt;
execute stmt;
prepare stmt from "select * from cs1 where a in (select count(*) over (partition by b) from cs2 where b <> 2)";
execute stmt;
execute stmt;
### IN subquery containing joins
prepare stmt from "select * from cs1 where a in (select t1.b from cs2 t1, cs2 t2 where t1.b=t2.b)";
execute stmt;
execute stmt;
prepare stmt from "select * from cs1 where a in (select t1.b from cs2 t1, cs2 t2 where t1.b=t2.b and t1.b <> 3)";
execute stmt;
execute stmt;
prepare stmt from "select * from cs1 where a in (select t1.b from cs2 t1 join cs2 t2 on t1.b=t2.b and t1.c=t2.c)";
execute stmt;
execute stmt;
### Outer query containing joins
prepare stmt from "select * from cs1 join cs2 on cs1.a=cs2.b and cs1.a in (select b from cs2) order by 1,2,3";
execute stmt;
execute stmt;
prepare stmt from "select * from cs1 join cs2 on cs1.a=cs2.b and cs1.a in (select b from cs2) and cs1.a=1 order by 1,2,3";
execute stmt;
execute stmt;
### Both IN subquery and outer queries containing joins
prepare stmt from "select * from cs1 join cs2 on cs1.a=cs2.b and cs1.a in (select t1.b from cs2 t1 join cs2 t2 on t1.b=t2.b and t1.b=1) order by 1,2,3";
execute stmt;
execute stmt;
execute stmt;
execute stmt;
drop database test_mcol4617;
View Git Blame Copy Permalink