Bug #60589 LINQ Group By Generating Broken Query

Submitted: 22 Mar 2011 15:06 Modified: 22 Mar 2011 17:12
Reporter: Nick Berardi Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.3.6 OS:Any
Assigned to: CPU Architecture:Any

[22 Mar 2011 15:06] Nick Berardi
Description:
LINQ Group By is generating queries that don't execute properly, when trying to get the key value of the group back with the resulting sums.

How to repeat:
I was executing the following LINQ statement:

var rollup = stats.Rollups
  .Where(x => x.Injected != 0 || x.BytesUsed != 0L)
  .Where(x => x.DateTime >= startOverageRange && x.DateTime <= endOverageRange)
  .GroupBy(x => new { x.ServerId, x.DateTime })
  .Select(x => new {
    ServerId = x.Key.ServerId,
    DateTime = x.Key.DateTime,
    Injected = x.Sum(y => y.Injected),
    BytesUsed = x.Sum(y => y.BytesUsed)
  })
  .ToList();

Against the following table:

CREATE TABLE `Rollups` (
  `ServerId` int(11) NOT NULL,
  `DateTime` datetime NOT NULL,
  `MailingId` varchar(150) CHARACTER SET ascii NOT NULL,
  `Injected` int(11) NOT NULL DEFAULT '0',
  `BytesUsed` bigint(20) NOT NULL DEFAULT '0',
  `Queued` int(11) NOT NULL DEFAULT '0',
  `Suppressed` int(11) NOT NULL DEFAULT '0',
  `Sent` int(11) NOT NULL DEFAULT '0',
  `Complaints` int(11) NOT NULL DEFAULT '0',
  `Opens` int(11) NOT NULL DEFAULT '0',
  `Clicks` int(11) NOT NULL DEFAULT '0',
  `HardFailures` int(11) NOT NULL DEFAULT '0',
  `SoftFailures` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ServerId`,`DateTime`,`MailingId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And it generated the following query

SELECT `GroupBy1`.`K1` AS `ServerId`,
       `GroupBy1`.`K2` AS `DateTime`,
       `GroupBy1`.`A1` AS `C1`,
       `GroupBy1`.`A2` AS `C2`
FROM   (SELECT   Sum(`Injected`)  AS `A1`,
                 Sum(`BytesUsed`) AS `A2`
        FROM     `Rollups` AS `Extent1`
        WHERE    ((NOT (0 = `Extent1`.`Injected`))
                   OR (NOT (0 = `Extent1`.`BytesUsed`)))
                 AND ((`Extent1`.`DateTime` >= '2011-02-28T00:00:00.00' /* @p__linq__0 */)
                      AND (`Extent1`.`DateTime` <= '2011-03-28T00:00:00.00' /* @p__linq__1 */))
        GROUP BY `Extent1`.`ServerId`,
                 `Extent1`.`DateTime`) AS `GroupBy1`

Which doesn't actually return the Group By Key value K1 and K2.

Suggested fix:
My suggestion is to make sure the key values get returned with the sub GroupBy1 query.

SELECT `GroupBy1`.`K1` AS `ServerId`,
       `GroupBy1`.`K2` AS `DateTime`,
       `GroupBy1`.`A1` AS `C1`,
       `GroupBy1`.`A2` AS `C2`
FROM   (SELECT   `ServerId` AS `K1`,
                 `DateTime` AS `K2`,
                 Sum(`Injected`)  AS `A1`,
                 Sum(`BytesUsed`) AS `A2`
        FROM     `Rollups` AS `Extent1`
        WHERE    ((NOT (0 = `Extent1`.`Injected`))
                   OR (NOT (0 = `Extent1`.`BytesUsed`)))
                 AND ((`Extent1`.`DateTime` >= '2011-02-28T00:00:00.00' /* @p__linq__0 */)
                      AND (`Extent1`.`DateTime` <= '2011-03-28T00:00:00.00' /* @p__linq__1 */))
        GROUP BY `Extent1`.`ServerId`,
                 `Extent1`.`DateTime`) AS `GroupBy1`
[22 Mar 2011 17:12] Valeriy Kravchuk
This is a duplicate of ages old and not fixed Bug #46742.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.050 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.