Bug #104919 MySQLCollateExpression.cs is causing EFCore to throw InvalidOperationException
Submitted: 13 Sep 4:39 Modified: 13 Sep 7:33
Reporter: EnglishName EnglishFamilyName Email Updates:
Status: Verified 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 4:39] EnglishName EnglishFamilyName
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 4:41] EnglishName EnglishFamilyName
1_SQLExpressionTree.png

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

[13 Sep 4:41] EnglishName EnglishFamilyName
2_BuildSQL.png

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

[13 Sep 4:41] EnglishName EnglishFamilyName
3_UnknownExpression.png

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

[13 Sep 4:42] EnglishName EnglishFamilyName
4_ExceptionThrown.png

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

[13 Sep 4:43] EnglishName EnglishFamilyName
5_SQLExpressionTreeRealSQL.png

Attachment: 5_SQLExpressionTreeRealSQL.png (image/png, text), 31.35 KiB.

[13 Sep 4:45] EnglishName EnglishFamilyName
SQL Statements that creates a sample table to repro this bug

Attachment: BugRepro-SQL.sql (application/octet-stream, text), 203 bytes.

[13 Sep 4:46] EnglishName EnglishFamilyName
Sample C# code that can repro this bug

Attachment: BugRepro-CSharp.cs (text/plain), 896 bytes.

[13 Sep 5:59] EnglishName EnglishFamilyName
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 6:53] EnglishName EnglishFamilyName
I submitted a pull request to fix this.
https://github.com/mysql/mysql-connector-net/pull/42
[13 Sep 7:33] MySQL Verification Team
Hello!

Thank you for the report and test case.

regards,
Umesh