MySQL Bugs: #75462: False syntax error when enclosing subquery in two parentheses

Bug #75462	False syntax error when enclosing subquery in two parentheses
Submitted:	9 Jan 2015 10:15	Modified:	9 Dec 2015 21:37
Reporter:	Ian Savell	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	6.2.4.12437	OS:	Windows (windows 7 and 8.1)
Assigned to:		CPU Architecture:	Any
Tags:	parentheses, subquery, syntax

Description:
The Workbench SQL editor reports a syntax error when enclosing a subquery with other elements in multiple parentheses. 

Example, reduced to the most trivial query that exhibits the problem:

UPDATE A SET A.A = ((SELECT B.B FROM B) + 1);

The first closing parenthesis is highlighted with error "Syntax error: missing 'closing parenthesis'". If you remove the outer parentheses or remove the subquery:

UPDATE A SET A.A = (SELECT B.B FROM B) + 1;
UPDATE A, B SET A.A = (B.B) + 1;

No syntax error is reported. However, all the above queries (the full versions in the correct environment) execute correctly.

Workaround: - Ignore the syntax error. But I've several times spent hours manually checking pairing in complex queries before concluding I can safely ignore the error. 

This bug appeared to come in around release 6 of workbench.

May be related to bug 74183 as both involve parentheses.

How to repeat:
Enter the above syntax in the SQL editor, a syntax error is flagged for the first version. If appropriate tables A and B exist the query can be executed.

Suggested fix:
Examine the parenthesis counting code in the syntax checker. It appears to be losing count after parsing a subquery. More complex variants of the above example generate increasing numbers of "missing closing parenthesis" errors which all disappear when the subquery is removed

Hello Ian Savell,

Thank you for the report.
Confirmed this issue on Win7 with Workbench 6.2.4.

Thanks,
Umesh

How to Repeat:

Try this query:

SELECT @l:=((SELECT 1) + 1 ) AS A;

No issues from CLI/SQL Editor after executing but SQL Editor wrongly point with syntax error.

mysql> SELECT @l:=((SELECT 1) + 1 ) AS A;
+---+
| A |
+---+
| 2 |
+---+
1 row in set (0.00 sec)

Screenshot..

Attachment: 75462.png (image/png, text), 33.55 KiB.

Posted by developer:
 
Fixed as of the upcoming MySQL Workbench 6.3.6 release, and here's the changelog entry:

Workbench would report SQL syntax errors for subqueries using multiple
parentheses.

Thank you for the bug report.