> > ij> SELECT ABS(a) FROM foo GROUP BY ABS(a); > > > > works fine. But if I use CEIL or FLOOR, I get an error:
> I'm truly speculating here, but I wonder if this behavior is > because CEIL and FLOOR return floating point values, and therefore > have a certain amount of inexactness in them, which is unacceptable > for GROUP BY processing? Hi Bryan. Thanks for your response. This precisely one of the subjects that were debated in the 2006 thread (http://markmail.org/message/pggvaqh6ejg2dtf3). Here are some snippets: Daniel Debrunner: "Grouping by DOUBLE should be allowed since they can be compared. It's probably not advisable, but Derby's SQL should allow it." Bernt Johnsen: "A double is just a finite set of bits ... so it should work, even if it is not the most reasonable thing to do." And of course, it works fine in MySQL and PostgreSQL ;-). > I'm afraid all I can really do is to confirm that I see the > same behavior that you see, and it doesn't appear to be random, > but rather is intentional, and has to do with the fact that CEIL > and FLOOR are implemented differently than ABS. What's next? Should I submit an issue to JIRA? > bryan
