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 1nVM0Y-0000Ir-BG for pgsql-docs@arkaria.postgresql.org; Fri, 18 Mar 2022 23:27:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nVM0X-0006lm-85 for pgsql-docs@arkaria.postgresql.org; Fri, 18 Mar 2022 23:27:45 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nVM0W-0006ld-Qp for pgsql-docs@lists.postgresql.org; Fri, 18 Mar 2022 23:27:45 +0000 Received: from mail-ed1-x533.google.com ([2a00:1450:4864:20::533]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nVM0S-00089R-Bj for pgsql-docs@postgresql.org; Fri, 18 Mar 2022 23:27:43 +0000 Received: by mail-ed1-x533.google.com with SMTP id w4so11888322edc.7 for ; Fri, 18 Mar 2022 16:27:40 -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=crTEgCXUBbAvpjn/VCWvozVYERvCMekyvmR2a6RQDqQ=; b=RmJinLoy67LDLUl/ZXF9iYSnNpIVE88euCFFfQ3vUZxl8XrIm+J22p9ISN3oat6vGw J+6EW4sIVCaEZ/J/XbIdZZCgZbKML5KgM8520XSnBj9wprk01ASFS9/wh1Bq3WvKC0S4 RMvqZEhU5+HiazsFKrRsxZtz9aq0qS45xwJyOOnh4pZeqShWE4ZHD6+n4V6lM9KbOMdZ zQwqfowHeB8XDQ1MdvUgv/E4AHbHGvYwr4U/wUS6B8vdX5LuwI5V3672AvzGUagldzNM aWYnjXmklibaJWnKhcVj4QUGlDjGVuHamo3/CSQhCIdZF9wsULm/+1z5B/lNcUdVRvVW p/aA== 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=crTEgCXUBbAvpjn/VCWvozVYERvCMekyvmR2a6RQDqQ=; b=OfERvjezoRDz9Q+GZ7k1DadFfONVkbE9DzRJriAxt/bDrlqKX1FqXTkNfSFrlvyQUw Ha4/IDdBsSWrDRrTQJ/uwRHqnMrcqqoZm4HZgeuaVhJgTnrv4tRkFoqDZv11VFhbbHTm kaq2YbeHcC9BNv57pF0ctNvWBsCVrLgz+Wqhr8Sg+nVS1ep83vLJfP9WEDc3Tq0RGGBx m2ZrAP9vB30w6PB6b2McqY4ygB0yqIXHu+rJX8UCVkBGCKzdqZI9ikE+TLsrulKtVHCA HOAiDVMDUYhIbw8NQ0QF+SxyOHQbZDjaXOcQfXptk1bIMB9fPFgDxymA6utmXKk8RGYi zKUQ== X-Gm-Message-State: AOAM533VlfzOz29QlBUqhW01bTErO5TUxQS8g8f8373/vLNHh9ad2eVd 9J/6VtV4G5CyGqWcKvN7MbNVeUvchCpbbGhd16g= X-Google-Smtp-Source: ABdhPJwS5ZHPfCViOLQFbNNPdMnHcLi1YCqhsLHIZaWmZjKaaxbE2Gudx4JrcRp77aRDtrDOa9eY/vkbXs2OPcTD2rQ= X-Received: by 2002:a05:6402:1385:b0:413:2bc6:4400 with SMTP id b5-20020a056402138500b004132bc64400mr12123532edv.94.1647646058185; Fri, 18 Mar 2022 16:27:38 -0700 (PDT) MIME-Version: 1.0 References: <1228519.1647638908@sss.pgh.pa.us> In-Reply-To: From: "David G. Johnston" Date: Fri, 18 Mar 2022 16:27:20 -0700 Message-ID: Subject: Re: count() counts ROW values that are NULL To: Erwin Brandstetter Cc: Tom Lane , pgsql-docs Content-Type: multipart/alternative; boundary="000000000000038a4105da867ece" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000038a4105da867ece Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Fri, Mar 18, 2022 at 3:06 PM Erwin Brandstetter wrote: > > On Fri, 18 Mar 2022 at 22:28, Tom Lane wrote: > >> Erwin Brandstetter writes: >> > >> https://www.postgresql.org/docs/current/functions-aggregate.html#FUNCTIO= NS-AGGREGATE-TABLE >> > The manual says: >> >> count ( "any" ) =E2=86=92 bigint >> >> Computes the number of input rows in which the input value is not nul= l. >> >> > 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 everythin= g >> 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 NUL= L, >> 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() is among the most frequently used functions, and > hardly any user reading the manual will be aware of the implications. May= be > 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. --000000000000038a4105da867ece Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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-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.)

=C2=A0
But count(<e= xpression>) is among the most frequently used functions, and hardly any = user reading the manual will be aware of the implications. Maybe just:
<= /div>

I'm with Tom on th= is.=C2=A0 The behavior exhibited is the expected behavior.=C2=A0 I haven= 9;t looked, but if anything I would make the desired point in "composi= te IS NULL" that this special (ROW(null) IS NULL -> true) interpret= ation of NULL is limited to this SQL Standard mandated operator and that wh= en speaking generally about a composite being null throughout the documenta= tion it is done in a scalar sense (I don't know how best to word this b= ut select null::rel yields "" while select row(null)::rel yields = "()" on printout (assuming rel has a single column)).
David J.

--000000000000038a4105da867ece--