ash-project / ash-project/ash_sqlite
ci_string is not honoured on SQLite for contains/2 or for attribute sorts
- Dominant language
- Elixir
- Stars
- 31
- Forks
- 32
- Avg merge
- 2d 9h
- Merged PRs (30d)
- 6
Description
On SQLite, `contains(field, ^Ash.CiString.new("rnz"))` compiles to `instr((field), (? COLLATE NOCASE)) > 0`. SQLite only applies a collation to comparison operators, so `COLLATE NOCASE` on a function argument does nothing and the filter silently matches case — `rnz` finds no `RNZ National`. Postgres is fine because `AshSql.Expr` takes the `ilike?()` branch (`expr.ex:707`); every other backend falls through to the same fragment as the non-CiString case. `starts_with/2` at `expr.ex:859` has the same shape. `instr(lower(x), lower(y)) > 0` would fix it, with the usual caveat that SQLite's `lower/1` and `NOCASE` both fold ASCII only.
Possibly related: sorting by a `ci_string` *calculation* emits `ORDER BY (x COLLATE NOCASE)`, because `AshSql.Sort` runs calculations through `dynamic_expr` with their type, but sorting by a `ci_string` *attribute* emits a bare `ORDER BY t0."x"` and stays BINARY. Same declared type, two orderings. And the migration generator maps `ci_string` to `:citext`, which SQLite accepts as a column type but treats as TEXT with BINARY collation, so a `ci_string` attribute isn't case-insensitive for ordering, unique indexes or `GROUP BY` either. Making that `TEXT COLLATE NOCASE` would need a table rebuild for existing tables, so that part is probably a separate conversation.
Found on `ash_sqlite 0.2.19` while making a browse list order and filter the way a person expects. Happy to send a PR for the `contains`/`starts_with` part if that's a direction you'd take.
Contributor guide
Research direction
Start in expr.ex at lines 707 and 859, then inspect how AshSql.Sort handles calculated and attribute ci_string values. Reproduce the SQLite contains/2 and starts_with/2 cases using the issue's example, and verify that case-insensitive matching works without changing the separately noted ordering and migration concerns.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- elixir, sqlite
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 65/100