Bug #32323 No Order by Rand() in Alter Clausel

• Submitted: 13 Nov 2007 15:46
• Modified: 15 Nov 2007 21:42
• Reporter: Patrick Müssig
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: > 5.0.33 && 5.1.26
• OS: Linux
• CPU Architecture: Any
• Tags: ALTER TABLE, Rand(), random

[13 Nov 2007 15:46] Patrick Müssig

Description:
Hello, 
I would like use the RAND() function in the ALTER clausel. My mysql Version is 5.1.22-rc-log.

Why was that the function deleted? 

How to repeat:
Mysql 5.0.26
ALTER TABLE test ORDER BY rand( ) 
No Errors

Mysql 5.1.22-rc-log
ALTER TABLE test ORDER BY rand( ) 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( )' at line

[13 Nov 2007 15:48] Patrick Müssig

change Server Version

[13 Nov 2007 16:03] Hartmut Holzgraefe

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

See http://dev.mysql.com/doc/refman/5.1/en/alter-table.html 

  alter_specification:
  [...]
   ORDER BY col_name [, col_name] ...
  [...]

So you can only order by column values on ALTER TABLE, 
not by calculated expressions ...

You might re-file this as a feature request to allow
expressions instead of just column names though.

[13 Nov 2007 21:20] Patrick Müssig

Yes, but in Version 5.0.26 is it possible ! why ??

[15 Nov 2007 21:38] Hartmut Holzgraefe

Behavior changed indeed somewhere between 5.0.33 and 5.0.37 (i didn't have the releases in between installed for testing).

The current behavior is as documented (only column names allowed) but as previously expressions worked just fine this should qualify as regression bug.

[15 Nov 2007 21:42] Hartmut Holzgraefe

Duplicate of Bug #24562, see the 5.0.36 release notes
http://dev.mysql.com/doc/refman/5.0/en/releasenotes-es-5-0-36.html

  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). (Bug#24562)