Alice Sherepa added a comment - 2017-09-14 09:34 Thanks for the report! I reproduced on 10.2, 10.3 sql_select.cc:18336: enum_nested_loop_state sub_select_cache(JOIN*, JOIN_TAB*, bool): Assertion `cache != __null' failed test case CREATE TABLE `t1` (`id2` int , `k` int ); CREATE TABLE `t2` (`id2` int ,`id3` int ); CREATE TABLE `t3` (`id3` int );   WITH d1 AS ( SELECT SUM (k) FROM t1 JOIN t2 ON t1.id2 = t2.id2 JOIN t3 ON t2.id3 = t3.id3 ), d2 AS ( SELECT SUM (k) FROM t1 JOIN t2 ON t1.id2 = t2.id2 JOIN t3 ON t2.id3 = t3.id3 ) SELECT * FROM d1 UNION SELECT * FROM d2; error log sql_select.cc:18336: enum_nested_loop_state sub_select_cache(JOIN*, JOIN_TAB*, bool): Assertion `cache != __null' failed. 170914 11:24:23 [ERROR] mysqld got signal 6 ; Server version: 10.2.9-MariaDB-debug   stack_bottom = 0x7f88380eeec0 thread_stack 0x49000 /data/bld/10.2/bin/mysqld(my_print_stacktrace+0x38)[0xb573b92d4b] /data/bld/10.2/bin/mysqld(handle_fatal_signal+0x3a3)[0xb57341f01a] /lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7f8869dd7390] /lib/x86_64-linux-gnu/libc.so.6(gsignal+0x38)[0x7f8869190428] /lib/x86_64-linux-gnu/libc.so.6(abort+0x16a)[0x7f886919202a] mysys/stacktrace.c:267(my_print_stacktrace)[0x7f8869188bd7] /lib/x86_64-linux-gnu/libc.so.6(+0x2dc82)[0x7f8869188c82] sql/sql_select.cc:18338(sub_select_cache(JOIN*, st_join_table*, bool))[0xb5732204a9] sql/sql_select.cc:18514(sub_select(JOIN*, st_join_table*, bool))[0xb5732206ea] sql/sql_select.cc:18109(do_select(JOIN*, Procedure*))[0xb57321fefb] sql/sql_select.cc:3483(JOIN::exec_inner())[0xb5731faa61] sql/sql_select.cc:3279(JOIN::exec())[0xb5731f9f02] sql/sql_select.cc:3680(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0xb5731fb0d2] sql/sql_derived.cc:1084(mysql_derived_fill(THD*, LEX*, TABLE_LIST*))[0xb5731831c2] sql/sql_derived.cc:197(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0xb5731813ae] sql/sql_select.cc:11856(st_join_table::preread_init())[0xb573210705] sql/sql_select.cc:18531(sub_select(JOIN*, st_join_table*, bool))[0xb573220774] sql/sql_select.cc:18107(do_select(JOIN*, Procedure*))[0xb57321fea6] sql/sql_select.cc:3483(JOIN::exec_inner())[0xb5731faa61] sql/sql_select.cc:3279(JOIN::exec())[0xb5731f9f02] sql/sql_union.cc:1006(st_select_lex_unit::exec())[0xb5732979b7] sql/sql_union.cc:41(mysql_union(THD*, LEX*, select_result*, st_select_lex_unit*, unsigned long))[0xb573294452] sql/sql_select.cc:351(handle_select(THD*, LEX*, select_result*, unsigned long))[0xb5731ef8c3] sql/sql_parse.cc:6434(execute_sqlcom_select(THD*, TABLE_LIST*))[0xb5731bb964] sql/sql_parse.cc:3461(mysql_execute_command(THD*))[0xb5731b1383] sql/sql_parse.cc:7875(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0xb5731bf2dd] sql/sql_parse.cc:1814(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0xb5731acc7b] sql/sql_parse.cc:1360(do_command(THD*))[0xb5731ab5ee] sql/sql_connect.cc:1354(do_handle_one_connection(CONNECT*))[0xb5732f8542] sql/sql_connect.cc:1261(handle_one_connection)[0xb5732f82c2] /lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7f8869dcd6ba] /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f88692623dd] stack trace Thread 1 (Thread 0x7fa6484bd700 (LWP 15410)): #0 0x00007fa66da69428 in __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:54 #1 0x00007fa66da6b02a in __GI_abort () at abort.c:89 #2 0x00007fa66da61bd7 in __assert_fail_base (fmt=<optimized out>, assertion=assertion@entry=0x7762753a3e "cache != __null", file=file@entry=0x7762752188 "/home/alice/git/10.2/sql/sql_select.cc", line=line@entry=18336, function=function@entry=0x7762755c20 <sub_select_cache(JOIN*, st_join_table*, bool)::__PRETTY_FUNCTION__> "enum_nested_loop_state sub_select_cache(JOIN*, JOIN_TAB*, bool)") at assert.c:92 #3 0x00007fa66da61c82 in __GI___assert_fail (assertion=0x7762753a3e "cache != __null", file=0x7762752188 "/home/alice/git/10.2/sql/sql_select.cc", line=18336, function=0x7762755c20 <sub_select_cache(JOIN*, st_join_table*, bool)::__PRETTY_FUNCTION__> "enum_nested_loop_state sub_select_cache(JOIN*, JOIN_TAB*, bool)") at assert.c:101 #4 0x0000007761d424a9 in sub_select_cache (join=0x7fa5dc0b8630, join_tab=0x7fa5dc0c8260, end_of_records=true) at /home/alice/git/10.2/sql/sql_select.cc:18336 #5 0x0000007761d426ea in sub_select (join=0x7fa5dc0b8630, join_tab=0x7fa5dc0c7eb0, end_of_records=true) at /home/alice/git/10.2/sql/sql_select.cc:18514 #6 0x0000007761d41efb in do_select (join=0x7fa5dc0b8630, procedure=0x0) at /home/alice/git/10.2/sql/sql_select.cc:18109 #7 0x0000007761d1ca61 in JOIN::exec_inner (this=0x7fa5dc0b8630) at /home/alice/git/10.2/sql/sql_select.cc:3483 #8 0x0000007761d1bf02 in JOIN::exec (this=0x7fa5dc0b8630) at /home/alice/git/10.2/sql/sql_select.cc:3278 #9 0x0000007761d1d0d2 in mysql_select (thd=0x7fa5dc000a98, tables=0x7fa5dc069350, wild_num=0, fields=..., conds=0x7fa5dc0c53f0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416184064, result=0x7fa5dc0b8520, unit=0x7fa5dc068bd0, select_lex=0x7fa5dc066900) at /home/alice/git/10.2/sql/sql_select.cc:3678 #10 0x0000007761ca51c2 in mysql_derived_fill (thd=0x7fa5dc000a98, lex=0x7fa5dc004570, derived=0x7fa5dc09e300) at /home/alice/git/10.2/sql/sql_derived.cc:1084 #11 0x0000007761ca33ae in mysql_handle_single_derived (lex=0x7fa5dc004570, derived=0x7fa5dc09e300, phases=96) at /home/alice/git/10.2/sql/sql_derived.cc:197 #12 0x0000007761d32705 in st_join_table::preread_init (this=0x7fa5dc0cc070) at /home/alice/git/10.2/sql/sql_select.cc:11857 #13 0x0000007761d42774 in sub_select (join=0x7fa5dc0b7f10, join_tab=0x7fa5dc0cc070, end_of_records=false) at /home/alice/git/10.2/sql/sql_select.cc:18531 #14 0x0000007761d41ea6 in do_select (join=0x7fa5dc0b7f10, procedure=0x0) at /home/alice/git/10.2/sql/sql_select.cc:18107 #15 0x0000007761d1ca61 in JOIN::exec_inner (this=0x7fa5dc0b7f10) at /home/alice/git/10.2/sql/sql_select.cc:3483 #16 0x0000007761d1bf02 in JOIN::exec (this=0x7fa5dc0b7f10) at /home/alice/git/10.2/sql/sql_select.cc:3278 #17 0x0000007761db99b7 in st_select_lex_unit::exec (this=0x7fa5dc004638) at /home/alice/git/10.2/sql/sql_union.cc:1005 #18 0x0000007761db6452 in mysql_union (thd=0x7fa5dc000a98, lex=0x7fa5dc004570, result=0x7fa5dc0b4dd0, unit=0x7fa5dc004638, setup_tables_done_option=0) at /home/alice/git/10.2/sql/sql_union.cc:41 #19 0x0000007761d118c3 in handle_select (thd=0x7fa5dc000a98, lex=0x7fa5dc004570, result=0x7fa5dc0b4dd0, setup_tables_done_option=0) at /home/alice/git/10.2/sql/sql_select.cc:351 #20 0x0000007761cdd964 in execute_sqlcom_select (thd=0x7fa5dc000a98, all_tables=0x7fa5dc09d0c0) at /home/alice/git/10.2/sql/sql_parse.cc:6434 #21 0x0000007761cd3383 in mysql_execute_command (thd=0x7fa5dc000a98) at /home/alice/git/10.2/sql/sql_parse.cc:3461 #22 0x0000007761ce12dd in mysql_parse (thd=0x7fa5dc000a98, rawbuf=0x7fa5dc073e90 "WITH d1 AS (SELECT SUM(k)\n FROM t1 \n JOIN t2 ON t1.id2 = t2.id2\n JOIN t3 ON t2.id3 = t3.id3\n),\nd2 AS (SELECT SUM(k)\n FROM t1 \n JOIN t2 ON t1.id2 = t2.id2\n JOIN t3 ON t2.id3 = t"..., length=249, parser_state=0x7fa6484bc240, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:7875 #23 0x0000007761ccec7b in dispatch_command (command=COM_QUERY, thd=0x7fa5dc000a98, packet=0x7fa5dc007e99 "WITH d1 AS (SELECT SUM(k)\n FROM t1 \n JOIN t2 ON t1.id2 = t2.id2\n JOIN t3 ON t2.id3 = t3.id3\n),\nd2 AS (SELECT SUM(k)\n FROM t1 \n JOIN t2 ON t1.id2 = t2.id2\n JOIN t3 ON t2.id3 = t"..., packet_length=249, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:1812 #24 0x0000007761ccd5ee in do_command (thd=0x7fa5dc000a98) at /home/alice/git/10.2/sql/sql_parse.cc:1360 #25 0x0000007761e1a542 in do_handle_one_connection (connect=0x7764fa4f48) at /home/alice/git/10.2/sql/sql_connect.cc:1354 #26 0x0000007761e1a2c2 in handle_one_connection (arg=0x7764fa4f48) at /home/alice/git/10.2/sql/sql_connect.cc:1260 #27 0x00007fa66e6a66ba in start_thread (arg=0x7fa6484bd700) at pthread_create.c:333 #28 0x00007fa66db3b3dd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Igor Babaev (Inactive) added a comment - 2017-10-09 18:50 The offending query can be simplified: WITH d1 AS (SELECT SUM(k) FROM t1, t2 as t2 WHERE t1.id2 = t2.id2), d2 AS (SELECT SUM(k) FROM t1, t2 as t2 WHERE t1.id2 = t2.id2) SELECT * FROM d1 UNION SELECT * FROM d2 The EXPLAIN output for this query looks quite strange: MariaDB [test]> EXPLAIN -> WITH d1 AS (SELECT SUM(k) FROM t1, t2 as t2 WHERE t1.id2 = t2.id2), -> d2 AS (SELECT SUM(k) FROM t1, t2 as t2 WHERE t1.id2 = t2.id2) -> SELECT * FROM d1 UNION SELECT * FROM d2; +------+-----------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-----------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | | | 3 | SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 1 | | | 3 | SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using join buffer (flat, BNL join) | | 2 | SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 1 | | | 2 | SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using join buffer (flat, BNL join) | | 4 | RECURSIVE UNION | <derived3> | ALL | NULL | NULL | NULL | NULL | 2 | | | NULL | UNION RESULT | <union1,4> | ALL | NULL | NULL | NULL | NULL | NULL | | +------+-----------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+

Igor Babaev (Inactive) added a comment - 2017-10-12 06:22 A fix for this bug was pushed into the 10.2 tree.