Datatype mismatch due to JSON encoding of lists in IN statements

@nadilas

There's a minor topic, the new list-to-JSON encoding converts all lists via Jason.encode including for IN statements, so I'm getting a datatype mismatch:
[error] GenServer {Oban.Registry, {Oban, Oban.Stager}} terminating
** (EctoLibSql.Error) SQLite failure: datatype mismatch
(ecto_sql 3.13.4) lib/ecto/adapters/sql.ex:1113: Ecto.Adapters.SQL.raise_sql_call_error/1

e.g.

WHERE j.state IN (?, ?) with params ["scheduled", "retryable"]

becomes

WHERE j.state IN ('[\"scheduled\",\"retryable\"]')

Originally posted by @nadilas in #57 (comment)

@nadilas

Excellent, thanks for the bug report @nadilas, I'll fix that soon too.

This is my first Elixir library, I'm using it in a side project which is still in development, because I wanted to try out Turso/libSQL. Because I haven't pushed the usage of it much yet, and it's only new, there isn't much "real world" usage, so your feedback is very useful to find all the actual use cases that haven't been caught by tests yet.

Fix issue #63: IN clause datatype mismatch with JSON-encoded lists

Merge pull request #64 from ocean/fix/in-clause-json-encoding

@nadilas

Thanks for your patience @nadilas - please try again with the latest release, 0.8.8, I've focused on bug fixes and have fixed the "IN" clause conversion issue as far as I can tell from testing.

@ocean

@ocean (pointing out again, I'm not familiar enough with elixir).

The current fix passes ~w(scheduled retryable) ecto wraps the list in an %Ecto.Query.Tagged{} struct for type safety. Which means the when is_list(right) guard doesn't catch.

Looking at this reference:
https://github.com/elixir-sqlite/ecto_sqlite3/blob/main/lib/ecto/adapters/sqlite3/connection.ex#L1314-L1323

should generate this:
state IN (SELECT value FROM JSON_EACH('["scheduled","retryable"]'))

locally tested and it works.

To cover this case with a test, this should do:

  test "IN clause with literal word list (~w) - e.g. oban" do
    # test data matching Oban's state values
    Ecto.Adapters.SQL.query!(TestRepo, """
    INSERT INTO test_items (state, name, inserted_at, updated_at)
    VALUES ('scheduled', 'job1', datetime('now'), datetime('now')),
           ('retryable', 'job2', datetime('now'), datetime('now')),
           ('available', 'job3', datetime('now'), datetime('now')),
           ('completed', 'job4', datetime('now'), datetime('now')) 
    """)                                                                                                                                                                 
                                                                                                                                                                         
    # This is how Oban's Lite engine queries jobs - using ~w() sigil
    # ~w() creates a compile-time list that Ecto wraps in %Ecto.Query.Tagged{}
    # This was causing "datatype mismatch" because the Tagged struct wasn't
    # being handled by the IN clause expression generator
    query =
      from(t in "test_items",
        where: t.state in ~w(scheduled retryable),
        select: t.name
      )                                                                                                                                                                  
                                                                                                                                                                         
    # should not raise "datatype mismatch" error
    result = TestRepo.all(query)

    assert length(result) == 2
    assert "job1" in result
    assert "job2" in result
  end

Thanks for the test, I'll add it in and make sure the fix is applied properly this time.

test: add failing test case for ~w() sigil in IN clause (issue #63)

fix: handle Ecto.Query.Tagged structs in IN clauses (e.g. ~w() sigil …

docs: add unreleased changelog entry for issue #63 fix

test: add comprehensive IN expression test suite for issues #63, #66, #…

@nadilas

Ok, @nadilas I release 0.8.9 which includes your tests and fixes, and should hopefully be better, and then just now 0.9.0 which has some more fixes and improvements in it care of @ricardo-valero 🎉 (and also precompiled NIFs, so it is heaps faster to compile your app).

Closing this now as we should be good. New release coming soon with some deep and security updates.

Navigation Menu

Repository navigation

Datatype mismatch due to JSON encoding of lists in IN statements #63

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Datatype mismatch due to JSON encoding of lists in IN statements #63

Description

Activity

ocean commented on Jan 19, 2026

ocean commented on Jan 23, 2026

nadilas commented on Jan 26, 2026

nadilas commented on Jan 26, 2026

ocean commented on Jan 26, 2026

ocean commented on Feb 2, 2026

ocean commented on Feb 6, 2026

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions