Re: SQL feature requests

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?

> 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
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 , , , ;

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.

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.

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:

test=# select * from observation;
observation_id | record_id | score_id
----------------+-----------+----------
3240 | 1 | 1
3239 | 1 | 1
3238 | 1 | 2
3237 | 1 | 1
2872 | 2 | 1
2869 | 2 | 2
2870 | 2 | 1
2871 | 2 | 1
3218 | 3 | 2
3217 | 3 | 1
(10 rows)

test=# select record_id as foo, count(observation_id) from
observation group by record_id;
foo | count
-----+-------
3 | 2
2 | 4
1 | 4
(3 rows)

test=# select record_id as foo, count(observation_id) from
observation group by foo;
foo | count
-----+-------
3 | 2
2 | 4
1 | 4
(3 rows)

test=# select record_id as foo, count(observation_id) as bar from
observation group by foo;
foo | bar
-----+-----
3 | 2
2 | 4
1 | 4
(3 rows)

test=# select record_id as foo, count(observation_id) as bar from
observation group by record_id;
foo | bar
-----+-----
3 | 2
2 | 4
1 | 4
(3 rows)

test=# select version();

version
------------------------------------------------------------------------
----------------------------------------------------------------------
PostgreSQL 8.2.4 on powerpc-apple-darwin8.9.0, compiled by GCC
powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc.
build 5367)
(1 row)

I'm not getting an error in any permutation that I can think of. What
am I missing?

> 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'm not trying to dismiss your points, just trying to address them.
I'm interested to hear what others have to say.

Michael Glaesemann
grzm seespotcode net