Hi Kim and Knut,
Some comments inline...
Knut Anders Hatlen wrote:
Kim Moore <[email protected]> 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
