Re: [Firebird-devel] CTE problem

[Frank <fr...@sc...>]

On 11/20/2012 10:53 AM, Frank Schlottmann-Gödde wrote:
> Hi,
> I asked this in the support list, but got no answer:
> 
> 
> I do not understand why
> 
> set planonly;
> with recursive
>   CTE_CNT1 as (select 0 as I
>     from RDB$DATABASE
>     union all select I + 1
>     from CTE_CNT1
>     where I < 9),
> 
>   CTE_CNT2 as (select I, 0 as J
>     from CTE_CNT1
>     union all select J * 10 + c1.I, c2.J + 1
>     from CTE_CNT1 c1
>     join CTE_CNT2 c2 on c2.J < 10)
> 
> select I from CTE_CNT2;
> 
> fails with:
> Statement failed, SQLSTATE = 42S22
> Dynamic SQL Error
> -SQL error code = -206
> -Column unknown
> -C2.J
> -At line 14, column 15
> 
> while the similar
> 
> with recursive
>   CTE_CNT1 as (select 0 as I
>     from RDB$DATABASE
>     union all select I + 1
>     from CTE_CNT1
>     where I < 9),
> 
>   CTE_CNT2 as (select 0 as I, 0 as J
>     from RDB$DATABASE
>     union all select J * 10 + c1.I, c2.J + 1
>     from CTE_CNT1 c1
>     join CTE_CNT2 c2 on c2.J < 10)
> 
> select I from CTE_CNT2;
> 
> succeds with
> 
> PLAN (CTE_CNT2 RDB$DATABASE NATURAL, CTE_CNT2 CTE_CNT1 RDB$DATABASE
> NATURAL, JOIN ())
>
No idea, anybody?

Frank

-- 
"Fascinating creatures, phoenixes, they can carry immensely heavy loads,
  their tears have healing powers and they make highly faithful pets."
      - J.K. Rowling