Crash in JOIN::optimize with variables and nested derived tables in maria-5.3

[Philip Stoev]

Not repeatable with maria-5.2. Does not require any tables to run.

test case:

SELECT * FROM ( SELECT * FROM ( SELECT @b ) AS a1 ) AS a2;

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2
2 DERIVED <derived3> ALL NULL NULL NULL NULL 2
3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used

backtrace:

#2 0x0000000000691b54 in handle_segfault (sig=11) at mysqld.cc:2805
#3 <signal handler called>
#4 0x000000000074b5b9 in JOIN::optimize (this=0x0) at sql_select.cc:868
#5 0x00000000008a0b63 in mysql_derived_optimize (thd=0xe8ea108, lex=0xe8ec510, derived=0xe995e60) at sql_derived.cc:755
#6 0x00000000008a1a74 in mysql_handle_single_derived (lex=0xe8ec510, derived=0xe995e60, phases=4) at sql_derived.cc:174
#7 0x000000000070e4c5 in TABLE_LIST::handle_derived (this=0xe995e60, lex=0xe8ec510, phases=4) at table.cc:5766
#8 0x000000000057a833 in st_select_lex::handle_derived (this=0xe970388, lex=0xe8ec510, phases=4) at sql_lex.cc:3159
#9 0x000000000074b681 in JOIN::optimize (this=0xe9a4e10) at sql_select.cc:878
#10 0x00000000008a0b63 in mysql_derived_optimize (thd=0xe8ea108, lex=0xe8ec510, derived=0xe996678) at sql_derived.cc:755
#11 0x00000000008a1a74 in mysql_handle_single_derived (lex=0xe8ec510, derived=0xe996678, phases=4) at sql_derived.cc:174
#12 0x000000000070e4c5 in TABLE_LIST::handle_derived (this=0xe996678, lex=0xe8ec510, phases=4) at table.cc:5766
#13 0x000000000057a833 in st_select_lex::handle_derived (this=0xe8eca98, lex=0xe8ec510, phases=4) at sql_lex.cc:3159
#14 0x000000000074b681 in JOIN::optimize (this=0xe997a10) at sql_select.cc:878
#15 0x000000000074feb7 in mysql_select (thd=0xe8ea108, rref_pointer_array=0xe8ecce8, tables=0xe996678, wild_num=1, fields=..., conds=0x0, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xe971b90, unit=0xe8ec5b0, select_lex=0xe8eca98)
    at sql_select.cc:2877
#16 0x0000000000756376 in handle_select (thd=0xe8ea108, lex=0xe8ec510, result=0xe971b90, setup_tables_done_option=0) at sql_select.cc:283
#17 0x00000000006a3046 in execute_sqlcom_select (thd=0xe8ea108, all_tables=0xe996678) at sql_parse.cc:5087
#18 0x00000000006a4d24 in mysql_execute_command (thd=0xe8ea108) at sql_parse.cc:2231
#19 0x00000000006adabd in mysql_parse (thd=0xe8ea108, rawbuf=0xe96fd40 "SELECT * FROM ( SELECT * FROM ( SELECT @b ) AS a1 ) AS a2", length=57,
    found_semicolon=0x4201bf08) at sql_parse.cc:6088
#20 0x00000000006ae955 in dispatch_command (command=COM_QUERY, thd=0xe8ea108, packet=0xe9669c9 "SELECT * FROM ( SELECT * FROM ( SELECT @b ) AS a1 ) AS a2",
    packet_length=57) at sql_parse.cc:1208
#21 0x00000000006aff63 in do_command (thd=0xe8ea108) at sql_parse.cc:906
#22 0x000000000069a903 in handle_one_connection (arg=0xe8ea108) at sql_connect.cc:1178
#23 0x00000033b600673d in start_thread () from /lib64/libpthread.so.0
#24 0x00000033b58d40cd in clone () from /lib64/libc.so.6

bzr version-info
revision-id: <email address hidden>
date: 2011-08-01 11:05:30 +0200
build-date: 2011-08-02 12:44:39 +0300
revno: 3138
branch-nick: maria-5.3

[Philip Stoev (pstoev-askmonty)]

Original query from user:

SELECT * FROM (
SELECT @rn := if( @lc = l.l_contentid, @rn +1, 0 ) AS rnum, @lc := l_contentid, l.*
FROM vb3_vbseo_likes l, (SELECT @rn :=0, @lc :=0) r
WHERE l_ctype = 1 AND l_cgroup =39820 AND l_contentid IN (289195,289196 )
ORDER BY l_contentid;

[Vladislav Vaintroub (wlad-montyprogram)]

Similar (judging by callstack) crash was reported on Winqual:

000000013F6B1B99 mysqld.exe!JOIN::optimize()[sql_select.cc:868]
000000013F739A55 mysqld.exe!mysql_derived_optimize()[sql_derived.cc:755]
000000013F73925F mysqld.exe!mysql_handle_single_derived()[sql_derived.cc:174]
000000013F68AF79 mysqld.exe!TABLE_LIST::handle_derived()[table.cc:5766]
000000013F6321F8 mysqld.exe!st_select_lex::handle_derived()[sql_lex.cc:3159]
000000013F6B1C1A mysqld.exe!JOIN::optimize()[sql_select.cc:878]
000000013F739A55 mysqld.exe!mysql_derived_optimize()[sql_derived.cc:755]
000000013F73925F mysqld.exe!mysql_handle_single_derived()[sql_derived.cc:174]
000000013F68AF79 mysqld.exe!TABLE_LIST::handle_derived()[table.cc:5766]
000000013F6321F8 mysqld.exe!st_select_lex::handle_derived()[sql_lex.cc:3159]
000000013F6B1C1A mysqld.exe!JOIN::optimize()[sql_select.cc:878]
000000013F6B450A mysqld.exe!mysql_select()[sql_select.cc:2877]
000000013F6B48C6 mysqld.exe!handle_select()[sql_select.cc:283]
000000013F5DC2B6 mysqld.exe!execute_sqlcom_select()[sql_parse.cc:5087]
000000013F5DF260 mysqld.exe!mysql_execute_command()[sql_parse.cc:2231]
000000013F5E3914 mysqld.exe!mysql_parse()[sql_parse.cc:6092]
000000013F5E444C mysqld.exe!dispatch_command()[sql_parse.cc:1210]
000000013F5E4F15 mysqld.exe!do_command()[sql_parse.cc:906]
000000013F6105AD mysqld.exe!handle_one_connection()[sql_connect.cc:1178]
000000013F985B85 mysqld.exe!pthread_start()[my_winthread.c:87]
000000013F95860B mysqld.exe!_callthreadstart()[thread.c:261]
000000013F958695 mysqld.exe!_threadstart()[thread.c:241]
0000000076F2652D kernel32.dll!BaseThreadInitThunk()
000000007705C521 ntdll.dll!RtlUserThreadStart()

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0000000059B3FFF8): =SELECT indizes.idx, indizes2.idx as idx_prev, data.* FROM ( SELECT COUNT(*) AS playcount, COUNT(*) / maxc.m * 100 AS barwidth, COUNT(*) / total.t * 100 AS percentage, CONCAT(a.artist, ' - ', t.name) AS label, previousPlayCount(t.id, null, 1, date('2011-06-01 00:00:00.0'), date('2011-06-30 00:00:00.0')) AS playcount_prev, p.* FROM (SELECT COUNT(*) AS m FROM plays WHERE (DATE(played_on) BETWEEN ifnull(date('2011-06-01 00:00:00.0'),null) AND ifnull(date('2011-06-30 00:00:00.0'),null) OR '2011-06-01 00:00:00.0' IS NULL AND '2011-06-30 00:00:00.0' IS NULL) AND user_id = 1 GROUP BY track_id ORDER BY 1 DESC LIMIT 1) as maxc, (SELECT COUNT(*) AS t FROM plays WHERE (DATE(played_on) BETWEEN ifnull(date('2011-06-01 00:00:00.0'),null) AND ifnull(date('2011-06-30 00:00:00.0'),null) OR '2011-06-01 00:00:00.0' IS NULL AND '2011-06-30 00:00:00.0' IS NULL) AND user_id = 1) as total, artists a INNER JOIN tracks t ON t.a
Connection ID (thread ID): 15
Status: NOT_KILLED