Comparing row type to null on PG12

[Landon Boyd]

Hi all,

I've run into this issue recently after upgrading to PG 12.7 from PG 10.16.  It seems that there is now a difference between a row type variable resulting from an empty "select into" versus one that is null.  Is this expected behaviour?

create schema mytest;

create table if not exists mytest."Foo" (

  a int

);

create or replace function

mytest."testFoo"()

returns setof text

security definer

language plpgsql

as $$

declare

  _row mytest."Foo";

begin

  return next is(_row, null, 'before select is');

  return next ok(_row is null, 'before select ok');

  select * 

  into _row

  from mytest."Foo"

  where false;

  return next is(_row, null, 'after select is');

  return next ok(_row is null, 'after select ok');

end;

$$;

select * from runtests('mytest', '^testFoo');

Output on PG12:

                 runtests                 

------------------------------------------

     # Subtest: mytest."testFoo"()

     ok 1 - before select is

     ok 2 - before select ok

     not ok 3 - after select is          +

     # Failed test 3: "after select is"  +

     #         have: NULL                +

     #         want: NULL

     ok 4 - after select ok

     1..4

     # Looks like you failed 1 tests of 4

 not ok 1 - mytest."testFoo"             +

 # Failed test 1: "mytest."testFoo""

 1..1

 # Looks like you failed 1 test of 1

(10 rows)

Output on PG12:

             runtests              

-----------------------------------

     # Subtest: mytest."testFoo"()

     ok 1 - before select is

     ok 2 - before select ok

     ok 3 - after select is

     ok 4 - after select ok

     1..4

 ok 1 - mytest."testFoo"

 1..1

(8 rows)

Thanks!

[David E. Wheeler]

On Aug 6, 2021, at 20:46, Landon Boyd <lan...@withreach.com> wrote:

> Output on PG12:
>
> runtests
> ------------------------------------------
> # Subtest: mytest."testFoo"()
> ok 1 - before select is
> ok 2 - before select ok
> not ok 3 - after select is +
> # Failed test 3: "after select is" +
> # have: NULL +
> # want: NULL
> ok 4 - after select ok
> 1..4
> # Looks like you failed 1 tests of 4
> not ok 1 - mytest."testFoo" +
> # Failed test 1: "mytest."testFoo""
> 1..1
> # Looks like you failed 1 test of 1
> (10 rows)

Well that’s interesting! I don’t have an answer, as it would be a change in behavior to Postgres itself in v11 or v12. I suggest asking on the Postgres mail list.

Best,

David