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

Extra parentheses around CTE cause a syntax error

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

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova added a comment -

          Both variants work in PostgreSQL.

          elenst Elena Stepanova added a comment - Both variants work in PostgreSQL.
          igor Igor Babaev (Inactive) added a comment -

          The SQL Standard says;

          <query expression> ::= [ <with clause> ] <query expression body>
          		 
           
          		 
          subquery ::= ...
          		 
                              | '(' <query expression> ')'

          So the following is not valid:

          select exists ( ( with x as ( select 1 ) select * from x ) );

          while this is ok

          select exists ( with x as ( select 1 ) (select * from x ) );
          		 
          MariaDB [test]> select exists ( with x as ( select 1 ) (select * from x ) );
          		 
          +------------------------------------------------------+
          		 
          | exists ( with x as ( select 1 ) (select * from x ) ) |
          		 
          +------------------------------------------------------+
          		 
          |                                                    1 |
          		 
          +------------------------------------------------------+

          igor Igor Babaev (Inactive) added a comment - The SQL Standard says; <query expression> ::= [ <with clause> ] <query expression body>   subquery ::= ... | '(' <query expression> ')' So the following is not valid: select exists ( ( with x as ( select 1 ) select * from x ) ); while this is ok select exists ( with x as ( select 1 ) (select * from x ) ); MariaDB [test]> select exists ( with x as ( select 1 ) (select * from x ) ); +------------------------------------------------------+ | exists ( with x as ( select 1 ) (select * from x ) ) | +------------------------------------------------------+ | 1 | +------------------------------------------------------+
          igor Igor Babaev (Inactive) added a comment -

          See my explanation in the comments

          igor Igor Babaev (Inactive) added a comment - See my explanation in the comments

          People

            igor Igor Babaev (Inactive)
            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.