MySQL Bugs: #45990: Order By with Union All problems when EntitySQL parses into native mysql SQL

Bug #45990	Order By with Union All problems when EntitySQL parses into native mysql SQL
Submitted:	7 Jul 2009 7:37	Modified:	20 Aug 2010 19:17
Reporter:	DBG DBG	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Any
Assigned to:	Reggie Burnett	CPU Architecture:	Any

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`

Hi DBG and thanks for your report.

I would like to see a test case attached to it and some more details regarding environment. See Bug#46142 or Bug#45723, for example, for quality test cases.

Replication project for Order By with Union All problems when EntitySQL parses into native mysql SQL

Attachment: testcase.zip (application/zip, text), 13.60 KiB.

I've added the archive of stuff you need to replicate the bug. Note that i simplified the project significantly so the actual native SQL in the differes from what i initially posted in the bug description, but nevertheless the problem occurs in it. Should be enough.

Verified as described with test case attached.

I've dug into this quite a bit and looked for the bug for sometime.  I then went back and tried to repeat using the latest builds of all products and your test app is producing correctly sorted results for me.  If you continue to see this problem with the latest editions of the products, please reopen this bug report.