db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: Functions in GROUP BY expressions? (related to DERBY-883)
Date Tue, 31 Oct 2006 09:49:46 GMT
>>>>>>>>>>>> Daniel John Debrunner wrote (2006-10-27 08:49:42):
> 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 is right. A double is just a finite set of bits (I was thinking
"too mathematical") so it should work, even if it is not the most
reasonable thing to do.

-- 
Bernt Marius Johnsen, Database Technology Group, 
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway

Mime
View raw message