Consider the hypothetical CTE concerned with displaying the n'th coin flip of a random series as follows. [This is a simplified proxy for any computation where an earlier computed table supplies the parameters for a later computed table.]
sqlite> .v SQLite 3.22.0 2018-01-12 23:38:10 dec3ea4e4e6c4b1761ddc883a29eaa50dcd663ce6199667cc0ff82f7849d4f2a ---------------------------- --This way works as expected: note how the final select is not joining the params table. sqlite> 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%5=0 LIMIT 3; flip,side 5,0 10,1 15,0 --However, 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; --Also infinite loop. --Simply cross join params table without using any columns from it. 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%5=0 LIMIT 3; --So, what's going on here? [params] is a constant one row table! How can it be adding any rows to the output set let alone an infinite number? ---------------------------- Peter _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
