Bug #99561 Incompatible SQL parser changed in UNION vs FOR UPDATE
Submitted: 14 May 2020 6:38 Modified: 8 Jun 2020 16:48
Reporter: Tsubasa Tanaka (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0.20 OS:CentOS (7.7)
Assigned to: CPU Architecture:x86

[14 May 2020 6:38] Tsubasa Tanaka
Description:
In MySQL 8.0, UNION vs SELECT Lockng clause(FOR UPDATE, LOCK IN SHARE MODE) has been changed to "UNION with Locking clause have to wrap '(' and ')'"

https://github.com/mysql/mysql-server/blob/mysql-8.0.20/sql/sql_yacc.yy#L9202

MySQL 5.7 and earlier, "UNION with Locking clause without '(' and ')'" is allowed(See "How to repeat").

This is incompatible change and should be describe in document.

How to repeat:
$ mysql -h172.17.0.3 -e "SELECT @@version; SELECT 1 FOR UPDATE UNION SELECT 1 FOR UPDATE"
+-----------+
| @@version |
+-----------+
| 5.7.30    |
+-----------+
+---+
| 1 |
+---+
| 1 |
+---+

$ mysql -h172.17.0.2 -e "SELECT @@version; SELECT 1 FOR UPDATE UNION SELECT 1 FOR UPDATE"
+-----------+
| @@version |
+-----------+
| 8.0.20    |
+-----------+
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FOR UPDATE' at line 1

Add "(", ")" works fine.

$ mysql -h172.17.0.2 -e "SELECT @@version; (SELECT 1 FOR UPDATE) UNION (SELECT 1 FOR UPDATE)"
+-----------+
| @@version |
+-----------+
| 8.0.20    |
+-----------+
+---+
| 1 |
+---+
| 1 |
+---+

Suggested fix:
Add description for this incompatible change.

I think both of "What is new" ( https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html ) and "Union Clause" ( https://dev.mysql.com/doc/refman/8.0/en/union.html ) are better.
[14 May 2020 6:55] Tsubasa Tanaka
Fix "Version" (8.0.19 to 8.0.20, but 8.0.19 was already not allowed without '(' and ')'
[14 May 2020 6:59] MySQL Verification Team
Hello tanaka-San,

Thank you for the report and feedback.

regards,
Umesh
[14 May 2020 6:59] MySQL Verification Team
- 8.0.20

bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT @@version; SELECT 1 FOR UPDATE UNION SELECT 1 FOR UPDATE;
+-----------+
| @@version |
+-----------+
| 8.0.20    |
+-----------+
1 row in set (0.00 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FOR UPDATE' at line 1
mysql>
mysql> show errors;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                     |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FOR UPDATE' at line 1 |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                     |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION SELECT 1 FOR UPDATE' at line 1 |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- 5.7.20

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.30 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT @@version; SELECT 1 FOR UPDATE UNION SELECT 1 FOR UPDATE;
+-----------+
| @@version |
+-----------+
| 5.7.30    |
+-----------+
1 row in set (0.00 sec)

+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
[8 Jun 2020 16:48] Paul DuBois
Posted by developer:
 
This was part of a set of parser changes made in MySQL 8.0.0. See:
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-0.html

Will add that same information to the UNION section:
https://dev.mysql.com/doc/refman/8.0/en/union.html
[9 Mar 2021 13:38] MySQL Verification Team
Bug #102883 marked as duplicate of this one