Thread: select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1); returns NULL instead of 0
select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1); returns NULL instead of 0
From
Alexander Farber
Date:
Good evening,
I wonder, why the following returns NULL and not 0 in 9.5.3?
# select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1);
array_length
--------------
(1 row)
# select array_length(array_remove(ARRAY[3,3,3],3), 1);
array_length
--------------
(1 row)
I wonder, why the following returns NULL and not 0 in 9.5.3?
# select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1);
array_length
--------------
(1 row)
# select array_length(array_remove(ARRAY[3,3,3],3), 1);
array_length
--------------
(1 row)
In a code for a word game (could be a card game too)
I remove played letter tiles from player's hand using
array_position and finally "compress" it using array_remove:
FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_tiles)
LOOP
_letter := _tile->>'letter';
_value := (_tile->>'value')::int;
_col := (_tile->>'col')::int + 1;
_row := (_tile->>'row')::int + 1;
IF _value = 0 THEN
_pos = ARRAY_POSITION(_hand, '*');
ELSE
_pos = ARRAY_POSITION(_hand, _letter);
END IF;
IF _pos >= 1 THEN
_hand[_pos] := NULL;
ELSE
RAISE EXCEPTION 'Tile % not found in hand %', _tile, _hand;
END IF;
_letters[_col][_row] := _letter;
_values[_col][_row] := _value;
END LOOP;
-- remove played tiles from player hand
_hand := ARRAY_REMOVE(_hand, NULL);
-- move up to 7 missing tiles from pile to hand
_hand_len := ARRAY_LENGTH(_hand, 1); -- OOPS can be NULL
_pile_len := ARRAY_LENGTH(_pile, 1); -- OOPS can be NULL
_move_len := LEAST(7 - _hand_len, _pile_len);
_hand := _hand || _pile[1:_move_len];
_pile := _pile[(1 + _move_len):_pile_len];
I understand that I have to wrap ARRAY_LENGTH calls
with COALESCE, but I am just curious why isn't 0 returned
in the first place...
Regards
Alex
Re: select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1); returns NULL instead of 0
From
Tom Lane
Date:
Alexander Farber <alexander.farber@gmail.com> writes:
> I wonder, why the following returns NULL and not 0 in 9.5.3?
> # select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1);
Because the result of the array_remove is an empty array, which is
defined to be zero-dimensional in PG.
regards, tom lane
Re: select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1); returns NULL instead of 0
From
Alexander Farber
Date:
Thank you, so should I maybe switch to cardinality then?
Re: select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1); returns NULL instead of 0
From
Tom Lane
Date:
Alexander Farber <alexander.farber@gmail.com> writes:
> Thank you, so should I maybe switch to cardinality then?
Yeah, that should work.
regards, tom lane
Re: select array_remove(ARRAY[NULL,NULL,NULL],NULL); returns {} instead of {NULL,NULL,NULL}
From
Alban Hertroys
Date:
> On 08 Aug 2016, at 20:19, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Alexander Farber <alexander.farber@gmail.com> writes:
>> I wonder, why the following returns NULL and not 0 in 9.5.3?
>
>> # select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1);
>
> Because the result of the array_remove is an empty array, which is
> defined to be zero-dimensional in PG.
Reading this, I'm a bit confused about why:
select array_remove(ARRAY[NULL, NULL, NULL], NULL);
Results in:
array_remove
--------------
{}
(1 row)
How does it now which unknown value to remove from that array of unknown values? Shouldn't the result be:
{NULL,NULL,NULL}?
(Sorry for sort-of hijacking this thread)
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
Re: select array_remove(ARRAY[NULL,NULL,NULL],NULL); returns {} instead of {NULL,NULL,NULL}
From
"David G. Johnston"
Date:
> On 08 Aug 2016, at 20:19, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Alexander Farber <alexander.farber@gmail.com> writes:
>> I wonder, why the following returns NULL and not 0 in 9.5.3?
>
>> # select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1);
>
> Because the result of the array_remove is an empty array, which is
> defined to be zero-dimensional in PG.
Reading this, I'm a bit confused about why:
select array_remove(ARRAY[NULL, NULL, NULL], NULL);
Results in:
array_remove
--------------
{}
(1 row)
How does it now which unknown value to remove from that array of unknown values? Shouldn't the result be:
{NULL,NULL,NULL}?
Is this a philosophical or technical question?
For the former I don't see why one would choose to define this function in any other way. If you accept that the searching value can be NULL then it follows that you must compare two NULLs as equal. If you don't accept that comparison then specifying NULL should result in an error (if you really don't want to remove anything don't call the function). Having it error when useful behavior can be defined seems wasteful - this way there isn't a need to write a "strip_nulls" function.
For the later its pretty much a simple "if (source is null and target is null) then {remove} else if (compares equal using equality operator) then { remove } else { leave }"
see /src/backend/utils/adt/arrayfuncs.c@6098
David J.
Re: select array_remove(ARRAY[NULL,NULL,NULL],NULL); returns {} instead of {NULL,NULL,NULL}
From
Tom Lane
Date:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Aug 8, 2016 at 5:51 PM, Alban Hertroys <haramrae@gmail.com> wrote:
>> How does it now which unknown value to remove from that array of unknown
>> values? Shouldn't the result be:
>> {NULL,NULL,NULL}?
> Is this a philosophical or technical question?
> For the former I don't see why one would choose to define this function in
> any other way. If you accept that the searching value can be NULL then it
> follows that you must compare two NULLs as equal.
Strictly speaking, array_remove (and I think array_replace as well)
implements "IS NOT DISTINCT FROM" semantics rather than "=" semantics.
I dunno that we want to make the documentation use that wording though,
it'd probably confuse more people than it helped.
regards, tom lane