5.5 > 5.6 Breaks Query With Having / Order By?


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


It seems, you are hitting this bug [URL]MySQL Bugs: #69638: Wrong results when running a SELECT that includes a HAVING based on a function . Can you provide some sample data to check this query on both the versions OR provide some test case?