Details
-
Type:
Technical task
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: 10.0.3
-
Component/s: None
-
Labels: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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Hide
Timour Katchaounov
added a comment -
Permalink
- pushed to the mdev-83 feature tree
Show
Timour Katchaounov
added a comment -
pushed to the mdev-83 feature tree
