PL/SQL parser (MDEV-10142)

[MDEV-12137] DELETE statement with the same source and target Created: 2017-02-27  Updated: 2021-01-09  Resolved: 2017-07-07

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: 10.3
Fix Version/s: 10.3.1

Type: Technical task Priority: Major
Reporter: Alexander Barkov Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: Compatibility

Issue Links:
Relates
relates to MDEV-12874 UPDATE statements with the same sourc... Closed
relates to MDEV-13429 main.delete_use_source fails sporadic... Closed
relates to MDEV-17954 multi-table DELETE statement with the... Confirmed
Sprint: 10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-4, 10.1.18

 Description   

Example :

DROP TABLE t1;
 
CREATE TABLE t1 (c1 INT, c2 INT);
 
DELETE FROM t1 WHERE c1 IN (SELECT b.c1 FROM t1 b WHERE b.c2=0);

currently returns:

DELETE FROM t1 WHERE c1 IN (SELECT b.c1 FROM t1 b WHERE b.c2=0);
 
ERROR 1093 (HY000): Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data

The same script works fine in Oracle.

 Comments   
Comment by Michael Widenius [ 2017-03-22 ]

Suggestion of how to implement this:

  • Instead of deleting the rows, first mark them deleted (by storing a record pointer to the be-deleted rows)
    and them delete them in a seconds pass.
  • We already do this for secondary tables in our multi_delete code (see end of sql_delete.cc).
    Normally we do deletes directly in the first scan table, but in this case we shouldn't do that but instead
    treat the first table like any other table (mark first and then delete)
  • There is a flag delete_while_scanning that looks like it already provides this functionality, don't know why this
    doesn't work in this case.
  • It could be that the above delete is treated as a single delete table and this is why one gets the error. Detecting this case and changing this to multi-delete may solve this or at least part of this issue.
Comment by Sergei Golubchik [ 2018-12-07 ]

Please, report it as a new bug. This issue was fixed, and there is a test proving that one can indeed use the same table as a source and a delete target, at least in all tested cases.

Comment by Sergei Golubchik [ 2021-01-09 ]

FYI, this feature was only about single-table DELETE, multi-delete isn't fixed yet

Generated at Thu Feb 08 07:55:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.