bk commit into 5.0 tree (gkodinov:1.2419) BUG#26186

[kgeorge]

Below is the list of changes that have just been committed into a local
5.0 repository of kgeorge. When kgeorge does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-02-23 18:49:41+02:00, gkodinov@stripped +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.

  mysql-test/r/delete.result@stripped, 2007-02-23 18:49:33+02:00, gkodinov@stripped +9 -0
    Bug #26186: test case

  mysql-test/t/delete.test@stripped, 2007-02-23 18:49:34+02:00, gkodinov@stripped +18 -0
    Bug #26186: test case

  sql/sql_delete.cc@stripped, 2007-02-23 18:49:34+02:00, gkodinov@stripped +21 -16
    Bug #26186: do validity check of the ORDER BY
    before deciding to skip it.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	gkodinov
# Host:	macbook.gmz
# Root:	/Users/kgeorge/mysql/work/B26186-5.0-opt

--- 1.192/sql/sql_delete.cc	2007-02-21 21:59:49 +02:00
+++ 1.193/sql/sql_delete.cc	2007-02-23 18:49:34 +02:00
@@ -60,6 +60,27 @@ bool mysql_delete(THD *thd, TABLE_LIST *
   if (mysql_prepare_delete(thd, table_list, &conds))
     DBUG_RETURN(TRUE);
 
+  /* check ORDER BY even if it can be ignored */
+  if (order && order->elements)
+  {
+    TABLE_LIST   tables;
+    List<Item>   fields;
+    List<Item>   all_fields;
+
+    bzero((char*) &tables,sizeof(tables));
+    tables.table = table;
+    tables.alias = table_list->alias;
+
+      if (select_lex->setup_ref_array(thd, order->elements) ||
+	  setup_order(thd, select_lex->ref_pointer_array, &tables,
+                    fields, all_fields, (ORDER*) order->first))
+    {
+      delete select;
+      free_underlaid_joins(thd, &thd->lex->select_lex);
+      DBUG_RETURN(TRUE);
+    }
+  }
+
   const_cond= (!conds || conds->const_item());
   safe_update=test(thd->options & OPTION_SAFE_UPDATES);
   if (safe_update && const_cond)
@@ -148,23 +169,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *
   {
     uint         length= 0;
     SORT_FIELD  *sortorder;
-    TABLE_LIST   tables;
-    List<Item>   fields;
-    List<Item>   all_fields;
     ha_rows examined_rows;
-
-    bzero((char*) &tables,sizeof(tables));
-    tables.table = table;
-    tables.alias = table_list->alias;
-
-      if (select_lex->setup_ref_array(thd, order->elements) ||
-	  setup_order(thd, select_lex->ref_pointer_array, &tables,
-                    fields, all_fields, (ORDER*) order->first))
-    {
-      delete select;
-      free_underlaid_joins(thd, &thd->lex->select_lex);
-      DBUG_RETURN(TRUE);
-    }
     
     if ((!select || table->quick_keys.is_clear_all()) && limit != HA_POS_ERROR)
       usable_index= get_index_for_order(table, (ORDER*)(order->first), limit);

--- 1.25/mysql-test/t/delete.test	2007-01-12 15:43:51 +02:00
+++ 1.26/mysql-test/t/delete.test	2007-02-23 18:49:34 +02:00
@@ -203,3 +203,21 @@ select * from t1 where a is null;
 delete from t1 where a is null;
 select count(*) from t1;
 drop table t1;
+
+#
+# Bug #26186: delete order by, sometimes accept unknown column
+#
+CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1);
+
+--error ER_BAD_FIELD_ERROR
+DELETE FROM t1 ORDER BY x;
+
+# even columns from a table not used in query (and not even existing)
+--error ER_BAD_FIELD_ERROR
+DELETE FROM t1 ORDER BY t2.x;
+
+# subquery (as long as the subquery from is valid or DUAL)
+--error ER_BAD_FIELD_ERROR
+DELETE FROM t1 ORDER BY (SELECT x);
+
+DROP TABLE t1;

--- 1.27/mysql-test/r/delete.result	2007-01-12 15:43:51 +02:00
+++ 1.28/mysql-test/r/delete.result	2007-02-23 18:49:33 +02:00
@@ -214,3 +214,12 @@ select count(*) from t1;
 count(*)
 0
 drop table t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+DELETE FROM t1 ORDER BY x;
+ERROR 42S22: Unknown column 'x' in 'order clause'
+DELETE FROM t1 ORDER BY t2.x;
+ERROR 42S22: Unknown column 't2.x' in 'order clause'
+DELETE FROM t1 ORDER BY (SELECT x);
+ERROR 42S22: Unknown column 'x' in 'field list'
+DROP TABLE t1;