[MDEV-10060] Extra parentheses around CTE cause a syntax error Created: 2016-05-12  Updated: 2017-02-09  Resolved: 2017-02-09

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE, Parser
Affects Version/s: 10.2
Fix Version/s: 10.2.4

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Igor Babaev
Resolution: Not a Bug Votes: 0
Labels: 10.2-rc, CTE

Issue Links:
Relates

 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



 Comments   
Comment by Elena Stepanova [ 2016-09-07 ]

Both variants work in PostgreSQL.

Comment by Igor Babaev [ 2017-02-09 ]

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 |
 
+------------------------------------------------------+

Comment by Igor Babaev [ 2017-02-09 ]

See my explanation in the comments

Generated at Thu Feb 08 07:39:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.