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 <[EMAIL PROTECTED]> 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.
