diff options
Diffstat (limited to 'mysql-test/main/subselect_nulls.result')
| -rw-r--r-- | mysql-test/main/subselect_nulls.result | 156 |
1 files changed, 156 insertions, 0 deletions
diff --git a/mysql-test/main/subselect_nulls.result b/mysql-test/main/subselect_nulls.result new file mode 100644 index 00000000..9d3b7b2c --- /dev/null +++ b/mysql-test/main/subselect_nulls.result @@ -0,0 +1,156 @@ +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 +# |