[
http://issues.apache.org/jira/browse/DERBY-1624?page=comments#action_12440038 ]
Andrew McIntyre commented on DERBY-1624:
----------------------------------------
Stumbled across this issue whilst searching for something else. Following
Manish's query-rewriting example, the original query:
select
model0_.name as col_0_0_,
count(*) as col_1_0_
from
Model model0_
group by
model0_.name
having
count(*) > 1
Could be rewritten as:
select * from
(
select
model0_.name as col_0_0_,
count(*) as col_1_0_
from
model model0_
group by
model0_.name
) as
model0_ (col_0_0_, col_1_0_)
where col_1_0_ > 1;
Unless I've missed something (which is likely, btw), I think this reproduces
the desired results with the current code. I realize this is hardly ideal, but
it seems that all the necessary pieces would be there: the table name, column
names, and identical results to what (I think) are expected.
One possible fix would be for the code that rewrites the group-by-with-having
predicate as a subquery to push the correlation names in the rewritten subquery
out as correlation names for the subquery. After looking at the code in
sqlgrammar.jj, this would appear to be a non-trivial, but doable, fix. A little
further investigation revealed some discussion about a related issue
(DERBY-280) and there is already a JIRA filed for rethinking the parsers
handling of queries with GROUP BY/HAVING that is filed as DERBY-681.
> 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
