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

View raw message