[
http://issues.apache.org/jira/browse/DERBY-1624?page=comments#action_12424959 ]
Emmanuel Bernard commented on DERBY-1624:
-----------------------------------------
All but Derby DB works fine with it
original issue from the hibernate dev list.
I am working on enhancing Derby support a little bit, but have run into
an issue with their syntax that I am unable to figure out. I was hoping
someone on this list was familiar enough with Derby to point me in the
right direction.
Specifically, I am trying to properly deal with the manner in which
Derby (and also DB2 largely) expects columns to be referenced in certain
clauses. For example, because Hibernate always aliases columns in the
select clause, derby requires that those aliases be used in certain
later clauses. The query I am trying to work through right now is as
follows:
select
model0_.name as col_0_0_,
count(*) as col_1_0_
from
Model model0_
group by
model0_.name
having
count(*) > 1
However, I get errors from Derby when passing this to the DB:
ERROR 42X04: Column 'MODEL0_.COL_0_0_' is either not in any table in the
FROM list or appears within a join specification and is outside the
scope of the join specification or appears in a HAVING clause and is not
in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then
'MODEL0_.COL_0_0_' is not a column in the target table.
If the having clause is removed, the query parses fine; I have tried
various incantations regarding how to define the having clause without
avail.
This query seems taken almost verbatim from their reference docs, yet I
cannot get this to work...
http://db.apache.org/derby/docs/10.1/ref/rrefselectexpression.html
Any thoughts?
> use of direct column name rather than alias make aggregation fail (Hibernate
> depends on that)
> ---------------------------------------------------------------------------------------------
>
> Key: DERBY-1624
> URL: http://issues.apache.org/jira/browse/DERBY-1624
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.1.1.0, 10.1.3.1
> Reporter: Emmanuel Bernard
>
> Error: org.apache.derby.client.am.SqlException: Column 'MODEL0_.COL_0_0_' is
> either not in any table in the FROM list or appears within a join
> specification and is outside the scope of the join specification or appears
> in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or
> ALTER TABLE statement then 'MODEL0_.COL_0_0_' is not a column in the target
> table., SQL State: 42X04, Error Code: -1
> for
> select
> model0_.balance as col_0_0_,
> count(*) as col_1_0_
> from
> account model0_
> group by
> model0_.balance
> having
> count(*) > 1
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
