set @tmp_subselect_nulls=@@optimizer_switch;
set optimizer_switch='semijoin=off';
create table x1(k int primary key, d1 int, d2 int);
create table x2(k int primary key, d1 int, d2 int);
insert into x1 values
(10,   10,   10),
(20,   20,   20),
(21,   20,   null),
(30,   null, 30),
(40,   40,   40);
insert into x2 values
(10,   10,   10),
(20,   20,   20),
(21,   20,   null),
(30,   null, 30);
select *
from x1
where (d1, d2) in (select d1, d2
from x2);
k d1 d2
10 10 10
20 20 20
select *
from x1
where (d1, d2) in (select d1, d2
from x2) is true;
k d1 d2
10 10 10
20 20 20
select *
from x1
where (d1, d2) in (select d1, d2
from x2) is false;
k d1 d2
40 40 40
select *
from x1
where (d1, d2) in (select d1, d2
from x2) is unknown;
k d1 d2
21 20 NULL
30 NULL 30
select *
from x1
where d1 in (select d1
from x2
where x1.d2=x2.d2);
k d1 d2
10 10 10
20 20 20
select *
from x1
where d1 in (select d1
from x2
where x1.d2=x2.d2) is true;
k d1 d2
10 10 10
20 20 20
select *
from x1
where d1 in (select d1
from x2
where x1.d2=x2.d2) is false;
k d1 d2
21 20 NULL
40 40 40
select *
from x1
where d1 in (select d1
from x2
where x1.d2=x2.d2) is unknown;
k d1 d2
30 NULL 30
select *
from x1
where 1 in (select 1
from x2
where x1.d1=x2.d1 and x1.d2=x2.d2);
k d1 d2
10 10 10
20 20 20
select *
from x1
where 1 in (select 1
from x2
where x1.d1=x2.d1 and x1.d2=x2.d2) is true;
k d1 d2
10 10 10
20 20 20
select *
from x1
where 1 in (select 1
from x2
where x1.d1=x2.d1 and x1.d2=x2.d2) is false;
k d1 d2
21 20 NULL
30 NULL 30
40 40 40
select *
from x1
where 1 in (select 1
from x2
where x1.d1=x2.d1 and x1.d2=x2.d2) is unknown;
k d1 d2
select *
from x1
where exists (select *
from x2
where x1.d1=x2.d1 and x1.d2=x2.d2);
k d1 d2
10 10 10
20 20 20
set optimizer_switch= @tmp_subselect_nulls;
drop table x1;
drop table x2;
#
# MDEV-7339 Server crashes in Item_func_trig_cond::val_int
#
select (select 1, 2) in (select 3, 4);
(select 1, 2) in (select 3, 4)
0
select (select NULL, NULL) in (select 3, 4);
(select NULL, NULL) in (select 3, 4)
NULL
#
# End of 5.5 tests
#
#
# MDEV-32555 wrong result with an index and a partially null-rejecting condition
#
create table t1 (a int primary key);
insert t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2 (
b int not null,
c int default null,
d int not null,
e int not null,
unique key (d,b,c)
);
insert t2 values (1,null,1,1),(1,null,2,2),(1,null,3,3),(1,null,4,4),(2,null,1,2),(3,null,1,3),(4,null,2,2),(4,null,1,4);
select (
select sum(t2_.e) from t2 t2_ where t2_.b = a and t2_.c <=> t2.c and t2_.d = 1
) x from t2 left join t1 on a = b;
x
1
2
3
4
1
4
1
1
drop table t1, t2;
#
# End of 10.10 tests
#
#
# MDEV-32868 SELECT NULL,NULL IN (SUBQUERY) returns 0 instead of NULL
#
create table t1 (a int);
insert into t1 values (1);
create table t2 (a int, b int);
insert into t2 values (null, null);
# Scalar subquery returning NULLs as left part of IN
# All NULLs on left, no match possible -> NULL (subquery has rows)
select (select null, null) in (select 1, 2 from t1) from t1;
(select null, null) in (select 1, 2 from t1)
NULL
# One NULL on left, other column matches -> NULL
select (select null, 2) in (select 1, 2 from t1) from t1;
(select null, 2) in (select 1, 2 from t1)
NULL
# One NULL on left, other column doesn't match -> FALSE
select (select null, 3) in (select 1, 2 from t1) from t1;
(select null, 3) in (select 1, 2 from t1)
0
# Table with NULLs as left part
select (select * from t2) in (select 1, 2 from t1) from t1;
(select * from t2) in (select 1, 2 from t1)
NULL
# Exact match -> TRUE
select (select 1, 2) in (select 1, 2 from t1) from t1;
(select 1, 2) in (select 1, 2 from t1)
1
# No match, no NULLs -> FALSE
select (select 3, 4) in (select 1, 2 from t1) from t1;
(select 3, 4) in (select 1, 2 from t1)
0
# Right side has NULLs
create table t3 (a int, b int);
insert into t3 values (1, null), (null, 2);
# Left matches one column, right has NULL in other -> NULL
select (select 1, 2) in (select * from t3) from t1;
(select 1, 2) in (select * from t3)
NULL
# Both sides have NULLs -> NULL
select (select null, 2) in (select * from t3) from t1;
(select null, 2) in (select * from t3)
NULL
drop table t1, t2, t3;
#
# End of 10.11 tests
#