Bug #53648 | Where clause with IS TRUE generates different plan | ||
---|---|---|---|
Submitted: | 14 May 2010 14:00 | Modified: | 18 May 2010 6:22 |
Reporter: | Roy Lyseng | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.1.48-bzr | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[14 May 2010 14:00]
Roy Lyseng
[14 May 2010 14:13]
Valeriy Kravchuk
Thank you for the problem report. Verified just as described with recent 5.1.48 from bzr: ... mysql> EXPLAIN -> SELECT t1n.* FROM t1n, t2n -> WHERE t1n.v=t2n.u; +----+-------------+-------+------+---------------+------+---------+------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------------+------+--------------------------+ | 1 | SIMPLE | t1n | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | SIMPLE | t2n | ref | u | u | 5 | test.t1n.v | 2 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+------------+------+--------------------------+ 2 rows in set (0.38 sec) mysql> EXPLAIN -> SELECT t1n.* FROM t1n, t2n -> WHERE t1n.v=t2n.u IS TRUE; +----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------+ | 1 | SIMPLE | t1n | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | SIMPLE | t2n | index | NULL | u | 5 | NULL | 10 | Using where; Using index; Using join buffer | +----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------+ 2 rows in set (0.00 sec) mysql> EXPLAIN SELECT t1n.* FROM t1n, t2n WHERE (t1n.v=t2n.u) IS TRUE; +----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------+ | 1 | SIMPLE | t1n | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | SIMPLE | t2n | index | NULL | u | 5 | NULL | 10 | Using where; Using index; Using join buffer | +----+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------------+ 2 rows in set (0.00 sec) mysql> select version(); +--------------+ | version() | +--------------+ | 5.1.48-debug | +--------------+ 1 row in set (0.00 sec)
[17 May 2010 21:58]
Omer Barnir
This is not a bug - "IS TRUE" converts a LOGICAL evaluation into a FUNCTION RESULT evaluation.
[18 May 2010 6:22]
Roy Lyseng
It is a bug because the optimizer treats two logically equivalent SQL queries differently. Even though the bug does not manifest in wrong results, it will simplify the optimizer if we consolidated the handling of the two queries into one. In particular, the optimization taken for the two queries should be the same. Notice that IS TRUE is part of a search condition in SQL - it is applied to any logical predicate result (true, false or unknown) and returns another truth value (true or false, but not unknown).