Incorrect query generated by mixing polymorphism, subqueryload and in_()

[Former user]

Hi,

The attached test script generates the following query:

SELECT interface.hw_ent_id AS interface_hw_ent_id, anon_1.hw_ent_id AS anon_1_hw_ent_id FROM (SELECT hw_ent.id AS hw_ent_id FROM hw_ent, machine WHERE machine.cpu_id IN (SELECT cpu.id FROM cpu WHERE cpu.name = ?)) AS anon_1 JOIN interface ON anon_1.hw_ent_id = interface.hw_ent_id ORDER BY anon_1.hw_ent_id

The join condition between hw_ent and machine is missing inside the subquery. Tested with 0.7.9 and 0.8.0.

[Former user]

Test case

[Mike Bayer]

df4e59ff557b16202c4c3e47ad48667ba1e143c0 0.8

1ad1cd2b4ba4247b33d930d9720b3470816de447 0.7

[Former user]

The changes fix the issue for me, thanks for the quick turnaround.

[Former user]

Unfortunately while this change fixes the original query, it breaks a different query. Test case attached, the query works with 0.8.0, the join condition is missing with this change applied (also tested latest 0.8 snapshot, it's also broken).

[Former user]

Test case for a query that is broken after this change

[Mike Bayer]

OK that helps, whenever we change things here its always important to find a case that breaks, will look soon.

[Mike Bayer]

so it's probably this, if you want to run all your tests with this patch:

diff -r f14ec0f517eaf6956962d2d0a34dacb69bead46b lib/sqlalchemy/orm/strategies.py
--- a/lib/sqlalchemy/orm/strategies.py  Sun Apr 14 19:32:54 2013 -0400
+++ b/lib/sqlalchemy/orm/strategies.py  Tue Apr 16 11:58:51 2013 -0400
@@ -781,7 +781,7 @@
         # TODO: why does polymporphic etc. require hardcoding
         # into _adapt_col_list ?  Does query.add_columns(...) work
         # with polymorphic loading ?
-        if entity_mapper.isa(leftmost_mapper):
+        if not q._from_obj and entity_mapper.isa(leftmost_mapper):
             q._set_select_from(entity_mapper)
         q._set_entities(q._adapt_col_list(leftmost_attr))

i need to re-analyze the two issues here to make sure im doing the right thing.

[Former user]

Thanks, I'll test it tomorrow.

[Mike Bayer]

e5d0592180a554a1220985d28dab8533030281f0

[Mike Bayer]

a2c52f1e07259f05006c1303fba000dfb3b9fd64 0.7

[Mike Bayer]

Removing milestone: 0.8.xx (automated comment)