List:General Discussion« Previous MessageNext Message »
From:Thomas J. Harris Date:July 4 2003 2:14am
Subject:HAVING clause - MySQL 4.0.13 - unknown column error
View as plain text  
To any MySQL users,

I am using an open-source object relational mapping layer from Java
called Hibernate to interface with a MySQL database on my machine. I am
running version 4.0.13 of the server, and SQL/J Connector version 3.0.7.
The query that is generated from Hibernate looks like this:

select skill0_.SKILL_GUID as x0_0_, skill0_.SKILL_NAME as x1_0_,
resource1_.COMPETENCE_LEVEL as x2_0_, resource1_.RESOURCE_SKILL_ID as
x3_0_ from SKILL skill0_, RESOURCE_SKILL resource1_ where
skill0_.SKILL_GUID=resource1_.SKILL_GUID and
((resource1_.RESOURCE_GUID=? )and(skill0_.ACTIVE=1
)and(resource1_.ACTIVE=1 )) group by skill0_.SKILL_GUID ,
skill0_.SKILL_NAME , resource1_.COMPETENCE_LEVEL having
(resource1_.COMPETENCE_LEVEL>? ) order by resource1_.COMPETENCE_LEVEL
desc , skill0_.SKILL_NAME asc 

This query does not work, giving me the following error:
Unknown column 'resource1_.COMPETENCE_LEVEL' in 'having clause' 

Now, if I remove the having clause, the query runs fine.

BUT, if I change the query so that the having clause is the following: 

having (x2_0_>? ) 

It runs fine! "x2_0_" is the SQL label given to the field in the select

I have asked the Hibernate developers to assist me on this issue, but
they replied that this was most certainly a problem with MySQL alone,
and that the SQL works on all the other DBs they have tested on. They
suggested I contact this mailing list to find a workaround.

Why is MySQL tossing an error back at me? The column is used in other
places in the same query without a problem. Is a HAVING clause special
in some way? Does it require an alias to operate? Why? This same query
works on Oracle and on MS SQL, as is.

I need the having clause because if I put this particular criteria in
the where clause, I'd get erroneous data. There are multiple matching
rows for the criteria, and I only want the most recent, which has the
max(resourceSkillID). Otherwise, I'd just do away with it.

I'd appreciate any advice or insight on this issue. We are running
benchmarks between different Java database abstraction layers against
multiple databases to see which we should use for upcoming projects. If
we select MySQL, we'd be getting a support contract, and would be
shipping many servers with MySQL on them, at the appropriate licensing
fee. Since this is one of the queries that we need to support in the
benchmark, we may need to drop MySQL from the running in order to finish
the work.

Tom Harris

~| Tom Harris - Cisco Systems - VTG - CCBU - CCIS - CEM |~
~|                     |~

HAVING clause - MySQL 4.0.13 - unknown column errorThomas J. Harris4 Jul
  • Re: HAVING clause - MySQL 4.0.13 - unknown column errorDan Nelson4 Jul