|
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.
|
|
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.
|
|
Minimum viable testcase
CREATE TABLE t(c INT);
|
DELETE t2.* FROM t t2 WHERE (SELECT 1 FROM t);
|
|
|
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.
|
|
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)
|
|
|
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)
|
|
|
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?
|
|
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.
|