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

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).

Cheers
Tom

On Mon, 2024-03-18 at 08:21 +0000, PG Doc comments form wrote:
> Page: https://www.postgresql.org/docs/16/intarray.html
>
> 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}

There is no harm in documenting that; I propose the attached patch.

Yours,
Laurenz Albe

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