Re: [sqlite] Defect: single row table cross join causes infinite loop
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 Ladischwrote: > 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
Re: [sqlite] Defect: single row table cross join causes infinite loop
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 Ladischwrote: > 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
Re: [sqlite] Defect: single row table cross join causes infinite loop
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