Details
Major
Description
The problem appeared on 5.3 tree with the following revision:
revno: 3765 revision-id: igor@askmonty.org-20140221052733-ypg4dpfcmy5l0b3q parent: psergey@askmonty.org-20140219143412-rp3flx1pmhw6zawn committer: Igor Babaev <igor@askmonty.org> branch nick: maria-5.3 timestamp: Thu 2014-02-20 21:27:33 -0800 message: After constant row substitution the optimizer should call the method update_used_tables for the the where condition to update cached indicators of constant subexpressions. It should be done before further possible simplification of the where condition. This change caused simplification of the executed where conditions in many test cases.
Test case:
CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (10,8); CREATE TABLE t2 (c INT) ENGINE=MyISAM; INSERT INTO t2 VALUES (8),(9); CREATE TABLE t3 (d INT) ENGINE=MyISAM; INSERT INTO t3 VALUES (3),(8); SELECT * FROM t1 INNER JOIN t2 ON ( c = b ) LEFT JOIN t3 ON ( d = a ) WHERE b IN (1,2,3) OR b = d;
Expected result: empty set
Actual result:
a b c d 10 8 8 8
EXPLAIN:
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select 10 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on(1) where ((`test`.`t2`.`c` = 8) and (`test`.`t3`.`d` = 8))
To see that the result is wrong, compare it with the result without the WHERE clause:
SELECT * FROM t1 INNER JOIN t2 ON ( c = b ) LEFT JOIN t3 ON ( d = a ); a b c d 10 8 8 NULL
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
A fix for this bug was pushed into the 5.3 tree.