Re: inconsistent composite type null handling in plpgsql out variable

On Fri, Aug 28, 2009 at 1:38 PM, Kevin
Grittner<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>
>> 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.
>
> Quite apart from the issue you're pursuing, this is another example of
> how the COALESCE predicate in PostgreSQL is not compliant with the
> standard, where it is *defined as* an abbreviation of the CASE
> predicate.
>
> I might be persuaded otherwise by a reference to the standard, but my
> understanding is that the CASE predicate should be conceptually
> similar to the "? :" predicate in C.  Does anyone else feel that these
> aren't implemented quite right in PostgreSQL?

I agree with you...it's a mess. Here's what I'm thinking:

1) 'is null', coalesce, STRICT, PQgetisnull, etc should all behave in
consistent manner (and ideally should use the same code paths)
2) make a decision on composite types:
3) If we decide the sql standard is correct, so that (null, null) is
null == true, then we should observe rule 1 and make things work in
consistent way. This means, for example, that null::foo and (null,
null)::foo should not be distinct.
4) If we decide to continue to ignore the standard, so that null::foo
is distinct from (null, null)::foo (which is basically how things work
now), then IS NULL as currently implemented is wrong and should be
changed.
5) plpgsql has a lot of corner cases where composite type behavior is
different from sql...POLS violations. For example, to assign a type
from a type selected in a query, sometimes you have to do (foo).* and
sometimes you have to proxy it through a record variable. input and
output arguments are especially vexing.

merlin