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
