UDF call returns null

[imor]

Describe the bug
UDF call returns null when it shouldn't

To Reproduce
Add the following before the last rollback in function_calls.sql test file:

    rollback to savepoint a;

    create table account(
        id serial primary key,
        email varchar(255) not null,
        name text null
    );

    create function returns_account()
        returns account language sql stable
    as $$ select id, email, name from account; $$;

    insert into account(email, name)
    values
        ('aardvark@x.com', null),
        ('bat@x.com', 'bat'),
        ('cat@x.com', 'cat');

    -- comment on table account is e'@graphql({"totalCount": {"enabled": true}})';

    select jsonb_pretty(graphql.resolve($$
        query {
            returnsAccount {
                id
                email
                nodeId
                __typename
            }
        }
    $$));

    select jsonb_pretty(graphql.resolve($$
        query {
            returnsAccount {
                email
                nodeId
            }
        }
    $$));

Expected behavior
Call to returnsAccount should return an object.

Screenshots
N/A

Versions:

• PostgreSQL: 15
• pg_graphql version v0.4.1

Additional context
The generated sql returns no row when a column in the returned table is null. E.g. this sql returns nothing: select ra.* from returns_account() ra where ra is not null;. In the test above if the 'aardvark' row's name column is made non-null then the test passes. In the returned row, even a single null value in any of the columns fails this test.

[imor]

Options to fix this bug include:

1. Remove the where x is not null part from the generated sql. This will reintroduce another bug.
2. Test for nullability of the composite type in Rust. This changes the whole paradigm of retrieving values in a single SQL query and introduces a lot of complexity.

Option # 2 is too costly. For # 1 we can either leave the reintroduced bug as is or fix it as done in this comment. This last option has a caveat that it requires all the columns selectable, but we already require that. To me this last option sounds most appealing. @olirice what do you think?

[olirice]

ok, heres the state of nullability of record types in Postgres

select row(null, null) is null -- true (all elements are null)
select row(1, 1) is null -- false (all elements are not null)

-- now the weird part
select row(1, null) is null -- false
select row(1, null) is not null -- false

so if the record contains a null it is neither is null nor is not null.

To get the behavior we want we can adjust

where x is not null

to

where not (x is null)

to get

select not (row(null, null) is null); -- false
select not (row(1, 1) is null); -- true
select not (row(1, null) is null); -- true

which will work in all cases except if the returned record type exclusively contains null values

[vicary]

@olirice Hi, I am redirected here by Supabase Support via email. This issue should covers my use case, but it is not working.

I am exposing a subset of auth.users via a view and an UDF.

create or replace view "User" with (security_invoker) as select
  id,
  created_at as "createdAt",
  updated_at as "updatedAt",
  email,
  phone
from auth.users
where deleted_at is null;

create
or replace function "author" (rec "Post") returns "User" stable strict language sql security definer
set
  search_path = public as $$
  select 
    *
  from "User" u
  where u.id = $1."user_id";
$$;

The whole Post.author object becomes null if the value of phone is null, it's otherwise working as expected.

How should I apply the not (x is null) logic to my view?

[imor]

@vicary You shouldn't have to use the no (x is null) logic in your app anywhere as it is internal to pg_graphql. Also I don't think this is exactly your problem as you have a patched version in your project.

I tried your example with a slight modification and it works for me. The only change I made was I replaced rec "Post" with user_id uuid because I didn't know how Post is defined:

create
or replace function "author" (user_id uuid) returns "User" stable strict language sql security definer
set
  search_path = public as $$
  select 
    *
  from "User" u
  where u.id = user_id;
$$;

And then this query:

{
  author(user_id: "1b35eefe-e512-442c-89e2-54cdbda0d07b") {
    id
    createdAt
    updatedAt
    email
    phone
  }
}

returns:

{
  "data": {
    "author": {
      "id": "1b35eefe-e512-442c-89e2-54cdbda0d07b",
      "email": "xxxxxx@yyyyy.com",
      "phone": null,
      "createdAt": "2024-04-13T11:35:00.203706+00:00",
      "updatedAt": "2024-04-13T11:35:00.228252+00:00"
    }
  }
}

I suspect the where condition where u.id = $1."user_id"; in the author function is causing null to be returned in your case. Can you double check?

[vicary]

@imor Poll is only a test table with two columns.

id	user_id
98813159-4814-42fa-911d-5cc900bd80b8	dd5add8a-7dd2-4495-bc1a-a1dfe95ef23a

---

Replacing the args with UUID works, returning null at the last column phone.

create or replace function
-  "author" (rec "Poll")
+  "author" (user_id uuid) 
  returns "User" 
  stable strict language sql security definer
  set search_path = public as
$$
  select * from "User" u
-  where u.id = $1."user_id";
+  where u.id = $1;
$$;

SELECT author('dd5add8a-7dd2-4495-bc1a-a1dfe95ef23a');

-- (dd5add8a-7dd2-4495-bc1a-a1dfe95ef23a,"2024-02-03 06:49:33.810255+00","2024-02-03 06:49:33.818834+00",test@example.com,)

I don't know why pg_graphql doesn't expose Query.author for me here, can't test with this:

select graphql.resolve($$ { author(id: "dd5add8a-7dd2-4495-bc1a-a1dfe95ef23a") { id } } $$);

---

Completely removing the where condition still returns null.

create or replace function
  "author" (rec "Poll")
  returns "User" 
  stable strict language sql security definer
  set search_path = public as
$$
  select * from "User" u
-  where u.id = $1."user_id";
+  limit 1;
$$;

SELECT graphql.resolve($$ { pollCollection { edges { node { author { id } } } } } $$);

-- {"data":{"pollCollection":{"edges":[{"node":{"author":null}}]}}}