[Regression still present] SharpCoreDb 1.9.6 does not fix the WHERE col IN (...) regression introduced in 1.9.5

[YBazanPro]

Summary

This is a follow-up to the
WHERE col IN (...) regression reported against V1.9.5.
That issue was tentatively marked as fixed when SharpCoreDb 1.9.6 shipped
on 2026-08, but a verification probe run against the published 1.9.6 package
on 2026-08-29 shows the regression is still present in every form that
matters for real applications.

The parser still consumes the IN keyword without applying any filter for
multi-value lists. The single-element form WHERE col IN (@p0) happens to
return the correct count, but that form is functionally identical to
WHERE col = @p0 — it does not indicate a fix.

Reproduction (single file, .NET 10, standalone)

// dotnet new console -n ProbeIn196 --framework net10.0
// cd ProbeIn196 && dotnet add package SharpCoreDB.EntityFrameworkCore --version 1.9.6
// 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-196.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 (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"));

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 (matches 1.9.3 / 1.9.4)

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 (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)
DELETE 2 rows by node_type='WorkItem'                                   affected=2 (expected 2)

Actual output (1.9.6 — verified 2026-08-29)

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)  ← REGRESSION
expected 2  -> WHERE node_type IN (VALUES (@p0))                        rows=0 (expected 2)  ← REGRESSION (new failure mode)
expected 2  -> WHERE node_type IN (VALUES (@p0), (@p1))                 rows=0 (expected 2)  ← REGRESSION (new failure mode)
expected 1  -> WHERE (node_type, external_id) IN (VALUES (@nt, @ei))    rows=0 (expected 1)  ← REGRESSION (new failure mode)
DELETE 2 rows by node_type='WorkItem'                                   affected=1 (expected 2)  ← REGRESSION

Comparison vs. 1.9.5

Form	1.9.5	1.9.6	Verdict
WHERE col = @p0	✅ 2	✅ 2	unchanged
WHERE col IN (@p0) (1 param)	❌ 3 (all rows)	✅ 2	looks fixed but is functionally =
WHERE col IN (@p0, @p1) (2 params)	❌ 3	❌ 3 (all rows)	still broken
WHERE col IN (VALUES (@p0))	❌ 3	❌ 0	regressed differently — now returns 0 instead of all rows
WHERE col IN (VALUES (@p0), (@p1))	❌ 3	❌ 0	regressed differently
(col1, col2) IN (VALUES (@nt, @ei)) (tuple)	❌ 3	❌ 0	regressed differently
WHERE col = @p0 OR col = @p1	❌ 0	❌ 0	unchanged (still 0)
DELETE ... WHERE ... ExecuteNonQuery affected count	❌ always 1	❌ always 1	unchanged

In other words, 1.9.6 made the IN (VALUES (@p0)) family of forms worse —
they now return 0 rows instead of all rows. The IN (@p0, @p1) family still
returns all rows. The behavior is inconsistent across the multi-value IN
shapes.

Impact

Forge (a .NET 10 / C# 14 application with a Knowledge Graph store backed by
SharpCoreDB) cannot use any batched IN lookup — every multi-value batch
becomes an O(N) per-row round trip, with hundreds of single-column =
queries per call for typical workloads. Our test suite currently passes
110 / 119 tests on 1.9.6 (identical to 1.9.5); the 9 unimprovable cases are
either blocked by this regression or by the persistent OR /
ExecuteNonQuery issues that were already filed in the original report.

We are blocked from:

1. Reverting the per-row SELECT workarounds in SharpCoreDbKnowledgeGraphStore.
2. Implementing a join-based RemoveIncidentEdges optimization that needs
   WHERE source IN (...) OR target IN (...) to filter.
3. Implementing a GraphSearch query API that uses tuple-IN to match
   composite node keys.

Suggested fix direction

1. The 1.9.6 parser seems to have a partial fix in place for
   IN (@p0) (single-element, equivalent to =). It's likely that the
   IN-handling path was rewritten to consume the comma-separated list as
   if it were a sequence of assignments (e.g., @p0, @p1, …) and the
   single-element case happens to leave one of those assignments intact. The
   multi-element case is then either silently skipping the predicate
   (IN (@p0, @p1)) or stopping early when it encounters the second
   VALUES row (IN (VALUES (@p0), (@p1))).
2. Add a regression test that round-trips IN queries with parameterized
   lists of size 1, 2, 5, and 10, both literal and VALUES-shaped, and
   asserts the row count matches the filter.
3. While in the same area, please also address the persistent OR and
   ExecuteNonQuery DELETE-affected-count issues from V1.9.4 — they remain
   in V1.9.6 unchanged.

Environment

• SharpCoreDB 1.9.6 (NuGet, released 2026-08)
• SharpCoreDB.EntityFrameworkCore 1.9.6
• SharpCoreDB.Graph 1.9.6 and SharpCoreDB.Graph.Advanced 1.9.6
• .NET 10 / C# 14, x64, Windows
• Verified on a clean standalone probe. Behavior
  reproducible with the snippet above in ~10 seconds.

[MPCoreDeveloper]

Thank you for the meticulous probe against the published 1.9.6 package — the exact SharpCoreDBConnection + .scdb repro made this one straightforward to verify. This is now fixed on master and ships in v1.9.7.

Root cause

The shared SqlInPredicate parser split IN value lists naively on every comma, so it could not handle the SQLite VALUES keyword, parenthesized tuple rows, or multi-value parameter lists — and the single-file (EvaluateCondition) evaluator only split on the literal string " AND ", so OR-chained predicates collapsed into one bogus comparison that returned 0 rows. The affected-row count was also hardcoded (-1/1) on every ExecuteNonQuery.

What changed (v1.9.7)

• IN / NOT IN lists are now parsed with top-level-comma awareness (respects parentheses and strings), strips SQLite VALUES, and supports tuple-column / tuple-row forms (a, b) IN (VALUES (…)) — used by both single-file and directory scan paths.
• SingleFileTable.EvaluateCondition now splits on top-level OR and AND.
• ExecuteNonQuery (ADO.NET provider and EF Core provider) now returns the real affected-row count via the new IDatabase.GetLastChanges() (SQLite changes() parity) — your DELETE 2 rows by node_type='WorkItem' probe now reports 2.

Every shape in your probe is covered by regression tests (WhereInRegressionTests + WhereInRegressionEfCoreTests, the latter reproducing your exact connection string + single-file scenario): IN (@p0), IN (@p0, @p1), IN (VALUES (@p0)), IN (VALUES (@p0), (@p1)), (node_type, external_id) IN (VALUES (@nt, @ei)), NOT IN, and the DELETE affected count. Validation: 1,521/1,521 core + 114/114 EF Core tests.

One small note on the probe's expectations: with your dataset (two WorkItem rows + one Person row), WHERE node_type IN (@p0='WorkItem', @p1='Person') legitimately matches all 3 rows — both values are present in the table, so 3 is the correct set-union result and would be returned by any correct engine. The discriminating cases (a proper subset such as IN ('WorkItem') → 2 of 3, the VALUES forms, and the tuple form) are what now prove the filter actually filters.

SharpCoreDB v2.0 — what's in it for this case

The next major line (in active development on release/v2.0.0.0) rewrites the storage engine: the PageManager now handles record growth/relocation without the previous silent-drop bug, engine Update returns the new storage reference, and UPDATE/DELETE paths are being rebuilt around fixed column offsets and key-only index cleanup — expect measurable gains for write-heavy and UPDATE/DELETE workloads. The #340/#341 fixes are also carried forward into that tree.

Thank you again for the detailed report — the discriminating test cases you provided are now permanent regression coverage.

— MPCoreDeveloper

[YBazanPro]

Hi, maybe I'm wrong, but I don't see any commit nor 1.9.7 release related to this ticket.

[MPCoreDeveloper]

Hello

https://www.nuget.org/profiles/MPCoreDeveloper

1.9.7 is released

Note that The fix and nuget automation are different ..... When I fix an issue and performed the tests it is closed for me .... and I reply to you as a user , however then the pipeline starts running and only when you see the checkmark everything is automatically submitted to nuget
now with Sonar this takes some more time , also note that some packages are dependent on each other and Nuget takes some time to refresh the cache globally ( I am in Europe Netherlands ) ....

Sometimes .... a pipe fails ( mostly the IOS or Ubuntu pipe ) and I have some minor fixing to do to get it working ... This was now not the case I only had a small Sonar Issue and when it occurred I was doing groceries .

Pease note I am very sorry this issue occurred and feel a bit ashamed , I even use the use your own dogfood principle ( this is where I created SharpCoreDB for ) and never encountered it myself ....

But having said so I am fully committed to this project in the evening and weekend hours when I can I solve things fast ( I hope ) as I really like the concept of this project ...

V 2 ( coming soon ) Wil be fully backwards compatible and will have a enormous speed boost ..... ( I am thinking of a preview release , Interested? ) ..... This version Is already , ready for Net 11 C# 15 it wil be a small swap in November