db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Manish Khettry (JIRA)" <derby-...@db.apache.org>
Subject [jira] Updated: (DERBY-883) Enhance GROUP BY clause to support expressions instead of just column references.
Date Mon, 17 Jul 2006 04:53:17 GMT
     [ http://issues.apache.org/jira/browse/DERBY-883?page=all ]

Manish Khettry updated DERBY-883:
---------------------------------

    Derby Info: [Patch Available]

Functionally, this patch supports expressions in the group by list. An expression
in the group by list can also be used in the select list. For example if the grouping
expression is c1+c2, then a valid expression in the select list would be 
c1+c2+1. The expression matching facility is not smart enough to allow c1+1+c2.

This patch does not allow the use of grouping expressions in the having clause
or order by clause. These are restrictions for now which should be removed
eventually. For more information on group by expressions:

http://www.pdc.kth.se/doc/SP/manuals/db2-5.0/html/db2s0/db2s0176.htm#HDRGRPBY

Another change has been to allow the use of duplicate grouping expressions: i.e
select a, sum(b) from test group by a,a;

Error messages have been changed. The most noticeable change is the use
of the more general sql exception: LANG_INVALID_GROUPED_SELECT_LIST.

VerifyAggregateExpressionsVisitor

o disallows java nodes. This should take care of functions in java. 
o the skipChildren method doesn't traverse a subtree that contains
  any grouping expression.
o the error messages are more appropriate-- LANG_INVALID_GROUPED_SELECT_LIST.

SelectNode

o calls preprocess on the group by list. This is needed because expressions 
can get rewritten in the select list but not in the grouping list causing
problems when the result set is generated.

GroupByNode

o changes to init() take care of the case where gropuing expressions is
not a column reference.

o the rewrite logic is a bit different now. Earlier, we would change
each unaggregate ColumnReference in the select list and point it to the
GroupByColumn. Now we replace each group by expression that we find
in the projection list with a virtual column node that effectively points
to a result column in the result set doing the group by. In addition
the original routine which did the rewrite is now split up into two separate
and smaller routines: addUnAggColumns and addAggregateColumns.

GroupByColumn

o now uses a ValueNode instead of a ColumnReference. Unused methods zapped.

GroupByDiff

o verifyUniqueGroupingColumn discarded. This was a restriction earlier and
it does not make sense.

o findGroupingColumn now does the hard work of finding a group by column
for a given expression.

sqlgrammer

o use additiveExpression instead of columnReference.

GroupByExpressionTest.java

o JUnit test case for this functionality.







> Enhance GROUP BY clause to support expressions instead of just column references.
> ---------------------------------------------------------------------------------
>
>                 Key: DERBY-883
>                 URL: http://issues.apache.org/jira/browse/DERBY-883
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions: 10.1.2.1
>         Environment: JDK 1.5.0_05
>            Reporter: Lluis Turro
>         Assigned To: Manish Khettry
>         Attachments: 883.patch.txt
>
>
> This select would return an error syntax on finding "(" after month if group by clause:
> select idissue, month(creation), year(creation), count(distinct idissue)
> where 
>   ....
> group by idissue, month(creation), year(creation)

-- 
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

        

Mime
View raw message