Declarative backref not filtering by polymorphic identity

[Mischa S]

I am trying to declare a backref relationship between a related table and a single-table polymorphic subclass. I expect the relationship to filter by the polymorphic discriminator column when I select the relationship, but instead it selects ALL related rows, regardless of polymorphic discriminator identity. This is confusing, and does not appear to be documented in http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/inheritance.html#declarative-inheritance I think this is a common use case, but I see very little documentation in the declarative inheritance relationship documentation. Please let me know if I'm missing something. Attached is a test case that fails to discriminate when selecting the related rows via backref. SQL logs are at: https://0bin.net/paste/P2VTuIUsIDyZ9vw4#zItNB6zFxnNm1usjqzqflA4eJ+JqGELuUq8uIm2pf2i

[Mike Bayer]

hello -

The script is not producing any exception for me. While it does produce a warning about the overlapping relationships under 1.0.x, these warnings aren't present under 1.1, I'd need to look more closely to see if this pattern is supported as you've implemented it, however I've tried all the way back to 0.9 and no error is produced.

Looking in the logs you pasted, I can confirm your attached test script is not the source of these because there are no primary keys generated as "33" here.

Please provide a test script that you've confirmed reproduces the problem as well as provide detail on what version of SQLAlchemy you're using. thanks!

[Mischa S]

I have tested on SQLAlchemy 1.1.5 and 1.1.9

This is my output:

$ python3 poly.py
2017-04-05 14:58:15,055 INFO sqlalchemy.engine.base.Engine (35,)
2017-04-05 14:58:15,056 INFO sqlalchemy.engine.base.Engine SELECT base_event.id AS base_event_id, base_event.event_type AS base_event_event_type, base_event.email_id AS base_event_email_id 
FROM base_event 
WHERE ? = base_event.email_id
2017-04-05 14:58:15,056 INFO sqlalchemy.engine.base.Engine (35,)
Traceback (most recent call last):
  File "poly.py", line 63, in <module>
    assert len(events_1) == 1, "Got unrelated related rows not discriminated by polymorphic_identity"
AssertionError: Got unrelated related rows not discriminated by polymorphic_identity

[Mischa S]

This is exactly produced from my test script. I have run it multiple times, I assume that is why my PK is a larger number.

[Mike Bayer]

the bug is only in Python 3 and it's in flask-sqlalchemy. Here's the script without using flask-sqlalchemy:

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


from sqlalchemy.sql.schema import Column, ForeignKey
from sqlalchemy.sql.sqltypes import Integer, Text

Base = declarative_base()


class Email(Base):
    __tablename__ = 'email'
    id = Column('id', Integer, primary_key=True)


class BaseEvent(Base):
    __tablename__ = 'base_event'

    id = Column('id', Integer, primary_key=True)
    event_type = Column(Text(), nullable=False)
    email_id = Column(Integer, ForeignKey('email.id', ondelete='CASCADE'), nullable=False)
    email = relationship('Email', backref=backref('base_events', cascade='all, delete-orphan'))

    __mapper_args__ = {
        'polymorphic_on': event_type,
        'polymorphic_identity': 'base',
    }


class Event1(BaseEvent):
    __mapper_args__ = {
        'polymorphic_identity': 'type1',
    }
    email = relationship('Email', backref=backref('events_1', cascade='all, delete-orphan'))


class Event2(BaseEvent):
    __mapper_args__ = {
        'polymorphic_identity': 'type2',
    }
    email = relationship('Email', backref=backref('events_2', cascade='all, delete-orphan'))

################

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

sess = Session(e)
em1 = Email()
em2 = Email()
sess.add_all([em1, em2])
sess.commit()

#########


ev1 = Event1(email_id=em1.id, event_type='type1')
ev2 = Event2(email_id=em1.id, event_type='type2')
sess.add_all([ev1, ev2])
sess.commit()

# now select from backrefs based on polymorphic identity
print("-------------------------")
events_1 = em1.events_1

# this should return 1, it returns 2 :(
assert len(events_1) == 1, "Got unrelated related rows not discriminated by polymorphic_identity"

the issue is somewhere in db.Model where their declarative base is interfering with declarative being able to set the "single inheritance" flag on the Event1 mapper. output:

$ /opt/python3.6/bin/python3 test.py 
2017-04-05 19:11:58,577 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2017-04-05 19:11:58,577 INFO sqlalchemy.engine.base.Engine ()
2017-04-05 19:11:58,578 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2017-04-05 19:11:58,578 INFO sqlalchemy.engine.base.Engine ()
2017-04-05 19:11:58,579 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("email")
2017-04-05 19:11:58,579 INFO sqlalchemy.engine.base.Engine ()
2017-04-05 19:11:58,579 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("base_event")
2017-04-05 19:11:58,579 INFO sqlalchemy.engine.base.Engine ()
2017-04-05 19:11:58,580 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE email (
    id INTEGER NOT NULL, 
    PRIMARY KEY (id)
)


2017-04-05 19:11:58,580 INFO sqlalchemy.engine.base.Engine ()
2017-04-05 19:11:58,581 INFO sqlalchemy.engine.base.Engine COMMIT
2017-04-05 19:11:58,581 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE base_event (
    id INTEGER NOT NULL, 
    event_type TEXT NOT NULL, 
    email_id INTEGER NOT NULL, 
    PRIMARY KEY (id), 
    FOREIGN KEY(email_id) REFERENCES email (id) ON DELETE CASCADE
)


2017-04-05 19:11:58,581 INFO sqlalchemy.engine.base.Engine ()
2017-04-05 19:11:58,581 INFO sqlalchemy.engine.base.Engine COMMIT
2017-04-05 19:11:58,594 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2017-04-05 19:11:58,595 INFO sqlalchemy.engine.base.Engine INSERT INTO email DEFAULT VALUES
2017-04-05 19:11:58,595 INFO sqlalchemy.engine.base.Engine ()
2017-04-05 19:11:58,595 INFO sqlalchemy.engine.base.Engine INSERT INTO email DEFAULT VALUES
2017-04-05 19:11:58,595 INFO sqlalchemy.engine.base.Engine ()
2017-04-05 19:11:58,596 INFO sqlalchemy.engine.base.Engine COMMIT
2017-04-05 19:11:58,596 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2017-04-05 19:11:58,597 INFO sqlalchemy.engine.base.Engine SELECT email.id AS email_id 
FROM email 
WHERE email.id = ?
2017-04-05 19:11:58,597 INFO sqlalchemy.engine.base.Engine (1,)
2017-04-05 19:11:58,599 INFO sqlalchemy.engine.base.Engine INSERT INTO base_event (event_type, email_id) VALUES (?, ?)
2017-04-05 19:11:58,599 INFO sqlalchemy.engine.base.Engine ('type1', 1)
2017-04-05 19:11:58,599 INFO sqlalchemy.engine.base.Engine INSERT INTO base_event (event_type, email_id) VALUES (?, ?)
2017-04-05 19:11:58,599 INFO sqlalchemy.engine.base.Engine ('type2', 1)
2017-04-05 19:11:58,600 INFO sqlalchemy.engine.base.Engine COMMIT
-------------------------
2017-04-05 19:11:58,601 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2017-04-05 19:11:58,601 INFO sqlalchemy.engine.base.Engine SELECT email.id AS email_id 
FROM email 
WHERE email.id = ?
2017-04-05 19:11:58,601 INFO sqlalchemy.engine.base.Engine (1,)
2017-04-05 19:11:58,603 INFO sqlalchemy.engine.base.Engine SELECT base_event.id AS base_event_id, base_event.event_type AS base_event_event_type, base_event.email_id AS base_event_email_id 
FROM base_event 
WHERE ? = base_event.email_id AND base_event.event_type IN (?)
2017-04-05 19:11:58,603 INFO sqlalchemy.engine.base.Engine (1, 'type1')
[classic@photon2 sqlalchemy]$ 

[Mischa S]

I opened a ticket on Flask-SQLAlchemy https://github.com/mitsuhiko/flask-sqlalchemy/issues/492 Thanks!

[Mike Bayer]

oh, Python 2 does it also, I had an older flask-sqlalchemy installed.

[Mike Bayer]

it's likely this: https://github.com/mitsuhiko/flask-sqlalchemy/blob/master/flask_sqlalchemy/init.py#L641 you don't want tablename set for single table inheritance.

[Mike Bayer]

downstream flask-sqlalchemy issue