Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10142 PL/SQL parser
  3. MDEV-12137

DELETE statement with the same source and target

Details

    • 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

          Technical task - A technical task. MDEV-12874 UPDATE statements with the same source and target

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13429 main.delete_use_source fails sporadically in buildbot with wrong execution plan

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-17954 multi-table DELETE statement with the same source and target in subselect

          • Major - Major loss of function.
          • Confirmed

          Activity

            Ascending order - Click to sort in descending order
            monty Michael Widenius added a comment -

            Suggestion of how to implement this:

            • Instead of deleting the rows, first mark them deleted (by storing a record pointer to the be-deleted rows)
              and them delete them in a seconds pass.
            • We already do this for secondary tables in our multi_delete code (see end of sql_delete.cc).
              Normally we do deletes directly in the first scan table, but in this case we shouldn't do that but instead
              treat the first table like any other table (mark first and then delete)
            • There is a flag delete_while_scanning that looks like it already provides this functionality, don't know why this
              doesn't work in this case.
            • It could be that the above delete is treated as a single delete table and this is why one gets the error. Detecting this case and changing this to multi-delete may solve this or at least part of this issue.
            monty Michael Widenius added a comment - Suggestion of how to implement this: Instead of deleting the rows, first mark them deleted (by storing a record pointer to the be-deleted rows) and them delete them in a seconds pass. We already do this for secondary tables in our multi_delete code (see end of sql_delete.cc). Normally we do deletes directly in the first scan table, but in this case we shouldn't do that but instead treat the first table like any other table (mark first and then delete) There is a flag delete_while_scanning that looks like it already provides this functionality, don't know why this doesn't work in this case. It could be that the above delete is treated as a single delete table and this is why one gets the error. Detecting this case and changing this to multi-delete may solve this or at least part of this issue.
            serg Sergei Golubchik added a comment -

            Please, report it as a new bug. This issue was fixed, and there is a test proving that one can indeed use the same table as a source and a delete target, at least in all tested cases.

            serg Sergei Golubchik added a comment - Please, report it as a new bug. This issue was fixed, and there is a test proving that one can indeed use the same table as a source and a delete target, at least in all tested cases.
            serg Sergei Golubchik added a comment -

            FYI, this feature was only about single-table DELETE, multi-delete isn't fixed yet

            serg Sergei Golubchik added a comment - FYI, this feature was only about single-table DELETE , multi-delete isn't fixed yet

            People

              serg Sergei Golubchik
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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