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

[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.
[22 Nov 2022 15:56] Daniel Valdez
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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.057 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.