Assertion `0' failed in replace_where_subcondition with semijoin subquery in HAVING

[Philip Stoev]

The following query:

SELECT *
FROM t3
WHERE (t3.a) IN (
        SELECT t1.d
        FROM t1
        HAVING ( 4 ) IN (
                SELECT t2.b
                FROM t2
        )
);

asserts as follows:

mysqld: opt_subselect.cc:1014: bool replace_where_subcondition(JOIN*, Item**, Item*, Item*, bool): Assertion `0' failed.

#8 0x006b0d98 in __assert_fail () from /lib/libc.so.6
#9 0x083db52b in replace_where_subcondition (join=0xae52cdd8, expr=0xae531fa8, old_cond=0xae513b10, new_cond=0xae514588, do_fix_fields=false)
    at opt_subselect.cc:1014
#10 0x083db09c in convert_join_subqueries_to_semijoins (join=0xae52cdd8) at opt_subselect.cc:843
#11 0x08319680 in JOIN::optimize (this=0xae52cdd8) at sql_select.cc:897
#12 0x081b5e0c in st_select_lex::optimize_unflattened_subqueries (this=0xb1f20fc) at sql_lex.cc:3126
#13 0x083e2867 in JOIN::optimize_unflattened_subqueries (this=0xae527b50) at opt_subselect.cc:4318
#14 0x0831bd23 in JOIN::optimize (this=0xae527b50) at sql_select.cc:1622
#15 0x0831ff2b in mysql_select (thd=0xb1f06b8, rref_pointer_array=0xb1f2238, tables=0xae5127d0, wild_num=1, fields=..., conds=0xae513c40, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xae513de8, unit=0xb1f1e1c, select_lex=0xb1f20fc)
    at sql_select.cc:2887
#16 0x08317da3 in handle_select (thd=0xb1f06b8, lex=0xb1f1dc0, result=0xae513de8, setup_tables_done_option=0) at sql_select.cc:283
#17 0x082b3171 in execute_sqlcom_select (thd=0xb1f06b8, all_tables=0xae5127d0) at sql_parse.cc:5090
#18 0x082a9f75 in mysql_execute_command (thd=0xb1f06b8) at sql_parse.cc:2234
#19 0x082b57ad in mysql_parse (thd=0xb1f06b8,
    rawbuf=0xae5125f0 "SELECT *\nFROM t3\nWHERE (t3.a) IN (\nSELECT t1.d\nFROM t1\nHAVING ( 4 ) IN (\nSELECT t2.b\nFROM t2\n)\n)", length=96,
    found_semicolon=0xae883228) at sql_parse.cc:6091
#20 0x082a7bf2 in dispatch_command (command=COM_QUERY, thd=0xb1f06b8,
    packet=0xb249549 "SELECT *\nFROM t3\nWHERE (t3.a) IN (\nSELECT t1.d\nFROM t1\nHAVING ( 4 ) IN (\nSELECT t2.b\nFROM t2\n)\n)", packet_length=96)
    at sql_parse.cc:1211
#21 0x082a704d in do_command (thd=0xb1f06b8) at sql_parse.cc:906
#22 0x082a40b5 in handle_one_connection (arg=0xb1f06b8) at sql_connect.cc:1186
#23 0x00821919 in start_thread () from /lib/libpthread.so.0
#24 0x0076acce in clone () from /lib/libc.so.6

explain also asserts.

minimal optimizer switch:semijoin=ON
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=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=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=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-08-23 15:51:47 +0300
build-date: 2011-08-26 12:02:03 +0300
revno: 3166
branch-nick: maria-5.3

test case:

CREATE TABLE t1 ( d int );
INSERT INTO t1 VALUES (2),(2),(0),(2),(2);

CREATE TABLE t2 ( b int );
INSERT INTO t2 VALUES (4),(3),(3);

CREATE TABLE t3 ( a int );

SET SESSION optimizer_switch='semijoin=ON';

SELECT *
FROM t3
WHERE (t3.a) IN (
        SELECT t1.d
        FROM t1
        HAVING ( 4 ) IN (
                SELECT t2.b
                FROM t2
        )
);