public inbox for [email protected]
help / color / mirror / Atom feedcount() counts ROW values that are NULL
4+ messages / 3 participants
[nested] [flat]
* count() counts ROW values that are NULL
@ 2022-03-18 20:58 Erwin Brandstetter <[email protected]>
2022-03-18 21:28 ` Re: count() counts ROW values that are NULL Tom Lane <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Erwin Brandstetter @ 2022-03-18 20:58 UTC (permalink / raw)
To: pgsql-docs
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. See:
*db<>fiddle [here](
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=7364f3f582322ac687b39c677826a074)*
Feels like a bug, but I cannot imagine how this would have slipped
everybody's attention for so long. It should at least be documented. Maybe:
Computes the number of input rows in which the input value is not a plain
NULL value. (Composite or ROW values count in any case - even if value IS
NULL evaluates to true.)
Regards
Erwin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: count() counts ROW values that are NULL
2022-03-18 20:58 count() counts ROW values that are NULL Erwin Brandstetter <[email protected]>
@ 2022-03-18 21:28 ` Tom Lane <[email protected]>
2022-03-18 22:05 ` Re: count() counts ROW values that are NULL Erwin Brandstetter <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2022-03-18 21:28 UTC (permalink / raw)
To: Erwin Brandstetter <[email protected]>; +Cc: pgsql-docs
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.)
You could use "COUNT(*) FILTER (WHERE NOT (whatever IS NULL))" if you want
to count values meeting the IS NULL definition. (Buttressing my point
that IS NULL is not well thought out, the obvious "whatever IS NOT NULL"
doesn't work here, because it's not the inverse of "whatever IS NULL".)
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: count() counts ROW values that are NULL
2022-03-18 20:58 count() counts ROW values that are NULL Erwin Brandstetter <[email protected]>
2022-03-18 21:28 ` Re: count() counts ROW values that are NULL Tom Lane <[email protected]>
@ 2022-03-18 22:05 ` Erwin Brandstetter <[email protected]>
2022-03-18 23:27 ` Re: count() counts ROW values that are NULL David G. Johnston <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Erwin Brandstetter @ 2022-03-18 22:05 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-docs
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.)
>
> You could use "COUNT(*) FILTER (WHERE NOT (whatever IS NULL))" if you want
> to count values meeting the IS NULL definition. (Buttressing my point
> that IS NULL is not well thought out, the obvious "whatever IS NOT NULL"
> doesn't work here, because it's not the inverse of "whatever IS NULL".)
>
I am aware of the mess, and I feel your pain (and my own).
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:
... in which the input value is not null (does not evaluate to a scalar
NULL).
To give them a fighting chance.
Regards
Erwin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: count() counts ROW values that are NULL
2022-03-18 20:58 count() counts ROW values that are NULL Erwin Brandstetter <[email protected]>
2022-03-18 21:28 ` Re: count() counts ROW values that are NULL Tom Lane <[email protected]>
2022-03-18 22:05 ` Re: count() counts ROW values that are NULL Erwin Brandstetter <[email protected]>
@ 2022-03-18 23:27 ` David G. Johnston <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2022-03-18 23:27 UTC (permalink / raw)
To: Erwin Brandstetter <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-docs
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-03-18 23:27 UTC | newest]
Thread overview: 4+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-03-18 20:58 count() counts ROW values that are NULL Erwin Brandstetter <[email protected]>
2022-03-18 21:28 ` Tom Lane <[email protected]>
2022-03-18 22:05 ` Erwin Brandstetter <[email protected]>
2022-03-18 23:27 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox