Exactly. But that doesn't resolve the problem of duplicated non-deterministic tables in a CTE. CTE is the acryonym for Common TABLE Expression, not Common View Expression.
eg: WITH x AS (<query with non-deterministic rows>) SELECT * FROM x UNION SELECT * FROM x; --Should return just x, not x1 UNION x2. Peter On Sat, Jan 20, 2018 at 3:17 PM, Shane Dev <devshan...@gmail.com> wrote: > The following statement executes the random() function twice - > > sqlite> select random() union all select random(); > random() > 2678358683566407062 > -5528866137931448843 > sqlite> explain select random() union all select random(); > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Init 0 1 0 00 > 1 Function0 0 0 1 random(0) 00 > 2 ResultRow 1 1 0 00 > 3 Function0 0 0 1 random(0) 00 > 4 ResultRow 1 1 0 00 > 5 Halt 0 0 0 00 > > but if random() is replaced with round(1.1) - > > sqlite> select round(1.1) union all select round(1.1); > round(1.1) > 1.0 > 1.0 > sqlite> explain select round(1.1) union all select round(1.1); > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Init 0 6 0 00 > 1 Copy 2 1 0 00 > 2 ResultRow 1 1 0 00 > 3 Copy 2 1 0 00 > 4 ResultRow 1 1 0 00 > 5 Halt 0 0 0 00 > 6 Real 0 3 0 1.1 00 > 7 Function0 1 3 2 round(1) 01 > 8 Goto 0 1 0 00 > > It seems to be executed once only. Does this happen because random() is > flagged non-deterministic? > > > On 19 January 2018 at 09:10, Clemens Ladisch <clem...@ladisch.de> wrote: > > > petern 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-users > > > > Its documentation says: > > <https://www.postgresql.org/docs/9.6/static/queries-with.html> > > | A useful property of WITH queries is that they are evaluated only once > > | per execution of the parent query, even if they are referred to more > > | than once by the parent query or sibling WITH queries. Thus, expensive > > | calculations that are needed in multiple places can be placed within > > | a WITH query to avoid redundant work. Another possible application is > > | to prevent unwanted multiple evaluations of functions with side- > > | effects. However, the other side of this coin is that the optimizer is > > | less able to push restrictions from the parent query down into a WITH > > | query than an ordinary subquery. > > > > This is an implementation detail of Postgres, and it is not required by > > the SQL specification. SQLite chose the other side of the coin. > > > > > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users