db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: User Defined Functions in a Group By Clause
Date Wed, 07 Jan 2009 08:42:27 GMT
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.

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

-- 
Knut Anders

Mime
View raw message