MySQL Bugs: #77543: Unknown column with orderby clause in subquery

Bug #77543	Unknown column with orderby clause in subquery
Submitted:	29 Jun 2015 13:43	Modified:	22 Nov 2022 15:56
Reporter:	Luca Cavagnoli	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	6.9.6	OS:	Windows (Windows 7 Enterprise)
Assigned to:		CPU Architecture:	Any

Description:
On invalid query string is generated from a LINQ expression that has an orderby clause in a subquery.

Full error:

System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> MySql.Data.MySqlClient.MySqlException: Unknown column 'Project1.C1' in 'field list'

The error occurs with every combynation EF 6.1.1, 6.1.2, 6.1.3 and Connector .NET 6.9.5, 6.9.6. I didn't test it against other versions.

How to repeat:
Run the following code:

using(var cxt = new MyContext())
{
 var query = from customer in cxt.Set<Customer>()
             let firstOrderTimestamp =
                 (from order in customer.Orders
                  orderby order.Timestamp
                  select (DateTime?)order.Timestamp).FirstOrDefault()
             select firstOrderTimestamp;
 
 query.ToArray();
}

The data model is: Customer 1-----* Order.

I can attache the full Visual studio project.

Sample Test Project to replicate the issue

Attachment: mysql-bug-data-77543.zip (application/zip, text), 2.31 MiB.

Hello Luca Cavagnoli,

Thank you for the bug report.
Verified this behavior on Visual Studio 2013 (C#.Net) with described versions.

Thanks,
Chiranjeevi.

Tables creation scripts:

CREATE TABLE Customers(
	Id bigint NOT NULL AUTO_INCREMENT UNIQUE,
    PRIMARY KEY (Id)) ENGINE=INNODB;

CREATE TABLE Orders(
	Id bigint NOT NULL AUTO_INCREMENT UNIQUE, 
	CustomerId bigint NOT NULL, 
	Timestamp datetime NOT NULL,
    PRIMARY KEY (Id),
    CONSTRAINT FK_OperatoreStorico
    FOREIGN KEY (CustomerId)
    REFERENCES Customers (Id)) ENGINE=INNODB;
    
    
Generated Query:

SELECT
`Apply1`.`C1`
FROM (SELECT
`Extent1`.`Id`, 
(SELECT
`Project1`.`C1`
FROM `Orders` AS `Project1`
 WHERE `Extent1`.`Id` = `Project1`.`CustomerId`
 ORDER BY 
`Project1`.`Timestamp` ASC LIMIT 1) AS `C1`, 
(SELECT
`Project1`.`Timestamp`
FROM `Orders` AS `Project1`
 WHERE `Extent1`.`Id` = `Project1`.`CustomerId`
 ORDER BY 
`Project1`.`Timestamp` ASC LIMIT 1) AS `Timestamp`
FROM `Customers` AS `Extent1`) AS `Apply1`;

Upon further inspection, the bug actually seems to occur when orderby is followed by FirstOrDefault.

As a matter of fact, the following query doesn't throw exception:

using(var cxt = new MyContext())
{
 var query = from customer in cxt.Set<Customer>()
             let ordersTimestamps =
                 (from order in customer.Orders
                  orderby order.Timestamp
                  select (DateTime?)order.Timestamp)
             select ordersTimestamps;
 query.ToArray();
}

This bug seems related to this other issue:

http://bugs.mysql.com/bug.php?id=41427

In MySQL you can corrlate only one level deep. However, Connector .NET generates queryes that correlate two levels deep.

Posted by developer:
 
This is no longer a bug. Verified using the latest version of MySQL Connector/NET 8.0.31 with the EF provider v6.4.4.