MySQL Bugs: #23478: Incorrect query result for index

Bug #23478	Incorrect query result for index_subquery-based IN subquery
Submitted:	19 Oct 2006 19:55	Modified:	28 Nov 2006 19:59
Reporter:	Sergey Petrunya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	4.1/5.0/5.1BK	OS:	Linux (Linux)
Assigned to:	Igor Babaev	CPU Architecture:	Any
Tags:	IN, index_subquery, subquery, subselect

Description:
An IN subquery that is executed via index_subquery access method produces wrong result (detected while working on BUG#8804)

How to repeat:
create table t10 (a int);
insert into t10 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t11 (a int, key(a));
insert into t11 select 1 + 2*(A.a + 10 * B.a) from t10 A, t10 B;
insert into t11 values (NULL);
# Ok, t11 has NULL, 1, 3, 5, ...

create table three (a int);
insert into three values (1),(2),(3);

explain select a,a in (select a from t11) from three;
+----+--------------------+-------+----------------+---------------+------+-----
----+------+------+-------------+
| id | select_type        | table | type           | possible_keys | key  |
key_len | ref  | rows | Extra       |
+----+--------------------+-------+----------------+---------------+------+-----
----+------+------+-------------+
|  1 | PRIMARY            | three | ALL            | NULL          | NULL |   
NULL | NULL |    3 |             | 
|  2 | DEPENDENT SUBQUERY | t11   | index_subquery | a             | a    |     
 5 | func |    2 | Using index | 
+----+--------------------+-------+----------------+---------------+------+-----
----+------+------+-------------+
2 rows in set (0.00 sec)

The query result will be incorrect:
select a,a in (select a from t11) from three;
+------+--------------------------+
| a    | a in (select a from t11) |
+------+--------------------------+
|    1 |                        1 | 
|    2 |                     NULL | 
|    3 |                     NULL | 
+------+--------------------------+
3 rows in set (0.00 sec)

select a,a in (select a from t11) from three order by a desc;
+------+--------------------------+
| a    | a in (select a from t11) |
+------+--------------------------+
|    3 |                        1 | 
|    2 |                     NULL | 
|    1 |                     NULL | 
+------+--------------------------+
3 rows in set (0.00 sec)

If a subuqery engine doesn't use index, the result will be correct:

create table t11n as select * from t11;
select a,a in (select a from t11n) from three;
+------+---------------------------+
| a    | a in (select a from t11n) |
+------+---------------------------+
|    1 |                         1 | 
|    2 |                      NULL | 
|    3 |                         1 | 
+------+---------------------------+

Version: 4.1.22-debug
tip: 
ChangeSet@1.2522.63.1, 2006-10-13 14:26:28+04:00, kroki@moonlight.intranet +4 -0
  Merge moonlight.intranet:/home/tomash/src/mysql_ab/mysql-4.0
  into  moonlight.intranet:/home/tomash/src/mysql_ab/mysql-4.1-bug9678
  MERGE: 1.1346.881.1

Initial analysis:

index-lookup based execution engine may produce incorrect result when 
oe IS NOT NULL, and SELECT produces a set without matching value and a NULL.
It seems that some flag is not being reset.

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/14028

ChangeSet@1.2532, 2006-10-19 23:02:11-07:00, igor@rurik.mysql.com +2 -0
  Fixed bug #23478.
  If elements a not top-level IN subquery were accessed by an index and 
  the subquery result set included  a NULL value then the quantified
  predicate that contained the subquery was evaluated to NULL when 
  it should return a non-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/14029

ChangeSet@1.2532, 2006-10-19 23:05:53-07:00, igor@rurik.mysql.com +3 -0
  Fixed bug #23478.
  If elements a not top-level IN subquery were accessed by an index and 
  the subquery result set included  a NULL value then the quantified
  predicate that contained the subquery was evaluated to NULL when 
  it should return a non-null value.

Pushed in 4.1.23/5.0.32/5.1.14-beta

Noted in 4.1.23, 5.0.32, 5.1.14 changelogs.