bug with JOIN precedence

[yoch]

Hi,

I wrote this code in python :

db.query(A, B, C).\ filter(func.coalesce(A.idB,A.idC)==B.id).\ outerjoin(C, C.id==B.id).\ all()

And it cause an strange error: (1054, "Unknown column 'A.id' in 'on clause'")

This happens because the produced SQL code looks like :

SELECT A., B., C.* FROM B, A LEFT OUTER JOIN C ON C.id = B.id WHERE coalesce(A.idB, A.idC) = B.id;

And this code is broken, because it mix commas joins withs others types of joins, and this lead an error. http://stackoverflow.com/questions/10483421/mysql-unknown-column-in-on-clause

[Mike Bayer]

1. this is a usage issue, not a bug or feature request. Please use the mailing list at https://groups.google.com/forum/#!forum/sqlalchemy.

2. the SQL emitted is correct based on what you are requesting. You are asking for rows from A, B and C, and are requesting a join "to" "C", but you are not stating what you'd like to be joining from, so SQLAlchemy as documented at http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html#sqlalchemy.orm.query.Query.join, see "controlling what to join from". But also, since you have three FROM clauses, you'd need two JOIN clauses in order for these three to be linked without any comma in your FROM list (which also is, by the way, valid SQL, the issue you have is because the ON clause isn't matching up).

Post the specific SQL you want on the mailing list and it will be very easy to show you how to get it.

[yoch]

OK, thanks you for your help. Expliciting the two joins works well.