public inbox for [email protected]  

help / color / mirror / Atom feed

From: David G. Johnston <[email protected]>
To: Erwin Brandstetter <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: pgsql-docs <[email protected]>
Subject: Re: count() counts ROW values that are NULL
Date: Fri, 18 Mar 2022 16:27:20 -0700
Message-ID: <CAKFQuwYfZY-=tE8Ej3EF3vmG1gUo5FQoGu-i-41xk=YR1jej1A@mail.gmail.com> (raw)
In-Reply-To: <CAGHENJ4yKo4mDLwjCN-nGqpekWZvb=rW=h+diM9Smwp7rZGqMg@mail.gmail.com>
References: <CAGHENJ6fxanftiWOf9-ZJKA4bQ5v97YH0YWzDKfv6Mvw8ZPtkg@mail.gmail.com>
	<[email protected]>
	<CAGHENJ4yKo4mDLwjCN-nGqpekWZvb=rW=h+diM9Smwp7rZGqMg@mail.gmail.com>

On Fri, Mar 18, 2022 at 3:06 PM Erwin Brandstetter <[email protected]>
wrote:

>
> On Fri, 18 Mar 2022 at 22:28, Tom Lane <[email protected]> wrote:
>
>> Erwin Brandstetter <[email protected]> writes:
>> >
>> https://www.postgresql.org/docs/current/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE
>> > The manual says:
>> >> count ( "any" ) → bigint
>> >> Computes the number of input rows in which the input value is not null.
>>
>> > But ROW values or composite types that "are null" are counted, anyway.
>>
>> Well, there's nulls and nulls.  The SQL "IS NULL" construct is fairly
>> badly designed IMO, because it considers both a plain NULL and a
>> row-of-all-NULL-fields to be "null".  count(), like just about everything
>> in Postgres other than "IS NULL", considers only a plain NULL to be null.
>>
>> This is discussed somewhere in the manual, but I think it's under IS NULL,
>> not under all the other places that'd have to be annotated if we decide to
>> annotate as you're suggesting.  (One example is that functions that are
>> marked STRICT use the tighter interpretation.)
>>
>>

> But count(<expression>) is among the most frequently used functions, and
> hardly any user reading the manual will be aware of the implications. Maybe
> just:
>

I'm with Tom on this.  The behavior exhibited is the expected behavior.  I
haven't looked, but if anything I would make the desired point in
"composite IS NULL" that this special (ROW(null) IS NULL -> true)
interpretation of NULL is limited to this SQL Standard mandated operator
and that when speaking generally about a composite being null throughout
the documentation it is done in a scalar sense (I don't know how best to
word this but select null::rel yields "" while select row(null)::rel yields
"()" on printout (assuming rel has a single column)).

David J.

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: count() counts ROW values that are NULL
  In-Reply-To: <CAKFQuwYfZY-=tE8Ej3EF3vmG1gUo5FQoGu-i-41xk=YR1jej1A@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox