Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17954

DELETE statement with the same source and target in subselect

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.3, 10.3.11
    • Fix Version/s: 10.3, 10.4
    • Component/s: None
    • Labels:
      None
    • Environment:
      Windows 10, Java 8

      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

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              Roderick Radek Wikturna
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated: