MySQL Bugs: #31742: delete from ... order by function call that causes an error, asserts server

Bug #31742	delete from ... order by function call that causes an error, asserts server
Submitted:	22 Oct 2007 9:01	Modified:	30 Oct 2007 0:37
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S6 (Debug Builds)
Version:	5.0.50-debug,5.1.23-debug	OS:	Any
Assigned to:	Alexey Kopytov	CPU Architecture:	Any
Tags:	assertion

Description:
When using a stored function in the order by clause of a delete statement, and the function call failed for some reason, debug assertion is triggered in server.

Two example reasons of failing that I saw are
1) function had an error, such as trying to select from some nonexistent table
2) wrong number of parameters passed to the function.

Error log:

5.1.22 (5.1.23BK same on linux):

071022 10:50:21 [ERROR] mysqld-debug: Sort aborted
Assertion failed: inited == NONE, file .\handler.cc, line 3647

mysqld-debug.exe!_NMSG_WRITE
mysqld-debug.exe!abort
mysqld-debug.exe!_assert
mysqld-debug.exe!handler::ha_reset
mysqld-debug.exe!mark_used_tables_as_free_for_reuse
mysqld-debug.exe!close_thread_tables
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
kernel32.dll!FlsSetValue

5.0.50 crashed similarly:

071022 10:54:53 [ERROR] mysqld-debug: Sort aborted
Assertion failed: !table->file || table->file->inited == handler::NONE, file .\sql_base.cpp, line 627

mysqld-debug.exe!_NMSG_WRITE
mysqld-debug.exe!abort
mysqld-debug.exe!_wassert
mysqld-debug.exe!close_thread_table
mysqld-debug.exe!close_thread_tables
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
kernel32.dll!FlsSetValue

How to repeat:
run debug build of server:

delimiter //
drop function if exists `f1` //
create function `f1`() returns int begin return 1; end //
delimiter ;
drop table if exists `t1`;
create table `t1` (a int)engine=myisam;
insert into `t1` values (0);
delete from `t1` order by (`f1`(10)) limit 1;

Suggested fix:
emit a warning or error instead of crashing the server.  For example this would be ok:
Incorrect number of arguments for FUNCTION test.f1; expected 0, got 1

note: release build didn't crash.

Another testcase:

delimiter //
drop function if exists `f1` //
create function `f1`() returns int
begin 
       select 1 from `t_nonexisting table` into @a;  return @a;
end //
delimiter ;
drop table if exists `t1`;
create table `t1` (a int)engine=heap;
insert into `t1` values (0);
delete from `t1` order by (`f1`()) limit 1;

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

ChangeSet@1.2547, 2007-10-22 16:10:08+04:00, kaa@polly.(none) +3 -0
  Fix for bug #31742: delete from ... order by function call that causes
  an error, asserts server
  
  In case of a fatal error during filesort in find_all_keys() the error
  was returned without the necessary handler uninitialization.
  Fixed by changing the code so that handler uninitialization is performed
  before returning the error.

Queued to 5.0-opt, 5.1-opt and 5.2-opt.

Pushed into 5.0.52

Pushed into 5.1.23-beta

Pushed into 6.0.4-alpha

Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.