bzr commit into mysql-6.0-bugteam branch (sergefp:2987)

[Sergey Petrunia]

#At file:///home/spetrunia/dev/mysql-6.0-bugteam/ based on revid:gshchepa@stripped

 2987 Sergey Petrunia	2009-01-28 [merge]
      Merge 5.1-bugteam -> 6.0-bugteam
modified:
  mysql-test/r/subselect.result
  mysql-test/t/subselect.test
  sql/item_cmpfunc.cc

=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2009-01-16 14:28:04 +0000
+++ b/mysql-test/r/subselect.result	2009-01-28 19:58:23 +0000
@@ -4659,7 +4659,6 @@ SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.
 a	incorrect
 1	1
 DROP TABLE t1,t2,t3;
-End of 5.1 tests.
 CREATE TABLE t1( a INT );
 INSERT INTO t1 VALUES (1),(2);
 CREATE TABLE t2( a INT, b INT );
@@ -4921,3 +4920,40 @@ ERROR 42000: You have an error in your S
 SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v );
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO @v )' at line 1
 DROP TABLE t1, t2;
+#
+# BUG#37822: Correlated subquery with IN and IS UNKNOWN provides wrong result
+#
+create table t1(id integer primary key, g integer, v integer, s char(1));
+create table t2(id integer primary key, g integer, v integer, s char(1));
+insert into t1 values
+(10, 10, 10,   'l'),
+(20, 20, 20,   'l'),
+(40, 40, 40,   'l'),
+(41, 40, null, 'l'),
+(50, 50, 50,   'l'),
+(51, 50, null, 'l'),
+(60, 60, 60,   'l'),
+(61, 60, null, 'l'),
+(70, 70, 70,   'l'),
+(90, 90, null, 'l');
+insert into t2 values
+(10, 10, 10,   'r'),
+(30, 30, 30,   'r'),
+(50, 50, 50,   'r'),
+(60, 60, 60,   'r'),
+(61, 60, null, 'r'),
+(70, 70, 70,   'r'),
+(71, 70, null, 'r'),
+(80, 80, 80,   'r'),
+(81, 80, null, 'r'),
+(100,100,null, 'r');
+select *
+from t1
+where v in(select v
+from t2
+where t1.g=t2.g) is unknown;
+id	g	v	s
+51	50	NULL	l
+61	60	NULL	l
+drop table t1, t2;
+End of 5.1 tests.

=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test	2009-01-16 14:28:04 +0000
+++ b/mysql-test/t/subselect.test	2009-01-28 19:58:23 +0000
@@ -3503,12 +3503,8 @@ INSERT INTO t3 VALUES (1,1,1), (2,32,1);
 explain 
 SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
 SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
-
 DROP TABLE t1,t2,t3;
 
---echo End of 5.1 tests.
-
-
 #
 # Bug#33204: INTO is allowed in subselect, causing inconsistent results
 #
@@ -3733,5 +3729,41 @@ SELECT * FROM t1 WHERE a IN ( SELECT 1 U
 SELECT * FROM t1 WHERE EXISTS ( SELECT 1 INTO @v );
 --error ER_PARSE_ERROR
 SELECT * FROM t1 WHERE a IN ( SELECT 1 INTO @v );
-
 DROP TABLE t1, t2;
+
+--echo #
+--echo # BUG#37822: Correlated subquery with IN and IS UNKNOWN provides wrong result
+--echo #
+create table t1(id integer primary key, g integer, v integer, s char(1));
+create table t2(id integer primary key, g integer, v integer, s char(1));
+insert into t1 values
+  (10, 10, 10,   'l'),
+  (20, 20, 20,   'l'),
+  (40, 40, 40,   'l'),
+  (41, 40, null, 'l'),
+  (50, 50, 50,   'l'),
+  (51, 50, null, 'l'),
+  (60, 60, 60,   'l'),
+  (61, 60, null, 'l'),
+  (70, 70, 70,   'l'),
+  (90, 90, null, 'l');
+insert into t2 values
+  (10, 10, 10,   'r'),
+  (30, 30, 30,   'r'),
+  (50, 50, 50,   'r'),
+  (60, 60, 60,   'r'),
+  (61, 60, null, 'r'),
+  (70, 70, 70,   'r'),
+  (71, 70, null, 'r'),
+  (80, 80, 80,   'r'),
+  (81, 80, null, 'r'),
+  (100,100,null, 'r');
+
+select *
+from t1
+where v in(select v
+           from t2
+           where t1.g=t2.g) is unknown;
+drop table t1, t2;
+
+--echo End of 5.1 tests.

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2009-01-16 11:53:32 +0000
+++ b/sql/item_cmpfunc.cc	2009-01-28 19:58:23 +0000
@@ -1622,8 +1622,8 @@ void Item_in_optimizer::cleanup()
 
 bool Item_in_optimizer::is_null()
 {
-  cache->store(args[0]);
-  return (null_value= (cache->null_value || args[1]->is_null()));
+  val_int();
+  return null_value;
 }