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:
None 
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:39] Shiyao Wang
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.
[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.