MySQL Bugs: #52397: another crash with explain extended and group

Bug #52397	another crash with explain extended and group_concat
Submitted:	26 Mar 2010 16:38	Modified:	20 Jun 2010 22:26
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.0.90, 5.0.91, 5.1.45, 5.1.46	OS:	Any
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any
Tags:	explain extended, group_concat, regression

Description:
5.1.46 stack trace:

mysqld.exe!Item_field::print()[item.cc:5709]
mysqld.exe!Item_func_group_concat::print()[item_sum.cc:3423]
mysqld.exe!Item::print_item_w_name()[item.cc:454]
mysqld.exe!st_select_lex::print()[sql_select.cc:17001]
mysqld.exe!st_select_lex_unit::print()[sql_lex.cc:2015]
mysqld.exe!TABLE_LIST::print()[sql_select.cc:16883]
mysqld.exe!print_join()[sql_select.cc:16787]
mysqld.exe!st_select_lex::print()[sql_select.cc:17011]
mysqld.exe!st_select_lex_unit::print()[sql_lex.cc:2015]
mysqld.exe!execute_sqlcom_select()[sql_parse.cc:5037]
mysqld.exe!mysql_execute_command()[sql_parse.cc:2248]
mysqld.exe!mysql_parse()[sql_parse.cc:5971]
mysqld.exe!dispatch_command()[sql_parse.cc:1233]
mysqld.exe!do_command()[sql_parse.cc:874]
mysqld.exe!handle_one_connection()[sql_connect.cc:1127]
mysqld.exe!pthread_start()[my_winthread.c:85]
mysqld.exe!_callthreadstart()[thread.c:293]
mysqld.exe!_threadstart()[thread.c:277]

o) debug and release binary crashes
o) testcase for bug #49487 doesn't crash 5.1.45/5.1.46 but this does.
o) testcase for bug #49735 doesn't crash 5.1.46/5.1.46 but this does.
o) 5.6.99-m4 is not affected by this crash

How to repeat:
drop table if exists `t1`;
create table `t1`(`a` int)engine=myisam;
insert into `t1` values (0),(0);
explain extended select 1 from(
select group_concat(`t1`.`a` order by `t1`.`a` asc)
from `t1` `t2`,`t1` group by `t1`.`a`) as d;

Verified just as described with recent 5.1.46 from bzr (mysql-5.1 tree) on Mac OS X:

...
Version: '5.1.46-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
100326 18:44:14 - 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 = 337716 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x1194218
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                              0x0057e2d2 my_print_stacktrace + 44
1   mysqld                              0x00101360 handle_segfault + 836
2   libSystem.B.dylib                   0x940472bb _sigtramp + 43
3   ???                                 0xffffffff 0x0 + 4294967295
4   mysqld                              0x0003aeef _ZN22Item_func_group_concat5printEP6String15enum_query_type + 387
5   mysqld                              0x00025f36 _ZN4Item17print_item_w_nameEP6String15enum_query_type + 46
6   mysqld                              0x001827d9 _ZN13st_select_lex5printEP3THDP6String15enum_query_type + 661
7   mysqld                              0x00005434 _ZN18st_select_lex_unit5printEP6String15enum_query_type + 224
8   mysqld                              0x001820c4 _ZN10TABLE_LIST5printEP3THDP6String15enum_query_type + 346
9   mysqld                              0x00182411 _ZN10TABLE_LIST5printEP3THDP6String15enum_query_type + 1191
10  mysqld                              0x00182847 _ZN13st_select_lex5printEP3THDP6String15enum_query_type + 771
11  mysqld                              0x00005434 _ZN18st_select_lex_unit5printEP6String15enum_query_type + 224
12  mysqld                              0x00112d0b _Z15update_precheckP3THDP10TABLE_LIST + 811
13  mysqld                              0x00115496 _Z21mysql_execute_commandP3THD + 2936
14  mysqld                              0x0011ee4f _Z11mysql_parseP3THDPKcjPS2_ + 625
15  mysqld                              0x0011fc13 _Z16dispatch_command19enum_server_commandP3THDPcj + 3061
16  mysqld                              0x00120fb4 _Z10do_commandP3THD + 666
17  mysqld                              0x0010ba84 handle_one_connection + 372
18  libSystem.B.dylib                   0x9400c095 _pthread_start + 321
19  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 0x1075628 = explain extended select 1 from(
select group_concat(`t1`.`a` order by `t1`.`a` asc)
from `t1` `t2`,`t1` group by `t1`.`a`) as d
thd->thread_id=7
...

5.5.4-m3 (mysql-trunk) is also NOT affected.

Older 5.1.37 (before that bug fixes) is NOT affected:

77-52-28-202: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> drop table if exists `t1`;
Query OK, 0 rows affected (0.00 sec)

mysql> create table `t1`(`a` int)engine=myisam;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into `t1` values (0),(0);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> explain extended select 1 from(
    -> select group_concat(`t1`.`a` order by `t1`.`a` asc)
    -> from `t1` `t2`,`t1` group by `t1`.`a`) as d;
+----+-------------+------------+--------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+------------+--------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 |                                 | 
|  2 | DERIVED     | t2         | ALL    | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using temporary; Using filesort | 
|  2 | DERIVED     | t1         | ALL    | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using join buffer               | 
+----+-------------+------------+--------+---------------+------+---------+------+------+----------+---------------------------------+
3 rows in set, 1 warning (0.00 sec)

So this is a (recent) regression.

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/104709

3436 Ramil Kalimullin	2010-03-31
      Fix for bug#52397: another crash with explain extended and group_concat
      
      Problem: EXPLAIN EXTENDED was trying to resolve references to 
      freed temporary table fields for GROUP_CONCAT()'s ORDER BY arguments.
      
      Fix: use stored original GROUP_CONCAT()'s arguments in such a case.
     @ mysql-test/r/func_gconcat.result
        Fix for bug#52397: another crash with explain extended and group_concat
          - test result.
     @ mysql-test/t/func_gconcat.test
        Fix for bug#52397: another crash with explain extended and group_concat
          - test case.
     @ sql/item_sum.cc
        Fix for bug#52397: another crash with explain extended and group_concat
          - use "pargs", printing ORDER BY arguments in the 
        Item_func_group_concat::print() instead of "order" to avoid
        possible reference resolving to (freed) temporary table fields.

Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:joro@sun.com-20100401110445-3g1dto64q7l1o5cb) (merge vers: 5.1.46) (pib:16)

Noted in 5.1.46 changelog.

EXPLAIN EXTENDED crashed trying to resolve references to freed
temporary table columns for GROUP_CONCAT() ORDER BY arguments.

Setting report to Need Merge pending push to Celosia.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:alik@sun.com-20100422150750-vp0n37kp9ywq5ghf) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:alik@sun.com-20100422150658-fkhgnwwkyugtxrmu) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:alexey.kopytov@sun.com-20100403173551-52r97erqowlqwkon) (merge vers: 5.5.4-m3) (pib:16)

Noted in 5.5.5, 6.0.14 changelogs.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)