Обсуждение: ARRAY[1] || NULL <> array_append(ARRAY[1], NULL)

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>

Michael Herold <quabla@hemio.de> writes:
> 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.

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

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

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

Hi,

On 07/09/2015 04:00 PM, Tom Lane wrote:
> 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?

Sounds perfect.

Best,
Michael

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/40854D0B.6000005@cromwell.co.uk>

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

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.

> 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}

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  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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

On Tue, Dec  1, 2015 at 01:22:25PM +0100, Michael Herold wrote:
> On 01/12/15 00:59, Bruce Momjian wrote:
> >I would argue that '{}'::int[] is zero dimmensions, so there is no
> >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.

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.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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

On 01/12/15 00:59, Bruce Momjian wrote:
> I would argue that '{}'::int[] is zero dimmensions, so there is no
> 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.