Bug #40317 Queries with left join on constant FALSE expression may return wrong results
Submitted: 24 Oct 2008 18:45 Modified: 14 Jan 2009 18:01
Reporter: Igor Babaev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:6.0-BKA OS:Any
Assigned to: Igor Babaev CPU Architecture:Any

[24 Oct 2008 18:45] Igor Babaev
Description:
A query with an outer join whose on expression is constant evaluated to FALSE may return wrong results. It happens when a join buffer is used for the outer join operation.

How to repeat:
Create and populate table t1 and t2 with the following commands:

create table t1 (a int);
insert into t1 values (30), (40), (20);
create table t2 (b int);
insert into t2 values (200), (100);

Set the mode to use join buffer for outer joins:
mysql> set join_cache_level=6;
Query OK, 0 rows affected (0.00 sec)

See that execution plans for the queries

select * from t1 left join t2 on (1=0);
select * from t1 left join t2 on (1=0) where a=40;

display usage of join buffers for join operations:

mysql> explain select * from t1 left join t2 on (1=0);
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra             |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 |                   |
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
2 rows in set (0.00 sec)

mysql> explain select * from t1 left join t2 on (1=0) where a=40;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra             |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where       |
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
2 rows in set (0.00 sec)

Execute these queries and you get:

mysql> select * from t1 left join t2 on (1=0);
+------+------+
| a    | b    |
+------+------+
|   30 |  200 |
|   40 |  200 |
|   20 |  200 |
|   30 |  100 |
|   40 |  100 |
|   20 |  100 |
+------+------+
6 rows in set (0.00 sec)

mysql> select * from t1 left join t2 on (1=0) where a=40;
+------+------+
| a    | b    |
+------+------+
|   40 |  200 |
|   40 |  100 |
+------+------+
2 rows in set (0.00 sec)

The results are obviously wrong.

They are correct if no join buffer is employed:

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

mysql> explain select * from t1 left join t2 on (1=0);
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    2 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)

mysql> select * from t1 left join t2 on (1=0);
+------+------+
| a    | b    |
+------+------+
|   30 | NULL |
|   40 | NULL |
|   20 | NULL |
+------+------+
3 rows in set (0.00 sec)

mysql> explain select * from t1 left join t2 on (1=0) where a=40;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    2 |             |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

mysql> select * from t1 left join t2 on (1=0) where a=40;
+------+------+
| a    | b    |
+------+------+
|   40 | NULL |
+------+------+
1 row in set (0.01 sec)
[24 Oct 2008 23:13] Igor Babaev
We also can see that when join buffer is not allowed to be used for outer joins there is a problem with EXPLAIN that lacks mentioning the condition pushed to the inner table:  

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

mysql> explain select * from t1 left join t2 on (1=0);
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    2 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)
[25 Oct 2008 0:31] 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/57051

2663 Igor Babaev	2008-10-24
      Fixed bug #40317.
      The condition pushed from constant ON expressions of an outer join
      mistakingly was not set in the select->cond fields of the last inner 
      table if there were no other conditions pushed to this table.
      This led to wrong results returned for queries with outer join on 
      constant conditions equal to FALSE.
      Also the comment that a pushdown condition had been used for the last
      inner table was missing in the output of the EXPLAIN command for
      such a query.
      Wrong results could appear only when join buffer was used for an outer
      join because only in this case the value of select->cond was
      used when condition pushdown operation was performed. If no join
      buffer was employed to join the last inner table the value of
      the select_cond field was used for this purposes. This field always
      was set correctly.
[14 Dec 2008 11:07] Bugs System
Pushed into 6.0.8-alpha  (revid:igor@mysql.com-20081025003549-qoj7exgyu75q7ri1) (version source revid:igor@mysql.com-20081025003549-qoj7exgyu75q7ri1) (pib:5)
[14 Jan 2009 0:35] Paul DuBois
Correction: This is pushed into 6.0.9.
[14 Jan 2009 0:59] Paul DuBois
Noted in 6.0.9 changelog.

A query with an outer join where the ON expression evaluated to the
constant FALSE could return incorrect results when a join buffer was
used for the outer join operation.
[9 May 2020 22:19] Marcos Albe
As a curiosity: it happens with any WHERE condition if you use MyISAM

Attachment: test.myisam.output (application/octet-stream, text), 16.35 KiB.