The following query returns no rows in maria-5.3-mwl90 with materialization , even though it returns 1 row ("16") in maria-5.3 and when run with materialization=off.

bzr version-info:

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

test case:

CREATE TABLE t1 ( f2 int(11)) ;
INSERT IGNORE INTO t1 VALUES ('7'),('9'),('7'),('4'),('2'),('6'),('8'),('5'),('6'),('188'),('2'),('1'),('1'),('0'),('9'),('4');

CREATE TABLE t2 ( f1 int(11), f2 int(11)) ENGINE=MyISAM;
INSERT IGNORE INTO t2 VALUES ('1','1');

CREATE TABLE t3 ( f1 int(11), f2 int(11), f3 int(11), PRIMARY KEY (f1)) ;
INSERT IGNORE INTO t3 VALUES ('16','6','1'),('18','3','4'),('19',NULL,'9'),('20','0','6'),('41','2','0'),('42','2','5'),('43','9','6'),('44','7','4'),('45','1','4'),('46','222','238'),('47','3','6'),('48','6','6'),('49',NULL,'1'),('50','5','1');

SET SESSION join_cache_level = 1;
SET SESSION optimizer_switch='materialization=on';

SELECT f1 FROM t3
WHERE ( f1 ) NOT IN ( SELECT MAX( f2 ) FROM t1 )
AND ( f3 ) IN ( SELECT MIN( f1 ) FROM t2 )
AND f1 IN ( SELECT COUNT( f2 ) FROM t1 )
;

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 1
1 PRIMARY <subquery4> ALL distinct_key NULL NULL NULL 16 Using join buffer (flat, BNL join)
1 PRIMARY t3 ALL PRIMARY NULL NULL NULL 14 Using where; Using join buffer (flat, BNL join)
4 SUBQUERY t1 ALL NULL NULL NULL NULL 16
3 SUBQUERY t2 system NULL NULL NULL NULL 1
2 SUBQUERY t1 ALL NULL NULL NULL NULL 16

in addition, the EXPLAIN lists a key, "distinct_key" that was not defined by the user. If it is an internal key, it must be placed in brackets < > .