Error when using subquery containing window function

[dipth]

Environment

• Erlang/OTP 20 [erts-9.0] [source] [64-bit] [smp:8:8] [ds:8:8:10] [async-threads:10] [hipe] [kernel-poll:false] [dtrace]
• Elixir 1.5.1
• PostgreSQL 9.6.3 on x86_64-apple-darwin16.5.0, compiled by Apple LLVM version 8.1.0 (clang-802.0.42), 64-bit
• ecto 2.2.6
• postgrex 0.13.3
• mac OS X High Sierra 10.13 (17A405)

Current behavior

Given the following module:

defmodule MyApp.BlogPost
  alias MyApp.Repo
  use MyApp.Web, :model

  schema "blog_posts" do
    field :title,         :string
    field :body,          :string
    field :published_at,  Calecto.DateTimeUTC

    timestamps()
  end

  def newest_for_frontpage(limit) do
    sq = from(
      bp in (MyApp.BlogPost),
      select: %{id: bp.id, row_number: fragment("row_number() OVER (PARTITION BY ? ORDER BY ? DESC)", bp.user_id, bp.published_at)}
    )

    from(
      bp in (MyApp.BlogPost),
      join: rn in subquery(sq), on: rn.id == bp.id,
      where: rn.row_number == 1,
      limit: ^limit
    )
  end
end

And the following calling code:

BlogPost.newest_for_frontpage(8)
|> Repo.all

Results in the following error:

    ** (Postgrex.Error) ERROR 42601 (syntax_error): syntax error at or near "."
        (ecto) lib/ecto/adapters/sql.ex:431: Ecto.Adapters.SQL.execute_and_cache/7
        (ecto) lib/ecto/repo/queryable.ex:133: Ecto.Repo.Queryable.execute/5
        (ecto) lib/ecto/repo/queryable.ex:37: Ecto.Repo.Queryable.all/4

Expected behavior

Before the error is raised, the following debug output can be seen in the log:

[debug] QUERY ERROR source="blog_posts" db=0.4ms
SELECT b0."id", b0."title", b0."body", b0."visitor_count", b0."comment_count", b0."published", b0."published_at", b0."inserted_at", b0."updated_at", b0."user_id" FROM "blog_posts" AS b0 INNER JOIN (SELECT b0."id" AS "id", row_number() OVER (PARTITION BY b0."user_id" ORDER BY b0."published_at" DESC) AS "row_number" FROM "blog_posts" AS b0) AS s1 ON s1."id" = b0."id" WHERE (s1."row_number" = 1) LIMIT $1 [8]
[info] Sent 500 in 38ms

Running that query (replacing $1 [8] with simply 8) directly in the database, returns a set of records just fine.
The expected behaviour would thus be for Ecto to also return the records instead of raising an error.

[josevalim]

If you replace limit: ^limit by limit: 8 does it work?

[dipth]

No, no effect

[josevalim]

@dipth OK. So is there any chance you are debugging the wrong query?

Instead of BlogPost.newest_for_frontpage(8) |> Repo.all can you try:

Ecto.Adapters.SQL.to_sql :all, BlogPost.newest_for_frontpage(8)

[dipth]

@josevalim I assume you mean to_sql/3 including the repo, as /2 is undefined or private:

iex(3)> Ecto.Adapters.SQL.to_sql :all, Repo, BlogPost.newest_for_frontpage(8)
{"SELECT b0.\"id\", b0.\"title\", b0.\"body\", b0.\"visitor_count\", b0.\"comment_count\", b0.\"published\", b0.\"published_at\", b0.\"inserted_at\", b0.\"updated_at\", b0.\"user_id\" FROM \"blog_posts\" AS b0 INNER JOIN (SELECT b0.\"id\" AS \"id\", row_number() OVER (PARTITION BY b0.\"user_id\" ORDER BY b0.\"published_at\" DESC) AS \"row_number\" FROM \"blog_posts\" AS b0) AS s1 ON s1.\"id\" = b0.\"id\" WHERE (s1.\"row_number\" = 1) LIMIT 8",
 []}

[josevalim]

I tried to reproduce it locally and failed. Do you have some sort of a minimal query that reproduces it?

[dipth]

@josevalim I created a new minimal repo that reproduces the error:
https://github.com/dipth/ecto_subquery_error

[josevalim]

Thanks for the app!

It seems I was barking at the wrong tree. I have run the query directly psql and I got this:

$ psql -d my_app_dev
psql (9.6.2)
Type "help" for help.

my_app_dev=# SELECT b0."id", b0."title", b0."body", b0."inserted_at", b0."updated_at" FROM "blog_posts" AS b0 INNER JOIN (SELECT b0."id" AS "id", row_number() OVER (PARTITION BY b0."user_id" ORDER BY b0."updated_at" DESC) AS "row_number" FROM "blog_posts" AS b0) AS s1 ON s1."id" = b0."id" WHERE (s1."row_number" = 1) LIMIT 8
my_app_dev-# ;
ERROR:  syntax error at or near "."
LINE 1: ...0."id" AS "id", row_number() OVER (PARTITION BY b0."user_id"...

it seems PG does not support quoted arguments there nor interpolations. So I would recommend you to write those directly in the fragment:

    sq = from(
      bp in (MyApp.BlogPost),
      select: %{id: bp.id, row_number: fragment("row_number() OVER (PARTITION BY user_id ORDER BY updated_at DESC)")}
    )

[ericmj]

I think postgres supports this syntax, we have the following code working on postgres 9.6.5:

fragment("rank() OVER (PARTITION BY ? ORDER BY ? DESC NULLS LAST)", group.id, score.score)

When I run the SQL query I don't get a syntax error either:

psql (10.1)
Type "help" for help.

ericmj=# SELECT b0."id", b0."title", b0."body", b0."inserted_at", b0."updated_at" FROM "blog_posts" AS b0 INNER JOIN (SELECT b0."id" AS "id", row_number() OVER (PARTITION BY b0."user_id" ORDER BY b0."updated_at" DESC) AS "row_number" FROM "blog_posts" AS b0) AS s1 ON s1."id" = b0."id" WHERE (s1."row_number" = 1) LIMIT 8
ericmj-# ;
ERROR:  relation "blog_posts" does not exist
LINE 1: ...b0."body", b0."inserted_at", b0."updated_at" FROM "blog_post...

[josevalim]

Maybe it was added to more recent Postgres versions? Because mine, which is one tiny version prior to the reporter, does not work. So another alternative here is to update to a more recent PG.