inconsistent composite type null handling in plpgsql out variable

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: inconsistent composite type null handling in plpgsql out variable
Date: 2009-08-28 16:10:39
Message-ID: b42b73150908280910y1cc6973etc010bca3f6ff3f6@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Today I ran into a problem relating to $subject. plpgsql's handling
of 'null' composite types is not consistent with what you get in sql:

create table foo(a text, b text);
create table bar(id int, f foo);
insert into bar values (1, ('a', 'b'));

create or replace function f(_foo out foo) returns foo as
$$
declare
r record;
begin
select coalesce(_foo, f) as f
from bar where id = 1 into r;

raise notice '%', r.f;

select f
from bar where id = 1 into r;

raise notice '%', r.f;
return;
end;
$$ language plpgsql;

plpgsql is not assigning the value to _foo in the first case because
coalesce is not treating the _foo as null for some reason.

I'm going to take this opportunity to editorialize a bit:
In this case postgresql is not applying either the insane sql standard
definition of null (non null composite type with fields null) or the
sane definition that is mostly used (a composite type may itself be
null independently of its fields). This leads to some very weird
behaviors, for example 'coalesce(foo, something)' and 'case when foo
is null then something else foo end' can give different answers.

postgresql treats non-null composite types with null fields as null in
terms of the 'IS NULL' operator but as non null every where else
(coalesce, STRICT, etc). IMO, the IS NULL behavior was hacked in for
compliance with the SQL standard. In the wider context of how we do
things, IS NULL simply lies to you. This isn't (IMO) really a big
deal, but when correcting the above behavior, which standard should we
apply?

merlin

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2009-08-28 16:36:37 Re: inconsistent composite type null handling in plpgsql out variable
Previous Message Dan O'Hara 2009-08-28 13:59:41 BUG #5021: ts_parse doesn't recognize email addresses with underscores