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:
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!