MySQL Bugs: #70941: Invalid SQL query when eager loading two nested collections

Bug #70941	Invalid SQL query when eager loading two nested collections
Submitted:	18 Nov 2013 15:49	Modified:	20 Feb 2015 3:12
Reporter:	Youssef Abidi	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:		OS:	Any
Assigned to:	Gabriela Martinez Sanchez	CPU Architecture:	Any

Description:
Hi, I am using Entity Framework 5.0.0 (code first), MySQL.Data.Entities 6.7.4.1, MySQL.Data 6.7.4 (all installed using NuGet) and .Net Framework 4.

I built a simple data model to illustrate the issue I found. The DbContext contains only 4 entities:

[Table("client")]
class Client
{
    [Key]
    public int Id { get; set; }

    public ICollection<Order> Orders { get; set; } 
}

[Table("order")]
class Order
{
    [Key]
    public int Id { get; set; }

    public ICollection<Item> Items { get; set; }

    public ICollection<Discount> Discounts { get; set; } 
}

[Table("item")]
class Item
{
    [Key]
    public int Id { get; set; }
}

[Table("discount")]
internal class Discount
{
    [Key]
    public int Id { get; set; }
}

Clients make orders. Every order has a collection of items and a collection of discounts.

But when I try to load clients, along with their orders, items and discounts, using the following LINQ syntax (eager loading):

var clients = model.Clients
                    .Include(c => c.Orders.Select(o => o.Items))
                    .Include(c => c.Orders.Select(o => o.Discounts));

The generated SQL query is incorrect:

SELECT
	`Project3`.`Id`, 
	`Project3`.`C11` AS `C1`, 
	`Project3`.`C2`, 
	`Project3`.`C3`, 
	`Project3`.`C4`, 
	`Project3`.`C1` AS `C5`, 
	`Project3`.`C5` AS `C6`, 
	`Project3`.`C6` AS `C7`, 
	`Project3`.`C7` AS `C8`, 
	`Project3`.`C8` AS `C9`, 
	`Project3`.`C9` AS `C10`, 
	`Project3`.`C10` AS `C11`
FROM
	(
		SELECT
			`Apply1`.`Id`, 
			`UnionAll1`.`C1`, 
			`UnionAll1`.`Id` AS `C2`, 
			`UnionAll1`.`Id1` AS `C3`, 
			`UnionAll1`.`Client_Id` AS `C4`, 
			`UnionAll1`.`Id2` AS `C5`, 
			`UnionAll1`.`Id3` AS `C6`, 
			`UnionAll1`.`Order_Id` AS `C7`, 
			`UnionAll1`.`C2` AS `C8`, 
			`UnionAll1`.`C3` AS `C9`, 
			`UnionAll1`.`C4` AS `C10`, 
			CASE WHEN (`UnionAll1`.`Id` IS  NULL) THEN (NULL)  ELSE (1) END AS `C11`
		FROM
			(
				SELECT
					`Extent1`.`Id`
				FROM `client` AS `Extent1`
			) AS `Apply1`
	) AS `Project3`
 ORDER BY 
	`Project3`.`Id` ASC, 
	`Project3`.`C11` ASC, 
	`Project3`.`C3` ASC, 
	`Project3`.`C1` ASC

The query references the alias UnionAll1 which has never been defined.

How to repeat:
I will attach a Visual Studio solution that allows to repeat the issue.

Suggested fix:
I feel like part of the generated query is missing (the one that creates a subquery with the "UnionAll1" alias) but I honestly have no idea how to fix this issue.

Visual Studio 2012 Solution to repeat the issue (Build to restore packages using NuGet and change connection string in Model.cs)

Attachment: TestEagerLoadingBug.zip (application/octet-stream, text), 460.69 KiB.

Ok, I got back to this one, confirmed SQL query is wrong.

Youssef, thanks for the bug report, working on a fix (also noticed in some configurations the order table won't be created because is not issued with quotes and it's a keyword, will fix that too).

Regards.

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/Net 6.7.7 release, and here's the changelog entry:

Loading two nested collections could be reported as an invalid SQL query
as MySQL Connector/Net only generated a partial query.

Thank you for the bug report.