Bug #34773 query with explain extended and derived table / other table crashes server
Submitted: 23 Feb 2008 9:29 Modified: 9 Jan 2009 1:30
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.56-debug,5.1.23-debug OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: avg distinct
Triage: Triaged: D1 (Critical) / R2 (Low) / E1 (None/Negligible)

[23 Feb 2008 9:29] Shane Bester
Description:
see how to repeat section.

mysqld-debug.exe!Item_field::print
mysqld-debug.exe!Item_sum::print
mysqld-debug.exe!Item::print_item_w_name
mysqld-debug.exe!st_select_lex::print
mysqld-debug.exe!st_select_lex_unit::print
mysqld-debug.exe!TABLE_LIST::print
mysqld-debug.exe!print_join
mysqld-debug.exe!st_select_lex::print
mysqld-debug.exe!st_select_lex_unit::print
mysqld-debug.exe!mysql_execute_command
mysqld-debug.exe!mysql_parse
mysqld-debug.exe!dispatch_command
mysqld-debug.exe!do_command
mysqld-debug.exe!handle_one_connection
mysqld-debug.exe!pthread_start
mysqld-debug.exe!_callthreadstart
mysqld-debug.exe!_threadstart

How to repeat:
run debug build of mysqld and make it listen on 127.0.0.1

create database if not exists `realdb`;
create database if not exists `test`;
drop table if exists `test`.`t0`;
drop table if exists `realdb`.`t0`;
create table `realdb`.`t0`(`a` int)engine=myisam;
create table `test`.`t0`(`a` int)engine=federated connection='mysql://root@127.0.0.1:3306/realdb/t0';

explain extended select 1 from (select `t1`.`a`,avg(distinct `t1`.`a`) from `test`.`t0`, `test`.`t0` as `t1` group by 1 limit 1) as `s1`;
[24 Feb 2008 0:16] Miguel Solorzano
Thank you for the bug report.

>	mysqld-debug.exe!Item_field::print(String * str=0x038be0bc)  Line 5179 + 0x12	C++
 	mysqld-debug.exe!Item_sum::print(String * str=0x038be0bc)  Line 369 + 0x28	C++
 	mysqld-debug.exe!Item::print_item_w_name(String * str=0x038be0bc)  Line 447 + 0x14	C++
 	mysqld-debug.exe!st_select_lex::print(THD * thd=0x0171bbc8, String * str=0x038be0bc)  Line 16457	C++
 	mysqld-debug.exe!st_select_lex_unit::print(String * str=0x038be0bc)  Line 1966	C++
 	mysqld-debug.exe!TABLE_LIST::print(THD * thd=0x0171bbc8, String * str=0x038be0bc)  Line 16349	C++
 	mysqld-debug.exe!print_join(THD * thd=0x0171bbc8, String * str=0x038be0bc, List<TABLE_LIST> * tables=0x0171cf38)  Line 16253	C++
 	mysqld-debug.exe!st_select_lex::print(THD * thd=0x0171bbc8, String * str=0x038be0bc)  Line 16467 + 0x17	C++
 	mysqld-debug.exe!st_select_lex_unit::print(String * str=0x038be0bc)  Line 1966	C++
 	mysqld-debug.exe!execute_sqlcom_select(THD * thd=0x0171bbc8, TABLE_LIST * all_tables=0x01749160)  Line 4734	C++
 	mysqld-debug.exe!mysql_execute_command(THD * thd=0x0171bbc8)  Line 2056 + 0xd	C++
 	mysqld-debug.exe!mysql_parse(THD * thd=0x0171bbc8, const char * inBuf=0x01747d60, unsigned int length=136, const char * * found_semicolon=0x038bfd00)  Line 5622 + 0x9	C++
 	mysqld-debug.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x0171bbc8, char * packet=0x0173fcb1, unsigned int packet_length=136)  Line 1121 + 0x1e	C++
 	mysqld-debug.exe!do_command(THD * thd=0x0171bbc8)  Line 781 + 0x1b	C++
 	mysqld-debug.exe!handle_one_connection(void * arg=0x0171bbc8)  Line 1120 + 0x9	C++
 	mysqld-debug.exe!pthread_start(void * param=0x011ef5a8)  Line 85 + 0x9	C
 	mysqld-debug.exe!_threadstart(void * ptd=0x017383d0)  Line 196 + 0xd	C
 	kernel32.dll!7c80b683()
[24 Feb 2008 12:04] Shane Bester
looks like similar bug to bug #28728
[6 Jun 2008 8:49] Ramil Kalimullin
It's a flaw in the optimizator.
Change the table type to innodb leads to the same crash.
[12 Jun 2008 13:23] 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/47781

2662 Georgi Kodinov	2008-06-12
      Bug#34773: query with explain extended and derived table / other table 
        crashes server
      
      When creating temporary table that contains aggregate functions a 
      non-reversible source transformation was performed to redirect aggregate
      function arguments towards temporary table columns.
      This caused EXPLAIN EXTENDED to fail because it was trying to resolve
      references to the (freed) temporary table.
      Fixed by preserving the original aggregate function arguments and
      using them (instead of the transformed ones) for EXPLAIN EXTENDED.
[2 Oct 2008 13:09] 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/55073

2662 Georgi Kodinov	2008-10-02
      Bug#34773: query with explain extended and derived table / other table 
              crashes server
            
      When creating temporary table that contains aggregate functions a 
      non-reversible source transformation was performed to redirect aggregate
      function arguments towards temporary table columns.
      This caused EXPLAIN EXTENDED to fail because it was trying to resolve
      references to the (freed) temporary table.
      Fixed by preserving the original aggregate function arguments and
      using them (instead of the transformed ones) for EXPLAIN EXTENDED.
[6 Oct 2008 14:18] 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/55457

2662 Georgi Kodinov	2008-10-06
      Bug#34773: query with explain extended and derived table / other table 
      crashes server
      
      When creating temporary table that contains aggregate functions a 
      non-reversible source transformation was performed to redirect aggregate
      function arguments towards temporary table columns.
      This caused EXPLAIN EXTENDED to fail because it was trying to resolve
      references to the (freed) temporary table.
      Fixed by preserving the original aggregate function arguments and
      using them (instead of the transformed ones) for EXPLAIN EXTENDED.
[10 Nov 2008 10:51] Bugs System
Pushed into 6.0.8-alpha  (revid:kgeorge@mysql.com-20081006141725-dlol5jjumw9lb5ar) (version source revid:kgeorge@mysql.com-20081010131729-7l9slxbb4u8kyrva) (pib:5)
[10 Nov 2008 11:35] Bugs System
Pushed into 5.1.26-rc  (revid:kgeorge@mysql.com-20081006141725-dlol5jjumw9lb5ar) (version source revid:kgeorge@mysql.com-20081006141725-dlol5jjumw9lb5ar) (pib:5)
[11 Nov 2008 1:42] Paul Dubois
Noted in 5.1.31, 6.0.8 changelogs.

EXPLAIN EXTENDED evaluation of aggregate functions that required a
temporary table caused a server crash.
[11 Nov 2008 16:25] Paul Dubois
6.0.9 changelog, not 6.0.8.
[26 Nov 2008 15:05] Mark Leith
Hi Georgi,

It doesn't appear that this was pushed to 5.0 - is there some reason for that? Can we have this pushed to a 5.0 release as well please?

Many thanks,

Mark
[9 Dec 2008 12:38] Georgi Kodinov
Moving back to Need Doc Info until pushed to 5.0
[9 Dec 2008 19:40] 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/61127

2735 Georgi Kodinov	2008-12-09
      backported the fix for bug #34773 to 5.0
[6 Jan 2009 13:56] Bugs System
Pushed into 5.0.76 (revid:joro@sun.com-20090105160414-8q9j4bi1klkfwiup) (version source revid:azundris@mysql.com-20081230114734-nmsc37ak330zlygn) (merge vers: 5.0.76) (pib:6)
[9 Jan 2009 1:30] Paul Dubois
Noted in 5.0.76 changelog.
[15 Jan 2009 6:32] Bugs System
Pushed into 5.1.31 (revid:joro@sun.com-20090115053147-tx1oapthnzgvs1ro) (version source revid:azundris@mysql.com-20081230114838-cn52tu180wcrvh0h) (merge vers: 5.1.31) (pib:6)
[19 Jan 2009 11:29] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090115073240-1wanl85vlvw2she1) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:07] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:13] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)
[20 Jan 2009 18:59] Bugs System
Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:azundris@mysql.com-20081230114916-c290n83z25wkt6e4) (merge vers: 6.0.9-alpha) (pib:6)
[9 Jun 2009 18:45] Paul Dubois
Noted in 5.0.74sp1 changelog.