Bug #104919 MySQLCollateExpression.cs is causing EFCore to throw InvalidOperationException
| Submitted: | 13 Sep 2021 4:39 | Modified: | 5 Jun 2025 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 2025 17:58]
Omar Chavez
Posted by developer: Could not reproduce with Connector/NET 9.3.0
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.046 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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.