Ecto: Can not use WITH RECURSIVE in postgres

[tatiesmars]

Environment

• Elixir version (elixir -v):

Erlang/OTP 21 [erts-10.1.1] [source] [64-bit] [smp:4:4] [ds:4:4:10] [async-threads:1] [hipe] [dtrace]

Elixir 1.7.4 (compiled with Erlang/OTP 21)

• Database and version (PostgreSQL 9.4, MongoDB 3.2, etc.):
  postgres:9.6-alpine
• Ecto version (mix deps):

* ecto 3.0.3 (Hex package) (mix)
  locked at 3.0.3 (ecto) 018a3df0
  ok
* ecto_sql 3.0.2 (Hex package) (mix)
  locked at 3.0.2 (ecto_sql) 0e04cbc1
  ok

• Database adapter and version (mix deps):

* postgrex 0.14.0 (Hex package) (mix)
  locked at 0.14.0 (postgrex) f3d6ffea
  ok

• Operating system:
  alpine

Current behavior

Running this method with ecto 2.2 was perfectly working

  def get_root_parent(context_id) do
    from(
      c in __MODULE__,
      join:
        ct in fragment(
          """
          WITH RECURSIVE context_tree(context_id, parent_id) AS
          (
              SELECT context_id, parent_id
              FROM context
              WHERE context_id = ?
            UNION ALL
              SELECT ctx.context_id, ctx.parent_id
              FROM context_tree ct, context ctx
              WHERE ctx.context_id=ct.parent_id
          )
          SELECT * FROM context_tree
          """,
          type(^context_id, Ecto.UUID)
        ),
      on: ct.context_id == c.context_id,
      where: is_nil(c.parent_id)
    )
    |> Repo.one()
  end

Since I update to ecto 3.0 I m getting error when executing the test suits

 56) test Context DB layer CRUD tests Get root parent context (SelectChat.Chat.Db.ContextTest)
     test/select_chat/chat/db/context_test.exs:25
     ** (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near "WITH"

         query: SELECT c0."context_id", c0."parent_id", c0."group_context_id", c0."type", c0."role", c0."deleted", c0."lock_version", c0."inserted_at", c0."updated_at" FROM "context" AS c0 INNER JOIN WITH RECURSIVE context_tree(context_id, parent_id) AS
     (
         SELECT context_id, parent_id
         FROM context
         WHERE context_id = $1::uuid
       UNION ALL
         SELECT ctx.context_id, ctx.parent_id
         FROM context_tree ct, context ctx
         WHERE ctx.context_id=ct.parent_id
     )
     SELECT * FROM context_tree
      AS f1 ON f1."context_id" = c0."context_id" WHERE (c0."parent_id" IS NULL)
     code: %Context{context_id: persisted_pcid} = Context.get_root_parent(context_id)
     stacktrace:
       (ecto_sql) lib/ecto/adapters/sql.ex:595: Ecto.Adapters.SQL.raise_sql_call_error/1
       (ecto_sql) lib/ecto/adapters/sql.ex:528: Ecto.Adapters.SQL.execute/5
       (ecto) lib/ecto/repo/queryable.ex:147: Ecto.Repo.Queryable.execute/4
       (ecto) lib/ecto/repo/queryable.ex:18: Ecto.Repo.Queryable.all/3
       (ecto) lib/ecto/repo/queryable.ex:66: Ecto.Repo.Queryable.one/3
       test/select_chat/chat/db/context_test.exs:31: (test)

It seem that RECURSIVE keyword is not usable anymore?

Expected behavior

Should be able to use WITH postgres keyword

[josevalim]

You need to explicitly wrap the SQL inside the fragment in parens:

 ct in fragment(
          """
          (WITH RECURSIVE context_tree(context_id, parent_id) AS
          (
              SELECT context_id, parent_id
              FROM context
              WHERE context_id = ?
            UNION ALL
              SELECT ctx.context_id, ctx.parent_id
              FROM context_tree ct, context ctx
              WHERE ctx.context_id=ct.parent_id
          )
          SELECT * FROM context_tree)
          """,
          type(^context_id, Ecto.UUID)
        ),

We were used to do it automatically but that broke some queries, so users need to explicitly wrap it now.

[josevalim]

Btw, this is listed in the CHANGELOG but I agree it is easy to miss it. :) So no worries.