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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

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