Crash in Item_func::fix_fields with partial_match_rowid_merge and unrealistic double subqueries on the left side

[Philip Stoev]

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

[Timour Katchaounov (timour)]

There is a known architectural deficiency in the way the optimizer
processes the left operand of IN. The problem is related to the fact
that if the left operand is treated always as a single subquery
that may return multiple columns, and the operand is treated
as a sequence. However, if the left operand contains several
subqueries, each returning several columns, then the current
code cannot handle it, because it is a sequence of sequences.

For instance PostgreSQL forbids this construct. In our case
the most reasonable solution is to forbid this construct as
well, because it has very unclear semantics.

Need to check what ANSI SQL says.

[Philip Stoev (pstoev-askmonty)]

Forbidding the construct works out for me. I have already adjusted the RQG to not put 2 subqueries on the left side. I only filed this bug because it was a crash.