Ecto not generating proper relational reference

[tjchambers]

Elixir version

Erlang/OTP 25 [erts-13.1] [source] [64-bit] [smp:32:32] [ds:32:32:10] [async-threads:1] [jit:ns] Elixir 1.14.3 (compiled with Erlang/OTP 23)

Database and Version

MySQL 8.0.30

Ecto Versions

ecto 3.9.4

Database Adapter and Versions (postgrex, myxql, etc)

myxql 0.6.3

Current behavior

There are two Queries that are composed that refer to the same table twice.

  @spec role_holders(%Sct.Internal.Product{}) :: Ecto.Query.t()
  @doc """
  Returns the current role holders for a given role and and the tenant the user is logged into.
  """
  def role_holders(%Sct.Internal.Product{} = p) do
    tenant_id = Sct.RegistryFetch.client().tenant_id

    from(
      p in Sct.Network.Person,
      join: pr in Sct.Internal.PersonRole,
      on: pr.person_id == p.id,
      where: p.active == true,
      where: p.tenant_id == ^tenant_id,
      join: r in Sct.Misc.Role,
      on:
        is_nil(r.authorizable_type) and
          is_nil(r.authorizable_id) and
          r.name == ^p.acl and
          r.id == pr.role_id,
      distinct: true
    )
  end

    defp current(
         %Sct.Internal.Product{} = p,
         %Sct.Align.StrategicCollaboration{} = strategic_collaboration
       ) do
    Sct.Internal.Product.role_holders(p)
    |> join(:inner, [ps], s in Sct.Internal.PersonRole, on: s.person_id == ps.id)
    |> join(:inner, [ps, s], rr in Sct.Misc.Role,
      on:
        s.role_id == rr.id and  <--- line generating incorrect relation ************************
          rr.authorizable_type == "Assessment" and
          rr.authorizable_id == ^strategic_collaboration.id and
          rr.name == ^p.acl
    )
    |> select([ps, s, rr], ps.id)
    |> Repo.all()
    |> dbg()
  end

The resulting query that is constructed is logged as:

 Sct.Internal.Product.role_holders(p) #=> #Ecto.Query<from p0 in Sct.Network.Person, join: p1 in Sct.Internal.PersonRole,
  on: p1.person_id == p0.id, join: r2 in Sct.Misc.Role,
  on: is_nil(r2.authorizable_type) and is_nil(r2.authorizable_id) and r2.name == ^"aa_admin" and
   r2.id == p1.role_id,
  where: p0.active == true, where: p0.tenant_id == ^3670, distinct: true>
 |> join(:inner, [ps], s in Sct.Internal.PersonRole, on: s.person_id == ps.id) #=> #Ecto.Query<from p0 in Sct.Network.Person, join: p1 in Sct.Internal.PersonRole,
  on: p1.person_id == p0.id, join: r2 in Sct.Misc.Role,
  on: is_nil(r2.authorizable_type) and is_nil(r2.authorizable_id) and r2.name == ^"aa_admin" and
   r2.id == p1.role_id,
  join: p3 in Sct.Internal.PersonRole, on: p3.person_id == p0.id,
  where: p0.active == true, where: p0.tenant_id == ^3670, distinct: true>
 |> join(:inner, [ps, s], rr in Sct.Misc.Role,
   on:
     s.role_id == rr.id and rr.authorizable_type == "Assessment" and
       rr.authorizable_id == ^strategic_collaboration.id and rr.name == ^p.acl
 ) #=> #Ecto.Query<from p0 in Sct.Network.Person, join: p1 in Sct.Internal.PersonRole,
  on: p1.person_id == p0.id, join: r2 in Sct.Misc.Role,
  on: is_nil(r2.authorizable_type) and is_nil(r2.authorizable_id) and r2.name == ^"aa_admin" and
   r2.id == p1.role_id,
  join: p3 in Sct.Internal.PersonRole, on: p3.person_id == p0.id,
  join: r4 in Sct.Misc.Role,
  on: p1.role_id == r4.id and r4.authorizable_type == "Assessment" and r4.authorizable_id == ^944 and
   r4.name == ^"aa_admin",
  where: p0.active == true, where: p0.tenant_id == ^3670, distinct: true>
 |> select([ps, s, rr], ps.id) #=> #Ecto.Query<from p0 in Sct.Network.Person, join: p1 in Sct.Internal.PersonRole,
  on: p1.person_id == p0.id, join: r2 in Sct.Misc.Role,
  on: is_nil(r2.authorizable_type) and is_nil(r2.authorizable_id) and r2.name == ^"aa_admin" and
   r2.id == p1.role_id,
  join: p3 in Sct.Internal.PersonRole, on: p3.person_id == p0.id,
  join: r4 in Sct.Misc.Role,
  on: p1.role_id == r4.id  <------ LINE in ERROR ********************************************
and r4.authorizable_type == "Assessment" and r4.authorizable_id == ^944 and
   r4.name == ^"aa_admin",
  where: p0.active == true, where: p0.tenant_id == ^3670, distinct: true,
  select: p0.id>
 |> Repo.all() #=> []

I have marked the line generating the incorrect relation above and the incorrect resulting reference to p1 instead of p3

Expected behavior

Expected that the query generated for the line indicated above should result in:

on: p3.role_id == r4.id

NOT

on: p1.role_id == r4.id

[tjchambers]

BTW - I do feel as though I can work around this but I wanted to report it. Sorry for the complexity of the example.

[josevalim]

Hi @tjchambers! 👋

I believe the query is correct? Bindings are positional, [ps, s] will match the from and the join of the initial query. If you want to get the last join, you can do [ps, ..., s]. Alternatively, use named bindings as outlined in Ecto.Query docs. :)

[tjchambers]

Thanks - let me try that. I expected the unique alias s to refer properly.

[josevalim]

No, the variable names have no impact on that. We did like this on purpose to avoid accidental variable name clashing from unrelated parts of the code.

[tjchambers]

Ok - adding ... got the "proper" reference. Thanks.

[tjchambers]

BTW - I absolutely am head over heels using dbg .