Labelling a subquery causes it to appear in the FROM clause

Suppose I have created a simple schema (in a Postgres database, as it happens):

CREATE TABLE parent (
  parent_id INTEGER PRIMARY KEY,
  parent_name TEXT NOT NULL
);

CREATE TABLE child (
  child_id INTEGER PRIMARY KEY,
  parent_id INTEGER
    CONSTRAINT has_parent
    REFERENCES parent (parent_id),
  child_name TEXT NOT NULL
);

I've also populated it with a few rows:

INSERT INTO parent VALUES (1, 'Mother');
INSERT INTO parent VALUES (2, 'Maiden Aunt');
INSERT INTO child VALUES (1, 1, 'Daughter');

Now I'd like to retrieve a list of parents, along with whether they have children. In SQL the following query works:

SELECT
  parent_id,
  parent_name,
  EXISTS (
    SELECT 1
    FROM child
    WHERE child.parent_id = parent.parent_id
    ) AS has_children
  FROM parent;

I get this output:

 parent_id | parent_name | has_children 
-----------+-------------+--------------
         1 | Mother      | t
         2 | Maiden Aunt | f
(2 rows)

Now I want to generate the same query from Python, so I try the following:

# Setup
import sqlalchemy as sa
db = sa.create_engine('postgres://tester:testing@localhost/test')
md = sa.BoundMetaData(db)
parent = sa.Table('parent', md, autoload=True)
child = sa.Table('child', md, autoload=True)
# Create a predicate to use as a subquery.
has_children = sa.exists(
  [1](1),
  child.c.parent_id == parent.c.parent_id,
  correlate=True)
# Query without label on predicate
query1 = sa.select([has_children](parent,))
print query1
# Query with label on predicate
query2 = sa.select([has_children.label('has_children')](parent,))
print query2 
db.execute(query)

Printing query1 gives me this:

SELECT parent.parent_id, parent.parent_name, EXISTS (SELECT 1 
FROM child 
WHERE child.parent_id = parent.parent_id) 
FROM parent

But printing query2 gives the following:

SELECT parent.parent_id, parent.parent_name, (EXISTS (SELECT 1 
FROM child 
WHERE child.parent_id = parent.parent_id)) AS has_children 
FROM parent, (SELECT 1 
FROM child 
WHERE child.parent_id = parent.parent_id)

As you can see, labelling the subquery has had a side-effect of including it in the FROM clause, which it shouldn't do. Unfortunately this upsets Postgres, which complains thus:

Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "build/bdist.darwin-8.9.0-Power_Macintosh/egg/sqlalchemy/engine/base.py", line 765, in execute
  File "build/bdist.darwin-8.9.0-Power_Macintosh/egg/sqlalchemy/engine/base.py", line 509, in execute
  File "build/bdist.darwin-8.9.0-Power_Macintosh/egg/sqlalchemy/engine/base.py", line 549, in execute_clauseelement
  File "build/bdist.darwin-8.9.0-Power_Macintosh/egg/sqlalchemy/engine/base.py", line 560, in execute_compiled
  File "build/bdist.darwin-8.9.0-Power_Macintosh/egg/sqlalchemy/engine/base.py", line 573, in _execute_raw
  File "build/bdist.darwin-8.9.0-Power_Macintosh/egg/sqlalchemy/engine/base.py", line 591, in _execute
sqlalchemy.exceptions.SQLError: (ProgrammingError) subquery in FROM must have an alias
HINT:  For example, FROM (SELECT ...) [AS](AS) foo.

Comments (5)