Bug #52711 Segfault when doing EXPLAIN SELECT with union...order by (select... where...)
Submitted: 9 Apr 2010 9:08 Modified: 14 Apr 2011 13:16
Reporter: Bjørn Munch Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.91, 5.1.37, 5.1.47 OS:Any
Assigned to: Sergey Glukhov CPU Architecture:Any
Tags: explain union
Triage: Triaged: D1 (Critical)

[9 Apr 2010 9:08] Bjørn Munch
Description:
While doing experimental coding for WL #4357, test main.union cause a segfault on the very last statement when EXPLAIN was added to it. The call stack from the debugger includes:

---
  ---- called from signal handler with signal 11 (SIGSEGV) ------
  [8] 0x0(0x12a0fc0, 0x12afff0, 0x12afff0, 0x33, 0x0, 0x12a0f00), at 0x0 
  [9] Item_singlerow_subselect::store(this = 0x12a0f00, i = 1U, item = 0x12afff0), line 482 in "item_subselect.cc"
  [10] select_singlerow_subselect::send_data(this = 0x12a0fd0, items = CLASS), line 2232 in "sql_class.cc"
  [11] select_describe(join = 0x12ad1f8, need_tmp_table = false, need_order = false, distinct = false, message = (nil)), line 16627 in "sql_select.cc"
  [12] JOIN::exec(this = 0x12ad1f8), line 1844 in "sql_select.cc"
  [13] subselect_single_select_engine::exec(this = 0x12a0ff8), line 1968 in "item_subselect.cc"
  [14] Item_subselect::exec(this = 0x12a0f00), line 261 in "item_subselect.cc"
  [15] Item_singlerow_subselect::val_str(this = 0x12a0f00, str = 0x12a0f10), line 585 in "item_subselect.cc"
  [16] remove_const(join = 0x12aba48, first_order = 0x12a1040, cond = (nil), change_list = true, simple_order = 0x12acffa), line 7183 in "sql_select.cc"
  [17] JOIN::optimize(this = 0x12aba48), line 1127 in "sql_select.cc"
  [18] mysql_select(thd = 0x1285790, rref_pointer_array = 0x129fb48, tables = 0x12871c8, wild_num = 0, fields = CLASS, conds = (nil), og_num = 1U, order = 0x12a1040, group = (nil), having = (nil), proc_param = (nil), select_options = 268435460ULL, result = 0x12a10a0, unit = 0x1287178, select_lex = 0x129f978), line 2471 in "sql_select.cc"
  [19] st_select_lex_unit::exec(this = 0x1287178), line 596 in "sql_union.cc"
  [20] mysql_explain_union(thd = 0x1285790, unit = 0x1287178, result = 0x12a10a0), line 16676 in "sql_select.cc"
  [21] execute_sqlcom_select(thd = 0x1285790, all_tables = 0x129f378), line 5030 in "sql_parse.cc"
  [22] mysql_execute_command(thd = 0x1285790), line 2248 in "sql_parse.cc"
  [23] mysql_parse(thd = 0x1285790, inBuf = 0x129f0e0 "EXPLAIN SELECT * FROM t2 UNION SELECT * FROM t2\nORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE))", length = 123U, found_semicolon = 0xfffffd7ffec67b90), line 5971 in "sql_parse.cc"
----

The relevant code is:

void Item_singlerow_subselect::store(uint i, Item *item)
{
  row[i]->store(item);
  row[i]->cache_value();
}

Apparently, row[i] is a null pointer.

Discovered on next-mr, reproduced on 5.1 (5.1-mtr), on Solaris x86.

How to repeat:
This extract from union.test with added EXPLAIN triggere the failure (tested on Solaris x86):

-----
CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a));
INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2 (b INT);
INSERT INTO t2 VALUES (1),(2);

EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t1
  ORDER BY (SELECT a FROM t2 WHERE b = 12);

EXPLAIN SELECT * FROM t2 UNION SELECT * FROM t2
  ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE));

DROP TABLE t1,t2;

exit;
-----

The first EXPLAIN works, the second crashes the server. Note that both work *without* EXPLAIN.
[9 Apr 2010 9:14] Valeriy Kravchuk
Verified just as described with recent 5.1.47 from bzr on Mac OS X:

Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0xb026af64 thread_stack 0x30000
0   mysqld                              0x0057ff2e my_print_stacktrace + 44
1   mysqld                              0x0010197c handle_segfault + 836
2   libSystem.B.dylib                   0x940472bb _sigtramp + 43
3   ???                                 0xffffffff 0x0 + 4294967295
4   mysqld                              0x000e4b3a _ZN26select_singlerow_subselect9send_dataER4ListI4ItemE + 372
5   mysqld                              0x001aa9d9 _Z19mysql_explain_unionP3THDP18st_select_lex_unitP13select_result + 11085
6   mysqld                              0x001ab749 _ZN4JOIN4execEv + 2459
7   mysqld                              0x000a8b16 _ZN30subselect_single_select_engine4execEv + 1358
8   mysqld                              0x000add73 _ZN14Item_subselect4execEv + 129
9   mysqld                              0x000a617e _ZN24Item_singlerow_subselect7val_strEP6String + 26
10  mysqld                              0x00194697 _Z16create_tmp_tableP3THDP15TMP_TABLE_PARAMR4ListI4ItemEP8st_orderbbyyPc + 10999
11  mysqld                              0x001a480e _ZN4JOIN8optimizeEv + 5678
12  mysqld                              0x001a7c8d _Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex + 741
13  mysqld                              0x002da371 _ZN18st_select_lex_unit4execEv + 2771
14  mysqld                              0x001a8112 _Z19mysql_explain_unionP3THDP18st_select_lex_unitP13select_result + 646
15  mysqld                              0x001132b2 _Z15update_precheckP3THDP10TABLE_LIST + 690
16  mysqld                              0x00115ab6 _Z21mysql_execute_commandP3THD + 2936
17  mysqld                              0x0011f477 _Z11mysql_parseP3THDPKcjPS2_ + 625
18  mysqld                              0x0012023b _Z16dispatch_command19enum_server_commandP3THDPcj + 3061
19  mysqld                              0x001215dc _Z10do_commandP3THD + 666
20  mysqld                              0x0010c0a4 handle_one_connection + 372
21  libSystem.B.dylib                   0x9400c095 _pthread_start + 321
22  libSystem.B.dylib                   0x9400bf52 thread_start + 34
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x1075828 = EXPLAIN SELECT * FROM t2 UNION SELECT * FROM t2
  ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE))
thd->thread_id=2
[9 Apr 2010 18:00] Valeriy Kravchuk
Both 5.0.91 and 5.1.37 crashed even with the first EXPLAIN:

77-52-4-109:mysql-5.1.37-osx10.5-x86_64 openxs$ sudo tail -100 data/77-52-46-29.dialup.umc.net.ua.err
100409 20:56:04 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql-5.1.37-osx10.5-x86_64/data
100409 20:56:05 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql-5.1.37-osx10.5-x86_64/data/ is case insensitive
100409 20:56:05 [Note] Plugin 'FEDERATED' is disabled.
100409 20:56:05  InnoDB: Started; log sequence number 0 57901
100409 20:56:05 [Note] Event Scheduler: Loaded 0 events
100409 20:56:05 [Note] /usr/local/mysql-5.1.37-osx10.5-x86_64/bin/mysqld: ready for connections.
Version: '5.1.37'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
100409 20:56:28 - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8384512
read_buffer_size=131072
max_used_connections=1
max_threads=151
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 338286 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x101035600
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x102f52ed0 thread_stack 0x40000
0   mysqld                              0x00000000002c7f9a my_print_stacktrace + 57
1   mysqld                              0x00000000000918b2 handle_segfault + 548
2   libSystem.B.dylib                   0x00000000805ed3aa _sigtramp + 26
3   ???                                 0x0000000000000000 0x0 + 0
4   mysqld                              0x00000000000e30fd _ZN4JOIN7prepareEPPP4ItemP10TABLE_LISTjS1_jP8st_orderS7_S1_S7_P13st_select_lexP18st_select_lex_unit + 907
5   mysqld                              0x00000000000ed6e8 _Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex + 392
6   mysqld                              0x00000000000eda06 _Z19mysql_explain_unionP3THDP18st_select_lex_unitP13select_result + 496
7   mysqld                              0x00000000000ef201 _Z19mysql_explain_unionP3THDP18st_select_lex_unitP13select_result + 6635
8   mysqld                              0x00000000000ebcdc _ZN4JOIN4execEv + 1346
9   mysqld                              0x000000000017bb21 _ZN18st_select_lex_unit4execEv + 531
10  mysqld                              0x00000000000ed945 _Z19mysql_explain_unionP3THDP18st_select_lex_unitP13select_result + 303
11  mysqld                              0x00000000000996ee _ZN13st_select_lex18convert_right_joinEv + 374
12  mysqld                              0x000000000009a1f8 _Z21mysql_execute_commandP3THD + 1888
13  mysqld                              0x000000000009f5bf _Z11mysql_parseP3THDPKcjPS2_ + 295
14  mysqld                              0x00000000000a0322 _Z16dispatch_command19enum_server_commandP3THDPcj + 3270
15  mysqld                              0x00000000000a0da6 _Z10do_commandP3THD + 266
16  mysqld                              0x000000000009554b handle_one_connection + 2397
17  libSystem.B.dylib                   0x00000000805b5dcb _pthread_start + 316
18  libSystem.B.dylib                   0x00000000805b5c8d thread_start + 13
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x101081a10 = EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t1
  ORDER BY (SELECT a FROM t2 WHERE b = 12)
[9 Apr 2010 18:02] Valeriy Kravchuk
Also when first EXPLAIN crashes FULLTEXT index does NOT matter:

77-52-4-109:mysql-5.1.37-osx10.5-x86_64 openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.37 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter table t1 drop key a;
Query OK, 2 rows affected (0.40 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t1   ORDER BY (SELECT a FROM t2 WHERE b = 12);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 100409 21:00:50 mysqld_safe mysqld restarted
[13 Apr 2010 17:42] Sinisa Milivojevic
The error is in row[1], for this test case, but row[1] is not NULL, it is just not initialized.
[15 Apr 2010 14:04] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/105750

3454 Georgi Kodinov	2010-04-15
      Bug #52711: Segfault when doing EXPLAIN SELECT with
      union...order by (select... where...)
      
      The problem is mysql is trying to materialize and 
      cache the scalar sub-queries at JOIN::optimize
      even for EXPLAIN where the number of columns is 
      totally different from what's expected.
      Fixed by not executing the scalar subqueries 
      for EXPLAIN.
[19 Jul 2010 14:34] Bugs System
Pushed into 5.1.49 (revid:build@mysql.com-20100719143034-omcma40sblwmay3x) (version source revid:georgi.kodinov@oracle.com-20100625132022-tjvtf6x0fl99w66d) (merge vers: 5.1.48) (pib:16)
[21 Jul 2010 14:52] Paul Dubois
Noted in 5.1.49 changelog.

Using EXPLAIN with queries of the form SELECT ... UNION ... ORDER BY
(SELECT ... WHERE ...) could cause a server crash.

Setting report to Need Merge pending further pushes.
[23 Jul 2010 12:24] Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[23 Jul 2010 12:31] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)
[26 Jul 2010 18:32] Paul Dubois
Noted in 5.5.6 changelog.
[14 Oct 2010 8:30] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:21)
[14 Oct 2010 8:45] Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:21)
[14 Oct 2010 9:00] Bugs System
Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:21)
[3 Nov 2010 15:41] Paul Dubois
CVE-2010-3682
[8 Nov 2010 15:03] Paul Dubois
Noted in 5.0.92 changelog.
[15 Dec 2010 5:50] Bugs System
Pushed into mysql-5.1 5.1.55 (revid:sunanda.menon@oracle.com-20101215054055-vgwki317xg1wphhh) (version source revid:sunanda.menon@oracle.com-20101215054055-vgwki317xg1wphhh) (merge vers: 5.1.55) (pib:23)
[16 Dec 2010 21:46] Bugs System
Pushed into mysql-trunk 5.6.1 (revid:alexander.nozdrin@oracle.com-20101216181820-7afubgk2fmuv9qsb) (version source revid:alexander.nozdrin@oracle.com-20101216173826-ze3y5h450sksotrh) (merge vers: 5.6.1) (pib:23)
[16 Dec 2010 22:27] Bugs System
Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)
[8 Feb 2011 13:47] Bugs System
Pushed into mysql-5.0 5.0.93 (revid:jonathan.perkin@oracle.com-20110208134327-62swunlfs2s88jo2) (version source revid:jonathan.perkin@oracle.com-20110208134327-62swunlfs2s88jo2) (merge vers: 5.0.93) (pib:24)