Bug #32030 DELETE does not return an error and deletes rows if error evaluating WHERE
Submitted: 1 Nov 2007 12:55 Modified: 14 Dec 2007 19:47
Reporter: Konstantin Osipov (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: Konstantin Osipov CPU Architecture:Any
Triage: D2 (Serious)

[1 Nov 2007 12:55] Konstantin Osipov
Description:
The following query in group_min_max.test executes successfully with a warning "Subquery returns more than one row" instead of returning an error:

DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x
                      FROM t1) > 10000;

The subquery in the WHERE clause returns more than one row.
P2 because it is a "bad data" bug.

How to repeat:
Run the test suite, group_min_max.test. Check the result file, the error is converted to a warning.

Suggested fix:
Check the error when evaluating the where clause.
===== sql_delete.cc 1.228 vs edited =====
--- 1.228/sql/sql_delete.cc     2007-10-30 20:08:12 +03:00
+++ edited/sql_delete.cc        2007-11-01 15:39:08 +03:00
@@ -159,13 +159,14 @@ bool mysql_delete(THD *thd, TABLE_LIST *
     delete select;
     free_underlaid_joins(thd, select_lex);
     thd->row_count_func= 0;
-    send_ok(thd, (ha_rows) thd->row_count_func);
+    if (! thd->is_error())
+      send_ok(thd, (ha_rows) thd->row_count_func);
     /*
       We don't need to call reset_auto_increment in this case, because
       mysql_truncate always gives a NULL conds argument, hence we never
       get here.
     */
-    DBUG_RETURN(0);                            // Nothing to delete
+    DBUG_RETURN(thd->is_error());                 // Nothing to delete
   }
 
   /* If running in safe sql mode, don't allow updates without keys */
@@ -254,10 +255,15 @@ bool mysql_delete(THD *thd, TABLE_LIST *
   while (!(error=info.read_record(&info)) && !thd->killed &&
         ! thd->is_error())
   {
-    // thd->is_error() is tested to disallow delete row on error
-    if (!(select && select->skip_record())&& ! thd->is_error() )
+    bool do_delete_record= !select || !select->skip_record();
+    /* thd->is_error() is tested to disallow delete row on error. */
+    if (thd->is_error())
+    {
+      error= 1;
+      break;
+    }
+    if (do_delete_record)
     {
-
       if (table->triggers &&
           table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
                                             TRG_ACTION_BEFORE, FALSE))
[1 Nov 2007 13:01] 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/36863

ChangeSet@1.2611, 2007-11-01 16:01:43+03:00, kostja@bodhi.(none) +3 -0
  A fix for Bug#32030 "DELETE does not return an error and deletes rows if 
  error evaluating WHERE"
  
  DELETE with a subquery in WHERE clause would sometimes ignore subquery
  evaluation error and proceed with deletion.
  
  The fix is to check for an error after evaluation of the WHERE clause
  in DELETE.
  
  The bug is covered by the existing test suite.
[1 Nov 2007 23:23] 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/36932

ChangeSet@1.2613, 2007-11-02 02:23:06+03:00, kostja@bodhi.(none) +5 -0
  A fix for Bug#32030 "DELETE does not return an error and deletes rows if 
  error evaluating WHERE"
  
  DELETE with a subquery in WHERE clause would sometimes ignore subquery
  evaluation error and proceed with deletion.
  
  The fix is to check for an error after evaluation of the WHERE clause
  in DELETE.
  
  The bug is covered by the existing test suite.
[1 Nov 2007 23:36] 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/36934

ChangeSet@1.2613, 2007-11-02 02:36:12+03:00, kostja@bodhi.(none) +5 -0
  A fix for Bug#32030 "DELETE does not return an error and deletes rows if 
  error evaluating WHERE"
  
  DELETE with a subquery in WHERE clause would sometimes ignore subquery
  evaluation error and proceed with deletion.
  
  The fix is to check for an error after evaluation of the WHERE clause
  in DELETE.
  
  Addressed review comments.
[2 Nov 2007 11:47] 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/36980

ChangeSet@1.2606, 2007-11-02 14:47:18+03:00, kostja@bodhi.(none) +2 -0
  Cleanup the test case for Bug#32030 "DELETE does not return an error and
   deletes rows if error evaluating WHERE"
[7 Nov 2007 21:59] Bugs System
Pushed into 6.0.4-alpha
[7 Nov 2007 22:01] Bugs System
Pushed into 5.1.23-rc
[6 Dec 2007 11:13] Konstantin Osipov
Pushed into 5.1.23
[14 Dec 2007 19:47] Paul Dubois
Noted in 5.1.23, 6.0.4 changelogs.

A DELETE statement with a subquery in the WHERE clause would
sometimes ignore an error during subquery evaluation and proceed with
the delete operation.