bzr commit into mysql-5.1-bugteam branch (sergefp:2751) Bug#37822

[Sergey Petrunia]

#At file:///home/spetrunia/dev/mysql-5.1-bug37822/ based on
revid:aelkin@stripped

 2751 Sergey Petrunia	2009-01-28
      BUG#37822: Correlated subquery with IN and IS UNKNOWN provides wrong result
      Item_in_optimizer::is_null() evaluated "NULL IN (SELECT ...)" to NULL regardless of
      whether subquery produced any records, this was a documented limitation.
      
      The limitation has been removed (see bugs 8804, 24085, 24127) now
      Item_in_optimizer::val_int() correctly handles all cases with NULLs. Make
      Item_in_optimizer::is_null() invoke val_int() to return correct values for
      "NULL IN (SELECT ...)".
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
    Item_in_optimizer::is_null() evaluated "NULL IN (SELECT ...)" to NULL regardless of
    whether subquery produced any records, this was a documented limitation.
    
    The limitation has been removed (see bugs 8804, 24085, 24127) now
    Item_in_optimizer::val_int() correctly handles all cases with NULLs. Make
    Item_in_optimizer::is_null() invoke val_int() to return correct values for
    "NULL IN (SELECT ...)".
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2009-01-15 18:11:25 +0000
+++ b/mysql-test/r/subselect.result	2009-01-28 19:18:27 +0000
@@ -4478,4 +4478,40 @@ WHERE 1 IN (SELECT id FROM t1) WITH CHEC
 DELETE FROM v3;
 DROP VIEW v1,v2,v3;
 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-15 18:11:25 +0000
+++ b/mysql-test/t/subselect.test	2009-01-28 19:18:27 +0000
@@ -3391,4 +3391,39 @@ DELETE FROM v3;
 DROP VIEW v1,v2,v3;
 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-15 18:11:25 +0000
+++ b/sql/item_cmpfunc.cc	2009-01-28 19:18:27 +0000
@@ -1623,8 +1623,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;
 }