From: | Tom van Tilburg <tom(dot)van(dot)tilburg(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | inconsistent behaviour of set-returning functions in sub-query with random() |
Date: | 2016-09-23 20:34:32 |
Message-ID: | CAP3PPDiucxYCNev52=YPVkrQAPVF1C5PFWnrQPT7iMzO1fiKFQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
Hi List,
Note beforehand: this question is a result of a stack-exchange that can be
seen here:
http://stackoverflow.com/questions/39624241/inconsistent-behaviour-of-set-returning-functions-in-sub-query-with-random
I'm often using the WHERE clause random() > 0.5 to pick a random subset of
my data. Now I noticed that when using a set-returning function in a
sub-query, I either get the whole set or none (meaning that the WHERE
random() > 0.5 clause is interpreted *before* the set is being generated).
e.g.:
SELECT num FROM (
SELECT unnest(Array[1,2,3,4,5,6,7,8,9,10]) num) AS foo WHERE random() > 0.5;
This seems inconsistent because the following query *does* take the whole
set into account:
SELECT num FROM (
SELECT * FROM unnest(Array[1,2,3,4,5,6,7,8,9,10]) num) AS foo
WHERE random() > 0.5;
So does this one:
WITH foo AS (
SELECT unnest(Array[1,2,3,4,5,6,7,8,9,10]) num)
SELECT num FROM foo WHERE random() > 0.5;
Could anyone reflect on the seeming inconsistency here? I do understand
that the planner sees the queries quite different (as can be seen from an
EXPLAIN) but I don't understand the rationale behind it.
Notes:
-
couldn't find another function to test apart from random(), but likely
there is some
-
I tested with generate_series and as well
-
My real use case works with postgis and pgpointcloud where a range of
set-returning functions is used in this manner
Thanks,
Tom
From | Date | Subject | |
---|---|---|---|
Next Message | phb07 | 2016-09-23 20:57:47 | Re: journaling / time travel |
Previous Message | Adrian Klaver | 2016-09-23 16:44:02 | Re: Transactions and functions |
From | Date | Subject | |
---|---|---|---|
Next Message | Joe Conway | 2016-09-23 20:34:56 | Re: PG 9.6.0 release schedule |
Previous Message | Tom Lane | 2016-09-23 20:31:55 | PG 9.6.0 release schedule |