_NEW_ IN/ALL/ANY/SOME behaviour with NULL (SCRUM related)

optimization of left expression evaluation
more descriptive method name

4 files changed, 265 insertions, 1 deletions

diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result
index ba33ee0831d..01d70c7c4c6 100644
--- a/mysql-test/r/func_in.result
+++ b/mysql-test/r/func_in.result
@@ -1,11 +1,95 @@
+select 1 in (1,2,3);
+1 in (1,2,3)
+1
+select 10 in (1,2,3);
+10 in (1,2,3)
+0
+select NULL in (1,2,3);
+NULL in (1,2,3)
+NULL
+select 1 in (1,NULL,3);
+1 in (1,NULL,3)
+1
+select 3 in (1,NULL,3);
+3 in (1,NULL,3)
+1
+select 10 in (1,NULL,3);
+10 in (1,NULL,3)
+NULL
+select 1.5 in (1.5,2.5,3.5);
+1.5 in (1.5,2.5,3.5)
+1
+select 10.5 in (1.5,2.5,3.5);
+10.5 in (1.5,2.5,3.5)
+0
+select NULL in (1.5,2.5,3.5);
+NULL in (1.5,2.5,3.5)
+NULL
+select 1.5 in (1.5,NULL,3.5);
+1.5 in (1.5,NULL,3.5)
+1
+select 3.5 in (1.5,NULL,3.5);
+3.5 in (1.5,NULL,3.5)
+1
+select 10.5 in (1.5,NULL,3.5);
+10.5 in (1.5,NULL,3.5)
+NULL
 drop table if exists t1;
+CREATE TABLE t1 (a int, b int, c int);
+insert into t1 values (1,2,3), (1,NULL,3);
+select 1 in (a,b,c) from t1;
+1 in (a,b,c)
+1
+1
+select 3 in (a,b,c) from t1;
+3 in (a,b,c)
+1
+1
+select 10 in (a,b,c) from t1;
+10 in (a,b,c)
+0
+NULL
+select NULL in (a,b,c) from t1;
+NULL in (a,b,c)
+NULL
+NULL
+drop table t1;
+CREATE TABLE t1 (a float, b float, c float);
+insert into t1 values (1.5,2.5,3.5), (1.5,NULL,3.5);
+select 1.5 in (a,b,c) from t1;
+1.5 in (a,b,c)
+1
+1
+select 3.5 in (a,b,c) from t1;
+3.5 in (a,b,c)
+1
+1
+select 10.5 in (a,b,c) from t1;
+10.5 in (a,b,c)
+0
+NULL
+drop table t1;
+CREATE TABLE t1 (a varchar(10), b varchar(10), c varchar(10));
+insert into t1 values ('A','BC','EFD'), ('A',NULL,'EFD');
+select 'A' in (a,b,c) from t1;
+'A' in (a,b,c)
+1
+1
+select 'EFD' in (a,b,c) from t1;
+'EFD' in (a,b,c)
+1
+1
+select 'XSFGGHF' in (a,b,c) from t1;
+'XSFGGHF' in (a,b,c)
+0
+NULL
+drop table t1;
 CREATE TABLE t1 (field char(1));
 INSERT INTO t1 VALUES ('A'),(NULL);
 SELECT * from t1 WHERE field IN (NULL);
 field
 SELECT * from t1 WHERE field NOT IN (NULL);
 field
-A
 SELECT * from t1 where field = field;
 field
 A
@@ -16,6 +100,7 @@ NULL
 DELETE FROM t1 WHERE field NOT IN (NULL);
 SELECT * FROM t1;
 field
+A
 NULL
 drop table t1;
 create table t1 (id int(10) primary key);
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index d442e4d97ce..8f3914fe493 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -598,3 +598,105 @@ INSERT INTO t1 values (1),(1);
 UPDATE t SET id=(SELECT * FROM t1);
 Subselect returns more than 1 record
 drop table t;
+create table t (a int);
+insert into t values (1),(2),(3);
+select 1 IN (SELECT * from t);
+1 IN (SELECT * from t)
+1
+select 10 IN (SELECT * from t);
+10 IN (SELECT * from t)
+0
+select NULL IN (SELECT * from t);
+NULL IN (SELECT * from t)
+NULL
+update t set a=NULL where a=2;
+select 1 IN (SELECT * from t);
+1 IN (SELECT * from t)
+1
+select 3 IN (SELECT * from t);
+3 IN (SELECT * from t)
+1
+select 10 IN (SELECT * from t);
+10 IN (SELECT * from t)
+NULL
+select 1 > ALL (SELECT * from t);
+1 > ALL (SELECT * from t)
+0
+select 10 > ALL (SELECT * from t);
+10 > ALL (SELECT * from t)
+NULL
+select 1 > ANY (SELECT * from t);
+1 > ANY (SELECT * from t)
+NULL
+select 10 > ANY (SELECT * from t);
+10 > ANY (SELECT * from t)
+1
+drop table t;
+create table t (a varchar(20));
+insert into t values ('A'),('BC'),('DEF');
+select 'A' IN (SELECT * from t);
+'A' IN (SELECT * from t)
+1
+select 'XYZS' IN (SELECT * from t);
+'XYZS' IN (SELECT * from t)
+0
+select NULL IN (SELECT * from t);
+NULL IN (SELECT * from t)
+NULL
+update t set a=NULL where a='BC';
+select 'A' IN (SELECT * from t);
+'A' IN (SELECT * from t)
+1
+select 'DEF' IN (SELECT * from t);
+'DEF' IN (SELECT * from t)
+1
+select 'XYZS' IN (SELECT * from t);
+'XYZS' IN (SELECT * from t)
+NULL
+select 'A' > ALL (SELECT * from t);
+'A' > ALL (SELECT * from t)
+0
+select 'XYZS' > ALL (SELECT * from t);
+'XYZS' > ALL (SELECT * from t)
+NULL
+select 'A' > ANY (SELECT * from t);
+'A' > ANY (SELECT * from t)
+NULL
+select 'XYZS' > ANY (SELECT * from t);
+'XYZS' > ANY (SELECT * from t)
+1
+drop table t;
+create table t (a float);
+insert into t values (1.5),(2.5),(3.5);
+select 1.5 IN (SELECT * from t);
+1.5 IN (SELECT * from t)
+1
+select 10.5 IN (SELECT * from t);
+10.5 IN (SELECT * from t)
+0
+select NULL IN (SELECT * from t);
+NULL IN (SELECT * from t)
+NULL
+update t set a=NULL where a=2.5;
+select 1.5 IN (SELECT * from t);
+1.5 IN (SELECT * from t)
+1
+select 3.5 IN (SELECT * from t);
+3.5 IN (SELECT * from t)
+1
+select 10.5 IN (SELECT * from t);
+10.5 IN (SELECT * from t)
+NULL
+select 1.5 > ALL (SELECT * from t);
+1.5 > ALL (SELECT * from t)
+0
+select 10.5 > ALL (SELECT * from t);
+10.5 > ALL (SELECT * from t)
+NULL
+select 1.5 > ANY (SELECT * from t);
+1.5 > ANY (SELECT * from t)
+NULL
+select 10.5 > ANY (SELECT * from t);
+10.5 > ANY (SELECT * from t)
+1
+drop table t;
diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test
index e5d42ec25c4..7bbc560276f 100644
--- a/mysql-test/t/func_in.test
+++ b/mysql-test/t/func_in.test
@@ -2,7 +2,39 @@
 # test of IN (NULL)
 #
 
+select 1 in (1,2,3);
+select 10 in (1,2,3);
+select NULL in (1,2,3);
+select 1 in (1,NULL,3);
+select 3 in (1,NULL,3);
+select 10 in (1,NULL,3);
+select 1.5 in (1.5,2.5,3.5);
+select 10.5 in (1.5,2.5,3.5);
+select NULL in (1.5,2.5,3.5);
+select 1.5 in (1.5,NULL,3.5);
+select 3.5 in (1.5,NULL,3.5);
+select 10.5 in (1.5,NULL,3.5);
 drop table if exists t1;
+CREATE TABLE t1 (a int, b int, c int);
+insert into t1 values (1,2,3), (1,NULL,3);
+select 1 in (a,b,c) from t1;
+select 3 in (a,b,c) from t1;
+select 10 in (a,b,c) from t1;
+select NULL in (a,b,c) from t1;
+drop table t1;
+CREATE TABLE t1 (a float, b float, c float);
+insert into t1 values (1.5,2.5,3.5), (1.5,NULL,3.5);
+select 1.5 in (a,b,c) from t1;
+select 3.5 in (a,b,c) from t1;
+select 10.5 in (a,b,c) from t1;
+drop table t1;
+CREATE TABLE t1 (a varchar(10), b varchar(10), c varchar(10));
+insert into t1 values ('A','BC','EFD'), ('A',NULL,'EFD');
+select 'A' in (a,b,c) from t1;
+select 'EFD' in (a,b,c) from t1;
+select 'XSFGGHF' in (a,b,c) from t1;
+drop table t1;
+
 CREATE TABLE t1 (field char(1));
 INSERT INTO t1 VALUES ('A'),(NULL);
 SELECT * from t1 WHERE field IN (NULL);
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 8b174882bc6..6a212c38255 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -362,3 +362,48 @@ INSERT INTO t1 values (1),(1);
 -- error 1240
 UPDATE t SET id=(SELECT * FROM t1);
 drop table t;
+
+
+#NULL test
+create table t (a int);
+insert into t values (1),(2),(3);
+select 1 IN (SELECT * from t);
+select 10 IN (SELECT * from t);
+select NULL IN (SELECT * from t);
+update t set a=NULL where a=2;
+select 1 IN (SELECT * from t);
+select 3 IN (SELECT * from t);
+select 10 IN (SELECT * from t);
+select 1 > ALL (SELECT * from t);
+select 10 > ALL (SELECT * from t);
+select 1 > ANY (SELECT * from t);
+select 10 > ANY (SELECT * from t);
+drop table t;
+create table t (a varchar(20));
+insert into t values ('A'),('BC'),('DEF');
+select 'A' IN (SELECT * from t);
+select 'XYZS' IN (SELECT * from t);
+select NULL IN (SELECT * from t);
+update t set a=NULL where a='BC';
+select 'A' IN (SELECT * from t);
+select 'DEF' IN (SELECT * from t);
+select 'XYZS' IN (SELECT * from t);
+select 'A' > ALL (SELECT * from t);
+select 'XYZS' > ALL (SELECT * from t);
+select 'A' > ANY (SELECT * from t);
+select 'XYZS' > ANY (SELECT * from t);
+drop table t;
+create table t (a float);
+insert into t values (1.5),(2.5),(3.5);
+select 1.5 IN (SELECT * from t);
+select 10.5 IN (SELECT * from t);
+select NULL IN (SELECT * from t);
+update t set a=NULL where a=2.5;
+select 1.5 IN (SELECT * from t);
+select 3.5 IN (SELECT * from t);
+select 10.5 IN (SELECT * from t);
+select 1.5 > ALL (SELECT * from t);
+select 10.5 > ALL (SELECT * from t);
+select 1.5 > ANY (SELECT * from t);
+select 10.5 > ANY (SELECT * from t);
+drop table t;