db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <de...@segel.com>
Subject RE: User Defined Functions in a Group By Clause
Date Wed, 07 Jan 2009 16:55:51 GMT
Not to be a 'Debbie Downer' but I have some reservations.

Knut points out that there is a viable work around. (Wrap the query)

Currently Derby/JavaDB doesn't have any way of creating a scalable foot

As more and more features/enhancements are added to Derby/JavaDB, you're
going to see an increase in the size of the engine's footprint. While this
may not be an issue for developers who want a full featured java based
relational engine, it does have an impact on the embedded developer who
won't use or see value to some of these enhancements.

My suggestion is that before looking to add more features, those who are in
charge of architecting derby/javadb consider a modification that would allow
for the ability to have a scalable foot print. 

An example... Suppose one wanted to allow Derby to use raw disk partitions
and also allow the use of data partitioned tables. These would have extreme
value for the developer who wants a java relational engine, while it would
have no value, but a cost, to the developer who wants to embed derby/javadb.

Does this make sense?


PS. Yeah I know it's easy to sit in the cheap seats and toss out
suggestions. But because it has commercial value/impact, I would defer this
request to those 'boffins' at Sun who are paid to support JavaDB to think
this through. I would have included IBM but they've seemed to have
disappeared when IBM dropped Cloudscape support. ;-)

> -----Original Message-----
> From: Richard.Hillegas@Sun.COM [mailto:Richard.Hillegas@Sun.COM]
> Sent: Wednesday, January 07, 2009 9:22 AM
> To: Derby Discussion
> Subject: Re: User Defined Functions in a Group By Clause
> 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

View raw message