Bug #73549 Generated Sql does not contain ORDER BY statement whose is requested by LINQ
Submitted: 12 Aug 2014 11:54 Modified: 16 Oct 2014 6:49
Reporter: Vladimir Krajcovic Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.8.3 OS:Windows
Assigned to: Fernando Gonzalez.Sanchez CPU Architecture:Any
Tags: orderby

[12 Aug 2014 11:54] Vladimir Krajcovic
Description:
Generated SQL is missing ORDER BY statement. I'm using LINQ to ENTITIES let statement.

How to repeat:
Here is linq
 var xq = from p in db.Processes
                    let currentAr = db.ActivityRuns.OrderByDescending(ar => ar.Id).Where(ar => ar.ProcessId == p.Id).FirstOrDefault()
                    join c in db.Components on p.ComponentId equals c.Id
                    orderby p.Id descending
                    select new
                    {
                        currentAr.ComponentVersion.Component.Name
                    };
            var b = xq.ToList();

Generated sql is

SELECT
`Apply1`.`Id`, 
`Extent5`.`Name`
FROM (SELECT
`Extent1`.`Id`, 
`Extent1`.`ComponentId`, 
`Extent1`.`ProcessStateId`, 
`Extent1`.`PriorityId`, 
`Extent1`.`StartedOn`, 
`Extent1`.`FinishedOn`, 
`Extent1`.`DontRunBefore`, 
`Extent1`.`LockGuid`, 
`Extent1`.`HasDeletedData`, 
(SELECT
`Project1`.`Id`
FROM `ActivityRun` AS `Project1`
 WHERE `Project1`.`ProcessId` = `Extent1`.`Id` LIMIT 1) AS `ID1`, 
(SELECT
`Project1`.`ComponentVersionId`
FROM `ActivityRun` AS `Project1`
 WHERE `Project1`.`ProcessId` = `Extent1`.`Id` LIMIT 1) AS `ComponentVersionId`
FROM `Process` AS `Extent1`) AS `Apply1` INNER JOIN `Component` AS `Extent3` ON `Apply1`.`ComponentId` = `Extent3`.`Id` LEFT OUTER JOIN `ComponentVersion` AS `Extent4` ON `Apply1`.`ComponentVersionId` = `Extent4`.`Id` LEFT OUTER JOIN `Component` AS `Extent5` ON `Extent4`.`ComponentId` = `Extent5`.`Id`
 ORDER BY 
`Apply1`.`Id` DESC

So, there is missing order by statement on line 
FROM `ActivityRun` AS `Project1` 
 WHERE `Project1`.`ProcessId` = `Extent1`.`Id` ORDER BY Id DESC LIMIT 1) AS `ComponentVersionId`

Suggested fix:
Add order by statement.
[6 Sep 2014 0:25] Emil Lenngren
This bug is triggered by the method "public override SqlFragment Visit(DbApplyExpression expression)" in Source/MySql.Data.Entity/Generators/SelectGenerator.cs

We can see most things are copied over to the column:

    newColSelect.From = applySel.From;
    newColSelect.Where = applySel.Where;
    newColSelect.Limit = applySel.Limit;

But as you say, Order By is not copied over. I assume Skip, Group By and Distinct are missing as well.

This bug is quite critical. There is no error message but instead wrong results are retrieved from the database. We have been affected by this bug in production until I noticed the wrong results and rewrote our LINQ queries.
[29 Sep 2014 16:52] Fernando Gonzalez.Sanchez
Posted by developer:
 
Generated SQL does now contains correctly the OrderBy clause (as well as others like GroupBy, Skip, etc) in cases that involve let keyword or other scenario that is translated itto a DbApplyExpresssion.

This fix will appear in MySQL Connector/NET 6.7.6, 6.8.4, 6.9.5.
[16 Oct 2014 6:49] Philip Olson
Fixed as of the upcoming MySQL Connector/NET 6.7.6 / 6.8.4 / 6.9.5 releases, and here's the changelog entry:

Generated SQL was missing several clauses, such as OrderBy, GroupBy, and
Skip), in cases that involved the "let" keyword, or in other scenarios
that were translated into a DbApplyExpression.

Thank you for the bug report.
[16 Oct 2014 6:50] Philip Olson
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:

Generated SQL was missing several clauses, such as OrderBy, GroupBy, and
Skip), in cases that involved the "let" keyword, or in other scenarios
that were translated into a DbApplyExpression.

Thank you for the bug report.