NoSuchColumnError when using a sql statement as the polymorphic_on and loading a relationship via joinedload

[sjhewitt]

Test Case

from sqlalchemy import *
from sqlalchemy import types
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(Base):
    __tablename__ = 'a'

    id = Column(types.Integer, primary_key=True)
    discriminator = Column(types.String(50), nullable=False)
    child_id = Column(types.Integer)
    child = relationship('A', primaryjoin='foreign(A.child_id) == remote(A.id)')

    __mapper_args__ = {
        'polymorphic_identity': 'a',
        "polymorphic_on": case([
            (discriminator == "a", "a"),
        ], else_="b"),
    }


class B(A):
    __mapper_args__ = {
        'polymorphic_identity': 'b'
    }


e = create_engine('sqlite:///:memory:', echo='debug')
Base.metadata.drop_all(e)
Base.metadata.create_all(e)

session = Session(e, autoflush=True, autocommit=False)

session.add_all([
    A(id=1, discriminator='a'),
    A(id=2, discriminator='b', child_id=1),
    A(id=3, discriminator='c', child_id=1),
])
session.commit()


session.query(A).options(joinedload('child')).all()

Error:

2016-09-19 15:03:31,801 INFO sqlalchemy.engine.base.Engine SELECT a.id AS a_id, a.discriminator AS a_discriminator, a.child_id AS a_child_id, CASE WHEN (a.discriminator = ?) THEN ? ELSE ? END AS _sa_polymorphic_on, a_1.id AS a_1_id, a_1.discriminator AS a_1_discriminator, a_1.child_id AS a_1_child_id, CASE WHEN (a_1.discriminator = ?) THEN ? ELSE ? END AS anon_1
FROM a LEFT OUTER JOIN a AS a_1 ON a.child_id = a_1.id
2016-09-19 15:03:31,801 INFO sqlalchemy.engine.base.Engine ('a', 'a', 'b', 'a', 'a', 'b')
2016-09-19 15:03:31,801 DEBUG sqlalchemy.engine.base.Engine Col ('a_id', 'a_discriminator', 'a_child_id', '_sa_polymorphic_on', 'a_1_id', 'a_1_discriminator', 'a_1_child_id', 'anon_1')
2016-09-19 15:03:31,802 DEBUG sqlalchemy.engine.base.Engine Row (1, u'a', None, u'a', None, None, None, u'b')
2016-09-19 15:03:31,802 DEBUG sqlalchemy.engine.base.Engine Row (2, u'b', 1, u'b', 1, u'a', None, u'a')
2016-09-19 15:03:31,802 DEBUG sqlalchemy.engine.base.Engine Row (3, u'c', 1, u'b', 1, u'a', None, u'a')
Traceback (most recent call last):
  File "sqla_test.py", line 45, in <module>
    session.query(A).options(joinedload('child')).all()
  File "env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2613, in all
    return list(self)
  File "env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 86, in instances
    util.raise_from_cause(err)
  File "env/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 202, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 71, in instances
    rows = [proc(row) for row in fetch]
  File "env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 597, in polymorphic_instance
    return instance_fn(row)
  File "env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 428, in _instance
    loaded_instance, populate_existing, populators)
  File "env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 497, in _populate_full
    populator(state, dict_, row)
  File "env/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 1586, in load_scalar_from_joined_new_row
    dict_[key] = _instance(row)
  File "env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 592, in polymorphic_instance
    discriminator = row[polymorphic_on]
  File "env/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 408, in _key_fallback
    expression._string_or_unprintable(key))
sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column 'CASE WHEN (a_1.discriminator = :param_1) THEN :param_2 ELSE :param_3 END'"

It seems that the _sa_polymorphic_on column for the joined relationship gets named anon1 and then cannot be found in the row

[Mike Bayer]

well, do it like this so your expression is a first class mapping:

class A(Base):
    __tablename__ = 'a'

    id = Column(types.Integer, primary_key=True)
    discriminator = Column(types.String(50), nullable=False)
    child_id = Column(types.Integer)
    child = relationship('A', primaryjoin='foreign(A.child_id) == remote(A.id)')

    p_on = column_property(
        case([
            (discriminator == "a", "a"),
        ], else_="b"))

    __mapper_args__ = {
        'polymorphic_identity': 'a',
        "polymorphic_on": p_on,
    }

I need to go back and see what the guidance is on freestanding "case()" for polymorphic_on in the docs. seems to work for all the other loaders including subqueryload.

[Mike Bayer]

okey doke seems like the docs include the case() freestanding in polymorphic_on

[sjhewitt]

ah thanks, changing it to a first class mapping seems to work

[Mike Bayer]

OK in the bad case:

/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/loading.py(265)_setup_entity_query()

(Pdb) context.secondary_columns
[Column('id', Integer(), table=<%(140584238025168 a)s>, primary_key=True, nullable=False), Column('discriminator', String(length=50), table=<%(140584238025168 a)s>, nullable=False), Column('child_id', Integer(), table=<%(140584238025168 a)s>), <sqlalchemy.sql.elements.Label object at 0x7fdc51376610>]
(Pdb) context.secondary_columns[-1]
<sqlalchemy.sql.elements.Label object at 0x7fdc51376610>
(Pdb) print context.secondary_columns[-1]
CASE WHEN (a_1.discriminator = :param_1) THEN :param_2 ELSE :param_3 END
(Pdb) adapter.columns[adapter.mapper.polymorphic_on]
<sqlalchemy.sql.elements.Label object at 0x7fdc51360690>

polymorphic_on, when retrieved from the adapter, generates a new object, and is not set to the value in context.secondary_columns. in the good case, it is.

[Mike Bayer]

https://gerrit.sqlalchemy.org/#/q/Iffe68196aaac592165c89684f09f4c06cd78ce54

[Mike Bayer]

Ensure mapper.polymorphic_on is polymorphic_prop.columns[0]

Fixed bug where joined eager loading would fail for a polymorphically- loaded mapper, where the polymorphic_on was set to an un-mapped expression such as a CASE expression.

Change-Id: Iffe68196aaac592165c89684f09f4c06cd78ce54 Fixes: #3800 (cherry picked from commit 97b294093617eca7298a2fe97bd23bd6dc3b59bf)

→ <<cset 3202fb4eabd7>>

[Mike Bayer]

Ensure mapper.polymorphic_on is polymorphic_prop.columns[0]

Fixed bug where joined eager loading would fail for a polymorphically- loaded mapper, where the polymorphic_on was set to an un-mapped expression such as a CASE expression.

Change-Id: Iffe68196aaac592165c89684f09f4c06cd78ce54 Fixes: #3800

→ <<cset 97b294093617>>