ash-project / ash-project/ash_sqlite

ci_string is not honoured on SQLite for contains/2 or for attribute sorts

Open
#228 0 comments 0 reactions 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.