Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
Description
This query:
SELECT a, MIN(d)
FROM t1
WHERE c = 'x'
AND d > 'x'
GROUP BY a;
when executed with ICP, returns rows that do not match the second, nonindexed part, of the WHERE predicate:
------------+
| a | MIN(d) |
------------+
| 2 | x |
| 5 | x |
------------+
explain:
----------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
----------------------------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | t1 | ref | c | c | 4 | const | 1 | Using index condition; Using where; Using temporary; Using filesort |
----------------------------------------------------------------------------------------------------------------------------+
bzr version-info
revision-id: <email address hidden>
date: 2011-10-06 01:21:15 +0400
build-date: 2011-10-07 17:34:34 +0300
revno: 3213
minimal switch: index_condition_pushdown=ON
full switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on
join_cache_level = 1
test case:
DROP TABLE t1;
CREATE TABLE t1 ( a int, c varchar(1), d varchar(1), KEY c (c));
INSERT INTO t1 VALUES (2,'x','x'),(5,'x','x');
SET SESSION optimizer_switch='index_condition_pushdown=on';
SELECT a, MIN(d)
FROM t1
WHERE c = 'x'
AND d > 'x'
GROUP BY a;