selectin loading in Single Table Inheritance produces cross join

[sqlalchemy-bot]

Migrated issue, originally created by Tim Chen

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

Base = declarative_base()


class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    type = Column(String)

    __mapper_args__ = {
        'polymorphic_on': type,
    }


class EmployerUser(User):
    # __tablename__ = 'employer_user'

    # id = Column(Integer, ForeignKey('user.id'), primary_key=True)
    roles = relationship('Role', lazy='selectin')

    __mapper_args__ = {
        'polymorphic_identity': 'employer',
    }


class Role(Base):
    __tablename__ = 'role'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'))


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

s = Session(e)

user = EmployerUser(roles=[Role(), Role(), Role()])
s.add(user)
s.commit()

s.query(EmployerUser).all()

The generated selectin load SQL looks like this:

SELECT user_1.id AS user_1_id, role.id AS role_id, role.user_id AS role_user_id
FROM user, user AS user_1 JOIN role ON user_1.id = role.user_id
WHERE user_1.id IN (?) AND user.type IN (?) AND user_1.type IN (?) ORDER BY user_1.id

You can see that user gets cross joined to itself, blowing up the result set unnecessarily. This becomes a huge problem for large tables.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

this affects 1.1 as well in a more general way however will keep this on the 1.2 series as 1.1 is moving into maintenance.

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

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: high priority

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• set milestone to "1.2.x"

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Ensure select_from_entity adapter is used in adjust_for_single_inheritance

Fixed issue in single-inheritance loading where the use of an aliased
entity against a single-inheritance subclass in conjunction with the
:meth:.Query.select_from method would cause the SQL to be rendered with
the unaliased table mixed in to the query, causing a cartesian product. In
particular this was affecting the new "selectin" loader when used against a
single-inheritance subclass.

Change-Id: Ic2cbe94a5269c101b1f98da9a466180dd4452783
Fixes: #4241

→ 4f2d091

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Ensure select_from_entity adapter is used in adjust_for_single_inheritance

Fixed issue in single-inheritance loading where the use of an aliased
entity against a single-inheritance subclass in conjunction with the
:meth:.Query.select_from method would cause the SQL to be rendered with
the unaliased table mixed in to the query, causing a cartesian product. In
particular this was affecting the new "selectin" loader when used against a
single-inheritance subclass.

Change-Id: Ic2cbe94a5269c101b1f98da9a466180dd4452783
Fixes: #4241
(cherry picked from commit 4f2d091)

→ c34a856

[sqlalchemy-bot]

Tim Chen wrote:

Thanks! Just curious - when do you plan to do release for this ?

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

this is a big one but I also have another big one going through today, so hopefully today