0.9 regression: polymorphic subclasses from outer query contaminate the subquery

[Gabor Gombas]

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

[Mike Bayer]

sqlite only or all DBs ? note that SQLite has more complex behavior in 0.9, see http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html#many-join-and-left-outer-join-expressions-will-no-longer-be-wrapped-in-select-from-as-anon-1.

[Mike Bayer]

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)

[Gabor Gombas]

Yes, I've just tried it with Oracle, and it works there.

[Mike Bayer]

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

[Mike Bayer]

• 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>>

[Mike Bayer]

• 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>>

[Mike Bayer]

OK great, these join rewrites often take all day but this one gave way pretty easily, thanks for the test case.