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

          Ascending order - Click to sort in descending order
          alvinr Alvin Richards (Inactive) created issue -
          alvinr Alvin Richards (Inactive) made changes -
          Field Original Value New Value
          Description h1. 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
          {code}
          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
          {code}

          h1. Reproduce

          Select works as expected with CTE
          {code}
          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)
          {code}

          View get created without error with the same CTE
          {code}
          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)
          {code}

          Query fails against the view
          {code}
          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
          {code}

          h1. 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.

          h1. Workaround
          None at present           		h1. 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
          {code}
          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
          {code}

          h1. Reproduce

          Select works as expected with CTE
          {code}
          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)
          {code}

          View get created without error with the same CTE
          {code}
          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)
          {code}

          Query fails against the view
          {code}
          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
          {code}

          h1. 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.

          h1. Workaround
          None at present

          h1. Found In
          10.2.4
          alvinr Alvin Richards (Inactive) made changes -
          Issue Type Task [ 3 ] Bug [ 1 ]
          alvinr Alvin Richards (Inactive) made changes -
          Affects Version/s 10.2.4 [ 22116 ]
          alvinr Alvin Richards (Inactive) made changes -
          Description h1. 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
          {code}
          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
          {code}

          h1. Reproduce

          Select works as expected with CTE
          {code}
          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)
          {code}

          View get created without error with the same CTE
          {code}
          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)
          {code}

          Query fails against the view
          {code}
          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
          {code}

          h1. 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.

          h1. Workaround
          None at present

          h1. Found In
          10.2.4           		h1. 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
          {code}
          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
          {code}

          h1. Reproduce

          Select works as expected with CTE
          {code}
          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)
          {code}

          View get created without error with the same CTE
          {code}
          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)
          {code}

          Query fails against the view
          {code}
          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
          {code}

          h1. 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.

          h1. Workaround
          None at present
          psergei Sergei Petrunia made changes -
          Assignee Sergei Petrunia [ psergey ]
          psergei Sergei Petrunia made changes -
          Assignee Sergei Petrunia [ psergey ] Igor Babaev [ igor ]
          igor Igor Babaev (Inactive) made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          igor Igor Babaev (Inactive) made changes -
          Fix Version/s 10.2.5 [ 22117 ]
          Resolution Fixed [ 1 ]
          Status In Progress [ 3 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 79853 ] MariaDB v4 [ 151775 ]
          mariadb-jira-automation Jira Automation (IT) made changes -
          Zendesk Related Tickets 106862

          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.