Thread: NULL in arrays

A person (cross) in the irc channel have found a bug with the new arrays.
Here is what I did to reproduce:

dennis=# CREATE TABLE foo (a int[]);
CREATE TABLE
dennis=# INSERT INTO foo VALUES (ARRAY[2,NULL]);
INSERT 25353 1

That last insert contains a NULL value which are not allowed in arrays and
yet a insert is performed. The table contains a NULL value afterwards
(and no array).

This is something that should be fixed for 7.4.2, so that it gives an
error instead of inserting bad data.

--
/Dennis Björklund

On Thu, 15 Jan 2004, Dennis Bjorklund wrote:

> A person (cross) in the irc channel

Actually, he calls himself crass which is the only name I have :-)

Not very important, but I like things to be correct (when I can).

--
/Dennis Björklund

Dennis Bjorklund <db@zigo.dhs.org> writes:
> dennis=# INSERT INTO foo VALUES (ARRAY[2,NULL]);
> INSERT 25353 1

> That last insert contains a NULL value which are not allowed in arrays and
> yet a insert is performed. The table contains a NULL value afterwards
> (and no array).

As we used to say at HP, this is not a bug, it's a definition
disagreement.  You need to give a coherent argument why we should
change, not just claim it's wrong.

Given the present lack of support for null elements in arrays, it's
impossible to have any really pleasant behavior in cases like this.
But I don't see an inherent reason why "raise an error" is better than
"return a null array".

I think Joe Conway is planning to tackle that underlying misfeature
for 7.5.  Whenever it happens, it will result in a number of behavioral
changes for arrays.  I'm not eager to move the definition around in the
meantime, especially not in dot-releases.

            regards, tom lane

On Thu, 15 Jan 2004, Tom Lane wrote:

> > dennis=# INSERT INTO foo VALUES (ARRAY[2,NULL]);
> > INSERT 25353 1
>
> > That last insert contains a NULL value which are not allowed in arrays and
> > yet a insert is performed. The table contains a NULL value afterwards
> > (and no array).
>
> As we used to say at HP, this is not a bug, it's a definition
> disagreement.  You need to give a coherent argument why we should
> change, not just claim it's wrong.
>
> Given the present lack of support for null elements in arrays, it's
> impossible to have any really pleasant behavior in cases like this.

This is the reason, you are not allowed to store NULL in arrays. Why
should you not give an error if someone tries to do that anyway.

> But I don't see an inherent reason why "raise an error" is better than
> "return a null array".

If you try to insert the value 'goofy' in a integer column, would you
expect an error or that you get the value NULL inserted?

--
/Dennis Björklund

Tom Lane wrote:
> As we used to say at HP, this is not a bug, it's a definition
> disagreement.  You need to give a coherent argument why we should
> change, not just claim it's wrong.

Additionally, this behavior was discussed during the 7.4 development and
beta cycles on at least a couple occassions -- that would have been the
time to complain, not now. For example, see this thread during beta:
http://archives.postgresql.org/pgsql-hackers/2003-07/msg00747.php

> Given the present lack of support for null elements in arrays, it's
> impossible to have any really pleasant behavior in cases like this.
> But I don't see an inherent reason why "raise an error" is better than
> "return a null array".

In fact, the above referenced thread shows a scenario where the former
behavior is unpleasant.

> I think Joe Conway is planning to tackle that underlying misfeature
> for 7.5.  Whenever it happens, it will result in a number of behavioral
> changes for arrays.  I'm not eager to move the definition around in the
> meantime, especially not in dot-releases.

Agreed. This and a few other changes to bring us closer to SQL99/SQL2003
compliance (see this thread:
   http://archives.postgresql.org/pgsql-hackers/2003-06/msg01167.php
) will cause some reasonably significant behavioral changes.


Joe

On Thu, 15 Jan 2004, Joe Conway wrote:

> Additionally, this behavior was discussed during the 7.4 development and
> beta cycles on at least a couple occassions -- that would have been the
> time to complain, not now.

Well, I will complain whenever I see something I don't like :-) Just
because I did not read the above thread does not mean I will be quiet for
ever after. You can of course disagree and I have no trouble with that.

> For example, see this thread during beta:
> http://archives.postgresql.org/pgsql-hackers/2003-07/msg00747.php

I've been reading that now. I still don't understand what one gains by
transforming arrays with a null value inside into just a null value.

> > Given the present lack of support for null elements in arrays, it's
> > impossible to have any really pleasant behavior in cases like this.
> > But I don't see an inherent reason why "raise an error" is better than
> > "return a null array".
>
> In fact, the above referenced thread shows a scenario where the former
> behavior is unpleasant.

What I saw in that thread was problems with STRICT and that the function
got inlined because ARRAY[] was assumed to be strict. ARRAY[] is not
strict if it produce an error when one of the elements are NULL, and this
this inlining was invalid, not anything else.

The reason I assumed it was a bug was that I did not see any gain of
allowing it to transforming arrays with NULL inside, into a NULL array. To
me this is as logical as transforming invalid integers into NULL values
and not into errors. Also I didn't see it in the documentation which
further convinced me it was a bug.

In the documentation I got the impression that NULL values inside an array
was not allowed. Now I know that you are allowed to form such an
expression and that it evaluates to the NULL array. The documentation
should be fixed then (or maybe it is documented buy i've not seen it).

--
/Dennis Björklund

Dennis Bjorklund <db@zigo.dhs.org> writes:
> In the documentation I got the impression that NULL values inside an array
> was not allowed. Now I know that you are allowed to form such an
> expression and that it evaluates to the NULL array. The documentation
> should be fixed then (or maybe it is documented buy i've not seen it).

I don't see anywhere that this behavior is mentioned, either.  Probably
should put something into section 4.2.10 (along with a note that the
behavior is expected to change soon).

            regards, tom lane

Dennis Bjorklund wrote:
> On Thu, 15 Jan 2004, Joe Conway wrote:
>>Additionally, this behavior was discussed during the 7.4 development and
>>beta cycles on at least a couple occassions -- that would have been the
>>time to complain, not now.
>
> Well, I will complain whenever I see something I don't like :-)

No issue with that, I just like to encourage more, ahem, timely feedback
whenever possible ;-).

> In the documentation I got the impression that NULL values inside an array
> was not allowed. Now I know that you are allowed to form such an
> expression and that it evaluates to the NULL array. The documentation
> should be fixed then (or maybe it is documented buy i've not seen it).

I agree, and see Tom does too in a nearby post. Do you want to propose
some wording, or just leave it up to me?

Joe

On Thu, 15 Jan 2004, Joe Conway wrote:

> I agree, and see Tom does too in a nearby post. Do you want to propose
> some wording, or just leave it up to me?

You do it, and I'll just complain later if I don't like it :-)

I still think it's a strange behaviour, but as long as it's documented
it's not a bug (per definition).

--
/Dennis Björklund