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:
None 
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
Description:
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
`time_created';"
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.
https://github.com/mysql/mysql-connector-net/pull/59
[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.

regards,
Umesh
[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