Issues

Issue #3131 resolved

innerjoin=True annotation is ignored sometimes

Gabor Gombas
created an issue

Hi,

See the attached test script - the only difference between classes C and D is the order of the definition of the two relations, yet querying C generates a LEFT OUTER JOIN to A, while querying D generates an inner JOIN as expected. Tested with rel_0_9; 0.8.1 generates the same query in both cases.

Gabor

Comments (15)

  1. Gabor Gombas reporter

    Sorry for the ambiguity - 0.8:

    SELECT ... FROM c LEFT OUTER JOIN b b_1 ON b_1.id = c.b_id JOIN a a_1 ON a_1.id = c.a_id
    SELECT ... FROM d JOIN a a_1 ON a_1.id = d.a_id LEFT OUTER JOIN b b_1 ON b_1.id = d.b_id
    

    While 0.9:

    SELECT ... FROM c LEFT OUTER JOIN b b_1 ON b_1.id = c.b_id LEFT OUTER JOIN a a_1 ON a_1.id = c.a_id
    SELECT ... FROM d JOIN a a_1 ON a_1.id = d.a_id LEFT OUTER JOIN b b_1 ON b_1.id = d.b_id
    
  2. Gabor Gombas reporter

    Ah ok, that's an older thread. Unfortunately, innerjoin="nested" does not seem to work:

    SELECT c.id AS c_id, c.a_id AS c_a_id, c.b_id AS c_b_id, b_1.id AS b_1_id, a_1.id AS a_1_id 
    FROM c LEFT OUTER JOIN (b b_1 JOIN a a_1 ON a_1.id = c.a_id) ON b_1.id = c.b_id
    
    sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-00904: "C"."A_ID": invalid identifier
    
  3. Mike Bayer repo owner

    wow, the 'nested' feature generates bad SQL. I have to revisit what that feature is supposed to do exactly, separate issue from this.

  4. Mike Bayer repo owner
    • Fixed a regression caused by ticket2976 released in 0.9.4 where the "outer join" propagation along a chain of joined eager loads would incorrectly convert an "inner join" along a sibling join path into an outer join as well, when only descendant paths should be receiving the "outer join" propagation; additionally, fixed related issue where "nested" join propagation would take place inappropriately between two sibling join paths.

    this is accomplished by re-introducing the removed flag "allow_innerjoin", now inverted and named "chained_from_outerjoin". Propagating this flag allows us to know when we have encountered an outerjoin along a load path, without confusing it for state obtained from a sibling path.

    fixes #3131 ref #2976

    → <<cset 61384fd0e529>>

  5. Mike Bayer repo owner
    • Fixed a regression caused by ticket2976 released in 0.9.4 where the "outer join" propagation along a chain of joined eager loads would incorrectly convert an "inner join" along a sibling join path into an outer join as well, when only descendant paths should be receiving the "outer join" propagation; additionally, fixed related issue where "nested" join propagation would take place inappropriately between two sibling join paths.

    this is accomplished by re-introducing the removed flag "allow_innerjoin", now inverted and named "chained_from_outerjoin". Propagating this flag allows us to know when we have encountered an outerjoin along a load path, without confusing it for state obtained from a sibling path.

    fixes #3131 ref #2976

    → <<cset 43663e7aac99>>

  6. Log in to comment