LP:861147 - Assertion `fixed == 1' failed in Item_func_eq::val_int() with semijoin + materialization + max_join_size

This particular example uses max_join_size=10 for brevity, however the issue has been observed with max_join_size=1000000 as well.

mysqld: item_cmpfunc.cc:1838: virtual longlong Item_func_eq::val_int(): Assertion `fixed == 1' failed.

#8 0x006b0d98 in __assert_fail () from /lib/libc.so.6
#9 0x0820e94e in Item_func_eq::val_int (this=0xae750230) at item_cmpfunc.cc:1838
#10 0x081f3d6f in eval_const_cond (cond=0xae750230) at item_func.cc:63
#11 0x083377ce in remove_eq_conds (thd=0xac9e6e0, cond=0xae750230, cond_value=0xae8fdf64) at sql_select.cc:12605
#12 0x083372c5 in remove_eq_conds (thd=0xac9e6e0, cond=0xae750650, cond_value=0xae73d2dc) at sql_select.cc:12486
#13 0x083371c3 in optimize_cond (join=0xae738188, conds=0xae750650, join_list=0xaca01e4, cond_value=0xae73d2dc, cond_equal=0xae73d36c) at sql_select.cc:12453
#14 0x0831b920 in JOIN::optimize (this=0xae738188) at sql_select.cc:975
#15 0x08321e11 in mysql_select (thd=0xac9e6e0, rref_pointer_array=0xaca025c, tables=0xae713188, wild_num=1, fields=..., conds=0xae72fd40, og_num=0,
order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764224, result=0xae72fe80, unit=0xac9fe40, select_lex=0xaca0120)
at sql_select.cc:2886
#16 0x08319c67 in handle_select (thd=0xac9e6e0, lex=0xac9fde4, result=0xae72fe80, setup_tables_done_option=0) at sql_select.cc:283
#17 0x082b4434 in execute_sqlcom_select (thd=0xac9e6e0, all_tables=0xae713188) at sql_parse.cc:5110
#18 0x082ab1f1 in mysql_execute_command (thd=0xac9e6e0) at sql_parse.cc:2250
#19 0x082b6a63 in mysql_parse (thd=0xac9e6e0,
rawbuf=0xae712ec0 "SELECT *\nFROM t1\nWHERE ( 1 , 3 ) IN (\nSELECT t2.f1 , MAX( t3.f3 ) \nFROM t2\nJOIN t3\nWHERE t3.f4 IN (\nSELECT t4.f5\nFROM t4\nSTRAIGHT_JOIN t5\nWHERE t4.f4 < t2.f5 \n)\n) AND ( 'p' , 'k' ) IN (\nSELECT f4 , f"..., length=212, found_semicolon=0xae8ff228) at sql_parse.cc:6110
#20 0x082a8e40 in dispatch_command (command=COM_QUERY, thd=0xac9e6e0,
packet=0xacf7321 "SELECT *\nFROM t1\nWHERE ( 1 , 3 ) IN (\nSELECT t2.f1 , MAX( t3.f3 ) \nFROM t2\nJOIN t3\nWHERE t3.f4 IN (\nSELECT t4.f5\nFROM t4\nSTRAIGHT_JOIN t5\nWHERE t4.f4 < t2.f5 \n)\n) AND ( 'p' , 'k' ) IN (\nSELECT f4 , f"..., packet_length=212) at sql_parse.cc:1221
#21 0x082a829b in do_command (thd=0xac9e6e0) at sql_parse.cc:916
#22 0x082a527f in handle_one_connection (arg=0xac9e6e0) at sql_connect.cc:1191
#23 0x00821919 in start_thread () from /lib/libpthread.so.0
#24 0x0076acce in clone () from /lib/libc.so.6

explain also crashes.

minimal optimizer switch: semijoin=on,materialization=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

test case:

CREATE TABLE t1 ( f2 int) ;

CREATE TABLE t2 ( f1 int, f3 int, f4 varchar(3), f5 varchar(35)) ;
INSERT INTO t2 VALUES (4057,9,'USA','Visalia'),(3993,11,'USA','Waco'),(3948,14,'USA','Warren'),(3813,57,'USA','Washington'),(4010,11,'USA','Waterbury'),(4017,11,'USA','West Covina'),(4004,11,'USA','West Valley City'),(4033,10,'USA','Westminster'),(3842,34,'USA','Wichita'),(4018,10,'USA','Wichita Falls'),(3899,19,'USA','Winston-Salem'),(3914,17,'USA','Worcester'),(3888,20,'USA','Yonkers');

CREATE TABLE t3 ( f3 int, f4 varchar(3)) ;
INSERT INTO t3 VALUES (86,'USA');

CREATE TABLE t4 ( f3 int, f4 varchar(3), f5 varchar(52)) ;
INSERT INTO t4 VALUES (0,'RUS','Belorussian'),(0,'USA','Portuguese');

CREATE TABLE t5 ( f2 int) ;

CREATE TABLE t6 ( f4 varchar(3));
INSERT INTO t6 VALUES ('RUS'),('USA');

SET SESSION max_join_size=10;
SET SESSION optimizer_switch='semijoin=ON,materialization=ON';
SELECT *
FROM t1
WHERE ( 1 , 3 ) IN (
SELECT t2.f1 , MAX( t3.f3 )
FROM t2
JOIN t3
WHERE t3.f4 IN (
SELECT t4.f5
FROM t4
STRAIGHT_JOIN t5
WHERE t4.f4 < t2.f5
)
) AND ( 'p' , 'k' ) IN (
SELECT f4 , f4 FROM t6
);

bzr version-info
revision-id: <email address hidden>
date: 2011-09-27 17:40:04 +0300
build-date: 2011-09-28 08:43:25 +0300
revno: 3197
branch-nick: maria-5.3