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

[22 Oct 2007 9:01] Shane Bester
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.
[22 Oct 2007 9:10] Shane Bester
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;
[22 Oct 2007 12:10] 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/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.
[23 Oct 2007 12:31] Alexey Kopytov
Queued to 5.0-opt, 5.1-opt and 5.2-opt.
[29 Oct 2007 8:42] Bugs System
Pushed into 5.0.52
[29 Oct 2007 8:45] Bugs System
Pushed into 5.1.23-beta
[29 Oct 2007 8:49] Bugs System
Pushed into 6.0.4-alpha
[30 Oct 2007 0:37] Paul Dubois
Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.