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 1lAddf-00077s-Io for pgsql-bugs@arkaria.postgresql.org; Fri, 12 Feb 2021 18:57:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lAdde-0006FS-4H for pgsql-bugs@arkaria.postgresql.org; Fri, 12 Feb 2021 18:57:58 +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 1lAddd-0006FL-UB for pgsql-bugs@lists.postgresql.org; Fri, 12 Feb 2021 18:57:57 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lAddX-0000RM-4I for pgsql-bugs@lists.postgresql.org; Fri, 12 Feb 2021 18:57:57 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 11CIvmMh1561482; Fri, 12 Feb 2021 13:57:48 -0500 From: Tom Lane To: =?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16862: Unexpected result of checking for null "IS NOT NULL" in function In-reply-to: References: <16862-c10a7401d2973a9b@postgresql.org> <1552247.1613143392@sss.pgh.pa.us> Comments: In-reply-to =?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= message dated "Fri, 12 Feb 2021 23:32:55 +0500" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1561480.1613156268.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Fri, 12 Feb 2021 13:57:48 -0500 Message-ID: <1561481.1613156268@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk =3D?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?=3D writes: > CREATE TABLE "public"."test" ( > "id" Bigint NOT NULL, > "name" Character Varying NOT NULL, > PRIMARY KEY ( "id" ) ); > -- ------------------------------------------------------------- > INSERT INTO "public"."test" ( "id", "name") > VALUES ( 1, 'sdsdsdsdsd' ); > CREATE OR REPLACE VIEW "public"."vtest" AS SELECT test.id, > test.name > FROM test;; > -- CREATE FUNCTION "class_is_actual3( int8, timestamp, timestamp )" > CREATE OR REPLACE FUNCTION test_is_null_id(iid bigint, OUT tr BOOLEAN, O= UT > vr BOOLEAN) > RETURNS RECORD > LANGUAGE plpgsql > AS $function$ > DECLARE > t "test"%ROWTYPE; > v "vtest"%ROWTYPE; > BEGIN > SELECT * INTO t FROM ONLY test WHERE id =3D iid; > SELECT * INTO v FROM ONLY vtest WHERE id =3D iid; > IF t IS NOT NULL THEN > tr =3D true; > ELSE > tr =3D false; > END IF; > IF v IS NOT NULL THEN > vr =3D true; > ELSE > vr =3D false; > END IF; > END; > $function$; > -- ------------------------------------------------------------- > SELECT * FROM test_is_null_id(1); > ALTER TABLE "public"."test" ADD COLUMN "New_olumn" Bigint[] NULL; > SELECT * FROM test_is_null_id(1); OK, I appreciate the test case, but as far as I can see the database is doing exactly what it's supposed to. After the ALTER ADD COLUMN we have # table test; id | name | New_olumn = ----+------------+----------- 1 | sdsdsdsdsd | = (1 row) # table vtest; id | name = ----+------------ 1 | sdsdsdsdsd (1 row) # SELECT * FROM test_is_null_id(1); tr | vr = ----+---- f | t (1 row) That looks fine to me: "test" now contains a column that is null, so it doesn't pass the IS NOT NULL test. On the other hand, "vtest" doesn't contain that column; all its columns are still non null, so it does pass the IS NOT NULL test. Note that "foo IS NOT NULL" is not the same as "NOT (foo IS NULL)" when foo is of composite type. I agree that's confusing, but it's required by the SQL spec. See https://www.postgresql.org/docs/12/functions-comparison.html regards, tom lane