fix(db): like not matching newline characters (#1263)

ldirer · claude · web-flow · commit 4ff3da57e095 · 2026-02-18T09:32:03.000-07:00
* tests: confirm ilike/like newline matching bug

Add failing tests that demonstrate % and _ wildcards in like/ilike
do not match across newline characters.

* tests: add ilike with newline e2e test

* fix: make like % match newline characters

* changeset

---------

Co-authored-by: Claude &lt;noreply@anthropic.com&gt;
diff --git a/.changeset/nice-months-nail.md b/.changeset/nice-months-nail.md
@@ -0,0 +1,5 @@
+---
+'@tanstack/db': patch
+---
+
+Fix like/ilike `%` and `_` not matching newline characters
diff --git a/packages/db-collection-e2e/src/fixtures/seed-data.ts b/packages/db-collection-e2e/src/fixtures/seed-data.ts
@@ -68,6 +68,7 @@ export function generateSeedData(): SeedDataResult {
       `Rose ${i}`,
       `sam ${i}`,
       `Tina ${i}`,
+      `Ursula ${i}\nNewline`,
     ]
     const name = names[i % names.length]
 
diff --git a/packages/db-collection-e2e/src/suites/predicates.suite.ts b/packages/db-collection-e2e/src/suites/predicates.suite.ts
@@ -412,6 +412,27 @@ export function createPredicatesTestSuite(
         await query.cleanup()
       })
 
+      it(`should filter with like() with wildcard pattern matching newline`, async () => {
+        const config = await getConfig()
+        const usersCollection = config.collections.onDemand.users
+
+        const query = createLiveQueryCollection((q) =>
+          q
+            .from({ user: usersCollection })
+            .where(({ user }) => like(user.name, `Ursula%`)),
+        )
+
+        await query.preload()
+        await waitForQueryData(query, { minSize: 1 })
+
+        const results = Array.from(query.state.values())
+        expect(results.length).toBeGreaterThan(0)
+        // should match names starting with "Ursula" even if it contains a newline character
+        assertAllItemsMatch(query, (u) => u.name.startsWith(`Ursula`))
+
+        await query.cleanup()
+      })
+
       it(`should filter with like() with lower() function`, async () => {
         const config = await getConfig()
         const usersCollection = config.collections.onDemand.users
diff --git a/packages/db/src/query/compiler/evaluators.ts b/packages/db/src/query/compiler/evaluators.ts
@@ -516,6 +516,7 @@ function evaluateLike(
   regexPattern = regexPattern.replace(/%/g, `.*`) // % matches any sequence
   regexPattern = regexPattern.replace(/_/g, `.`) // _ matches any single char
 
-  const regex = new RegExp(`^${regexPattern}$`)
+  // 's' (dotAll flag) makes '.' match all characters including line terminations
+  const regex = new RegExp(`^${regexPattern}$`, 's')
   return regex.test(searchValue)
 }
diff --git a/packages/db/tests/query/compiler/evaluators.test.ts b/packages/db/tests/query/compiler/evaluators.test.ts
@@ -374,6 +374,23 @@ describe(`evaluators`, () => {
           // In 3-valued logic, ilike with undefined pattern returns UNKNOWN (null)
           expect(compiled({})).toBe(null)
         })
+
+        it(`like % wildcard matches across newline characters`, () => {
+          const func = new Func(`like`, [
+            new Value(`hello\nworld`),
+            new Value(`%world`),
+          ])
+          const compiled = compileExpression(func)
+
+          expect(compiled({})).toBe(true)
+        })
+
+        it(`like _ wildcard matches a newline character`, () => {
+          const func = new Func(`like`, [new Value(`a\nb`), new Value(`a_b`)])
+          const compiled = compileExpression(func)
+
+          expect(compiled({})).toBe(true)
+        })
       })
 
       describe(`comparison operators`, () => {
diff --git a/packages/query-db-collection/e2e/query-filter.ts b/packages/query-db-collection/e2e/query-filter.ts
@@ -556,7 +556,7 @@ function evaluateLike(
   regexPattern = regexPattern.replace(/%/g, `.*`) // % matches any sequence
   regexPattern = regexPattern.replace(/_/g, `.`) // _ matches any single char
 
-  const regex = new RegExp(`^${regexPattern}$`)
+  const regex = new RegExp(`^${regexPattern}$`, 's')
   return regex.test(searchValue)
 }
 

-Original file line number
+Diff line change
@@ @@ -0,0 +1,5 @@ @@
 +---
 +'@tanstack/db': patch
 +---
++
 +Fix like/ilike `%` and `_` not matching newline characters
Original file line number	Diff line number	Diff line change
`@@ -68,6 +68,7 @@ export function generateSeedData(): SeedDataResult {`
`68`	`68`	`Rose ${i}`,
`69`	`69`	`sam ${i}`,
`70`	`70`	`Tina ${i}`,
	`71`	+ `Ursula ${i}\nNewline`,
`71`	`72`	`]`
`72`	`73`	`const name = names[i % names.length]`
`73`	`74`
Original file line number	Diff line number	Diff line change
`@@ -516,6 +516,7 @@ function evaluateLike(`
`516`	`516`	regexPattern = regexPattern.replace(/%/g, `.*`) // % matches any sequence
`517`	`517`	regexPattern = regexPattern.replace(/_/g, `.`) // _ matches any single char
`518`	`518`
`519`		- const regex = new RegExp(`^${regexPattern}$`)
	`519`	`+ // 's' (dotAll flag) makes '.' match all characters including line terminations`
	`520`	+ const regex = new RegExp(`^${regexPattern}$`, 's')
`520`	`521`	`return regex.test(searchValue)`
`521`	`522`	`}`
Original file line number	Diff line number	Diff line change
`@@ -556,7 +556,7 @@ function evaluateLike(`
`556`	`556`	regexPattern = regexPattern.replace(/%/g, `.*`) // % matches any sequence
`557`	`557`	regexPattern = regexPattern.replace(/_/g, `.`) // _ matches any single char
`558`	`558`
`559`		- const regex = new RegExp(`^${regexPattern}$`)
	`559`	+ const regex = new RegExp(`^${regexPattern}$`, 's')
`560`	`560`	`return regex.test(searchValue)`
`561`	`561`	`}`
`562`	`562`