Thread: BUG #16862: Unexpected result of checking for null "IS NOT NULL" in function
BUG #16862: Unexpected result of checking for null "IS NOT NULL" in function
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 16862 Logged by: Дмитрий Иванов Email address: firstdismay@gmail.com PostgreSQL version: 12.5 Operating system: Windows 10 Description: Good day! 1. Create TABLE: "bpd"."group" AND view 2. Create FUNCTION: DECLARE: egroupV "bpd"."vgroup"%ROWTYPE; view DECLARE: egroupT "bpd"."group"%ROWTYPE; table 3. Retrieving data checking the result in function: SELECT * INTO egroupV FROM "bpd"."vgroup" WHERE id = iid; SELECT * INTO egroupT FROM "bpd"."group" WHERE id = iid; IF (egroupV IS NOT NULL) THEN => state OK IF (egroupT IS NOT NULL) THEN => state OK IF NOT(egroupV IS NULL) THEN => state OK IF NOT(egroupT IS NULL) THEN => state OK 4. ALTER TABLE "bpd"."group" ADD COLUMN 5. Retrieving data checking the result in function: SELECT * INTO egroup FROM "bpd"."vgroup" WHERE id = iid; SELECT * INTO egroupT FROM "bpd"."group" WHERE id = iid; IF (egroupV IS NOT NULL) THEN => state OK IF (egroupT IS NOT NULL) THEN => state NOT WORK??????????? IF NOT(egroupV IS NULL) THEN => state OK IF NOT(egroupT IS NULL) THEN => state OK
Re: BUG #16862: Unexpected result of checking for null "IS NOT NULL" in function
From
Tom Lane
Date:
PG Bug reporting form <noreply@postgresql.org> writes: > 1. Create TABLE: "bpd"."group" AND view > 2. Create FUNCTION: > DECLARE: egroupV "bpd"."vgroup"%ROWTYPE; view > DECLARE: egroupT "bpd"."group"%ROWTYPE; table > 3. Retrieving data checking the result in function: > SELECT * INTO egroupV FROM "bpd"."vgroup" WHERE id = iid; > SELECT * INTO egroupT FROM "bpd"."group" WHERE id = iid; > IF (egroupV IS NOT NULL) THEN => state OK > IF (egroupT IS NOT NULL) THEN => state OK > IF NOT(egroupV IS NULL) THEN => state OK > IF NOT(egroupT IS NULL) THEN => state OK > 4. ALTER TABLE "bpd"."group" ADD COLUMN > 5. Retrieving data checking the result in function: > SELECT * INTO egroup FROM "bpd"."vgroup" WHERE id = iid; > SELECT * INTO egroupT FROM "bpd"."group" WHERE id = iid; > IF (egroupV IS NOT NULL) THEN => state OK > IF (egroupT IS NOT NULL) THEN => state NOT WORK??????????? > IF NOT(egroupV IS NULL) THEN => state OK > IF NOT(egroupT IS NULL) THEN => state OK It's impossible to make any sense of this report. What do you think "not work" means? You have not provided enough detail to let someone else reproduce whatever you're seeing, either. Please read https://wiki.postgresql.org/wiki/Guide_to_reporting_problems regards, tom lane
Re: BUG #16862: Unexpected result of checking for null "IS NOT NULL" in function
From
Tom Lane
Date:
=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay@gmail.com> writes: > Does not work means does not produce the expected result. It seemed > obvious to me. If it were obvious I wouldn't be asking you for more information. You've left out a lot, like the table details and any sample data that might be necessary to reproduce the problem. The link I sent you offers some suggestions about how to create a self-contained problem report. Ideally, provide a SQL script that anyone can run in an empty database to see the misbehavior. Also, please keep the mailing list cc'd. I'm not the only developer who might take an interest in this problem, once we understand what it is. regards, tom lane
Re: BUG #16862: Unexpected result of checking for null "IS NOT NULL" in function
From
"David G. Johnston"
Date:
On Fri, Feb 12, 2021 at 3:05 AM PG Bug reporting form <noreply@postgresql.org> wrote:
1. Create TABLE: "bpd"."group" AND view
4. ALTER TABLE "bpd"."group" ADD COLUMN
IF (egroupV IS NOT NULL) THEN => state OK
IF (egroupT IS NOT NULL) THEN => state NOT WORK???????????
I agree with Tom that this report needs more commentary, not just code and "ok/not work". But I believe there are two behaviors in PostgreSQL that you are unaware of that lead to your confusion.
First:
"If the expression is row-valued, then IS NULL is true when the row expression itself is null or when all the row's fields are null, while IS NOT NULL is true when the row expression itself is non-null and all the row's fields are non-null. Because of this behavior, IS NULL and IS NOT NULL do not always return inverse results for row-valued expressions;"
Second:
While you've altered the table bpd.group, adding a column, you didn't do the same to the view and so that view does not include that column.
Those two things combined, specifically the newly added column being left null causing the is not null check to return false, likely explain why the behavior you observe is not a bug.
David J.
Re: BUG #16862: Unexpected result of checking for null "IS NOT NULL" in function
From
Tom Lane
Date:
=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay@gmail.com> 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, OUT > vr BOOLEAN) > RETURNS RECORD > LANGUAGE plpgsql > AS $function$ > DECLARE > t "test"%ROWTYPE; > v "vtest"%ROWTYPE; > BEGIN > SELECT * INTO t FROM ONLY test WHERE id = iid; > SELECT * INTO v FROM ONLY vtest WHERE id = iid; > IF t IS NOT NULL THEN > tr = true; > ELSE > tr = false; > END IF; > IF v IS NOT NULL THEN > vr = true; > ELSE > vr = 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