Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nVKjV-0004EW-4V for pgsql-docs@arkaria.postgresql.org; Fri, 18 Mar 2022 22:06:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nVKjU-0000OW-2G for pgsql-docs@arkaria.postgresql.org; Fri, 18 Mar 2022 22:06:04 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nVKjT-0000ON-Oo for pgsql-docs@lists.postgresql.org; Fri, 18 Mar 2022 22:06:03 +0000 Received: from mail-ej1-x634.google.com ([2a00:1450:4864:20::634]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nVKjQ-0000uS-T7 for pgsql-docs@postgresql.org; Fri, 18 Mar 2022 22:06:03 +0000 Received: by mail-ej1-x634.google.com with SMTP id p15so19517026ejc.7 for ; Fri, 18 Mar 2022 15:06:00 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=Pay9bXYrkcx4wnJJV4z6HsSaQwutSdR5FibNeLy+Io0=; b=fqYI3oc7uSFfGWrnCPKfLwhr1ZqwkyTfvNA211BTK7dNjlTUPlwRgf1HQl2NJKojEN fDUbAqJxdVfonJ0SirOV7QJCvFw+2viOtif/4ofukcEbFFhMXt5mWMdK35CFdA1EyBgz yVWvydWpkBir9WegYGpCo+DW9M0LP6+Nbo9Dkvk/TUET9/hAuaQufYNBn6aASsdmPZxq Qvg36rk5TA46UoDWKPyuJV00rv20zpYzTZcJEjYmvKLVDK0iZ+rkNzW2gyneeOsOCApS 7h5xAXnsF3ZljqPp2hx7+M+sRLlKcnk2Qls3YaDTo+TS6hLY/MDM0p0j1k77srCfK2Xx dxAA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=Pay9bXYrkcx4wnJJV4z6HsSaQwutSdR5FibNeLy+Io0=; b=cPONAjoJIOZIlnpN4MAMlsV1Dvtskmy3fZlU7ZHSIF/xvTpfW5MiZNsb8Q3TWYtcFw 9YIN0OIRkJ5EKHuOlD6pQfql9JApK81CJy3a+c9gNObwVb+IjgjoAc0gxrkOTImBIOyv LiVhKr1DxodXfF3kdnCxmKvMFF3cuxZBx7bdQcvFQJK8XseigWs2Qbr7zsJoA9wpokLW PV4WEaF82AU58Xm2Hmm1Bm+0CLhxyxX3pKZVLQzskfV+FnOnt21HUQCwUc20C5xViMQv kmQIh/JS/t7YLdDJ+usgSddc+1myidC/XgB4TqTZzsUvaH4sDJuNztXHLWesMQA/EPp2 WOzQ== X-Gm-Message-State: AOAM533hvBKsY41LbuYdfsy+khiXvGScaPOuBqtV37JPg0q4twF2RLlU 1tUwW074FePtkvugSdFSacjELkssBEWP0nyAXm0= X-Google-Smtp-Source: ABdhPJyKHeM4RsKF5y9dODgWstgN1wi+j+OdR8rDe6NmZA5lSdgGeNaEBQGnRcpTheR7llYP6W7YPU46sKm/5q/2DhU= X-Received: by 2002:a17:907:7244:b0:6db:1487:e75 with SMTP id ds4-20020a170907724400b006db14870e75mr10796680ejc.467.1647641159863; Fri, 18 Mar 2022 15:05:59 -0700 (PDT) MIME-Version: 1.0 References: <1228519.1647638908@sss.pgh.pa.us> In-Reply-To: <1228519.1647638908@sss.pgh.pa.us> From: Erwin Brandstetter Date: Fri, 18 Mar 2022 23:05:33 +0100 Message-ID: Subject: Re: count() counts ROW values that are NULL To: Tom Lane Cc: pgsql-docs Content-Type: multipart/alternative; boundary="0000000000000d11ee05da855ae7" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000d11ee05da855ae7 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Fri, 18 Mar 2022 at 22:28, Tom Lane wrote: > Erwin Brandstetter writes: > > > https://www.postgresql.org/docs/current/functions-aggregate.html#FUNCTION= S-AGGREGATE-TABLE > > The manual says: > >> count ( "any" ) =E2=86=92 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 t= o > 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 wan= t > 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() 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 --0000000000000d11ee05da855ae7 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Fri, 18 Mar 2022 at 22:28, Tom Lane &l= t;tgl@sss.pgh.pa.us> wrote:
=
Erwin Brandstetter = <brsaweda@gmail.= com> writes:
> https= ://www.postgresql.org/docs/current/functions-aggregate.html#FUNCTIONS-AGGRE= GATE-TABLE
> The manual says:
>> count ( "any" ) =E2=86=92 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 counte= d, anyway.

Well, there's nulls and nulls.=C2=A0 The SQL "IS NULL" constr= uct is fairly
badly designed IMO, because it considers both a plain NULL and a
row-of-all-NULL-fields to be "null".=C2=A0 count(), like just abo= ut 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 NU= LL,
not under all the other places that'd have to be annotated if we decide= to
annotate as you're suggesting.=C2=A0 (One example is that functions tha= t 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.=C2=A0 (Buttressing my point=
that IS NULL is not well thought out, the obvious "whatever IS NOT NUL= L"
doesn't work here, because it's not the inverse of "whatever I= S 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 th= e 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

Erwi= n

--0000000000000d11ee05da855ae7--