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

Bug #859375 reported by Philip Stoev
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MariaDB
Fix Released
High
Oleksandr "Sanja" Byelkin

Bug Description

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;

Changed in maria:
milestone: none → 5.3
Changed in maria:
importance: Undecided → High
Changed in maria:
status: New → Confirmed
Changed in maria:
assignee: nobody → Oleksandr "Sanja" Byelkin (sanja-byelkin)
Revision history for this message
Oleksandr "Sanja" Byelkin (sanja-byelkin) wrote :

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

Revision history for this message
Oleksandr "Sanja" Byelkin (sanja-byelkin) wrote :

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
        )
";

Changed in maria:
status: Confirmed → In Progress
Revision history for this message
Oleksandr "Sanja" Byelkin (sanja-byelkin) wrote :

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

Changed in maria:
status: In Progress → Fix Committed
Changed in maria:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.