NOT IN query with custom type that uses atoms no longer working.

[Hermanverschooten]

Code of Conduct

• I agree to follow this project's Code of Conduct

AI Policy

• I agree to follow this project's AI Policy, or I agree that AI was not used while creating this issue.

Versions

• ash 3.5.42
• ash_postgres 2.6.19
• ash_sql 0.2.93

Operating system

MacOS

Current Behavior

After upgrading to 0.2.93 my tests fail when there is a "NOT IN" query, apparently where before dump_to_native was called with the atom (:cancelled) now a string "cancelled" is used causing the postgres query to fail.

This is my custom type:

defmodule Admin.Resources.Types.TimerStatus do
  @moduledoc """
  Custom Ash type for timer status enum that maps atoms to integers
  """
  use Ash.Type

  @values [running: 0, complete: 1, cancelled: 2, invoiced: 3]
  @atom_to_int Map.new(@values)
  @int_to_atom Map.new(@values, fn {k, v} -> {v, k} end)

  @impl Ash.Type
  def storage_type(_), do: :integer

  @impl Ash.Type
  def cast_input(value, _) when is_atom(value) do
    if value in Map.keys(@atom_to_int) do
      {:ok, value}
    else
      :error
    end
  end

  def cast_input(value, _) when is_integer(value) do
    if Map.has_key?(@int_to_atom, value) do
      {:ok, Map.get(@int_to_atom, value)}
    else
      :error
    end
  end

  def cast_input(value, _) when is_binary(value) do
    case String.to_existing_atom(value) do
      atom when is_atom(atom) -> cast_input(atom, nil)
      _ -> :error
    end
  rescue
    _ -> :error
  end

  def cast_input(_, _), do: :error

  @impl Ash.Type
  def cast_stored(nil, _), do: {:ok, nil}

  def cast_stored(value, _) when is_integer(value) do
    case Map.get(@int_to_atom, value) do
      nil -> :error
      atom -> {:ok, atom}
    end
  end

  def cast_stored(_, _), do: :error

  @impl Ash.Type
  def dump_to_native(value, _) when is_atom(value) do
    case Map.get(@atom_to_int, value) do
      nil -> :error
      int -> {:ok, int}
    end
  end

  def dump_to_native(_, _), do: :error
end

and this is the now failing code:

  @spec list_active() :: [Timer.t()]
  def list_active do
    Timer
    |> Ash.Query.filter(status not in [:cancelled, :invoiced])
    |> Ash.Query.load([:customer])
    |> Ash.Query.sort(:id)
    |> Ash.read!()
  end

And the error:

29) test editing invoice details handles errors when updating detail (AdminWeb.InvoicesLive.EditTest)
     test/admin_web/live/invoices_live/edit_test.exs:214
     ** (Ash.Error.Unknown)
     Bread Crumbs:
       > Error returned from: Admin.Resources.Timer.read

     Unknown Error

     * ** (DBConnection.EncodeError) Postgrex expected an integer in -9223372036854775808..9223372036854775807, got "cancelled". 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.21.1) lib/postgrex/type_module.ex:1084: Postgrex.DefaultTypes.encode_list/3
       (postgrex 0.21.1) lib/postgrex/extensions/array.ex:88: Postgrex.Extensions.Array.encode/4
       (postgrex 0.21.1) lib/postgrex/extensions/array.ex:58: Postgrex.Extensions.Array.encode/3
       (postgrex 0.21.1) lib/postgrex/type_module.ex:1084: Postgrex.DefaultTypes.encode_params/3
       (postgrex 0.21.1) lib/postgrex/query.ex:75: DBConnection.Query.Postgrex.Query.encode/3
       (db_connection 2.8.1) lib/db_connection.ex:1446: DBConnection.encode/5
       (db_connection 2.8.1) lib/db_connection.ex:1546: DBConnection.run_prepare_execute/5
       (db_connection 2.8.1) lib/db_connection.ex:1650: DBConnection.run/6
       (db_connection 2.8.1) lib/db_connection.ex:769: DBConnection.parsed_prepare_execute/5
       (db_connection 2.8.1) lib/db_connection.ex:761: DBConnection.prepare_execute/4
       (ecto_sql 3.13.2) lib/ecto/adapters/postgres/connection.ex:102: Ecto.Adapters.Postgres.Connection.prepare_execute/5
       (ecto_sql 3.13.2) lib/ecto/adapters/sql.ex:1004: Ecto.Adapters.SQL.execute!/5
       (ecto_sql 3.13.2) lib/ecto/adapters/sql.ex:996: Ecto.Adapters.SQL.execute/6
       (ecto 3.13.3) lib/ecto/repo/queryable.ex:241: Ecto.Repo.Queryable.execute/4
       (ecto 3.13.3) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
       (ash_postgres 2.6.19) lib/data_layer.ex:828: anonymous fn/3 in AshPostgres.DataLayer.run_query/2
       (ash_postgres 2.6.19) lib/data_layer.ex:827: AshPostgres.DataLayer.run_query/2
       (ash 3.5.41) lib/ash/actions/read/read.ex:3988: Ash.Actions.Read.run_query/4
       (ash 3.5.41) lib/ash/actions/read/read.ex:729: anonymous fn/9 in Ash.Actions.Read.do_read/5
       (ash 3.5.41) lib/ash/actions/read/read.ex:1495: Ash.Actions.Read.maybe_in_transaction/3
     stacktrace:
       (ash 3.5.41) lib/ash/error/unknown.ex:3: Ash.Error.Unknown."exception (overridable 2)"/1
       (ash 3.5.41) /Users/herman/Projects/admin/deps/splode/lib/splode.ex:264: Ash.Error.to_class/2
       (ash 3.5.41) lib/ash/error/error.ex:108: Ash.Error.to_error_class/2
       (ash 3.5.41) lib/ash/actions/read/read.ex:489: anonymous fn/3 in Ash.Actions.Read.do_run/3
       (ash 3.5.41) lib/ash/actions/read/read.ex:438: Ash.Actions.Read.do_run/3
       (ash 3.5.41) lib/ash/actions/read/read.ex:86: anonymous fn/3 in Ash.Actions.Read.run/3
       (ash 3.5.41) lib/ash/actions/read/read.ex:85: Ash.Actions.Read.run/3
       (ash 3.5.41) lib/ash.ex:2772: Ash.read/2
       (ash 3.5.41) lib/ash.ex:2707: Ash.read!/2
       (admin 0.1.0) lib/admin/timers.ex:86: Admin.Timers.list_for_customer/1
       (admin 0.1.0) lib/admin_web/live/invoices_live/edit.ex:30: AdminWeb.InvoicesLive.Edit.mount/3
       (phoenix_live_view 1.1.12) lib/phoenix_live_view/utils.ex:348: anonymous fn/6 in Phoenix.LiveView.Utils.maybe_call_live_view_mount!/5
       (telemetry 1.3.0) /Users/herman/Projects/admin/deps/telemetry/src/telemetry.erl:324: :telemetry.span/3
       (phoenix_live_view 1.1.12) lib/phoenix_live_view/static.ex:324: Phoenix.LiveView.Static.call_mount_and_handle_params!/5
       (phoenix_live_view 1.1.12) lib/phoenix_live_view/static.ex:155: Phoenix.LiveView.Static.do_render/4
       (phoenix_live_view 1.1.12) lib/phoenix_live_view/controller.ex:39: Phoenix.LiveView.Controller.live_render/3
       (phoenix 1.8.1) lib/phoenix/router.ex:416: Phoenix.Router.__call__/5
       (admin 0.1.0) lib/admin_web/endpoint.ex:1: AdminWeb.Endpoint.plug_builder_call/2
       (admin 0.1.0) lib/admin_web/endpoint.ex:1: AdminWeb.Endpoint.call/2

According to claude:
The regression is triggered at ash/lib/ash/type/atom.ex:115:

  def dump_tonative(value, ) when is_atom(value) do
    {:ok, to_string(value)}
  end

Root Cause Analysis:

The regression path:

1. ash_sql 0.2.93 introduced evaluate_right() function in commit 3cad640
2. This calls maybe_type_expr() at /Users/herman/Projects/_playground/ash_sql/lib/expr.ex:3411-3418
3. Which wraps expressions in Ash.Query.Function.Type
4. The Type function calls Ash.Type.coerce()
5. Coerce determines the values are atoms and processes them with Ash.Type.Atom.dump_to_native/2
6. This converts :cancelled → "cancelled" at line 115
7. When strings reach TimerStatus.dump_to_native/2, it expects atoms but gets strings
8. Results in Postgrex expected an integer...got "cancelled" error

The issue is that the new Type coercion layer in ash_sql 0.2.93 prematurely converts atoms to strings before they reach custom types that expect to handle the original atom values.

Reproduction

I made a reproduction repo: https://github.com/Hermanverschooten/ash_sql_reproduction

Expected Behavior

No response

[zachdaniel]

The tests in that repo are passing for me, before and after mix deps.update ash_sql. Perhaps the issue was resolved by something else?

[Hermanverschooten]

I can still reproduce it, set ash_sql to version 0.2.90 in the mix.exs file and mix deps.get now when you run the tests they fail because the error is not generated.

Downgraded:
  ash_sql 0.2.93 => 0.2.90
* Updating ash_sql (Hex package)
MiniM4➜  ash_sql_reproduction git:(main) ✗ mix test
==> ash_sql
Compiling 13 files (.ex)
Generated ash_sql app
==> ash_postgres
Compiling 56 files (.ex)
Generated ash_postgres app
==> ash_sql_reproduction
Compiling 3 files (.ex)
Generated ash_sql_reproduction app
Getting extensions in current project...
Running setup for AshPostgres.DataLayer...
Running ExUnit with seed: 900428, max_cases: 28

..........

  1) test User queries with StatusType available_users query with NOT IN should convert atoms to integers (AshSqlReproduction.UserQueryTest)
     test/ash_sql_reproduction/user_query_test.exs:8
     Expected exception Ash.Error.Unknown but nothing was raised
     code: assert_raise(
     stacktrace:
       test/ash_sql_reproduction/user_query_test.exs:14: (test)



  2) test User queries with StatusType dynamic NOT IN query should convert atoms to integers (AshSqlReproduction.UserQueryTest)
     test/ash_sql_reproduction/user_query_test.exs:26
     Expected exception Ash.Error.Unknown but nothing was raised
     code: assert_raise(
     stacktrace:
       test/ash_sql_reproduction/user_query_test.exs:30: (test)


Finished in 0.02 seconds (0.01s async, 0.01s sync)
12 tests, 2 failures

[capoccias]

Hey, I seem to have the same error but with an in

Ash version: 3.5.43
AshPostgres version: 2.6.20
AshSql version: 0.3.0

Callsite

MyApp.MyDomain.MyResource
|> filter(user_id == ^user.id)
|> filter(state in [:pending, :confirmed, :unconfirmed])
|> Ash.bulk_update!(:confirm, %{state: state}, actor: actor)

Enum definition

defmodule MyApp.MyDomain.Types.MyResourceStateEnumType do
  use Ash.Type.Enum, values: [:pending, :confirmed, :unconfirmed]

  @values %{
    10 => :pending,
    20 => :confirmed,
    30 => :unconfirmed
  }
  @inverse_values Map.new(@values, fn {k, v} -> {v, k} end)

  @impl Ash.Type
  def storage_type(), do: :smallint

  # Casting input values (e.g., from forms or changesets)
  @impl Ash.Type
  def cast_input(value, _) do
    if value in Map.values(@values), do: {:ok, value}, else: :error
  end

  # Dumping atoms to integers for database storage
  @impl Ash.Type
  def dump_to_native(value, _) do
    if value in Map.values(@values), do: {:ok, Map.fetch!(@inverse_values, value)}, else: :error
  end

  # Loading integers from the database into atoms
  @impl Ash.Type
  def cast_stored(value, _) do
    if value in Map.keys(@values), do: {:ok, Map.fetch!(@values, value)}, else: :error
  end
end

Error

** (Ash.Error.Unknown) 
Bread Crumbs:
  > Error returned from: MyApp.MyDomain.MyResource.confirm

Unknown Error

* ** (DBConnection.EncodeError) Postgrex expected an integer in -32768..32767, got "confirmed". 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.21.1) lib/postgrex/type_module.ex:1084: Postgrex.DefaultTypes.encode_list/3
  (postgrex 0.21.1) lib/postgrex/extensions/array.ex:88: Postgrex.Extensions.Array.encode/4
  (postgrex 0.21.1) lib/postgrex/extensions/array.ex:58: Postgrex.Extensions.Array.encode/3
  (postgrex 0.21.1) lib/postgrex/type_module.ex:1084: Postgrex.DefaultTypes.encode_params/3
  (postgrex 0.21.1) lib/postgrex/query.ex:75: DBConnection.Query.Postgrex.Query.encode/3
  (db_connection 2.8.1) lib/db_connection.ex:1446: DBConnection.encode/5
  (db_connection 2.8.1) lib/db_connection.ex:1546: DBConnection.run_prepare_execute/5
  (db_connection 2.8.1) lib/db_connection.ex:769: DBConnection.parsed_prepare_execute/5
    (ash 3.5.43) lib/ash/error/unknown.ex:3: Ash.Error.Unknown."exception (overridable 2)"/1
    (ash 3.5.43) lib/ash/error/error.ex:108: Ash.Error.to_error_class/2
    (ash 3.5.43) lib/ash/actions/update/bulk.ex:837: Ash.Actions.Update.Bulk.do_atomic_update/5
    (ash 3.5.43) lib/ash/actions/update/bulk.ex:293: Ash.Actions.Update.Bulk.run/6
    (ash 3.5.43) lib/ash.ex:3317: Ash.bulk_update!/4
    (app 0.1.0) lib/my_app/my_domain/my_resource/changes/confirm.ex:30: anonymous fn/3 in MyApp.MyDomain.MyResource.Changes.Confirm.change/3
[os_mon] cpu supervisor port (cpu_sup): Erlang has closed
[os_mon] memory supervisor port (memsup): Erlang has closed

[zachdaniel]

Could you try this branch out of curiosity? #178

[zachdaniel]

Oh, also IIRC there was a component of this fix in latest ash as well. Try main there in addition to ash_sql.

[Hermanverschooten]

With ash from main and ash_sql from the fix/weird-psql-type-issue, the tests in the demo repo fail, as they should.
And in my real project all tests pass again.

[Hermanverschooten]

I just tried with ash 3.5.43 and the branch for ash_sql and that also makes the tests on my project pass.