[sqlite] Defect: Redundant CTE table materialization gives wrong answer.

[petern]

WITH flips(s) AS (VALUES (random()>0), (random()>0), (random()>0))
SELECT sum(s),(SELECT sum(s) FROM flips) FROM flips;
sum(s),"(SELECT sum(s) FROM flips)"
1,3
--Expected output is 1,1.

Why isn't the constant notional table table [flips] materialized just once
per CTE?

FYI.  PostgreSQL 9.6 materializes [flips] exactly once per CTE:

WITH flips(s) AS (VALUES (random()), (random()), (random()))
SELECT sum(s),(SELECT sum(s) FROM flips) FROM flips;
sum sum
1.503042308613658 1.503042308613658

Also FYI, double checked:  random() is indeed run 3 times by PostgreSQL.

WITH flips(s) AS (VALUES (random()), (random()), (random()))
SELECT * FROM flips;

s
0.760850821621716
0.9941047639586031
0.48273737309500575

Peter
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users