[
http://issues.apache.org/jira/browse/DERBY-1624?page=comments#action_12428453 ]
Manish Khettry commented on DERBY-1624:
---------------------------------------
This has to do with the way Derby rewrites querys with a groupby. What I find
odd is that the query works without the having clause because the same rewrite
is done for only group by's. I tracked it down to this bit of code in
FromSubquery#findMatching column. So, when we are trying to bind "model0_.name"
in the select list why look in different places (case 2 vs case 4) depending on
whether there is a having clause or not?!
/* We have 5 cases here:
* 1. ColumnReference was generated to replace an aggregate.
* (We are the wrapper for a HAVING clause and the
ColumnReference
* was generated to reference the aggregate which
was pushed down into
* the SELECT list in the user's query.)
* Just do what you would expect. Try to resolve
the
* ColumnReference against our RCL if the
ColumnReference is unqualified
* or if it is qualified with our exposed name.
* 2. We are the wrapper for a GROUP BY and a HAVING
clause and
* either the ColumnReference is qualified or it
is in
* the HAVING clause. For example:
* select a from t1 group by a having t1.a
= 1
* select a as asdf from t1 group by a
having a = 1
* We need to match against the underlying
FromList and then find
* the grandparent ResultColumn in our RCL so that
we return a
* ResultColumn from the correct ResultSetNode.
It is okay not to
* find a matching grandparent node. In fact,
this is how we ensure
* the correct semantics for ColumnReferences in
the HAVING clause
* (which must be bound against the GROUP BY list.)
* 3. We are the wrapper for a HAVING clause without a GROUP
BY and
* the ColumnReference is from the HAVING clause.
ColumnReferences
* are invalid in this case, so we return null.
* 4. We are the wrapper for a GROUP BY with no HAVING. This
has
* to be a separate case because of #5 and the
following query:
* select * from (select c1 from t1) t,
(select c1 from t1) tt
* group by t1.c1, tt.c1
* (The correlation names are lost in the
generated FromSuquery.)
* 5. Everything else - do what you would expect. Try to
resolve the
* ColumnReference against our RCL if the
ColumnReference is unqualified
* or if it is qualified with our exposed name.
*/
if (columnReference.getGeneratedToReplaceAggregate()) // 1
{
resultColumn =
resultColumns.getResultColumn(columnReference.getColumnName());
}
else if (generatedForGroupByClause && generatedForHavingClause
&&
columnReference.getClause() !=
ValueNode.IN_SELECT_LIST) // 2
{
if (SanityManager.DEBUG)
{
SanityManager.ASSERT(correlationName == null,
"correlationName expected to be null");
SanityManager.ASSERT(subquery instanceof
SelectNode,
"subquery expected to be instanceof
SelectNode, not " +
subquery.getClass().getName());
}
SelectNode select = (SelectNode) subquery;
resultColumn =
select.getFromList().bindColumnReference(columnReference);
/* Find and return the matching RC from our RCL.
* (Not an error if no match found. Let
ColumnReference deal with it.
*/
if (resultColumn != null)
{
/* Is there a matching resultColumn in the
subquery's RCL? */
resultColumn =
subquery.getResultColumns().findParentResultColumn(
resultColumn);
if (resultColumn != null)
{
/* Is there a matching resultColumn in
our RCL? */
resultColumn =
resultColumns.findParentResultColumn(
resultColumn);
}
}
}
else if ((generatedForHavingClause && !
generatedForGroupByClause) // 3
&& (columnReference.getClause() !=
ValueNode.IN_SELECT_LIST) )
{
resultColumn = null;
}
else if (generatedForGroupByClause) // 4
{
resultColumn = resultColumns.getResultColumn(
columnsTableName,
columnReference.getColumnName());
}
else if (columnsTableName == null ||
columnsTableName.equals(correlationName)) // 5?
{
resultColumn =
resultColumns.getAtMostOneResultColumn(columnReference, correlationName);
}
if (resultColumn != null)
{
columnReference.setTableNumber(tableNumber);
}
return resultColumn;
}
Emmanuel, Is this a big problem for Hibernate with Derby? Others on the derby
list who may know this-- how hard is it to ditch the rewrite of groupby/having
queries or can we still keep the rewrite and fix this?
> 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
