Bug #37822 Correlated subquery with IN and IS UNKNOWN provides wrong result

Description:
A correlated subquery with IN and IS UNKNOWN is expected to return 2 rows but returns 4.

How to repeat:
create table t1n(id integer primary key, g integer, v integer, s char(1));
create table t2n(id integer primary key, g integer, v integer, s char(1));
insert into t1n values(10, 10, 10,   'l');
insert into t2n values(10, 10, 10,   'r');
insert into t1n values(20, 20, 20,   'l');
insert into t2n values(30, 30, 30,   'r');
insert into t1n values(40, 40, 40,   'l');
insert into t1n values(41, 40, null, 'l');
insert into t1n values(50, 50, 50,   'l');
insert into t1n values(51, 50, null, 'l');
insert into t2n values(50, 50, 50,   'r');
insert into t1n values(60, 60, 60,   'l');
insert into t1n values(61, 60, null, 'l');
insert into t2n values(60, 60, 60,   'r');
insert into t2n values(61, 60, null, 'r');
insert into t1n values(70, 70, 70,   'l');
insert into t2n values(70, 70, 70,   'r');
insert into t2n values(71, 70, null, 'r');
insert into t2n values(80, 80, 80,   'r');
insert into t2n values(81, 80, null, 'r');
insert into t1n values(90, 90, null, 'l');
insert into t2n values(100,100,null, 'r');

select *
from t1n
where v in(select v
           from t2n
           where t1n.g=t2n.g) is unknown;

The bug affects 5.1 also: 

mysql> select *
    -> from t1n
    -> where v in(select v
    ->            from t2n
    ->            where t1n.g=t2n.g) is unknown;
+----+------+------+------+
| id | g    | v    | s    |
+----+------+------+------+
| 41 |   40 | NULL | l    | 
| 51 |   50 | NULL | l    | 
| 61 |   60 | NULL | l    | 
| 90 |   90 | NULL | l    | 
+----+------+------+------+
4 rows in set (0.00 sec)

mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 5.1.25-rc-debug-log | 
+---------------------+
1 row in set (0.00 sec)

Verfied as described. It effects all main bzr trees.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/50077

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

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/64327

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 ...)".

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/64374

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 ...)".

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/64495

2985 Georgi Kodinov	2009-01-29 [merge]
      fixed a non-updated test result of bug #37822 causing failures in test suite.
      modified:
        mysql-test/lib/mtr_report.pl
        mysql-test/r/subselect_no_opts.result

=== modified file 'mysql-test/lib/mtr_report.pl'
--- a/mysql-test/lib/mtr_report.pl	2009-01-06 16:52:32 +0000
+++ b/mysql-test/lib/mtr_report.pl	2009-01-29 13:55:56 +0000
@@ -497,6 +497,11 @@ sub mtr_report_stats ($) {
                 # this test is expected to print warnings
                 ($testname eq 'main.innodb_bug39438') or
 
+                # Bug#39886, logs 'Table full' error message
+                (($testname eq 'main.almost_full' or 
+                  $testname eq 'main.myisam_data_pointer_size_func') and
+                 (/The table '.*' is full/
+                )) or
                 # maria-recovery.test has warning about missing log file
                 /File '.*maria_log.000.*' not found \(Errcode: 2\)/ or
                 # and about marked-corrupted table

=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result	2008-12-29 16:40:21 +0000
+++ b/mysql-test/r/subselect_no_opts.result	2009-01-29 13:55:56 +0000
@@ -4663,7 +4663,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 );
@@ -4925,6 +4924,43 @@ 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.
 set optimizer_switch='';
 show variables like 'optimizer_switch';
 Variable_name	Value

-- 
MySQL Code Commits Mailing List
For list archives: http://lists.mysql.com/commits
To unsubscribe:    http://lists.mysql.com/commits?unsub=commits@bugs.mysql.com

Pushed into 5.1.32 (revid:joro@sun.com-20090203090549-gos3v4320vimrzg6) (version source revid:sergefp@mysql.com-20090128191827-dkgdesnqxuvdtc4i) (merge vers: 5.1.32) (pib:6)

Pushed into 6.0.10-alpha (revid:kostja@sun.com-20090204104420-mw1i2u9lum4bxjo6) (version source revid:davi.arnaut@sun.com-20090129184519-mgp2vr1rna9nk8li) (merge vers: 6.0.10-alpha) (pib:6)

Reference to the documented limitation:

  Item_in_optimizer::is_null() evaluated "NULL IN (SELECT ...)" to NULL regardless of 
  whether subquery produced any records, this was a documented limitation.

Mention of it in the manual is at the top of subquery-restrictions.html
page:

  In MySQL 5.1 before 5.1.16, if you compare a NULL value to a subquery using
  ALL, ANY, or SOME, and the subquery returns an empty result, the comparison
  might evaluate to the non-standard result of NULL rather than to TRUE or FALSE.

And this bug was about that we've fixed everything, except for the case where
the subquery predicate is inside IS NULL. If one runs a query which a
subquery:

  ... expr IN (SELECT ...) IS NULL ...

then he'll get the old, pre-5.1.16 behavior, i.e. "NULL IN (SELECT ...)" will 
always evaluate to NULL.

This fix was to make the subqueries inside IS NULL (and IS UNKNOWN, they are 
internally the same) produce correct results.

Pushed into 5.1.32-ndb-6.3.23 (revid:tomas.ulin@sun.com-20090217131017-6u8qz1edkjfiobef) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)

Pushed into 5.1.32-ndb-6.4.3 (revid:tomas.ulin@sun.com-20090217134419-5ha6xg4dpedrbmau) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)

Pushed into 5.1.32-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090217134216-5699eq74ws4oxa0j) (version source revid:tomas.ulin@sun.com-20090202111723-1zzwax187rtls913) (merge vers: 5.1.32-ndb-6.2.17) (pib:6)

Noted in 5.1.32, 6.0.10 changelogs.

For comparison of NULL to a subquery result inside IS NULL, the
comparison could evaluate to NULL rather than to TRUE or FALSE. This
occurred for expressions such as:

SELECT ... WHERE NULL IN (SELECT ...) IS NULL

Also updated "restrictions" section.