Filtering on a union of join silently fails to filter.

[Christophe Biocca]

I attached a reproduction of the bug. The important bit:

# This doesn't filter both side of the union, and the generated sql looks really wrong.
session.query(B).join(A).union(session.query(B).join(A)).filter(A.id == '2').count()

Simple workaround was to duplicate the filter call to each query before the union. To be honest, I'm not even sure the filter call is valid (but at least it should throw an error, not let everything go through).

[Mike Bayer]

its not silent at all it is just not aliasing A on the second go:

SELECT anon_1.b_id AS anon_1_b_id, anon_1.b_a_id AS anon_1_b_a_id 
FROM (SELECT b.id AS b_id, b.a_id AS b_a_id 
FROM b JOIN a ON a.id = b.a_id UNION SELECT b.id AS b_id, b.a_id AS b_a_id 
FROM b JOIN a ON a.id = b.a_id) AS anon_1, a 
WHERE a.id = :id_1

[Christophe Biocca]

Is this expected behaviour then?

[Mike Bayer]

this is a hard one, but lets start with this:

SELECT anon_1.b_id AS anon_1_b_id, anon_1.b_a_id AS anon_1_b_a_id 
FROM (SELECT b.id AS b_id, b.a_id AS b_a_id 
FROM b JOIN a ON a.id = b.a_id UNION SELECT b.id AS b_id, b.a_id AS b_a_id 
FROM b JOIN a ON a.id = b.a_id) AS anon_1

given that SQLAlchemy never changes the structure of an already-generated SQL statement, it can only replace elements if necessary, the above SELECT cannot be ordered by A.id. There is no column which derives from it expressed in the list of columns available. So when it tacks on the "a" into the FROM list, that's the behavioral contract of a select() - anything that's in the WHERE clause gets added to the FROM list automatically if not present already.

the only way your query would do what you expect, without changing how you're calling it, is if SQLAlchemy added a new column to the SELECT list of the inner query, which is not something I think it would ever know how to do.

If you include A.id in the columns clause then it is fine:

print session.query(B, A.id).join(A).union(session.query(B, A.id).join(A)).filter(A.id == '2')

[Christophe Biocca]

That makes sense, I'll make sure to take that into account in our code. Thanks.

[Mike Bayer]

tricky one, but i dont think there's a change we can make here.