Why does the first query return a result while the second one return an error (unless Ecto.UUID.dump/1 or Ecto.UUID.cast!/1 are called)?

Questions / Help
, , ,
1

Hi all,

Let’s say that I have three tables:

  • companies,
  • workers,
  • and company_workers as a join table between the two previous tables

A worker can work at several companies (and a company has several workers). The query should return all the companies for which the worker is working.

In both queries, the value of the worker_id variable is "2353e61e-d047-41a9-8bd6-2155a6aa94c3". The first query returns the expected result:

# QUERY 1
  from(
    c in Company,
    join: cw in "company_workers",
    on: c.id == cw.company_id,
    join: w in Worker,
    on: cw.worker_id == w.id,
    where: w.id == ^worker_id
  )
 |> Repo.all()

But surprisingy, the second one returns an error:

# QUERY 2
  from(
    c in Company,
    join: cw in "company_workers",
    on: c.id == cw.company_id,
    where: cw.worker_id == ^worker_id
  )
  |> Repo.all()

** (DBConnection.EncodeError) Postgrex expected a binary of 16 bytes, got "2353e61e-d047-41a9-8bd6-2155a6aa94c3". Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.

I can see in the debug output of the first query, that the worker_id is automatically encoded like so:

[<<35, 83, 230, 30, 208, 71, 65, 169, 139, 214, 33, 85, 166, 170, 148, 195>>]

while in the second query, the worker_id looks like the original value:

"2353e61e-d047-41a9-8bd6-2155a6aa94c3"

Also, calling Ecto.UUID.cast!(worker_id) or Ecto.UUID.dump(worker_id) in the second query makes it return the expected value:

# QUERY 2
worker_id = Ecto.UUID.cast!(worker_id)
  from(
    c in Company,
    join: cw in "company_workers",
    on: c.id == cw.company_id,
    where: cw.worker_id == ^worker_id
  )
  |> Repo.all()

Why the worker_id is automatically translated to <<35, 83, 230, 30, 208, 71, 65, 169, 139, 214, 33, 85, 166, 170, 148, 195>> in the first query but not in the second one?

2

For w.id the query is aware of the schema Worker and the setup of the fields in that schema, like types. For cw.worker_id the query selects from a bare table "company_workers", therefore ecto does not have any type information at runtime about that column.

4 Likes
3

Alright, thank you for the explanation!