Re: BUG #16862: Unexpected result of checking for null "IS NOT NULL" in function

On Fri, Feb 12, 2021 at 3:05 AM PG Bug reporting form <
noreply(at)postgresql(dot)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;"

https://www.postgresql.org/docs/current/functions-comparison.html

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.