subqueries like "exists()" interact with _adjust_for_single_inheritance() poorly
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.
Comments (8)
-
repo owner -
repo owner - changed milestone to 1.2
- changed component to orm
- changed title to subqueries like "exists()" interact with _adjust_for_single_inheritance() poorly
continuing with
#3177, which is too simplistic for cases like these -
reporter Thanks for the quick response.
-
repo owner - changed milestone to 1.1
-
repo owner - changed status to resolved
- 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>>
-
repo owner that case should work now (1.1 only, slight behavioral change) thanks for reporting!
-
reporter Thanks a lot, really impressed by how quickly you respond and fix things!
-
repo owner - correct the commit from ref
#3582to refer to the correct sub-element
→ <<cset 3370cbde509b>>
- correct the commit from ref
- Log in to comment
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: