diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/subselect.out | 64 | ||||
| -rw-r--r-- | src/test/regress/sql/subselect.sql | 16 |
2 files changed, 80 insertions, 0 deletions
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index 850777acd5c..03b0caa51fd 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -639,3 +639,67 @@ where a.thousand = b.thousand ---------- (0 rows) +-- +-- Check that nested sub-selects are not pulled up if they contain volatiles +-- +explain (verbose, costs off) + select x, x from + (select (select now()) as x from (values(1),(2)) v(y)) ss; + QUERY PLAN +--------------------------- + Values Scan on "*VALUES*" + Output: $0, $1 + InitPlan 1 (returns $0) + -> Result + Output: now() + InitPlan 2 (returns $1) + -> Result + Output: now() +(8 rows) + +explain (verbose, costs off) + select x, x from + (select (select random()) as x from (values(1),(2)) v(y)) ss; + QUERY PLAN +---------------------------------- + Subquery Scan on ss + Output: ss.x, ss.x + -> Values Scan on "*VALUES*" + Output: $0 + InitPlan 1 (returns $0) + -> Result + Output: random() +(7 rows) + +explain (verbose, costs off) + select x, x from + (select (select now() where y=y) as x from (values(1),(2)) v(y)) ss; + QUERY PLAN +-------------------------------------- + Values Scan on "*VALUES*" + Output: (SubPlan 1), (SubPlan 2) + SubPlan 1 + -> Result + Output: now() + One-Time Filter: ($0 = $0) + SubPlan 2 + -> Result + Output: now() + One-Time Filter: ($1 = $1) +(10 rows) + +explain (verbose, costs off) + select x, x from + (select (select random() where y=y) as x from (values(1),(2)) v(y)) ss; + QUERY PLAN +-------------------------------------------- + Subquery Scan on ss + Output: ss.x, ss.x + -> Values Scan on "*VALUES*" + Output: (SubPlan 1) + SubPlan 1 + -> Result + Output: random() + One-Time Filter: ($0 = $0) +(8 rows) + diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql index 8ca7a3bd2fb..5034964bb6f 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -389,3 +389,19 @@ where a.thousand = b.thousand and exists ( select 1 from tenk1 c where b.hundred = c.hundred and not exists ( select 1 from tenk1 d where a.thousand = d.thousand ) ); + +-- +-- Check that nested sub-selects are not pulled up if they contain volatiles +-- +explain (verbose, costs off) + select x, x from + (select (select now()) as x from (values(1),(2)) v(y)) ss; +explain (verbose, costs off) + select x, x from + (select (select random()) as x from (values(1),(2)) v(y)) ss; +explain (verbose, costs off) + select x, x from + (select (select now() where y=y) as x from (values(1),(2)) v(y)) ss; +explain (verbose, costs off) + select x, x from + (select (select random() where y=y) as x from (values(1),(2)) v(y)) ss; |