Affects Version/s: 2.0.2
Fix Version/s: None
Security Level: All
When using explicit join syntax, MySQL requires the from-clause argument-list to have either only one entry, or, if it contains multiple entries, it requires that the complete argument-list be surrounded by parantheses. Currently, Doctrine 2 does not respect this requirement when compiling DQL statements, and produces broken SQL in some situations.
Consider the following example of a legitimate DQL query.
This will be compiled down to:
This SQL query can't be executed, because MySQL will complain. The error message is "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'v0_.vendorListXML_dbID' in 'on clause".
The resulting error gives no sensible error message, and is a pain to debug, because obviously, the column mentioned does exist. The real cause of the error is that MySQL will cease to correctly interpret the aliases given, as long as parantheses not are used. I acknowledge that the MySQL behavior is downright stupid. This might well be considered a bug in MySQL, but unfortunately, the parantheses are documented in their syntax docs (http://dev.mysql.com/doc/refman/5.0/en/join.html), though I could not find any official statement regarding the use.
Fortunately it is easy to patch Doctrine to support it.
Now, let's add parantheses around the from clause argument list of the generated SQL statement:
This works without complaints.
The example query is a bit peculiar in that it uses both explicit join syntax and various from-clause entries. But according to DQL's EBNF definition, there is nothing that should prevent Doctrine 2 users from doing it. And it must work this way, because anyway else it would be impossible to specify join criteria that are no declared relationships (using explicit join-syntax only), and the other way around (using from-clause entries only, i. e. no explicit join syntax, and specifying the join conditions in the where clause) makes it impossible to join on foreign keys. Hence, under such circumstances, combining both approaches is a requirement if joins should be fully expressive.
It is possible to completely work around these issues by sticking to a single from-clause entry, using explicit join syntax, and adding all further joins that require non-foreign-key join expressions into correlated subqueries. However, this is not straightforward and forces users into a workaround for what joins are intended for in the first place.
This scenario is annoying, because if you were to suppress that use case completely, the EBNF would have to be significantly rewritten (and it would be the wrong thing to do IMO anyway).
I believe the most straightforward solution is to always add parantheses around the from-clause argument-list for the MySQL dialect.
I found this web page, which might be worth reading to get an understanding of the problem, as it describes the exact same issue (though with Hibernate):