LP:922254 - Assertion `0' failed at item_cmpfunc.cc:5899: Item* Item_equal::get_first(JOIN_TAB*, Item*) with semijoin+materialization on EXPLAIN

#8 0xb747c014 in __assert_fail () from /lib/libc.so.6
#9 0x0824b839 in Item_equal::get_first (this=0x946ef38, context=0x94bf138, field_item=0x945e2c0) at item_cmpfunc.cc:5899
#10 0x08205fe4 in Item_field::replace_equal_field (this=0x945e2c0, arg=0xae8f3fc0 "8\357F\t8\361K\t\274\355F\tP\360F\t")
at item.cc:5038
#11 0x081fbd55 in Item::transform (this=0x945e2c0, transformer=&virtual Item::replace_equal_field(uchar*),
arg=0xae8f3fc0 "8\357F\t8\361K\t\274\355F\tP\360F\t") at item.cc:647
#12 0x0836c6de in substitute_for_best_equal_field (context_tab=0x94bf138, cond=0x945e2c0, cond_equal=0x946edb4,
table_join_idx=0x94be150) at sql_select.cc:11702
#13 0x08353862 in JOIN::optimize (this=0x9476628) at sql_select.cc:1271
#14 0x08358dc0 in mysql_select (thd=0x93edad0, rref_pointer_array=0x93ef65c, tables=0x945dba8, wild_num=1, fields=...,
conds=0x946d760, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764740, result=0x946d888,
unit=0x93ef240, select_lex=0x93ef520) at sql_select.cc:2917
#15 0x083838e1 in mysql_explain_union (thd=0x93edad0, unit=0x93ef240, result=0x946d888) at sql_select.cc:21381
#16 0x082ebf1a in execute_sqlcom_select (thd=0x93edad0, all_tables=0x945dba8) at sql_parse.cc:5127
#17 0x082e3474 in mysql_execute_command (thd=0x93edad0) at sql_parse.cc:2281
#18 0x082ee6d3 in mysql_parse (thd=0x93edad0,
rawbuf=0x945cc20 "EXPLAIN \nSELECT * FROM \n( SELECT * FROM t1 ) AS alias1, \nt2 AS alias2 \nWHERE b = a AND a IN ( \nSELECT alias3.c \nFROM t2 AS alias3, t2 AS alias4 \nWHERE alias4.c = alias3.b \n)", length=175, found_semicolon=0xae8f5234)
at sql_parse.cc:6149
#19 0x082e10d6 in dispatch_command (command=COM_QUERY, thd=0x93edad0, packet=0x9447361 "", packet_length=175)
at sql_parse.cc:1227
#20 0x082e0580 in do_command (thd=0x93edad0) at sql_parse.cc:922
#21 0x082dd545 in handle_one_connection (arg=0x93edad0) at sql_connect.cc:1193
#22 0xb770ab25 in start_thread () from /lib/libpthread.so.0

bzr version-info
revision-id: <email address hidden>
date: 2012-01-25 22:05:20 +0400
build-date: 2012-01-26 23:04:21 +0400
revno: 3395
branch-nick: maria-5.3

Also reproducible on revno 3390, but isn't reproducible on 3389.

Minimal switch: semijoin=on,materialization=on
Full 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=on,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=on,semijoin_with_cache=on,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 = 'semijoin=on,materialization=on';

CREATE TABLE t1 ( a VARCHAR(3) );
CREATE TABLE t2 ( b VARCHAR(3), c VARCHAR(8), KEY(c) );
INSERT INTO t2 VALUES ('USA','Abilene'),('USA','Akron');

EXPLAIN
SELECT * FROM
( SELECT * FROM t1 ) AS alias1,
t2 AS alias2
WHERE b = a AND a IN (
SELECT alias3.c
FROM t2 AS alias3, t2 AS alias4
WHERE alias4.c = alias3.b
);