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
