Bug #94 Two aggregates in a single query can return incorrect info

• Submitted: 24 Feb 2003 11:54
• Modified: 24 Feb 2003 14:20
• Reporter: Marc Peterson
• Status: Not a Bug
• Category: MySQL Websites: bugs.mysql.com
• Severity: S3 (Non-critical)
• Version: 3.23
• OS: Windows (Win2000)
• CPU Architecture: Any

[24 Feb 2003 11:54] Marc Peterson

Description:
The following query should count the number of attachments and sum the order total.  The results are almost perfect.  But in cases where there is multiple attachments the sum may be doubled (some entries are summed properly, some are not).

SELECT SalesOrders.SalesOrderID, 
COUNT(SalesAttachments.SalesOrderID) AS NumAttachments, 
SUM(SalesOrderParts.ExtendedPrice) AS OrderTotal
FROM SalesOrders 
LEFT JOIN SalesOrderParts ON SalesOrders.SalesOrderID = SalesOrderParts.SalesOrderID 
LEFT JOIN SalesAttachments ON SalesOrders.SalesOrderID = SalesAttachments.SalesOrderID 
GROUP BY SalesOrders.SalesOrderID
ORDER BY SalesOrderID DESC

How to repeat:

Suggested fix:
If I remove the COUNT clause, the SUM clause works properly.  For example:

SELECT SalesOrders.SalesOrderID, 
SalesAttachments.SalesOrderID AS IsAttachment
SUM(SalesOrderParts.ExtendedPrice) AS OrderTotal
FROM SalesOrders 
LEFT JOIN SalesOrderParts ON SalesOrders.SalesOrderID = SalesOrderParts.SalesOrderID 
LEFT JOIN SalesAttachments ON SalesOrders.SalesOrderID = SalesAttachments.SalesOrderID 
GROUP BY SalesOrders.SalesOrderID, IsAttachment
ORDER BY SalesOrderID DESC

[24 Feb 2003 11:57] Marc Peterson

Sorry, that's version 3.23

[24 Feb 2003 12:12] Marc Peterson

The aggregate has nothing to do with it.  It's the two LEFT JOINs.  The following query produces the same erroneos sum:

SELECT SalesOrders.SalesOrderID,
SUM(SalesOrderParts.ExtendedPrice) AS OrderTotal
FROM SalesOrders 
LEFT JOIN SalesOrderParts ON SalesOrders.SalesOrderID = SalesOrderParts.SalesOrderID 
LEFT JOIN SalesAttachments ON SalesOrders.SalesOrderID = SalesAttachments.SalesOrderID 
GROUP BY SalesOrders.SalesOrderID
ORDER BY SalesOrderID DESC

Removing the second LEFT JOIN will return the correct results.

[24 Feb 2003 14:20] Alexander Keremidarski

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Report does not include any information under How-to-repeat section.

Without any data provided and without sample result it is impossible to be tested.

Please note that all Aggregation functions will exclude 
NULLs from evaluation.

The only exception is COUNT called as COUNT(*)