This also works as expected in PostreSQL but not in SQLite.
WITH RECURSIVE params(n) AS (
VALUES (5)
),
coinflip(flip,side) AS (
SELECT 1, random()>0.5
UNION ALL
SELECT flip+1, random()>0.5 FROM coinflip
)
SELECT flip,side FROM coinflip,params WHERE flip%n=0 LIMIT 3;
flip side
5 true
10 false
15 true
--no infinite loop here. On the other hand, SQLite 3.22 beta plans an
infinite loop.
On Mon, Jan 15, 2018 at 1:10 AM, Clemens Ladisch <clem...@ladisch.de> wrote:
> petern wrote:
> > there is an infinite loop when params table column "n" is used.
>
> > WITH params(n) AS (
> > VALUES (5)
> > ),
> > coinflip(flip,side) AS (
> > SELECT 1, random()>0
> > UNION ALL
> > SELECT flip+1, random()>0 FROM coinflip
> > )
> > SELECT flip,side FROM coinflip,params WHERE flip%n=0 LIMIT 3;
>
> > So, what's going on here? [params] is a constant one row table!
>
> But "coinflip" is infinitely large. And the EXPLAIN output shows that
> the database tries to compute the entire table before doing the join.
>
> The only reliable way to prevent the infinite loop is to put a LIMIT (or
> a WHERE on a counter) inside the recursive CTE.
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
