MySQL Bugs: #40317: Queries with left join on constant FALSE expression may return wrong results

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

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)

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)

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.

Pushed into 6.0.8-alpha  (revid:igor@mysql.com-20081025003549-qoj7exgyu75q7ri1) (version source revid:igor@mysql.com-20081025003549-qoj7exgyu75q7ri1) (pib:5)

Correction: This is pushed into 6.0.9.

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.

As a curiosity: it happens with any WHERE condition if you use MyISAM

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