Type checking still has issues with single-inheritance joins (related to #3222)

[Boris Kozinsky]

When doing multiple joins with single-inheritance objects, type/discriminator checking has issues. In some cases, the checking is not done when "from_joinpoint = True" is used. In other cases, when explicit two-argument joins are used, the type checking is on the wrong alias. See tests 8 and 9 in attached test_inh_query.py file.

[Mike Bayer]

going through. there's a strong theme in these test cases that the queries are nonsensical. Calc, Struc, Data, and Node are all the same table. You can't use more than one of those in a single query as themselves without using aliased(). I'll go through but I suspect most/all of these are not bugs. There's no automatic aliasing if you use a Calc and Struc together:

print session.query(Struc).join(Calc)

output:

sqlalchemy.exc.InvalidRequestError: Can't join table/selectable 'node' to itself

all of these queries seem to name Struc and Calc directly. It can't (and will never) work that way, unfortunately.

[Mike Bayer]

so q4:

q4 = session.query(Struc).\
        join(Link.start_node, aliased=True).\
        join(Calc.out_links, aliased=True)

this is nonsensical. There is no join from Struc->Link. There's no indication that we're joining to a Calc, Link.start_node joins to Node so no "on" clause is needed. The sudden presense of Calc there is not going to help. No bug with this one. Here's how it should look:

calc_alias = aliased(Calc)

q4 = session.query(Struc).\
    join(Struc.out_links).\
    join(Link.start_node.of_type(calc_alias)).\
    join(calc_alias.out_links, aliased=True)

SQL:

SELECT node.id AS node_id, node.name AS node_name, node.type AS node_type 
FROM node JOIN link ON link.start_id = node.id JOIN node AS node_1 ON link.start_id = node_1.id AND node_1.type IN (:type_1) JOIN link AS link_1 ON link_1.start_id = node_1.id 
WHERE node.type IN (:type_2)

[Mike Bayer]

q5:

q5 = session.query(Struc).\
    join(Struc.out_links).\
    join(Calc.in_links, aliased=True)

It's wrong, yup, because "Struc.out_links" points to a Link, not a Calc, and the join(Calc.in_links) is no different from saying join(Struc.in_links), because these are the same table. So essentially its query(Struc).join(Struc.out_links).join(Struc.in_links, aliased=True) and that is exactly the SQL we get:

SELECT node.id AS node_id, node.name AS node_name, node.type AS node_type 
FROM node JOIN link ON link.start_id = node.id JOIN link AS link_1 ON link_1.end_id = node.id 
WHERE node.type IN (:type_1)

again if you want Struc and Calc represented separately, you need to aliased() one or both of them, and additionally if you want to join from Link to Calc you need to specify that as well:

calc_alias = aliased(Calc)

q5 = session.query(Struc).\
    join(Struc.out_links).\
    join(Link.start_node.of_type(calc_alias)).\
    join(calc_alias.in_links, aliased=True)

I'm assuming Link.start_node, the query doesn't specify which one. This produces the expected SQL:

SELECT node.id AS node_id, node.name AS node_name, node.type AS node_type 
FROM node JOIN link ON link.start_id = node.id JOIN node AS node_1 ON link.start_id = node_1.id AND node_1.type IN (:type_1) JOIN link AS link_1 ON link_1.end_id = node_1.id 
WHERE node.type IN (:type_2)

[Mike Bayer]

q8 is pretty complicated, and I'm surprised of_type() works with aliased=True but it does. It looks like what you describe is actually happening on this one, Link.end_node.of_type(Data) isn't getting the discriminator. But this is a lot easier to see with a minimal query:

print session.query(Link).\
    join(Link.end_node.of_type(Calc), aliased=True, from_joinpoint=True)

So that's a bug report! this is good for 0.9 as it is small.

[Mike Bayer]

q9 has something very wrong with it, that one is aliasing totally where it should not be, and not consistently. these two queries should be the same:

print session.query(Struc).\
    join(Link, Struc.out_links).\
    join(Calc, Link.end_node)

print session.query(Struc).\
    join(Link, Struc.out_links).\
    join(Calc, Calc.id == Link.end_id)

but they are not. the first one is aliasing "Calc" somewhere and I really think that's very wrong. this is kind of ominous and I'm making a second issue for this one.

[Mike Bayer]

• Fixed bug where the ON clause for :meth:.Query.join, and :meth:.Query.outerjoin to a single-inheritance subclass using of_type() would not render the "single table criteria" in the ON clause if the from_joinpoint=True flag were set. fixes #3232

Conflicts: test/orm/inheritance/test_single.py

→ <<cset c5fa20fb2a85>>

[Mike Bayer]

• Fixed bug where the ON clause for :meth:.Query.join, and :meth:.Query.outerjoin to a single-inheritance subclass using of_type() would not render the "single table criteria" in the ON clause if the from_joinpoint=True flag were set. fixes #3232

→ <<cset 47d316ec665e>>

[Mike Bayer]

see #3233. almost finished

[Mike Bayer]

so #3233 is fixed. there's no automatic aliasing of single-inh entities and fortunately no test cases were relying on this, the aliasing that occurs in your q9 is not supposed to be there, that's fixed. It's only for 1.0 as people may have been relying on that and a migration note is added at http://docs.sqlalchemy.org/en/latest/changelog/migration_10.html#single-inheritance-join-targets-will-no-longer-sometimes-implicitly-alias-themselves