Eagerloading with deferred columns in the join clause generates invalid SQL

You'll need MySQL for this - SQLite happily accepts the generated SQL but as far as I know doesn't actually return accurate SQL. I reported this as a defect in SQLAlchemy itself instead of on MySQL because there is no reason for SQLAlchemy to alter the query in the situation proposed in my test file.

What's going on is if you defer a column used in the join clause of a relation AND nesting is required, trunk SQLAlchemy chokes and generates incorrect SQL. SQLAlchemy 0.4.0 handles this case without error.

Let us take a look at the SQL generated under different circumstances.

a) SQLAlchemy r3771, no deferred columns:

SELECT anon_1.thing_owner_id AS anon_1_thing_owner_id, anon_1.thing_id AS anon_1_thing_id, anon_1.thing_name AS anon_1_thing_name, owner_2.id AS owner_2_id, owner_2.name AS owner_2_name 
FROM (SELECT thing.owner_id AS thing_owner_id, thing.id AS thing_id, thing.name AS thing_name, thing.id AS thing_oid 
FROM thing 
WHERE thing.id = %s ORDER BY thing.id 
 LIMIT 1) AS anon_1 LEFT OUTER JOIN owner AS owner_2 ON owner_2.id = anon_1.thing_owner_id ORDER BY anon_1.thing_id, owner_2.id

Fine.

b) SQLAlchemy r3771, deferred "thing.owner_id":

SELECT anon_1.thing_id AS anon_1_thing_id, anon_1.thing_name AS anon_1_thing_name, owner_2.id AS owner_2_id, owner_2.name AS owner_2_name 
FROM (SELECT thing.id AS thing_id, thing.name AS thing_name, thing.id AS thing_oid 
FROM thing 
WHERE thing.id = %s ORDER BY thing.id 
 LIMIT 1) AS anon_1 LEFT OUTER JOIN owner AS owner_2 ON owner_2.id = thing.owner_id, thing ORDER BY anon_1.thing_id, owner_2.id

Here's the difference:

LEFT OUTER JOIN owner AS owner_2 ON owner_2.id = thing.owner_id, thing

and here's what MySQL has to say about it:

sqlalchemy.exceptions.OperationalError: (OperationalError) (1054, "Unknown column 'thing.owner_id' in 'on clause'") u'SELECT anon_1.thing_id AS anon_1_thing_id, anon_1.thing_name AS anon_1_thing_name, owner_2.id AS owner_2_id, owner_2.name AS owner_2_name \nFROM (SELECT thing.id AS thing_id, thing.name AS thing_name, thing.id AS thing_oid \nFROM thing \nWHERE thing.id = %s ORDER BY thing.id \n LIMIT 1) AS anon_1 LEFT OUTER JOIN owner AS owner_2 ON owner_2.id = thing.owner_id, thing ORDER BY anon_1.thing_id, owner_2.id' [1](1)

Comments (5)