buildWhere supports =, !=, >, >=, <, <=, like, in, not in. There is no substring-match operator, so callers who want "does this column contain this text" have to hand-build like with wildcards themselves — and cannot do it correctly, because the value has to be escaped and buildWhere cannot emit an ESCAPE clause.
Surfaced from happyvertical/smrt#2276: smrt-core's WHERE validator whitelisted a contains operator that never existed here. parseConditionKey could not split it off the key, so buildCondition read "name contains" as a single identifier and threw Invalid SQL identifier: name contains at query time, after smrt's API had already told the caller the query was valid. smrt has now removed contains from its whitelist and rejects it at the API boundary, pointing callers at like. Reinstating it is blocked on this package.
Why it belongs here, not in the caller
A caller rewriting contains into like '%' || value || '%' gets a different operator wearing the same name:
- Wildcards leak.
% and _ in the value become wildcards. Escaping them requires LIKE ... ESCAPE '\', which buildWhere has no way to emit — the operator table maps to a bare LIKE.
- Case sensitivity is dialect-dependent. SQLite's
LIKE is case-insensitive for ASCII by default; PostgreSQL's is case-sensitive. The same contains call would mean two different things depending on the adapter.
Both are decidable here, where adapterType is already threaded through buildCondition, and not decidable in a caller that does not know the dialect.
What would resolve it
A contains operator in VALID_OPERATORS/parseConditionKey with settled semantics:
- Literal substring match — the value is data, never a pattern.
%/_ match themselves.
- A documented, adapter-consistent case rule (case-sensitive by default with a separate case-insensitive operator, or the reverse — either is fine as long as it is the same on SQLite, PostgreSQL, and DuckDB).
- Emission per adapter that honours 1 and 2 (e.g.
LIKE ... ESCAPE with the value escaped, or strpos/instr).
Worth deciding at the same time whether contains should also mean JSON containment for JSON columns — smrt's original test for it read { 'metadata contains': 'userId' }, i.e. "does this JSON document contain this key", which is a different operator again. If both are wanted they need distinct names.
Cross-links
buildWheresupports=,!=,>,>=,<,<=,like,in,not in. There is no substring-match operator, so callers who want "does this column contain this text" have to hand-buildlikewith wildcards themselves — and cannot do it correctly, because the value has to be escaped andbuildWherecannot emit anESCAPEclause.Surfaced from happyvertical/smrt#2276: smrt-core's WHERE validator whitelisted a
containsoperator that never existed here.parseConditionKeycould not split it off the key, sobuildConditionread"name contains"as a single identifier and threwInvalid SQL identifier: name containsat query time, after smrt's API had already told the caller the query was valid. smrt has now removedcontainsfrom its whitelist and rejects it at the API boundary, pointing callers atlike. Reinstating it is blocked on this package.Why it belongs here, not in the caller
A caller rewriting
containsintolike '%' || value || '%'gets a different operator wearing the same name:%and_in the value become wildcards. Escaping them requiresLIKE ... ESCAPE '\', whichbuildWherehas no way to emit — the operator table maps to a bareLIKE.LIKEis case-insensitive for ASCII by default; PostgreSQL's is case-sensitive. The samecontainscall would mean two different things depending on the adapter.Both are decidable here, where
adapterTypeis already threaded throughbuildCondition, and not decidable in a caller that does not know the dialect.What would resolve it
A
containsoperator inVALID_OPERATORS/parseConditionKeywith settled semantics:%/_match themselves.LIKE ... ESCAPEwith the value escaped, orstrpos/instr).Worth deciding at the same time whether
containsshould also mean JSON containment for JSON columns — smrt's original test for it read{ 'metadata contains': 'userId' }, i.e. "does this JSON document contain this key", which is a different operator again. If both are wanted they need distinct names.Cross-links
containsfrom smrt's whitelist: PLACEHOLDER_PR