Re: BUG #3543: ARRAY(SELECT ...) contruct yields NULL without rows - Mailing list pgsql-bugs

From Tom Lane
Subject Re: BUG #3543: ARRAY(SELECT ...) contruct yields NULL without rows
Date
Msg-id 3764.1187276421@sss.pgh.pa.us
Whole thread Raw
In response to Re: BUG #3543: ARRAY(SELECT ...) contruct yields NULL without rows  (Decibel! <decibel@decibel.org>)
List pgsql-bugs
Decibel! <decibel@decibel.org> writes:
> On Thu, Aug 16, 2007 at 11:11:49AM +0000, Jens Schicke wrote:
>> SELECT ARRAY(SELECT 1 WHERE 1 = 0) IS NULL; -- true

> And just what would you have it return? An array with no elements is by
> definition NULL.

No, there is a difference:

regression=# select null::int4[];
 int4
------

(1 row)

regression=# select '{}'::int4[];
 int4
------
 {}
(1 row)

I'm inclined to think that Jens is right and it'd be more consistent to
return the latter when the SELECT returns no rows.  It'd be a behavioral
change more than a bug fix, though, so I would not suggest back-patching
it.

[ looks at code... ]  It looks like the implementation problem is for
ExecScanSubPlan to know what the array element type is supposed to be.
Normally it finds that out from the sub-select's returned rows; but
if there aren't any then it'll have to do something else.

            regards, tom lane

pgsql-bugs by date:

Previous
From: Decibel!
Date:
Subject: Re: BUG #3543: ARRAY(SELECT ...) contruct yields NULL without rows
Next
From: Stephan Szabo
Date:
Subject: Re: BUG #3542: Dropped and recreated columns have problems with NOT NULL contraints