| 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: | |
| 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: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
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.