[ http://issues.apache.org/jira/browse/DERBY-883?page=comments#action_12412227 ]
Satheesh Bandaram commented on DERBY-883: ----------------------------------------- Thanks for making progress on this important new functionality, Manish. > 1. In terms of syntax, do we allow expressions in the group by list or > positional parameters, or both? > > select tomonth(creationdt), toyear(creationdt), count(*) > from bugs > group by 1, 2; I have seen positional parameters for ORDER BY expressions, not typically used in GROUP BY. Looking at both DB2 and Oracle documentation, it seems neither support positional parameters. > An implementation question on this note-- does the language code have a way > of looking > at two expressions (ValueNode?) and checking to see if they are equivalent? > We'll need > some way of doing this to match an expression in the group by list to an > expression > in the select list right? Correct. Don't think there is any existing expression matching to compare two expressions. DB2 docs discuss how group by expressions are matched in SQL reference manual. (Page 484: ftp://ftp.software.ibm.com/ps/products/db2/info/vr82/pdf/en_US/db2s1e81.pdf) > 2. I assume that an expression in a group by list must appear in the select > list without > aggregation right? Is this an error? > > select x+1, x+2, sum(y) > from test > group by x NO... This is a valid query. See the reference I provided above. > 3. What do we do with duplicates? i.e. > > select x+1, x+1, sum(y) > from test > group by x+1, x+1; > > Is this an error? The current implementation throws an error if the same > column > occurs more than once in the group by list. I am not sure why Derby currently considers this an error... Looking at the code, it seems it may be looking for ambiguous column references (like 'x' being part of two different tables in from_list), which makes sense, but not sure why duplicate references should be prevented. > Is there a standard somewhere which I should consult before trying to nail > down the functionality? Unfortunately, NO.... SQL 2003 seems to allow only column references in GROUP BY clause. But both DB2 and Oracle allow expressions in GROUP BY list and likely allowed by other database vendors too. You could use either DB2 or Oracle docs to understand how this functionality is defined there. Much easier to read these docs than confusing SQL 2003 spec. > 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 > Type: New Feature > Components: SQL > Versions: 10.1.2.1 > Environment: JDK 1.5.0_05 > Reporter: Lluis Turro > Assignee: Manish Khettry > > 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
