Details
-
Technical task
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
None
-
None
-
None
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
|
Attachments
Issue Links
- links to
Activity
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
}