MySQL Bugs: #24562: ALTER TABLE ... ORDER BY ... with complex expression asserts

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

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 ... ?

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>

See WL#3681.

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.

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.

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.

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.

Pushed into 5.0.38 and 5.1.17.

Noted in 4.1.23 changelog.

BUG#25154 (crash in alter table) has been marked
as a duplicate of this bug.

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.