MySQL Bugs: #17399: Multi-table DELETE statement fails, identical SELECT statement works

Bug #17399	Multi-table DELETE statement fails, identical SELECT statement works
Submitted:	14 Feb 2006 20:47	Modified:	5 May 2006 16:39
Reporter:	Ty Schalter	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.16	OS:	Windows (Windows Server 2003)
Assigned to:		CPU Architecture:	Any

Description:
I'm trying to make sure the data exists in the four tables (Roster, Information, Citizen, Primary) before deleting and re-populating the data. This statement gives me the following error: 

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELETE  
  FROM  e 
 USING  Original.Extract_Keys e, 
(SELECT 'Cri-Civ' as CA' at line 1

Statement:

DELETE  
  FROM  e 
 USING  Original.Extract_Keys e, 
(SELECT 'Cri-Civ' as FILE_CODE 
   FROM  Update.Roster 
  LIMIT 1) d, 
(SELECT 'Cri-Civ' as FILE_CODE
   FROM  Update.Information
  LIMIT 1) dd, 
(SELECT 'Cri-Civ' as FILE_CODE 
   FROM  Update.Citizen
  LIMIT 1) pd, 
(SELECT 'Cri-Civ' as FILE_CODE 
   FROM  Update.Primary
  LIMIT 1) a 
 WHERE e.VENDOR  = 'ACME' 
   AND e.FILE_CODE  = d.FILE_CODE
   AND d.FILE_CODE  = dd.FILE_CODE 
   AND dd.FILE_CODE = pd.FILE_CODE 
   AND pd.FILE_CODE = a.FILE_CODE

The following has been tried and found to work: 
SELECT * 
  FROM  Original.Extract_Keys e,  
(SELECT 'Cri-Civ' as FILE_CODE
   FROM Update.Roster
  LIMIT 1) d, 
(SELECT 'Cri-Civ' as FILE_CODE 
   FROM Update.Information
  LIMIT 1) dd, 
(SELECT 'Cri-Civ' as FILE_CODE 
   FROM Update.Citizen
  LIMIT 1) pd, 
(SELECT 'Cri-Civ' as FILE_CODE
   FROM Update.Primary
  LIMIT 1) a 
 WHERE e.VENDOR = 'ACME' 
   AND e.FILE_CODE  = d.FILE_CODE 
   AND d.FILE_CODE  = dd.FILE_CODE
   AND dd.FILE_CODE = pd.FILE_CODE 
   AND pd.FILE_CODE = a.FILE_CODE

I've also tried switching to explicit inner joins instead (in the delete), and get the same error. I've tried removing the alias for Extract_Keys and using the entire database/table name, and the same error occurs there as well.

How to repeat:
Install MySQL server 5.0 on Windows Server 2003.  Craft a multi-table SELECT statement like the above; it should return results.  Craft a multi-table DELETE statement like the above, it should fail with error 1064.

Thank you for a problem report. Sorry, but it is not a bug. Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/delete.html):

"...
Currently, you cannot delete from a table and select from the same table in a subquery."

This is the reason for the error message you got.

I'm not sure we're on the same page.  I am not deleting from the same table I'm selecting from.  I am deleting from table "e", but selecting from tables "d", "dd", "pd", and "a".

Thanks,
Ty

Please, send the 

EXPLAIN SELECT * 
  FROM  Original.Extract_Keys e,  
(SELECT 'Cri-Civ' as FILE_CODE
   FROM Update.Roster
  LIMIT 1) d, 
(SELECT 'Cri-Civ' as FILE_CODE 
   FROM Update.Information
  LIMIT 1) dd, 
(SELECT 'Cri-Civ' as FILE_CODE 
   FROM Update.Citizen
  LIMIT 1) pd, 
(SELECT 'Cri-Civ' as FILE_CODE
   FROM Update.Primary
  LIMIT 1) a 
 WHERE e.VENDOR = 'ACME' 
   AND e.FILE_CODE  = d.FILE_CODE 
   AND d.FILE_CODE  = dd.FILE_CODE
   AND dd.FILE_CODE = pd.FILE_CODE 
   AND pd.FILE_CODE = a.FILE_CODE\G

results. Let's check the execution plan.

+----+-------------+-----------------+--------+-----------------------------------------------------------------------------+---------------+---------+-------------+----------+--------------------------+
| id | select_type | table           | type   | possible_keys                                                               | key           | key_len | ref         | rows     | Extra                    |
+----+-------------+-----------------+--------+-----------------------------------------------------------------------------+---------------+---------+-------------+----------+--------------------------+
|  1 | PRIMARY     | <derived2>      | system | NULL                                                                        | NULL          | NULL    | NULL        |        1 |                          |
|  1 | PRIMARY     | <derived3>      | system | NULL                                                                        | NULL          | NULL    | NULL        |        1 |                          |
|  1 | PRIMARY     | <derived4>      | system | NULL                                                                        | NULL          | NULL    | NULL        |        1 |                          |
|  1 | PRIMARY     | <derived5>      | system | NULL                                                                        | NULL          | NULL    | NULL        |        1 |                          |
|  1 | PRIMARY     | e               | ref    | PRIMARY,VENDOR,VENDOR_2,VENDOR_3,VENDOR_4,VENDOR_5 | VENDOR_5 | 12      | const,const |     6418 | Using where; Using index |
|  5 | DERIVED     | Primary       | index  | NULL                                                                        | PRIMARY       | 57      | NULL        |  1951176 | Using index              |
|  4 | DERIVED     | Citizen     | index  | NULL                                                                        | PRIMARY       | 15      | NULL        |  1616151 | Using index              |
|  3 | DERIVED     | Information| index  | NULL                                                                        | PRIMARY       | 17      | NULL        | 14327514 | Using index              |
|  2 | DERIVED     | Roster      | index  | NULL                                                                        | PRIMARY       | 10      | NULL        |   786970 | Using index              |
+----+-------------+-----------------+--------+-----------------------------------------------------------------------------+---------------+---------+-------------+----------+--------------------------+
9 rows in set (0.00 sec)

Please, send the SHOW CREATE TABLE results for all the tables mentioned in your SELECT/DELETYE statement:

- Original.Extract_Keys
- Update.Roster
- Update.Information
- Update.Citizen
- Update.Primary

Do you have any triggers defined for any of the tables?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".