MySQL Bugs: #85854: WHERE condition on columns of type string

Bug #85854	WHERE condition on columns of type string
Submitted:	7 Apr 2017 10:35	Modified:	7 Apr 2017 11:53
Reporter:	Maxime Beaudry	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.10.1-beta	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I am using MySql.Data.EntityFrameworkCore version 6.10.1-beta to write the following LINQ query:

            var data = await _context
                .Assets
                .Where(a => string.Compare(a.Name, "b", StringComparison.OrdinalIgnoreCase) > 0)
                .Take(2)
                .ToListAsync();

where the 'Name' property is a string. In other words, I want to get the next 2 database entries that have a 'Name' property greater than 'b'.

The code works as expected from the outside but when I look at the generated SQL, I see the following:

SELECT `a`.`F_ASSET_PK`, `a`.`F_ASSET_TYPE`, `a`.`F_CREATION_TIME`, `a`.`F_ID`, `a`.`F_IS_SOFT_DELETED`, `a`.`F_LAST_MODIFIED_TIME`, `a`.`F_LIBRARY_ID`, `a`.`F_NAME`, `a`.`F_OWNER_ID`, `a`.`F_TENANT_ID`
FROM `accounting`.`T_ASSET` AS `a`

As you can see my LIMIT and WHERE clauses are not in the generated SQL. They are applied in memory causing sub-optimal performances.

If I remove my 'Where' in the C#, then the query is properly generated with the LIMIT. Therefore, as soon as I try to compare strings in my query through 'string.Compare(a.Name, "b", StringComparison.OrdinalIgnoreCase) > 0', the MySql driver gets confused and he fails to generate the proper SQL.

Is there another syntax that I could use to compare strings that would result in the proper SQL?

How to repeat:
See above

Hello Maxime,

Thank you for the bug report.
Could you please provide repeatable test case (steps/sample project, code, create table statements etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.

I found the solution:

I have to use 'string.Compare(a.Name, "b") > 0' instead of 'string.Compare(a.Name, "b", StringComparison.OrdinalIgnoreCase) > 0'.

Hello Maxime,

Thank you for the feedback.
I'm glad that you found a solution for your problem and let us know if you are still having the issue with complete repeatable steps.

Thanks,
Chiranjeevi.