Announcement

Announcement Module
Collapse
No announcement yet.

5.5 > 5.6 Breaks Query With Having / Order By?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 5.5 > 5.6 Breaks Query With Having / Order By?

    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!

  • #2
    Hi,

    It seems, you are hitting this bug http://bugs.mysql.com/bug.php?id=69638 . Can you provide some sample data to check this query on both the versions OR provide some test case?

    Thanks.

    Comment

    Working...
    X