1

The following example is just a test, but I guess one can construct something that actually does make sense. Anyway, I don't understand why it doesn't work. MySQL reports [42S22][1054] Unknown column 'document0_.age' in 'having clause'. I tested the same query with many other databases(PostgreSQL, DB2, Oracle, SQL Server, etc.), MySQL is the only to complain about this.

Could anyone explain to me why this doesn't work? I know I can just put in the age column into the GROUP BY clause and be done with it, but this is generated code and I'd need to add a special case for handling this MySQL problem which is why I'd like to better understand the issue. Is this only problematic for CASE WHEN statements or are there other expressions(e.g. nested function calls) that might also lead to such an error?

select count(versions1_.id) as col_0_0_
from Document document0_
  left outer join document_version versions1_ on document0_.id=versions1_.document_id
group by
  document0_.id,
  case when document0_.id=document0_.age and document0_.age<4 then '2' when document0_.id=4 then '4' else '3' end
having
  case when document0_.id=document0_.age and document0_.age<4 then '2' when document0_.id=4 then '4' else '3' end='2'

EDIT: I just tried out whether function calls migh also be problematic and indeed they are. The following also fails with the same error message.

select count(versions1_.id) as col_0_0_
from Document document0_
  left outer join document_version versions1_ on document0_.id=versions1_.document_id
group by
  document0_.id,
  POW(document0_.id,document0_.age)
having
  POW(document0_.id,document0_.age)=2

I begin to believe that MySQL does not detect that the expressions are equal and thus tries to re-evaluate them when doing the HAVING clause part. Am I right?

Christian Beikov
  • 15,141
  • 2
  • 32
  • 58
  • I am testing with 5.6.25. So you say although I am grouping by an expression which is the same thing that I later use in the HAVING clause, I still have to put all columns that are used by that expression into the GROUP BY clause? Sounds like MySQL is evaluating the expression multiple times. Interestingly, when using the CASE WHEN expression in the SELECT or ORDER BY clause, it doesn't complain. – Christian Beikov Jan 17 '17 at 09:32
  • my guess is that mysql processes the group by (and having) only on the projected fields. did you try adding `id` and `age` ti the selection? – barvaz Jan 17 '17 at 09:40
  • It does work, but that does not answer my question. I could also just add the columns to the GROUP BY clause to resolve this issue, but I'd like to know what MySQL does or why it fails to accept the query as it is. I also think that adding the columns to the SELECT clause "implicitly" adds them to the GROUP BY as per the documentation: http://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html – Christian Beikov Jan 17 '17 at 10:02

1 Answers1

0

It's working after adding SUM in the HAVING clause as below:

HAVING SUM(case when document0_.id=document0_.age and document0_.age<4
                     then '2' 
                when document0_.id=4 then '4' else '3' 
            end)='2'

Please try it, if it works. I have checked it with the similar case and it should work on your case also.

Follow this link for detail Unknown column in 'having clause'

Community
  • 1
  • 1
Shushil Bohara
  • 5,556
  • 2
  • 15
  • 32
  • Interestingly this indeed makes the example work, but still, this is no answer to my question. I guess it works because aggregate functions are evaluated during grouping where still all columns are accessible. – Christian Beikov Jan 17 '17 at 10:07
  • It's very simple and explained everywhere in the web so I have added one link please refer it. – Shushil Bohara Jan 17 '17 at 10:09
  • I guess putting the values in the SELECT clause is equivalent to using the ANY_VALUE construct that MySQL offers. Since the values are functionally dependent on the id, MySQL 5.7.5+ would probably even allow this query, but this only confirms my guess that MySQL does not even try to match expressions to reuse values it already grouped by and instead re-evaluates the HAVING expressions. – Christian Beikov Jan 17 '17 at 10:24
  • @Suraz: No, this isn't "explained everywhere in the web". Christian is grouping by an expression, so that expression must be available in the `HAVING` clause. **This is definitely a bug in MySQL.** This has nothing to do with the commonly error to use a non-aggregated column after aggregation that is not functionally dependant on the `GROUP BY` clause, which is what you are referring to. – Thorsten Kettner Jan 17 '17 at 11:16
  • 1
    Using `SUM` (or whatever aggregate function) on the expression is a nice workaround though, for of course the sum or average or maximum etc. of one single value is the value itself. And MySQL simply sees an aggregate function and doesn't complain any longer. – Thorsten Kettner Jan 17 '17 at 11:20