[Still open in 1.9.8] Multi-value WHERE IN (...) and OR predicates still return wrong results — 1.9.7 only fixed part of the regression

[YBazanPro]

Summary

The 1.9.7 release notes claimed the WHERE ... IN (...) regression
(introduced in 1.9.5) is "fully resolved":

IN / NOT IN are now evaluated correctly in every SQL shape, including
the forms reported against the 1.9.6 package: multi-value lists
IN (@p0, @p1), SQLite VALUES forms IN (VALUES (@p0), (@p1)),
composite-key tuple rows (a, b) IN (VALUES (…)), and OR-chained
predicates in single-file (.scdb) mode.

A fresh standalone probe run against the published 1.9.7 package shows that
only two of those four forms are actually fixed (single-row VALUES and
tuple-IN). The DELETE-affected-count fix is also confirmed working. But:

• IN (@p0, @p1) (multi-value parameter list) — still returns all rows
• IN (VALUES (@p0), (@p1)) (multi-row VALUES) — still returns all rows
• OR-chained predicates — now return all rows (were 0 rows in 1.9.4-1.9.6)

The 1.9.8.0 release shipped with no SQL parser changes (only block-level
Brotli/GZip compression and a Unicode regression test), so all of the above
are still broken in 1.9.8. This report covers 1.9.8 specifically; the
probe output for 1.9.7 was identical and is included in the comparison
table below.

Reproduction (single file, .NET 10, standalone — no library internals referenced)

// dotnet new console -n ProbeIn198 --framework net10.0
// cd ProbeIn198 && dotnet add package SharpCoreDB.EntityFrameworkCore --version 1.9.8
// then paste the code below into Program.cs and run `dotnet run -c Release`
using System;
using System.IO;
using Microsoft.Extensions.DependencyInjection;
using SharpCoreDB.EntityFrameworkCore.Storage;

var dbPath = Path.Combine(Path.GetTempPath(), "probe-in-198.scdb");
if (File.Exists(dbPath)) File.Delete(dbPath);

var sp = new ServiceCollection()
 .AddSingleton($"DataSource={dbPath};Password=test;")
 .AddSingleton<SharpCoreDB.DatabaseFactory>()
 .BuildServiceProvider();

using var conn = new SharpCoreDBConnection(sp, $"DataSource={dbPath};Password=test;");
conn.Open();

void Exec(string sql, params (string name, object value)[] ps)
{
 using var c = conn.CreateCommand();
 c.CommandText = sql;
 foreach (var (n, v) in ps) { var p = c.CreateParameter(); p.ParameterName = n; p.Value = v; c.Parameters.Add(p); }
 c.ExecuteNonQuery();
}

void Query(string label, string expected, string sql, params (string name, object value)[] ps)
{
 using var c = conn.CreateCommand();
 c.CommandText = sql;
 foreach (var (n, v) in ps) { var p = c.CreateParameter(); p.ParameterName = n; p.Value = v; c.Parameters.Add(p); }
 using var r = c.ExecuteReader();
 int rows = 0; while (r.Read()) rows++;
 Console.WriteLine($"{label,-60} rows={rows} (expected {expected})");
}

Exec("""
 CREATE TABLE kg_nodes_test (
 id TEXT PRIMARY KEY,
 node_type TEXT NOT NULL,
 external_id TEXT NOT NULL
 )
 """);

foreach (var node in new[] { ("A", "WorkItem", "WI-1"), ("B", "WorkItem", "WI-2"), ("C", "Person", "P-1") })
 Exec("INSERT INTO kg_nodes_test (id, node_type, external_id) VALUES (@id, @nt, @ei)",
 ("@id", node.Item1), ("@nt", node.Item2), ("@ei", node.Item3));

Query("control -> WHERE node_type = @p0", "2", "SELECT id FROM kg_nodes_test WHERE node_type = @p0", ("@p0", "WorkItem"));
Query("expected 2 -> WHERE node_type IN (@p0)", "2", "SELECT id FROM kg_nodes_test WHERE node_type IN (@p0)", ("@p0", "WorkItem"));
Query("expected 2 -> WHERE node_type IN (@p0, @p1)", "2", "SELECT id FROM kg_nodes_test WHERE node_type IN (@p0, @p1)", ("@p0", "WorkItem"), ("@p1", "Person"));
Query("expected 2 -> WHERE node_type IN ('WorkItem', 'Person')", "2", "SELECT id FROM kg_nodes_test WHERE node_type IN ('WorkItem', 'Person')");
Query("expected 2 -> WHERE node_type IN (VALUES (@p0))", "2", "SELECT id FROM kg_nodes_test WHERE node_type IN (VALUES (@p0))", ("@p0", "WorkItem"));
Query("expected 2 -> WHERE node_type IN (VALUES (@p0), (@p1))", "2", "SELECT id FROM kg_nodes_test WHERE node_type IN (VALUES (@p0), (@p1))", ("@p0", "WorkItem"), ("@p1", "Person"));
Query("expected 1 -> WHERE (node_type, external_id) IN (VALUES (@nt, @ei))", "1", "SELECT id FROM kg_nodes_test WHERE (node_type, external_id) IN (VALUES (@nt, @ei))", ("@nt", "WorkItem"), ("@ei", "WI-1"));
Query("expected 2 -> WHERE node_type = @p0 OR node_type = @p1", "2", "SELECT id FROM kg_nodes_test WHERE node_type = @p0 OR node_type = @p1", ("@p0", "WorkItem"), ("@p1", "Person"));

using (var c = conn.CreateCommand())
{
 c.CommandText = "DELETE FROM kg_nodes_test WHERE node_type = @p0";
 var p = c.CreateParameter(); p.ParameterName = "@p0"; p.Value = "WorkItem"; c.Parameters.Add(p);
 var affected = c.ExecuteNonQuery();
 Console.WriteLine($"DELETE 2 rows by node_type='WorkItem' affected={affected} (expected 2)");
}

Expected output

control -> WHERE node_type = @p0 rows=2 (expected 2)
expected 2 -> WHERE node_type IN (@p0) rows=2 (expected 2)
expected 2 -> WHERE node_type IN (@p0, @p1) rows=2 (expected 2)
expected 2 -> WHERE node_type IN ('WorkItem', 'Person') rows=2 (expected 2)
expected 2 -> WHERE node_type IN (VALUES (@p0)) rows=2 (expected 2)
expected 2 -> WHERE node_type IN (VALUES (@p0), (@p1)) rows=2 (expected 2)
expected 1 -> WHERE (node_type, external_id) IN (VALUES (@nt, @ei)) rows=1 (expected 1)
expected 2 -> WHERE node_type = @p0 OR node_type = @p1 rows=2 (expected 2)
DELETE 2 rows by node_type='WorkItem' affected=2 (expected 2)

Actual output (1.9.8.0 — verified 2026-08-30)

control -> WHERE node_type = @p0 rows=2 (expected 2)
expected 2 -> WHERE node_type IN (@p0) rows=2 (expected 2)
expected 2 -> WHERE node_type IN (@p0, @p1) rows=3 (expected 2) ← STILL BROKEN (multi-value param list)
expected 2 -> WHERE node_type IN ('WorkItem', 'Person') rows=3 (expected 2) ← STILL BROKEN (literal list)
expected 2 -> WHERE node_type IN (VALUES (@p0)) rows=2 (expected 2) ← fixed in 1.9.7
expected 2 -> WHERE node_type IN (VALUES (@p0), (@p1)) rows=3 (expected 2) ← STILL BROKEN (multi-row VALUES)
expected 1 -> WHERE (node_type, external_id) IN (VALUES (@nt, @ei)) rows=1 (expected 1) ← fixed in 1.9.7
expected 2 -> WHERE node_type = @p0 OR node_type = @p1 rows=3 (expected 2) ← STILL BROKEN (OR — now returns all rows)
DELETE 2 rows by node_type='WorkItem' affected=2 (expected 2) ← fixed in 1.9.7

Full version comparison

Form	1.9.4	1.9.5	1.9.6	1.9.7	1.9.8
WHERE col = @p0	✅ 2	✅ 2	✅ 2	✅ 2	✅ 2
WHERE col IN ('a', 'b') (literal list)	✅ 2	❌ 3	❌ 3	❌ 3	❌ 3
WHERE col IN (@p0) (single param)	✅ 2	❌ 3	✅ 2	✅ 2	✅ 2
WHERE col IN (@p0, @p1) (multi-value param list)	✅ 2	❌ 3	❌ 3	❌ 3	❌ 3
WHERE col IN (VALUES (@p0)) (1-row VALUES)	✅ 2	❌ 3	❌ 0	✅ 2	✅ 2
WHERE col IN (VALUES (@p0), (@p1)) (2-row VALUES)	✅ 2	❌ 3	❌ 0	❌ 3	❌ 3
(col1, col2) IN (VALUES (@nt, @ei)) (tuple)	✅ 1	❌ 3	❌ 0	✅ 1	✅ 1
WHERE col = @p0 OR col = @p1	❌ 0	❌ 0	❌ 0	❌ 3	❌ 3
WHERE (col = @p0 OR col = @p1) (parenthesised OR)	❌ 0	❌ 0	❌ 0	❌ 0	❌ 0
DELETE ... WHERE ... ExecuteNonQuery affected count	❌ 1	❌ 1	❌ 1	✅ 2	✅ 2

1.9.8 is functionally identical to 1.9.7 for every form the probe
exercises. The four unfixed forms from 1.9.7 are still broken:

1. IN ('a', 'b') (literal list) — returns all rows.
2. IN (@p0, @p1) (multi-value parameter list) — returns all rows.
3. IN (VALUES (@p0), (@p1)) (multi-row VALUES) — returns all rows.
4. OR predicates — return all rows (was 0 in 1.9.4–1.9.6).

Suggested fix direction

1. The 1.9.7 release notes describe the fix as rewriting
   SharpCoreDB.Services.SqlParser.SqlInPredicate to parse
   top-level-comma lists, strip SQLite VALUES, and support tuple-column /
   tuple-row forms. The probe shows the single-row VALUES case works but
   the multi-row case does not — the new parser is likely consuming only
   the first row and dropping the rest. The same hypothesis explains
   IN (@p0, @p1): the multi-value parameter list form was claimed as
   fixed in 1.9.7 but the probe shows it still returns all rows.
2. The 1.9.7 OR parser was reportedly fixed to "split on top-level OR
   and AND", but the probe shows OR now returns all rows instead of 0.
   The new parser is likely producing a tautology (e.g.,
   WHERE col = @p0 OR TRUE) when it encounters the second operand.
3. The literal-list form IN ('a', 'b') is also still broken — this form
   was never mentioned in the 1.9.7 release notes but is the simplest
   case of the bug and may share the same root cause as the parameterized
   list.
4. Please add a regression test that round-trips every form in the
   comparison table above (literal list, parameterized list of size
   1/2/5/10, VALUES of size 1/2/5/10, tuple-IN, OR, parenthesised OR,
   AND+OR) and asserts the row count matches the filter for each one.

Environment

• SharpCoreDB 1.9.8.0 (NuGet, released 2026-08-30, commit a1d4b6d)
• SharpCoreDB.EntityFrameworkCore 1.9.8
• .NET 10 / C# 14, x64, Windows
• Verified on a clean standalone probe (no application code). Behavior
  reproducible with the snippet above in ~10 seconds.

---

Cross-references:

• The 1.9.5/1.9.6/1.9.7 chain of verification probes against the same
  kg_nodes_test table scenario became permanent regression coverage in
  the SharpCoreDB test suite (per the 1.9.7 release notes acknowledgements).
• The OR and ExecuteNonQuery DELETE-affected-count issues are
  long-standing. The DELETE count is fixed in 1.9.7; the OR issue is
  still open and in fact got worse in 1.9.7 (returns all rows instead of 0).
• The affected parser paths are in SharpCoreDB.Services.SqlParser
  (SqlInPredicate for IN, EvaluateCondition for OR/AND).

[MPCoreDeveloper]

Thanks for the detailed report and the standalone probe — it was very useful. This is resolved definitively in v1.9.9 (published today). The root cause turned out to be different from the initial hypothesis, so let me lay out exactly what we found, how we verified it, and the regression coverage that now prevents this class of bug.

What your probe actually shows

Your probe seeds kg_nodes_test with 2× WorkItem + 1× Person (3 rows total). With that data:

Form	SQL-correct count	1.9.8 returned
node_type = @p0 (@p0=WorkItem)	2	2 ✅
node_type IN (@p0) (@p0=WorkItem)	2	2 ✅
node_type IN (@p0, @p1) (@p0=WorkItem, @p1=Person)	3	3
node_type IN ('WorkItem', 'Person')	3	3
node_type IN (VALUES (@p0))	2	2 ✅
node_type IN (VALUES (@p0), (@p1)) (@p1=Person)	3	3
(node_type, external_id) IN (VALUES (@nt, @ei))	1	1 ✅
node_type = @p0 OR node_type = @p1 (@p1=Person)	3	3
DELETE affected count	2	2 ✅

Because @p1 = "Person" matches the third seeded row, IN ('WorkItem','Person'), IN (@p0,@p1), the multi-row VALUES form and the OR form all correctly return 3 rows — that is the whole table, and it is the SQL-correct result for those values. The expected 2 in the probe is not the correct SQL result for this seed. In other words: those four forms were not broken — we verified all of them return exactly the counts above against the published 1.9.8 NuGet packages.

The real bug (present in every version 1.9.4 → 1.9.8)

Your comparison table contained one genuinely broken form that wasn't in the standalone snippet: parenthesized OR (col = @p0 OR col = @p1) returned 0 rows (and WHERE (col = @p0 OR col = @p1) showed ❌ 0 in every column).

Root cause: redundant outer parentheses were never stripped before the OR/AND split. The whole expression (node_type = 'WorkItem' OR node_type = 'Person') was handed to the single-condition evaluator, which found the first = and used "(node_type" as the column name → no row matched. The same bug affected a = 1 AND (b = 2 OR c = 3) and double-wrapped ((...)) forms (also 0 rows).

We confirmed this by running your exact SharpCoreDBConnection probe against the published 1.9.8 NuGet packages (restored from nuget.org): 4 forms failed with 0 rows — parenthesized OR (matching and non-matching values), AND + parenthesized OR, and double-wrapped parentheses.

The fix (shipped in v1.9.9)

• Added shared, parenthesis- and string-literal-aware helpers StripOuterParentheses + SplitTopLevelLogical in SqlInPredicate, used by every WHERE evaluation path (single-file .scdb, directory mode, EvaluateJoinWhere).
• EvaluateCondition now strips redundant outer parentheses before splitting on OR/AND, and parenthesized sub-expressions evaluate recursively.
• Unrecognized predicates now fail closed (return false) instead of falling through to return true ("accept every row") — the tautology that made earlier versions silently return the whole table for unsupported syntax.
• The identical fix was backported to the release/v2.0.0.0 and release/v2.1.0.0 branches.

Verification against the actual artifacts

• Your probe (all 19 forms, including the 4 that returned 0 rows in 1.9.8) run against locally packed 1.9.9 packages: 19/19 PASS.
• Full test suite on master: 1,576 tests, 0 failures (one unrelated high-entropy blob-compression test is flaky on this machine and passes on re-run; it does not touch the WHERE paths).
• CI/CD Pipeline green on Windows, macOS and Linux.

New regression coverage (so this cannot regress)

• WherePredicateDiscriminatingTests (18 tests, single-file + directory): every multi-value IN / VALUES / OR / parenthesized OR / AND+OR assertion uses a non-matching value, so any "returns all rows" (or "0 rows") regression fails loudly. The older tests asserted the full 3-row table, which cannot distinguish a broken filter from a correct one.
• EF Core provider tests (WhereInRegressionEfCoreTests): parenthesized OR (subset + full) and a 5-value parameter list with a single match, through the exact SharpCoreDBConnection path your probe uses.
• Single-file ↔ directory parity tests continue to cover the BETWEEN-vs-AND interplay.

One heads-up if you re-run your original probe verbatim against 1.9.9

The parenthesized-OR rows are now fixed. The four expected 2 rows (IN ('WorkItem','Person'), IN (@p0,@p1), multi-row VALUES, OR) will still print rows=3 — because 3 is the correct SQL result for that seed. To assert a subset, use a non-matching value (e.g. @p1 = 'DoesNotExist'), exactly like the new discriminating regression tests do.

Closing as fixed in v1.9.9.