Re: COALESCE documentation

I do not have the specs on hand. But if the CASE equivalence should hold,
then I deduce that

COALESCE ( ROW(NULL, 1), ROW(NULL, 2)) results in ROW(NULL, 2)
COALESCE ( ROW(NULL, 2), ROW(NULL, 1)) results in ROW(NULL, 1)

I understand that order of parameters for coalesce matters for parameters
that "are not null". It feels unnatural though that the result should be
different in this case, since both parameters are NULL. It may be a weak
point in the standard, worth investigating.

It may, however, relate to my "original" question on StackOverflow -
whether it is feasible for a user to differentiate between NULL and
ROW(NULL, NULL) - AFAIK the IS DISTINCT FROM operator is Postgres extension
and without that there is no way to distinguish the two as by the standard.

To get back to my "docs patch proposal" - I could submit a patch if you
would kindly point me where to start. I would also prefer to submit such a
patch only after it is decided whether this is a docs bug or impl bug, and
whether or not it will be fixed (it would be suitable to put a disclaimer
in case the implementation intentionally diverges from the standard). Most
importantly, the implementation and documentation should be in accord, even
if it means both of them deviate from the standard.

On a side note, I tested similar behavior in Oracle databases, and for
them, something like
select testtype(null, null) is null; -- returns 0 (false)
select testtype(null, null) is not null; -- returns 1 (true)
...and as far as I could test, in Oracle the IS NULL and IS NOT NULL
operators are truly dual, which does not hold for Postgres or the standard
- where (1, NULL) is neither NULL nor NOT NULL. There is a lot of
discrepancy concerning composite types in general, to such an extent that
being vendor-agnostic is close to impossible to achieve and there is a
strong incentive to avoid composites in such scenarios.

st 3. 7. 2024 v 11:11 odesílatel Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
napsal:

> 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
>

--

*Ing. Ondřej Navrátil, Ph.D.*
IT Analytik
M +420 728 625 950
E onavratil(at)monetplus <onavratil(at)monetplus(dot)cz>.cz <onavratil(at)monetplus(dot)cz>

MONET+,a.s., Za Dvorem 505, 763 14 Zlín-Štípa
monetplus.com <https://www.monetplus.cz/> | linkedin
<https://www.linkedin.com/company/monetplus/> | facebo
<https://www.facebook.com/monetplus/>ok
<https://www.facebook.com/monetplus/>