[Bug] order unexpected, when inserting array data into a table with an auto-increment column

[congxuebin]

Cloudberry Database version

PostgreSQL 14.4 (Cloudberry Database 1.5.5+dev.2.g36d8c281 build 77586 commit:36d8c281) (HashData Lightning 1.5.5+dev.2.g36d
8c281 build 77586 commit:36d8c281) on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 10.2.1 20210130 (Red Hat 10.2.1-11), 6
4-bit compiled on Jun 20 2024 04:10:44

What happened

ICW testcase gpdtm_plpgsql failed intermittently.
I identified the result of the following is not consistent.

DROP function parse_arr;
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;

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

INSERT INTO test_parse_arr (b)
        SELECT parse_arr(x) as pr FROM
                                (
                                  SELECT '(1, 2, 3)' AS x
                                  UNION ALL
                                  SELECT NULL
                                  UNION ALL
                                  SELECT '(4, 5, 6)' AS x
                                ) AS q order by pr ;

SELECT * FROM test_parse_arr ORDER BY a;

Here are results from two separate executions.

 a |     b     
---+-----------
 1 | {{4,5,6}}
 2 | {{1,2,3}}
 3 | 
(3 rows)

 a |     b     
---+-----------
 1 | 
 2 | {{1,2,3}}
 3 | {{4,5,6}}
(3 rows)

What you think should happen instead

I would expect the result is stable. I tried the same statements running against postgreSQL, the result is stable.

How to reproduce

Simply rerun the statements above

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]

It is not a cbdb bug. Even though the ORDER BY clause in the INSERT statement ensures the elements in array b are ordered, due to data distribution mechanics—where data is dispatched to different nodes for processing — the sequential generation of auto-increment values(for column a) across these nodes cannot be consistently guaranteed.

Consequently, when querying "SELECT * FROM test_parse_arr ORDER BY a" the resulting sequence may not reflect the initial order of the array elements as they were inserted.

The test case need to be modified to make sure test result consistent.