Cross Provider Parity

Cross-Provider Parity

The claim QueryForge makes is that the same Query returns the same QueryResult<T> from every provider, against the same data. This page states what that guarantees, how it is enforced, and — importantly — where it does not hold.

---

What is guaranteed

Given identical data and an identical Query, every provider returns:

	Guarantee
Row set	the same rows match
Row order	the same order, provided SortColumns is total (see below)
Null placement	nulls first ascending, last descending, on every engine
Meta.Total.Rows	the same count — matching rows when flat, distinct outermost keys when grouped
Meta.Total.Pages	the same ceil(total / size)
Meta.Type	the same shape
Group keys	the same keys, in the same order, at every level
Group counts	the same Count at every node
Nesting	the same depth and structure
Leaf item order	the same, following SortColumns
Dropped inputs	the same conditions, sorts, groupings and projections are ignored
Type coercion	"30" against an integer column behaves the same everywhere

That covers the whole result contract, which is the point: a caller can switch providers without changing a line of consuming code, and a test written against the in-memory provider is meaningful about the database one.

How it is enforced

Two shared suites run against every provider on every build. They are inherited by each provider's test project rather than duplicated, so a behaviour change shows up on all of them at once instead of drifting apart quietly.

• QueryForgeConformanceTests — 90 tests taking each input apart: Criteria (all eleven operators, nulls, unusable conditions, all four logic modes, multiple groups), Paging (windows, page counts, past-the-end, non-positive values), SelectColumns, SortColumns (multi-level, null placement, numeric-not-lexical) and GroupByColumns (one to three levels, null keys, group paging), flat and grouped.
• SalesScenarioTests — 35 tests posting whole business requests against a seeded order book of 36 orders across two years, nine countries, four statuses and three sales reps: a dashboard, a drill-down grid, an export, a search box, a client JSON payload posted verbatim.

Every expected value was derived from the seed data independently of any engine, so a wrong answer fails rather than being re-asserted.

Both providers run those suites against real database servers — SQLite, PostgreSQL, MySQL, SQL Server and Oracle — not just asserted SQL text. See Testing.

What real-database testing actually found

Three divergences that SQL-text assertions could not have caught. All three are fixed, and they are the reason the guarantees above are stated as guarantees.

1. Loosely-typed filter values

A JSON client sending "value": "30" against an integer column produced operator does not exist: integer > text on PostgreSQL. SQLite and MySQL coerce silently, so the bug was invisible until a strict engine saw it.

Fixed by discovering each column's real type from the result set and coercing the value before binding it. See Query Semantics.

2. Null ordering

PostgreSQL and Oracle sort nulls last ascending; SQL Server, MySQL and SQLite sort them first. The same query returned rows in a different order depending on the database.

Fixed by standardising on nulls-first-ascending and emitting an explicit clause where the engine's default differs. EF Core had the same problem for the same reason — it defers ORDER BY to the database — and is fixed with an explicit null-rank ordering key.

3. Derived table aliases

The group-count statement was emitted as FROM (…) AS qf_groups. Oracle rejects AS before a table alias, so every grouped query would have failed there.

Fixed by emitting the bare alias, which is valid on all five engines.

---

Known divergences

These are real and are not fixed, because they are properties of the databases rather than of QueryForge. Each is listed with what to do about it.

String comparison is the column's collation

Provider	Behaviour
In-Memory	always OrdinalIgnoreCase
Dapper, EF Core	whatever the column's collation says

On a case-insensitive collation — SQL Server's default, MySQL's default — the three agree. On a case-sensitive collation, or on PostgreSQL, which is case-sensitive by default, the database is stricter than the in-memory provider.

What to do: if case-insensitive matching is part of your contract, set it on the column's collation rather than relying on the provider. Do not let a test that passes in memory be your only evidence for a case-sensitive database.

Oracle treats empty strings as NULL

'' and NULL are the same value in Oracle. A filter for Equals "" behaves as IS NULL there and as an empty-string match everywhere else, and a column storing "" reads back as null.

What to do: nothing can be done in the library. Avoid storing empty strings on Oracle, or normalize them to null everywhere so the behaviour is uniform.

Floating-point representation

float/double columns can differ in the last bits between engines, and between a database and .NET. Equality comparisons on floating-point values are not portable.

What to do: use decimal for money and any value equality is applied to.

Date and time precision

Engines differ in what they store: SQL Server datetime2 keeps 100ns, MySQL DATETIME keeps seconds unless you ask for fractional precision, Oracle TIMESTAMP keeps 6 digits by default.

What to do: an exact-equality filter on a timestamp is fragile on any database. Use Between with an explicit window.

Unstable ordering without a total sort

A result whose SortColumns does not uniquely determine an order can return rows in a different order on different engines — and on different calls to the same engine. Paging is then unstable: a row can appear on two pages or on none.

This is not a QueryForge behaviour; it is how databases work. On SQL Server the paging clause requires an ORDER BY, so the dialect emits ORDER BY (SELECT NULL) when nothing else sorts — that satisfies the syntax without imposing an order.

What to do: always include a unique column as the last sort term.

.Sort(new SortDescriptor("Score", SortOrder.Descending),
      new SortDescriptor("UserId"))               // the tie-breaker that makes paging stable

Stored-procedure filtering happens in the application

For DapperObjectType.SP, the rows are materialized and then filtered, sorted, paged and grouped in memory. The QueryResult<T> is identical to what the SQL path would produce — the parity guarantee holds — but the data transfer does not, and the in-memory divergences above (string case in particular) apply instead of the database's.

What to do: prefer a view or table-valued function when the result set is large, or when case-sensitivity matters.

---

Testing parity in your own application

The most useful test you can write against QueryForge is a parity test over your own data:

[Fact]
public async Task Same_query_same_answer()
{
    var query = QueryBuilder.New()
        .Where(SomeRealisticCriteria)
        .Sort(new SortDescriptor("Score", SortOrder.Descending),
              new SortDescriptor("UserId"))
        .Page(10, 2)
        .Build();

    var fromDatabase = await db.Users.AsNoTracking().ToQueryResultAsync<User>(query);
    var fromMemory   = SeedData.Users.ToQueryResult(query);

    Assert.Equal(fromMemory.Meta.Total.Rows, fromDatabase.Meta.Total.Rows);
    Assert.Equal(
        fromMemory.Models.Select(u => u.UserId),
        fromDatabase.Models.Select(u => u.UserId));
}

Note the unique tie-breaker in the sort — without it the test is flaky for the reason described above, and the flakiness is real rather than a test artefact.

---

Adding a provider

A new execution provider is "correct" when it passes both shared suites unchanged. That is the definition, and it is why the suites live in a separate project (PepperX.QueryForge.Conformance) that is not itself a test project — it is inherited.

Adding one means writing a subclass of each suite and supplying a RunAsync. See Extending QueryForge and Testing.