Ecto: fragment/1 can't auto dump uuid string to a binary of 16 bytes

[ZhengQingchen]

Environment

• Elixir version (elixir -v):
  Elixir 1.6.0-dev (ba19fc8) (compiled with OTP 20)
• Database and version (PostgreSQL 9.4, MongoDB 3.2, etc.):
  PostgreSQL 10.0
• Ecto version (mix deps):
  2.2.4
• Database adapter and version (mix deps):
  "postgrex": {:hex, :postgrex, "0.13.3"}
• Operating system:
  Mac OS
  

  Current behavior

Fragment can't auto dump uuid string to a binary of 16 bytes

schema do
    field :targets, {:array, :binary_id}
end

  def for_user(targets, user_id) do
    from n in Notification,
      where: fragment("? && ?", n.targets, targets)
  end

n.targets is array of :uuid, input targets is array of uuid string. Then get an error message

[debug] QUERY ERROR source="notifications" db=1.6ms queue=0.2ms
SELECT n0."id", n0."content", n0."digest", n0."from_id", n0."from_type", n0."images", n0."reply_count", n0."title", n0."deadline", n0."total_count", n0."targets", n0."user_id", n0."from", n0."repliers", n0."inserted_at", n0."updated_at", NOT (n1."user_id" IS NULL) FROM "notifications" AS n0 LEFT OUTER JOIN "notifications_users" AS n1 ON (n1."user_id" = $1) AND (n0."id" = n1."notification_id") WHERE (n0."targets" && $2) ORDER BY n0."inserted_at" DESC, n0."id" DESC LIMIT 25 [<<152, 121, 56, 111, 119, 159, 68, 173, 182, 153, 124, 187, 95, 222, 63, 176>>, ["9879386f-779f-44ad-b699-7cbb5fde3fb0"]]
[info] Sent 500 in 61ms
[error] #PID<0.14358.0> running ClazzWeb.Endpoint terminated
Server: 127.0.0.1:4000 (http)
Request: GET /api/notifications
** (exit) an exception was raised:
    ** (ArgumentError) Postgrex expected a binary of 16 bytes, got "9879386f-779f-44ad-b699-7cbb5fde3fb0". Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.
        (clazz) deps/postgrex/lib/postgrex/type_module.ex:717: Clazz.PostgresTypes.encode_list/3
        (postgrex) lib/postgrex/extensions/array.ex:72: Postgrex.Extensions.Array.encode/4
        (postgrex) lib/postgrex/extensions/array.ex:45: Postgrex.Extensions.Array.encode/3
        (clazz) deps/postgrex/lib/postgrex/type_module.ex:717: Clazz.PostgresTypes.encode_params/3
        (postgrex) lib/postgrex/query.ex:45: DBConnection.Query.Postgrex.Query.encode/3
        (db_connection) lib/db_connection.ex:1071: DBConnection.describe_run/5
        (db_connection) lib/db_connection.ex:1142: anonymous fn/4 in DBConnection.run_meter/5
        (db_connection) lib/db_connection.ex:1199: DBConnection.run_begin/3
        (db_connection) lib/db_connection.ex:584: DBConnection.prepare_execute/4
        (ecto) lib/ecto/adapters/postgres/connection.ex:73: Ecto.Adapters.Postgres.Connection.prepare_execute/5
        (ecto) lib/ecto/adapters/sql.ex:256: Ecto.Adapters.SQL.sql_call/6
        (ecto) lib/ecto/adapters/sql.ex:426: Ecto.Adapters.SQL.execute_and_cache/7
        (ecto) lib/ecto/repo/queryable.ex:133: Ecto.Repo.Queryable.execute/5
        (ecto) lib/ecto/repo/queryable.ex:37: Ecto.Repo.Queryable.all/4
        (clazz) lib/clazz/moments/moments.ex:171: Clazz.Moments.list_notifications/2
        (clazz) lib/clazz_web/controllers/notification_controller.ex:10: ClazzWeb.NotificationController.index/3
        (clazz) lib/clazz_web/controllers/notification_controller.ex:1: ClazzWeb.NotificationController.action/2
        (clazz) lib/clazz_web/controllers/notification_controller.ex:1: ClazzWeb.NotificationController.phoenix_controller_pipeline/2
        (clazz) lib/clazz_web/endpoint.ex:1: ClazzWeb.Endpoint.instrument/4
        (phoenix) lib/phoenix/router.ex:278: Phoenix.Router.__call__/1

So I can do manual dump the string value to a a binary of 16 bytes, then it can be success.
the dump function like this

  defp dump_uuid(uuids) do
    Enum.map(uuids, fn uuid ->
      {:ok, uuid} = Ecto.UUID.dump(uuid)
      uuid
    end)
  end

Then use the help function can get thing done.

  def for_user(targets, user_id) do
    from n in Notification,
      where: fragment("? && ?", n.targets, ^dump_uuid(targets))
 end

So it can be done auto automate or have to do manual?

Expected behavior

  def for_user(targets, user_id) do
    from n in Notification,
      where: fragment("? && ?", n.targets, targets)
  end

The uuid string can be auto dump to a binary of 16 bytes in the fragment.

[josevalim]

Have you tried casting it explicitly?

    from n in Notification,
      where: fragment("? && ?", n.targets, type(^targets, :binary_id))

Although that may not work since a UUID is a binary, so it is hard for us to say which binary do you want.

[ZhengQingchen]

The targets is a list of :binary_id
and I try this

    from n in Notification,
      where: fragment("? && ?", n.targets, type(^targets, {:array, :binary_id}))

It's work. 👍

[josevalim]

Hooray! :heart:

[supersimple]

@ZhengQingchen this just helped me out of a similar issue. Thanks!!!