From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Peter Eisentraut <peter@eisentraut.org>
To: Navrátil, Ondřej <onavratil@monetplus.cz>
To: pgsql-docs@lists.postgresql.org
Subject: Re: COALESCE documentation
Date: Wed, 03 Jul 2024 11:11:51 +0200
Message-ID: <0c98e0da0df5780f05e3eb598bb84955450a74f8.camel@cybertec.at> (raw)
In-Reply-To: <62f888dc-1ee5-48cf-b76c-6a75dcfa8b10@eisentraut.org>
References: <CAEELDaqnn5GayihUUhnKLR1_fegb-_b2i+4_dP4U192uK7j9vw@mail.gmail.com>
<62f888dc-1ee5-48cf-b76c-6a75dcfa8b10@eisentraut.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: laurenz.albe@cybertec.at, peter@eisentraut.org, onavratil@monetplus.cz, pgsql-docs@lists.postgresql.org
Subject: Re: COALESCE documentation
In-Reply-To: <0c98e0da0df5780f05e3eb598bb84955450a74f8.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox