Skip to content

TypeError raised with with_loader_criteria and polymorphic mapping, starting in 1.4.23 #7557

New issue
New issue
Closed
Closed
TypeError raised with with_loader_criteria and polymorphic mapping, starting in 1.4.23#7557
Labels
bugSomething isn't workinggreat mcveAn issue with a great mcveloader optionsORM options like joinedload(), load_only(), these are complicated and have a lot of issuesormregressionsomething worked and was broken by a change
Milestone
 
1.4.x
@sweetro

Description

@sweetro
sweetro
opened on Jan 10, 2022

Describe the bug

A TypeError is raised where a list rather than a tuple is is returned from orig_query._with_options in do_load (

sqlalchemy/lib/sqlalchemy/orm/loading.py

Line 1083 in ad244b9

options = (enable_opt,) + orig_query._with_options + (disable_opt,)
).

This occurs under the following conditions:

  • A polymorphic table T with "polymorphic_load": "selectin"
  • A relationship referencing table T using lazy=select
  • Adding with_loader_criteria options on do_orm_execute (the loading criteria itself doesn't seem to matter, rather the existence of it triggers the error).

To Reproduce

import enum
from sqlalchemy import event
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declared_attr
from sqlalchemy.orm import Session, backref
from sqlalchemy import Integer, Column, String, ForeignKey
from sqlalchemy import create_engine
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.sqltypes import Enum
    
if __name__ == "__main__":

  Base = declarative_base()
  @enum.unique
  class AddressType(str, enum.Enum):
      EMAIL = "email"
      PHYSICAL = "physical"

  class Address(Base):
      __tablename__ = "address"

      id = Column(Integer, primary_key=True)
      user_id = Column(Integer, ForeignKey("user.id"))
      # Polymorphic mapping for SQLAlchemy
      address_type = Column(Enum(AddressType), nullable=False)
      __mapper_args__ = {
          "polymorphic_identity": "base_address",
          "polymorphic_on": address_type,
      }

  class PhysicalAddress(Address):
      __tablename__ = "physical_address"
      street = Column(String)
      address_id = Column(
          Integer,
          ForeignKey(Address.id),
          primary_key=True,
      )

      __mapper_args__ = {
          "polymorphic_identity": AddressType.PHYSICAL,
          "polymorphic_load": "selectin",
      }

  class EmailAddress(Address):
      __tablename__ = "email_address"
      email = Column(String)
      address_id = Column(
          Integer,
          ForeignKey(Address.id),
          primary_key=True,
      )

      __mapper_args__ = {
          "polymorphic_identity": AddressType.EMAIL,
          "polymorphic_load": "selectin",
      }

  class User(Base):
      __tablename__ = "user"

      id = Column(Integer, primary_key=True)
      name = Column(String)
      # Fails
      address = relationship(Address, uselist=False, lazy="select")
      # Succeeds 
      # address = relationship(Address, uselist=False, lazy="joined")


  @event.listens_for(Session, "do_orm_execute")
  def _add_filtering_criteria(execute_state):
    if (
        not execute_state.is_relationship_load
    ):
        # this will always return the user, but need to pass some load option to get the tuple issue
        user_id = 1
        execute_state.statement = execute_state.statement.options(
            orm.with_loader_criteria(
                User,
                lambda cls: cls.id == 1,
                include_aliases=False,
            )
        )

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

  Session = sessionmaker(bind=engine, future=True)
  sess = Session()
  sess.add_all([
    User(
      name="u1",
    ),
    EmailAddress(
      email="foo",
      user_id=1
    )
  ])

  sess.commit()
  u = sess.query(User).one()
  print(u.address)

Error

python3 webapp_tests/test_with_loader.py
2022-01-10 09:37:40,881 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-01-10 09:37:40,882 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("address")
2022-01-10 09:37:40,882 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-01-10 09:37:40,882 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("address")
2022-01-10 09:37:40,882 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-01-10 09:37:40,882 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("physical_address")
2022-01-10 09:37:40,883 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-01-10 09:37:40,883 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("physical_address")
2022-01-10 09:37:40,883 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-01-10 09:37:40,883 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("email_address")
2022-01-10 09:37:40,883 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-01-10 09:37:40,883 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("email_address")
2022-01-10 09:37:40,883 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-01-10 09:37:40,883 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("user")
2022-01-10 09:37:40,883 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-01-10 09:37:40,883 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("user")
2022-01-10 09:37:40,883 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-01-10 09:37:40,884 INFO sqlalchemy.engine.Engine 
CREATE TABLE user (
	id INTEGER NOT NULL, 
	name VARCHAR, 
	PRIMARY KEY (id)
)


2022-01-10 09:37:40,884 INFO sqlalchemy.engine.Engine [no key 0.00007s] ()
2022-01-10 09:37:40,885 INFO sqlalchemy.engine.Engine 
CREATE TABLE address (
	id INTEGER NOT NULL, 
	user_id INTEGER, 
	address_type VARCHAR(8) NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(user_id) REFERENCES user (id)
)


2022-01-10 09:37:40,885 INFO sqlalchemy.engine.Engine [no key 0.00009s] ()
2022-01-10 09:37:40,885 INFO sqlalchemy.engine.Engine 
CREATE TABLE physical_address (
	street VARCHAR, 
	address_id INTEGER NOT NULL, 
	PRIMARY KEY (address_id), 
	FOREIGN KEY(address_id) REFERENCES address (id)
)


2022-01-10 09:37:40,885 INFO sqlalchemy.engine.Engine [no key 0.00007s] ()
2022-01-10 09:37:40,886 INFO sqlalchemy.engine.Engine 
CREATE TABLE email_address (
	email VARCHAR, 
	address_id INTEGER NOT NULL, 
	PRIMARY KEY (address_id), 
	FOREIGN KEY(address_id) REFERENCES address (id)
)


2022-01-10 09:37:40,886 INFO sqlalchemy.engine.Engine [no key 0.00018s] ()
2022-01-10 09:37:40,886 INFO sqlalchemy.engine.Engine COMMIT
2022-01-10 09:37:40,889 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-01-10 09:37:40,890 INFO sqlalchemy.engine.Engine INSERT INTO address (user_id, address_type) VALUES (?, ?)
2022-01-10 09:37:40,890 INFO sqlalchemy.engine.Engine [generated in 0.00012s] (1, 'EMAIL')
2022-01-10 09:37:40,891 INFO sqlalchemy.engine.Engine INSERT INTO email_address (email, address_id) VALUES (?, ?)
2022-01-10 09:37:40,891 INFO sqlalchemy.engine.Engine [generated in 0.00010s] ('foo', 1)
2022-01-10 09:37:40,892 INFO sqlalchemy.engine.Engine INSERT INTO user (name) VALUES (?)
2022-01-10 09:37:40,892 INFO sqlalchemy.engine.Engine [generated in 0.00009s] ('u1',)
2022-01-10 09:37:40,892 INFO sqlalchemy.engine.Engine COMMIT
2022-01-10 09:37:40,893 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-01-10 09:37:40,895 INFO sqlalchemy.engine.Engine SELECT user.id AS user_id, user.name AS user_name 
FROM user 
WHERE user.id = ?
2022-01-10 09:37:40,895 INFO sqlalchemy.engine.Engine [generated in 0.00011s] (1,)
2022-01-10 09:37:40,897 INFO sqlalchemy.engine.Engine SELECT address.id AS address_id, address.user_id AS address_user_id, address.address_type AS address_address_type 
FROM address 
WHERE ? = address.user_id
2022-01-10 09:37:40,897 INFO sqlalchemy.engine.Engine [generated in 0.00011s] (1,)
Traceback (most recent call last):
  File "webapp_tests/test_with_loader.py", line 103, in <module>
    print(u.address)
  File "/Users/dylanpetro/Library/Caches/pypoetry/virtualenvs/pyapp-OgJmIjB3-py3.7/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py", line 480, in __get__
    return self.impl.get(state, dict_)
  File "/Users/dylanpetro/Library/Caches/pypoetry/virtualenvs/pyapp-OgJmIjB3-py3.7/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py", line 939, in get
    value = self._fire_loader_callables(state, key, passive)
  File "/Users/dylanpetro/Library/Caches/pypoetry/virtualenvs/pyapp-OgJmIjB3-py3.7/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py", line 975, in _fire_loader_callables
    return self.callable_(state, passive)
  File "/Users/dylanpetro/Library/Caches/pypoetry/virtualenvs/pyapp-OgJmIjB3-py3.7/lib/python3.7/site-packages/sqlalchemy/orm/strategies.py", line 916, in _load_for_state
    extra_criteria,
  File "/Users/dylanpetro/Library/Caches/pypoetry/virtualenvs/pyapp-OgJmIjB3-py3.7/lib/python3.7/site-packages/sqlalchemy/orm/strategies.py", line 1046, in _emit_lazyload
    result = result.unique().scalars().all()
  File "/Users/dylanpetro/Library/Caches/pypoetry/virtualenvs/pyapp-OgJmIjB3-py3.7/lib/python3.7/site-packages/sqlalchemy/engine/result.py", line 1362, in all
    return self._allrows()
  File "/Users/dylanpetro/Library/Caches/pypoetry/virtualenvs/pyapp-OgJmIjB3-py3.7/lib/python3.7/site-packages/sqlalchemy/engine/result.py", line 400, in _allrows
    rows = self._fetchall_impl()
  File "/Users/dylanpetro/Library/Caches/pypoetry/virtualenvs/pyapp-OgJmIjB3-py3.7/lib/python3.7/site-packages/sqlalchemy/engine/result.py", line 1275, in _fetchall_impl
    return self._real_result._fetchall_impl()
  File "/Users/dylanpetro/Library/Caches/pypoetry/virtualenvs/pyapp-OgJmIjB3-py3.7/lib/python3.7/site-packages/sqlalchemy/engine/result.py", line 1687, in _fetchall_impl
    return list(self.iterator)
  File "/Users/dylanpetro/Library/Caches/pypoetry/virtualenvs/pyapp-OgJmIjB3-py3.7/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 158, in chunks
    post_load.invoke(context, path)
  File "/Users/dylanpetro/Library/Caches/pypoetry/virtualenvs/pyapp-OgJmIjB3-py3.7/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 1334, in invoke
    loader(context, path, states, self.load_keys, *arg, **kw)
  File "/Users/dylanpetro/Library/Caches/pypoetry/virtualenvs/pyapp-OgJmIjB3-py3.7/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 1085, in do_load
    options = (enable_opt,) + orig_query._with_options + (disable_opt,)
TypeError: can only concatenate tuple (not "list") to tuple

Versions

  • OS: OSX 11.6.2
  • Python: 3.7.8
  • SQLAlchemy: 1.4.29 (but have also reproed in 1.4.23)
  • Database: Postgres
  • DBAPI (eg: psycopg, cx_oracle, mysqlclient): psycopg

Additional context

  1. It's totally possible I'm misunderstanding how these different relationship loader options work, and this combination is not supported.
  2. Either changing the relationship to lazy="joined" or the polymorphic_mapping=inline or removing with_loader_criteria resolves the issue.

Activity

sweetro
added
requires triageNew issue that requires categorization
on Jan 10, 2022
zzzeek

zzzeek commented on Jan 10, 2022

@zzzeek
zzzeek
on Jan 10, 2022
Member

luckily this looks like something trivial, but let's see

CaselIT
added
bugSomething isn't working
orm
and removed
requires triageNew issue that requires categorization
on Jan 10, 2022
zzzeek

zzzeek commented on Jan 10, 2022

@zzzeek
zzzeek
on Jan 10, 2022
Member

OK it is 1b5ae17 , so much for it being a small change, not really sure how a list is getting in there yet

zzzeek

zzzeek commented on Jan 10, 2022

@zzzeek
zzzeek
on Jan 10, 2022
Member

okey doke

the question is how on earth are tests not hitting this

diff --git a/lib/sqlalchemy/orm/strategies.py b/lib/sqlalchemy/orm/strategies.py
index c29fc7749d..a8a5639528 100644
--- a/lib/sqlalchemy/orm/strategies.py
+++ b/lib/sqlalchemy/orm/strategies.py
@@ -964,7 +964,7 @@ class LazyLoader(AbstractRelationshipLoader, util.MemoizedSlots):
         if state.load_options or (loadopt and loadopt._extra_criteria):
             effective_path = state.load_path[self.parent_property]
 
-            opts = list(state.load_options)
+            opts = tuple(state.load_options)
 
             if loadopt and loadopt._extra_criteria:
                 use_get = False
sweetro

sweetro commented on Jan 10, 2022

@sweetro
sweetro
on Jan 10, 2022 · edited by sweetro
Author

+1 I noticed in 1b5ae17, newly introduces stmt._with_options = opts, where as shown in your snippet, opts is cast as a list.

Though in most cases, except the one I listed, ._with_options appears to be cast back to a tuple once it gets to do_load, though I couldn't grok where that happens.

zzzeek
added
great mcveAn issue with a great mcve
loader optionsORM options like joinedload(), load_only(), these are complicated and have a lot of issues
on Jan 10, 2022
zzzeek
added this to the 1.4.x milestone on Jan 10, 2022
zzzeek
added
regressionsomething worked and was broken by a change
on Jan 10, 2022
zzzeek

zzzeek commented on Jan 10, 2022

@zzzeek
zzzeek
on Jan 10, 2022
Member

just insanely relieved it's not related to with_loader_criteria

zzzeek
mentioned this on Jan 10, 2022
sqla-tester

sqla-tester commented on Jan 10, 2022

@sqla-tester
sqla-tester
on Jan 10, 2022
Collaborator

Mike Bayer has proposed a fix for this issue in the main branch:

ensure with_options not switched to a list https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/3497

3 remaining items

Loading
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workinggreat mcveAn issue with a great mcveloader optionsORM options like joinedload(), load_only(), these are complicated and have a lot of issuesormregressionsomething worked and was broken by a change

    Type

    No type

    Projects

    No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions
      TypeError raised with with_loader_criteria and polymorphic mapping, starting in 1.4.23 · Issue #7557 · sqlalchemy/sqlalchemy