[Sqlalchemy-tickets] Issue #2967: eager loaders generate incorrect SQL when loading a class mapped to a join (zzzeek/sqlalchemy)

[monsanto <iss...@bi...>]

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 ()
```