subqueryload_all() generates invalid query: a column is referenced from a table that is not joined

[Former user]

See the attached test case. Tested with SQLA 0.6.5 and the current rel_0_6 branch. Removing the subqueryload_all() makes things work.

[Former user]

Test case

[Mike Bayer]

some notes on this.

simplest form of the issue:

#! /usr/bin/env python

from sqlalchemy import (create_engine, Table, Column, Integer,
                        ForeignKey)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (Session, relation, subqueryload_all)

engine = create_engine('sqlite://', echo=True)
session = Session(engine)

Base = declarative_base()

class C(Base):
    __tablename__ = "c"

    id = Column(Integer, primary_key=True)

class BSuper(Base):
    __tablename__ = "bsuper"

    id = Column(Integer, primary_key=True)
    c_id = Column(Integer, ForeignKey("c.id"), nullable=False)
    c = relation(C)

class BSub(BSuper):
    __tablename__ = "bsub"

    id = Column(Integer, ForeignKey('bsuper.id'), primary_key=True)
    a_id = Column(Integer, ForeignKey('a.id'), nullable=False)

class A(Base):
    __tablename__ = "a"

    id = Column(Integer, primary_key=True)
    bs = relation(BSub)

Base.metadata.create_all(engine)

a  = A(bs=[BSub(c=C())](BSub(c=C())))
session.add(a)
session.commit()

q = session.query(A).options(subqueryload_all("bs.c")).all()

then, the actual mis-representation of the join from within strategies.py is due to the failure of this statement:

a1 = aliased(A)
b1 = aliased(BSuper)
session.query(a1).join((b1, a1.bs)).join(b1.c).all()

which renders:

SELECT a_1.id AS a_1_id FROM a AS a_1 JOIN bsuper AS bsuper_1 ON a_1.id = bsub.a_id JOIN c ON c.id = bsuper_1.c_id

so this is a conflict between the philosophy of query.join() with aliased() constructs, which is to not mess with the target, and the way subqueryload is using query.join().

[Mike Bayer]

here's the patch if you're impatient:

diff -r e46301b5154000148155ef00d15b35857ebb31ad lib/sqlalchemy/orm/strategies.py
--- a/lib/sqlalchemy/orm/strategies.py  Tue Jan 04 13:48:46 2011 -0500
+++ b/lib/sqlalchemy/orm/strategies.py  Thu Jan 06 12:03:50 2011 -0500
@@ -746,9 +746,23 @@
                     for i in xrange(0, len(subq_path), 2)
                 ]

+        # determine the immediate parent class we are joining from,
+        # which needs to be aliased.
+
         if len(to_join) < 2:
+            # in the case of a one level eager load, this is the
+            # leftmost "left_alias".
             parent_alias = left_alias
+        elif subq_path[-2](-2).isa(self.parent):
+            # In the case of multiple levels, retrieve
+            # it from subq_path[-2](-2). This is the same as self.parent 
+            # in the vast majority of cases, and #2014 
+            # illustrates a case where sub_path[-2](-2) is a subclass
+            # of self.parent
+            parent_alias = mapperutil.AliasedClass(subq_path[-2](-2))
         else:
+            # if of_type() were used leading to this relationship, 
+            # self.parent is more specific than subq_path[-2](-2)
             parent_alias = mapperutil.AliasedClass(self.parent)

         local_cols, remote_cols = \

[Mike Bayer]

9f3b42cbce9db3592961b375dd14f6701a37f7cf (0.7) 07091da10fc277e39f484da7c40ae9ce1adf7d2b (0.6)

[Former user]

The patch works, thanks for the quick response.

[Mike Bayer]

Removing milestone: 0.6.6 (automated comment)