Re: COALESCE documentation

On Wed, 2024-07-03 at 11:00 +0200, Peter Eisentraut wrote:
> On 02.07.24 12:45, Navrátil, Ondřej wrote:
> > as per documentation
> > <https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL>
> > > The |COALESCE| function returns the first of its arguments that is
> > not null. Null is returned only if all arguments are null.
> >
> > This is not exactly true. In fact:
> > The |COALESCE| function returns the first of its arguments that *is
> > distinct* *from *null. Null is returned only if all arguments *are not
> > distinct from* null.
> >
> > See my stack overflow question here
> > <https://stackoverflow.com/questions/78691097/postgres-null-on-composite-types>.
> >
> > Long story short
> >
> > > select coalesce((null, null), (10, 20)) as magic; |
> >
> > returns
> >
> > > magic ------- (,) (1 row)|
> >
> > However, this is true:
> >
> > > select (null, null) is null;|
>
> I think this is actually a bug in the implementation, not in the
> documentation. That is, the implementation should behave like the
> documentation suggests.

You are right. I find this in the standard:

COALESCE (V1, V2) is equivalent to the following <case specification>:

CASE WHEN V1
IS NOT NULL THEN
V1 ELSE
V2 END

That would mean that coalesce(ROW(1,NULL), ROW(2,1)) should return
the second argument. Blech. I am worried about the compatibility pain
such a bugfix would cause...

Yours,
Laurenz Albe