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: | |
| Category: | Connector / NET | Severity: | S1 (Critical) |
| Version: | 6.9.6 | OS: | Windows (Windows 7 Enterprise) |
| Assigned to: | CPU Architecture: | Any |
[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.
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.