subqueries like "exists()" interact with _adjust_for_single_inheritance() poorly

[Yegor Roganov]

Given class hierarchy from http://docs.sqlalchemy.org/en/rel_1_0/changelog/migration_10.html

class Widget(Base):
    __tablename__ = 'widget'
    id = Column(Integer, primary_key=True)
    type = Column(String)
    related_id = Column(ForeignKey('related.id'))
    related = relationship("Related", backref="widget")
    __mapper_args__ = {'polymorphic_on': type}


class FooWidget(Widget):
    __mapper_args__ = {'polymorphic_identity': 'foo'}


class Related(Base):
    __tablename__ = 'related'
    id = Column(Integer, primary_key=True)

query session.query(exists().where(and_(FooWidget.related_id == 5, FooWidget.id == 6))) produces strange SQL (widget.type IN (:type_1) should be in the subquery):

SELECT EXISTS (SELECT * 
FROM widget 
WHERE widget.related_id = :related_id_1 AND widget.id = :id_1) AS anon_1 
FROM widget 
WHERE widget.type IN (:type_1)

Query session.query(session.query(FooWidget).filter(and_(FooWidget.related_id == 5, FooWidget.id == 6)).exists()) is also suboptimal:

SELECT EXISTS (SELECT 1 
FROM widget 
WHERE widget.related_id = :related_id_1 AND widget.id = :id_1 AND widget.type IN (:type_1)) AS anon_1 
FROM widget 
WHERE widget.type IN (:type_1)

(note the repeating widget.type IN (:type_1) clause)

Not sure if this is a bug or I'm just using it wrong, but I figured I'd create an issue. Sorry for the hassle if it's not a bug.

[Mike Bayer]

well yeah, the query needs to add that criteria for most single-inheritance subclass calls. One of the few places that we unfortunately still have to guess within a fairly open field. The EXISTS is typically meant to be in the WHERE clause of another query. some obvious way of instructing this logic to not traverse into certain kinds of queries, or more generally what kinds of queries we'd want to exclude (I'd guess don't traverse into subqueries) isn't jumping out at me at the moment.

you can get the query you want like this:

q = s.query(FooWidget).filter(and_(FooWidget.related_id == 5, FooWidget.id == 6)).exists().select()
result = s.scalar(q)

[Mike Bayer]

continuing with #3177, which is too simplistic for cases like these

[Yegor Roganov]

Thanks for the quick response.

[Mike Bayer]

• Fixed bug where the "single table inheritance" criteria would be added onto the end of a query in some inappropriate situations, such as when querying from an exists() of a single-inheritance subclass.

fixes #3582

→ <<cset 33c378f768c6>>

[Mike Bayer]

that case should work now (1.1 only, slight behavioral change) thanks for reporting!

[Yegor Roganov]

Thanks a lot, really impressed by how quickly you respond and fix things!

[Mike Bayer]

• correct the commit from ref #3582 to refer to the correct sub-element

→ <<cset 3370cbde509b>>