From 3f619478f796eddbba6e39502fe941b285dd97b1 Mon Sep 17 00:00:00 2001
From: Daniel Baumann <daniel.baumann@progress-linux.org>
Date: Sat, 4 May 2024 20:00:34 +0200
Subject: Adding upstream version 1:10.11.6.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
---
 mysql-test/main/subselect_nulls.test | 129 +++++++++++++++++++++++++++++++++++
 1 file changed, 129 insertions(+)
 create mode 100644 mysql-test/main/subselect_nulls.test

(limited to 'mysql-test/main/subselect_nulls.test')

diff --git a/mysql-test/main/subselect_nulls.test b/mysql-test/main/subselect_nulls.test
new file mode 100644
index 00000000..68575eca
--- /dev/null
+++ b/mysql-test/main/subselect_nulls.test
@@ -0,0 +1,129 @@
+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);
+
+# Test various IN and EXISTS queries with NULL values and UNKNOWN
+# Q1 T=(10, 20) U=(21,30) F=(40)
+select *
+from x1
+where (d1, d2) in (select d1, d2
+                   from x2);
+select *
+from x1
+where (d1, d2) in (select d1, d2
+                   from x2) is true;
+select *
+from x1
+where (d1, d2) in (select d1, d2
+                   from x2) is false;
+select *
+from x1
+where (d1, d2) in (select d1, d2
+                   from x2) is unknown;
+
+# Q2 T=(10, 20) U=(30) F=(21, 40)
+select *
+from x1
+where d1 in (select d1
+             from x2
+             where x1.d2=x2.d2);
+select *
+from x1
+where d1 in (select d1
+             from x2
+             where x1.d2=x2.d2) is true;
+select *
+from x1
+where d1 in (select d1
+             from x2
+             where x1.d2=x2.d2) is false;
+select *
+from x1
+where d1 in (select d1
+             from x2
+             where x1.d2=x2.d2) is unknown;
+
+# Q3 T=(10, 20) U=() F=(21, 30, 40)
+select *
+from x1
+where 1 in (select 1
+            from x2
+            where x1.d1=x2.d1 and x1.d2=x2.d2);
+select *
+from x1
+where 1 in (select 1
+            from x2
+            where x1.d1=x2.d1 and x1.d2=x2.d2) is true;
+select *
+from x1
+where 1 in (select 1
+            from x2
+            where x1.d1=x2.d1 and x1.d2=x2.d2) is false;
+select *
+from x1
+where 1 in (select 1
+            from x2
+            where x1.d1=x2.d1 and x1.d2=x2.d2) is unknown;
+
+# Q4 T=(10, 20) F=(21, 30, 40)
+select *
+from x1
+where exists (select *
+              from x2
+              where x1.d1=x2.d1 and x1.d2=x2.d2);
+
+set optimizer_switch= @tmp_subselect_nulls;
+
+drop table x1;
+drop table x2;
+
+--echo #
+--echo # MDEV-7339 Server crashes in Item_func_trig_cond::val_int
+--echo #
+select (select 1, 2) in (select 3, 4);
+select (select NULL, NULL) in (select 3, 4);
+
+--echo #
+--echo # End of 5.5 tests
+--echo #
+
+--echo #
+--echo # MDEV-32555 wrong result with an index and a partially null-rejecting condition
+--echo #
+
+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;
+
+drop table t1, t2;
+
+--echo #
+--echo # End of 10.10 tests
+--echo #
-- 
cgit v1.2.3