Wrong result with view , derived EXISTS subquery, join_cache_level = 0

[Philip Stoev]

The following query:

SELECT v1.b
FROM v1
WHERE EXISTS (
 SELECT t2.b
 FROM t2
 WHERE t2.c < v1.c
);

returns no rows when executed with join_cache_level=0 and

| b |
+----+
| 19 |

otherwise.

explain:

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+---------------+---------+---------+------+------+--------------------------+
| 1 | PRIMARY | t1 | index | NULL | PRIMARY | 4 | NULL | 2 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | t2 | system | NULL | NULL | NULL | NULL | 1 | |

optimizer 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=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

bzr version-info
revision-id: <email address hidden>
date: 2011-10-17 03:42:56 -0700
build-date: 2011-10-18 13:36:56 +0300
revno: 3239
branch-nick: maria-5.3

repeatable with 5.3 without derived_merge and derived_with_keys. Not repeatable on maria 5.2, mysql 5.5 or with join_cache_level=1. Not repeatable with SELECT * in place of SELECT v1.b

test case:

CREATE TABLE t1 ( b int(11) NOT NULL , c int(11), PRIMARY KEY (b)) ;
INSERT INTO t1 VALUES (18,2),(19,9);

CREATE TABLE t2 ( b int(11) NOT NULL , c int(11)) ;
INSERT INTO t2 VALUES (10,8);

SET SESSION join_cache_level=0;
CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1;

SELECT v1.b
FROM v1
WHERE EXISTS (
 SELECT t2.b
 FROM t2
 WHERE t2.c < v1.c
);