PostgreSQL `IS NOT NULL` does not work properly for composite types

Question

I am trying to create a function which should map the table row to my user-defined type (which is a composite type) and return that as a result. If some column in that table is NULL, then IS NOT NULL check on my custom type does not work!

Example

I have a simple composite type:

CREATE TYPE my_custom_type AS (
  sender VARCHAR(30),
  destination VARCHAR(30),
  count INTEGER
);

And a table:

CREATE TABLE messages (
  id INTEGER PRIMARY KEY,
  sender VARCHAR(30),
  destination VARCHAR(30),
  count INTEGER
);

Insert single row for this example:

INSERT INTO messages VALUES (1, 'sender', 'destination', 100);

And now I want to create a function which will return that row as a custom Postgres type:

CREATE OR REPLACE FUNCTION my_custom_function() 
RETURNS my_custom_type AS
$$
DECLARE
    result my_custom_type;
BEGIN

    SELECT sender, destination, count
    FROM messages
    LIMIT 1
    INTO result;

    IF result IS NULL THEN
        RAISE EXCEPTION 'no data';
    END IF;

    RETURN result;

END; $$
    LANGUAGE plpgsql;

I get expected results when I use this function:

SELECT * from my_custom_function();

But unexpected behaviors start to occur when some column is updated to NULL:

UPDATE messages SET destination = NULL;

When I execute the function again, it still returns good results:

But if I change the IS NULL condition to IS NOT NULL condition:

CREATE OR REPLACE FUNCTION my_custom_function() 
RETURNS my_custom_type AS
$$
DECLARE
    result my_custom_type;
BEGIN

    SELECT sender, destination, count
    FROM messages
    LIMIT 1
    INTO result;

    IF result IS NOT NULL THEN
        RETURN result;
    END IF;

    RAISE EXCEPTION 'no data';

END; $$
    LANGUAGE plpgsql;

Then I got an error: ERROR: no data

Can someone please explain me why this does not work? It makes no sense to me...

Answer 1

A composite type IS NULL if all its elements are NULL, and it IS NOT NULL if all elements are not NULL.

That is required by the SQL standard.

This has unpleasant consequences, for example is x IS NOT NULL not the same as NOT x IS NULL for composite types. Also, two values that both return TRUE for the IS NULL test can be distinct:

SELECT ROW(NULL, NULL) IS DISTINCT FROM NULL;

 ?column? 
══════════
 t
(1 row)

The SQL standard clearly didn't do a good job there. Read this thread from the pgsql-hackers list for further edificationconfusion.