Bug #24562 ALTER TABLE ... ORDER BY ... with complex expression asserts
Submitted: 23 Nov 2006 22:48 Modified: 8 Mar 2007 3:14
Reporter: Jeremy Cole (Basic Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.27/5.0BK/5.1BK OS:Linux (Linux)
Assigned to: Marc ALFF CPU Architecture:Any
Tags: rt_q1_2007

[23 Nov 2006 22:48] Jeremy Cole
Description:
ALTER TABLE ... ORDER BY ... asserts as follows:

sql_base.cc:561: bool close_thread_table(THD*, TABLE**): Assertion `table->file->inited == handler::NONE' failed.

How to repeat:
# Create a very simple stored function
DROP FUNCTION IF EXISTS n;
CREATE FUNCTION n (i INT) RETURNS INT
RETURN i+1;

# A table with a couple of rows in it
DROP TABLE IF EXISTS t;
CREATE TABLE t (i INT);
INSERT INTO t (i) VALUES (5), (6);

# Force MySQL to open mysql.proc and init the stored function
SELECT n(1);

# Kaboom
ALTER TABLE t ORDER BY n(i);

Suggested fix:
Not entirely sure.  Should stored functions be usable in ALTER TABLE ... ORDER BY ... ?
[23 Nov 2006 23:33] MySQL Verification Team
Thank you for the bug report.

mysql> # Kaboom
mysql> ALTER TABLE t ORDER BY n(i);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[11 Jan 2007 2:59] Marc ALFF
See WL#3681.
[17 Jan 2007 1:50] 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/18221

ChangeSet@1.2386, 2007-01-16 18:49:43-07:00, malff@weblab.(none) +3 -0
  Bug#24562 (ALTER TABLE ... ORDER BY ... with a stored function in expression
    asserts)
  
  Before this fix, the ALTER TABLE statement implemented an ORDER BY option
  with the following characteristics :
  
  1) The order by clause accepts a list of criteria, with optional ASC or
  DESC keywords
  
  2) Each criteria can be a general expression, involving operators,
  native functions, stored functions, user defined functions, subselects ...
  
  With this fix :
  
  1) has been left unchanged, since it's a de-facto existing feature,
  that was already present in the code base and partially covered in the test
  suite. Code coverage for ASC and DESC was missing and has been improved.
  
  2) has been changed to limit the kind of criteria that are permissible:
  now only a column name is valid.
[18 Jan 2007 23:54] 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/18400

ChangeSet@1.2386, 2007-01-18 08:55:00-07:00, malff@weblab.(none) +3 -0
  Bug#24562 (ALTER TABLE ... ORDER BY ... with a stored function in expression
    asserts)
  WL#3681 (ALTER TABLE ORDER BY)
  
  Before this fix, the ALTER TABLE statement implemented an ORDER BY option
  with the following characteristics :
  
  1) The order by clause accepts a list of criteria, with optional ASC or
  DESC keywords
  
  2) Each criteria can be a general expression, involving operators,
  native functions, stored functions, user defined functions, subselects ...
  
  With this fix :
  
  1) has been left unchanged, since it's a de-facto existing feature,
  that was already present in the code base and partially covered in the test
  suite. Code coverage for ASC and DESC was missing and has been improved.
  
  2) has been changed to limit the kind of criteria that are permissible:
  now only a column name is valid.
[18 Jan 2007 23:54] 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/18401

ChangeSet@1.2601, 2007-01-18 16:53:49-07:00, malff@weblab.(none) +3 -0
  Bug#24562 (ALTER TABLE ... ORDER BY ... with complex expression asserts)
  WL#3681 (ALTER TABLE ORDER BY)
  
  Before this fix, the ALTER TABLE statement implemented an ORDER BY option
  with the following characteristics :
  
  1) The order by clause accepts a list of criteria, with optional ASC or
  DESC keywords
  
  2) Each criteria can be a general expression, involving operators,
  native functions, stored functions, user defined functions, subselects ...
  
  With this fix :
  
  1) has been left unchanged, since it's a de-facto existing feature,
  that was already present in the code base and partially covered in the test
  suite. Code coverage for ASC and DESC was missing and has been improved.
  
  2) has been changed to limit the kind of criteria that are permissible:
  now only a column name is valid.
[8 Feb 2007 16:59] Paul DuBois
Noted in 5.0.36, 5.1.16 changelogs.

For ALTER TABLE, using ORDER BY expression could cause a server 
crash. Now the ORDER BY clause allows only column names to be
specified as sort criteria (which was the only documented syntax,
anyway).

Leaving report in Patch Queued state pending push into 4.1 tree.
[7 Mar 2007 22:06] Konstantin Osipov
Pushed into 5.0.38 and 5.1.17.
[8 Mar 2007 3:14] Paul DuBois
Noted in 4.1.23 changelog.
[20 Mar 2007 11:27] Alexander Nozdrin
BUG#25154 (crash in alter table) has been marked
as a duplicate of this bug.
[8 May 2007 21:13] Nathan Tanner
The following statement should be clarified at http://dev.mysql.com/doc/refman/5.0/en/alter-table.html:

"ORDER BY syntax allows for one or more column names to be specified for sorting, each of which optionally can be followed by ASC or DESC to indicate ascending or descending sort order, respectively. The default is ascending order. Only column names are allowed as sort criteria; arbitrary expressions are not allowed. "

It should be clarified that up until very recently, expressions were in fact permitted in ALTER TABLE ... ORDER BY - it would actually be nice if it were fixed so that these could be supported again.