Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

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
+#