| Bug #104919 | MySQLCollateExpression.cs is causing EFCore to throw InvalidOperationException | ||
|---|---|---|---|
| Submitted: | 13 Sep 2021 4:39 | Modified: | 5 Jun 17:58 |
| Reporter: | Shiyao Wang (OCA) | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / NET | Severity: | S1 (Critical) |
| Version: | MySql.EntityFrameworkCore 5.0.5 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | EFCore, EntityFrameworkCore, MySql.EntityFrameworkCore, MySQLCollateExpression | ||
[13 Sep 2021 4:41]
Shiyao Wang
1_SQLExpressionTree.png
Attachment: 1_SQLExpressionTree.png (image/png, text), 70.11 KiB.
[13 Sep 2021 4:41]
Shiyao Wang
2_BuildSQL.png
Attachment: 2_BuildSQL.png (image/png, text), 30.82 KiB.
[13 Sep 2021 4:41]
Shiyao Wang
3_UnknownExpression.png
Attachment: 3_UnknownExpression.png (image/png, text), 114.85 KiB.
[13 Sep 2021 4:42]
Shiyao Wang
4_ExceptionThrown.png
Attachment: 4_ExceptionThrown.png (image/png, text), 33.31 KiB.
[13 Sep 2021 4:43]
Shiyao Wang
5_SQLExpressionTreeRealSQL.png
Attachment: 5_SQLExpressionTreeRealSQL.png (image/png, text), 31.35 KiB.
[13 Sep 2021 4:45]
Shiyao Wang
SQL Statements that creates a sample table to repro this bug
Attachment: BugRepro-SQL.sql (application/octet-stream, text), 203 bytes.
[13 Sep 2021 4:46]
Shiyao Wang
Sample C# code that can repro this bug
Attachment: BugRepro-CSharp.cs (text/plain), 896 bytes.
[13 Sep 2021 5:59]
Shiyao Wang
I tried my suggested fix, it is working OK.
Here's the SQL from the log:
``` SQL
SELECT COUNT(*)
FROM `SampleTable` AS `s`
WHERE (`s`.`SampleColumn` LIKE CONCAT('a', '%')) AND (LEFT(`s`.`SampleColumn`, CHAR_LENGTH(CONVERT('a' USING utf8mb4) COLLATE utf8mb4_bin)) = CONVERT('a' USING utf8mb4) COLLATE utf8mb4_bin)
```
[13 Sep 2021 6:53]
Shiyao Wang
I submitted a pull request to fix this. https://github.com/mysql/mysql-connector-net/pull/42
[13 Sep 2021 7:33]
MySQL Verification Team
Hello! Thank you for the report and test case. regards, Umesh
[8 Mar 2024 1:56]
Shiyao Wang
This bug is so old. I believe it's not there any more.
[5 Jun 17:58]
Omar Chavez
Posted by developer: Could not reproduce with Connector/NET 9.3.0
Description: # Version Information MySQL: Docker mysql:5.7 (Docker host:Ubuntu20.04) Connector: MySql.Data 8.0.26 MySql.EntityFrameworkCore: 5.0.5 Microsoft.EntityFrameworkCore: 5.0.5/latest(5.0.9) (Both versions can repro this bug) .Net: .Net5 VisualStudio: VisualStudio 2019 Community OS: Windows 10 20H2 # Description ``` CSharp var result = dbContext.SampleTable.Count(c => c.SampleColumn.StartsWith("a")); ``` Above code cannot execute. I got InvalidOperationException saying EntityFrameworkCore cannot handle type MySQLCollateExpression. # My Triage 1. When EFCore is building the SqlExpression tree, MySQLCollateExpression got in.(Orange block in 1_SQLExpressionTree.png) 2. EFCore tries to build sql against the tree. (2_BuildSQL.png) 3. EFCore encounters an "unknown" expression type. (3_UnknownExpression.png) 4. InvalidOperationException is thrown. (4_ExceptionThrown.png) 5. I manually built the SQL from the expression tree, this can actually execute in MySQLWorkbench. (5_SqlExpressionTreeRealSQL.png) # Severity This bug can affect 90% of varchar/char related operation. So I consider this bug as S1(Critical). How to repeat: Create table SQL: ``` SQL CREATE TABLE `SampleTable` ( `SampleColumn` char(28) NOT NULL DEFAULT '', PRIMARY KEY (`SampleColumn`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `SampleTable` values ('abcd'),('wsad'); ``` Minium C# code to repro the bug ``` CSharp using System; using System.Linq; using Microsoft.EntityFrameworkCore; namespace BugRepro.SampleNamespace { record SampleTable([property: System.ComponentModel.DataAnnotations.Key] string SampleColumn); class WeefeDbContext : DbContext { public DbSet<SampleTable> SampleTables { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder.UseMySQL("server=mysql;uid=root;pwd=password;database=sample_db;charset=utf8mb4"); } class Program { static void Main(string[] args) { using (WeefeDbContext dbContext = new WeefeDbContext()) { var result = dbContext.SampleTables.Count(c => c.SampleColumn.StartsWith("a")); Console.WriteLine("resule evaluated! S: {0}", result); } } } } ``` Suggested fix: MySQLCollateExpression shall inherit SqlConstantExpression and override corresponding methods. So that in step 3 of MyTriage, EFCore would not encounter an SQLConstantExpression and you can generate correct sql. This shall fix this bug.