Wrong result with icp, ranges in maria-5.3-icp

[Philip Stoev]

The following query:

SELECT * FROM t1 WHERE NOT ( b = 'Texas' ) AND b BETWEEN 'wy' AND 'y' OR ( b = 'Pennsylvania' ) ORDER BY a;

returns

a b
d xdmbdkpjda
f Pennsylvan

when executed with ICP in maria-5.3-icp and

a b
d xdmbdkpjda

otherwise. The table contains a string "Pennsylvan" which is a subset of "Pennsylvania"

EXPLAIN with ICP: note that ICP is not shown anywhere:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 range b b 13 NULL 2 100.00 Using filesort
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (((`test`.`t1`.`b` <> 'Texas') and (`test`.`t1`.`b` between 'wy' nd 'y')) or (`test`.`t1`.`b` = 'Pennsylvania')) order by `test`.`t1`.`a`

EXPLAIN without ICP:
EXPLAIN EXTENDED SELECT * FROM t1 WHERE NOT ( b = 'Texas' ) AND b BETWEEN 'wy' AND 'y' OR ( b = 'Pennsylvania' ) ORDER BY a;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 range b b 13 NULL 2 100.00 Using where; Using filesort
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (((`test`.`t1`.`b` <> 'Texas') and (`test`.`t1`.`b` between 'wy' nd 'y')) or (`test`.`t1`.`b` = 'Pennsylvania')) order by `test`.`t1`.`a`

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=on,deried_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_tble_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cche_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

 bzr version-info
revision-id: <email address hidden>
date: 2011-11-02 01:22:11 -0700
build-date: 2011-11-02 13:53:50 +0200
revno: 3267
branch-nick: maria-5.3-icp

test case:

CREATE TABLE t1 ( a varchar(64), b varchar(10), KEY (a), KEY (b)) ;
INSERT INTO t1 VALUES ('Ohio','Iowa'),('k','d'),('bdkpj','mbdkpjdanp'),('d','xdmbdkpjda'),('fkxdmbdkpjdanpje','o'),('f','Pennsylvan'),('Virginia','ei');

SET SESSION optimizer_switch='index_condition_pushdown=on';
SELECT * FROM t1 WHERE NOT ( b = 'Texas' ) AND b BETWEEN 'wy' AND 'y' OR ( b = 'Pennsylvania' ) ORDER BY a;
SET SESSION optimizer_switch='index_condition_pushdown=off';
SELECT * FROM t1 WHERE NOT ( b = 'Texas' ) AND b BETWEEN 'wy' AND 'y' OR ( b = 'Pennsylvania' ) ORDER BY a;

[Philip Stoev (pstoev-askmonty)]

See also https://bugs.launchpad.net/maria/+bug/884175

[Philip Stoev (pstoev-askmonty)]

Another test case with a similar EXPLAIN . The query below returns rows even though no rows match the WHERE predicate:

--source include/have_innodb.inc
CREATE TABLE t1 ( a int NOT NULL , b int, c int, d varchar(1), PRIMARY KEY (a), KEY (c), KEY (d,b)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (12,2,2008,'c'),(14,1,2001,'q'),(15,8,2001,'y');

CREATE TABLE t2 ( b int, d varchar(1), KEY (d)) ENGINE=InnoDB;
INSERT INTO t2 VALUES (0,NULL),(4,'d'),(8,'g'),(NULL,'x'),(NULL,'f'),(0,'p'),(NULL,'j'),(8,'c');

SET SESSION optimizer_switch='index_condition_pushdown=on';

SELECT t1.c
FROM t1
JOIN t2
ON ( t2.d = t1.d )
WHERE t1.a > 165
OR (
        t1.b > 165
        AND t1.b < 165 + 63
)
GROUP BY t1.c
LIMIT 2;