List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:July 4 2003 3:50am
Subject:Re: HAVING clause - MySQL 4.0.13 - unknown column error
View as plain text  
In the last episode (Jul 03), Thomas J. Harris said:
> This query does not work, giving me the following error:
> Unknown column 'resource1_.COMPETENCE_LEVEL' in 'having clause' 
> 
> 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
> clause. 
> 
> 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.

That's the wrong answer though :)  "It works on Oracle and MS SQL"
doesn't mean it's right.  They may both have implemented a non-standard
extension the same way.  Who knows; the SQL spec might allow this, but
I don't have the spec.
 
> 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.

HAVING is supposed to apply to the resulting recordset just before it
is sorted, and I'm pretty sure the columns have been aliased at this
point.  I guess it depends on the SQL definition of alias.  Does it
mean "alternate name", or "new name"? 

-- 
	Dan Nelson
	dnelson@stripped
Thread
HAVING clause - MySQL 4.0.13 - unknown column errorThomas J. Harris4 Jul
  • Re: HAVING clause - MySQL 4.0.13 - unknown column errorDan Nelson4 Jul