From: | "Ben Tilly" <btilly(at)gmail(dot)com> |
---|---|
To: | "Gregory Stark" <stark(at)enterprisedb(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: SQL feature requests |
Date: | 2007-08-23 01:49:50 |
Message-ID: | acc274b30708221849y66f5dc01qac0e4e7b412ae24c@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 8/22/07, Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
>
> "Ben Tilly" <btilly(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Florian G. Pflug | 2007-08-23 02:31:47 | Re: SQL feature requests |
Previous Message | Ben Tilly | 2007-08-23 01:36:15 | Re: SQL feature requests |