GraphQL list queries fail to load aggregates with parent filter references

[jechol]

Code of Conduct

• I agree to follow this project's Code of Conduct

AI Policy

• I agree to follow this project's AI Policy, or I agree that AI was not used while creating this issue.

Versions

• Elixir: 1.18.3
• Ash: 3.7.6
• AshGraphql: 1.8.3
• AshPostgres: 2.6.23
• AshSql: 0.3.9

Operating system

macOS 15.6.1

Current Behavior

GraphQL list queries fail when loading aggregates that reference parent fields in their filter expressions.

Error: ERROR 42703 (undefined_column) column s0.last_read_msg_id does not exist

The generated SQL's subquery s0 doesn't include the field referenced in the aggregate filter.

Important: This ONLY affects aggregates in list queries. Everything else works:

• Resource-level queries (read/read_one) ✅
• GraphQL get queries ✅
• has_many relationships with identical filters ✅

Reproduction

Full reproduction repository: https://github.com/jechol/ash-graphql-aggregate-bug

Expected: 1 failure out of 8 tests (GraphQL list query with aggregate)

The repository includes:

• Minimal schema (Chat, Message)
• Aggregate using parent(last_read_msg_id) in filter
• has_many with identical filter (works fine)
• Tests comparing all scenarios

Expected Behavior

GraphQL list queries should successfully load aggregates that reference parent fields.

[barnabasJ]

I'll take a look later this week

[zachdaniel]

I hadn't looked yet, but note in the linked PR that I'm fairly certain this issue is in ash_sql, not ash_graphl.

[zachdaniel]

This could also be a similar/related issue: ash-project/ash#2408

[zachdaniel]

Actually @barnabasJ @jechol I think the problem is here:

  def return_query(query, resource) do
    query =
      AshSql.Bindings.default_bindings(query, resource, query.__ash_bindings__.sql_behaviour)

    with_sort_applied =
      if query.__ash_bindings__[:sort_applied?] do
        {:ok, query}
      else
        AshSql.Sort.apply_sort(query, query.__ash_bindings__[:sort], resource)
      end

    case with_sort_applied do
      {:error, error} ->
        {:error, error}

      {:ok, query} ->
        query =
          if query.__ash_bindings__[:__order__?] && query.windows[:order] do
            if query.distinct do
              {calculations_require_rewrite, aggregates_require_rewrite, query} =
                rewrite_nested_selects(query)

              query_with_order =
                from(row in query, select_merge: %{__order__: over(row_number(), :order)})

              query_without_limit_and_offset =
                query_with_order
                |> Ecto.Query.exclude(:limit)
                |> Ecto.Query.exclude(:offset)

              from(row in subquery(query_without_limit_and_offset),
                as: ^0,
                select: row,
                order_by: row.__order__
              )
              |> Map.put(:limit, query.limit)
              |> Map.put(:offset, query.offset)
              |> AshSql.Bindings.default_bindings(
                resource,
                query.__ash_bindings__.sql_behaviour,
                query.__ash_bindings__.context
              )
              |> Map.update!(:__ash_bindings__, fn bindings ->
                Map.merge(
                  bindings,
                  %{
                    calculations_require_rewrite: calculations_require_rewrite,
                    aggregates_require_rewrite: aggregates_require_rewrite,
                    load_aggregates: query.__ash_bindings__[:load_aggregates]
                  },
                  fn _, v1, v2 -> Map.merge(v1, v2) end
                )
              end)
            else
              order_by = %{query.windows[:order] | expr: query.windows[:order].expr[:order_by]}

              %{
                query
                | windows: Keyword.delete(query.windows, :order),
                  order_bys: [order_by]
              }
            end
          else
            %{query | windows: Keyword.delete(query.windows, :order)}
          end

        combination_fieldset =
          query.__ash_bindings__.context[:data_layer][:combination_fieldset]

        query =
          if combination_fieldset do
            fields =
              resource
              |> Ash.Resource.Info.fields([:attributes, :calculations, :aggregates])
              |> Enum.map(& &1.name)

            to_add_to_calcs = combination_fieldset -- fields

            add_combination_calcs(query, to_add_to_calcs)
          else
            query
          end

        {:ok, query}
    end
    |> case do
      {:ok, query} ->
        load_aggs = query.__ash_bindings__[:load_aggregates] || []

        if Enum.empty?(load_aggs) do
          {:ok, query}
        else
          AshSql.Aggregate.add_aggregates(
            query,
            load_aggs,
            resource,
            true,
            query.__ash_bindings__.root_binding
          )
        end

      {:error, error} ->
        {:error, error}
    end

in ash_sql. Not 100% sure yet but that would be where I'd look here.

[zachdaniel]

@jechol its unlikely, but I've just fixed a separate bug in main of ash, it would be worth trying it out to see if that helps. Otherwise I will leave this one to @barnabasJ for now and check on it later.

[jechol]

I tested with ash main HEAD at 2a8f6088d9a39647178f921d7eb0f7ea4cde8f0d, but the issue still occurs. I've added a failing reproduction test to ash_postgres: ash-project/ash_postgres#644