Bug #40134 Wrong result when 'not exists' optimization and join buffer are used together

• Submitted: 18 Oct 2008 21:29
• Modified: 22 Nov 2010 1:16
• Reporter: Igor Babaev
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 6.0-BKA
• OS: Any
• Assigned to: Igor Babaev
• CPU Architecture: Any

[18 Oct 2008 21:29] Igor Babaev

Description:
When the optimizer decides to join an inner table of an outer join using both 'not exists' optimization and join buffer a wrong result set can be returned.

How to repeat:
The following test case demonstrates the problem on the mysql-6.0-bka-preview tree:

mysql> set join_cache_level=1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (a int not null);
Query OK, 0 rows affected (0.47 sec)

mysql> insert into t1 values (2), (4), (3), (5), (1);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> create table t2 (a int not null, b int not null, index i_a(a));
Query OK, 0 rows affected (0.43 sec)

mysql> insert into t2 values (4,10), (2,10), (2,30), (2,20), (4,20);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> explain select * from t1 left join t2 on t1.a=t2.a where t2.b is null;
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref       | rows | Extra                   |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------------------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL      |    5 |                         |
|  1 | SIMPLE      | t2    | ref  | i_a           | i_a  | 4       | test.t1.a |    2 | Using where; Not exists |
+----+-------------+-------+------+---------------+------+---------+-----------+------+-------------------------+
2 rows in set (0.00 sec)

mysql> select * from t1 left join t2 on t1.a=t2.a where t2.b is null;
+---+------+------+
| a | a    | b    |
+---+------+------+
| 3 | NULL | NULL |
| 5 | NULL | NULL |
| 1 | NULL | NULL |
+---+------+------+
3 rows in set (0.01 sec)

mysql> set join_cache_level=6;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from t1 left join t2 on t1.a=t2.a where t2.b is null;
+----+-------------+-------+------+---------------+------+---------+-----------+------+--------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref       | rows | Extra                                      |
+----+-------------+-------+------+---------------+------+---------+-----------+------+--------------------------------------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL      |    5 |                                            |
|  1 | SIMPLE      | t2    | ref  | i_a           | i_a  | 4       | test.t1.a |    2 | Using where; Not exists; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+-----------+------+--------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from t1 left join t2 on t1.a=t2.a where t2.b is null;
+---+------+------+
| a | a    | b    |
+---+------+------+
| 4 |    4 |   10 |
| 2 |    2 |   10 |
| 3 | NULL | NULL |
| 5 | NULL | NULL |
| 1 | NULL | NULL |
+---+------+------+
5 rows in set (0.00 sec)

[18 Oct 2008 21:34] Igor Babaev

The problem can be observed only with the BKA code.

[19 Oct 2008 3:05] 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/56536

2658 Igor Babaev	2008-10-18
      Fixed bug #40134.
      After a match for the on expression of an outer join has been
      found the pushdown condition attached to the inner tables must
      be re-evaluated as they contain conditional expressions 
      dependent on the value of the flag 'found' that is set on
      the 'match found' event.
      By mistake when a join buffer was used to join an inner table
      of an outer join and 'not exist' optimization was applied to
      the join operation no re-evaluation of the pushdown condition
      attached to the table was performed.

[14 Dec 2008 11:07] Bugs System

Pushed into 6.0.8-alpha  (revid:[e-mail]-20081019030924-32nvxaghaez00rlq) (version source revid:[e-mail]-20081016151624-70chzf0ilmb10a6s) (pib:5)

[14 Jan 2009 0:36] Paul DuBois

Correction: This is pushed into 6.0.9.

[14 Jan 2009 0:49] Paul DuBois

Noted in 6.0.9 changelog.

When the optimizer joined an inner table of an outer join using both
"not exists" optimization and a join buffer, an incorrect result set
could be returned.

[16 Aug 2010 6:32] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100816062819-bluwgdq8q4xysmlg) (version source revid:[e-mail]-20100816062612-enatdwnv809iw3s9) (pib:20)

[13 Nov 2010 16:18] Bugs System

Pushed into mysql-trunk 5.6.99-m5 (revid:[e-mail]-20101113155825-czmva9kg4n31anmu) (version source revid:[e-mail]-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)

[22 Nov 2010 1:16] Paul DuBois

Noted in 5.6.1 changelog.

[23 Nov 2010 2:29] Paul DuBois

Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.