MySQL Bugs: #28811: server crash with subqueries and order by

Bug #28811	server crash with subqueries and order by
Submitted:	31 May 2007 16:14	Modified:	14 Jun 2007 19:49
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.0.42, 5.0.44-BK	OS:	Any
Assigned to:	Igor Babaev	CPU Architecture:	Any
Tags:	crash, subquery

Description:
A query that is formed similar to this, causes a repeatable crash of mysqld.
Query has derived tables, and single row subselect (due to limit 1).

select * from
(
	select col,
	
	(select col from table 
	where ...
	order by timestamp_col
	limit 1
	) as sub1,
		
	(select col from table 
	where ...
	order by timestamp_col
	limit 1
	) as sub2,
		
	count(*) as c
		
	from a,b
	where ...
	group by sub1, sub2) as sub3
) where sub3.col is not null

Shortened stack trace:

mysqld-debug.exe!Field_timestamp::sort_string
mysqld-debug.exe!make_sortkey
mysqld-debug.exe!find_all_keys
mysqld-debug.exe!filesort
mysqld-debug.exe!create_sort_index
mysqld-debug.exe!JOIN::exec
mysqld-debug.exe!subselect_single_select_engine::exec
mysqld-debug.exe!Item_subselect::exec
mysqld-debug.exe!Item_singlerow_subselect::val_str
mysqld-debug.exe!Item::save_in_field
mysqld-debug.exe!Item::save_org_in_field
mysqld-debug.exe!end_update
mysqld-debug.exe!evaluate_join_record
mysqld-debug.exe!sub_select
mysqld-debug.exe!evaluate_join_record
mysqld-debug.exe!sub_select
mysqld-debug.exe!do_select
mysqld-debug.exe!JOIN::exec
mysqld-debug.exe!mysql_select
mysqld-debug.exe!mysql_derived_filling
mysqld-debug.exe!mysql_handle_derived
mysqld-debug.exe!open_and_lock_tables
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!_threadstart

How to repeat:
will upload a testcase later

increasing sort_buffer_size works around the crash.

testcase crashes my 5.0.42

Attachment: final_testcase_reduced.txt (text/plain), 8.09 KiB.

Crashes latest 5.0.44-BK on Linux also:

openxs@suse:~/dbs/5.0> bin/resolve_stack_dump -s /tmp/mysqld5.sym 28811.stack
0x83b39dd __pthread_sighandler + 173
0x80bf01f _ZN15Field_timestamp11sort_stringEPcj + 15
0x81bb966 _Z8filesortP3THDP8st_tableP13st_sort_fieldjP10SQL_SELECTyPy + 4070
0x814dae9 _Z17create_sort_indexP3THDP4JOINP8st_orderyy + 345
0x815e313 _ZN4JOIN4execEv + 4259
0x80b4432 _ZN30subselect_single_select_engine4execEv + 338
0x80b282c _ZN14Item_subselect4execEv + 44
0x80b2ab3 _ZN24Item_singlerow_subselect7val_intEv + 19
0x805af00 _ZN4Item13save_in_fieldEP5Fieldb + 304
0x812c6fa _Z11fill_recordP3THDPP5FieldR4ListI4ItemEb + 90
0x8212019 _ZN12select_union9send_dataER4ListI4ItemE + 89
0x813e659 _Z8end_sendP4JOINP13st_join_tableb + 329
0x81448a8 _Z20evaluate_join_recordP4JOINP13st_join_tableiPc + 312
0x81449e6 _Z10sub_selectP4JOINP13st_join_tableb + 118
0x814bb01 _Z9do_selectP4JOINP4ListI4ItemEP8st_tableP9Procedure + 321
0x815e455 _ZN4JOIN4execEv + 4581
0x815abd8 _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orde
rSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_sel + 184
0x821244a _Z21mysql_derived_fillingP3THDP6st_lexP13st_table_list + 490
0x821220f _Z20mysql_handle_derivedP6st_lexPFbP3THDS0_P13st_table_listE + 79
0x8135d8a _Z20open_and_lock_tablesP3THDP13st_table_list + 378
0x81094b6 _Z21mysql_execute_commandP3THD + 15190
0x810e6ff _Z11mysql_parseP3THDPKcjPS2_ + 559
0x8110673 _Z16dispatch_command19enum_server_commandP3THDPcj + 2355
0x8111f53 handle_one_connection + 1955
0x83ae8b1 pthread_start_thread + 225
0x83e86aa __clone + 106

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

ChangeSet@1.2522, 2007-06-07 22:35:31-07:00, igor@olga.mysql.com +3 -0
  Fixed bug #28811: crash for a query containing a subquery with 
  ORDER BY and LIMIT 1. 
  The bug was introduced by the patch for bug 21727. The patch
  erroneously skipped initialization of the array of headers
  for sorted records for non-first evaluations of the subquery.
  
  To fix the problem a new parameter has been added to the
  function make_char_array that performs the initialization.
  Now this function is called for any invocation of the 
  filesort procedure. Yet it allocates the buffer for sorted
  records only if this parameter is NULL.

Pushed into 5.1.20-beta

Pushed into 5.0.44

Noted in 5.0.44, 5.1.20 changelogs.