Not repeatable with maria-5.3. The following query returns no rows when executed with jkl =8 , join_cache_bka=OFF , even though there is 1 row that matches the WHERE predicate.

bzr version-info:

revision-id: <email address hidden>
date: 2011-02-20 11:35:26 +0300
build-date: 2011-02-24 15:30:06 +0200
revno: 2922
branch-nick: maria-5.3-mwl90

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 system NULL NULL NULL NULL 1
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 const 1 Using where
1 PRIMARY <subquery3> hash distinct_key distinct_key 5 <subquery2>.COUNT( f3 ) 1 Using where; Using join buffer (flat, BNLH join)
3 SUBQUERY t1 system NULL NULL NULL NULL 1
2 SUBQUERY t2 system NULL NULL NULL NULL 1

test case:

SET SESSION join_cache_level = 8;
SET SESSION optimizer_switch = 'join_cache_bka=off';

CREATE TABLE t1 ( f1 int(11));
INSERT INTO t1 VALUES (1);

CREATE TABLE t2 ( f3 int(11));
INSERT INTO t2 VALUES (7);

CREATE TABLE t3 ( f1 int(11)) ;
INSERT INTO t3 VALUES (1);

SELECT * FROM t3
WHERE f1 IN ( SELECT COUNT( f3 ) FROM t2 )
AND f1 IN ( SELECT MIN( f1 ) FROM t1 );