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

multi-table DELETE statement with the same source and target in subselect



    • Task
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.11
    • None
    • None


      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.


        Issue Links



              igor Igor Babaev
              Roderick Radek Wikturna
              1 Vote for this issue
              10 Start watching this issue



                Git Integration

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