Bug #113443 SQL Keyword "RETURNING" not handled properly when updating data

• Submitted: 15 Dec 2023 19:42
• Modified: 21 May 22:03
• Reporter: Shiyao Wang (OCA)
• Status: Closed
• Category: Connector / NET
• Severity: S1 (Critical)
• Version: 7.0.10+MySQL8.2.0
• OS: Any
• CPU Architecture: Any
• Tags: EFCore

[15 Dec 2023 19:42] Shiyao Wang

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] Shiyao Wang

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] Shiyao Wang

A screenshot of the exception message of this bug

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

[15 Dec 2023 19:44] Shiyao Wang

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

[21 May 22:03] Philip Olson

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 9.0.0 release, and here's the proposed changelog entry from the documentation team:

The AppendReturningClause method is now overridden to prevent an
DatabaseGeneratedOption.Computed attribute annotation from adding an
unsupported RETURNING clause to the generated SQL.

Our thanks to Wang Shiyao for the contribution.

Thank you for the bug report.