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 <[email protected]> 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 <[email protected]> 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
> > [email protected]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
