Excessive joins within nested polymorphic table

Author: Luke265Created Apr 27, 2026Updated Apr 27, 2026

Labels🐛 bug

Summary

In the reproduction provided below, the Task and TaskTimeline tables utilize polymorphic relationships (relational and unionMember modes, respectively).

When querying the TaskComment file, the generated SQL contains one identical File join for every Task type. For example, if there are 4 Task types, the generated query includes 4 identical joins to the same File table.

Steps to reproduce

https://github.com/benjie/ouch/pull/46 run query:

gql

query {
  allTasks {
    nodes {
      taskTimelinesByTaskId {
        nodes {
          item {
            ... on TaskComment {
              content
              taskCommentFilesByTaskCommentId {
                nodes {
                  fileByFileId {
                    id
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

Expected results

expected query for TaskComment:

sql

select
  __task_comment__."content" as "0",
  __task_comment__."id"::text as "1",
  array(
    select array[
      __task_comment_file__."file_id"::text,
      __file__."id"::text
    ]::text[]
    from "public"."task_comment_file" as __task_comment_file__
    left outer join "public"."file" as __file__
    on (
    /* WHERE becoming ON */ (
      __file__."id" = __task_comment_file__."file_id"
    ))
    where (
      __task_comment_file__."task_comment_id" = __task_comment__."id"
    )
    order by __task_comment_file__."id" asc
  )::text as "2"
from "public"."task_comment" as __task_comment__
where (
  __task_comment__."id" = $1::"int4"
);

Actual results

sql

select
  __task_comment__."content" as "0",
  __task_comment__."id"::text as "1",
  array(
    select array[
      __task_comment_file__."file_id"::text,
      __file__."id"::text,
      __file_2."id"::text,
      __file_3."id"::text,
      __file_4."id"::text
    ]::text[]
    from "public"."task_comment_file" as __task_comment_file__
    left outer join "public"."file" as __file__
    on (
    /* WHERE becoming ON */ (
      __file__."id" = __task_comment_file__."file_id"
    ))
    left outer join "public"."file" as __file_2
    on (
    /* WHERE becoming ON */ (
      __file_2."id" = __task_comment_file__."file_id"
    ))
    left outer join "public"."file" as __file_3
    on (
    /* WHERE becoming ON */ (
      __file_3."id" = __task_comment_file__."file_id"
    ))
    left outer join "public"."file" as __file_4
    on (
    /* WHERE becoming ON */ (
      __file_4."id" = __task_comment_file__."file_id"
    ))
    where (
      __task_comment_file__."task_comment_id" = __task_comment__."id"
    )
    order by __task_comment_file__."id" asc
  )::text as "2"
from "public"."task_comment" as __task_comment__
where (
  __task_comment__."id" = $1::"int4"
);

Source: graphile/crystal

View original on GitHub View discussion on GitHub