[ https://issues.apache.org/jira/browse/DERBY-4003?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14132791#comment-14132791 ]
Stephen Kruger commented on DERBY-4003: --------------------------------------- Alternatively, could we get some additional functions implemented? One glaring hole is dayOfWeek - should be trivial to implement. Right now, it's impossible to count rows and aggregate by day of week to show weekly statistics : select dayOfWeek(date) as dow, count(Uid) from INBOX group by dow > Allow user-defined functions in GROUP BY expressions > ---------------------------------------------------- > > Key: DERBY-4003 > URL: https://issues.apache.org/jira/browse/DERBY-4003 > Project: Derby > Issue Type: Improvement > Components: SQL > Affects Versions: 10.5.1.1 > Reporter: Rick Hillegas > Labels: derby_triage10_10 > > Derby does not let you GROUP BY an expression which involves a user-defined > function. Technically, I think that the SQL standard forbids grouping by > anything other than a plain column reference. See chapter 2 of the 2003 SQL > standard, section 7.9 <group by clause>. However, I think that many other > databases allow this useful extension. Derby already lets you GROUP BY > expressions involving system functions--this extension was added as part of > DERBY-883. The discussion around that issue raised the concern that you > should only be able to use DETERMINISTIC user-defined functions in GROUP BY > expressions. See > http://www.nabble.com/Functions-in-GROUP-BY-expressions--(related-to-DERBY-883)-td7021186.html > A follow-on email thread pointed out that you can work around this limitation > by putting your expressions inside a subquery and then grouping by a select > from the subquery results: > http://www.nabble.com/User-Defined-Functions-in-a-Group-By-Clause-td21326165.html#a21326165 > Release 10.5 will add the DETERMINISTIC keyword to function declarations, so > now we should be able to allow DETERMINISTIC user-defined functions in GROUP > BY expressions. > We could further relax the current limitation by also allowing > non-DETERMINISTIC functions in GROUP BY expressions. The distinction between > DETERMINISTIC and non-DETERMINISTIC functions does not seem to me to be > rooted in the SQL standard since the standard only allows plain column > references. Using the subquery-workaround mentioned above, you can already > ask for non-deterministic grouped results. However, there may be some > implementation reasons for limiting this extension to DETERMINISTIC functions. > Here is a script showing the issue: > drop table t; > drop function f; > create table t( a int, b int ); > insert into t(a, b) values ( 1, 0 ), ( -1, 1 ), ( -2, 2 ); > create function f > ( > raw int > ) > returns int > language java > parameter style java > deterministic > no sql > external name 'java.lang.Math.abs' > ; > select abs( a ), count(*) > from t > group by abs( a ); > select f( a ), count(*) > from t > group by f( a ); -- This message was sent by Atlassian JIRA (v6.3.4#6332)