Not the answer you need?
Register and ask your own question!

5.5 > 5.6 Breaks Query With Having / Order By?

marhatmarhat EntrantCurrent User Role Beginner
Hello,

I have recently upgraded my Percona database from version 5.5.30 to 5.6.14. It seems that queries that ran fine on 5.5 no longer work. Specifically when the query uses HAVING with an ORDER BY clause.

For a simplified example:

SELECT
s.saleNumber,
ROUND(COALESCE((s.saleLinesTotal + s.taxTotal + s.shippingCost),0),2) AS 'SaleTotal',
ROUND(COALESCE(SUM(p.allocateamount),0),2) AS 'AllocatedTotal'
FROM CustomerSale s
LEFT JOIN CustomerInvoicePaymentAllocation ip ON ip.customerInvoiceDetail_id = s.customerinvoicedetail_id
LEFT JOIN CustomerPaymentAllocation p ON p.id = ip.id
WHERE s.isCancelled = false
GROUP BY s.id
HAVING AllocatedTotal != SaleTotal
ORDER BY s.saleNumber

Running this query in 5.6 returns results even when the allocated total and the sale total are the same. If I remove the ORDER BY clause, the query appears to work as expected.

Am I missing something in the change notes that can explain this change in behaviour?

Thanks for any ideas!

Comments

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.