Good question On 19 January 2018 at 06:04, petern <peter.nichvolo...@gmail.com> wrote:
> 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
