On 8/22/07, Michael Glaesemann <[EMAIL PROTECTED]> 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
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
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?
> > 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"
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.
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?