COALESCE documentation
Hello,
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;
--
*Ing. Ondřej Navrátil, Ph.D.*
IT Analytik
M +420 728 625 950
E onavratil@monetplus <onavratil@monetplus.cz>.cz <onavratil@monetplus.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/>
On Tue, 2024-07-02 at 12:45 +0200, Navrátil, Ondřej wrote:
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.
+1
Do you want to write a documentation patch?
Yours,
Laurenz Albe
On 02.07.24 12:45, Navrátil, Ondřej wrote:
Hello,
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.
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
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.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 ENDThat 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 <onavratil@monetplus.cz>.cz <onavratil@monetplus.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/>
On Wednesday, July 3, 2024, Navrátil, Ondřej <onavratil@monetplus.cz> wrote:
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.
I’m already writing a patch to better document NULL behavior in PostgreSQL
and will add whatever we come up with to that. I really doubt we are
going to change this in the name of standard conformance. One can get
standard behavior via case of really needed.
David J.
On Wed, 2024-07-03 at 11:42 +0200, Navrátil, Ondřej wrote:
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
That only goes to say that Oracle is not very standard compliant, but
I wouldn't expect anything else from a system where '' IS NULL.
Yours,
Laurenz Albe
Peter Eisentraut <peter@eisentraut.org> writes:
I think this is actually a bug in the implementation, not in the
documentation. That is, the implementation should behave like the
documentation suggests.
The trouble with that is that it presumes that the standard's
definition of IS NOT NULL is not broken. I think it *is* broken
for rowtypes; it certainly cannot be claimed to be intuitive.
We already have disclaimers about that in our documentation
about IS [NOT] NULL. I don't really want to propagate similar
confusion into COALESCE, much less everyplace else that this'd
matter.
Having said that, I'm not sure that substituting "is distinct from
null" in the COALESCE documentation is much better, because it's not
clear to me that we're entirely standards-compliant about what that
means for rowtypes either.
regards, tom lane