Bug #26186 delete order by, sometimes accept unknown column
Submitted: 8 Feb 2007 14:53 Modified: 4 Apr 2007 4:33
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.22, 5.0.33, 5.0.34 OS:Any (*)
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: delete, order by, qc

[8 Feb 2007 14:53] Martin Friebe
Description:
delete from table order by x

If neither a where, nor a limit clause is given, mysql does not check, if the columns in order by exist.

Of course if there is no where and no limit, the order by can be ignored, because all entries will be deleted.
I think it should still be checked for correctness, if it is supplied.

How to repeat:
create table t1 (a int); insert into t1 select 1;

delete from t1 order by x;

# even columns from a table not used in query (and not even existing)
delete from t1 order by t2.x;

# subquery (as long as the subquery from is valid or DUAL)
delete from t1 order by (select x);

Suggested fix:
-
[19 Feb 2007 13:38] Miguel Solorzano
Thank you for the bug report. Verified as described.
[23 Feb 2007 16: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/20472

ChangeSet@1.2419, 2007-02-23 18:49:41+02:00, gkodinov@macbook.gmz +3 -0
  Bug #26186: 
  When handling DELETE ... FROM if there is no
  condition it is internally transformed to 
  TRUNCATE for more efficient execution by the
  storage handler.
  The check for validity of the optional ORDER BY 
  clause is done after the check for the above 
  optimization and will not be performed if the
  optimization can be applied.
  Moved the validity check for ORDER BY before 
  the optimization so it performed regardless of
  the optimization.
[31 Mar 2007 8:39] Bugs System
Pushed into 5.1.18-beta
[31 Mar 2007 8:44] Bugs System
Pushed into 5.0.40
[4 Apr 2007 4:33] Paul Dubois
Noted in 5.0.40, 5.1.18 changelogs.

For DELETE FROM tbl_name ORDER BY col_name (with no WHERE or LIMIT
clause), the server did not check whether col_name was a valid column
in the table.