Re: count() counts ROW values that are NULL

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: count() counts ROW values that are NULL
Дата
Msg-id CAKFQuwYfZY-=tE8Ej3EF3vmG1gUo5FQoGu-i-41xk=YR1jej1A@mail.gmail.com
обсуждение исходный текст
Ответ на Re: count() counts ROW values that are NULL  (Erwin Brandstetter <brsaweda@gmail.com>)
Список pgsql-docs
On Fri, Mar 18, 2022 at 3:06 PM Erwin Brandstetter <brsaweda@gmail.com> wrote:

On Fri, 18 Mar 2022 at 22:28, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Erwin Brandstetter <brsaweda@gmail.com> 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.

В списке pgsql-docs по дате отправления:

Предыдущее
От: Erwin Brandstetter
Дата:
Сообщение: Re: count() counts ROW values that are NULL
Следующее
От: PG Doc comments form
Дата:
Сообщение: systemd service start - disable timeout with "infinity"