Error after upgrading to v2.33.1: SelectQueryError<"Referencing missing column `computed_review_score`">

[whollacsek]

Bug report

• [x] I confirm this is a bug with Supabase, not with my own application.
• [x] I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

Error after upgrading to 2.33.1 I'm getting this Typescript error: SelectQueryError<"Referencing missing column 'computed_review_score'">

computed_review_score is a function:

CREATE FUNCTION computed_review_score(event event_ticketing.events) RETURNS numeric
    LANGUAGE sql AS
$$
SELECT COALESCE(event.review_score,
                (SELECT ROUND(AVG((VALUE ->> 'score')::NUMERIC), 1) FROM JSONB_ARRAY_ELEMENTS(event.reviews)));
$$;

And this is the query:

supabaseClient
    .rpc('search_events', payload)
    .select(
      '*, computed_review_score'
    )

Also it is strange that the generated type for this function has unknown for the argument, it should be the table type:

...
    Functions: {
      computed_review_score: {
        Args: {
          event: unknown
        }
        Returns: number
      }
    }
...

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

1. Go to '…'
2. Click on '…'
3. Scroll down to '…'
4. See error

Expected behavior

A clear and concise description of what you expected to happen.

Screenshots

If applicable, add screenshots to help explain your problem.

System information

• OS: [e.g. macOS, Windows]
• Browser (if applies) [e.g. chrome, safari]
• Version of supabase-js: [e.g. 6.0.2]
• Version of Node.js: [e.g. 10.10.0]

Additional context

Add any other context about the problem here.

[lopezjurip]

Same issue here, broke my entire codebase.

I'm rolled back to an older version that supports computed columns:

"@supabase/postgrest-js": "=1.2.0"
"@supabase/supabase-js": "=2.4.0"

Sadly these version has issues with .maybeSingle(), see https://github.com/supabase/postgrest-js/issues/361

[d-e-h-i-o]

I'm experiencing the same problem with supabase-js 2.39.8 and postgrest-js@1.9.2.

[AlexisKenAlvarez]

I'm experiencing the same problem with supabase-js 2.39.8 and postgrest-js@1.9.2.

Im having the same issue! Same version notificationFrom: string & SelectQueryError<"Referencing missing column email">[];

But when I console log it, the object exists

[stefan-girlich]

Similar problem with supabase-js 2.42.4 and postgrest-js 1.15.2 and updated TypeScript types:

const { data, error } = await supabase.from('category')
    .select('*, parent_category(id, name, color)')

// parent_category: SelectQueryError<"Referencing missing column `color`">[]
// or whatever column is mentioned last in the query

parent_category is set up as a function according to PostgREST docs.

[zineanteoh]

having the same issues as what everyone else is mentioning. i am using postgrest-js@1.12.0 and supabase-js@2.39.7.

is Supabase not able to parse PostgREST syntax that contains computed columns/computed fields correctly in TypeScript?

SelectQueryError<"Referencing missing column `session_count`">

[JonNorman]

I am getting the same error, but when querying a table and joining to the same secondary table twice.

With the following tables:

create table country (
    id bigint generated by default as identity,
    code text not null
  );

create table trip (
    origin_id bigint not null,
    destination_id bigint not null,
    constraint trip_origin_id_fkey foreign key (origin_id) references country (id),
    constraint trip_destination_id_fkey foreign key (destination_id) references country (id),
  );

and access as such

function fetchTrip(tripId: number) {
  return supabase.from("trip").select(`
      id
      origin:origin_id ( code ),
      destination:destination_id ( code )
      `).eq(
      "id",
      tripId,
    ).single()
}

In the browser I can see that this returns, for a randomly selected trip.

{
    "id": 1,
    "origin": {
        "code": "AU",
    },
    "destination": {
        "code": "US"
    }
}

But if try to type the return type of fetchShipment then I get errors like Property 'code' is missing in type 'SelectQueryError<"Referencing missing columncode">[]'

[avallete]

having the same issues as what everyone else is mentioning. i am using postgrest-js@1.12.0 and supabase-js@2.39.7.

is Supabase not able to parse PostgREST syntax that contains computed columns/computed fields correctly in TypeScript?

SelectQueryError<"Referencing missing column `session_count`">

Yes I think that's correct. Looking at it I've made a MRE with this schema:

CREATE TABLE public.events (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    review_score NUMERIC,
    reviews JSONB
);

-- Create the computed_review_score function
CREATE FUNCTION computed_review_score(event public.events)
RETURNS NUMERIC
LANGUAGE SQL
AS $$
    SELECT COALESCE(
        event.review_score,
        (SELECT ROUND(AVG((VALUE ->> 'score')::NUMERIC), 1)
         FROM JSONB_ARRAY_ELEMENTS(event.reviews))
    );
$$;
INSERT INTO public.events (name, review_score, reviews)
VALUES
    ('Event 1', 4.5, '[{"score": 4}, {"score": 5}]'::JSONB),
    ('Event 2', NULL, '[{"score": 3}, {"score": 4}, {"score": 5}]'::JSONB);

Which produce this type after introspection:

export type Json =
  | string
  | number
  | boolean
  | null
  | { [key: string]: Json | undefined }
  | Json[]

export type Database = {
  public: {
    Tables: {
      events: {
        Row: {
          id: number
          name: string
          review_score: number | null
          reviews: Json | null
        }
        Insert: {
          id?: number
          name: string
          review_score?: number | null
          reviews?: Json | null
        }
        Update: {
          id?: number
          name?: string
          review_score?: number | null
          reviews?: Json | null
        }
        Relationships: []
      }
    }
    Views: {
      [_ in never]: never
    }
    Functions: {
      computed_review_score: {
        Args: {
          event: unknown
        }
        Returns: number
      }
    }
    Enums: {
      [_ in never]: never
    }
    CompositeTypes: {
      [_ in never]: never
    }
  }
}

type PublicSchema = Database[Extract<keyof Database, "public">]

And I think that's one of the root cause of the issue, as we can see there is now way to link the computed_review_score and events table at type level. So this will require introspection to give more details, as well as a rewrite of the result type logic to consider computed fields.