Re: general purpose array_sort

On Mon, Nov 04, 2024 at 03:16:35PM +0800, jian he wrote:
> drop table if exists t;
> CREATE TABLE t (a int[]);
> insert into t values ('{1,3}'),('{1,2,3}'),('{11}');
> insert into t values ('{{1,12}}'), ('{{4,3}}');
> SELECT array_sort(a) from t;
>
> In the above case,
> tuplesort_begin_datum needs the int type information and int[] type information.
> otherwise the cached TypeCacheEntry is being used to sort mult-dimension array,
> which will make the result false.

All these behaviors need more extensive testing.

This brings me an extra question around the caching. Would the
sorting be able to behave correctly when feeding to a single
array_sort() context array values that have multiple COLLATE clauses?
Or merge_collation_state() would be smart enough to make sure that
collation conflicts never happen to begin with? I am wondering if we
should worry about multiple VALUES, CTEs, or PL functions where
array_sort() could be fed into its cache values that lead to
unpredictible results for some values. This stuff should perhaps have
more testing around such behaviors, stressing what kind of
interactions we have between the sorting of multiple values and the
caching, in the context of a single array_sort() call.
--
Michael