Error upgrading from 1.0.7 -> 1.0.8: cannot preload associations in subquery in query

[davidye]

Upgraded to 1.0.8 of dataloader and am getting this error now:

** (Dataloader.GetError) {%Ecto.SubQueryError{exception: %Ecto.QueryError{message: "cannot preload associations in subquery in query:\n\nfrom u0 in Server.Accounts.User,\n  join: u1 in Server.Accounts.UserProfile,\n  on: u1.user_id == u0.id,\n  where: u0.id == parent_as(:input).id,\n  limit: ^40,\n  offset: ^0,\n  preload: [profile: u1]\n"}, message: "the following exception happened when compiling a subquery.\n\n    ** (Ecto.QueryError) cannot preload associations in subquery in query:\n    \n    from u0 in Server.Accounts.User,\n      join: u1 in Server.Accounts.UserProfile,\n      on: u1.user_id == u0.id,\n      where: u0.id == parent_as(:input).id,\n      limit: ^40,\n      offset: ^0,\n      preload: [profile: u1]\n    \n\nThe subquery originated from the following query:\n\nfrom u0 in subquery(from u0 in Server.Accounts.User,\n  where: u0.id in ^[\"4cb62820-1d0e-4265-852a-e62086d98264\"],\n  distinct: true,\n  select: [:id]),\n  as: :input,\n  join_lateral: u1 in subquery(from u0 in Server.Accounts.User,\n  join: p1 in assoc(u0, :profile),\n  where: u0.id == parent_as(:input).id,\n  limit: ^40,\n  offset: ^0,\n  preload: [profile: p1]),\n  on: true,\n  select: u1\n"}, [{Ecto.Repo.Queryable, :execute, 4, [file: 'lib/ecto/repo/queryable.ex', line: 176]}, {Ecto.Repo.Queryable, :all, 3, [file: 'lib/ecto/repo/queryable.ex', line: 17]}, {Dataloader.Ecto, :run_batch, 6, [file: 'lib/dataloader/ecto.ex', line: 328]}, {Dataloader.Source.Dataloader.Ecto, :run_batch, 2, [file: 'lib/dataloader/ecto.ex', line: 643]}, {Dataloader.Source.Dataloader.Ecto, :"-run_batches/1-fun-1-", 2, [file: 'lib/dataloader/ecto.ex', line: 601]}, {Task.Supervised, :invoke_mfa, 2, [file: 'lib/task/supervised.ex', line: 90]}, {Task.Supervised, :reply, 5, [file: 'lib/task/supervised.ex', line: 35]}, {:proc_lib, :init_p_do_apply, 3, [file: 'proc_lib.erl', line: 226]}]}         (dataloader 1.0.8) lib/dataloader.ex:223: Dataloader.do_get/2

Didn't see anything mentioned in the changelog about this - happy to dig in and provide more information if helpful!

[benwilson512]

Hey @davidye can you provide more info? Can you show your loader file?

[davidye]

@benwilson512 I have schemas User and and UserProfile and querying for user, I preload UserProfile.

So in my resolver:

  def add_source(loader, session) do
    loader
    |> Dataloader.add_source(
      Accounts,
      Dataloader.Ecto.new(
        Repo,
        query: fn _query, params ->
          {_params_session, args} = Map.split(params, [:session])
          Accounts.query(args, session)
        end,
        default_params: %{session: session}
      )
    )
  end

  def user_profile(%User{id: user_id}, _args, %{context: %{loader: loader}}, fun \\ nil) do
    loader
    |> Dataloader.load(Accounts, {User, load: :profile}, user_id)
    |> on_load(fn loader ->
      loader
      |> Dataloader.get(Accounts, {User, load: :profile}, user_id)
      |> case do
        %User{} = user -> {:ok, user.profile}
        nil -> {:error, :not_found}
      end
      |> default_fun(fun)
    end)
  end

So dataloader calls Accounts.query(...) with [load: :profile] and that constructs a standard Ecto query of this form:

    from user in query,
      join: profile in assoc(user, :profile),
      preload: [profile: profile]

Also just noticed there's more error output - did something change with the way dataloader constructs ecto queries in 1.0.8?

11:11:29.346 [error] Task #PID<0.3055.0> started from #PID<0.3058.0> terminating
** (Ecto.SubQueryError) the following exception happened when compiling a subquery.

    ** (Ecto.QueryError) cannot preload associations in subquery in query:

    from u0 in Server.Accounts.User,
      join: u1 in Server.Accounts.UserProfile,
      on: u1.user_id == u0.id,
      where: u0.id == parent_as(:input).id,
      limit: ^40,
      offset: ^0,
      preload: [profile: u1]


The subquery originated from the following query:

from u0 in subquery(from u0 in Server.Accounts.User,
  where: u0.id in ^["18dd4bf9-92ff-4448-9631-b6031355ce32"],
  distinct: true,
  select: [:id]),
  as: :input,
  join_lateral: u1 in subquery(from u0 in Server.Accounts.User,
  join: p1 in assoc(u0, :profile),
  where: u0.id == parent_as(:input).id,
  limit: ^40,
  offset: ^0,
  preload: [profile: p1]),
  on: true,
  select: u1

EDIT: Looks like there's been some changes to preload behavior in lib/dataloader/ecto.ex which maybe related? @mbuhot

https://github.com/absinthe-graphql/dataloader/commits/6cd72f263563f49dc31cd351234241cce0c4a0ef/lib/dataloader/ecto.ex

[mbuhot]

@davidye yes the presence of the limit/offset in the query is causing it to use a new code path that runs your query as a lateral subquery.
It's intended to support getting the first N associated rows per parent record, but looks like I didn't test it with preloads, 🤦‍♂️.

To fix this specific issue we could update the code to not use the load_rows_lateral function when loading a batch of rows using the primary key. load_rows_lateral should only be used when loading an association by a foreign key.

I'll have to do some experimenting to figure out how to support preloads generally.
In some cases we could replace a preload in the subquery with a preload in the outer query, or a call to Repo.preload However that won't work for join preloads 🤔

[benwilson512]

Fixed in #98