From: Date: July 19 2008 3:25pm
Subject: bzr commit into mysql-5.1 branch (sergefp:2708) Bug#37822
List-Archive: http://lists.mysql.com/commits/50077
X-Bug: 37822
Message-Id: <20080719132524.3DA5B22B0C5@pslp.localdomain>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

#At file:///home/psergey/dev/mysql-5.1-bugteam-two/

 2708 Sergey Petrunia	2008-07-19
      BUG#37822: Correlated subquery with IN and IS UNKNOWN provides wrong result
      - Make Item_in_optimizer::is_null() evaluate the subquery, not just return this->null_value
modified:
  mysql-test/r/subselect.result
  mysql-test/t/subselect.test
  sql/item_cmpfunc.cc

per-file messages:
  mysql-test/r/subselect.result
    BUG#37822: Correlated subquery with IN and IS UNKNOWN provides wrong result
    - Testcase
  mysql-test/t/subselect.test
    BUG#37822: Correlated subquery with IN and IS UNKNOWN provides wrong result
    - Testcase
  sql/item_cmpfunc.cc
    BUG#37822: Correlated subquery with IN and IS UNKNOWN provides wrong result
    - Make Item_in_optimizer::is_null() evaluate the subquery, not just return this->null_value
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2008-07-07 08:43:56 +0000
+++ b/mysql-test/r/subselect.result	2008-07-19 13:25:17 +0000
@@ -4399,3 +4399,40 @@ SELECT * FROM t2 WHERE b NOT IN (SELECT 
 a	b
 DROP TABLE t1,t2;
 End of 5.0 tests.
+
+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	2008-07-07 08:43:56 +0000
+++ b/mysql-test/t/subselect.test	2008-07-19 13:25:17 +0000
@@ -3300,3 +3300,41 @@ SELECT * FROM t2 WHERE b NOT IN (SELECT 
 DROP TABLE t1,t2;
 
 --echo End of 5.0 tests.
+
+--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	2008-07-15 12:12:08 +0000
+++ b/sql/item_cmpfunc.cc	2008-07-19 13:25:17 +0000
@@ -1618,8 +1618,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;
 }