Thread: Bug ? different behaviour between 8.3 and 8.4 won IS NULL with sub arrays of nulls

Hi,

I found a difference of behaviour between 8.3 and 8.4 on IS NULL with 
multi-level arrays with NULL values.

I looked at the Changelog between 8.3 and 8.4, but I didn't find something 
really clear about this.

Is this a bug or a known issue or a normal, documented, 
difference of behaviour ?

See the following use case:

<~~~~~~~~~~~~~~~~~~~~~~~~
$ psql -p 5433 -U postgres -h localhost -c \
'SELECT substring(version() from 0 for 15),  ROW(NULL,NULL) IS NULL, ROW(ROW(NULL),NULL) IS NULL;'
   substring    | ?column? | ?column?
----------------+----------+---------- PostgreSQL 8.3 | t        | f
(1 ligne)

$ psql -p 5434 -U postgres -h localhost -c \
'SELECT substring(version() from 0 for 15),  ROW(NULL,NULL) IS NULL, ROW(ROW(NULL),NULL) IS NULL;'
   substring    | ?column? | ?column?
----------------+----------+---------- PostgreSQL 8.4 | t        | t
(1 ligne)
~~~~~~~~~~~~~~~~~~~~~~~~>

Regards,
-- 
Jehan-Guillaume (ioguix) de Rorthais
DBA
http://www.dalibo.com

ioguix@free.fr writes:
> I found a difference of behaviour between 8.3 and 8.4 on IS NULL with 
> multi-level arrays with NULL values.

8.3's behavior is just a bug --- try comparing the results when the
values are variables that happen to be null, rather than simple
constant nulls.  8.4 is consistent with that case, 8.3 isn't.
        regards, tom lane

On Tue, 19 Jan 2010, Tom Lane wrote:

> ioguix@free.fr writes:
>> I found a difference of behaviour between 8.3 and 8.4 on IS NULL with
>> multi-level arrays with NULL values.
>
> 8.3's behavior is just a bug ---

Ok, should I report through the -bugs ml for tracking purpose ? or 
is it useless cause it's on -hackers andsomeone will jump on this bug to 
"fix and forget it" ?

> try comparing the results when the
> values are variables that happen to be null, rather than simple
> constant nulls.  8.4 is consistent with that case, 8.3 isn't.

Right, it behaves consistently with variables.

Here is another test case where 8.3 is inconsistent with *himself* this 
time:

<~~~~~~~~~~~~~~~~~~~~~~~~~~~~
postgres=# SELECT substring(version(),12,5); substring
----------- 8.3.9
(1 ligne)

postgres=# SELECT ROW(ROW(NULL),NULL) IS NULL FROM (SELECT 1) t; ?column?
---------- t
(1 ligne)

postgres=# SELECT ROW(ROW(NULL),NULL) IS NULL; ?column?
---------- f
(1 ligne)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~>


>             regards, tom lane
>

-- 
Jehan-Guillaume (ioguix) de Rorthais
DBA
http://www.dalibo.com