join_tab_cmp_straight(): Assertion `!jt2->emb_sj_nest' failed in maria-5.3-mwl89 with semijoin

[Philip Stoev]

Not repeatable in maria-5.3. Requires semijoin.

Assertion:

mysqld: sql_select.cc:5438: int join_tab_cmp_straight(const void*, const void*, const void*): Assertion `!jt2->emb_sj_nest' failed.

backtrace:

#8 0x006b0d98 in __assert_fail () from /lib/libc.so.6
#9 0x0831800f in join_tab_cmp_straight (dummy=0x0, ptr1=0xae89eccc, ptr2=0xae89ecd0) at sql_select.cc:5453
#10 0x08760bc3 in my_qsort2 (base_ptr=0xae89eccc, count=3, size=4,
    cmp=0x8317f8a <join_tab_cmp_straight(void const*, void const*, void const*)>, cmp_argument=0x0) at mf_qsort.c:130
#11 0x08317cef in choose_plan (join=0xae535b48, join_tables=7) at sql_select.cc:5354
#12 0x08312fb3 in make_join_statistics (join=0xae535b48, tables_arg=0xae5134f8, conds=0xae5331f0, keyuse_array=0xae53a898)
    at sql_select.cc:3325
#13 0x0830b216 in JOIN::optimize (this=0xae535b48) at sql_select.cc:1005
#14 0x081b0174 in st_select_lex::optimize_unflattened_subqueries (this=0xae9f670) at sql_lex.cc:3132
#15 0x083cae05 in JOIN::optimize_unflattened_subqueries (this=0xae52dcb8) at opt_subselect.cc:3656
#16 0x0830ccbe in JOIN::optimize (this=0xae52dcb8) at sql_select.cc:1502
#17 0x08310db9 in mysql_select (thd=0xae9dcd8, rref_pointer_array=0xae9f774, tables=0x0, wild_num=0, fields=..., conds=0x0,
    og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764742, result=0xae514978, unit=0xae9f3d4,
    select_lex=0xae9f670) at sql_select.cc:2750
#18 0x08339860 in mysql_explain_union (thd=0xae9dcd8, unit=0xae9f3d4, result=0xae514978) at sql_select.cc:19915
#19 0x082a654f in execute_sqlcom_select (thd=0xae9dcd8, all_tables=0xae5134f8) at sql_parse.cc:5069
#20 0x0829d53f in mysql_execute_command (thd=0xae9dcd8) at sql_parse.cc:2235
#21 0x082a8c70 in mysql_parse (thd=0xae9dcd8,
    rawbuf=0xae512eb0 "EXPLAIN SELECT STRAIGHT_JOIN (\nSELECT f2 FROM t1 WHERE ( f2 ) IN ( SELECT t3.f2 FROM t3 JOIN t2 ON t2.f1 = 1 )\n)", length=113, found_semicolon=0xae8a0228) at sql_parse.cc:6090
#22 0x0829b1c8 in dispatch_command (command=COM_QUERY, thd=0xae9dcd8, packet=0xaef85d9 "", packet_length=113) at sql_parse.cc:1211
#23 0x0829a675 in do_command (thd=0xae9dcd8) at sql_parse.cc:904
#24 0x08297728 in handle_one_connection (arg=0xae9dcd8) at sql_connect.cc:1154
#25 0x00821919 in start_thread () from /lib/libpthread.so.0
#26 0x0076acce in clone () from /lib/libc.so.6

test case:

CREATE TABLE t1 ( f2 int) ;
INSERT INTO t1 VALUES (0),(0);

CREATE TABLE t2 ( f1 int NOT NULL ) ;
INSERT INTO t2 VALUES (0),(0);

CREATE TABLE t3 ( f1 int NOT NULL , f2 int) ;
INSERT INTO t3 VALUES (0,0), (0,0);

EXPLAIN SELECT STRAIGHT_JOIN (
        SELECT f2 FROM t1 WHERE ( f2 ) IN ( SELECT t3.f2 FROM t3 JOIN t2 ON t2.f1 = 1 )
);

[Timour Katchaounov (timour)]

The bug requires both EXPLAIN and STRAIGHT_JOIN.