Ecto: Ambigous query produced on upsert attempt

[aerosol]

Environment

• Elixir version (elixir -v): 1.3.2
• Database and version: PostgreSQL 9.6.1
• Ecto version (mix deps): 2.1.2

• Database adapter and version (mix deps):

  • postgrex 0.13.0 (Hex package) (mix)
    locked at 0.13.0 (postgrex) e101ab47

• Operating system: Darwin Kernel Version 16.3.0

Current behavior

Trying to perform upsert with counter increase on conflict (unique constraint violation).

Migration:

create table(:people) do
   add :email, :string, null: false
   add :times_seen, :integer, default: 1
   timestamps
end

create unique_index(:people, [:email])

Upsert:

l = [%{email: "[email protected]", updated_at: now, inserted_at: now}]

"people"
|> Repo.insert_all(l, 
  returning: [:id, :email], 
  on_conflict: [
    inc: [times_seen: 1]
  ], conflict_target: :email)

Produces an error:

** (Postgrex.Error) ERROR 42702 (ambiguous_column): column reference "times_seen" is ambiguous

Query:

INSERT INTO "people" AS s0 ("email","inserted_at","updated_at") 
VALUES ($1,$2,$3) ON CONFLICT ("email") 
DO UPDATE SET "times_seen" = "times_seen" + $4 
RETURNING "id", "email"

Bindings:

 ["[email protected]", "2017-01-17 10:57:51", "2017-01-17 10:57:51", 1]

The problem with generated query seems to be:

SET "times_seen" = "times_seen" + $4

Expected behavior

SET "times_seen" = "s0"."times_seen" + $4

[aerosol]

Failing test case:

    @tag :with_conflict_target
    test "on conflict with inc" do
      uuid = "6fa459ea-ee8a-3ca4-894e-db77e160355e"
      post = %Post{title: "first", uuid: uuid}
      {:ok, _} = TestRepo.insert(post)

      post = %{title: "upsert", uuid: uuid}

      TestRepo.insert_all(Post, [post], on_conflict: [inc: [visits: 1]], conflict_target: :uuid)
    end

[josevalim]

Beautiful! I will work on a fix today and likely get a new v2.1 out as we have had other bug fixes lately.

[michalmuskala]

I think the final query should rather be:

SET "s0"."times_seen" = "s0"."times_seen" + $4

so we include the table reference in all the places.

[josevalim]

@michalmuskala just FYI, that query is not supported. You can't have "s0" on the left side of =.

[josevalim]

Fixed on e25aed8. v2.1.3 coming out shortly. Thank you for reporting and the test case @aerosol!

[aerosol]

@josevalim thank you, really appreciate your work!