[
https://issues.apache.org/jira/browse/DERBY-4003?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14564385#comment-14564385
]
Francesco Foresti edited comment on DERBY-4003 at 5/29/15 8:08 AM:
-------------------------------------------------------------------
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 can I reproduce the same behavior of
Oracle's TRUNC without using a custom function? We have Derby 10.8.1.2. Thanks
in advance.
was (Author: francesco.foresti):
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 10.8.1.2. 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: 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)
