Assertion `0' failed in st_select_lex_unit::optimize with view , UNION and prepared statement

[Philip Stoev]

mysqld: sql_union.cc:538: bool st_select_lex_unit::optimize(): Assertion `0' failed.

backtrace:

#8 0x006b0d98 in __assert_fail () from /lib/libc.so.6
#9 0x0846dbcc in st_select_lex_unit::optimize (this=0xae62aae8) at sql_union.cc:538
#10 0x0846deda in st_select_lex_unit::exec (this=0xae62aae8) at sql_union.cc:599
#11 0x0824c419 in subselect_union_engine::exec (this=0xae62b280) at item_subselect.cc:2992
#12 0x08246003 in Item_subselect::exec (this=0xae62b2f0) at item_subselect.cc:572
#13 0x082464aa in Item_in_subselect::exec (this=0xae62b2f0) at item_subselect.cc:725
#14 0x08247ffe in Item_in_subselect::val_bool (this=0xae62b2f0) at item_subselect.cc:1438
#15 0x081e1e04 in Item::val_bool_result (this=0xae62b2f0) at item.h:842
#16 0x0820e21e in Item_in_optimizer::val_int (this=0xae62cdd8) at item_cmpfunc.cc:1717
#17 0x081ca06c in Item::val_bool (this=0xae62cdd8) at item.cc:197
#18 0x0820adcc in Item_func_not::val_int (this=0xae62b3e0) at item_cmpfunc.cc:333
#19 0x08251d08 in Item_func_trig_cond::val_int (this=0xae6141a0) at item_cmpfunc.h:478
#20 0x081ca06c in Item::val_bool (this=0xae6141a0) at item.cc:197
#21 0x082164bc in Item_cond_and::val_int (this=0xae6142b0) at item_cmpfunc.cc:4495
#22 0x0833d066 in evaluate_join_record (join=0xae635280, join_tab=0xae6138fc, error=0) at sql_select.cc:15230
#23 0x0833cc34 in sub_select (join=0xae635280, join_tab=0xae6138fc, end_of_records=false) at sql_select.cc:15100
#24 0x0833d2d1 in evaluate_join_record (join=0xae635280, join_tab=0xae6136f8, error=0) at sql_select.cc:15297
#25 0x0833cd7c in sub_select (join=0xae635280, join_tab=0xae6136f8, end_of_records=false) at sql_select.cc:15140
#26 0x0833c487 in do_select (join=0xae635280, fields=0xae629698, table=0x0, procedure=0x0) at sql_select.cc:14763
#27 0x08320e5d in JOIN::exec (this=0xae635280) at sql_select.cc:2679
#28 0x0832168a in mysql_select (thd=0x950a6b8, rref_pointer_array=0xae629740, tables=0xae629e68, wild_num=0, fields=..., conds=0xae612b30, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416200192, result=0xae62b738, unit=0xae629324, select_lex=0xae629604)
    at sql_select.cc:2900
#29 0x0831945b in handle_select (thd=0x950a6b8, lex=0xae6292c8, result=0xae62b738, setup_tables_done_option=0) at sql_select.cc:283
#30 0x082b41d9 in execute_sqlcom_select (thd=0x950a6b8, all_tables=0xae629e68) at sql_parse.cc:5102
#31 0x082aafea in mysql_execute_command (thd=0x950a6b8) at sql_parse.cc:2247
#32 0x08363c48 in Prepared_statement::execute (this=0xae6240b0, expanded_query=0xae7fe70c, open_cursor=false) at sql_prepare.cc:3735
#33 0x08363163 in Prepared_statement::execute_loop (this=0xae6240b0, expanded_query=0xae7fe70c, open_cursor=false, packet=0x0, packet_end=0x0)
    at sql_prepare.cc:3416
#34 0x08361a32 in mysql_sql_stmt_execute (thd=0x950a6b8) at sql_prepare.cc:2641
#35 0x082ab013 in mysql_execute_command (thd=0x950a6b8) at sql_parse.cc:2256
#36 0x082b6808 in mysql_parse (thd=0x950a6b8, rawbuf=0xae612a10 "EXECUTE st1", length=11, found_semicolon=0xae7ff228) at sql_parse.cc:6102
#37 0x082a8c30 in dispatch_command (command=COM_QUERY, thd=0x950a6b8, packet=0x9563549 "", packet_length=11) at sql_parse.cc:1221
#38 0x082a808b in do_command (thd=0x950a6b8) at sql_parse.cc:916
#39 0x082a5015 in handle_one_connection (arg=0x950a6b8) at sql_connect.cc:1187
#40 0x00821919 in start_thread () from /lib/libpthread.so.0
#41 0x0076acce in clone () from /lib/libc.so.6

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL

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

repeatable in maria-5.3 . Not repeatable in maria-5.2

bzr version-info:

revision-id: <email address hidden>
date: 2011-09-24 14:45:49 +0200
build-date: 2011-09-26 08:01:19 +0300
revno: 3194
branch-nick: maria-5.3

test case:

CREATE TABLE t1 ( f1 int NOT NULL, f4 varchar(1) NOT NULL) ;
INSERT INTO t1 VALUES (6,'d'),(7,'y');

CREATE TABLE t2 ( f1 int NOT NULL, f2 int NOT NULL) ;
INSERT INTO t2 VALUES (10,7);

CREATE VIEW v2 AS SELECT * FROM t2;

PREPARE st1 FROM "
        SELECT *
        FROM t1
        LEFT JOIN v2 ON ( v2.f2 = t1.f1 )
        WHERE v2.f1 NOT IN (
                SELECT 1 UNION
                SELECT 247
        ) AND t1.f4 != 2
";

EXECUTE st1;

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

There other problem here: When we create the union temporary table it is not distinct then it turn to be distinct some how...

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

A bit simplified test suite:

PREPARE st1 FROM "
        SELECT *
        FROM t1
        LEFT JOIN v2 ON ( v2.f2 = t1.f1 )
        WHERE v2.f1 NOT IN (
                SELECT 1 UNION
                SELECT 247
        )
";

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

abort_on_null is not set for the subquery, it is very strange but still should lead to the correct result...