Details
-
Type:
Technical task
-
Status: Closed
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
CREATE TABLE t1 ( pk INT PRIMARY KEY, a INT, b INT, c VARCHAR(2), KEY(a), KEY(b), KEY(c) ) ENGINE=LevelDB; INSERT INTO t1 VALUES (1, 11, 101, 'p'), ( 2, 12, 102, 'h'), (3, 13, 103, 'z'), (4, 14, 104, 'h'); SELECT a, b, c FROM t1 WHERE c IN ( 'p', 'u' ) OR c IN ( 'h', 'l', 'gw' ) OR ( b > 255 OR a IN ( 223, 255 ) ) AND ( b = 500 OR c IS NULL ) ; a b c 11 101 p
Expected result (all 3 rows satisfy the first two conditions, the last big OR shouldn't matter):
a b c 11 101 p 12 102 h 14 104 h
EXPLAIN:
EXPLAIN EXTENDED
SELECT a, b, c FROM t1 WHERE
c IN ( 'p', 'u' )
OR c IN ( 'h', 'l', 'gw' )
OR ( b > 255 OR a IN ( 223, 255 ) )
AND ( b = 500 OR c IS NULL )
;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index_merge a,b,c b,a,c 5,5,5 NULL 50 100.00 Using sort_union(b,a,c); Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`c` in ('p','u')) or (`test`.`t1`.`c` in ('h','l','gw')) or (((`test`.`t1`.`b` > 255) or (`test`.`t1`.`a` in (223,255))) and ((`test`.`t1`.`b` = 500) or isnull(`test`.`t1`.`c`))))
@@optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on
Test case:
CREATE TABLE t1 ( pk INT PRIMARY KEY, a INT, b INT, c VARCHAR(2), KEY(a), KEY(b), KEY(c) ) ENGINE=LevelDB; INSERT INTO t1 VALUES (1, 11, 101, 'p'), ( 2, 12, 102, 'h'), (3, 13, 103, 'z'), (4, 14, 104, 'h'); SELECT a, b, c FROM t1 WHERE c IN ( 'p', 'u' ) OR c IN ( 'h', 'l', 'gw' ) OR ( b > 255 OR a IN ( 223, 255 ) ) AND ( b = 500 OR c IS NULL ) ;
revision-id: psergey@askmonty.org-20130123185233-7xokxc9ck9nosb2l revno: 4509 branch-nick: mysql-5.6-leveldb
Gliffy Diagrams
Attachments
Issue Links
- links to
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
The query plan looks incorrect: the index_merge has a branch for
c IN ( 'p', 'u' ) (1)
but does not have anything for
OR c IN ( 'h', 'l', 'gw' ) (2)
which seems wrong, because the WHERE clause has form of "(1) OR (2) OR ...".
It is likely that the problem is observable only on leveldb, because other storage engine return different range estimates and so the optimizer chooses different query plan.
Filed a bug report with a test case involving InnoDB: http://bugs.mysql.com/bug.php?id=68194
So, it's not LevelDB-specific.
The upstream bug is said to have been fixed in 5.6.11, so i'm decreasing the priority of this one, but leaving it open till merge with 5.6.11, for the reference.
The index_merge problem is tracked as MDEV-4103. Closing this issue.
optimizer_trace shows this plan is used:
"chosen_range_access_summary": {
{ "type": "range_scan", "index": "b", "rows": 10, "ranges": [ "255 < b" ] }"range_access_plan": {
"type": "index_merge",
"index_merge_of": [
,
{ "type": "range_scan", "index": "a", "rows": 20, "ranges": [ "223 <= a <= 223", "255 <= a <= 255" ] },
{ "type": "range_scan", "index": "c", "rows": 20, "ranges": [ "p <= c <= p", "u <= c <= u" ] }]
},
"rows_for_plan": 50,
"cost_for_plan": 104.91,
"chosen": true
}