Details
-
Technical task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
Description
SET optimizer_switch='expensive_pred_static_pushdown=on'; |
SET optimizer_use_condition_selectivity=3; |
SET use_stat_tables=PREFERABLY; |
|
|
CREATE TABLE t1 (a INT, b INT, KEY(b)) ENGINE=MyISAM; |
INSERT INTO t1 VALUES (1,1),(2,2),(1,1),(0,0); |
ANALYZE TABLE t1; |
FLUSH TABLES;
|
|
|
SELECT * FROM t1 AS outer_t1 WHERE b IN ( SELECT a FROM t1 WHERE b != 1 ) AND EXISTS ( SELECT 1 FROM t1 WHERE b < outer_t1.a ); |
Expected result:
a b
|
2 2
|
Actual result:
a b
|
2 2
|
0 0
|
(the second row is wrong because it doesn't match the EXISTS condition: there is no row in t1 where b would be less than 0)
Also reproducible with optimizer_use_condition_selectivity=2, without table statistics.
EXPLAIN with expensive_pred_static_pushdown=on:
EXPLAIN EXTENDED SELECT * FROM t1 AS outer_t1 WHERE b IN ( SELECT a FROM t1 WHERE b != 1 ) AND EXISTS ( SELECT 1 FROM t1 WHERE b < outer_t1.a );
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY outer_t1 ALL b NULL NULL NULL 4 100.00
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where
|
2 MATERIALIZED t1 ALL b NULL NULL NULL 4 75.00 Using where
|
3 DEPENDENT SUBQUERY t1 index b b 5 NULL 4 100.00 Using where; Using index
|
Warnings:
|
Note 1276 Field or reference 'test.outer_t1.a' of SELECT #3 was resolved in SELECT #1
|
Note 1003 select `test`.`outer_t1`.`a` AS `a`,`test`.`outer_t1`.`b` AS `b` from `test`.`t1` `outer_t1` semi join (`test`.`t1`) where (<expr_cache><`test`.`outer_t1`.`a`>(exists(select 1 from `test`.`t1` where (`test`.`t1`.`b` < `test`.`outer_t1`.`a`))) and (`test`.`t1`.`b` <> 1))
|
EXPLAIN with expensive_pred_static_pushdown=off:
EXPLAIN EXTENDED SELECT * FROM t1 AS outer_t1 WHERE b IN ( SELECT a FROM t1 WHERE b != 1 ) AND EXISTS ( SELECT 1 FROM t1 WHERE b < outer_t1.a );
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 100.00
|
1 PRIMARY outer_t1 ALL b NULL NULL NULL 4 75.00 Using where; Subqueries: 3; Using join buffer (flat, BNL join)
|
2 MATERIALIZED t1 ALL b NULL NULL NULL 4 75.00 Using where
|
3 DEPENDENT SUBQUERY t1 index b b 5 NULL 4 100.00 Using where; Using index
|
Warnings:
|
Note 1276 Field or reference 'test.outer_t1.a' of SELECT #3 was resolved in SELECT #1
|
Note 1003 select `test`.`outer_t1`.`a` AS `a`,`test`.`outer_t1`.`b` AS `b` from `test`.`t1` `outer_t1` semi join (`test`.`t1`) where ((`test`.`outer_t1`.`b` = `test`.`t1`.`a`) and <expr_cache><`test`.`outer_t1`.`a`>(exists(select 1 from `test`.`t1` where (`test`.`t1`.`b` < `test`.`outer_t1`.`a`))) and (`test`.`t1`.`b` <> 1))
|
revision-id: timour@askmonty.org-20130417090331-mdqmyx1dwkciogqn
|
revno: 3611
|
branch-nick: 10.0-mdev83
|