Measured on @objectstack/driver-sql@17.0.0-rc.1 against a real SQLite database while implementing hotcrm#626 (case-insensitive account matching). Filed from the HotCRM reference app.
What was measured
$regex is the only case-insensitive operator ObjectQL offers, so it is what an author reaches for when they need one. On driver-sql it is not executed as a regular expression. It is compiled to LIKE '%value%' — a substring test.
Both failure directions were reproduced on a real SQLite database, with rows 'Acme Corp' and 'Not Acme Corp Ltd':
| query |
expected (regex semantics) |
actual |
{ name: { $regex: 'Acme Corp' } } |
['Acme Corp'] |
['Acme Corp', 'Not Acme Corp Ltd'] |
{ name: { $regex: '^acme\\s+corp$' } } |
['Acme Corp'] (case-insensitive) |
[] |
The second row is the dangerous one: a correctly written anchored pattern matches nothing at all, with no error. An author who tests their filter against data that happens to contain the substring gets a passing result and ships a query that silently returns the wrong set — or the empty set — in production.
Why this is worse than the "declared but inert" family
We have filed several issues about metadata that is declared and never enforced (#4649 validations failing open, #4697 flow variables, #4698 the general class, plus hotcrm#650 for inert decision conditions). Those are inert: the key does nothing.
This one is different and worse: the operator's name promises one semantics and the implementation delivers another. Nothing about $regex suggests "substring". There is no warning, no validation error, and no runtime signal — the query succeeds and returns a plausible-looking result set. An author has no reason to doubt that an operator called $regex is a regex, so this defect is invisible precisely to the people most likely to hit it.
It also affects a second axis the app cares about: the leading % wildcard makes the query unindexable, so what looks like an indexed lookup is a full scan.
What to establish
- Decide what
$regex means and make the implementation and the name agree. Either implement real regex matching on the SQL drivers (SQLite REGEXP needs a registered function; Postgres has ~*), or — if substring matching is the intended and only supported behaviour — rename the operator ($contains / $like) and reject $regex at query-validation time with a message that says what to use instead. A silent semantic substitution is the one option that should not survive.
- Whatever is chosen, make the mismatch detectable: a pattern containing regex metacharacters (
^, $, \s, [, +) passed to a substring matcher is almost certainly an author error, and the driver is the only layer that can see it.
- Check the other drivers for the same divergence.
driver-memory / driver-mongodb may well implement true regex, which would mean the same query returns different result sets on different drivers — the app's test suite would then pass on the in-memory double and fail in production, which is the exact shape hotcrm#630 already burned us on.
Point 3 is the one we most want an answer to, because it decides whether app-level tests can be trusted at all for any query using this operator.
Context
HotCRM did not end up depending on $regex — the measurement is what ruled it out, and the app now matches on a stored, hook-maintained normalized column instead (hotcrm#626 / PR #654). Filing because the next author to reach for a case-insensitive match will reach for the same operator and has no way to discover this from the outside.
Related: hotcrm#626, hotcrm#630 (driver column-completeness divergence), #4698 (the declared-≠-enforced class).
Measured on
@objectstack/driver-sql@17.0.0-rc.1against a real SQLite database while implementing hotcrm#626 (case-insensitive account matching). Filed from the HotCRM reference app.What was measured
$regexis the only case-insensitive operator ObjectQL offers, so it is what an author reaches for when they need one. Ondriver-sqlit is not executed as a regular expression. It is compiled toLIKE '%value%'— a substring test.Both failure directions were reproduced on a real SQLite database, with rows
'Acme Corp'and'Not Acme Corp Ltd':{ name: { $regex: 'Acme Corp' } }['Acme Corp']['Acme Corp', 'Not Acme Corp Ltd']{ name: { $regex: '^acme\\s+corp$' } }['Acme Corp'](case-insensitive)[]The second row is the dangerous one: a correctly written anchored pattern matches nothing at all, with no error. An author who tests their filter against data that happens to contain the substring gets a passing result and ships a query that silently returns the wrong set — or the empty set — in production.
Why this is worse than the "declared but inert" family
We have filed several issues about metadata that is declared and never enforced (#4649 validations failing open, #4697 flow variables, #4698 the general class, plus hotcrm#650 for inert
decisionconditions). Those are inert: the key does nothing.This one is different and worse: the operator's name promises one semantics and the implementation delivers another. Nothing about
$regexsuggests "substring". There is no warning, no validation error, and no runtime signal — the query succeeds and returns a plausible-looking result set. An author has no reason to doubt that an operator called$regexis a regex, so this defect is invisible precisely to the people most likely to hit it.It also affects a second axis the app cares about: the leading
%wildcard makes the query unindexable, so what looks like an indexed lookup is a full scan.What to establish
$regexmeans and make the implementation and the name agree. Either implement real regex matching on the SQL drivers (SQLiteREGEXPneeds a registered function; Postgres has~*), or — if substring matching is the intended and only supported behaviour — rename the operator ($contains/$like) and reject$regexat query-validation time with a message that says what to use instead. A silent semantic substitution is the one option that should not survive.^,$,\s,[,+) passed to a substring matcher is almost certainly an author error, and the driver is the only layer that can see it.driver-memory/driver-mongodbmay well implement true regex, which would mean the same query returns different result sets on different drivers — the app's test suite would then pass on the in-memory double and fail in production, which is the exact shape hotcrm#630 already burned us on.Point 3 is the one we most want an answer to, because it decides whether app-level tests can be trusted at all for any query using this operator.
Context
HotCRM did not end up depending on
$regex— the measurement is what ruled it out, and the app now matches on a stored, hook-maintained normalized column instead (hotcrm#626 / PR #654). Filing because the next author to reach for a case-insensitive match will reach for the same operator and has no way to discover this from the outside.Related: hotcrm#626, hotcrm#630 (driver column-completeness divergence), #4698 (the declared-≠-enforced class).