db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] Created: (DERBY-4003) Allow user-defined functions in GROUP BY expressions
Date Wed, 07 Jan 2009 15:16:44 GMT
Allow user-defined functions in GROUP BY expressions

                 Key: DERBY-4003
                 URL: https://issues.apache.org/jira/browse/DERBY-4003
             Project: Derby
          Issue Type: New Feature
          Components: SQL
    Affects Versions:
            Reporter: Rick Hillegas

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
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 is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message