db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: User Defined Functions in a Group By Clause
Date Wed, 07 Jan 2009 15:22:05 GMT
Hi Kim and Knut,

Some comments inline...

Knut Anders Hatlen wrote:
> Kim Moore <kmoore@google.com> writes:
>
>   
>> I am working on a query that uses a user defined function in a group
>> by clause.
>>
>> select myfunction (datecolumn)
>>          ,count(*)
>> from    table
>> group by myfunction (datecolumn)
>>
>> Executing the query gives the error "The SELECT list of a group query
>> contains at least one invalid expression."
>>
>> When I replace myfunction (user defined function) with a DATE function
>> which comes standard with Derby, the query works.
>>
>> All help is greatly appreciated.
>>     
>
> I think this is because user-defined functions are not known to be
> deterministic. See this discussion for more details:
> http://www.nabble.com/Functions-in-GROUP-BY-expressions--%28related-to-DERBY-883%29-tf2517296.html
>
> Derby 10.5 will support the DETERMINISTIC keyword in CREATE FUNCTION
> statements (https://issues.apache.org/jira/browse/DERBY-3570), but I
> haven't tested if that will actually allow you to use user-defined
> functions in GROUP BY.
>   
Nothing was done to relax the limitation on user-defined functions in 
GROUP BY expressions. Even if you declare the function to be 
DETERMINISTIC, the query will fail.
> Putting the function call in a subquery and renaming the column holding
> the value returned by the function should work even without the
> DETERMINISTIC keyword, though:
>
> select x, count(*) from
>   (select myfunction(datecolumn) from mytable) t(x)
> group by x
>
>   
I agree that it would be reasonable to allow user-defined functions in 
GROUP BY expressions. I have logged DERBY-4003 to track this issue. That 
JIRA would be a good place to continue the discussion about whether we 
should limit this extension to DETERMINISTIC functions. The limitation 
does not seem to me to be rooted in the SQL standard, but there may be 
some good implementation-related reasons for maintaining it.

Regards,
-Rick

Mime
View raw message