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).
Top comments (3)
If you aren't using a schema, you can also define the type in the query like:
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.
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.