Bug #60589 LINQ Group By Generating Broken Query
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`
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.
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`