[TYPO3-core] FYI #14182: Multiple join conditions are not supported

[Ries van Twisk]

On Apr 22, 2010, at 2:54 PM, Jigal van Hemert wrote:

> Xavier Perseguers wrote:
>> $rec = $GLOBALS['TYPO3_DB']->exec_SELECTquery(
>>    '*',
>>    'T1 LEFT OUTER JOIN T2 ON T2.pid=T1.uid AND T2.size=4',
>>    'T1.cr_userid=1'
>> );
>> Problem is that this query cannot be rewritten due to the T2.size=4  
>> which cannot be moved to the WHERE clause and cannot be left alone  
>> as left outer join condition.
>
> Just curious, as it might be different for other DBMS-s:
> why can't you move it to the WHERE clause? The MySQL manual only  
> says: "The conditional_expr used with ON is any conditional  
> expression of the form that can be used in a WHERE  clause.  
> Generally, you should use the ON  clause for conditions that specify  
> how to join tables, and the WHERE clause to restrict which rows you  
> want in the result set."
>
> There is 'should' in the text, not 'must'. The ON condition can be  
> put in the WHERE clause, but it might perform better if the  
> conditions are in the correct ON clause.
>
> [1] http://dev.mysql.com/doc/refman/5.1/en/join.html
>

you cannot always move T2.size to the where clause specially not with  
a outer join,
this get's most tricky when size can be NULL.

Ries