v5-0003-WIP-make-some-regression-tests-sort-order-more-de.patch
application/x-patch
Filename: v5-0003-WIP-make-some-regression-tests-sort-order-more-de.patch
Type: application/x-patch
Part: 2
Message:
Re: tuple radix sort
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v5-0003
Subject: WIP make some regression tests' sort order more deterministic
| File | + | − |
|---|---|---|
| src/test/regress/expected/tsrf.out | 8 | 8 |
| src/test/regress/expected/window.out | 36 | 36 |
| src/test/regress/sql/tsrf.sql | 1 | 1 |
| src/test/regress/sql/window.sql | 10 | 10 |
From 960b81a62a1c11bf47b79dcc232824620c015fbc Mon Sep 17 00:00:00 2001
From: John Naylor <john.naylor@postgresql.org>
Date: Wed, 12 Nov 2025 18:56:29 +0700
Subject: [PATCH v5 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