Обсуждение: COALESCE documentation
Hello,
--
as per documentation
> 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.
Long story short
select coalesce((null, null), (10, 20)) as magic;
returns
magic
------- (,)
(1 row)However, this is true:
select (null, null) is null;Ing. Ondřej Navrátil, Ph.D.
IT Analytik
M +420 728 625 950
E onavratil@monetplus.cz
MONET+,a.s., Za Dvorem 505, 763 14 Zlín-Štípa
monetplus.com | linkedin | facebook
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.
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.albe@cybertec.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@monetplus.cz
MONET+,a.s., Za Dvorem 505, 763 14 Zlín-Štípa
monetplus.com | linkedin | facebook