MySQL Bugs: #96677: "SELECT ... INTO var_name FOR UPDATE" not working in MySQL 8

Bug #96677	"SELECT ... INTO var_name FOR UPDATE" not working in MySQL 8
Submitted:	28 Aug 2019 3:15	Modified:	3 Dec 2019 17:57
Reporter:	Vinodh Krish	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	8.0, 8.0.11, 8.0.17	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression, SELECT, select into, variable

Description:
As per document - https://dev.mysql.com/doc/refman/8.0/en/select.html, the user defined variable could be mentioned in SELECT stmt (same as 5.7) as follows:

"SELECT
...
...
      | INTO var_name [, var_name]]
    [FOR {UPDATE | SHARE} [OF tbl_name [, tbl_name] ...] [NOWAIT | SKIP LOCKED] 
      | LOCK IN SHARE MODE]]"

When assigning variables like above, it is working as expected in 5.7 whereas failing in 8.0 versions. This needs code change everywhere when migrating from 5.7 to 8.0 versions. So it needs to be fixed

How to repeat:
-------------------
In 5.7 version test:
-------------------
mysql> select version();
+---------------+
| version()  |
+---------------+
| 5.7.26-29-log |
+---------------+
1 row in set (0.00 sec)

mysql> select id from t1 limit 1;
+------+
| id  |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql> select id from t1 limit 1 into @id_var for update;
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> select id from t1 limit 1 for update into @id_var;
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 'into @id_var' at line 1
mysql> 
++++++++++++++++++++++++++++++++++++++

--------------------
In 8.0 version test:
-------------------
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.15-6 |
+-----------+
1 row in set (0.00 sec)

mysql> select id from t1 limit 1;
+------+
| id  |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql> select id from t1 limit 1 into @id_var for update;
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 'for update' at line 1
mysql> 
mysql> 
mysql> select id from t1 limit 1 for update into @id_var;
Query OK, 1 row affected (0.00 sec)
+++++++++++++++++++++++++++++

Suggested fix:
Using the following code change:

mysql> select id from t1 limit 1 for update into @id_var;
Query OK, 1 row affected (0.00 sec)

Tested and confirmed the behaviour in MySQL Community 8.0.17 and Percona MySQL.

Sorry, Please note the change in suggested fix mentioned in initial report. The suggested fix should be the following query to work as mentioned in the document and 5.7:

select id from t1 limit 1 into @id_var for update;

Hello Vinodh,

Thank you for the report.
Observed that this behavior exists since 8.0.11.

regards,
Umesh

A possible fix (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug96677.diff (application/octet-stream, text), 12.64 KiB.

I added a patch which solves the mentioned issue, for all the queries I tested with.

The root cause seems to be WL#8083 (or something even before it, I didn't check git blame further), where the refactoring assumed that INTO is the last clause in a select, there's even a long comment explaining this.

As it's possible that somebody already relies on the new, undocumented order, my patch allows both versions.

Thank you, Zsolt Parragi for the contribution.

regards,
Umesh

Posted by developer:
 
Fixed in 8.0.19.

For SELECT statements, an INTO var_name clause prior to a locking
clause is legal but the parser rejected it.