That's interesting because a cross join can always be optimized for lazy evaluation as rows are generated. There are no join constraints.
As it stands, without lazy cross join optimization, the only general solution here is to 'pick' individual params from using subqueries: 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 WHERE flip%(SELECT n FROM params)=0 LIMIT 3; 'flip','side' 5,1 10,0 15,1 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