Bug #43612 crash with explain extended, union, order by
Submitted: 12 Mar 2009 20:42 Modified: 26 Jun 2009 2:08
Reporter: Gary Pendergast Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.82, 5.1.32,5.1.35 OS:Any
Assigned to: Sergey Glukhov
Tags: crash, SELECT
Triage: Triaged: D1 (Critical)

[12 Mar 2009 20:42] Gary Pendergast
Description:
MySQL crashes when the following queries are run. Stack trace:

/users/gpendergast/mysql_sandbox_2.0.12/5.1.32/bin/mysqld(my_print_stacktrace+0x21)[0x84b05f1]
/users/gpendergast/mysql_sandbox_2.0.12/5.1.32/bin/mysqld(handle_segfault+0x381)[0x81e8ec1]
[0x2ce420]
/users/gpendergast/mysql_sandbox_2.0.12/5.1.32/bin/mysqld(_ZN13st_select_lex11print_orderEP6StringP8st_order15enum_query_type+0x9b)[0x812fa0b]
/users/gpendergast/mysql_sandbox_2.0.12/5.1.32/bin/mysqld(_ZN13st_select_lex5printEP3THDP6String15enum_query_type+0x24c)[0x825023c]
/users/gpendergast/mysql_sandbox_2.0.12/5.1.32/bin/mysqld(_ZN18st_select_lex_unit5printEP6String15enum_query_type+0x50)[0x812fab0]
/users/gpendergast/mysql_sandbox_2.0.12/5.1.32/bin/mysqld[0x81f5f8e]
/users/gpendergast/mysql_sandbox_2.0.12/5.1.32/bin/mysqld(_Z21mysql_execute_commandP3THD+0x32ac)[0x81fbafc]
/users/gpendergast/mysql_sandbox_2.0.12/5.1.32/bin/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x340)[0x81ffec0]
/users/gpendergast/mysql_sandbox_2.0.12/5.1.32/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x11e0)[0x82010b0]
/users/gpendergast/mysql_sandbox_2.0.12/5.1.32/bin/mysqld(_Z10do_commandP3THD+0xe0)[0x8201930]
/users/gpendergast/mysql_sandbox_2.0.12/5.1.32/bin/mysqld(handle_one_connection+0x233)[0x81f25a3]
/lib/libpthread.so.0[0x45fbd4]
/lib/libc.so.6(__clone+0x5e)[0x3b74fe]

How to repeat:
CREATE TABLE `t1` (
`StartDateTime` datetime DEFAULT NULL,
`EndDateTime` datetime DEFAULT NULL,
`SeqNo` varchar(100) DEFAULT NULL,
`Counter` int(4) DEFAULT NULL,
`Duration` varchar(100) DEFAULT NULL,
`Duration2` varchar(100) DEFAULT NULL,
`ID` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into `t1`(`StartDateTime`,`EndDateTime`,`SeqNo`,`Counter`,`Duration`,`Duration2`,`ID`) values ('2009-03-03 12:23:13',NULL,'0303091223130029',1,'287','132','233089582313050'),('2009-03-03 12:23:13',NULL,'0303091223130029',2,'419','380','233089582313050'),('2009-03-03 12:23:13',NULL,'0303091223130029',3,'36','11','233089582313050'),('2009-03-03 12:23:13',NULL,'0303091223130029',4,'278','202','233089582313050'),('2009-03-03 12:23:13','2009-03-03 12:39:22','0303091223130029',5,'1020','202','233089582313050');

-- I had to run the following set of queries twice for the crash to occur. Seems to vary.

SET PROFILING = 1;
SET profiling_history_size = 0;
SET profiling_history_size = 15;
SHOW STATUS;
SELECT
StartDateTime,
Concat('Connection:', counter) AS cnt,
Duration/60 As Duration,
round(Duration2/60.0,2) As Duration2,
counter
FROM t1 a where ID= '233089582313050' and enddatetime is null
Union
SELECT
StartDateTime,
Concat('Final:', counter) AS cnt,
concat('<font color=red>Total:',round(Duration/60.0,2),'</font>') As Duration,
concat('<font color=red>Total:',round(Duration2/60.0,2),'</font>') As Duration2,
counter
FROM t1 a where ID= '233089582313050' and enddatetime is not null
order by StartDateTime desc,counter desc ;
SHOW STATUS;
SHOW PROFILES;
select state, round(sum(duration),5) as `duration (summed) in sec` from information_schema.profiling where query_id = 16 group by state order by `duration (summed) in sec` desc;
SET PROFILING = 0;
EXPLAIN EXTENDED SELECT
StartDateTime,
Concat('Connection:', counter) AS cnt,
Duration/60 As Duration,
round(Duration2/60.0,2) As Duration2,
counter
FROM t1 a where ID= '233089582313050' and enddatetime is null
Union
SELECT
StartDateTime,
Concat('Final:', counter) AS cnt,
concat('<font color=red>Total:',round(Duration/60.0,2),'</font>') As Duration,
concat('<font color=red>Total:',round(Duration2/60.0,2),'</font>') As Duration2,
counter
FROM t1 a where ID= '233089582313050' and enddatetime is not null
order by StartDateTime desc,counter desc ;
SHOW WARNINGS;

Suggested fix:
Don't crash.
[6 May 2009 11:38] Shane Bester
5.1.35 valgrind output clearly shows the problem each time

Attachment: bug43612_5.1.35_valgrind_output.sql (application/unknown, text), 4.13 KiB.

[6 May 2009 12:04] Shane Bester
simpified testcase to cause the valgrind errors (and possible crash)

drop table if exists `t1`;
create table `t1`(`a` int);
explain extended 
select `a` from `t1`
union
select `a` from `t1`
order by `a`;
[12 May 2009 11:36] 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/73810

2735 Sergey Glukhov	2009-05-12
      Bug#43612 crash with explain extended, union, order by
      In UNION if we use last SELECT without braces and this
      SELECT have ORDER BY clause, such clause belongs to
      global UNION. At this moment it is parsed like last SELECT
      part and used further as 'unit->global_parameters->order_list' value.
      So during DESCRIBE EXTENDED we call select_lex->print_order() for
      last SELECT where order fields refer to tmp table 
      which already freed. It leads to crash.
      The fix is move such ORDER BY clause to
      global_parameters->order_list and cleanup
      last SELECT order_list.
     @ mysql-test/r/union.result
        test result
     @ mysql-test/t/union.test
        test case
     @ sql/sql_yacc.yy
        In UNION if we use last SELECT without braces and this
        SELECT have ORDER BY clause, such clause belongs to
        global UNION. At this moment it is parsed like last SELECT
        part and used further as 'unit->global_parameters->order_list' value.
        So during DESCRIBE EXTENDED we call select_lex->print_order() for
        last SELECT where order fields refer to tmp table 
        which already freed. It leads to crash.
        The fix is move such ORDER BY clause to
        global_parameters->order_list and cleanup
        last SELECT order_list.
[15 May 2009 4:19] Shane Bester
Omer, this bug hits release and debug binaries.  the valgrind output tell us this.  however, some os's are more forgiving than others when it comes to this type of memory mismanagement.  64-bit windows binaries will always crash for example.  some glibc linux versions will get a signal 6, and some will not even crash the first time.
[15 May 2009 7:28] 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/74131

2735 Sergey Glukhov	2009-05-15
      Bug#43612 crash with explain extended, union, order by
      In UNION if we use last SELECT without braces and this
      SELECT have ORDER BY clause, such clause belongs to
      global UNION. It is parsed like last SELECT
      part and used further as 'unit->global_parameters->order_list' value.
      During DESCRIBE EXTENDED we call select_lex->print_order() for
      last SELECT where order fields refer to tmp table 
      which already freed. It leads to crash.
      The fix is clean up global_parameters->order_list
      instead of fake_select_lex->order_list.
     @ mysql-test/r/union.result
        test result
     @ mysql-test/t/union.test
        test case
     @ sql/sql_union.cc
        In UNION if we use last SELECT without braces and this
        SELECT have ORDER BY clause, such clause belongs to
        global UNION. It is parsed like last SELECT
        part and used further as 'unit->global_parameters->order_list' value.
        During DESCRIBE EXTENDED we call select_lex->print_order() for
        last SELECT where order fields refer to tmp table 
        which already freed. It leads to crash.
        The fix is clean up global_parameters->order_list
        instead of fake_select_lex->order_list.
[15 May 2009 8: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/74139

2741 Sergey Glukhov	2009-05-15
      Bug#43612 crash with explain extended, union, order by
      In UNION if we use last SELECT without braces and this
      SELECT have ORDER BY clause, such clause belongs to
      global UNION. It is parsed like last SELECT
      part and used further as 'unit->global_parameters->order_list' value.
      During DESCRIBE EXTENDED we call select_lex->print_order() for
      last SELECT where order fields refer to tmp table 
      which already freed. It leads to crash.
      The fix is clean up global_parameters->order_list
      instead of fake_select_lex->order_list.
     @ mysql-test/r/union.result
        test result
     @ mysql-test/t/union.test
        test case
     @ sql/sql_union.cc
        In UNION if we use last SELECT without braces and this
        SELECT have ORDER BY clause, such clause belongs to
        global UNION. It is parsed like last SELECT
        part and used further as 'unit->global_parameters->order_list' value.
        During DESCRIBE EXTENDED we call select_lex->print_order() for
        last SELECT where order fields refer to tmp table 
        which already freed. It leads to crash.
        The fix is clean up global_parameters->order_list
        instead of fake_select_lex->order_list.
[28 May 2009 7:42] Bugs System
Pushed into 5.0.83 (revid:joro@sun.com-20090528073529-q9b8s60vlpu28fny) (version source revid:sergey.glukhov@sun.com-20090515070334-t2bmuovmuw4kjzfe) (merge vers: 5.0.82) (pib:6)
[28 May 2009 8:17] Bugs System
Pushed into 5.1.36 (revid:joro@sun.com-20090528073639-yohsb4q1jzg7ycws) (version source revid:jimw@mysql.com-20090515174051-ndjvfd1e9hc9k9c3) (merge vers: 5.1.36) (pib:6)
[1 Jun 2009 19:00] Paul Dubois
Noted in 5.0.83, 5.1.36 changelogs.

EXPLAIN EXTENDED could crash for UNION queries in which the last
SELECT was not parenthesized and included an ORDER BY clause.

Setting report to NDI pending push into 6.0.x.
[17 Jun 2009 19:24] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:kristofer.pettersson@sun.com-20090515092117-d8kbrf2wwohyf7l5) (merge vers: 6.0.12-alpha) (pib:11)
[26 Jun 2009 2:08] Paul Dubois
Noted in 5.4.4 changelog.
[12 Aug 2009 22:40] Paul Dubois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 2009 1:56] Paul Dubois
Ignore previous comment about 5.4.2.
[26 Aug 2009 13:46] Bugs System
Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[26 Aug 2009 13:46] Bugs System
Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[26 Aug 2009 13:48] Bugs System
Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[27 Aug 2009 16:32] Bugs System
Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[8 Oct 2009 2:48] Paul Dubois
The 5.4 fix has been pushed to 5.4.2.