0.9 regression: polymorphic subclasses from outer query contaminate the subquery

Issue #3130 resolved
Gabor Gombas
created an issue

Hi,

The attached test case runs fine with 0.8.1, fails with rel_0_9 branch (tested commit: 2c41a702adca96d6847b72eb642ccbcfd21a0828). The subquery gets unexpectedly transformed into a join that contains polymorphic subclasses mentioned in the enclosing query only.

Gabor

Comments (9)

  1. Michael Bayer repo owner

    sqlite only. With any other dialect, your join makes this very nice query that was not possible in 0.8 using the ORM:

    SELECT cluster.id AS cluster_id 
    FROM cluster JOIN holder ON cluster.id = holder.cluster_id JOIN 
    (resource JOIN virtual_machine ON resource.id = virtual_machine.resource_id) 
    ON holder.id = resource.holder_id 
    JOIN host ON host.id = virtual_machine.host_id 
    JOIN disk ON host.id = disk.host_id 
    WHERE disk.backing_store_id IN (SELECT share.resource_id 
    FROM resource JOIN share ON resource.id = share.resource_id)
    
  2. Michael Bayer repo owner

    OK got a patch on this, the more tedious part is getting a test written, working on that now.

    diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
    index da810e9..7a6cece 100644
    --- a/lib/sqlalchemy/sql/compiler.py
    +++ b/lib/sqlalchemy/sql/compiler.py
    @@ -1352,7 +1352,7 @@ class SQLCompiler(Compiled):
    
                     newelem.onclause = visit(newelem.onclause, **kw)
    
    -            elif newelem.is_selectable and newelem._is_from_container:
    +            elif newelem._is_from_container:
                     # if we hit an Alias or CompoundSelect, put a marker in the
                     # stack.
                     kw['transform_clue'] = 'select_container'
    diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
    index 4f58f61..ab07efe 100644
    --- a/lib/sqlalchemy/sql/elements.py
    +++ b/lib/sqlalchemy/sql/elements.py
    @@ -231,6 +231,7 @@ class ClauseElement(Visitable):
         is_clause_element = True
    
         _order_by_label_element = None
    +    _is_from_container = False
    
         def _clone(self):
             """Create a shallow copy of this ClauseElement.
    diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
    index 0d8162b..eef4a17 100644
    --- a/lib/sqlalchemy/sql/selectable.py
    +++ b/lib/sqlalchemy/sql/selectable.py
    @@ -3050,6 +3051,7 @@ class Select(HasPrefixes, GenerativeSelect):
    
     class ScalarSelect(Generative, Grouping):
         _from_objects = []
    +    _is_from_container = True
    
         def __init__(self, element):
             self.element = element
    
  3. Michael Bayer repo owner
    • Fixed a SQLite join rewriting issue where a subquery that is embedded as a scalar subquery such as within an IN would receive inappropriate substitutions from the enclosing query, if the same table were present inside the subquery as were in the enclosing query such as in a joined inheritance scenario. fixes #3130

    → <<cset 3d7b18863813>>

  4. Michael Bayer repo owner
    • Fixed a SQLite join rewriting issue where a subquery that is embedded as a scalar subquery such as within an IN would receive inappropriate substitutions from the enclosing query, if the same table were present inside the subquery as were in the enclosing query such as in a joined inheritance scenario. fixes #3130

    → <<cset b090e3a3e274>>

  5. Log in to comment