MySQL Bugs: #36024: group_concat distinct in subquery crash

Bug #36024	group_concat distinct in subquery crash
Submitted:	13 Apr 2008 13:29	Modified:	16 Apr 2008 16:22
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.0.58, 5.1.24	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	group_concat, subquery

Description:
I am not sure if this is the same as bug #36005 or bug #35945 or bug #35298
Hence I report new bug to be sure it's tested.

Stack trace:

mysqld-nt.exe!free_tree
mysqld-nt.exe!reset_tree
mysqld-nt.exe!Unique::reset
mysqld-nt.exe!JOIN::reinit
mysqld-nt.exe!subselect_single_select_engine::exec
mysqld-nt.exe!Item_subselect::exec
mysqld-nt.exe!Item_in_subselect::val_bool
mysqld-nt.exe!Item_in_optimizer::val_int
mysqld-nt.exe!Item::val_bool
mysqld-nt.exe!Item_func_not::val_int
mysqld-nt.exe!evaluate_join_record
mysqld-nt.exe!sub_select
mysqld-nt.exe!do_select
mysqld-nt.exe!JOIN::exec
mysqld-nt.exe!mysql_select
mysqld-nt.exe!handle_select
mysqld-nt.exe!mysql_execute_command
mysqld-nt.exe!mysql_parse
mysqld-nt.exe!dispatch_command
mysqld-nt.exe!do_command
mysqld-nt.exe!handle_one_connection
mysqld-nt.exe!pthread_start
mysqld-nt.exe!_threadstart
kernel32.dll!FlsSetValue

How to repeat:
drop table if exists `t1`;
create table `t1` (`a` int,key(`a`))engine=myisam;
insert into `t1` values (null),(8),(2);
drop table if exists `t2`;
create table `t2` (`b` int)engine=myisam;
insert into `t2` values (4),(0);

select 1 from t1 where t1.a not in
(select group_concat(distinct t1.a) a
from t1 where   
t1.a in (select b from t2) and not
t1.a >=(select t1.a from t1 limit 1)
group by  t1.a)  ;

Thank you for a bug report. Verified just as described. 

I've got the following stack trace with 5.0.58 on 32-bit Windows:

 	mysqld-nt.exe!free_tree(st_tree * tree=0x00000000, int free_flags=2)  Line 141	C
 	mysqld-nt.exe!reset_tree(st_tree * tree=0x000000c0)  Line 173 + 0xb bytes	C
 	mysqld-nt.exe!Unique::reset()  Line 355	C++
 	mysqld-nt.exe!JOIN::reinit()  Line 1543 + 0xb bytes	C++
 	mysqld-nt.exe!subselect_single_select_engine::exec()  Line 1807 + 0x8 bytes	C++
 	mysqld-nt.exe!Item_subselect::exec()  Line 214	C++
 	mysqld-nt.exe!Item_in_subselect::val_bool()  Line 775 + 0xc bytes	C++
 	mysqld-nt.exe!Item_in_optimizer::val_int()  Line 1530	C++
 	mysqld-nt.exe!Item::val_bool()  Line 175 + 0x7 bytes	C++
 	mysqld-nt.exe!Item_func_not::val_int()  Line 236	C++
 	mysqld-nt.exe!evaluate_join_record(JOIN * join=0x044fa678, st_join_table * join_tab=0x00000000, int error=0, char * report_error=0x01ac3858)  Line 10716 + 0xc bytes	C++
 	mysqld-nt.exe!sub_select(JOIN * join=0x044fa678, st_join_table * join_tab=0x04500a60, int end_of_records=0)  Line 10676 + 0x9 bytes	C++
 	mysqld-nt.exe!do_select(JOIN * join=0x00000000, List<Item> * fields=0x01ac4580, st_table * table=0x00000000, Procedure * procedure=0x00000000)  Line 10433 + 0x8 bytes	C++
 	mysqld-nt.exe!JOIN::exec()  Line 2119	C++
 	mysqld-nt.exe!mysql_select(THD * thd=0x01ac3590, Item * * * rref_pointer_array=0x01ac4610, TABLE_LIST * tables=0x044f20b0, unsigned int wild_num=0, List<Item> & fields={...}, Item * conds=0x044fa578, unsigned int og_num=0, st_order * order=0x00000000, st_order * group=0x00000000, Item * having=0x00000000, st_order * proc_param=0x00000000, unsigned __int64 select_options=2156153344, select_result * result=0x044fa660, st_select_lex_unit * unit=0x01ac4250, st_select_lex * select_lex=0x01ac44e0)  Line 2298	C++
 	mysqld-nt.exe!handle_select(THD * thd=0x01ac3590, st_lex * lex=0x01ac41f0, select_result * result=0x044fa660, unsigned long setup_tables_done_option=0)  Line 257 + 0x79 bytes	C++
 	mysqld-nt.exe!mysql_execute_command(THD * thd=0x01ac3590)  Line 2720 + 0xa bytes	C++
 	mysqld-nt.exe!mysql_parse(THD * thd=0x01ac3590, const char * inBuf=0x044f1f20, unsigned int length=177, const char * * found_semicolon=0x053afb64)  Line 6159	C++
 	mysqld-nt.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x01ac3590, char * packet=0x044e9ef1, unsigned int packet_length=178)  Line 1876	C++
 	mysqld-nt.exe!do_command(THD * thd=0x00000000)  Line 1580 + 0xe bytes	C++
 	mysqld-nt.exe!handle_one_connection(void * arg=0x01ac3590)  Line 1186 + 0x9 bytes	C++
 	mysqld-nt.exe!pthread_start(void * param=0x01ab8400)  Line 85 + 0x3 bytes	C
>	mysqld-nt.exe!_threadstart(void * ptd=0x01ac4aa8)  Line 196 + 0x6 bytes	C

Duplicate of bug #35298.