db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Francesco Foresti (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-4003) Allow user-defined functions in GROUP BY expressions
Date Fri, 29 May 2015 08:08:18 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4003?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14564385#comment-14564385

Francesco Foresti commented on DERBY-4003:

Hi everyone. I know it's not the right place, but anyway.. Where I work we have Oracle db
in production, and I have a query that does 'group by TRUNC(...)'; We are doing our unit tests
using Derby (with Arquillian), and I thought I could create a custom TRUNC function that mimics
Oracle in order to run the tests. So I ran into this issue.. Do you know how i could reproduce
the same behavior of Oracle's TRUNC without using a custom function? We have Derby
Thanks in advance.

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

View raw message