HAVING not observed with aggregate +subquery and semijoin=off

[Philip Stoev]

Repeatable on maria-5.3. Not repeatable on mysql-5.5. Requires semijoin=off.

Even after the fix for 702345, the following query:

SELECT MIN( t1.f10 ) AS field1
FROM t1 , t2
WHERE t2.f2 IN ( SELECT f3 FROM t3 )
HAVING field1 < 's';

returns NULL even though NULL does not match the HAVING clause.

minimal optimizer_switch='semijoin=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=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,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=on,table_elimination=off

explain:

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

test case:

SET SESSION optimizer_switch='semijoin=off';

CREATE TABLE t1 (f3 int, f10 varchar(1), f11 int, KEY (f10) );
INSERT INTO t1 VALUES (NULL,'a',0),(8,'b',0);

CREATE TABLE t2 (f2 int);
INSERT INTO t2 VALUES (7);

CREATE TABLE t3 (f3 int);
INSERT INTO t3 VALUES (0),(8);

SELECT MIN( t1.f10 ) AS field1
FROM t1 , t2
WHERE t2.f2 IN ( SELECT f3 FROM t3 )
HAVING field1 < 's';

bzr version-info:

revision-id: <email address hidden>
date: 2011-07-06 17:26:01 -0700
build-date: 2011-07-07 16:01:22 +0300
revno: 3088
branch-nick: maria-5.3

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

in 5.2 this bug fixed as bug:938518