Wrong result with join_cache_level + index_condition_pushdown

[Philip Stoev]

If the following query is executed with join_cache_level=1 and index_condition_pushdown=on (the default values for both options), it returns 1 row whereas all other plans for this query, including full scans, return zero rows.

Query:

SELECT f2 FROM t2
JOIN t1 FORCE KEY (PRIMARY) ON t1.f1 = t2.f4
WHERE t2.f3
AND t2.f3 <= 4
AND t1.f3 IN ( 2 , 1 )
OR t1.f3 > 1
AND t1.f3 < 5
AND t2.f3 BETWEEN 6 AND 6 ;

explain:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 100.00 Using index condition; Using MRR
1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Using MRR; Using join buffer (flat, BNL join)

test case:
CREATE TABLE t1 (f1 varchar(10), f3 int(11), PRIMARY KEY (f3)) ;
INSERT IGNORE INTO t1 VALUES ('y',1),('or',5);

CREATE TABLE t2 (f3 int(11), f2 varchar(1024), f4 varchar(10), PRIMARY KEY (f3)) ;
INSERT IGNORE INTO t2 VALUES (6,'RPOYT','y'),(10,'JINQE','m');

SELECT f2 FROM t2 JOIN t1 FORCE KEY (PRIMARY) ON t1.f1 = t2.f4 WHERE t2.f3 AND t2.f3 <= 4 AND t1.f3 IN ( 2 , 1 ) OR t1.f3 > 1 AND t1.f3 < 5 AND t2.f3 BETWEEN 6 AND 6 ;

maria-5.3 , maria-5.3-wl128 and maria-5.3-wl128-dsmrr-cpk are all affected. The FORCE KEY is only required to produce a stable execution plan, the problem was also observed without FORCE KEY.

[Philip Stoev (philip-stoev)]

Maria-5.2 is not affected, so this is a regression.