diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/window.out | 98 | ||||
| -rw-r--r-- | src/test/regress/sql/window.sql | 50 |
2 files changed, 148 insertions, 0 deletions
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out index 5505e9a2dac..90e89fb5b68 100644 --- a/src/test/regress/expected/window.out +++ b/src/test/regress/expected/window.out @@ -3885,6 +3885,104 @@ WHERE depname = 'sales'; Filter: ((depname)::text = 'sales'::text) (7 rows) +-- Ensure that the evaluation order of the WindowAggs results in the WindowAgg +-- with the same sort order that's required by the ORDER BY is evaluated last. +EXPLAIN (COSTS OFF) +SELECT empno, + enroll_date, + depname, + sum(salary) OVER (PARTITION BY depname order by empno) depsalary, + min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary +FROM empsalary +ORDER BY depname, empno; + QUERY PLAN +---------------------------------------------------- + WindowAgg + -> Incremental Sort + Sort Key: depname, empno + Presorted Key: depname + -> WindowAgg + -> Sort + Sort Key: depname, enroll_date + -> Seq Scan on empsalary +(8 rows) + +-- As above, but with an adjusted ORDER BY to ensure the above plan didn't +-- perform only 2 sorts by accident. +EXPLAIN (COSTS OFF) +SELECT empno, + enroll_date, + depname, + sum(salary) OVER (PARTITION BY depname order by empno) depsalary, + min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary +FROM empsalary +ORDER BY depname, enroll_date; + QUERY PLAN +----------------------------------------------- + WindowAgg + -> Incremental Sort + Sort Key: depname, enroll_date + Presorted Key: depname + -> WindowAgg + -> Sort + Sort Key: depname, empno + -> Seq Scan on empsalary +(8 rows) + +SET enable_hashagg TO off; +-- Ensure we don't get a sort for both DISTINCT and ORDER BY. We expect the +-- sort for the DISTINCT to provide presorted input for the ORDER BY. +EXPLAIN (COSTS OFF) +SELECT DISTINCT + empno, + enroll_date, + depname, + sum(salary) OVER (PARTITION BY depname order by empno) depsalary, + min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary +FROM empsalary +ORDER BY depname, enroll_date; + QUERY PLAN +----------------------------------------------------------------------------------------------- + Unique + -> Sort + Sort Key: depname, enroll_date, empno, (sum(salary) OVER (?)), (min(salary) OVER (?)) + -> WindowAgg + -> Incremental Sort + Sort Key: depname, enroll_date + Presorted Key: depname + -> WindowAgg + -> Sort + Sort Key: depname, empno + -> Seq Scan on empsalary +(11 rows) + +-- As above but adjust the ORDER BY clause to help ensure the plan with the +-- minimum amount of sorting wasn't a fluke. +EXPLAIN (COSTS OFF) +SELECT DISTINCT + empno, + enroll_date, + depname, + sum(salary) OVER (PARTITION BY depname order by empno) depsalary, + min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary +FROM empsalary +ORDER BY depname, empno; + QUERY PLAN +----------------------------------------------------------------------------------------------- + Unique + -> Sort + Sort Key: depname, empno, enroll_date, (sum(salary) OVER (?)), (min(salary) OVER (?)) + -> WindowAgg + -> Incremental Sort + Sort Key: depname, empno + Presorted Key: depname + -> WindowAgg + -> Sort + Sort Key: depname, enroll_date + -> Seq Scan on empsalary +(11 rows) + +RESET enable_hashagg; -- Test Sort node reordering EXPLAIN (COSTS OFF) SELECT diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql index 10f450fee47..b7bd0a83da4 100644 --- a/src/test/regress/sql/window.sql +++ b/src/test/regress/sql/window.sql @@ -1276,6 +1276,56 @@ SELECT * FROM FROM empsalary) emp WHERE depname = 'sales'; +-- Ensure that the evaluation order of the WindowAggs results in the WindowAgg +-- with the same sort order that's required by the ORDER BY is evaluated last. +EXPLAIN (COSTS OFF) +SELECT empno, + enroll_date, + depname, + sum(salary) OVER (PARTITION BY depname order by empno) depsalary, + min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary +FROM empsalary +ORDER BY depname, empno; + +-- As above, but with an adjusted ORDER BY to ensure the above plan didn't +-- perform only 2 sorts by accident. +EXPLAIN (COSTS OFF) +SELECT empno, + enroll_date, + depname, + sum(salary) OVER (PARTITION BY depname order by empno) depsalary, + min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary +FROM empsalary +ORDER BY depname, enroll_date; + +SET enable_hashagg TO off; + +-- Ensure we don't get a sort for both DISTINCT and ORDER BY. We expect the +-- sort for the DISTINCT to provide presorted input for the ORDER BY. +EXPLAIN (COSTS OFF) +SELECT DISTINCT + empno, + enroll_date, + depname, + sum(salary) OVER (PARTITION BY depname order by empno) depsalary, + min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary +FROM empsalary +ORDER BY depname, enroll_date; + +-- As above but adjust the ORDER BY clause to help ensure the plan with the +-- minimum amount of sorting wasn't a fluke. +EXPLAIN (COSTS OFF) +SELECT DISTINCT + empno, + enroll_date, + depname, + sum(salary) OVER (PARTITION BY depname order by empno) depsalary, + min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary +FROM empsalary +ORDER BY depname, empno; + +RESET enable_hashagg; + -- Test Sort node reordering EXPLAIN (COSTS OFF) SELECT |