pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedCOALESCE documentation
8+ messages / 5 participants
[nested] [flat]
* COALESCE documentation
@ 2024-07-02 10:45 Navrátil, Ondřej <onavratil@monetplus.cz>
0 siblings, 2 replies; 8+ messages in thread
From: Navrátil, Ondřej @ 2024-07-02 10:45 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org
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/;
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: COALESCE documentation
@ 2024-07-03 08:49 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Navrátil, Ondřej <onavratil@monetplus.cz>
1 sibling, 0 replies; 8+ messages in thread
From: Laurenz Albe @ 2024-07-03 08:49 UTC (permalink / raw)
To: Navrátil, Ondřej <onavratil@monetplus.cz>; pgsql-docs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: COALESCE documentation
@ 2024-07-03 09:00 Peter Eisentraut <peter@eisentraut.org>
parent: Navrátil, Ondřej <onavratil@monetplus.cz>
1 sibling, 2 replies; 8+ messages in thread
From: Peter Eisentraut @ 2024-07-03 09:00 UTC (permalink / raw)
To: Navrátil, Ondřej <onavratil@monetplus.cz>; pgsql-docs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: COALESCE documentation
@ 2024-07-03 09:11 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Peter Eisentraut <peter@eisentraut.org>
1 sibling, 1 reply; 8+ messages in thread
From: Laurenz Albe @ 2024-07-03 09:11 UTC (permalink / raw)
To: Peter Eisentraut <peter@eisentraut.org>; Navrátil, Ondřej <onavratil@monetplus.cz>; pgsql-docs@lists.postgresql.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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: COALESCE documentation
@ 2024-07-03 09:42 Navrátil, Ondřej <onavratil@monetplus.cz>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 8+ messages in thread
From: Navrátil, Ondřej @ 2024-07-03 09:42 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Peter Eisentraut <peter@eisentraut.org>; pgsql-docs@lists.postgresql.org
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 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 <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/;
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: COALESCE documentation
@ 2024-07-03 12:34 David G. Johnston <david.g.johnston@gmail.com>
parent: Navrátil, Ondřej <onavratil@monetplus.cz>
1 sibling, 0 replies; 8+ messages in thread
From: David G. Johnston @ 2024-07-03 12:34 UTC (permalink / raw)
To: Navrátil, Ondřej <onavratil@monetplus.cz>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Peter Eisentraut <peter@eisentraut.org>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: COALESCE documentation
@ 2024-07-03 14:41 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Navrátil, Ondřej <onavratil@monetplus.cz>
1 sibling, 0 replies; 8+ messages in thread
From: Laurenz Albe @ 2024-07-03 14:41 UTC (permalink / raw)
To: Navrátil, Ondřej <onavratil@monetplus.cz>; +Cc: Peter Eisentraut <peter@eisentraut.org>; pgsql-docs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: COALESCE documentation
@ 2024-07-03 14:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Peter Eisentraut <peter@eisentraut.org>
1 sibling, 0 replies; 8+ messages in thread
From: Tom Lane @ 2024-07-03 14:57 UTC (permalink / raw)
To: Peter Eisentraut <peter@eisentraut.org>; +Cc: Navrátil, Ondřej <onavratil@monetplus.cz>; pgsql-docs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-07-03 14:57 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-02 10:45 COALESCE documentation Navrátil, Ondřej <onavratil@monetplus.cz>
2024-07-03 08:49 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-07-03 09:00 ` Peter Eisentraut <peter@eisentraut.org>
2024-07-03 09:11 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-07-03 09:42 ` Navrátil, Ondřej <onavratil@monetplus.cz>
2024-07-03 12:34 ` David G. Johnston <david.g.johnston@gmail.com>
2024-07-03 14:41 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-07-03 14:57 ` Tom Lane <tgl@sss.pgh.pa.us>
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