Bug #113443 SQL Keyword "RETURNING" not handled properly when updating data
Submitted: 15 Dec 2023 19:42 Modified: 17 Dec 2023 12:45
Reporter: Wang Shiyao (OCA) Email Updates:
Status: Verified Impact on me:
Category:Connector / NET Severity:S1 (Critical)
Version:7.0.10+MySQL8.2.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: EFCore

[15 Dec 2023 19:42] Wang Shiyao
On Mar 17, 2022, a commit was made to EFCore (https://github.com/dotnet/efcore/commit/b906ca3dd58dc89952960261e7d6eea766d4a7b4), introducing a new method called `AppendReturningClause`.
If a data class, has a property that is annotated with '[DatabaseGenerated(DatabaseGeneratedOption.Computed)]' attribute, when updating data in this table, the `AppendReturningClause` method will add a `RETURNING` clause to the generated SQL.
MySQL does not support the `RETURNING` clause, and MySQL.EntityFrameworkCore did not override this method, causing the update operation to fail in some scenarios.

How to repeat:
1. Create a table with a column named 'time_created' of type 'TIMESTAMP' and default value of 'CURRENT_TIMESTAMP'
2. Create a C# project with EFCore and MySQL.EntityframeworkCore.
3. Annotate C# data class with '[DatabaseGenerated(DatabaseGeneratedOption.Computed)]' attribute on the property of the above 'time_created' column.
4. Update data in this table with EFCore.
5. You will see an exception saying "MySqlException: 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 'RETURNING
6. If you dig into this exception, the SQL generated by EFCore is like this: "UPDATE `sample_table` SET `name` = @p0
WHERE `id` = @p1
RETURNING `time_created`"

Suggested fix:
Override the method `AppendReturningClause` and generate a correct SQL fragment.
I have already created a GitHub pull request for this bug.
[15 Dec 2023 19:43] Wang Shiyao
Source file of a C# project that can repro this bug.

Attachment: WSY.MySQL.BugRepro.souce.zip (application/x-zip-compressed, text), 4.82 KiB.

[15 Dec 2023 19:43] Wang Shiyao
A screenshot of the exception message of this bug

Attachment: MySQL-BugRepro.png (image/png, text), 265.27 KiB.

[15 Dec 2023 19:44] Wang Shiyao
The sql generated by EFCore when the bug is triggered.

Attachment: SqlUpdateStatementGeneratedByEFCore.sql (application/octet-stream, text), 98 bytes.

[17 Dec 2023 12:45] MySQL Verification Team
Hello Wang Shiyao,

Thank you for the report and test case.
Verified as described.

[22 Dec 2023 17:35] OCA Admin
Contribution submitted via Github - Fixed bug with "RETURNING" clause of EFCore (Oracle Bug #113443) 
(*) Contribution by Wang Shiyao (Github wsy, mysql-connector-net/pull/59#issuecomment-1858409388): "I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it."

Contribution: git_patch_1646528056.txt (text/plain), 1.21 KiB.

[2 Feb 18:38] Quentin Snow
Also affecting me on 8.0.0+MySQL8.0.32