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

"No database selected" when using CTE in a subquery of DELETE statement

    XMLWordPrintable

Details

    Description

      The problem can be reproduced as follows

      create database x;
      use x;
       
      create table t (i int);
      insert into t values (1);
       
       
      delete from x.t
      where x.t.i in (
        with u (i) as (
          select 1
        )
        select i
        from u
      );
      

      There is an error in the DELETE statement:

      SQL Error [1046] [3D000]: (conn:77) No database selected
      

      Using the syntax in a SELECT statement works as expected:

      select *
      from x.t
      where x.t.i in (
        with u (i) as (
          select 1
        )
        select i
        from u
      );
      

      And so does replacing the CTE by a derived table:

      delete from x.t
      where x.t.i in (
        select i
        from (select 3 i) u
      );
      

      Attachments

        Issue Links

          Activity

            People

              igor Igor Babaev
              lukas.eder Lukas Eder
              Votes:
              2 Vote for this issue
              Watchers:
              6 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.