Thread: 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
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 arenull. > > 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 argumentsare 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.
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
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