Bug #24127 (a,b) IN (SELECT c,d ...) can produce wrong results if a and/or b are NULLs.
Submitted: 9 Nov 2006 7:53 Modified: 3 Feb 2007 2:25
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version: OS:
Assigned to: Sergey Petrunya CPU Architecture:Any
Tags: NULL IN, subquery, subselect, wrong query result

[9 Nov 2006 7:53] Sergey Petrunya
Description:
Subqueries in form (a,b) IN (SELECT c,d ...) can produce wrong results if 
a and/or b are NULLs.

How to repeat:
create table t2 (a int, b int, oref int);
insert into t2 values (NULL,1, 100);
insert into t2 values (NULL,2, 100);

create table t1 (a int, b int, c int);
insert into t1 select 2*A, 2*A, 100 from test.one_k;
alter table t1 add key(a,b);

mysql> select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2;
+------+------+------+------+
| a    | b    | oref | Z    |
+------+------+------+------+
| NULL |    1 |  100 | NULL | 
| NULL |    2 |  100 | NULL | 
+------+------+------+------+

This is incorrect, the value of Z should be NULL for the second row.

The EXPLAIN is:
+----+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------+
| id | select_type        | table | type           | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------+
|  1 | PRIMARY            | t2    | ALL            | NULL          | NULL | NULL    | NULL |    2 |                          | 
|  2 | DEPENDENT SUBQUERY | t1    | index_subquery | a             | a    | 5       | func |    2 | Using index; Using where | 
+----+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------+
[9 Nov 2006 8:14] Sergey Petrunya
Analysis: 
For the posted example, BUG#8804 code functions as designed. However, the fix for 
BUG#8804 did not account for row-based IN subqueries.

S1: (NULL, NULL) IN (SELECT a,b ...)
S2: (NULL, 1   ) IN (SELECT a,b ...)
S3: (1,    NULL) IN (SELECT a,b ...)

BUG#8804-fix code does the following: 
 if (left tuple contains a NULL) 
   if (SELECT produces any rows at all)
      return NULL;
   else 
      return FALSE;

This is adequate for handling query S1. For query S2 we need a different strategy:
S2's result is 
  NULL, if the SELECT produces a row (<anything>, 1) or a row (<anything>, NULL)
  FALSE otherwise.
i.e. if some elements in the left part of IN are not NULLs, we still need them to be pushed to the subquery.

For S3, the situation is analogous.
[15 Nov 2006 3:14] Bugs System
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/15326

ChangeSet@1.2304, 2006-11-15 06:14:17+03:00, sergefp@mysql.com +13 -0
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...)
  - Make the code produce correct result: use an array of triggers to turn on/off equalities for each
    compared column. Also turn on/off optimizations based on those equalities.
  - Make EXPLAIN output show "Full scan on NULL key" for tables for which we switch between 
    ref/unique_subquery/index_subquery and ALL access.
  - Fix incorrect presense of "Using index" for index/unique-based subqueries (BUG#22930)
  // bk trigger note: this commit refers to BUG#24127
[6 Dec 2006 1:34] Bugs System
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/16487

ChangeSet@1.2305, 2006-12-06 04:33:30+03:00, sergefp@mysql.com +5 -0
  BUG#24127: Post-review fixes batch 1:
  Added comments and fixed not covered wrong query result problems:
  - Let indexsubquery_engine have the "HAVING" clause (which has predicates
    insert by subquery rewrite code) and use it to filter rows. This is needed
    for correct query results.
  - init_read_record_seq() now reads the first record, as it is required
    by its calling convention
[13 Dec 2006 12:06] Bugs System
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/16877

ChangeSet@1.2306, 2006-12-13 15:05:27+03:00, sergefp@mysql.com +4 -0
  BUG#24127: (a,b) IN (SELECT c,d ...) can produce wrong results if a and/or b are NULLs:
  Post-review fixes:
   - since index_subuquery now has HAVING clause, show it in EXPLAIN EXTENDED.
[11 Jan 2007 9:15] Bugs System
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/17913

ChangeSet@1.2307, 2007-01-11 12:15:08+03:00, sergefp@mysql.com +7 -0
  BUG#24127: post-review fixes, batch 3 (more to come)
[12 Jan 2007 18:18] Bugs System
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/18051

ChangeSet@1.2376, 2007-01-12 21:17:47+03:00, sergefp@mysql.com +9 -0
  BUG#24127: Post-review fixes part 3:
[12 Jan 2007 20:23] Bugs System
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/18070

ChangeSet@1.2376, 2007-01-12 23:22:41+03:00, sergefp@mysql.com +14 -0
  BUG#24127: (a,b) IN (SELECT c,d ...) can produce wrong results if a and/or b are NULLs:
  - Make the code produce correct result: use an array of triggers to turn on/off equalities for each
    compared column. Also turn on/off optimizations based on those equalities.
  - Make EXPLAIN output show "Full scan on NULL key" for tables for which we switch between
    ref/unique_subquery/index_subquery and ALL access.
  - index_subquery engine now has HAVING clause when it is needed, and it is
    displayed in EXPLAIN EXTENDED
  - Fix incorrect presense of "Using index" for index/unique-based subqueries (BUG#22930)
  // bk trigger note: this commit refers to BUG#24127
[24 Jan 2007 19:24] Bugs System
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/18746

ChangeSet@1.2386, 2007-01-24 22:23:50+03:00, sergefp@mysql.com +3 -0
  BUG#24127: merge fixes: If subselect is a union, dont allocate several 
  identical pushed_cond_guards arrays. Allocate only one always.
[24 Jan 2007 23:27] Bugs System
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/18755

ChangeSet@1.2387, 2007-01-25 02:27:28+03:00, sergefp@mysql.com +1 -0
  BUG#24127: wrong result for (null,not-null) IN (SELECT a,b ...):
  Fixed typo problem that surfaced after the merge
[25 Jan 2007 7:00] Bugs System
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/18759

ChangeSet@1.2408, 2007-01-25 10:00:09+03:00, sergefp@mysql.com +3 -0
  BUG#24127: merge: update tests results
[28 Jan 2007 2:20] Igor Babaev
The fix has been pushed into 5.0.36, 5.1.16-beta main trees.
[3 Feb 2007 2:25] Paul DuBois
Noted in 5.0.36, 5.1.16 changelogs.

Expressions of the form (a, b) IN (SELECT c, d ...) could produce
incorrect results if a, b, or both are NULL.
[19 Feb 2007 17:38] Bugs System
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/20063

ChangeSet@1.2408, 2007-02-19 09:36:54+01:00, mhansson@linux-st28.site +3 -0
  BUG#24127: merge: update tests results