MySQL Bugs: #26186: delete order by, sometimes accept unknown column

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

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:
-

Thank you for the bug report. Verified as described.

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.

Pushed into 5.1.18-beta

Pushed into 5.0.40

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.