Subquery referencing columns of an enclosing polymorphic mapper corrupts the query.

[Former user]

Suppose I start with the schema from my previous bug report:

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

Now I want to extend my database to record more information about children. Since children are intrinsically good or bad and this can't be changed any more than engineers can become managers, I create two new tables:

CREATE TABLE child_good (
  child_good_id INTEGER PRIMARY KEY,
  child_id INTEGER
    CONSTRAINT "is_child" REFERENCES child(child_id),
  christmas_present TEXT NOT NULL);

CREATE TABLE child_bad (
  child_bad_id INTEGER PRIMARY KEY,
  child_id INTEGER CONSTRAINT "is_child" REFERENCES child(child_id),
  punishment TEXT NOT NULL);

I reflect my schema into Python as follows:

import sqlalchemy as sa

testDbs = 'postgres://tester:testing@localhost/test'

db = sa.create_engine(testDbs)
md = sa.BoundMetaData(db)
parent = sa.Table('parent', md, autoload=True)
child = sa.Table('child', md, autoload=True)
child_good = sa.Table('child_good', md, autoload=True)
child_bad = sa.Table('child_bad', md, autoload=True)

Next I create the join I will use to load children:

j = sa.polymorphic_union({
    'good' : pc.child.join(pc.child_good),
    'bad' : pc.child.join(pc.child_bad)
  }, None, 'child_join')

Printing this gives the expected output:

SELECT
 child_bad.child_bad_id,
 CAST(NULL AS TEXT) AS christmas_present,
 child.child_name, child.parent_id,
 child_bad.child_id,
 CAST(NULL AS INTEGER) AS child_good_id,
 child_bad.punishment
FROM child JOIN child_bad
 ON child.child_id = child_bad.child_id

UNION ALL

SELECT CAST(NULL AS INTEGER) AS child_bad_id,
 child_good.christmas_present,
 child.child_name,
 child.parent_id,
 child_good.child_id,
 child_good.child_good_id,
 CAST(NULL AS TEXT) AS punishment
FROM child JOIN child_good
 ON child.child_id = child_good.child_id

So now I can map these tables onto my model objects:

class Child(object): pass
class GoodChild(Child) : pass
class BadChild(Child) : pass
class Parent(object): pass

childMapper = sa.mapper(Child, child, select_table=j, polymorphic_on=child.c.child_name)
sa.mapper(GoodChild, child_good, inherits=childMapper, polymorphic_identity='good')
sa.mapper(BadChild, child_bad, inherits=childMapper, polymorphic_identity='bad')

s = sa.create_session(db)

And I'm all set to do some queries. First, let's list all the children:

s.query(Child).select()

That' fine. But this one explodes:

s.query(Child).select(sa.exists([1](1), Parent.c.parent_id == Child.c.parent_id))

Traceback (most recent call last):  File "<stdin>", line 1, in ?  File "build/bdist.darwin-8.9.0-Power_Macintosh/egg/sqlalchemy/orm/query.py", line 319, in select  File "build/bdist.darwin-8.9.0-Power_Macintosh/egg/sqlalchemy/orm/query.py", line 326, in select_whereclause  File "build/bdist.darwin-8.9.0-Power_Macintosh/egg/sqlalchemy/orm/query.py", line 927, in _select_statement  File "build/bdist.darwin-8.9.0-Power_Macintosh/egg/sqlalchemy/orm/query.py", line 831, in execute  File "build/bdist.darwin-8.9.0-Power_Macintosh/egg/sqlalchemy/orm/session.py", line 183, 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) missing FROM-clause entry in subquery for table "child_join"

The problem is, the SQL it results in references the inner select's alias from within the inner select itself. Here it is:

SELECT child_join.child_bad_id AS child_join_child_bad_id, child_join.christmas_present AS child_join_christmas_present, child_join.child_name AS child_join_child_name, child_join.parent_id AS child_join_parent_id, child_join.child_id AS child_join_child_id, child_join.child_good_id AS child_join_child_good_id, child_join.punishment AS child_join_punishment

FROM (

 SELECT child_bad.child_bad_id AS child_bad_id, CAST(NULL AS TEXT) AS christmas_present, child.child_name AS child_name, child.parent_id AS parent_id, child_bad.child_id AS child_id, CAST(NULL AS INTEGER) AS child_good_id, child_bad.punishment AS punishment
 FROM child JOIN child_bad ON child.child_id = child_join.child_id

 UNION ALL

 SELECT CAST(NULL AS INTEGER) AS child_bad_id, child_good.christmas_present AS christmas_present, child.child_name AS child_name, child.parent_id AS parent_id, child_good.child_id AS child_id, child_good.child_good_id AS child_good_id, CAST(NULL AS TEXT) AS punishment
 FROM child JOIN child_good ON child.child_id = child_join.child_id

) AS child_join

WHERE
 EXISTS (SELECT 1
  FROM parent
  WHERE parent.parent_id = child_join.parent_id)
ORDER BY child_join.child_good_id

But there's definitely something very odd going on here, because if now I re-run the first query again, I get a similar error message:

s.query(Child).select()

Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "build/bdist.darwin-8.9.0-Power_Macintosh/egg/sqlalchemy/orm/query.py", line 319, in select
  File "build/bdist.darwin-8.9.0-Power_Macintosh/egg/sqlalchemy/orm/query.py", line 326, in select_whereclause
  File "build/bdist.darwin-8.9.0-Power_Macintosh/egg/sqlalchemy/orm/query.py", line 927, in _select_statement
  File "build/bdist.darwin-8.9.0-Power_Macintosh/egg/sqlalchemy/orm/query.py", line 831, in execute
  File "build/bdist.darwin-8.9.0-Power_Macintosh/egg/sqlalchemy/orm/session.py", line 183, 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) missing FROM-clause entry in subquery for table "child_join"
 'SELECT child_join.child_bad_id AS child_join_child_bad_id, child_join.christmas_present AS child_join_christmas_present, child_join.child_name AS child_join_child_name, child_join.parent_id AS child_join_parent_id, child_join.child_id AS child_join_child_id, child_join.child_good_id AS child_join_child_good_id, child_join.punishment AS child_join_punishment \nFROM (SELECT child_bad.child_bad_id AS child_bad_id, CAST(NULL AS TEXT) AS christmas_present, child.child_name AS child_name, child.parent_id AS parent_id, child_bad.child_id AS child_id, CAST(NULL AS INTEGER) AS child_good_id, child_bad.punishment AS punishment \nFROM child JOIN child_bad ON child.child_id = child_join.child_id UNION ALL SELECT CAST(NULL AS INTEGER) AS child_bad_id, child_good.christmas_present AS christmas_present, child.child_name AS child_name, child.parent_id AS parent_id, child_good.child_id AS child_id, child_good.child_good_id AS child_good_id, CAST(NULL AS TEXT) AS punishment \nFROM child JOIN child_good ON child.child_id = child_join.child_id) AS child_join ORDER BY child_join.child_good_id' {}

[Mike Bayer]

the issue is that by creating a WHERE criterion that is essentially:

exists([1](1), parent.c.parent_id == j.c.parent_id)

the polymorphic union "j" is fully embedded into a subquery, and should be correlated to an enclosing query that is also against "j". but Query processes the WHERE criterion first, visits all the binary clauses (like colx=coly) and converts all occurrences of columns that correspond to the external "columns" list of the mapped table (i.e. j) into that external column...in this case child_good.child_id and child_bad.child_id in the ON clauses become child_join.child_id. i.e. it expects that all the binary expressions in the WHERE criterion are ad-hoc and can be modified, and did not expect that it would hit binary expressions that are part of the ON clauses of "j".

so, this issue is timely in that the idea of query "adaption" is what I'm working on anyway, where the "adaption" logic that modifies the binary clauses will soon be expanded to modify any kind of ClauseElement it finds (since we need that for other situations). as such i just yesterday enhanced ClauseVisitor so that it can "stop" traversing at any arbitrary point, in this case it needs to "stop" when it hits "j"...and thats whats in changeset:2621