[MDEV-17954] multi-table DELETE statement with the same source and target in subselect - Jira

Details

Type: Task
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Fix Version/s: 10.11
Component/s: None
Labels:
None

Description

I've been long awaiting the fix of ~~MDEV-12137~~ becuase this problem is a blocker in a few use-cases in our application. I've downloaded the latest MariaDB release (10.3.11) and retested our use-case.
To my utmost dissappointment, the problem persists! How can anyone say this problem is fixed, when it's not? Have you actually considered and tested other queries than the one in the issue description?

My use case:

DROP TABLE t1;

CREATE TABLE t1 (

id INT primary key,

c1 INT,

c2 INT,

parent_id INT);

alter table t1 add CONSTRAINT F1_PARENT FOREIGN KEY (parent_id) REFERENCES t1 (id);

DELETE CT0.* FROM t1 CT0 WHERE not exists (select 1 from t1 C1T0 where C1T0.parent_id = CT0.id);

-->

 ERROR: Error Code: 1093, SQL State: HY000, Message: (conn=27) Table 'CT0' is specified twice, both as a target for 'DELETE' and as a separate source for data

Please do not tell me to rewrite the query (as a workaround) because I can't do that. The query is generated by an ORM framework.
Please, test this use case and fix is as soon as possible.

Attachments

Issue Links

is blocked by

MDEV-28883 Re-design the upper level of handling UPDATE and DELETE statements

Closed

relates to

MDEV-12137 DELETE statement with the same source and target

Closed

Activity

Ascending order - Click to sort in descending order

Radek Wikturna added a comment - 2018-12-10 07:36 - edited

another query that fails:

DELETE CD2T0.*

FROM AMA_SYSTEM CT0

INNER JOIN AMA_COMPSYSTEM CD1T0 ON CD1T0.SYSTEM_ID = CT0.SYSTEM_ID AND (CD1T0.VALIDFROM<='2018-10-04') and (CD1T0.VALIDTO>='2018-10-04')

INNER JOIN AMA_COMPONENT CD2T0 ON CD2T0.COMPONENT_ID = CD1T0.COMPONENT_ID

WHERE (CT0.NAME like 'USU-%' escape '#') and not exists (

select 1

from AMA_COMPSYSTEM C5T0

INNER JOIN AMA_COMPONENT C6T0 ON C6T0.COMPONENT_ID = C5T0.COMPONENT_ID

INNER JOIN AMA_COMPONENT C7T0 ON C7T0.UPD_SUCCESSOR_ID = C6T0.COMPONENT_ID

where C5T0.SYSTEM_ID = CT0.SYSTEM_ID)

Again, this query is generated by an ORM framework.

AMA_COMPSYSTEM is a link table betweeb AMA_SYSTEM and AMA_COMPONENT :

CREATE TABLE `ama_compsystem` (

`COMPSYSTEM_ID` int(11) NOT NULL,

`COMPONENT_ID` int(11) NOT NULL,

`SYSTEM_ID` int(11) NOT NULL,

`VALIDFROM` date NOT NULL,

`VALIDTO` date,

PRIMARY KEY (`COMPSYSTEM_ID`),

CONSTRAINT `F_COS_CO` FOREIGN KEY (`COMPONENT_ID`) REFERENCES `ama_component` (`COMPONENT_ID`) ON DELETE CASCADE,

CONSTRAINT `F_COS_SY` FOREIGN KEY (`SYSTEM_ID`) REFERENCES `ama_system` (`SYSTEM_ID`),

);

if you need the complete table defintiions, please ask.

Radek Wikturna added a comment - 2018-12-10 07:36 - edited another query that fails: DELETE CD2T0.* FROM AMA_SYSTEM CT0 INNER JOIN AMA_COMPSYSTEM CD1T0 ON CD1T0.SYSTEM_ID = CT0.SYSTEM_ID AND (CD1T0.VALIDFROM<= '2018-10-04' ) and (CD1T0.VALIDTO>= '2018-10-04' ) INNER JOIN AMA_COMPONENT CD2T0 ON CD2T0.COMPONENT_ID = CD1T0.COMPONENT_ID WHERE (CT0. NAME like 'USU-%' escape '#' ) and not exists ( select 1 from AMA_COMPSYSTEM C5T0 INNER JOIN AMA_COMPONENT C6T0 ON C6T0.COMPONENT_ID = C5T0.COMPONENT_ID INNER JOIN AMA_COMPONENT C7T0 ON C7T0.UPD_SUCCESSOR_ID = C6T0.COMPONENT_ID where C5T0.SYSTEM_ID = CT0.SYSTEM_ID) Again, this query is generated by an ORM framework. AMA_COMPSYSTEM is a link table betweeb AMA_SYSTEM and AMA_COMPONENT : CREATE TABLE `ama_compsystem` ( `COMPSYSTEM_ID` int (11) NOT NULL , `COMPONENT_ID` int (11) NOT NULL , `SYSTEM_ID` int (11) NOT NULL , `VALIDFROM` date NOT NULL , `VALIDTO` date , PRIMARY KEY (`COMPSYSTEM_ID`), CONSTRAINT `F_COS_CO` FOREIGN KEY (`COMPONENT_ID`) REFERENCES `ama_component` (`COMPONENT_ID`) ON DELETE CASCADE , CONSTRAINT `F_COS_SY` FOREIGN KEY (`SYSTEM_ID`) REFERENCES `ama_system` (`SYSTEM_ID`), ); if you need the complete table defintiions, please ask.

Sergei Golubchik added a comment - 2021-01-09 16:31

Just to clarify, ~~MDEV-12137~~ only implemented a single-table DELETE case. It was a contributed patch and the contributor implemented what was important to him.

The syntax DELETE table.* FROM means it's a multi-table delete.

But the first DELETE from the description only even accesses one table, it can be rewritten to use single-table DELETE syntax by removing CT0.* part.

The DELETE from the comment uses many tables. It can, probably, be rewritten into a single-table DELETE by moving all other tables into a subquery.

I realize, though, that these statements are generated by an ORM framework, so rewriting them can be not practically possible.

Sergei Golubchik added a comment - 2021-01-09 16:31 Just to clarify, MDEV-12137 only implemented a single-table DELETE case. It was a contributed patch and the contributor implemented what was important to him. The syntax DELETE table.* FROM means it's a multi-table delete. But the first DELETE from the description only even accesses one table, it can be rewritten to use single-table DELETE syntax by removing CT0.* part. The DELETE from the comment uses many tables. It can, probably, be rewritten into a single-table DELETE by moving all other tables into a subquery. I realize, though, that these statements are generated by an ORM framework, so rewriting them can be not practically possible.

Roel Van de Paar added a comment - 2022-07-21 17:50

Minimum viable testcase

CREATE TABLE t(c INT);

DELETE t2.* FROM t t2 WHERE (SELECT 1 FROM t);

Roel Van de Paar added a comment - 2022-07-21 17:50 Minimum viable testcase CREATE TABLE t(c INT ); DELETE t2.* FROM t t2 WHERE ( SELECT 1 FROM t);

Roel Van de Paar added a comment - 2022-07-21 18:00 - edited

There is another bug here. This alternative sql should not succeed. t2 does not exist.

CREATE TABLE t(c INT);

DELETE t2.* FROM t t2 WHERE c=1;

10.10.0 88b22356e623fd63aa87273a895521a6e6667bc7 (Debug)
10.10.0-dbg>CREATE TABLE t(c INT);
Query OK, 0 rows affected (0.014 sec)

10.10.0-dbg>DELETE t2.* FROM t t2 WHERE c=1;
Query OK, 0 rows affected (0.002 sec)

There is no difference in output if t2 is created beforehand.

Roel Van de Paar added a comment - 2022-07-21 18:00 - edited There is another bug here. This alternative sql should not succeed. t2 does not exist. CREATE TABLE t(c INT ); DELETE t2.* FROM t t2 WHERE c=1; 10.10.0 88b22356e623fd63aa87273a895521a6e6667bc7 (Debug) 10.10.0-dbg>CREATE TABLE t(c INT); Query OK, 0 rows affected (0.014 sec) 10.10.0-dbg>DELETE t2.* FROM t t2 WHERE c=1; Query OK, 0 rows affected (0.002 sec) There is no difference in output if t2 is created beforehand.

Roel Van de Paar added a comment - 2022-07-21 18:20

Also, the manual (https://mariadb.com/kb/en/delete/) states that more than two tables can be specified. There seem to be further inconsistencies:

CREATE TABLE t(c INT);

CREATE TABLE t2(d INT);

INSERT INTO t VALUES (1);

INSERT INTO t2 VALUES (2);

DELETE t2.* FROM t t0 t2 WHERE c=1;

DELETE t2.* FROM t2 t0 t WHERE c=1;

DELETE t2.* FROM t2 t0 WHERE c=1;

DELETE t2.* FROM t0 t2 WHERE c=1;

DELETE t2.* FROM t t2 WHERE c=1;

10.10.0 88b22356e623fd63aa87273a895521a6e6667bc7 (Debug)
10.10.0-dbg>CREATE TABLE t(c INT);
Query OK, 0 rows affected (0.014 sec)

10.10.0-dbg>CREATE TABLE t2(d INT);
Query OK, 0 rows affected (0.012 sec)

10.10.0-dbg>INSERT INTO t VALUES (1);
Query OK, 1 row affected (0.003 sec)

10.10.0-dbg>INSERT INTO t2 VALUES (2);
Query OK, 1 row affected (0.002 sec)

10.10.0-dbg>DELETE t2.* FROM t t0 t2 WHERE c=1;
ERROR 1109 (42S02): Unknown table 't2' in MULTI DELETE
10.10.0-dbg>DELETE t2.* FROM t2 t0 t WHERE c=1;
ERROR 1109 (42S02): Unknown table 't2' in MULTI DELETE
10.10.0-dbg>DELETE t2.* FROM t2 t0 WHERE c=1;
ERROR 1109 (42S02): Unknown table 't2' in MULTI DELETE
10.10.0-dbg>DELETE t2.* FROM t0 t2 WHERE c=1;
ERROR 1146 (42S02): Table 'test.t0' doesn't exist
10.10.0-dbg>DELETE t2.* FROM t t2 WHERE c=1;
Query OK, 1 row affected (0.002 sec)

Roel Van de Paar added a comment - 2022-07-21 18:20 Also, the manual ( https://mariadb.com/kb/en/delete/ ) states that more than two tables can be specified. There seem to be further inconsistencies: CREATE TABLE t(c INT ); CREATE TABLE t2(d INT ); INSERT INTO t VALUES (1); INSERT INTO t2 VALUES (2); DELETE t2.* FROM t t0 t2 WHERE c=1; DELETE t2.* FROM t2 t0 t WHERE c=1; DELETE t2.* FROM t2 t0 WHERE c=1; DELETE t2.* FROM t0 t2 WHERE c=1; DELETE t2.* FROM t t2 WHERE c=1; 10.10.0 88b22356e623fd63aa87273a895521a6e6667bc7 (Debug) 10.10.0-dbg>CREATE TABLE t(c INT); Query OK, 0 rows affected (0.014 sec) 10.10.0-dbg>CREATE TABLE t2(d INT); Query OK, 0 rows affected (0.012 sec) 10.10.0-dbg>INSERT INTO t VALUES (1); Query OK, 1 row affected (0.003 sec) 10.10.0-dbg>INSERT INTO t2 VALUES (2); Query OK, 1 row affected (0.002 sec) 10.10.0-dbg>DELETE t2.* FROM t t0 t2 WHERE c=1; ERROR 1109 (42S02): Unknown table 't2' in MULTI DELETE 10.10.0-dbg>DELETE t2.* FROM t2 t0 t WHERE c=1; ERROR 1109 (42S02): Unknown table 't2' in MULTI DELETE 10.10.0-dbg>DELETE t2.* FROM t2 t0 WHERE c=1; ERROR 1109 (42S02): Unknown table 't2' in MULTI DELETE 10.10.0-dbg>DELETE t2.* FROM t0 t2 WHERE c=1; ERROR 1146 (42S02): Table 'test.t0' doesn't exist 10.10.0-dbg>DELETE t2.* FROM t t2 WHERE c=1; Query OK, 1 row affected (0.002 sec)

Roel Van de Paar added a comment - 2022-07-21 18:22 - edited

Unless I am mistaken, this also looks significantly incorrect:

CREATE TABLE t(c INT);

CREATE TABLE t2(d INT);

INSERT INTO t VALUES (1);

INSERT INTO t2 VALUES (2);

DELETE FROM t2 USING t t2 WHERE c=1;

10.10.0 88b22356e623fd63aa87273a895521a6e6667bc7 (Debug)
10.10.0-dbg>CREATE TABLE t(c INT);
Query OK, 0 rows affected (0.015 sec)

10.10.0-dbg>CREATE TABLE t2(d INT);
Query OK, 0 rows affected (0.012 sec)

10.10.0-dbg>INSERT INTO t VALUES (1);
Query OK, 1 row affected (0.003 sec)

10.10.0-dbg>INSERT INTO t2 VALUES (2);
Query OK, 1 row affected (0.002 sec)

10.10.0-dbg>DELETE FROM t2 USING t t2 WHERE c=1;
Query OK, 1 row affected (0.002 sec)

10.10.0-dbg>SELECT * FROM t2;
+------+
\| d \|
+------+
\| 2 \|
+------+
1 row in set (0.001 sec)

10.10.0-dbg>SELECT * FROM t;
Empty set (0.001 sec)

Roel Van de Paar added a comment - 2022-07-21 18:22 - edited Unless I am mistaken, this also looks significantly incorrect: CREATE TABLE t(c INT ); CREATE TABLE t2(d INT ); INSERT INTO t VALUES (1); INSERT INTO t2 VALUES (2); DELETE FROM t2 USING t t2 WHERE c=1; 10.10.0 88b22356e623fd63aa87273a895521a6e6667bc7 (Debug) 10.10.0-dbg>CREATE TABLE t(c INT); Query OK, 0 rows affected (0.015 sec) 10.10.0-dbg>CREATE TABLE t2(d INT); Query OK, 0 rows affected (0.012 sec) 10.10.0-dbg>INSERT INTO t VALUES (1); Query OK, 1 row affected (0.003 sec) 10.10.0-dbg>INSERT INTO t2 VALUES (2); Query OK, 1 row affected (0.002 sec) 10.10.0-dbg>DELETE FROM t2 USING t t2 WHERE c=1; Query OK, 1 row affected (0.002 sec) 10.10.0-dbg>SELECT * FROM t2; +------+ | d | +------+ | 2 | +------+ 1 row in set (0.001 sec) 10.10.0-dbg>SELECT * FROM t; Empty set (0.001 sec)

Lukas Eder added a comment - 2023-03-03 11:17

This is a curious issue, especially given that:

create table t (i int);

-- This works: (It doesn't work in MySQL)

delete from t where t.i = (select max(i) from t);

-- This doesn't work, but should really be equivalent:

delete from u using t as u where u.i = (select max(i) from t);

It seems that MariaDB did a few things to solve the MySQL trouble heritage in the first query:

SQL Error [1093] [HY000]: You can't specify target table 't' for update in FROM clause

But this particular syntax seems to have been overlooked?

Lukas Eder added a comment - 2023-03-03 11:17 This is a curious issue, especially given that: create table t (i int); -- This works: (It doesn't work in MySQL) delete from t where t.i = (select max(i) from t); -- This doesn't work, but should really be equivalent: delete from u using t as u where u.i = (select max(i) from t); It seems that MariaDB did a few things to solve the MySQL trouble heritage in the first query: SQL Error [1093] [HY000]: You can't specify target table 't' for update in FROM clause But this particular syntax seems to have been overlooked?

Sergei Petrunia added a comment - 2023-08-24 10:27

After 11.1 with fixes for ~~MDEV-28883~~ ~~MDEV-7487~~, the request in the MDEV description seems to be satisfied. The testcase by lukas.eder now works, too.

However, some of the examples provided by Roel still seem to work incorrectly.

Sergei Petrunia added a comment - 2023-08-24 10:27 After 11.1 with fixes for MDEV-28883 MDEV-7487 , the request in the MDEV description seems to be satisfied. The testcase by lukas.eder now works, too. However, some of the examples provided by Roel still seem to work incorrectly.

People

Assignee:: Unassigned

Reporter:: Radek Wikturna

Votes:: 1 Vote for this issue

Watchers:: 10 Start watching this issue

Dates

Created:: 2018-12-10 07:33

Updated:: 2025-04-01 13:14

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server