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

Extra parentheses around CTE cause a syntax error

    XMLWordPrintable

    Details

      Description

      I'm not quite sure what SQL standard thinks about it, but it looks somewhat inconsistent.

      Both of these work:

      MariaDB [test]> select 1;
      +---+
      | 1 |
      +---+
      | 1 |
      +---+
      1 row in set (0.00 sec)
       
      MariaDB [test]> ( select 1 );
      +---+
      | 1 |
      +---+
      | 1 |
      +---+
      1 row in set (0.00 sec)
      

      But here the second one does not work:

      MariaDB [test]> with x as ( select 1 ) select * from x;
      +---+
      | 1 |
      +---+
      | 1 |
      +---+
      1 row in set (0.00 sec)
       
      MariaDB [test]> ( with x as ( select 1 ) select * from x );
      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 'x as ( select 1 ) select * from x )' at line 1
      

      Another example. Both of these work:

      MariaDB [test]> select exists ( select 1 );
      +---------------------+
      | exists ( select 1 ) |
      +---------------------+
      |                   1 |
      +---------------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> select exists ( ( select 1 ) );
      +-------------------------+
      | exists ( ( select 1 ) ) |
      +-------------------------+
      |                       1 |
      +-------------------------+
      1 row in set (0.00 sec)
      

      But here the second one does not work:

      MariaDB [test]> select exists ( with x as ( select 1 ) select * from x );
      +---------------------------------------------------+
      | exists ( with x as ( select 1 ) select * from x ) |
      +---------------------------------------------------+
      |                                                 1 |
      +---------------------------------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> select exists ( ( with x as ( select 1 ) select * from x ) );
      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 'x as ( select 1 ) select * from x ) )' at line 1
      

        Attachments

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.