Ecto ArgumentError: "comparison with nil is forbidden" when querying for null field in Phoenix/Elixir

Questions / Help
1

I’m getting an error in my Phoenix/Elixir application when searching for records where a DateTime field is null. Here’s the error:

** (ArgumentError) comparison with nil is forbidden as it is unsafe. If you want to check if a value is nil, use is_nil/1 instead
    (ecto 3.12.4) lib/ecto/query/builder.ex:1188: Ecto.Query.Builder.not_nil!/1

Here’s my schema:

schema "instances" do
  field :id, :string, primary_key: true
  field :tournament_id, :string
  field :config_id, :string
  field :occurrence_id, :string
  field :ended_at, :utc_datetime, default: nil
  timestamps()
end

And here’s the query that’s failing:

def get_active_instance(tournament_id, config_id) do
  Instance
  |> where(
    [i],
    i.tournament_id == ^tournament_id and
    i.config_id == ^config_id and
    is_nil(i.ended_at)
  )
  |> Repo.one()
  |> case do
    nil -> {:error, :no_active_tournament_instance}
    instance -> {:ok, instance}
  end
end

I’m trying to find instances where ended_at is null (indicating an active instance), along with matching tournament_id and config_id. The schema has the field defaulting to nil, but I’m getting this comparison error.
What’s the correct way to query for null fields in Ecto? The error message suggested using is_nil/1, but I still get the same error.
Environment:

Elixir 1.17.3
Phoenix LiveView 1.0.0
Ecto 3.12.4

2

Seems you are already using is_nil so maybe it’s one of the other comparisons that’s bumping into a nil value? :thinking:

Do you have a unit test demonstrating that these two values are not nil and only the ended_at field is nil and then you get this error?

2 Likes
3

The issue happens because one of the following values was nil: tournament_id or config_id. I added a guard clause to fix it to check that both inputs are valid strings before running the query. Here’s an example:

def get_active_instance(tournament_id, config_id)
      when is_binary(tournament_id) and is_binary(config_id) do
    Instance
    |> where(
      [i],
      i.tournament_id == ^tournament_id and
        i.config_id == ^config_id and
        is_nil(i.ended_at)
    )
    |> Repo.one()
    |> case do
      nil -> {:error, :no_active_tournament_instance}
      instance -> {:ok, instance}
    end
  end

The reason why it was confusing in the first place was that the Ecto query did not explicitly specify which value was trying to compare

1 Like