The Sneaky LIKE Assertion: When 1 Catches 19
A LIKE pattern over serialized JSON made an integration assertion flaky: object 1 caught object 19 through a substring collision.
TL;DR
A LIKE pattern meant to find JSON object 1 also matched objects 16 and 19, since substring matching treats them as prefixes. The flake hid because fixture ordering kept the right row first until data shifted. Fix by comparing parsed values or using JSON functions like JSON_SEARCH instead of loose string matching.
An integration test in one of my importer modules carried an assertion that looked healthy: find one row in a table, compare its longitude. The query used a pattern like LIKE '%"no":1%' to locate the object numbered 1 inside a JSON-serialized column. The test stayed green for weeks, then once returned the wrong row: the longitude belonged to object 19, not 1.
The trail of a flake that wasn't a race
The first reflex when a test flakes is to blame data ordering or connections. This one is pure string mechanics. The searched column holds serialized JSON, and the pattern is a substring. That JSON holds several objects: "no":1, "no":16, and "no":19. The search pattern "no":1 matches all of them, because the other two both start with "no":1. Which row gets captured depends only on ordering; once the data shifts, a different row shows up.
The MySQL manual spells out the two LIKE wildcards: % "matches any number of characters, even zero characters" and _ "matches exactly one character" [1]. They make LIKE great for loose searching, and exactly that makes it wrong as an identity predicate. 1, 16, and 19 are different values; a correct search must treat them as whole values, not prefixes.
Why did this flake survive so long? The collision needs two conditions at once: the fixture data must contain adjacent numbers (16, 19), and the result ordering must place row 1 somewhere other than first. While both hold, everything is green. Integration suites usually run on stable datasets, so traps like this stay hidden until a small seed change shifts the order. A flake born from data rather than code is its own diagnostic trap: the log points at the query, while the real fault sits in the search pattern.
When LIKE is honest, and when it lies
I still use LIKE where loose matching is the point: admin filters on name fragments, reports asking for "everything containing X". Partial matches are the requirement there. The danger starts when a substring picks ONE row as a fixture or assertion target. The difference is thin in code and huge in guarantees: a loose match promises no uniqueness, and an assertion that silently assumes uniqueness is a time bomb.
There is a subtler second trap: wildcard characters can appear inside the data. JSON frequently contains underscores and percent signs in content. The MySQL manual warns, "To test for literal instances of a wildcard character, precede it by the escape character" [1]. So even a committed LIKE user can drift off target because of data, not design.
Fixes in tiers, from cheap to correct
First tier: tighten the predicate to exact-value. Compare parsed values, not string fragments; 1 stays 1 even when 16 lives in the same table. The fix commit in my repo took this path, and the test is deterministic again.
Second tier, when searching inside JSON is a real product need: use JSON functions, not LIKE. The MySQL manual ships JSON_SEARCH, which "Returns the path to the given string within a JSON document" [2], plus JSON_CONTAINS for membership checks. These functions understand document structure; they will not swallow "no":16 as "no":1.
One practical note for anyone who finds a similar pattern: don't throw the test away. An assertion that once lied is an asset; it has proven it can deceive, so let it keep working, just with an honest predicate.
What I take away from this small incident: an assertion is an identity predicate, and identity must not be searched loosely. Once the search pattern may match more than one row, the decision of "which row is under test" moves from the test to chance. Chance can hold for weeks, and that is precisely what makes it dangerous.