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

[Jigal van Hemert]

Ries van Twisk 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'
>>> );
> 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.

Can you give an example for this situation?

If T2.size is NULL, T2.size=4 becomes NULL=4 which evaluates to "not 
true" in the expression.
Actually:
Size   (Size=4) IS TRUE	  (Size=4) IS FALSE   (Size=4) IS UNKNOWN
4	1			0			0
3	0			1			0
NULL	0			0			1
For conditions NULL=4 is "not true", so that will work fine (in MySQL at 
least).

The only difference AFAIK can be that the query optimizer does not move 
this condition to an early stage in the JOIN processing. The condition 
"should" be in the ON clause to suggest to the query optimizer that it 
can be used at that point to decide which rows to consider for that join.
If the condition is in the WHERE clause it might be processed later, 
which leads to a larger possible result set before the WHERE conditions 
are applied.

So far I've seen that other DBMS-s might handle this and other SQL 
features in different ways.


-- 
Jigal van Hemert
skype:jigal.van.hemert
msn: jigal at xs4all.nl
http://twitter.com/jigalvh