Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <16449719.1155752114908.JavaMail.jira@brutus>
Date: Wed, 16 Aug 2006 11:15:14 -0700 (PDT)
From: "Manish Khettry (JIRA)" <derby-dev@db.apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-1624) use of direct column name rather
 than alias make aggregation fail (Hibernate depends on that)
In-Reply-To: <23972790.1154455274246.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

    [ 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