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
