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

