Wrong result (missing row) with join_cache_level>=4, firstmatch=OFF, ORDER BY, Aria

[Elena Stepanova]

The following query

SELECT * FROM t1, t2
WHERE c IN ( SELECT c FROM t1, t2 )
ORDER BY b;

produces a wrong result (with one row in t1 and N rows in t2, returns only one row instead of N) if it's run with firstmatch=OFF and join_cache_level>=4.

Only reproducible if t2 is Aria and t1 is either Aria or MyISAM.

I failed to see any difference between EXPLAIN output for join_cache_level=1 vs 4, but I will put both here anyway (first two outputs below).

EXPLAIN output with join_cache_level=4, firstmatch=OFF (wrong result)

1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 Using filesort
1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
1 PRIMARY t2 range c c 5 NULL 2 100.00 Using index condition
1 PRIMARY t2 ref c c 5 test.t2.c 2 100.00 Using index; Start temporary; End temporary

select 'x' AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` semi join (`test`.`t1` join `test`.`t2`) join `test`.`t2` where (`test`.`t2`.`c` = `test`.`t2`.`c`) order by `test`.`t2`.`b`

EXPLAIN output with join_cache_level=1, firstmatch=OFF (correct result)

1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 Using filesort
1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
1 PRIMARY t2 range c c 5 NULL 2 100.00 Using index condition
1 PRIMARY t2 ref c c 5 test.t2.c 2 100.00 Using index; Start temporary; End temporary

select 'x' AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` semi join (`test`.`t1` join `test`.`t2`) join `test`.`t2` where (`test`.`t2`.`c` = `test`.`t2`.`c`) order by `test`.`t2`.`b`

EXPLAIN output with join_cache_level=1, firstmatch=ON (correct result)

1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 Using filesort
1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
1 PRIMARY t2 range c c 5 NULL 2 100.00 Using index condition
1 PRIMARY t2 ref c c 5 test.t2.c 2 100.00 Using index; FirstMatch(t2)

select 'x' AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` semi join (`test`.`t1` join `test`.`t2`) join `test`.`t2` where (`test`.`t2`.`c` = `test`.`t2`.`c`) order by `test`.`t2`.`b`

Minimal optimizer_switch: firstmatch=off

Full 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=on,derived_merge=on,derived_with_keys=on,firstmatch=off,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,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:

SET optimizer_switch='firstmatch=off';
SET join_cache_level=4; # Reproducible with 4..8

CREATE TABLE t1 ( a CHAR(1) ) ENGINE=Aria;
INSERT INTO t1 VALUES ('x');
CREATE TABLE t2 ( b INT, c INT, KEY(b), KEY(c)) ENGINE=Aria;
INSERT INTO t2 VALUES (1,0);
INSERT INTO t2 VALUES (2,8);

SELECT * FROM t1, t2
WHERE c IN ( SELECT c FROM t1, t2 )
ORDER BY b;

# End of test case

# Expected result:
#
# a b c
# x 1 0
# x 2 8
#
# Result:
#
# a b c
# x 1 0

[Elena Stepanova (elenst)]

bzr version-info
revision-id: <email address hidden>
date: 2011-12-08 04:22:38 +0400
build-date: 2011-12-09 17:06:06 +0200
revno: 3337
branch-nick: maria-5.3