Re: SQL feature requests

On 8/22/07, Michael Glaesemann <grzm@seespotcode.net> wrote:
>
> On Aug 22, 2007, at 20:49 , Ben Tilly wrote:
>
> > If your implementation accepts:
> >
> >   group by case when true then 'foo' end
>
> What would that mean? Regardless of whether or not it's accepted, it
> should have *some* meaning.

To my eyes it has a very clear meaning, we're grouping on an
expression that happens to be a constant.  Which happens to be the
same for all rows.  Which is a spectacularly useless thing to actually
do, but the ability to do it happens to be convenient when I'm looking
for something to terminate a series of commas in a dynamically built
query.

> It's not equivalent to GROUP BY "foo"

I wouldn't want it to be.  Strings and identifiers are very different things.

[...]
> *This* seems like a bug:
> test=# select record_id
>         , count(observation_id) as bar
>         from observation
>         group by record_id
>             , case when true
>                    then 'foo'
>               end;
> record_id | bar
> -----------+-----
>           1 |   4
>           2 |   4
>           3 |   2
> (3 rows)

Why does it seem like a bug to you?

Turn it around, and tell me in what way is its behaviour surprising to
someone who knows SQL.  You asked to group on something that is the
same for all rows.  That group by condition did nothing.  (Except
rendered the syntax valid when it might not have been.)  As I would
expect.

Furthermore ask yourself whether anyone who wrote that would likely
have written it by accident.

Cheers,
Ben