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 sid
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 (
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=
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
dec3ea4e4e6c4
4 matches
Mail list logo
