Details
-
Technical task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3(EOL)
-
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.
Attachments
Issue Links
- relates to
-
MDEV-12874
UPDATE statements with the same source and target
-
- Closed
-
-
MDEV-13429 main.delete_use_source fails sporadically in buildbot with wrong execution plan
-
- Closed
-
-
MDEV-17954 multi-table DELETE statement with the same source and target in subselect
-
- Confirmed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Summary | sql_mode=ORACLE: DELETE statement with the same source and target | DELETE statement with the same source and target |
| Link |
This issue relates to |
| Labels | Compatibility |
| Fix Version/s | 10.3 [ 22126 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Assignee | Alexander Barkov [ bar ] | Sergei Golubchik [ serg ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Fix Version/s | 10.3.1 [ 22532 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Review [ 10002 ] | Closed [ 6 ] |
| Link |
This issue relates to |
| Parent Issue | MDEV-10764 [ MDEV-10764 ] |
|
| Link | This issue relates to MDEV-17954 [ MDEV-17954 ] |
| Comment |
[ 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) 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`), ); ] |
| Comment |
[ I've been long awaiting this fix 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 query that fails: DELETE CT0.* FROM AMS_TICKET CT0 WHERE not exists (select 1 from AMS_TICKET C1T0 where C1T0.INITIAL_CALL_ID = CT0.TICKET_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 INITIAL_CALL_ID is a foreign key to the same table: CONSTRAINT `F_TICKET_TICKETIC` FOREIGN KEY (`INITIAL_CALL_ID`) REFERENCES `ams_ticket` (`TICKET_ID`), 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. Until then, the issue should be reopened. Or should I create a new one? ] |
| Workflow | MariaDB v3 [ 79762 ] | MariaDB v4 [ 151755 ] |