MySQL Bugs: #24127: (a,b) IN (SELECT c,d ...) can produce wrong results if a and/or b are NULLs.

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

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 | 
+----+--------------------+-------+----------------+---------------+------+---------+------+------+--------------------------+

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.

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

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

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.

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)

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:

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

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.

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

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

The fix has been pushed into 5.0.36, 5.1.16-beta main trees.

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.

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