db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Manish Khettry" <manish.khet...@gmail.com>
Subject Re: Functions in GROUP BY expressions? (related to DERBY-883)
Date Fri, 27 Oct 2006 20:31:41 GMT
The compilation error with functions like sin are expected. Anytime Derby
sees a JavaValue node, it gives up trying to compare subexpressions for
equivalence.

Regarding constant expressions:
   // Works but I don't think it should??  Perhaps a problem with
   // constant expressions...?
   ij> select 'i' || 'j' from t1 group by 'h' || 'h;

group by with a constant expression results in a single group and since the
expression in the select clause ('i' || 'j') contains no column refrences
this is a valid query. It works in mysql as well with the same results.

m

On 10/27/06, Daniel John Debrunner <djd@apache.org> wrote:
>
> 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