Joining two queries together results in query planning error

[zachdaniel]

Environment

• Elixir version (elixir -v): 1.5.1
• Database and version (PostgreSQL 9.4, MongoDB 3.2, etc.): Postgresql 9.6
• Ecto version (mix deps): 2.2.7
• Database adapter and version (mix deps): postgrex 0.13.3
• Operating system: OSX

Current behavior

alias MyApp.Repo
import Ecto.Query

q1 = from row in MyApp.Post, where: row.published == true
q2 = from row in MyApp.User, where: row.super == false

qx = 
  from post in q1,
    join: user in ^q2,
    on: user.id == post.user_id

Repo.all(qx)

raises

** (Ecto.QueryError) deps/ecto/lib/ecto/query/planner.ex:18: field `super` in `join` does not exist in schema MyApp.Post in query:

from a0 in MyApp.Post,
  join: a1 in MyApp.User,
  on: a1.super == false and a1.id == a0.user_id,
  where: a0.published == true,
  select: a0

    (elixir) lib/enum.ex:1357: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3
    (elixir) lib/enum.ex:1357: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3
    (elixir) lib/enum.ex:1357: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3
    (elixir) lib/enum.ex:1811: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto) lib/ecto/repo/queryable.ex:124: Ecto.Repo.Queryable.execute/5
    (ecto) lib/ecto/repo/queryable.ex:37: Ecto.Repo.Queryable.all/4
iex(6)>

Expected behavior

It should correctly build the where clauses as on statements and not apply them to the main query that I'm joining to.

EDIT: Fixed an alias name in the example.

[josevalim]

Thanks @zachdaniel!

Is the error really reporting this: join: a1 in MyApp.,? Notice the alias is incomplete.

[zachdaniel]

No, my apologies! I went through and replaced our model names, and got that wrong. Fixing now. Edited the example.

[josevalim]

I am still confused by the error message. It says the super column does not exist in Post... but the query is accessing the super for User? Do you have a sample app that reproduces the error? Or can you send a PR with a failing test case to our suite? We have a join test suite inside the integration_test folder.

[zachdaniel]

I will add a test case, but I need to be able to run the query itself since postgrex raises the error.

With that said, the mismatch of fields there is correct. That is what it shows in the error message. When running the query it appears to behave differently than when inspecting it.

[josevalim]

If you do Repo.to_sql (or Ecto.Adapters.SQL.to_sql(Repo, query) depending on your version)... is the returned query correct or does it yield the same error?

[zachdaniel]

It yields the same error.

Repo.to_sql(:all, qx)
** (Ecto.QueryError) deps/ecto/lib/ecto/query/planner.ex:18: field `super` in `join` does not exist in schema MyApp.Post in query:
....

With the same field/schema mismatch.

[zachdaniel]

Seemed easiest to get a sample app running, as I wasn't sure of the best way to use a Repo in tests, or to explicitly run queries. Here is the sample app: https://github.com/zachdaniel/sample_ecto_error

To reproduce, you should be able to

mix ecto.create
mix ecto.migrate
iex -S mix
iex> SampleEctoError.Example.reproduce()

[zachdaniel]

This error was produced by just copying and pasting my code from above, with actual schemas modeled the same way

    Repo.start_link()

    q1 = from row in SampleEctoError.Post, where: row.published == true
    q2 = from row in SampleEctoError.User, where: row.super == false

    qx =
      from post in q1,
      join: user in ^q2,
      on: user.id == post.user_id

    Repo.all(qx)


** (Ecto.QueryError) deps/ecto/lib/ecto/query/planner.ex:18: field `super` in `join` does not exist in schema SampleEctoError.Post in query:

from p in SampleEctoError.Post,
  join: u in SampleEctoError.User,
  on: u.super == false and u.id == p.user_id,
  where: p.published == true,
  select: p

    (elixir) lib/enum.ex:1357: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3
    (elixir) lib/enum.ex:1357: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3
    (elixir) lib/enum.ex:1357: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3

[zachdaniel]

Does anyone know the potential magnitude of this issue? We're on a big crunch, and w/o the ability to do this a lot of our primitives break down. The issue presents a potentially large amount of work for me. A workaround that I've found (but don't really want to implement everywhere) is to wrap the other query in a subquery.

[josevalim]

I can take a look at it today and attempt a fix if you can reproduce it in our test suite. I often spend most time trying to reproduce bugs than fixing them. :)

[zachdaniel]

I can do that. Do you have any tips for making it to the query planning stage without a backing db? It looks like none of the join tests use an actual database (which makes sense), so it doesn't look like I can use Repo in the test suite.

[josevalim]

I would recommend using the integration join suite: https://github.com/elixir-ecto/ecto/blob/master/integration_test/cases/joins.exs

[zachdaniel]

Oh, nice! I had totally forgotten about those. Thanks :)