Thread: The semantics of (NULL,NULL) vs NULL

Hi,

I have a question.

As I noted that (null,null) is null, I thought why put (null,null) in an array when that is the same as putting null in there.
However, when trying to unnest that array I got an error when using null instead of the tuple. I experimented a bit, and
read the documentation on row and array comparison, but I could not find documentation explaining the results below.

create type int_tuple as (a int, b int);
CREATE TYPE
=# select (null,null)::int_tuple is null;
 ?column?
----------
 t
(1 row)

=# select array[null::int_tuple] = array[null::int_tuple];
 ?column?
----------
 t
(1 row)

as the documentation states: 'Array comparisons compare the array contents element-by-element'
Taking into account the results above I would expect the following to be true

=# select array[(null,null)::int_tuple] = array[null::int_tuple];
 ?column?
----------
 f
(1 row)

apparently (null,null) is has more information then just null:

=# select null::int_tuple;
 int_tuple
-----------
 
(1 row)


=# select (null,null)::int_tuple;
 row
-----
 (,)
(1 row)


=# select * from  unnest(array[null::int_tuple]);
ERROR:  function returning set of rows cannot return null value

=# select * from unnest(array[(null,null)::int_tuple]);
 a | b
---+---
   | 
(1 row)


Can anyone explain why:


create type int_tuple as (a int, b int);
CREATE TYPE
=# select (null,null)::int_tuple is null;
 ?column?
----------
 t
(1 row)

and not

=# select array[(null,null)::int_tuple] = array[null::int_tuple];
 ?column?
----------
 f
(1 row)


Thanks in advance,

Ingmar


                                                   version                                                   
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.6.1 20110908 (Red Hat 4.6.1-9), 64-bit
(1 row)

On 2 August 2012 17:47, Ingmar Brouns <swingi@gmail.com> wrote:
> As I noted that (null,null) is null, I thought why put (null,null) in an
> array when that is the same as putting null in there.
> However, when trying to unnest that array I got an error when using null
> instead of the tuple. I experimented a bit, and
> read the documentation on row and array comparison, but I could not find
> documentation explaining the results below.
>
> create type int_tuple as (a int, b int);
> CREATE TYPE
> =# select (null,null)::int_tuple is null;
>  ?column?
> ----------
>  t
> (1 row)

Without commenting on the specifics of your test-case, I don't think
any thorough reading of the SQL standard would leave the reader with
the impression that the behaviour of SQL NULL is consistent with some
simple axiom that can be generalised from. I found this blogpost to be
insightful:

http://thoughts.j-davis.com/2009/08/02/what-is-the-deal-with-nulls/

--
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services

Ingmar Brouns <swingi@gmail.com> wrote:

> Can anyone explain why:

When it comes to record types, IS NULL is defined to mean "there is
no record or all elements of the record are null".  NOT NULL is
defined to mean that *all elements* of the record are not null.

-Kevin