Problem with Ecto fragment and type

I have a custom bit of Ecto code that was working just fine before. I am trying to do a contains query (@>) on an int4range column. The error that I’m getting is:

ERROR 42883 (undefined_function): operator does not exist: int4range @> bigint

Here is the code:

defmodule PG.Queries do
  defmacro contains(first, second) do
    quote do
      # TODO: WHy is :integer becoming bigint?
      fragment("? @> ?", unquote(first), type(unquote(second), :integer))
    end
  end
end

And a minimal example of how it is used:

defmodule Games.DB.GameQuery do
  import Ecto.Query
  import PG.Queries

  alias Games.Repo

  def call() do
    num_players = 1
    query = from g in Games.DB.Game
    query
    |> where([g], contains(g.num_players, ^num_players))
    |> Repo.all
  end
end

Here is the table:

games_dev=# \d games
                            Table "public.games"
    Column    |            Type             | Collation | Nullable | Default
--------------+-----------------------------+-----------+----------+---------
 id           | uuid                        |           | not null |
 name         | text                        |           |          |
 description  | text                        |           |          |
 num_players  | int4range                   |           |          |
 playing_time | int4range                   |           |          |
 inserted_at  | timestamp without time zone |           | not null |
 updated_at   | timestamp without time zone |           | not null |

Here is the query:

SELECT g0."id", g0."description", g0."name", g0."num_players", g0."playing_time",
g0."inserted_at", g0."updated_at" FROM "games" AS g0 WHERE (g0."num_players" @> 1::bigint);
ERROR:  operator does not exist: int4range @> bigint
LINE 1: ...ed_at" FROM "games" AS g0 WHERE (g0."num_players" @> 1::bigi...
                                                         ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

If I change the query to use ::integer instead of ::bigint it works fine:

games_dev=# SELECT * FROM "games" AS g0 WHERE (g0."num_players" @> 1::integer);
                  id                  |   name    | description | num_players | playing_time |
        inserted_at         |         updated_at
--------------------------------------+-----------+-------------+-------------+--------------+
----------------------------+----------------------------
 5369065d-8a9b-4e39-9b3b-529442e07fd1 | Test game |             | [1,3)       | [10,21)      |
 2017-12-06 08:12:04.445278 | 2017-12-06 08:12:04.449007
(1 row)

I am at a loss for why 1 is being cast as 1::bigint instead of 1::integer, does anyone understand what is happening?

Environment:

  • Ecto 2.2.6
  • Elixir 1.5.0
  • Postgres 10.1
2 Likes

Created a minimal reproduction repo.

Test for yourself with:

git clone https://github.com/axelson/bigint_bug_repro.git
cd bigint_bug_repro 
mix deps.get
mix test

You should see output like:

raw query: {"SELECT b0.\"id\", b0.\"num_players\" FROM \"bug_example\" AS b0 WHERE (b0.\"num_players\" @> $1::bigint)",  [1]}


1) test Contains query (BugTest)
   test/bug_test.exs:4
   ** (Postgrex.Error) ERROR 42883 (undefined_function): operator does not exist: int4range @> bigint
   code: Bug.test
   stacktrace:
     (ecto) lib/ecto/adapters/sql.ex:431: 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
     test/bug_test.exs:5: (test)
1 Like

Okay, I think I found the troublesome commit: https://github.com/elixir-ecto/ecto/commit/ecd702dbe86707d57cd2af2ea621b112161fd959

I’ll raise an issue on Ecto.

Edit: Raised at https://github.com/elixir-ecto/ecto/issues/2343

4 Likes

To follow up on this it is marked as won’t fix for now until someone comes forward with a solution or more people run into this problem.

1 Like

But, other people have…
https://github.com/elixir-ecto/ecto/issues/2509

It makes no sense to convert integer to bigint conceptually either, they are not the same type…

1 Like