PostgreSQL: subqueries and alias

[Manlio Perillo]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi.

I have noted that with this query:

# query is the original query
# query_aux is the query required to compute the number of rows returned
# by the original query
query_aux = sql.select(
[sql.func.count()], from_obj=[query])

I get, with PostgreSQL 8.3.7 (and SQLALchemy from trunk):

ProgrammingError: (ProgrammingError) subquery in FROM must have an alias
HINT: For example, FROM (SELECT ...) [AS] foo

The solution is to explicitly add an alias
query_aux = sql.select(
[sql.func.count()], from_obj=[query.alias('subquery')])

However IMHO this should be done by SQLALchemy, and not by the programmer.

What do you think?

Thanks Manlio Perillo

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkoByRkACgkQscQJ24LbaUQz8QCeIrSG2o+XeiZEi1PUY7RmbGG3
148An39JLOw/KRnffUcKknmF8aQQnZmh
=hiSI
-----END PGP SIGNATURE-----

[Michael Bayer]

the implicit behavior might lead to confusion later on. such as:

from sqlalchemy import *

from sqlalchemy.sql import table, column

t1 = table("t1", column("c1"), column("c2"))
t2 = table("t2", column("c1"), column("c2"))

s1 = select([t1, t2], use_labels=True)
s2 = select([t1, t2], use_labels=True).alias()

print select([s1.c.t1_c1, s1.c.t1_c2])
print select([s2.c.t1_c1, s2.c.t1_c2])

s3 = select([s1.c.t1_c1, s1.c.t1_c2], use_labels=True).alias()
s4 = select([s2.c.t1_c1, s2.c.t1_c2], use_labels=True).alias()

print s3.c.t1_c1

print s4.c.t1_c1 # error - the columns are anonymously named