Description:
When running an eSQL query that selects a union all of ordered subqueries, the native SQL
that is ran, is incorrect, and the wrong field is ordered.
It was confirmed that EntityFramework produces the correct Command Tree and the
incorrectness is in mysql data provider (connector/net 6.0)
More about it on EntityFramework forums here:
http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/cd66a8dd-4d2...
How to repeat:
1) Run an Entity SQL query like this:
SELECT
(SELECT value a FROM c1.founders as a
order by a.FOUNDER_ID) as data_old,
(SELECT value b FROM c1.na.na_founders as b
order by b.FOUNDER_ID) as data_new
FROM Entities.mainSet as c1
WHERE c1.main_id = @main_id
2) Notice that it becomes such sql:
SELECT
`UnionAll1`.`MAIN_ID` AS `C1`,
`UnionAll1`.`MAIN_ID1` AS `C2`,
`UnionAll1`.`C2` AS `C3`,
`UnionAll1`.`C1` AS `C4`,
`UnionAll1`.`C3` AS `C5`,
`UnionAll1`.`FOUNDER_ID` AS `C6`,
`UnionAll1`.`C4` AS `C7`,
`UnionAll1`.`FOUNDER_NAME` AS `C8`,
`UnionAll1`.`MAIN_ID2` AS `C9`,
`UnionAll1`.`FOUNDER_ID1` AS `C10`,
`UnionAll1`.`C5` AS `C11`,
`UnionAll1`.`C6` AS `C12`,
`UnionAll1`.`C7` AS `C13`,
`UnionAll1`.`C8` AS `C14`,
`UnionAll1`.`C9` AS `C15`
FROM ((SELECT
CASE WHEN (`Extent2`.`FOUNDER_ID` IS NULL) THEN (NULL) ELSE (1) END AS `C1`,
`Extent1`.`MAIN_ID`,
`Extent1`.`MAIN_ID` AS `MAIN_ID1`,
1 AS `C2`,
CASE WHEN (`Extent2`.`FOUNDER_ID` IS NULL) THEN (NULL) ELSE (1) END AS `C3`,
`Extent2`.`FOUNDER_ID`,
CASE WHEN (`Extent2`.`FOUNDER_ID` IS NULL) THEN (NULL) ELSE (1) END AS `C4`,
`Extent2`.`FOUNDER_NAME`,
`Extent2`.`MAIN_ID` AS `MAIN_ID2`,
`Extent2`.`FOUNDER_ID` AS `FOUNDER_ID1`,
NULL AS `C5`,
NULL AS `C6`,
NULL AS `C7`,
NULL AS `C8`,
NULL AS `C9`,
NULL AS `C10`
FROM `main` AS `Extent1` LEFT OUTER JOIN `founders` AS `Extent2` ON `Extent1`.`MAIN_ID` =
`Extent2`.`MAIN_ID`
WHERE `Extent1`.`MAIN_ID` = 11111) UNION ALL (SELECT
2 AS `C1`,
`Extent3`.`MAIN_ID`,
`Extent3`.`MAIN_ID` AS `MAIN_ID1`,
1 AS `C2`,
NULL AS `C3`,
NULL AS `C4`,
NULL AS `C5`,
NULL AS `C6`,
NULL AS `C7`,
NULL AS `C8`,
1 AS `C9`,
`Extent4`.`FOUNDER_ID`,
1 AS `C10`,
`Extent4`.`FOUNDER_NAME`,
`Extent4`.`MAIN_ID` AS `MAIN_ID2`,
`Extent4`.`FOUNDER_ID` AS `FOUNDER_ID1`
FROM `main` AS `Extent3` INNER JOIN (`na_founders` AS `Extent4` INNER JOIN (`na` AS
`Extent5` LEFT OUTER JOIN `main` AS `Extent6` ON `Extent5`.`MAIN_ID` =
`Extent6`.`MAIN_ID`) ON `Extent4`.`MAIN_ID` = `Extent5`.`MAIN_ID`) ON `Extent3`.`MAIN_ID`
= `Extent5`.`MAIN_ID`
WHERE `Extent3`.`MAIN_ID` = 11111)) AS `UnionAll1`
ORDER BY
`MAIN_ID` ASC,
`MAIN_ID1` ASC,
`C1` ASC,
`FOUNDER_ID1` ASC,
`C10` ASC
Suggested fix:
Here's the difference that will fix the ordering:
http://i.piccy.info/i3/46/65/ce97f74e0d64432fe106a5cf4d43.png
http://i.piccy.info/i3/08/a3/bb06c0488d047f8e4aa4adcf2bc7.png
So suggested fix is either to add such ordering or prefix prefix order keys with
`UnionAll1`
