bug with JOIN precedence

Issue #3280 closed
yoch created an issue

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

Comments (2)

  1. Mike Bayer repo owner
    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.

  2. Log in to comment