|
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:
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
|
|