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 wrote:
> CTEs are not as-if-memoized in SQL Server either. I can't
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 wrote:
> Exactly. But that doesn't resolve the problem of duplicated
> non-deterministic
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 ()
SELECT * FROM x UNION SELECT * FROM x; --Should return just x, not
x1 UNION x2.
Peter
On Sat, Jan
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 p1p2p3p4 p5 comment
---
On 2018/01/19 11:30 AM, petern wrote:
You're entitled to your opinion but I'd rather hear from somebody with
actual source check-in privileges.
Perhaps mail a dev directly then? On here you will never satisfy that
requirement.
___
sqlite-users m
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 o
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?
NO. I wrote that to rule out the objection that PostrgeSQL is getting the
right answer because random() is only being computed once per statement.
I naturally DO expect random() to run every time it is called. What I
wrote is a formality for those thinking ahead about what else could be
happening.
On 19 Jan 2018, at 5:04am, petern wrote:
> WITH flips(s) AS (VALUES (random()), (random()), (random()))
> SELECT * FROM flips;
>
> s
> 0.760850821621716
> 0.9941047639586031
> 0.48273737309500575
Are you expecting the three values to be the same ? What about
INSERT INTO MyTable VAKUES (random
Good question
On 19 January 2018 at 06:04, 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 t
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 materi
11 matches
Mail list logo
