Re: SQL feature requests

Ben,

pgsql-sql is probably the appropriate list for future queries of this
nature.

Note that the below is my personal opinion; each PG developer has their
own.

> 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:

I *think* the alias is a requirement of the SQL standard. Yes/No?

> 2. Why is 'non-integer constant in GROUP BY' an error?

Again, this needs to reference one of the SQL standards if you're
interested in a change of behavior. If we're out of standards compliance,
that's a strong argument. If we're in compliance, you have a pretty steep
hurdle to justify new syntax.

> 3. How hard would it be to have postgres ignore aliases in group by
> clauses?

Unfortunately, I think this is also a SQL compliance issue. However, I'd
be more liable to support your arguments for it; it's much more obviously
useful functionality.

> 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.

In addition to SQL compliance issues, we're reluctant to do anything which
makes implicit assumptions which could easily be wrong in PostgreSQL.
Such shortcutting all to often leads to runaway queries or wrong data when
the assumptions are incorrect. MySQL gives us lots of examples of what
can happen if you do too many things for convenience and compromise
correctness.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco