Re: [Firebird-devel] CTE problem
|
From: Frank Ingermann <frank.ingermann@kl...> - 2012-11-29 10:25:27
|
Hi Frank, Am 29.11.2012 07:51, schrieb Frank: > On 11/20/2012 10:53 AM, Frank Schlottmann-Gödde wrote: > > No idea, anybody? > Isn't it so that a CTE Select can only have one recursive member? (at least that's what i remember from Vlad's presentation back in Bergamo, but i can't find a proof for it right now.) In your first example the 2nd CTE is also recursive, in the 2nd one it isn't. cheers, Frank |
Thread view
|
From: Frank Schlottmann-Gödde <frank@sc...> - 2012-11-20 09:51:30
|
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 ())
Frank
-- "Fascinating creatures, phoenixes, they can carry immensely heavy
loads, their tears have healing powers and they make highly faithful
pets." - J.K. Rowling
|
|
From: Dmitry Yemanov <firebird2@ya...> - 2012-11-20 10:03:21
|
20.11.2012 13:53, Frank Schlottmann-Gödde wrote: > > I asked this in the support list, but got no answer: First of all, what FB version are you talking about? Dmitry |
|
From: Frank <frank@sc...> - 2012-11-20 10:38:19
|
On 11/20/2012 11:03 AM, Dmitry Yemanov wrote:
> 20.11.2012 13:53, Frank Schlottmann-Gödde wrote:
>>
>> I asked this in the support list, but got no answer:
>
> First of all, what FB version are you talking about?
>
Sorry,
2.5.2.26510 and 3.0.0.30126
Frank
--
"Fascinating creatures, phoenixes, they can carry immensely heavy loads,
their tears have healing powers and they make highly faithful pets."
- J.K. Rowling
|
|
From: Frank <frank@sc...> - 2012-11-29 06:51:13
|
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
|
|
From: Frank Ingermann <frank.ingermann@kl...> - 2012-11-29 10:25:27
|
Hi Frank, Am 29.11.2012 07:51, schrieb Frank: > On 11/20/2012 10:53 AM, Frank Schlottmann-Gödde wrote: > > No idea, anybody? > Isn't it so that a CTE Select can only have one recursive member? (at least that's what i remember from Vlad's presentation back in Bergamo, but i can't find a proof for it right now.) In your first example the 2nd CTE is also recursive, in the 2nd one it isn't. cheers, Frank |
|
From: Frank Schlottmann-Gödde <frank@sc...> - 2012-11-30 16:42:51
|
On 29.11.2012 11:08, Frank Ingermann wrote:
> Hi Frank,
>
> Am 29.11.2012 07:51, schrieb Frank:
>> On 11/20/2012 10:53 AM, Frank Schlottmann-Gödde wrote:
>>
>> No idea, anybody?
>>
> Isn't it so that a CTE Select can only have one recursive member?
I don't think so, because the construct we have problems with, already
contains two recursive CTE's, must be something else.
I can post the real one's if there is interest.
Frank
--
"Fascinating creatures, phoenixes, they can carry immensely heavy loads,
their tears have healing powers and they make highly faithful pets."
- J.K. Rowling
|
|
From: Frank Ingermann <frank.ingermann@kl...> - 2012-12-03 10:55:44
|
Hi Frank, Am 30.11.2012 17:42, schrieb Frank Schlottmann-Gödde: > On 29.11.2012 11:08, Frank Ingermann wrote: >> Isn't it so that a CTE Select can only have one recursive member? > I don't think so, because the construct we have problems with, already > contains two recursive CTE's, must be something else. You're right - and i was (kind of) right, too :-) A _single CTE_ can only have one recursive member, but you have multiple CTEs - which _should_ work. So i'm with Vlad: looks like a bug... cheers, Frank |
|
From: Vlad Khorsun <hvlad@us...> - 2012-12-01 13:49:46
|
> 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 ())
I think, this is a bug.
Regards,
Vlad
|
|
From: Frank <frank@sc...> - 2012-12-04 08:10:12
|
On 12/01/2012 02:49 PM, Vlad Khorsun wrote:
> I think, this is a bug.
I added it to the tracker.
kind regards
Frank
--
"Fascinating creatures, phoenixes, they can carry immensely heavy loads,
their tears have healing powers and they make highly faithful pets."
- J.K. Rowling
|