MySQL Bugs: #70722: ' The method or operation is not implemented. ' when using LINQ with OrderBy.

Bug #70722	' The method or operation is not implemented. ' when using LINQ with OrderBy.
Submitted:	24 Oct 2013 23:43	Modified:	21 Oct 2014 21:49
Reporter:	Sam Kelleher	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.8.3	OS:	Windows
Assigned to:	Roberto Ezequiel Garcia Ballesteros	CPU Architecture:	Any

Description:
Windows 8.1, Connector 6.7.4 (have tried 6.8.0 and 6.6.6), Entity Framework 5.0, .NET 4.5 project.

Using the included statement will give a NotImplementedException when a LINQ statement is used to see if a nullable DateTime property has a value greater than a given value.  Checking if the value is not null does nothing.  The query works if the datetime is removed from the where clause.

Stack Trace:

[NotImplementedException: The method or operation is not implemented.]
   MySql.Data.Entity.SelectStatement.Accept(SqlFragmentVisitor visitor) +36
   MySql.Data.Entity.ExistsFragment.Accept(SqlFragmentVisitor visitor) +30
   MySql.Data.Entity.BinaryFragment.Accept(SqlFragmentVisitor visitor) +48
   MySql.Data.Entity.SqlGenerator.FuseSelectWithInnerSelect(SelectStatement outer, SelectStatement inner) +465
   MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type) +152
   MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type) +19
   MySql.Data.Entity.SelectGenerator.Visit(DbLimitExpression expression) +34
   MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type) +52
   MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type) +19
   MySql.Data.Entity.SelectGenerator.Visit(DbProjectExpression expression) +45
   MySql.Data.Entity.SelectGenerator.GenerateSQL(DbCommandTree tree) +72
   MySql.Data.MySqlClient.MySqlProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree) +471
   System.Data.EntityClient.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree) +668

[EntityCommandCompilationException: An error occurred while preparing the command definition. See the inner exception for details.]
   System.Data.EntityClient.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree) +2348
   System.Data.EntityClient.EntityProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree) +152
   System.Data.Objects.Internal.ObjectQueryExecutionPlan.Prepare(ObjectContext context, DbQueryCommandTree tree, Type elementType, MergeOption mergeOption, Span span, ReadOnlyCollection`1 compiledQueryParameters, AliasGenerator aliasGenerator) +1030
   System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption) +1229
   System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) +254
   System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() +51
   System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +446
   System.Linq.Enumerable.ToList(IEnumerable`1 source) +80

How to repeat:

var expireAfter = DateTime.UtcNow();
var companyLogoList = db.MyObjects.Where(t => t.IsEnabled == true && t.ChildItems.Any(j => j.IsEnabled == true && j.ExpiryDate > expireAfter));

Hi Sam,

I wasn't able to reproduce this bug. 

Could you please provide the following:

- MySql server version
- Code: model, context class, etc.

Thank you.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I created a sample project reading a simple child model with one field using connector 6.8.3 and the sample works.

Rather cryptically, in my project, the exact same code does not work.  So yes there will be something in the project or related models that is causing this problem, and the exception being thrown is hiding the underlying cause; as I am unable to set a breakpoint.

Curiously, if I just use connector 6.6.5 in the broken project, the application works, replace it with anything newer and it breaks.

So I am trying to debug the project to find out what changed from >6.6.5 that now throws the error rather than just work.

Scrap that, on further testing I can not get the error to occour just by using an OrderBy statement.

To get it to happen create a select query that has a nullable datetime field in a child .Any() clause with an .OrderBy().

Breaks:

  var items = db.DataTableItems.Where(t => t.Id > 0 && t.ChildDataTableItems.Any(q => q.ProblemField <= now)).OrderBy(t => t.Id);

These work:

  var items = db.DataTableItems.Where(t => t.Id > 0 && t.ChildDataTableItems.Any(q => q.ProblemField <= now));

  var items = db.DataTableItems.Where(t => t.Id > 0 && t.ChildDataTableItems.Any()).OrderBy(t => t.Id);

I have a sample project which I'll upload.

Sample console app using EF6.0.2 and connector 6.8.3 via nuget

Attachment: Sample Project.zip (application/zip, text), 459.78 KiB.

I am also facing this problem. I am using EF 5 and .Net Connector 6.8.3. To provide more data, I thought to include following queries that I was trying to perform:
Step 1: query.Where(u => u.UserRoles.Any(ur => InputRoleTypeIds.Contains(ur.RoleTypeId)&& (ur.ValidUntil == null || ur.ValidUntil > utcTimeNow)));
Step 2: query = query.OrderBy(user => user.Id);

If I don't use (ur.ValidUntil == null || ur.ValidUntil > utcTimeNow))) in Step 1 the query works. 
If I don't do OrderBy in Step 2, the query works. But, the problem is that I need both. 

Looking forward for the fix for this. Please let me know if you need any further information.

Raghav

I have a very similar problem. The following query works:

this.DB.transactions
    .Where(t => t.transaction_details.Select(s => s.sku).Where(s => s == this.SKU).Any())
    .Count()

This very similar query blows up in my face:

this.DB.transactions
    .Where(t => t.transaction_details.Select(s => s.sku).Where(s => s == this.SKU).Any())
    .OrderByDescending(t => t.transaction_time)
    .Skip(page * pageSize)
    .Take(pageSize)
    .ToList()

This has happened to me in many projects and I eagerly look forward to a fix for this.

Whoops. The Expression inside the Where in my previous comment should be:

t => t.transaction_details.Where(d => d.sku == this.SKU).Any();

this works fine:

var  list = query.ToList<PostListBEC>()
             .OrderByDescending(o => o.DateInclude)
             .ToList();

@Rene Bizelli:

This bug occurs when a query is produced containing an Any inside a Where. Your example will never trigger this bug (and will therefore always work) because:

a) It is operating on an in-memory set and never invokes Connector/Net (because of your ToList before the OrderBy)
b) Even if it were creating a query, there is no problem executing OrderBy queries normally. Its when an OrderBy is called on a query with an Any inside a Where.

I also get the same error message, when I use an Order By + Take together with a Where that contains an Any.

I examined the source code for the generator.
The tree given by EF to MySql's generator is something like the following if it's written in sql:
Select ... From (Select ... From Table As ExtentX Where Exists (Select 1 From OtherTable Where ...)) As ProjectX Order By ... Limit ...

The guys at the MySql team found out that doing this kind of unnecessary projection is very bad for MySql performance (at least in older versions of MySql). So in the connector, these kind of queries are identified and post-processed by combining (called "FuseSelectWithInnerSelect" in the source code) the two select statements into one, like this:
Select ... From Table As ExtentX Where Exists (Select 1 From OtherTable Where ...) Order By ... Limit ...

When doing this, references to tables and subtables (ExtentX and ProjectX) must be combined and one name must be used. So a visitor visits the generated parts of the query and replaces each occurrence of ProjectX with Extent1. Now the bug is in Source/MySql.Data.Entity/Statements/SelectStatement.cs:

internal override void Accept(SqlFragmentVisitor visitor)
{
throw new System.NotImplementedException();
}

For some reason, they didn't implement the visitor that visits Exists (Select ...) inside a subquery that are to be "fused" with an outer query.

Suggested fix: implement this method by simply calling the visitor's accept method on each part of the select statement: Where, Limit, GroupBy, OrderBy. Since (sadly) MySql does not support correlated subqueries in a from clause, it doesn't need to visit the From part (until this gets supported by MySql).

Forgot one thing in the comment above: the visitor should also visit the input for projected columns of the Select statement, since they can reference columns of the (most) outer table.

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 6.7.6 / 6.8.4 / 6.9.5 releases, and here's the changelog entry:

A SELECT query that had a nullable DATETIME field in a child ".Any()"
clause with an ".OrderBy()" would fail and emit a
"NotImplementedException" exception.

Thank you for the bug report.