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

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

    XMLWordPrintable

Details

    • Task
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.11
    • None
    • 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

          Activity

            People

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

              Dates

                Created:
                Updated:

                Git Integration

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