The following unrealisic query:

SELECT * FROM t1
WHERE (
( SELECT a FROM t2 WHERE a = 9 ) ,
( SELECT a FROM t2 WHERE a = 3 )
) NOT IN (
SELECT b , a
FROM t3
);

crashes as follows when run wtih partial_match_rowid_merge:

#4 <signal handler called>
#5 0x00000000 in ?? ()
#6 0x081f32d8 in Item_func::fix_fields (this=0xae534230, thd=0xb1456a8, ref=0xae534188) at item_func.cc:182
#7 0x0824ec76 in Ordered_key::init (this=0xae534120, col_idx=1) at item_subselect.cc:4856
#8 0x0824fc32 in subselect_rowid_merge_engine::init (this=0xae533e78, non_null_key_parts=0x0, partial_match_key_parts=0xae532dec) at item_subselect.cc:5316
#9 0x0824e494 in subselect_hash_sj_engine::exec (this=0xae532da0) at item_subselect.cc:4664
#10 0x08245287 in Item_subselect::exec (this=0xae528298) at item_subselect.cc:572
#11 0x0824572a in Item_in_subselect::exec (this=0xae528298) at item_subselect.cc:725
#12 0x0824724e in Item_in_subselect::val_bool (this=0xae528298) at item_subselect.cc:1434
#13 0x081e12e6 in Item::val_bool_result (this=0xae528298) at item.h:842
#14 0x0820d65a in Item_in_optimizer::val_int (this=0xae529cb0) at item_cmpfunc.cc:1688
#15 0x081c9650 in Item::val_bool (this=0xae529cb0) at item.cc:197
#16 0x0820a260 in Item_func_not::val_int (this=0xae5283b0) at item_cmpfunc.cc:333
#17 0x0831d373 in JOIN::exec (this=0xae52c270) at sql_select.cc:2111
#18 0x0831fbd2 in mysql_select (thd=0xb1456a8, rref_pointer_array=0xb147224, tables=0xae5130e0, wild_num=1, fields=..., conds=0xae5283b0, og_num=0,
order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xae5284b0, unit=0xb146e0c, select_lex=0xb1470e8)
at sql_select.cc:2891
#19 0x08317a67 in handle_select (thd=0xb1456a8, lex=0xb146db0, result=0xae5284b0, setup_tables_done_option=0) at sql_select.cc:283
#20 0x082b2e4e in execute_sqlcom_select (thd=0xb1456a8, all_tables=0xae5130e0) at sql_parse.cc:5087
#21 0x082a9c52 in mysql_execute_command (thd=0xb1456a8) at sql_parse.cc:2231
#22 0x082b548a in mysql_parse (thd=0xb1456a8,
rawbuf=0xae512ec0 "SELECT * FROM t1\nWHERE (\n( SELECT a FROM t2 WHERE a = 9 ) , \n( SELECT a FROM t2 WHERE a = 3 ) \n) NOT IN (\nSELECT b , a \nFROM t3 \n)", length=131, found_semicolon=0xae8e1228) at sql_parse.cc:6088
#23 0x082a78cf in dispatch_command (command=COM_QUERY, thd=0xb1456a8,
packet=0xb19e561 "SELECT * FROM t1\nWHERE (\n( SELECT a FROM t2 WHERE a = 9 ) , \n( SELECT a FROM t2 WHERE a = 3 ) \n) NOT IN (\nSELECT b , a \nFROM t3 \n)", packet_length=131) at sql_parse.cc:1208
#24 0x082a6d55 in do_command (thd=0xb1456a8) at sql_parse.cc:906
#25 0x082a3dbc in handle_one_connection (arg=0xb1456a8) at sql_connect.cc:1178
#26 0x00821919 in start_thread () from /lib/libpthread.so.0
#27 0x0076acce in clone () from /lib/libc.so.6

minimal optimizer_switch: ,materialization=ON,partial_match_rowid_merge=ON,partial_match_table_scan=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=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 ( a int) ;
INSERT INTO t1 VALUES (20),(30);

CREATE TABLE t2 (a int) ;
INSERT INTO t2 VALUES (3),(9);

CREATE TABLE t3 ( a int, b int) ;
INSERT INTO t3 VALUES (20,5),(30,6);

SET SESSION optimizer_switch='semijoin=OFF,in_to_exists=OFF,materialization=ON,partial_match_rowid_merge=ON,partial_match_table_scan=OFF';

SELECT * FROM t1
WHERE (
( SELECT a FROM t2 WHERE a = 9 ) ,
( SELECT a FROM t2 WHERE a = 3 )
) NOT IN (
SELECT b , a
FROM t3
);

bzr version-info

revision-id: <email address hidden>
date: 2011-08-09 18:34:26 +0300
build-date: 2011-08-11 13:09:22 +0300
revno: 3148
branch-nick: maria-5.3