Bug #70014 MySQL crashes on explain with JSON formatting (debug builds)
Submitted: 13 Aug 2013 13:34 Modified: 19 Nov 2013 16:31
Reporter: Bjorn Seime Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.6.12-debug, 5.6.13-debug , 5.7.2-m12-enterprise-commercial-advanced-debug OS:Linux (Fedora 18)
Assigned to: CPU Architecture:Any
Tags: debug-build, explain, json

[13 Aug 2013 13:34] Bjorn Seime
Description:
MySQL crashes when executing the following query:

explain format=JSON
 select * from (
     select d from item where d in (
         select d from item where c in ('Freeeze'))) crash_query;

How to repeat:
 create database json_explain_bug;
 use json_explain_bug;
 
 create table item (
     id int,
     d int,
     c char(10)
 );
 
 insert into item values (1, 2, 'a'), (2, 3, 'b'), (3, 3, 'c');
 
 create index i_id on item(id);
 create index i_d on item(d);
 create index i_c on item(c);
 
 explain format=JSON
 select * from (
     select d from item where d in (
         select d from item where c in ('Freeeze'))) crash_query;
[13 Aug 2013 13:43] Bjorn Seime
Stack trace from MySQL 5.7.2-m12-debug-log

Attachment: mysqld.1.err (application/octet-stream, text), 6.18 KiB.

[13 Aug 2013 13:45] Bjorn Seime
The crash was also present in version 5.7.2-m12-debug-log. See attached error log for stack trace.
[13 Aug 2013 14:13] Umesh Shastry
Hello Bjorn,

Thank you for the bug report and the test case. 
Verified as described on debug builds of 5.6.13-debug , 5.7.2-m12-debug-log.

Thanks,
Umesh
[15 Aug 2013 13:17] Shane Bester
Adding this comment for search engines to pick up:

Version: '5.7.2-m12-debug'  socket: ''  port: 3306  MySQL Community Server (GPL)
Assertion failed: !"No derived table found!", file opt_explain_json.cc, line 1941

mysqld-debug.exe!my_sigabrt_handler()[my_thr_init.c:451]
mysqld-debug.exe!raise()[winsig.c:593]
mysqld-debug.exe!abort()[abort.c:81]
mysqld-debug.exe!_wassert()[assert.c:153]
mysqld-debug.exe!Explain_format_JSON::end_context()[opt_explain_json.cc:1941]
mysqld-debug.exe!Explain::explain_subqueries()[opt_explain.cc:635]
mysqld-debug.exe!Explain::send()[opt_explain.cc:683]
mysqld-debug.exe!explain_query_specification()[opt_explain.cc:2025]
mysqld-debug.exe!mysql_explain_unit()[opt_explain.cc:2154]
mysqld-debug.exe!explain_query()[opt_explain.cc:2094]
mysqld-debug.exe!execute_sqlcom_select()[sql_parse.cc:4872]
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:2495]
mysqld-debug.exe!mysql_parse()[sql_parse.cc:5274]
mysqld-debug.exe!dispatch_command()[sql_parse.cc:1331]
mysqld-debug.exe!do_command()[sql_parse.cc:1027]
mysqld-debug.exe!handle_connection()[connection_handler_per_thread.cc:227]
mysqld-debug.exe!pfs_spawn_thread()[pfs.cc:1985]
mysqld-debug.exe!pthread_start()[my_winthread.c:60]
mysqld-debug.exe!_callthreadstartex()[threadex.c:314]
mysqld-debug.exe!_threadstartex()[threadex.c:297]
[19 Nov 2013 16:31] Paul Dubois
Noted in 5.7.4 changelog.

For debug builds, JSON-format EXPLAIN statements for queries that
involve semi-join materialization could cause a server exit.