Subject: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL)

From: Michael Herold <[email-masked]>
Date: Thu, 09 Jul 2015 02:31:10 +0200

A few minutes ago I got bitten by the following PostgreSQL (9.4) behavior

SELECT ARRAY[1] || NULL, array_append(ARRAY[1], NULL);
  ?column? | array_append
----------+--------------
  {1}      | {1,NULL}

I expected that array_append and || are equivalent in this case but 
obviously they are not. Sure, this is not too surprising since "||" has 
to guess which operation is appropriate. However, I would have highly 
appreciated ARRAY[1,2] || NULL as an example in [Table 9-45]. Combined 
with the example of NULL || ARRAY[1,2] the underlying principle becomes 
clear to me.

Strings behave different, but maybe this is also a potential pitfall:

SELECT 'abc' || NULL, concat('abc', NULL);
  ?column? | concat
----------+--------
  (NULL)   | abc

Best,
Michael

[Table 9-45]: 
<http://www.postgresql.org/docs/9.4/static/functions-array.html#ARRAY-OPERATORS-TABLE>

-- 
Sent via pgsql-docs mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

---

From: Tom Lane <[email-masked]>
Date: Thu, 09 Jul 2015 10:00:57 -0400

> obviously they are not.

Yeah, the first case turns out to be array_cat not array_append.

> Sure, this is not too surprising since "||" has 
> appreciated ARRAY[1,2] || NULL as an example in [Table 9-45].

Hm.  I don't think there is really room for such an example in that table,
and certainly not room for a discursive discussion.  What would make more
sense to me is to add a paragraph and example concerning this issue at the
bottom of section 8.15.4, right after array_append and array_cat are
introduced.  Since there's already a pointer to 8.15 just below that
table, people would hopefully find the example from that vicinity as well.

Seem reasonable?

			regards, tom lane

---

From: Michael Herold <[email-masked]>
Date: Sat, 11 Jul 2015 17:39:19 +0200

Hi,

> table, people would hopefully find the example from that vicinity as well.

Sounds perfect.

Best,
Michael

---

From: Michael Herold <[email-masked]>
Date: Mon, 26 Oct 2015 11:04:40 +0100

I just got confused by another behavior of the || operator. I thought it 
might be appropriate to report it here.

The docs say "the result retains the lower bound subscript of the 
left-hand operand’s outer dimension" [1]. That's again not true for 
corner cases.

Expected:
# SELECT '{0}'::int[] || '[15:16]={1,2}';
----------
  {0,1,2}

Unexpected (lower bound is untouched):
# SELECT '{}'::int[] || '[15:16]={1,2}';
---------------
  [15:16]={1,2}

Actually, I was looking for a way to reset the index lower bound of an 
array to the default. I didn't found a solution documented anywhere, [2] 
only works for known array sizes. So, it might also be worth stating 
ARRAY(SELECT UNNEST(...)) as a solution?

[1]: <http://www.postgresql.org/docs/9.5/static/arrays.html>
[2]: <http://www.postgresql.org/message-id/[email-masked]>

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 30 Nov 2015 18:59:19 -0500

On Mon, Oct 26, 2015 at 11:04:40AM +0100, Michael Herold wrote:
> I just got confused by another behavior of the || operator. I
> thought it might be appropriate to report it here.
> 

You didn't quote the entire sentence:

	When two arrays with an equal number of dimensions are concatenated, the
	-------------------------------------------------------------------
	result retains the lower bound subscript of the left-hand operand's
	outer dimension.

> # SELECT '{0}'::int[] || '[15:16]={1,2}';

I would argue that '{}'::int[] is zero dimmensions, so there is no
documented behavior for this.

The C code is:

    /*
     * short circuit - if one input array is empty, and the other is not, we
     * return the non-empty one as the result
     *
     * if both are empty, return the first one
     */
    if (ndims1 == 0 && ndims2 > 0)
        PG_RETURN_ARRAYTYPE_P(v2);

I doubt we want to change this.

-- 
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription                             +

---

From: Michael Herold <[email-masked]>
Date: Tue, 01 Dec 2015 13:22:25 +0100

> documented behavior for this.

Thank you for your reply. Agree, I am writing to pgsql-docs because I 
think this should be fixed with proper documentation.

My actual concern is that it is totally unclear how to (re)set the lower 
bound of an array. At least I waisted an our to figure that out.

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 1 Dec 2015 08:23:47 -0500

On Tue, Dec  1, 2015 at 01:22:25PM +0100, Michael Herold wrote:
> Thank you for your reply. Agree, I am writing to pgsql-docs because
> I think this should be fixed with proper documentation.

Right.  I think the docs just don't explain what happens in the case you
showed.

> My actual concern is that it is totally unclear how to (re)set the
> lower bound of an array. At least I waisted an hour to figure that
> out.

Yes, I am not sure how to do that either.