Re: Duplicates being removed from intarray on subtraction of another intarray

On 2024-03-18 09:21 +0100, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/intarray.html
> Description:
>
> Hi,
> I recently ran into an unusual issue with the intarray extension where if
> you subtract one array from another the result is *also* sorted and
> de-duplicated. The documentation does not seem to imply that this should be
> the case, stating only that the operator "removes elements of the right
> array from the left array" and not that it also de-duplicates and sorts the
> result... It seems to only occur when subtracting an array. Is this the
> intended behavior?
>
> SELECT '{3,1,1,2,2,2}'::int[] - 1; --> {3,2,2,2} as you would expect
> SELECT '{3,1,1,2,2,2}'::int[] - '{1}'::int[]; --> {2,3} instead of
> {3,2,2,2}
>
> I have confirmed that I get the same result when using PostgreSQL 9 through
> 16 on DBFiddle. See here: https://dbfiddle.uk/i-eXKhFR
> I am using the official PostgreSQL 16.2 Docker image for Debian (PostgreSQL
> 16.2 (Debian 16.2-1.pgdg120+2) on x86_64-pc-linux-gnu, compiled by gcc
> (Debian 12.2.0-14) 12.2.0, 64-bit).

I don't know if it's intended behavior but it's implemented that way
since its inception (see intset_subtract in [1]). Also the intersection
and union operators behave similarly (sorted result without duplicates).
The attached patch changes the docs to state that the operator computes
the difference.

Operator integer[] - integer OTOH appears to be more in line with
integer[] + integer[] and integer[] + integer in that it doesn't treat
the arguments as sets.

It's unfortunate that both operations use the same operator and not
something like integer[] / integer[] which would be closer to the usual
notation for set difference.

[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=181ca96e7a730ba35e973d3361422e6d8a460f88

--
Erik