MySQL Bugs: #2920: Using subquery with the delete command

Bug #2920	Using subquery with the delete command
Submitted:	22 Feb 2004 6:47	Modified:	25 Feb 2004 4:21
Reporter:	Fabrizio Camuso	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.1 alpha	OS:	Windows (win xp pro (without sp1))
Assigned to:	Peter Gulutzan	CPU Architecture:	Any

Description:
THE SEQUENT COMMAND WORKS FINE

select * from macchinette as tab1
where (select sum(quantitaMerendine) from macchinette, posizioni
       where idMacchinetta=ksMacchinetta and tab1.idMacchinetta = idMacchinetta) = 0;

BUT IF I CHANGE THE SELECT WITH DELETE:

delete from macchinette as tab1
where (select sum(quantitaMerendine) from macchinette, posizioni
       where idMacchinetta=ksMacchinetta and tab1.idMacchinetta = idMacchinetta) = 0;

I OBTAIN THE ERROR:
Check the manual that corresponds to your MySQL server version for the right syntax to use near 'where (select sum(quantitaMerendine) from macchinette, posizion

I apologize if this can't be considered a real bug but my ignorance about a well known limitation of the delete sintax ... :) 

How to repeat:
CREATE TABLE `a` (`idA` INT (3) NOT NULL AUTO_INCREMENT, `fieldA` CHAR (30), PRIMARY KEY(`idA`));

INSERT INTO a (idA, fieldA) VALUES (NULL, 'A1');
INSERT INTO a (idA, fieldA) VALUES (NULL, 'A2');

CREATE TABLE `b` (`idB` INT (3) UNSIGNED NOT NULL AUTO_INCREMENT, `sumThis` INT (3) UNSIGNED DEFAULT '0', `EK` INT (3) UNSIGNED DEFAULT '0', PRIMARY KEY(`idB`));

/* EK is the foreign key wich refers to idA; table A is 1-M associated with table B */

INSERT INTO b (idB, sumThis, EK) VALUES (NULL, 0, 1);
INSERT INTO b (idB, sumThis, EK) VALUES (NULL, 0, 1);
INSERT INTO b (idB, sumThis, EK) VALUES (NULL, 0, 2);

/*
  the query below works fine and selects the rows for which the sum of the value of the field sumThis  is 0 (join between table A and table B)
*/
select * from A as tab1
where (select sum(sumThis) from A, B
       where idA=EK and tab1.idA = idA) = 0;

/*
  the query below obtains the sintax error ...
*/
delete from A as tab1
where (select sum(sumThis) from A, B
       where idA=EK and tab1.idA = idA) = 0;

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Several things:

* The syntax error you get is because DELETE does not allow specifying an Alias of table

DELETE FROM a AS b; -> this is not allowed

* You use correlated subquery in WHERE clause of DELETE.
I am not sure if it is allowed by standard at all

select * from a as tab1 where (select sum(sumThis) from a, b where idA=EK and tab1.idA = idA) = 0;

* Even without correlated subquery MySQL will not allow you to use table you delete from in subquery.

mysql> delete from a where (select sum(0) from a) = 0;
ERROR 1093 (HY000): You can't specify target table 'a' for update in FROM clause

Again I am not sure if standard allows this or not.

* You should be able to resolve this with using Multi Table Delete Syntax MySQL implements.

From Manual Chapter DELETE Syntax

http://www.mysql.com/doc/en/DELETE.html

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] table_name[.*] [, table_name[.*] ...]
       FROM table-references
       [WHERE where_definition]
or

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
       FROM table_name[.*] [, table_name[.*] ...]
       USING table-references
       [WHERE where_definition]

Small correction.

MySQL allows correlated subquery in WHERE clause of DELETE in form of:

DELETE FROM x WHERE (SELECT SUM(0) FROM y WHERE y.id = x.id) = 0;

And something very important I forgot:

Thank you a lot for the perfect test case.