[Bug] explicit sorting is needed for ICW testcase gpdtm_plpgsql.sql

[congxuebin]

Cloudberry Database version

PostgreSQL 14.4 (Cloudberry Database 1.5.5 build 77126 commit:64f8d776) (HashData Lightning 1.5.5 build 77126 commit:64f8d776) on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 10.3.0, 64-bit compiled on Jun 17 2024 04:10:34
(1 row)

What happened

Failure in https://code.hashdata.xyz/cloudberry/cbdb-release/-/jobs/883544

--- /code/cbdb_src/src/test/regress/expected/gpdtm_plpgsql.out	2024-06-17 09:52:03.932424857 +0800
+++ /code/cbdb_src/src/test/regress/results/gpdtm_plpgsql.out	2024-06-17 09:52:03.952425151 +0800
@@ -530,8 +530,8 @@
 SELECT * FROM test_parse_arr ORDER BY a;
  a |     b     
 ---+-----------
- 1 | {{1,2,3}}
- 2 | 
+ 1 | 
+ 2 | {{1,2,3}}
 (2 rows)

What you think should happen instead

a | b
---+-----------
1 | {{1,2,3}}
2 |

How to reproduce

Testcase source:
https://code.hashdata.xyz/cloudberry/database/hashdata-lightning/-/blob/main/contrib/pax_storage/src/test/regress/sql/gpdtm_plpgsql.sql
https://code.hashdata.xyz/cloudberry/database/hashdata-lightning/-/blob/main/src/test/regress/expected/gpdtm_plpgsql.out

CREATE OR REPLACE function parse_arr (p_array text)
	returns int[] IMMUTABLE
	AS $dbvis$
	declare
		v_return int[][];
		v_text text;
		v_first int; 
	BEGIN
		if p_array is null 
		then 
			return null;
		end if;  

		v_first := 1;
		for v_text in
			select unnest(string_to_array(p_array, ';'))
		loop 
			v_return := v_return || ARRAY[(string_to_array(regexp_replace(v_text, '[)( ]', '', 'g'), ',')::int[])];
		end loop;
   
		RETURN v_return;
	END; 
$dbvis$ LANGUAGE plpgsql;

CREATE TABLE test_parse_arr (a bigserial, b int[]);

INSERT INTO test_parse_arr (b)
	SELECT parse_arr(x) FROM 
				(    
				  SELECT '(1, 2, 3)' AS x 
				  UNION ALL
				  SELECT NULL 
				) AS q;

SELECT * FROM test_parse_arr ORDER BY a;

Operating System

centos7

Anything else

No response

Are you willing to submit PR?

• Yes, I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct.

[congxuebin]

SELECT '(1, 2, 3)' AS x 
UNION ALL
SELECT NULL 

The union all does not make sure the order, thus explicit sorting is needed to avoid unstable results, like this

SELECT '(1, 2, 3)' AS x 
UNION ALL
SELECT NULL AS x
order by x

[congxuebin]

after adding sorting, the result is still not stable. It could be bug. I would reopen another issue for tracking.