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
