Besides PostrgreSQL, MySQL also says x is TABLE with exactly one row as x UNION x = x
WITH x AS (SELECT rand()) SELECT * FROM x UNION SELECT * FROM x; rand() 0.6710336931711377 Peter On Sat, Jan 20, 2018 at 7:31 PM, Cory Nelson <phro...@gmail.com> wrote: > CTEs are not as-if-memoized in SQL Server either. I can't find any > standards language saying if they should or shouldn't be, which > typically indicates "anything goes". > > On Sat, Jan 20, 2018 at 5:57 PM, petern <peter.nichvolo...@gmail.com> > wrote: > > 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 > > > > -- > Cory Nelson > http://int64.org > _______________________________________________ > 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
