Thank you for your answer, Mark,

my friend and collegue Frank (fsg) and I kept in mind, that at the prior
to the last FB-conference, we listend to an optimization talk of one of
the core devs (it must have been Dimitry ;-) ) and remembered that:

- engine does not reorder joins for optimization purpose if left join
and straight join get mixed
- engine evaluates join before left join.

This was, what made me assue, the expression would be evaluated like the
braced version. I did not check SQL standard definition yet, but it
seemed somewhat natural to me...

Thomas

Am 20.03.2014 21:04, schrieb Mark Rotteveel:

>
>
> On 20-3-2014 10:52, Thomas Beckmann wrote:
>> Please consider the following example:
>>
>> with recursive
>> CTE_CNT as (select 1 as I from RDB$DATABASE
>> union all select I + 1 from CTE_CNT where I < 20)
>>
>>
>> select i.I, j.I, k.I from CTE_CNT i
>> left join CTE_CNT j on i.I = j.I and j.I<10
>> join CTE_CNT k on j.I = k.I
>>
>> I would expect this to behave as
>>
>> select i.I, j.I, k.I from CTE_CNT i
>> left join (CTE_CNT j
>> join CTE_CNT k on j.I = k.I) on i.I = j.I and j.I<10
>>
>> But it does not: It behaves like
>>
>> select i.I, j.I, k.I from CTE_CNT i
>> join CTE_CNT j on i.I = j.I and j.I<10
>> join CTE_CNT k on j.I = k.I
>>
>> Can someone explain this to me? Is this as intended or is this a bug?
>
> Why are you expecting that? Your second join condition explicitly
> disallows that.
>
> Say we do:
> with recursive CTE_CNT as (
> select 1 as I from RDB$DATABASE
> union all select I + 1 from CTE_CNT where I < 20
> )
> select i.I, j.I /*, k.I*/ from CTE_CNT i
> left join CTE_CNT j on i.I = j.I and j.I<10
> -- join CTE_CNT k on j.I = k.I
>
> This results in:
> i.I 1-10 having j.I 1-10
> and i.I 11-20 having j.I NULL
>
> When you next include the second join you are executing the condition
> j.I = k.I, as a result it will only return rows for i.I 1-10, because
> for values of j.I NULL the join condition is false.
>
> Maybe you meant to use a left join in your second join as well?

--
Mit freundlichen Grüßen,

Thomas Beckmann
Diplom-Informatiker


Wielandstraße 14c • 23558 Lübeck
Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604
Mail thomas.beckmann@... <mailto:thomas.beckmann@...>

ASSFINET-Logo

*ASSFINET Dienstleistungs-GmbH*
Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn
info@... <mailto:info@...> • www.assfinet.de
<http://www.assfinet.de/>

Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann
Registergericht Koblenz HRB 23331

Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der
richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Mail.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist
nicht gestattet.