TIL: Ecto's pin is coercing

TL;DR

This:

query |> where([account], account.owner_id == "42")

is not the same as this:

owner_id = "42"
query |> where([account], account.owner_id == ^owner_id)

In the latter case, the value "42" is coerced to the integer 42 (if the owner_id of an account is an integer according to the Ecto schema).

Background

Today I found a bug in some code I wrote a long time ago.

The code was supposed to take a map of atom => String.t and convert some of the values to integers. But it didn't. And my other code dependent on this still ran without errors!

Time to pick up the Sherlock Holmes monocle and find out why this bug never yielded an actual error.

It turns out I later used the value like so in Ecto:

query |> where([account], account.owner_id == ^owner_id)

The account's owner_id is an integer, but I try to compare it with a string, because of the above bug. And it does not fail!?

I opened up an iex and tried this:

iex> Account |> where([account], owner_id: "42") |> Repo.all()
** (Ecto.QueryError) iex:2: value `"42"` cannot be dumped to type :id.
   Or the value is incompatible or it must be interpolated (using ^) so it may be cast
   accordingly in query:

from a0 in Account,
  where: a0.owner_id == "42",
  select: a0

    (elixir) lib/enum.ex:1440: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3 
    (elixir) lib/enum.ex:1440: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3
    (elixir) lib/enum.ex:1948: Enum."-reduce/3-lists^foldl/2-0-"/3
    (elixir) lib/enum.ex:1948: Enum."-reduce/3-lists^foldl/2-0-"/3

This fails. Ah, finally some sanity :)

The error message also suggests that I might need a pin operator to interpolate the value. So I tried this:

iex> Account |> where([account], owner_id: ^"42") |> Repo.all()
[]

And this is why my code did not fail. It was simply ignoring that I had to do with a string and coerced it into a integer.

I thought the pin-operator was only for inserting values of variables, but now I know it also acts as a coercer (or interpolator).

Comments

[Ryan Winchester]

If you aren't using a schema, you can also define the type in the query like:

owner_id = "42"

where(
  query,
  [account],
  account.owner_id == type(^owner_id, :integer)
)

[Paulo Renato]

Thanks for sharing, always learning something new every day ;)

In Postgres I don't know, but in MySql comparing a string with an integer works but its very inefficient in terms of query performance.

[Lasse Skindstad Ebert]

Thanks for reading 🙂

I think comparing string with integer can't be done in postgres, but the above error is from Ecto, which will complain before it even hits the db.