Ecto querying 2 record IDs as integer fails with Postgrex expected binary

Questions / Help
,
1

I have a query that is basically like this:

uuids = ["1234"]
product = %Product{org_id: 18}

q  = from(k in OrgKey, join: f in Firmware, on: [org_key_id: k.id], where: f.uuid in ^uuids and k.org_id != ^product.org_id)

Repo.all(q)

But it produces this error:

[debug] QUERY ERROR source="org_keys" db=7.4ms queue=4.7ms idle=751.8ms
SELECT $1, o0."name", o0."key" FROM "org_keys" AS o0 INNER JOIN "firmwares" AS f1 ON f1."org_key_id" = o0."id" WHERE (f1."uuid" = ANY($2)) AND (o0."org_id" != $3) [18, ["5cb84916-4c7c-55e9-2d20-e32ec6ba0b11"], 18]
** (DBConnection.EncodeError) Postgrex expected a binary, got 18. Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.
    (postgrex 0.15.7) lib/postgrex/type_module.ex:897: Postgrex.DefaultTypes.encode_params/3
    (postgrex 0.15.7) lib/postgrex/query.ex:75: DBConnection.Query.Postgrex.Query.encode/3
    (db_connection 2.3.1) lib/db_connection.ex:1165: DBConnection.encode/5
    (db_connection 2.3.1) lib/db_connection.ex:1263: DBConnection.run_prepare_execute/5
    (db_connection 2.3.1) lib/db_connection.ex:1359: DBConnection.run/6
    (db_connection 2.3.1) lib/db_connection.ex:557: DBConnection.parsed_prepare_execute/5
    (db_connection 2.3.1) lib/db_connection.ex:550: DBConnection.prepare_execute/4
    (ecto_sql 3.5.3) lib/ecto/adapters/sql.ex:692: Ecto.Adapters.SQL.execute!/4
    (ecto_sql 3.5.3) lib/ecto/adapters/sql.ex:684: Ecto.Adapters.SQL.execute/5
    (ecto 3.5.5) lib/ecto/repo/queryable.ex:229: Ecto.Repo.Queryable.execute/4
    (ecto 3.5.5) lib/ecto/repo/queryable.ex:17: Ecto.Repo.Queryable.all/3

And I can’t for the life of me figure out why it is failing here. Im totally lost on if there is a bug to investigate or a user setup error. As far as I can tell, the DB field values look as expected (from psql using /d table)

# Truncated output
$ \d org_keys

Table "public.org_keys"
|   Column    |       Type
| org_id      | bigint            

$ \d products

Table "public.products"
|    Column      |   Type   | 
| org_id          | bigint   |

Any one have any id why it thinks I’m comparing ids of different types when they both clear seem to be integers? Am I using Ecto wrong somehow?

Environment
  • Elixir version (elixir -v):
Erlang/OTP 23 [erts-11.1.3] [source] [64-bit] [smp:24:24] [ds:24:24:10] [async-threads:1] [hipe]

Elixir 1.11.2 (compiled with Erlang/OTP 23)

  • Database and version (PostgreSQL 9.4, MongoDB 3.2, etc.): Postgres 11.10

  • Versions

* db_connection 2.3.1 (Hex package) (mix)
  locked at 2.3.1 (db_connection) abaab617

* ecto 3.5.5 (Hex package) (mix)
  locked at 3.5.5 (ecto) 98dd0e5e

* ecto_sql 3.5.3 (Hex package) (mix)
  locked at 3.5.3 (ecto_sql) d2f53592

* postgrex 0.15.7 (Hex package) (mix)
  locked at 0.15.7 (postgrex) 88310c01
  • Operating system: MacOS 10.15.7 & Ubuntu 20.04.1
2

This seems strange, is there anything unusual in the schema definition for OrgKey?

3

Derp. Sorry, I also have a select: %{name: k.name, key: k.key, org_id: ^product.org_id} in the query and thats where it is probably coming from

4

Also, this is in NervesHub web repos and code can be inspected at https://github.com/nerves-hub/nerves_hub_web

5

I was able to reproduce the error with that information - I suspect the bare $1 in that list means there’s not type information for that column. Changing the query to use (the equivalent of) org_id: ^"#{product.org_id}" allows it to work, which suggests there’s a “default to string” behavior :thinking:

EDIT: using Ecto.Query.API.type/2 also helps - org_id: type(^product.org_id, :integer)