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

            • Assignee:
              igor Igor Babaev
              Reporter:
              alvinr Alvin Richards (Inactive)
            • Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: