From 0525e6f284779da7f68f35bc415f3da0848ec0df Mon Sep 17 00:00:00 2001 From: John Naylor Date: Wed, 12 Nov 2025 18:56:29 +0700 Subject: [PATCH v4 3/4] WIP make some regression tests' sort order more deterministic The previous commit still results in failures in the TAP test 002_pg_upgrade.pl, namely that the regression tests fail on the old cluster. XXX it's not clear why only some tests fail this way --- src/test/regress/expected/tsrf.out | 16 +++---- src/test/regress/expected/window.out | 72 ++++++++++++++-------------- src/test/regress/sql/tsrf.sql | 2 +- src/test/regress/sql/window.sql | 20 ++++---- 4 files changed, 55 insertions(+), 55 deletions(-) diff --git a/src/test/regress/expected/tsrf.out b/src/test/regress/expected/tsrf.out index fd3914b0fad..f5647ee561c 100644 --- a/src/test/regress/expected/tsrf.out +++ b/src/test/regress/expected/tsrf.out @@ -397,26 +397,24 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(d | | 2 | 3 (24 rows) -SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY dataa; +SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY dataa, datab, g; dataa | b | g | count -------+-----+---+------- - a | foo | | 2 - a | | | 4 - a | | 2 | 2 a | bar | 1 | 1 a | bar | 2 | 1 a | bar | | 2 a | foo | 1 | 1 a | foo | 2 | 1 + a | foo | | 2 a | | 1 | 2 + a | | 2 | 2 + a | | | 4 b | bar | 1 | 1 - b | | | 2 - b | | 1 | 1 b | bar | 2 | 1 b | bar | | 2 + b | | 1 | 1 b | | 2 | 1 - | | 2 | 3 - | | | 6 + b | | | 2 | bar | 1 | 2 | bar | 2 | 2 | bar | | 4 @@ -424,6 +422,8 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(d | foo | 2 | 1 | foo | | 2 | | 1 | 3 + | | 2 | 3 + | | | 6 (24 rows) SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY g; diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out index b3cdeaea4b3..8a38417e721 100644 --- a/src/test/regress/expected/window.out +++ b/src/test/regress/expected/window.out @@ -18,13 +18,13 @@ INSERT INTO empsalary VALUES ('sales', 3, 4800, '2007-08-01'), ('develop', 8, 6000, '2006-10-01'), ('develop', 11, 5200, '2007-08-15'); -SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary; +SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary, empno; depname | empno | salary | sum -----------+-------+--------+------- develop | 7 | 4200 | 25100 develop | 9 | 4500 | 25100 - develop | 11 | 5200 | 25100 develop | 10 | 5200 | 25100 + develop | 11 | 5200 | 25100 develop | 8 | 6000 | 25100 personnel | 5 | 3500 | 7400 personnel | 2 | 3900 | 7400 @@ -33,18 +33,18 @@ SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM emps sales | 1 | 5000 | 14600 (10 rows) -SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary) FROM empsalary; +SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary, empno) FROM empsalary; depname | empno | salary | rank -----------+-------+--------+------ develop | 7 | 4200 | 1 develop | 9 | 4500 | 2 - develop | 11 | 5200 | 3 develop | 10 | 5200 | 3 + develop | 11 | 5200 | 4 develop | 8 | 6000 | 5 personnel | 5 | 3500 | 1 personnel | 2 | 3900 | 2 sales | 3 | 4800 | 1 - sales | 4 | 4800 | 1 + sales | 4 | 4800 | 2 sales | 1 | 5000 | 3 (10 rows) @@ -75,33 +75,33 @@ GROUP BY four, ten ORDER BY four, ten; 3 | 9 | 7500 | 9.0000000000000000 (20 rows) -SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname); +SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname) ORDER BY depname, empno; depname | empno | salary | sum -----------+-------+--------+------- - develop | 11 | 5200 | 25100 develop | 7 | 4200 | 25100 - develop | 9 | 4500 | 25100 develop | 8 | 6000 | 25100 + develop | 9 | 4500 | 25100 develop | 10 | 5200 | 25100 - personnel | 5 | 3500 | 7400 + develop | 11 | 5200 | 25100 personnel | 2 | 3900 | 7400 - sales | 3 | 4800 | 14600 + personnel | 5 | 3500 | 7400 sales | 1 | 5000 | 14600 + sales | 3 | 4800 | 14600 sales | 4 | 4800 | 14600 (10 rows) -SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary) ORDER BY rank() OVER w; +SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary) ORDER BY rank() OVER w, empno; depname | empno | salary | rank -----------+-------+--------+------ - develop | 7 | 4200 | 1 - personnel | 5 | 3500 | 1 - sales | 4 | 4800 | 1 sales | 3 | 4800 | 1 - develop | 9 | 4500 | 2 + sales | 4 | 4800 | 1 + personnel | 5 | 3500 | 1 + develop | 7 | 4200 | 1 personnel | 2 | 3900 | 2 - develop | 11 | 5200 | 3 - develop | 10 | 5200 | 3 + develop | 9 | 4500 | 2 sales | 1 | 5000 | 3 + develop | 10 | 5200 | 3 + develop | 11 | 5200 | 3 develop | 8 | 6000 | 5 (10 rows) @@ -3754,7 +3754,7 @@ FROM empsalary; SELECT empno, depname, - row_number() OVER (PARTITION BY depname ORDER BY enroll_date) rn, + row_number() OVER (PARTITION BY depname ORDER BY enroll_date, empno) rn, rank() OVER (PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) rnk, count(*) OVER (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN @@ -3765,8 +3765,8 @@ FROM empsalary; 8 | develop | 1 | 1 | 1 10 | develop | 2 | 2 | 1 11 | develop | 3 | 3 | 1 - 9 | develop | 4 | 4 | 2 - 7 | develop | 5 | 4 | 2 + 7 | develop | 4 | 4 | 2 + 9 | develop | 5 | 4 | 2 2 | personnel | 1 | 1 | 1 5 | personnel | 2 | 2 | 1 1 | sales | 1 | 1 | 1 @@ -4202,7 +4202,7 @@ SELECT * FROM -- Ensure we correctly filter out all of the run conditions from each window SELECT * FROM - (SELECT *, + (SELECT depname, count(salary) OVER (PARTITION BY depname || '') c1, -- w1 row_number() OVER (PARTITION BY depname) rn, -- w2 count(*) OVER (PARTITION BY depname) c2, -- w2 @@ -4210,10 +4210,10 @@ SELECT * FROM ntile(2) OVER (PARTITION BY depname) nt -- w2 FROM empsalary ) e WHERE rn <= 1 AND c1 <= 3 AND nt < 2; - depname | empno | salary | enroll_date | c1 | rn | c2 | c3 | nt ------------+-------+--------+-------------+----+----+----+----+---- - personnel | 5 | 3500 | 12-10-2007 | 2 | 1 | 2 | 2 | 1 - sales | 3 | 4800 | 08-01-2007 | 3 | 1 | 3 | 3 | 1 + depname | c1 | rn | c2 | c3 | nt +-----------+----+----+----+----+---- + personnel | 2 | 1 | 2 | 2 | 1 + sales | 3 | 1 | 3 | 3 | 1 (2 rows) -- Ensure we remove references to reduced outer joins as nulling rels in run @@ -4498,23 +4498,23 @@ SELECT * FROM empno, salary, enroll_date, - row_number() OVER (PARTITION BY depname ORDER BY enroll_date) AS first_emp, - row_number() OVER (PARTITION BY depname ORDER BY enroll_date DESC) AS last_emp + row_number() OVER (PARTITION BY depname ORDER BY enroll_date, empno) AS first_emp, + row_number() OVER (PARTITION BY depname ORDER BY enroll_date DESC, empno) AS last_emp FROM empsalary) emp WHERE first_emp = 1 OR last_emp = 1; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------- Subquery Scan on emp Filter: ((emp.first_emp = 1) OR (emp.last_emp = 1)) -> WindowAgg - Window: w2 AS (PARTITION BY empsalary.depname ORDER BY empsalary.enroll_date ROWS UNBOUNDED PRECEDING) + Window: w2 AS (PARTITION BY empsalary.depname ORDER BY empsalary.enroll_date, empsalary.empno ROWS UNBOUNDED PRECEDING) -> Incremental Sort - Sort Key: empsalary.depname, empsalary.enroll_date + Sort Key: empsalary.depname, empsalary.enroll_date, empsalary.empno Presorted Key: empsalary.depname -> WindowAgg - Window: w1 AS (PARTITION BY empsalary.depname ORDER BY empsalary.enroll_date ROWS UNBOUNDED PRECEDING) + Window: w1 AS (PARTITION BY empsalary.depname ORDER BY empsalary.enroll_date, empsalary.empno ROWS UNBOUNDED PRECEDING) -> Sort - Sort Key: empsalary.depname, empsalary.enroll_date DESC + Sort Key: empsalary.depname, empsalary.enroll_date DESC, empsalary.empno -> Seq Scan on empsalary (12 rows) @@ -4523,14 +4523,14 @@ SELECT * FROM empno, salary, enroll_date, - row_number() OVER (PARTITION BY depname ORDER BY enroll_date) AS first_emp, - row_number() OVER (PARTITION BY depname ORDER BY enroll_date DESC) AS last_emp + row_number() OVER (PARTITION BY depname ORDER BY enroll_date, empno) AS first_emp, + row_number() OVER (PARTITION BY depname ORDER BY enroll_date DESC, empno) AS last_emp FROM empsalary) emp WHERE first_emp = 1 OR last_emp = 1; depname | empno | salary | enroll_date | first_emp | last_emp -----------+-------+--------+-------------+-----------+---------- develop | 8 | 6000 | 10-01-2006 | 1 | 5 - develop | 7 | 4200 | 01-01-2008 | 5 | 1 + develop | 7 | 4200 | 01-01-2008 | 4 | 1 personnel | 2 | 3900 | 12-23-2006 | 1 | 2 personnel | 5 | 3500 | 12-10-2007 | 2 | 1 sales | 1 | 5000 | 10-01-2006 | 1 | 3 diff --git a/src/test/regress/sql/tsrf.sql b/src/test/regress/sql/tsrf.sql index 7c22529a0db..af7bd4bdd95 100644 --- a/src/test/regress/sql/tsrf.sql +++ b/src/test/regress/sql/tsrf.sql @@ -96,7 +96,7 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(d SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab) ORDER BY dataa; SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab) ORDER BY g; SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g); -SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY dataa; +SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY dataa, datab, g; SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY g; reset enable_hashagg; diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql index 37d837a2f66..cb28d552fe8 100644 --- a/src/test/regress/sql/window.sql +++ b/src/test/regress/sql/window.sql @@ -21,17 +21,17 @@ INSERT INTO empsalary VALUES ('develop', 8, 6000, '2006-10-01'), ('develop', 11, 5200, '2007-08-15'); -SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary; +SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary, empno; -SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary) FROM empsalary; +SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary, empno) FROM empsalary; -- with GROUP BY SELECT four, ten, SUM(SUM(four)) OVER (PARTITION BY four), AVG(ten) FROM tenk1 GROUP BY four, ten ORDER BY four, ten; -SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname); +SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname) ORDER BY depname, empno; -SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary) ORDER BY rank() OVER w; +SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary) ORDER BY rank() OVER w, empno; -- empty window specification SELECT COUNT(*) OVER () FROM tenk1 WHERE unique2 < 10; @@ -1145,7 +1145,7 @@ FROM empsalary; SELECT empno, depname, - row_number() OVER (PARTITION BY depname ORDER BY enroll_date) rn, + row_number() OVER (PARTITION BY depname ORDER BY enroll_date, empno) rn, rank() OVER (PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) rnk, count(*) OVER (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN @@ -1366,7 +1366,7 @@ SELECT * FROM -- Ensure we correctly filter out all of the run conditions from each window SELECT * FROM - (SELECT *, + (SELECT depname, count(salary) OVER (PARTITION BY depname || '') c1, -- w1 row_number() OVER (PARTITION BY depname) rn, -- w2 count(*) OVER (PARTITION BY depname) c2, -- w2 @@ -1507,8 +1507,8 @@ SELECT * FROM empno, salary, enroll_date, - row_number() OVER (PARTITION BY depname ORDER BY enroll_date) AS first_emp, - row_number() OVER (PARTITION BY depname ORDER BY enroll_date DESC) AS last_emp + row_number() OVER (PARTITION BY depname ORDER BY enroll_date, empno) AS first_emp, + row_number() OVER (PARTITION BY depname ORDER BY enroll_date DESC, empno) AS last_emp FROM empsalary) emp WHERE first_emp = 1 OR last_emp = 1; @@ -1517,8 +1517,8 @@ SELECT * FROM empno, salary, enroll_date, - row_number() OVER (PARTITION BY depname ORDER BY enroll_date) AS first_emp, - row_number() OVER (PARTITION BY depname ORDER BY enroll_date DESC) AS last_emp + row_number() OVER (PARTITION BY depname ORDER BY enroll_date, empno) AS first_emp, + row_number() OVER (PARTITION BY depname ORDER BY enroll_date DESC, empno) AS last_emp FROM empsalary) emp WHERE first_emp = 1 OR last_emp = 1; -- 2.51.1