Thread: PG 7.4 BETA 3: Bug in NULL arrays updating
When updating a NULL cell which is an array of something,
setting an adressed member of a non existent array, the value of the
cell is not changed.
> CREATE TABLE dummy (foo INT, bar VARCHAR[]);
CREATE TABLE
> INSERT INTO dummy (foo) VALUES (1);
INSERT 43266442 1
> SELECT bar IS NULL AS is_null FROM dummy;
is_null
---------
t
(1 row)
> UPDATE dummy SET bar[0]='Blah';
UPDATE 1
> SELECT * FROM dummy;
foo | bar
-----+-----
1 |
(1 row)
> SELECT bar IS NULL AS is_null FROM dummy;
is_null
---------
t
(1 row)
> SELECT version();
version
------------------------------------------------------------------------
PostgreSQL 7.4beta3 on i386-unknown-freebsd4.5, compiled by GCC 2.95.3
(1 row)
I expected that the bar column be set to {"Blah"}. After a few
seconds of thought, I pictured the updating as setting the first
member of the NULL array (NULL[0] := 'Blah'). Ouch that looks like a
"null pointer". So maybe the non updating of bar is correct. But in
this case an error or a warning should be raised.
--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
Bertrand Petit <pgsql@phoe.frmug.org> writes:
> When updating a NULL cell which is an array of something,
> setting an adressed member of a non existent array, the value of the
> cell is not changed.
Assigning to a member of a NULL array has always yielded another NULL
array. While I've never been particularly satisfied with that behavior
either, it has some logical symmetry to it. What do you think the
behavior ought to be? (In particular, if a non-null array should
result, where do we get its dimensions and subscripts from?)
regards, tom lane
Tom Lane wrote:
> Bertrand Petit <pgsql@phoe.frmug.org> writes:
>> When updating a NULL cell which is an array of something,
>>setting an adressed member of a non existent array, the value of the
>>cell is not changed.
>
> Assigning to a member of a NULL array has always yielded another NULL
> array. While I've never been particularly satisfied with that behavior
> either, it has some logical symmetry to it. What do you think the
> behavior ought to be? (In particular, if a non-null array should
> result, where do we get its dimensions and subscripts from?)
I think the behavior is correct. An analogy I is text concatenation. If
I concatenate 'a' to NULL::text, I get NULL. But if I concatenate 'a' to
an empty text value, '', I get 'a'.
Similarly if you assign to an element of an empty array, '{}', you get
an array with the one appended element. Not sure if this works pre-7.4
though -- I know I made some changes related to this, but I forget the
exact details.
Joe
On Wed, Sep 24, 2003 at 12:09:52AM -0400, Tom Lane wrote:
>
> Assigning to a member of a NULL array has always yielded another NULL
> array. While I've never been particularly satisfied with that behavior
> either, it has some logical symmetry to it. What do you think the
> behavior ought to be? (In particular, if a non-null array should
> result, where do we get its dimensions and subscripts from?)
My view on this is that the problem should be simplified by
throwing an error and a notice reminding the user to use coalesce().
Another view might be that, on an update operation, a new
array be created as you sugested. The type and number of dimensions of
the new array would be those of the updated column. All array members
would be NULLs except the explicitely referenced members. The
referenced subscripts would be the upper bounds for the dimensions
sizes.
Regards,
Bertrand.
--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage