| 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 | ||
| Category: | Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 6.0-BKA | OS: | Any |
| Assigned to: | Igor Babaev | Target Version: | 6.0-beta |
| Triage: | Triaged: D2 (Serious) | ||
[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.
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)