Query.filter.options(load_only) difference in behaviour from sqlalchemy 1.4 onwards

[joaocosta]

I am working on a legacy code base, currently using sqlalchemy 1.3 which I am trying to migrate to 1.4 first and then 2.0.

To illustrate the point of this discussion, I have whittled things down to this code snippet:

from sqlalchemy import create_engine
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy import ForeignKey
from sqlalchemy.orm import load_only
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Parent(Base):
    __tablename__ = "parent"
    id = Column(Integer, primary_key=True)
    label = Column(String)
    children = relationship("Child", back_populates="parent")

class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey(Parent.id))
    label = Column(String)
    parent = relationship("Parent", back_populates="children")

engine = create_engine("sqlite:///hello.db", echo=True)
session = sessionmaker(bind=engine)
metadata = Base.metadata

metadata.create_all(engine)

subq = session().query(Parent).filter(Parent.id < 10).options(load_only(Parent.id))
q = session().query(Parent).join(Child).filter(Parent.id.in_(subq))
q.all()

That used to work in sqlalchemy 1.3, but in both 1.4 and 2.0, it generates an invalid sql query, ie:

SELECT parent.id AS parent_id, parent.label AS parent_label 
FROM parent JOIN child ON parent.id = child.parent_id 
WHERE parent.id IN (
    SELECT parent.id, parent.label 
    FROM parent 
    WHERE parent.id < ?)

Which triggers a database error such as:

sqlite3.OperationalError: sub-select returns 2 columns - expected

I could re-write the expression above as a join instead of a IN clause, and in the example above that would be fairly straight-forward, but not so much in the actual legacy code base I need to migrate.

My question is, why is the load_only option not being honoured, and is there a different syntax I could use to guarantee that the only one column is selected in the inner query I'm passing to the IN clause ?

Thanks!

[zzzeek]

load_only() is a loader option and is only intended to affect how objects are populated. they dont take effect for subqueries since subqueries aren't linked to object loading. this is discussed in the migration document at https://docs.sqlalchemy.org/en/14/changelog/migration_14.html#column-loaders-such-as-deferred-with-expression-only-take-effect-when-indicated-on-the-outermost-full-entity-query

[zzzeek]

I could re-write the expression above as a join instead of a IN clause, and in the example above that would be fairly straight-forward, but not so much in the actual legacy code base I need to migrate.

there's no need for that, simply write subqueries naturally, e.g.

subq = session().query(Parent.id).filter(Parent.id < 10)
q = session().query(Parent).join(Child).filter(Parent.id.in_(subq))

[joaocosta]

Thank you, that does look like a rather simple/sensible solution. Because of the way my code base is organised (which is a bit more complex than my whittled down example), it's not that straight forward to make that change, however I found that this works too:

subq = session().query(Parent).filter(Parent.id < 10).with_entities(Parent.id)

Are there any disadvantages to this later method that I should be wary of ?

Thank you for all your help and the great work overall on sqlalchemy.

[zzzeek]

using query.with_entities() is fine, it's the same thing essentially as setting up the select()/query() with those cols up front.