diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/subselect.out | 47 | ||||
| -rw-r--r-- | src/test/regress/sql/subselect.sql | 16 |
2 files changed, 63 insertions, 0 deletions
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index 2d35de3fad6..da219aba4c4 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -1750,6 +1750,53 @@ where tname = 'tenk1' and attnum = 1; tenk1 | unique1 (1 row) +-- Check behavior when there's a lateral reference in the output expression +explain (verbose, costs off) +select t1.ten, sum(x) from + tenk1 t1 left join lateral ( + select t1.ten + t2.ten as x, t2.fivethous from tenk1 t2 + ) ss on t1.unique1 = ss.fivethous +group by t1.ten +order by t1.ten; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Sort + Output: t1.ten, (sum((t1.ten + t2.ten))) + Sort Key: t1.ten + -> HashAggregate + Output: t1.ten, sum((t1.ten + t2.ten)) + Group Key: t1.ten + -> Hash Right Join + Output: t1.ten, t2.ten + Hash Cond: (t2.fivethous = t1.unique1) + -> Seq Scan on public.tenk1 t2 + Output: t2.unique1, t2.unique2, t2.two, t2.four, t2.ten, t2.twenty, t2.hundred, t2.thousand, t2.twothousand, t2.fivethous, t2.tenthous, t2.odd, t2.even, t2.stringu1, t2.stringu2, t2.string4 + -> Hash + Output: t1.ten, t1.unique1 + -> Seq Scan on public.tenk1 t1 + Output: t1.ten, t1.unique1 +(15 rows) + +select t1.ten, sum(x) from + tenk1 t1 left join lateral ( + select t1.ten + t2.ten as x, t2.fivethous from tenk1 t2 + ) ss on t1.unique1 = ss.fivethous +group by t1.ten +order by t1.ten; + ten | sum +-----+------- + 0 | 0 + 1 | 2000 + 2 | 4000 + 3 | 6000 + 4 | 8000 + 5 | 10000 + 6 | 12000 + 7 | 14000 + 8 | 16000 + 9 | 18000 +(10 rows) + -- -- Tests for CTE inlining behavior -- diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql index af6e157aca0..1815a5e6642 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -908,6 +908,22 @@ select relname::information_schema.sql_identifier as tname, * from right join pg_attribute a on a.attrelid = ss2.oid where tname = 'tenk1' and attnum = 1; +-- Check behavior when there's a lateral reference in the output expression +explain (verbose, costs off) +select t1.ten, sum(x) from + tenk1 t1 left join lateral ( + select t1.ten + t2.ten as x, t2.fivethous from tenk1 t2 + ) ss on t1.unique1 = ss.fivethous +group by t1.ten +order by t1.ten; + +select t1.ten, sum(x) from + tenk1 t1 left join lateral ( + select t1.ten + t2.ten as x, t2.fivethous from tenk1 t2 + ) ss on t1.unique1 = ss.fivethous +group by t1.ten +order by t1.ten; + -- -- Tests for CTE inlining behavior -- |