Flaky tests to fix

I am documenting here some flaky tests I have ran into the past weeks, but none of them have extra investigation.

- [ ] `subquery_in_where` needs order by https://github.com/citusdata/citus/actions/runs/19567084981/attempts/1#summary-56032048177
```diff
diff -dU10 -w /__w/citus/citus/src/test/regress/expected/subquery_in_where.out /__w/citus/citus/src/test/regress/results/subquery_in_where.out
--- /__w/citus/citus/src/test/regress/expected/subquery_in_where.out.modified 2025-11-21 10:16:20.148456917 +0000
+++ /__w/citus/citus/src/test/regress/results/subquery_in_where.out.modified 2025-11-21 10:16:20.175457049 +0000
@@ -747,22 +747,22 @@
IN
(SELECT
user_id
FROM
users_table);
DEBUG: generating subplan 80_1 for subquery SELECT id, value_1 FROM subquery_in_where.local_table
DEBUG: generating subplan 80_2 for subquery SELECT user_id FROM public.users_table
DEBUG: Plan 80 query after replacing subqueries and CTEs: SELECT id, value_1 FROM (SELECT intermediate_result.id, intermediate_result.value_1 FROM read_intermediate_result('80_1'::text, 'binary'::citus_copy_format) intermediate_result(id integer, value_1 integer)) sub_table WHERE (id OPERATOR(pg_catalog.=) ANY (SELECT intermediate_result.user_id FROM read_intermediate_result('80_2'::text, 'binary'::citus_copy_format) intermediate_result(user_id integer)))
id | value_1
----+---------
- 1 | 1
2 | 2
+ 1 | 1
(2 rows)

-- Use local table in WHERE clause
SELECT
COUNT(*)
FROM
(SELECT
*
FROM
users_table
```
- [ ] columnar_alter https://github.com/citusdata/citus/actions/runs/6821867022/attempts/1#summary-18553017466
```diff
CREATE TABLE has_volatile AS
SELECT * FROM generate_series(1,10) id;
ALTER TABLE has_volatile ADD col4 int DEFAULT (random() * 10000)::int;
SELECT id, col4 < 10000 FROM has_volatile ORDER BY id;
id | ?column?
----+----------
1 | t
- 2 | t
+ 2 | f
3 | t
4 | t
5 | t
6 | t
7 | t
8 | t
9 | t
10 | t
(10 rows)
```

- [ ] citus_local_tables https://app.circleci.com/pipelines/github/citusdata/citus/34199/workflows/492aea01-e562-4114-8526-75c6e3aff95a/jobs/1208653
```diff
-- verify we still preserve the child-parent hierarchy after all conversions
-- check the shard partition
select inhrelid::regclass from pg_inherits where (select inhparent::regclass::text) ~ '^parent_1_\d{7}$' order by 1;
inhrelid
--------------------------
- parent_1_child_1_1904006
+ parent_1_child_1_1904004
(1 row)
```

- [ ] multi_insert_select https://app.circleci.com/pipelines/github/citusdata/citus/34280/workflows/269dfa5e-df5b-4ed2-a5e4-d47b2da75024/jobs/1212730
```diff
raw_events_second.value_1
FROM raw_events_first,
raw_events_second
WHERE raw_events_first.user_id = raw_events_second.user_id) AS foo
GROUP BY id
ORDER BY id;
DEBUG: distributed statement: INSERT INTO multi_insert_select.agg_events_13300008 AS citus_table_alias (user_id, value_1_agg) SELECT foo.id, sum(foo.value_1) AS sum FROM (SELECT raw_events_second.user_id AS id, raw_events_second.value_1 FROM multi_insert_select.raw_events_first_13300000 raw_events_first, multi_insert_select.raw_events_second_13300004 raw_events_second WHERE (raw_events_first.user_id OPERATOR(pg_catalog.=) raw_events_second.user_id)) foo WHERE (foo.id IS NOT NULL) GROUP BY foo.id ORDER BY foo.id
DEBUG: distributed statement: INSERT INTO multi_insert_select.agg_events_13300009 AS citus_table_alias (user_id, value_1_agg) SELECT foo.id, sum(foo.value_1) AS sum FROM (SELECT raw_events_second.user_id AS id, raw_events_second.value_1 FROM multi_insert_select.raw_events_first_13300001 raw_events_first, multi_insert_select.raw_events_second_13300005 raw_events_second WHERE (raw_events_first.user_id OPERATOR(pg_catalog.=) raw_events_second.user_id)) foo WHERE (foo.id IS NOT NULL) GROUP BY foo.id ORDER BY foo.id
DEBUG: distributed statement: INSERT INTO multi_insert_select.agg_events_13300010 AS citus_table_alias (user_id, value_1_agg) SELECT foo.id, sum(foo.value_1) AS sum FROM (SELECT raw_events_second.user_id AS id, raw_events_second.value_1 FROM multi_insert_select.raw_events_first_13300002 raw_events_first, multi_insert_select.raw_events_second_13300006 raw_events_second WHERE (raw_events_first.user_id OPERATOR(pg_catalog.=) raw_events_second.user_id)) foo WHERE (foo.id IS NOT NULL) GROUP BY foo.id ORDER BY foo.id
DEBUG: distributed statement: INSERT INTO multi_insert_select.agg_events_13300011 AS citus_table_alias (user_id, value_1_agg) SELECT foo.id, sum(foo.value_1) AS sum FROM (SELECT raw_events_second.user_id AS id, raw_events_second.value_1 FROM multi_insert_select.raw_events_first_13300003 raw_events_first, multi_insert_select.raw_events_second_13300007 raw_events_second WHERE (raw_events_first.user_id OPERATOR(pg_catalog.=) raw_events_second.user_id)) foo WHERE (foo.id IS NOT NULL) GROUP BY foo.id ORDER BY foo.id
-ERROR: duplicate key value violates unique constraint "agg_events_user_id_value_1_agg_key_13300008"
+ERROR: duplicate key value violates unique constraint "agg_events_user_id_value_1_agg_key_13300009"
-- subquery one more level depth
INSERT INTO agg_events
(value_4_agg,
```

- [ ] isolation_ref2ref_foreign_keys https://app.circleci.com/pipelines/github/citusdata/citus/34552/workflows/67998346-ae15-48cc-b426-24c52d948c5b/jobs/1229317
```diff
regexp_replace(query, E'[\\n\\r\\u2028]+', ' ', 'g' ) query
FROM pg_locks l
JOIN pg_stat_activity a
ON l.pid = a.pid
WHERE locktype='advisory'
AND application_name <> 'Citus Maintenance Daemon'
ORDER BY 1, 2, 3, 4;

classid| objid|objsubid|mode |application_name |backend_type |query
-------+-------+--------+-------------+-------------------------------------------+--------------+-------------------------------------------------
- 0|8429800| 5|ExclusiveLock|isolation/isolation_ref2ref_foreign_keys/s2|client backend| UPDATE ref_table_3 SET id = 2 WHERE id = 1;
- 0|8429801| 5|ExclusiveLock|isolation/isolation_ref2ref_foreign_keys/s2|client backend| UPDATE ref_table_3 SET id = 2 WHERE id = 1;
0|8429802| 4|ShareLock |isolation/isolation_ref2ref_foreign_keys/s2|client backend| UPDATE ref_table_3 SET id = 2 WHERE id = 1;
0|8429802| 5|ExclusiveLock|isolation/isolation_ref2ref_foreign_keys/s2|client backend| UPDATE ref_table_3 SET id = 2 WHERE id = 1;
-(4 rows)
+(2 rows)

step s1-rollback:
ROLLBACK;

step s2-rollback:
ROLLBACK;

step s1-view-locks:
-- The following output changed in PG versions 13.6 and 14.2. The output is expected
-- to change in earlier versions of PG as the and application_name format did not use
```

Start with the listed regression tests: subquery_in_where, columnar_alter, citus_local_tables, multi_insert_select, and isolation_ref2ref_foreign_keys, then review their linked CI runs and expected output files under src/test/regress. Investigate why each output differs between runs and stabilize the tests without masking real failures. Done means the affected tests pass repeatedly with consistent expected results.

Written by the indexing model from the issue text.