Bug #77543 Unknown column with orderby clause in subquery
Submitted: 29 Jun 2015 13:43 Modified: 2 Jul 2015 14:20
Reporter: Luca Cavagnoli Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.9.6 OS:Microsoft Windows (Windows 7 Enterprise)
Assigned to: CPU Architecture:Any

[29 Jun 2015 13:43] Luca Cavagnoli
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.
[29 Jun 2015 13:48] Luca Cavagnoli
Sample Test Project to replicate the issue

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

[30 Jun 2015 8:22] Chiranjeevi Battula
Hello Luca Cavagnoli,

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

Thanks,
Chiranjeevi.
[30 Jun 2015 8:23] Chiranjeevi Battula
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`;
[1 Jul 2015 9:00] Luca Cavagnoli
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();
}
[2 Jul 2015 14:20] Luca Cavagnoli
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.