[Sqlalchemy-tickets] Issue #2967: eager loaders generate incorrect SQL when loading a class mapped
Brought to you by:
zzzeek
|
From: monsanto <iss...@bi...> - 2014-02-19 21:20:48
|
New issue 2967: eager loaders generate incorrect SQL when loading a class mapped to a join https://bitbucket.org/zzzeek/sqlalchemy/issue/2967/eager-loaders-generate-incorrect-sql-when monsanto: See attached test. There are three tables "A", "AB", "B", and "C". AB joins A and B. We desire to relate "A" and "B", but allow the Bs to know what A they came from. To do this, we make a new class BFromA, map it to join(AB, B), and declare a relationship() between A and BFromA. If you try to joinedload() BFromAs, you get two different possible SQL queries, depending on whether you str() the query, or execute it. str() gives the correct result: ``` SELECT a.a_id AS a_a_id, ab_1.b_id AS ab_1_b_id, b_1.b_id AS b_1_b_id, ab_1.a_id AS ab_1_a_id FROM a LEFT OUTER JOIN (ab AS ab_1 JOIN b AS b_1 ON b_1.b_id = ab_1.b_id) ON a.a_id = ab_1.a_id ``` Executing gives an incorrect result: notice how the joined tables have been changed to a SELECT: ``` SELECT a.a_id AS a_a_id, anon_1.ab_1_b_id AS ab_1_b_id, anon_1.b_1_b_id AS b_1_b_id, anon_1.ab_1_a_id AS ab_1_a_id FROM a LEFT OUTER JOIN (SELECT ab_1.a_id AS ab_1_a_id, ab_1.b_id AS ab_1_b_id, b_1.b_id AS b_1_b_id FROM ab AS ab_1 JOIN b AS b_1 ON b_1.b_id = ab_1.b_id) AS anon_1 ON a.a_id = anon_1.ab_1_a_id ``` By changing the joined tables to a SELECT, we break any correlated subqueries. We define table C to be a subset of Bs, and add a column_property to BFromA that checks if the current B is in this set. The following is the output of `python 3.3 bugtest.py` watch as the lazyload and str() joinedload correlate the B correctly with the subquery, and the executed joinedload/subqueryload decorrelate the B. ``` **Lazy load: 2014-02-19 21:19:24,805 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2014-02-19 21:19:24,806 INFO sqlalchemy.engine.base.Engine SELECT ab.b_id AS ab_b_id, b.b_id AS b_b_id, EXISTS (SELECT * FROM c WHERE c.b_id = b.b_id) AS anon_1, ab.a_id AS ab_a_id FROM ab JOIN b ON b.b_id = ab.b_id LIMIT ? OFFSET ? 2014-02-19 21:19:24,806 INFO sqlalchemy.engine.base.Engine (1, 0) **Joined load str(): SELECT a.a_id AS a_a_id, ab_1.b_id AS ab_1_b_id, b_1.b_id AS b_1_b_id, EXISTS (SELECT * FROM c WHERE c.b_id = b_1.b_id) AS anon_1, ab_1.a_id AS ab_1_a_id FROM a LEFT OUTER JOIN (ab AS ab_1 JOIN b AS b_1 ON b_1.b_id = ab_1.b_id) ON a.a_id = ab_1.a_id **Joined load: 2014-02-19 21:01:50,012 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2014-02-19 21:01:50,013 INFO sqlalchemy.engine.base.Engine SELECT a.a_id AS a_a_id, anon_1.ab_1_b_id AS ab_1_b_id, anon_1.b_1_b_id AS b_1_b_id, EXI STS (SELECT * FROM c, b AS b_1 WHERE c.b_id = b_1.b_id) AS anon_2, anon_1.ab_1_a_id AS ab_1_a_id FROM a LEFT OUTER JOIN (SELECT ab_1.a_id AS ab_1_a_id, ab_1.b_id AS ab_1_b_id, b_1.b_id AS b_1_b_id FROM ab AS ab_1 JOIN b AS b_1 ON b_1.b_id = ab_1.b_id) AS anon_1 ON a.a_id = anon_1.ab_1_a_id 2014-02-19 21:01:50,013 INFO sqlalchemy.engine.base.Engine () **Subqueryload: 2014-02-19 21:01:50,015 INFO sqlalchemy.engine.base.Engine SELECT a.a_id AS a_a_id FROM a 2014-02-19 21:01:50,015 INFO sqlalchemy.engine.base.Engine () 2014-02-19 21:01:50,017 INFO sqlalchemy.engine.base.Engine SELECT anon_1.ab_b_id AS ab_b_id, anon_1.b_b_id AS b_b_id, EXISTS (SELECT * FROM c, b WHERE c.b_id = b.b_id) AS anon_2, anon_1.ab_a_id AS ab_a_id, anon_3.a_a_id AS anon_3_a_a_id FROM (SELECT a.a_id AS a_a_id FROM a) AS anon_3 JOIN (SELECT ab.a_id AS ab_a_id, ab.b_id AS ab_b_id, b.b_id AS b_b_id FROM ab JOIN b ON b.b_id = ab.b_id) AS anon_1 ON anon_3.a_a_id = anon_1.ab_a_id ORDER BY anon_3.a_a_id 2014-02-19 21:01:50,017 INFO sqlalchemy.engine.base.Engine () ``` |