Ecto 3 join with prefix issue

Questions / Help
1

I’m migrating my application to Ecto 3.0 and have hit an issue with query prefixes and join. The following snippet worked fine in Ecto 2.x:

  actions = from(a in Action, where: ^action_dynamic)

  query =
    from(
      t in Ticket,
      join: a in ^actions,
      on: a.ticket_id == t.id,
      where: ^dynamic,
      select: count(t.id)
    )

  Repo.one(query, prefix: Triplex.to_prefix(domain))

with Ecto 3 it gives the error:

ERROR 42P01 (undefined_table) relation "actions" does not exist

It looks as though the query prefix isn’t applying to the join. Attempting to force the join query to use the prefix with

prefix = Triplex.to_prefix(domain)
actions = from(a in Action, prefix: ^prefix, where: ^action_dynamic)

gives a different error:

`prefix` must be a compile time string, got: `^prefix`

This isn’t really what I’m after. I’ve also tried variations on putting the prefix into the query: %{query | prefix: prefix}

Ecto version 3.2.5

It feels like I am missing something obvious here… Anyone?

2

For sure, I’m not a maintainer, but this looks for me like a bug.

(…) This will be applied to all from and join s in the query that did not have a prefix previously given either via the :prefix option on join / from or via @schema_prefix in the schema.
Source: Ecto.Repo — Ecto v3.11.1

Could you please share a minimal repository example in order to quickly reproduce your issue?

3

I’ll see if I can create something small to help re-produce. There are quite a few moving parts here

4

For sure for one project yes, but imagine doing that every time someone asks for help.

Also for sure it’s not only for me. Look that few people would like to help and everyone would need to fully setup ecto-based app and reproduce your problem.

Also in other cases people run into trouble with for example using Elixir API and their description (unlike yours) may not be clear and they are often asked to show what tried so far. This helps a lot to understand a problem, so one code example shows more than thousand of words. :077:

In short it’s really a time saving when you report problem for community with a smallest code example.

5

I’ve put up a fork with a failing test case:

The new test case is at

Unfortunately I am not at all familiar with the ecto internals at this point, but am happy to be pointed in the right direction if I can help with a fix.

6

@seanhinde I have create an issue with shorter test:

TIP: We do not need a database to check generated SQL code. :smiley:

7

Great, thank you. That’s a much cleaner test case.

8

@seanhinde Not sure if you noticed, but about 4 hours ago it has been fixed in master branch.

Also there is one tip before this change would be published in next ecto_sql release.

Instead of a in ^actions you can use a in subquery(actions) which should fix prefix issue as well.

Could you please mark this or my previous comment as a solution to this topic (as it’s fixed in ecto_sql).

9

I did notice. Very impressive turn around time from you and the team :grinning:

I also thought I had already ticked your message as solution yesterday. I’ll tick the latest one as well.

Thanks for the tip on trying subquery. I tried it out and it didn’t immediately solve the prefix issue:

actions = from(a in Action, where: ^action_dynamic)

query =
    from(
      t in Ticket,
      join: a in subquery(actions),
      on: a.ticket_id == t.id,
      where: ^dynamic,
      select: count(t.id)
    )

Still gives:

ERROR 42P01 (undefined_table) relation "actions" does not exist

But I’m totally happy to run off git master for the time being

10

I can also confirm that running directly off master no longer errors out