Re: SQL feature requests - Mailing list pgsql-hackers

On 8/22/07, Michael Glaesemann <grzm@seespotcode.net> wrote:
>
> On Aug 22, 2007, at 18:45 , Ben Tilly wrote:
>
> > 1. Just a minor annoyance, but why must subqueries in FROM clauses
> > have an alias?
>
> It's required by the SQL standard, AIUI. I wonder what EnterpriseDB
> does?

I can well believe that the standard says that you must accept
subqueries with aliases.  But does it say you must reject subqueries
without aliases?  I strongly doubt that.

I have no clue what EnterpriseDB does.

> > 2. Why is 'non-integer constant in GROUP BY' an error?
>
> >  This works for now:
> >
> >   case when true then true end
> >
> > but I don't know whether some future version of postgres might break
> > my code by banning that as well.
>
> The PostgreSQL developers generally tries hard to preserve backwards
> compatibility, so I doubt the case expression as you have it would go
> away (though I'm kind of surprised it's allowed). Am I wrong in
> thinking that Oracle would accept the same format PostgreSQL does? In

The reason for my comparing to Oracle is that I used to work at an
Oracle shop.  I now work at a postgres shop.  Portability is not my
issue, just the annoyances that I experienced moving from one to the
other.

As for whether that case expression would go away, that it is allowed
is such an obscure feature that I doubt anyone changing that code
would notice if it was removed.

> that case, couldn't you use whatever method works in PostgreSQL in
> Oracle? I haven't checked the SQL standard, but it seems unlikely
> it'd allow something like
>
> GROUP BY , , , ;

That's not what Oracle accepts  that postgres does not.  What Oracle accepts is:
 ... GROUP BY 'foo';

> AIUI, Integers are only allowed because the SQL standard explicitly
> allows you to refer to columns by the order they appear in the SELECT
> list. Otherwise the GROUP BY items need to be column names.

Need to be?

The SQL-92 standard is clear that you must accept a list of column
names.  It is also clear that a column name must be be of the form
field or table.field.  In no way, shape or form does that allow having
terms like trim(foo.bar) in a group by.

But every reasonable database that I know - including postgres - allows that.

The standard very wisely does not forbid extensions.  Every database
has extensions.  In some cases, such as allowing trim(foo.bar) in a
group by clause, some extensions are so common as to be a standard.
(I don't have a copy of any later standards so I don't know whether
that has since been explicitly allowed.)  Therefore the real question
is how much farther than the standard you go.

Postgres explicitly disallows a constant character expression.  But it
allows the constant case expression that I gave.  It would be nice for
me to not have to remember that very obscure and convoluted case.

> Both 1 and 2 seem to me to be places where Oracle is likely deviating
> from the standard. If you're targeting Oracle, then using Oracle-
> specific syntax might be warranted. If you're hoping to target more
> than one possible backend, I'd think it be better to use more
> portable syntax (e.g., SQL-standard syntax) than expecting other
> DBMSs to follow another's deviations. That's not to say PostgreSQL
> does not have non-standard syntax: in places, it does. But it does
> try to hew very closely to the standard.

The queries that I'm writing are not hoping to target more than one
database at one company.

> Again, I wonder what EnterpriseDB does in this case?

No clue.

> > 3. How hard would it be to have postgres ignore aliases in group by
> > clauses?  Per my comments above, I often build complex queries in
> > code.  I can't easily use the shortcut of referring to the select
> > column by number because the position is hard to determine.  So my
> > code has to copy the select terms.  But I can't copy them exactly
> > because the select terms include lots of "...as foo" clauses that are
> > not allowed in a group by.  So I have to store very similar terms to
> > use twice.
>
> Perhaps someone else knows what you're referring to here, but I'm
> having a hard time without an example. Here's what I *think* you're
> trying to say:

What I'm trying to say is that it would be convenient for me to be
able to write:
 select bar as "baz"   , count(*) as "some count" from foo group by bar as "baz"

That's not allowed right now because as is not allowed in a group by statement.

[...]
> > 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
>
> It's been discussed before. I don't believe it's been rejected out of
> hand (though you can check the archives), just that no one's gotten
> around to it. (Don't know what the SQL-spec says on this point.)

I don't know what the SQL spec says, but I know (having talked to
other developers) that many people would find it very nice.

> I'm not trying to dismiss your points, just trying to address them.
> I'm interested to hear what others have to say.

Cheers,
Ben