Re: WITH RECURSIVE updated to CVS TIP

On Sat, Jul 05, 2008 at 10:43:57AM +0200, Hans-Juergen Schoenig wrote:
> hello david,
>
> i did some quick testing with this wonderful patch.
> it seems there are some flaws in there still:
>
> test=# explain select count(*)
> test-#         from ( WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT
> DISTINCT n+1 FROM t )
> test(#                 SELECT * FROM t WHERE n < 5000000000) as t
> test-#         WHERE n < 100;
> server closed the connection unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
> !> \q
>
> this one will kill the planner :(
> removing the (totally stupid) distinct avoids the core dump.

Any idea why this might be happening?

> i found one more issue;
>
> -- broken: wrong result
> test=# select count(*) from ( WITH RECURSIVE t(n) AS (
>         SELECT 1 UNION ALL SELECT n + 1 FROM t)
>         SELECT * FROM t WHERE n < 5000000000) as t WHERE n < (
>                 select count(*) from ( WITH RECURSIVE t(n) AS (
>                         SELECT 1 UNION ALL SELECT n + 1 FROM t )
>         SELECT * FROM t WHERE n < 5000000000) as t WHERE n < 100) ;
>  count
> -------
>      1
> (1 row)
>
> if i am not totally wrong, this should give us a different result.

What result should it give, and what do you think is going wrong here?

> i am looking forward to see this patch in core :).

So am I :)

> it is simply wonderful ...
>
>     many thanks,

Thanks go to the kind people who actually wrote the thing.  I've just
been using git to keep the bit-rot off it :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate