Invalid query generated when selecting after updating using or operator

[kinolaev]

Environment

• PostgreSQL version: public.ecr.aws/supabase/postgres:15.6.1.113
• PostgREST version: public.ecr.aws/supabase/postgrest:v12.2.3

Description of issue

I faced a problem when trying to update a timestamp column and return the updated value.

Here is the table structure:

create table jobs (
    id uuid primary key default gen_random_uuid(),
    started_at timestamp with time zone
)

I want to update and return a job if it has not been started yet or if it was started more then one minute ago.

But when I make the following query using supabase:

const min_start_at = new Date(Date.now() - 60000).toISOString()
supabase.from("jobs").update({ started_at: "now()" })
  .eq("id", id).or(`started_at.is.null,started_at.lt.${min_start_at}`)
  .select("id,started_at").maybeSingle()

PostgREST generates a query that doesn't returns the updated row:

WITH pgrst_source AS (
    UPDATE "public"."jobs"
    SET "started_at" = "pgrst_body"."started_at"
    FROM
        (SELECT $1 AS json_data) pgrst_payload,
        LATERAL (SELECT "started_at" FROM json_to_record(pgrst_payload.json_data) AS _("started_at" timestamp with time zone) ) pgrst_body
    WHERE  "public"."jobs"."id" = $2
        AND  ( "public"."jobs"."started_at" IS NULL OR  "public"."jobs"."started_at" < $3)
    RETURNING "public"."jobs"."id", "public"."jobs"."started_at"
)
SELECT
    '' AS total_result_set, pg_catalog.count(_postgrest_t) AS page_total,
    array[]::text[] AS header, coalesce(json_agg(_postgrest_t)->0, 'null') AS body,
    nullif(current_setting('response.headers', true), '') AS response_headers,
    nullif(current_setting('response.status', true), '') AS response_status,
    '' AS response_inserted
FROM (
    SELECT "jobs"."started_at"
    FROM "pgrst_source" AS "jobs"
    WHERE  ( "jobs"."started_at" IS NULL OR  "jobs"."started_at" < $4)
);

The problem is the last WHERE clause because at this stage started_at has already been set to now() which can't be less then a moment in the past.

Furthermore, if you try to remove started_at from select:

const min_start_at = new Date(Date.now() - 60000).toISOString()
supabase.from("jobs").update({ started_at: "now()" })
  .eq("id", id).or(`started_at.is.null,started_at.lt.${min_start_at}`)
  .select("id").maybeSingle()

you'll recieve an error because the following query is invalid:

WITH pgrst_source AS (
    UPDATE "public"."jobs"
    SET "started_at" = "pgrst_body"."started_at"
    FROM (
        SELECT $1 AS json_data) pgrst_payload,
        LATERAL (SELECT "started_at" FROM json_to_record(pgrst_payload.json_data) AS _("started_at" timestamp with time zone) ) pgrst_body
    WHERE  "public"."jobs"."id" = $2
        AND  ( "public"."jobs"."started_at" IS NULL OR  "public"."jobs"."started_at" < $3)
    RETURNING "public"."jobs"."id"
)
SELECT
    '' AS total_result_set, pg_catalog.count(_postgrest_t) AS page_total,
    array[]::text[] AS header, coalesce(json_agg(_postgrest_t)->0, 'null') AS body,
    nullif(current_setting('response.headers', true), '') AS response_headers,
    nullif(current_setting('response.status', true), '') AS response_status,
    '' AS response_inserted
FROM (
    SELECT "jobs"."id"
    FROM "pgrst_source" AS "jobs"
    WHERE  ( "jobs"."started_at" IS NULL OR  "jobs"."started_at" < $4)
);

Notice the pgrst_source here, it contains only id but the WHERE clause still references started_at.

Looks like the problem is related to the or operator because when I use is.null:

supabase.from("jobs").update({ started_at: "now()" })
  .eq("id", id).is("started_at", null)
  .select("id,started_at").maybeSingle()

or lt. separately:

const min_start_at = new Date(Date.now() - 60000).toISOString()
supabase.from("jobs").update({ started_at: "now()" })
  .eq("id", id).lt("started_at", min_start_at)
  .select("id,started_at").maybeSingle()

there is no WHERE clause in the generated by PostgREST query:

WITH pgrst_source AS (
    UPDATE "public"."jobs"
    SET "started_at" = "pgrst_body"."started_at"
    FROM
        (SELECT $1 AS json_data) pgrst_payload,
        LATERAL (SELECT "started_at" FROM json_to_record(pgrst_payload.json_data) AS _("started_at" timestamp with time zone) ) pgrst_body
    WHERE  "public"."jobs"."id" = $2
        AND  "public"."jobs"."started_at" < $3
    RETURNING "public"."jobs"."id", "public"."jobs"."started_at"
)
SELECT
    '' AS total_result_set, pg_catalog.count(_postgrest_t) AS page_total,
    array[]::text[] AS header, coalesce(json_agg(_postgrest_t)->0, 'null') AS body,
    nullif(current_setting('response.headers', true), '') AS response_headers,
    nullif(current_setting('response.status', true), '') AS response_status,
    '' AS response_inserted
FROM (
    SELECT "jobs"."started_at"
    FROM "pgrst_source" AS "jobs"
);

This is expected behavior and or operator should not add WHERE clause as I understand.

[laurenceisla]

To give a similar example with our tests:

curl -X PATCH 'localhost:3000/entities?select=id,name&or=(name.is.null,name.like.*test*)' \
  -H 'Content-Type: application/json' \
  -H 'Prefer: return=representation' \
  -d '{"name": "New name"}'

Got:

[]

Expected:

[{"id":4,"name":"New name"}]

[laurenceisla]

To add another issue related to this. If we do not select the column used for filters (the name in the previous example), we'd get a Postgres error like this one:

curl -X PATCH 'localhost:3000/entities?select=id&or=(name.is.null,name.like.*test*)' \
  -H 'Content-Type: application/json' \
  -H 'Prefer: return=representation' \
  -d '{"name": "New name"}'

{"code":"42703","details":null,"hint":null,"message":"column entities.name does not exist"}