Bug #104919 MySQLCollateExpression.cs is causing EFCore to throw InvalidOperationException
Submitted: 13 Sep 2021 4:39 Modified: 8 Mar 1:56
Reporter: Shiyao Wang (OCA) Email Updates:
Status: Closed 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:39] Shiyao Wang
# 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`)
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

Attachment: 1_SQLExpressionTree.png (image/png, text), 70.11 KiB.

[13 Sep 2021 4:41] Shiyao Wang

Attachment: 2_BuildSQL.png (image/png, text), 30.82 KiB.

[13 Sep 2021 4:41] Shiyao Wang

Attachment: 3_UnknownExpression.png (image/png, text), 114.85 KiB.

[13 Sep 2021 4:42] Shiyao Wang

Attachment: 4_ExceptionThrown.png (image/png, text), 33.31 KiB.

[13 Sep 2021 4:43] Shiyao Wang

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
      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.
[13 Sep 2021 7:33] MySQL Verification Team

Thank you for the report and test case.

[8 Mar 1:56] Shiyao Wang
This bug is so old. I believe it's not there any more.