Re: SQL feature requests - Mailing list pgsql-hackers

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