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

CTE alias does not work as part of view expression

Details

    Description

      Problem

      CTE alias do not appear to work as part of a View definition. The view will be created without error, but selecting from the view results in

      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'alias2 as (select 2 AS `two`), select `alias1`.`one` AS `one`,`alias2`.`two` AS ' at line 1

      Reproduce

      Select works as expected with CTE

      with alias1 as (select 1 as one), alias2 as (select 2 as two) select one, two from alias1, alias2;
      		 
      +-----+-----+
      		 
      | one | two |
      		 
      +-----+-----+
      		 
      |   1 |   2 |
      		 
      +-----+-----+
      		 
      1 row in set (0.00 sec)

      View get created without error with the same CTE

      create view vw as with alias1 as (select 1 as one), alias2 as (select 2 as two) select one, two from alias1, alias2;
      		 
      Query OK, 0 rows affected (0.01 sec)

      Query fails against the view

      select * from vw; 
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'alias2 as (select 2 AS `two`), select `alias1`.`one` AS `one`,`alias2`.`two` AS ' at line 1

      Solution

      If this is a valid construct, then the query should execute as expected (per the un-view version).

      If this is an invalid construct, then the create view should fail.

      Workaround

      None at present

      Attachments

        Activity

          People

            igor Igor Babaev (Inactive)
            alvinr Alvin Richards (Inactive)
            Votes:
            1 Vote for this issue
            Watchers:
            4 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.