Bug #38250 ALTER TABLE tablename ORDER BY rand() doesn't work

• Submitted: 20 Jul 2008 23:58
• Modified: 21 Jul 2008 0:04
• Reporter: Federico Razzoli (OCA)
• Status: Not a Bug
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.0.51
• OS: Linux (Ubuntu 8.04)
• CPU Architecture: Any

[20 Jul 2008 23:58] Federico Razzoli

Description:
If you try to perform an ALTER TABLE ... ORDER BY rand() on an existing table, you get en error 1064.

If you perform ALTER TABLE ... ORDER BY fieldname it works correctly.

This is a trivial bug; however, if you give an order to a table, then you can't undo it.

How to repeat:
CREATE TABLE mytable (id INT, data VARCHAR(100));
ALTER TABLE mytable ORDER BY id;
ALTER TABLE mytable ORDER BY data;
ALTER TABLE mytable ORDER BY rand()

[21 Jul 2008 0:04] Federico Razzoli

NOT A BUG.

SORRY!

I just read carefully ALTER TABLE page in the documentation and found out that SQL expressions are not allowed in ALTER TABLE ORDER BY.