db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@apache.org>
Subject Re: Functions in GROUP BY expressions? (related to DERBY-883)
Date Fri, 27 Oct 2006 15:49:42 GMT
Army wrote:
> Bernt M. Johnsen wrote:
>>>> [...]
>>>> // Doesn't work but seems like it could/should based on above 
>>>> queries...
>>>
>>> ij> select sin(i) from t1 group by sin(i);
>>> ERROR 42Y30: The SELECT list of a grouped query contains at least one 
>>> invalid expression. If a SELECT list has a GROUP BY, the list may 
>>> only contain valid grouping expressions and valid aggregate expressions.
>>> [...]
>>
>> To me it seems natural that this one doesn't work. To group by double
>> or float values is not reasonable.
> 
> Thank you for the reply, Bernt.  Perhaps this is the detail I was 
> missing.  When you say "is not reasonable", can you elaborate on why 
> that's the case (excuse me if that's a naive question)?  Is it because 
> the types are imprecise?
> 
> I did notice that following works:
> 
> ij> create table td (d double);
> 0 rows inserted/updated/deleted
> ij> insert into td values 2.08, 3.08;
> 2 rows inserted/updated/deleted
> ij> select d from td group by d;
> D
> ----------------------
> 2.08
> 3.08
> 
> If it is not reasonable to group by a double, then should this query 
> work?  Or should we be throwing the same error that we throw for "sin(i)"?

No, you are correct Army, the grouping by functions does not work 
because at the moment Derby assumes all functions are non-deterministic. 
Grouping by DOUBLE should be allowed since they can be compared. It's 
probably not advisable, but Derby's SQL should allow it.

Dan.


Mime
View raw message