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

[whollacsek]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• 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 #361

[d-e-h-i-o]

I'm experiencing the same problem with supabase-js 2.39.8 and [email protected].

[AlexisKenAlvarez]

I'm experiencing the same problem with supabase-js 2.39.8 and [email protected].

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 [email protected] and [email protected].

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 column code">[]'

[avallete]

having the same issues as what everyone else is mentioning. i am using [email protected] and [email protected].

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.

[whollacsek]

Hi is there any ETA regarding a solution? As this seem to be a regression it would be great if it is being prioritized.

I made some more tests by overriding the Args type of computed_review_score to see if it's enough to make it work but without success.

This is how I replaced the type:

import { MergeDeep } from "type-fest";
import { Database as DatabaseGenerated } from "./database.gen";

// Override the type from generated database types:
export type Database = MergeDeep<
  DatabaseGenerated,
  {
    event_ticketing: {
      Functions: {
        computed_review_score: {
          Args: {
            event: DatabaseGenerated["event_ticketing"]["Tables"]["events"]['Row'];
          };
        };
      };
    };
  }
>;

[avallete]

Hi is there any ETA regarding a solution? As this seem to be a regression it would be great if it is being prioritized.

I made some more tests by overriding the Args type of computed_review_score to see if it's enough to make it work but without success.

This is how I replaced the type:

import { MergeDeep } from "type-fest";
import { Database as DatabaseGenerated } from "./database.gen";

// Override the type from generated database types:
export type Database = MergeDeep<
  DatabaseGenerated,
  {
    event_ticketing: {
      Functions: {
        computed_review_score: {
          Args: {
            event: DatabaseGenerated["event_ticketing"]["Tables"]["events"]['Row'];
          };
        };
      };
    };
  }
>;

Hi @whollacsek,

This issue actually bundles multiple problems together, some of which have been addressed in the new release candidate: https://github.com/orgs/supabase/discussions/30324.

Could you please try this release candidate and let us know if the issue persists? If it does, please follow the bug report procedure mentioned in the discussion.

To move this forward, I need to reproduce your setup in a test case. I attempted to do so here: #559, but I wasn’t able to replicate the issue. To help us assist you better, could you open a new issue with the following information:

• The minimal SQL declaration that leads to the faulty types. That can be used on our side to reproduce similar schema.
• The exact query that fails to be properly inferred.

Thanks for your help!

[whollacsek]

@avallete Thanks for your reply I'll find some time to setup a test repo.

However in your example (#559) I see that the computed column (username_status) appears in the Row type which is not correct either and I've never seen that happen in my codebase.

[avallete]

@avallete Thanks for your reply I'll find some time to setup a test repo.

However in your example (#559) I see that the computed column (username_status) appears in the Row type which is not correct either and I've never seen that happen in my codebase.

Exactly, which is why I can't reproduce and I think something is on, with the SQL way the computed_column is declared in your codebase. You can reproduce my result by doing a:

npx supabase init
npx supabase start
npx supabase migration new init
<put-the-sql-i-provided-in-the-migration-in-559>
npx supabase db reset
<go-to http://127.0.0.1:54323/project/default/api?page=tables-intro to get the generated types>

But I need to be able to reproduce your problem the same way to be able to move this further.

[whollacsek]

I see, I'll do the repro on my side and report back