Bug #79817 Error in LINQ to Entities query when using multiple GroupBy's
Submitted: 31 Dec 2015 11:20 Modified: 15 Jan 2016 9:16
Reporter: Jorr it Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.9.8 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: Connector/Net, entity framework, linq

[31 Dec 2015 11:20] Jorr it
Description:
When we write an LINQ statement with multiple GroupBy's, the generated SQL is invalid and gives exception:

Unknown column 'GroupBy1.K1' in 'where clause'

Below there are three variations of LINQ statements which all more or less result in same the exception.

new myDbContext().careactivity
	.GroupBy(x => new { x.ActivityGroup, x.MedicalSpecialist })
	.Select(o => new
	{
		P6 = o.Key,
		P3 = o.Sum(elem => elem.HoursAfterSummary)
	})
	.ToList();
	
-- or --

new myDbContext().careactivity
	.GroupBy(x => x.ActivityGroup })
	.Select(o => new
	{
		P6 = o.Key,
		P3 = o.Sum(elem => elem.HoursAfterSummary),
		P4 = o.GroupBy(elem => elem.MedicalSpecialist).Count()
	})
	.ToList();
	
-- or --

new myDbContext().careactivity
	.GroupBy(x => x.ActivityGroup })
	.Select(o => new
	{
		P6 = o.Key,
		P3 = o.Sum(elem => elem.HoursAfterSummary),
		P4 = o.Select(elem => elem.MedicalSpecialist).Distinct().Count()
	})
	.ToList();	

I'm using .NET Framework 4.5.1, EntityFramework 6.1.3 and MySQL Connection/NET 6.9.8. My project has an Database First approach.  This is the table:

CREATE TABLE `careactivity` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Oid` int(11) DEFAULT NULL,
  `CustomerSiteId` int(11) DEFAULT NULL,
  `MedicalSpecialist` varchar(255) DEFAULT NULL,
  `ActivityGroup` varchar(255) DEFAULT NULL,
  `HoursAfterSummary` decimal(19,5) DEFAULT NULL,
  PRIMARY KEY (`Id`),
)

Any sample data can be used. Sample data can be sent. Also a complete sample VS project can be sent.

It seems to have similarities with this bug:
http://bugs.mysql.com/bug.php?id=68513

How to repeat:
A complete sample VS project can be sent.

Suggested fix:
LINQ Query must work without throwing exception.
[4 Jan 2016 6:55] Chiranjeevi Battula
Hello  Jorrit Steetskamp,

Thank you for the bug report.
Verified this behavior on Visual Studio 2013 (C#.Net) with  MySQL Connector/Net 6.9.8.

Thanks,
Chiranjeevi.
[4 Jan 2016 6:56] Chiranjeevi Battula
Result StandardOutput:	
---
SELECT
`GroupBy1`.`K3` AS `C1`, 
`GroupBy1`.`K2` AS `ActivityGroup`, 
`GroupBy1`.`K1` AS `MedicalSpecialist`, 
`GroupBy1`.`A1` AS `C2`
FROM (SELECT
`Extent1`.`MedicalSpecialist` AS `K1`, 
`Extent1`.`ActivityGroup` AS `K2`, 
SUM(`Extent1`.`HoursAfterSummary`) AS `A1`
FROM `careactivity` AS `Extent1`
 GROUP BY 
`Extent1`.`MedicalSpecialist`, 
`Extent1`.`ActivityGroup`, 
1) AS `GroupBy1`
********************************
SELECT
1 AS `C1`, 
`Project2`.`ActivityGroup`, 
`Project2`.`C1` AS `C2`, 
`Project2`.`C2` AS `C3`
FROM (SELECT
`GroupBy1`.`A1` AS `C1`, 
`GroupBy1`.`K1` AS `ActivityGroup`, 
(SELECT
COUNT(1) AS `A1`
FROM (SELECT DISTINCT 
`Extent2`.`MedicalSpecialist`
FROM `careactivity` AS `Extent2`
 WHERE (`GroupBy1`.`K1` = `Extent2`.`ActivityGroup`) OR ((`GroupBy1`.`K1` IS  NULL) AND (`Extent2`.`ActivityGroup` IS  NULL))) AS `Distinct1`) AS `C2`
FROM (SELECT
`Extent1`.`ActivityGroup` AS `K1`, 
SUM(`Extent1`.`HoursAfterSummary`) AS `A1`
FROM `careactivity` AS `Extent1`
 GROUP BY 
`Extent1`.`ActivityGroup`) AS `GroupBy1`) AS `Project2`
********************************
SELECT
1 AS `C1`, 
`Project2`.`ActivityGroup`, 
`Project2`.`C1` AS `C2`, 
`Project2`.`C2` AS `C3`
FROM (SELECT
`GroupBy1`.`A1` AS `C1`, 
`GroupBy1`.`K1` AS `ActivityGroup`, 
(SELECT
COUNT(1) AS `A1`
FROM (SELECT DISTINCT 
`Extent2`.`MedicalSpecialist`
FROM `careactivity` AS `Extent2`
 WHERE (`GroupBy1`.`K1` = `Extent2`.`ActivityGroup`) OR ((`GroupBy1`.`K1` IS  NULL) AND (`Extent2`.`ActivityGroup` IS  NULL))) AS `Distinct1`) AS `C2`
FROM (SELECT
`Extent1`.`ActivityGroup` AS `K1`, 
SUM(`Extent1`.`HoursAfterSummary`) AS `A1`
FROM `careactivity` AS `Extent1`
 GROUP BY 
`Extent1`.`ActivityGroup`) AS `GroupBy1`) AS `Project2`
[7 Jan 2016 17:40] Yoni Tjio
Grouping by function will also trigger the same error:

var salesDailies = (from o in this.AppContext.SalesDailies
  group o by DbFunctions.TruncateTime(o.Date) into g
  select new { Date = g.Key, Qty = g.Sum(q => q.Qty) });
[5 Apr 2016 5:58] Chiranjeevi Battula
http://bugs.mysql.com/bug.php?id=80952 marked as duplicate of this one.
[17 Aug 2016 6:37] Chiranjeevi Battula
http://bugs.mysql.com/bug.php?id=82604 marked as duplicate of this one.
[24 Sep 2018 9:48] André Reichelt
Is there any solution or workaround for this bug? I'm also affected by it. It is still present in version 6.10.8.0!

Here's my code:

            var l = from t in db.ticket
                    group t by t.change_time.YearWeek(3).Insert(5, 0, "/") into g
                    select new {
                        g.Key,
                        cnt = g.Count()
                    };

            var test4 = l.ToList();
[24 Sep 2018 9:50] André Reichelt
By the way, this generates the following weird SQL syntax:

SELECT
1 AS `C1`, 
`GroupBy1`.`K1` AS `C2`, 
`GroupBy1`.`A1` AS `C3`
FROM (SELECT
COUNT(1) AS `A1`
FROM `ticket` AS `Extent1`
 GROUP BY 
INSERT(YEARWEEK(`Extent1`.`change_time`, 3), 5, 0, @gp1)) AS `GroupBy1`