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?

Mark
--
Mark Rotteveel