Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.39, 10.0.13, 5.3.13
-
None
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
|