Pomelo.entityframeworkcore.mysql: Invalid sql statement is generated by the library when integrated with odata / MySQL 5.7+ returns NULL for constant values in LEFT JOINs if statement contains ORDER BY

[OlehGaras]

Steps to reproduce

Steps to reproduce were completely described in the separate issue created for OData/WebApi here: https://github.com/OData/WebApi/issues/2124

Please find the example where the error can be reproduced here: https://github.com/huiali/odata-sample

The issue

OData URL-->GET
https://localhost:5002/oq/A?$expand=B($expand=C)-->
ERROR --> Value cannot be null. (Parameter 'key')

Using OData, expand inside another expand, or filter inside expand the error appears.

Investigations in the separate thread (https://github.com/OData/WebApi/issues/2124) showed that the issue is with the sql statement that is generated by the Pomelo.EntityFrameworkCore.MySql lib when OData is executing expand inside another expand

Further technical details

Pomelo.EntityFrameworkCore.MySql version: 3.2.4
Microsoft.AspNetCore.App version: 3.1
Microsoft.AspNetCore.OData version: 7.5.0

[mguinness]

Investigations in the separate thread (OData/WebApi#2124) showed that the issue is with the sql statement that is generated by the Pomelo.EntityFrameworkCore.MySql lib when OData is executing expand inside another expand

That is contrary to https://github.com/OData/WebApi/issues/2124#issuecomment-690886665 which states the problem is with MySQL engine and not the Pomelo provider (same issue w/ Oracle provider).

Also from same thread, this is only an issue with MySQL 8.0 and not MariaDB 10. You didn't state what engine and version you are using so please add that above (as requested in new issue template).

I would suggest looking at MySQL 8.0 Release Notes for fixes to known issues and also search MySQL Bug System for reported issues. MySQL 8.0.22 has some serious regression bugs (see #1207, #1210 and #1216) so you may need to revert to 8.0.21 until Oracle fixes these bugs.

[lauxjpn]

@OlehGaras I posted my answer over on the original https://github.com/OData/WebApi/issues/2124 issue:

I can confirm that this is a MySQL database engine bug, that I reported as [REGRESSION] Order by and constant from left joined table result in NULL value to Oracle over a year ago.

In my personal experience, the MySQL dev team works very slow when it comes to bugs, even critical ones like this. So I would not get my hopes up, that this will be fixed anytime soon. Of course it might help to post a comment on the bug-tracker issue, so they are aware that this is still a huge and critical issue.

Maybe MySql team fixed it in the higher versions.

@woojunfeng Unfortunately, they did not.

But what about thousands of people using mysql (buggy version ) who wants to use oData ?

@BinduShreeU You have currently 3 options:

• Use MariaDB instead of MySQL.
• Use MySQL 6, which does not contain the bug, but will reach its end-of-life in about two weeks on 2021-02-05.
• Do not order the result server side. Instead, query the result unordered and order it client side.

If a CAST is applied to the two Guid literals in the inner query, the expected result is returned, i.e.,

@gathogojr That is correct. Explicitly casting all constant values does successfully work around this bug.

I will take a look at implementing this workaround in Pomelo.EntityFrameworkCore.MySql for MySQL 5.7+.

[OlehGaras]

@mguinness , @lauxjpn
thanks for your help,
I use MySQL 5.7.12, so I hope the workaround will fix my issue.

[lauxjpn]

We implemented a workaround for this MySQL bug in Pomelo 5.0.0 with #1294 and in Pomelo 3.2.5 with 30295d1.
Both are already available in our nightly builds as _prereleases_.

@OlehGaras If you are currently using Pomelo 3.2.4, then the latest nightly build containing the fix is 3.2.5-servicing.21065.4.

[OlehGaras]

@lauxjpn , I used the package 3.2.5-servicing.21065.4 as you suggested and checked the fix you applied.
I found out that it helps but not completely, I got the same error as previously:
ERROR --> Value cannot be null. (Parameter 'key').

Except constantExpression the same problem exists with the parameterExpression in that case, I think they have to be explicitly casted too.
I cloned your repo, extended your fix, build the package locally and used it inside my solution and it started to work perfectly.

Here is the PR with my changes
https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/pull/1295/commits

Please take a look.

Thanks