Re: SQL feature requests

On 8/23/07, Florian G. Pflug <fgp@phlo.org> wrote:
> Ben Tilly wrote:
> > 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.
>
> Which is the same very clear meaning that "group by 1" has - we're
> grouping on a expression which happens to be the constant 1. Hey,
> wait a second. This isn't what "group by 1" means at all - it
> rather means group by whatever the fist column in the select list is.

Which feature shocked me when I first saw it in Oracle.  It violated
every expectation that I have.

I also deliberately do NOT use that feature.  Because it is not safe
if someone else is possibly going to edit your query.  Add a field in
a natural place and, oops, your query just broke.  Also I hate
referring to things by position in code.  Particularly when they are
far away from each other as they may be in a large query.  (I've
written queries that are over 1000 lines long in the past.)

> So, yes, "group by 'foo'" *seems* to have a very clear meaning - but
> that clearness vanishes as soon as you take into account what "group by 1"
> means.

I'm happy to use "group by 'foo'::text" instead.  Anyone else in my
position will have to stumble on their own solution, but I don't think
there are that many in my position.

Cheers,
Ben