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

Subquery cannot see a CTE table

    XMLWordPrintable

Details

    Description

      I have a query that

      • defines two CTEs
      • refers to the second CTE from a query and a subquery

      I believe the query should work, but it fails with a "Table doesn't exist" error.

      Testcase:

      create table employees (
        name varchar(32),
        dept varchar(32),
        country varchar(8)
      );
       
      insert into employees 
      values 
      ('Sergei Golubchik', 'Development', 'DE'),
      ('Claudio Nanni', 'Support', 'ES'),
      ('Sergei Petrunia', 'Development', 'RU');
      

      with eng as 
      (
         select * from employees
         where dept in ('Development','Support')
      ),
      eu_eng  as 
      (
        select * from eng where country IN ('DE','ES','RU')
      )
      select * from eu_eng T1 
      where 
        not exists (select 1 from eu_eng T2 
                    where T2.country=T1.country
                    and T2.name <> T1.name);
      ERROR 1146 (42S02): Table 'j55.eng' doesn't exist
      

      Attachments

        Activity

          People

            igor Igor Babaev
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.