Another implementation detail? I was wondering where you were, Clemens. You're entitled to your opinion but I'd rather hear from somebody with actual source check-in privileges.
I've posted at least 5 legitimate defects which triangulate on a consistency problem when trying to access properties of the current statement by any of CTE, cross join, aux_data, pointer, and subtype API's. What response did I get? Crickets - and then Clemens telling me I'm imagining things because these are ALL implementation details. Well, Clemens you are definitely wrong about the CTE. If table aliases of CTE's were intended as ordinary independent views, the CTE syntax makes no sense. Every other implementation agrees with the expected set-mathematical definition. SQLite is producing the wrong answer and I'm looking forward to seeing the trouble ticket that fixes this problem. Peter On Fri, Jan 19, 2018 at 12:10 AM, 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
