Ash Framework•4w ago

unselected attribute used in relationship filter + aggregate results in column does not exist error

I'm not sure at what level of the stack this bug is happening, but I suspect it's at the ash_sql level. I have an ltree based resource. I decided to use relationships/calculations to abstract some of the ltree details away, so I have a children relationship that looks like this (level is the ltree column, and nlevel is a calculation that is just nlevel(level)):

has_many :children, MyResource do
  public? true
  no_attributes? true

  # use ltree <@ operator to get descendants, where nlevel is exactly 1 more
  # than the parent's nlevel (that is, direct children)
  filter expr(
           fragment(
             """
             ? <@ ? AND ? = ? + 1
             """,
             level,
             parent(level),
             nlevel,
             parent(nlevel)
           )
         )
end

has_many :children, MyResource do
  public? true
  no_attributes? true

  # use ltree <@ operator to get descendants, where nlevel is exactly 1 more
  # than the parent's nlevel (that is, direct children)
  filter expr(
           fragment(
             """
             ? <@ ? AND ? = ? + 1
             """,
             level,
             parent(level),
             nlevel,
             parent(nlevel)
           )
         )
end

I then have this aggregate:

count :count_children,
      :children do
  public? true
end

count :count_children,
      :children do
  public? true
end

Using ash_graphql, I grab some resources like this (note I do not select level):

query GetResources($ids: [ID!]) {
  myResources(filter: {id: {in: $ids}}) {
    id
    countChildren
  }
}

query GetResources($ids: [ID!]) {
  myResources(filter: {id: {in: $ids}}) {
    id
    countChildren
  }
}

This results in the following error from postgres: ** (Postgrex.Error) ERROR 42703 (undefined_column) column s0.level does not exist It seems like at some point, ash loses track of the fact it needs to include the level field in the SQL select so that it can correlate the relationship to it, even though it doesn't need to be included in the gql response.

Solution:

There is an open bug about this in ash_postgres

Jump to solution

8 Replies

Jesse WilliamsOP•4w ago

This is what the generated SQL looks like (formatted), for reference:

SELECT s0."id",
    s0."name",
    coalesce(s1."count_children"::bigint, $1::bigint)::bigint
FROM (
        SELECT sg0."id" AS "id",
            sg0."name" AS "name"
        FROM "my_resource" AS sg0
        WHERE (sg0."id"::uuid = ANY($2::uuid []))
        ORDER BY sg0."id"
        LIMIT $3
    ) AS s0
    LEFT OUTER JOIN LATERAL (
        SELECT coalesce(count(*), $4::bigint)::bigint AS "count_children"
        FROM "public"."my_resource" AS sg0
        WHERE (
                (
                    sg0."level"::ltree <@ s0."level"::ltree
                    AND (nlevel(sg0."level"::ltree))::bigint = (nlevel(s0."level"::ltree))::bigint + 1
                )
            )
    ) AS s1 ON TRUE

SELECT s0."id",
    s0."name",
    coalesce(s1."count_children"::bigint, $1::bigint)::bigint
FROM (
        SELECT sg0."id" AS "id",
            sg0."name" AS "name"
        FROM "my_resource" AS sg0
        WHERE (sg0."id"::uuid = ANY($2::uuid []))
        ORDER BY sg0."id"
        LIMIT $3
    ) AS s0
    LEFT OUTER JOIN LATERAL (
        SELECT coalesce(count(*), $4::bigint)::bigint AS "count_children"
        FROM "public"."my_resource" AS sg0
        WHERE (
                (
                    sg0."level"::ltree <@ s0."level"::ltree
                    AND (nlevel(sg0."level"::ltree))::bigint = (nlevel(s0."level"::ltree))::bigint + 1
                )
            )
    ) AS s1 ON TRUE

Solution

ZachDaniel•4w ago

There is an open bug about this in ash_postgres

ZachDaniel•4w ago

Hopefully will be fixed soon

Jesse WilliamsOP•4w ago

Oh nice! Luckily for the time being I can make do with just having the graphql select level. Glad to hear it's at least known ❤️ thanks!

Jesse WilliamsOP•4w ago

ah looks like this one? https://github.com/ash-project/ash_postgres/pull/644

GitHub

Add failing test for aggregate with parent() + select() + limit() b...

Reproduces a bug where combining select() + limit() with an aggregate that uses parent() in its filter causes SQL error. This bug was found in ash_graphql where GraphQL list queries would automatic...

ZachDaniel•4w ago

Yep!

barnabasj•4w ago

I'll try to get more work done on the ash_sql pr this week, I'm at codebeam though, so no promises 🙂

ZachDaniel•4w ago

I actually have some work going on it its very hard lol

Gaming

Programming

unselected attribute used in relationship filter + aggregate results in column does not exist error

Did you find this page helpful?