Typing issue when querying children

[StevenClontz]

Bug report

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

Describe the bug

I believe there's a typing issue when querying children of a row. I'm relatively new so this could possibly be user error, but I received no feedback via Discord nor StackOverflow.

To Reproduce

I have the following generated (from supabase-cli) type for my collections table:

export interface Database {
  public: {
    Tables: {
      collections: {
        Row: {
          created_at: string
          description: string | null
          id: string
          parent_id: string | null
          short_title: string | null
          title: string | null
          website: string | null
        }
        Insert: {
          created_at?: string
          description?: string | null
          id?: string
          parent_id?: string | null
          short_title?: string | null
          title?: string | null
          website?: string | null
        }
        Update: {
          created_at?: string
          description?: string | null
          id?: string
          parent_id?: string | null
          short_title?: string | null
          title?: string | null
          website?: string | null
        }
        Relationships: [
          {
            foreignKeyName: "collections_parent_id_fkey"
            columns: ["parent_id"]
            isOneToOne: false
            referencedRelation: "collections"
            referencedColumns: ["id"]
          }
        ]
      }

I'm grabbing a particular collection like so:

    const { data: collection } = await supabase
        .from('collections')
        .select(`*, collections(*)`)
        .eq(`id`, params.id)
        .single()

And this is the typing that's been inferred:

const collection: {
    created_at: string;
    description: string | null;
    id: string;
    parent_id: string | null;
    short_title: string | null;
    title: string | null;
    website: string | null;
    collections: {
        created_at: string;
        ... 5 more ...;
        website: string | null;
    } | null;
} | null

Expected behavior

In particular, collections is typed as an object, not an array of objects. However, if I coerce the typing to be any, collections is an array as expected, so this is just an issue with the generated type. Any clues on what I've done wrong or how to fix things?

System information

• OS: Windows
• Browser: Chrome
• Version of supabase-js: 2.38.5
• Version of Node.js: 10.1.0
• Dev environment: GitHub Codespaces

[bryanmylee]

I've noticed this as well.

From what I understand, on a self-referential relationship on a relation collection, when encountering a query like *, collection(*), PostgREST actually prioritizes the foreign relationship (i.e. from-many reference), since the local reference (i.e. to-one reference) can be specified with the column name e.g. *, parent_id(*).

The source for this is at select-query-parser.ts.