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 [email protected] <mailto:[email protected]> ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn [email protected] <mailto:[email protected]> • 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.
