One last comment (and I apologize for flooding the list): it looks like this was discussed extensively in 2006. See http://markmail.org/message/pggvaqh6ejg2dtf3. But it does not appear that the issue was ever resolved.
On 3/4/09 1:25 PM, "Matt Solnit" <firstname.lastname@example.org> wrote:
P.S. I forgot to include the following:
Derby version 10.4.2.0
Java version 1.5.0_16
Mac OS X 10.5.6 (32-bit)
On 3/4/09 1:22 PM, "Matt Solnit" <email@example.com> wrote:
> Hi all. I'm trying to use GROUP BY with Derby, and it looks like certain
> functions work and certain ones don't.
> For example, the following:
> ij> CREATE TABLE foo (a INT);
> ij> SELECT ABS(a) FROM foo GROUP BY ABS(a);
> works fine. But if I use CEIL or FLOOR, I get an error:
> ij> SELECT CEIL(a) FROM foo GROUP BY CEIL(a);
> ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid
> If a SELECT list has a GROUP BY, the list may only contain valid grouping
> expressions and
> valid aggregate expressions.
> I'm not sure what the difference is, but I came across DERBY-592 (JDBC-escaped
> functions) and I'm wondering if this is part of the issue.
> Am I doing something incorrectly, or is this a bug?
> -- Matt