MySQL HAVING not behaving properly

Question

I have a query:

SELECT I.Id
    , CAST(SUBSTRING_INDEX(GROUP_CONCAT(I.StatusId ORDER BY I.TransactionId DESC), ',', 1) AS UNSIGNED) AS StatusId
    , SUM(I.RefundAmount) AS RefundAmount
    FROM (
        SELECT I.Id
            , IT.Id AS TransactionId
            , IT.StatusId
            , IF(IT.TypeId = 2, IT.RefundAmount, 0) AS RefundAmount
            FROM Items I
            INNER JOIN ItemTransactions IT ON IT.ItemId = I.Id
            WHERE I.Id = someValue
    ) I
    GROUP BY I.Id
    HAVING StatusId = 1 AND RefundAmount = 0

Where Items table has transaction records stored in ItemTransactions table. I've been using this type of query and works for me until this time, got some issues with the having clause.

The query works in SQL Editors but not working properly when used on stored procedures. (Don't get me wrong, I've been using this query for most of my stored procedures). Debugging line per line, found that there is an issue with having clause.

As a temporary fix, I changed the query to:

SELECT I.Id
    , I.StatusId
    , I.RefundAmount
    FROM (
        SELECT I.Id
            , CAST(SUBSTRING_INDEX(GROUP_CONCAT(I.StatusId ORDER BY I.TransactionId DESC), ',', 1) AS UNSIGNED) AS StatusId
            , SUM(I.RefundAmount) AS RefundAmount
            FROM (
                SELECT I.Id
                    , IT.Id AS TransactionId
                    , IT.StatusId
                    , IF(IT.TypeId = 2, IT.RefundAmount, 0) AS RefundAmount
                    FROM Items I
                    INNER JOIN ItemTransactions IT ON IT.ItemId = I.Id
                    WHERE I.Id = someValue
            ) I
            GROUP BY I.Id
            --HAVING StatusId = 1 AND RefundAmount = 0
    ) I
    WHERE I.StatusId = 1 AND I.RefundAmount = 0

The query works fine. But I'd like to know if somebody has already encountered this, and found a fix. I'm using MySQL 5.0.

Thanks

I was having the same perplexity, and then realized that earlier in the procedure I DECLARE'd variables with the same names as ones in the HAVING clause. Renaming the former fixed the problem. — Bijou Trouvaille, Mar 22 '13 at 08:34

score 3 · Accepted Answer · edited May 23 '17 at 12:06

WHERE clause is used to filter data on the common attributes and expressions. HAVING clause is used to filter data after groupping had been performed and it's arguments should be either the ones of the GROUP BY clause, or expressions containing aggregate functions. It is also illegal to use the alias of the column in WHERE, GROUP BY or HAVING clauses, while it does works for the ORDER BY clause.

One option, as you've found, is to use a sub-query and then column references will work. Another one is to duplicate the whole expression in the HAVING clause:

SELECT I.Id,
    CAST(SUBSTRING_INDEX(GROUP_CONCAT(I.StatusId ORDER BY I.TransactionId DESC),
           ',', 1) AS UNSIGNED) AS StatusId,
    SUM(I.RefundAmount) AS RefundAmount
    FROM (
        SELECT I.Id
            , IT.Id AS TransactionId
            , IT.StatusId
            , IF(IT.TypeId = 2, IT.RefundAmount, 0) AS RefundAmount
            FROM Items I
            INNER JOIN ItemTransactions IT ON IT.ItemId = I.Id
            WHERE I.Id = someValue
    ) I
    GROUP BY I.Id
    HAVING
    CAST(SUBSTRING_INDEX(GROUP_CONCAT(I.StatusId ORDER BY I.TransactionId DESC),
         ',', 1) AS UNSIGNED) = 1
    AND SUM(I.RefundAmount) = 0;

EDIT: A closer look leaded to this question Using column alias in WHERE clause of MySQL query produces an error and to the MySQL documentation, that outlines that it is possible to use column aliases in GROUP BY, HAVING and ORDER BY clauses if such aliases are quoted with backticks, like:

...
HAVING `StatusId` = 1 AND `RefundAmount` = 0

Thanks for pointing that out, I'm familiar with `WHERE` and `HAVING` clauses, but not with expressions. I've been using column's alias in `HAVING` clauses, seems to work for me, until this time. Is this a flaw? — KaeL, Aug 09 '12 at 12:26
Added backticks but didn't work. But the link you've posted is great. Anyway, I was able to make the `HAVING` clause working, just by making the `ALIAS` name different from the field name. It worked. — KaeL, Aug 10 '12 at 02:43
@Pacerier: Perhaps I was using version 5. I can't remember, this was less than 3 years ago. :D — KaeL, Apr 12 '15 at 00:03

MySQL HAVING not behaving properly

1 Answers1