Wrong result with icp , HAVING , ORDER BY in 5.3-icp

[Philip Stoev]

The following query:

SELECT t1.b, t1.c
FROM t1
JOIN t2
ON t1.a = t2.a
WHERE t1.b != 0
HAVING t1.c != 5
ORDER BY t1.c;

returns no rows in 5.3-icp even though there are rows that match the WHERE and HAVING conditions.

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Using filesort
1 SIMPLE t2 ref a a 770 test.t1.a 1 Using where

bzr version-info
revision-id: <email address hidden>
date: 2011-11-04 05:39:45 -0700
build-date: 2011-11-04 16:17:50 +0200
revno: 3268
branch-nick: maria-5.3-icp

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,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=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_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

test case:

--source include/have_innodb.inc

CREATE TABLE t2 ( a varchar(1024), KEY (a)) ENGINE=InnoDB;
INSERT INTO t2 VALUES ('Ill'),('eckqzsflbzaffti'),('w'),('she'),('gxbwypqtjzwywwer'),('w');

CREATE TABLE t1 ( b int NOT NULL , c int, a varchar(1024), PRIMARY KEY (b)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1,4,'Ill');

SET SESSION optimizer_switch='index_condition_pushdown=ON';

SELECT t1.b, t1.c
FROM t1
JOIN t2
ON t1.a = t2.a
WHERE t1.b != 0
HAVING t1.c != 5
ORDER BY t1.c;