Re: SQL feature requests - Mailing list pgsql-hackers

On 8/22/07, Gregory Stark <stark@enterprisedb.com> wrote:
>
> "Ben Tilly" <btilly@gmail.com> writes:
>
> > Hopefully this is the right place for a few feature requests that
> > would address some of the things that I've noticed in postgres.
> >
> > 1. Just a minor annoyance, but why must subqueries in FROM clauses
> > have an alias?  For instance suppose that I have an orders table, and
> > one of the fields is userid.  The following is unambiguous and is
> > legal in Oracle:
>
> Thank you, this is one of my top pet peeves but when I proposed changing it I
> was told nobody's complained. Now we have at least one user complaint, any
> others out there?

Always happy to complain. :-)

> > 2. Why is 'non-integer constant in GROUP BY' an error?
>
> Hm... I was a bit surprised by this warning myself. IIRC there was an
> implementation convenience issue.

If your implementation accepts:
 group by case when true then 'foo' end

how much harder can it be to accept:
 group by 'foo'

?

> > 3. How hard would it be to have postgres ignore aliases in group by
> > clauses?
>
> That sounds like a strange idea.

It is a strange idea, but it makes dynamically building queries
easier.  Right now I'm following a strategy of storing what I'm going
to insert in the select clause in one variable, and the group by
clause in another.  So I need 2 variables for each dynamic field that
I might choose to group by and want to have a custom name for.  With
this change I would only need one variable.

> > 4) Items 2 and 3 would both be made irrelevant if postgres did
> > something that I'd really, really would like.  Which is to assume that
> > a query without a group by clause, but with an aggregate function in
> > the select, should have an implicit group by clause where you group by
> > all non-aggregate functions in the select.
> >
> > For example
> >
> >   SELECT foo, count(*)
> >   FROM bar
> >
> > would be processed as:
> >
> >   SELECT foo, count(*)
> >   FROM bar
> >   GROUP BY foo
>
> I agree this would be convenient but it seems too scary to actually go
> anywhere. What would you group by in the case of:
>
> SELECT a+b, count(*) FROM bar
>
> Should it group by a,b or a+b ?

It should group by a+b.  Which is to say, every field in the select
clause that currently triggers an error because it isn't in the group
by clause.

> Also, this might be a bit shocking for MySQL users who are accustomed to
> MySQL's non-standard extension for the same syntax. There it's treated as an
> assertion that the columns are equal for all records in a group or at least
> that it doesn't matter which such value is returned, effectively equivalent to
> our DISTINCT ON feature.

I don't mind shocking MySQL users. ;-)

But seriously, if that objection is the barrier then I'd be happy to
see it be something that is explicitly turned on in the query.  For
instance:
 select autogroup bar, count(*) from foo

If that was available then I for one would type autogroup a lot more
often than group by.  After all autogroup is about as hard to type,
and I don't have to type the redundant list of fields in the group by.

Cheers,
Ben