MySQL Bugs: #88468: SELECT with wrong parenthesis is accepted

Bug #88468	SELECT with wrong parenthesis is accepted
Submitted:	13 Nov 2017 21:13	Modified:	14 Nov 2017 8:14
Reporter:	Federico Razzoli (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I don't expect a query like this to work. Developers should produce legal SQL syntax, or they should see an error. If they produce illegal syntax, probably that they have a bug in code which generates the query, or they think that their illegal syntax somehow changes the meaning of the query.

mysql> create table users (id int, deleted int, user_name char(50));
Query OK, 0 rows affected (0.28 sec)

mysql> (SELECT users.id AS __id FROM users WHERE deleted = false) ORDER BY user_name LIMIT 2790060, 60;
Empty set (0.00 sec)

How to repeat:
.

Suggested fix:
Deprecate illegal use of parenthesis and remove it in a future version.

If you don't agree that this is desirable, then I suggest to do it adding a new sql_mode like 'STRICT_SYNTAX'.

Note that I only reported a real case, but I didn't test if similar illegal syntaxes are also accepted.

If the bug report only refers about parenthesis of part(select ...where..) disregarding the other particularities of MySQL syntax (i.e limit) below an example how Oracle 12C Databases behaves:

C:\>sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 13 19:57:28 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: system
Enter password:
Last Successful login time: Mon Nov 13 2017 19:36:21 -02:00

Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

SQL> (SELECT users.id AS xid FROM users WHERE deleted = 0) ORDER BY user_name;

no rows selected

SQL>

This is actually standard compliant syntax.

The statement

(SELECT users.id AS __id FROM users WHERE deleted = false) ORDER BY user_name LIMIT 2790060, 60;

complies to the syntax rules:

<query expression> ::= <query expression body> <order by clause>  <result offset clause>
 
<query expression body> ::=   <query term>

<query term> ::=  <query primary>

<query primary> ::=  <left paren> <query expression body> <right paren>

...

<query primary> ::=<simple table>

<simple table> ::=  <query specification>

<query specification> ::= SELECT [ <set quantifier> ] <select list> <table expression>

Irrelevant syntax rules have been removed for brevity, and we ignore the non-standard LIMIT clause.

So, it's weird but standard compliant. Thanks for clarification.