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