v3.0.0

Added

• PG Free: full v2-pvt query engine reaches Pro-parity (0.5.x → 0.6.1).
  The PostgreSQL Free path got the feature-complete v2-pvt module ahead of
  MSSql Free (commits 2026-05-21 … 2026-05-28). Before this series the Free
  path was emitting -- not available in Open Source stubs for several
  preview surfaces and was missing several Pro-only operators. Now in Free
  on PG:

  • Universal "no black box" SQL preview for GroupBy / Window /
    GroupedWindow / Tree-* via two-pass compile (pvt_build_*_sql);
    tree previews resolve the subtree and delegate to the matching non-tree
    preview with a -- Tree …: subtree resolved to N object(s) header.
  • Sql.Function<T> whitelist at the SQL boundary
    (17_pvt_expr.sql) with a
    hardcoded ELSIF chain and RAISE EXCEPTION for non-whitelisted names;
    parser routes Sql.Function<T>(name, args) to
    CustomFunctionExpression (FREE-OVER-PRO §2.4).
  • ValueTuple composite dict keys (Dictionary<(int,int), V>)
    consistently encoded as Base64-JSON on both write and read sides
    (FREE-OVER-PRO §2.2).
  • arr.Length / coll.Count in filters via the array-aware
    FacetFilterBuilder (.$count modifier in Free); e.Tags.Any()
    1-arg form mapped to <field>.$length > 0.
  • Take(0) returns empty instead of ArgumentException.
  • HAVING parser + ArrayGroupBy with PVT agg array unnest
    (19_pvt_agg_expr.sql) —
    fixes 42883 function sum(bigint[]) does not exist;
    26_pvt_array_groupby.sql
    added.
  • ListItem.Value / .Alias via a single LEFT JOIN _list_items
    (v2-pvt 0.6.1) — plan-shape parity with Pro; replaces correlated
    subquery per field.
  • Nested-dict CTE pushdown for Field[key].Child (FREE-OVER-PRO §2.x):
    outer WHERE references the already-built pivot column instead of a
    redundant EXISTS over _values.
  • Auto-deploy of the v2-pvt bundle on version mismatch (see the
    matching item below — same infrastructure serves both PG and MSSql).

  The MSSql Free engine described next ports this PG Free baseline; the
  parity line in the next item ("145/145 parity with PG Free") refers to
  this newly-completed PG Free feature set, not a pre-existing one.

• MSSql Free: full v2-pvt query engine (0.1.0 → 0.1.3) — 145/145 parity
  with PG Free. The old MSSql Free path generated a wide inline CASE WHEN
  aggregate; it is now replaced with the Pro-shape CTE: a single pass over
  _values using MAX(CASE WHEN _id_structure = X AND _array_index IS NULL THEN ...) and a single LEFT JOIN _list_items. All modes present in PG
  Free are implemented: flat/tree, scalar/array/dict fields, ListItem
  (.Id/.Value/.Alias), same-scheme nested POCO (compound path),
  OrderBy/DistinctBy/Take/Skip, GroupBy/HAVING, ArrayGroupBy
  (via OUTER APPLY), array aggregates ($count, $sum/$avg/$min/$max
  over _Long/_Double/_Numeric/_DateTimeOffset), array operators
  ($arrayContains, $arrayAny, $arrayCount*, $arrayAt,
  $arrayStartsWith, etc.), Sql.Function (whitelist), $expr, null
  semantics ($exists/$notNull). The SQL module is split into 27 source
  files under redb.MSSql/sql/v2-pvt/ assembled
  into a single pvt_bundle.sql by MSBuild. Delivery stages: Stage 1 (pivot
  CTE) → 2a (tree TVFs) → 2b (tree provider) → 2c.E (nested-dict accessor
  Field[key].Child) → 0.1.1 LIKE-pattern fix → 0.1.2 string $const
  unwrap + ListItem $arrayContains → 0.1.3 nested-dict CTE pushdown +
  outer WHERE references pivot column instead of a redundant EXISTS.
  Shape parity with Pro throughout: _id_scheme + extra_where +
  tree-filter pushed into inner _objects subquery, narrow-with-nested CTE
  (skips _values JOIN when no scalar sids), stable default ORDER BY when
  paging without an explicit order.

• Auto-deploy v2-pvt bundle on version mismatch (both databases).
  ISqlDialect gained Query_PvtRequiredVersion() — the semver the embedded
  bundle ships. RedbServiceBase.EnsurePvtModuleDeployedAsync reads
  pvt_module_version() on InitializeAsync(), compares with an exact-match,
  and automatically applies the embedded pvt_bundle.sql resource when the
  deployed version differs. No more manual DROP FUNCTION … CREATE FUNCTION …
  after a SQL change. The MSBuild target ConcatenateSqlFiles regenerates the
  bundle whenever any .sql source changes (hooked to DispatchToInnerBuilds
  for multi-TFM builds; EmbeddedResource uses an explicit LogicalName —
  without it MSBuild silently replaces - with _ in resource paths, causing
  GetManifestResourceStream to return null).

• Pro: GroupBy + HAVING via PVT pipeline on both providers
  (Postgres.Pro + MSSql.Pro). HavingAsync existed in Free but had no Pro
  counterpart. Added full HAVING parser in the shared facet layer
  (FacetFilterBuilder), SQL generation in both Pro providers, and a base
  test suite in
  GroupByHavingTestsBase
  with per-dialect wrappers (PG, PG.Pro, MSSql.Pro). 33/33 HAVING + 6/6
  no-HAVING — all green.

• Pro: GroupBy over array fields (ArrayGroupBy) — unified implementation
  for Postgres.Pro + MSSql.Pro. PG.Pro uses an inline GroupByArray override
  with PVT agg array unnest; MSSql.Pro has its own override.
  GroupBy(items => items.SelectMany(o => o.Skills)) with aggregates works
  on all four tiers (PG Free, PG.Pro, MSSql Free, MSSql.Pro).

• MSSql Pro: AggregateBatch parity with PG.Pro — non-numeric MIN/MAX and
  inline filter subquery. MinAsync/MaxAsync over string/DateTime/Guid
  fields and a Where filter inside a batch aggregation now produce the same
  query shape as PG.Pro (PVT CTE + outer aggregate).

• MSSql Free: pushdown parity with Pro/PG for expression-form predicates
  and $expr — the filter-splitting optimizer
  pvt_split_filter now pushes
  top-level $eq/$ne/$lt/$lte/$gt/$gte/$like/$ilike/$in/$nin/$between/$null/ $notnull/$contains/$startsWith/$endsWith expressions and arbitrary boolean
  $expr trees into the inner _objects o subquery (Shape A) when all
  $field references resolve to kind='base'. If any props field is present
  the node stays in the residual (Shape C). The new classifier
  pvt_expr_is_base_only makes
  this decision; the pushdown SQL itself is generated by the existing
  pvt_build_where_from_json
  walker (extended with a $expr branch). Covered by 4 functional and 3
  shape-inspect tests in
  99_smoke_auto.sql
  (195 PASS / 0 FAIL / 1 SKIP).

Fixed

• Schema sync now honors Configuration.DefaultStrictDeleteExtra
  (FREE-OVER-PRO §4 #1). Prior to this fix RedbServiceConfiguration.DefaultStrictDeleteExtra
  was set by builders, copied across configuration clones and read from
  appsettings, but no execution-path code consumed it —
  SchemeSyncProviderBase.SyncSchemeAsync<T>
  hardcoded strictDeleteExtra: true, so old binaries restarting in a
  multi-version rolling deploy would unconditionally remove _structures
  rows added by the new binary, and every _values row referencing those
  structures along with them. On PostgreSQL this is done via the FK
  _values._id_structure -> _structures._id ON DELETE CASCADE
  (redbPostgre.sql:215). On MSSQL
  the same effect is produced by the INSTEAD OF DELETE trigger
  TR__structures__cascade_values
  (redbMSSQL.sql:717) — the FK
  NO ACTION at redbMSSQL.sql:270
  is a workaround for the MSSQL multiple-cascade-paths restriction, not a
  behavioral difference. SyncSchemeAsync<T> now reads
  now reads Configuration.DefaultStrictDeleteExtra instead. The default
  value is preserved (true) so users on the default config see no
  behavioral change. Behavioral change: the built-in presets
  Development, HighPerformance, and Migration (in
  PredefinedConfigurations.cs)
  already declared DefaultStrictDeleteExtra = false; that setting was
  silently ignored before and now actually takes effect — apps on those
  presets will no longer auto-delete _structures rows missing from the
  Props class on startup.

Added
a fallback to ROW_NUMBER() OVER (PARTITION BY <key> ORDER BY (SELECT 1))

• WHERE _rn = 1 (symmetric with the Free path), plus support for
  CoalesceExpression in the DistinctBy key.

• PG v2-pvt 0.6.1: ListItem .Value/.Alias now uses a single
  LEFT JOIN _list_items instead of a correlated subquery per field —
  plan-shape parity with Pro. Additionally: nested-dict predicates in the
  outer WHERE now reference _pvt_cte.[<field>] (the already-built pivot
  column) instead of re-running a separate EXISTS over _values.

• MSSql Free: ORDER BY $expr on base fields no longer produces "constant
  in ORDER BY" — two regressions fixed: (1)
  pvt_collect_fields
  did not walk $expr nodes in order entries, so a field like Age was not
  collected, the shape was classified as A, and pvt_b2_expr_sql emitted
  /*unknown-b2-field:Age*/NULL turning Age*2 into a constant; (2)
  pvt_build_order_conditions
  passed a trailing-dot alias (_pvt_cte.) into pvt_b2_expr_sql, producing
  the double-dot _pvt_cte..[_name] for base fields inside $expr ORDER.
  Both sites fixed.

• arr.Length / coll.Count in Where filters no longer crash on array
  PVT columns — e.Skills!.Length >= 3 was translated to LENGTH(text[])
  and raised PostgreSQL error 42883.
  BaseFilterExpressionParser
  now emits PropertyFunction.Count (instead of PropertyFunction.Length)
  for CLR UnaryExpression(ArrayLength) nodes. In Pro this produces
  COALESCE(array_length(col,1), 0); in Free,
  FacetFilterBuilder.TryBuildArrayLengthCountFilter
  translates the filter to the PVT modifier .$count. PropertyInfo gained
  an optional FunctionSourceType field so the facet builder can distinguish
  arrays from strings when choosing the modifier. Covered by
  PropertyFunction_ArrayCount_Filters on both tiers.

• Take(0) now returns an empty result instead of ArgumentException —
  validation in RedbQueryable.Take() and TreeQueryableBase.Take() relaxed
  from count <= 0 to count < 0 to match standard LINQ semantics
  (Enumerable.Take(0) → empty). Affects both tiers (Free and Pro), flat and
  tree queries. Covered by Take_Zero_ReturnsEmpty_WithoutThrowing and
  Take_Zero_ReturnsEmpty_OnTreeQuery in PvtAuditTestsBase.

Tests

• PostgresFreePvtAuditTests moved to the shared base
  PvtAuditTestsBase
  and now runs against both PostgresFixture (Free) and PostgresProFixture
  (Pro) — a regression on either tier fails immediately. Added tests for
  Take(0) (flat + tree), Take(-1) (still throws), and DistinctBy on a
  tree query.
• Three audit probes from FREE-OVER-PRO §2.x confirmed working on both
  tiers without any SQL/parser changes — tests were the only missing piece:
  • DictTupleKey_PerformanceReviews_FiltersByCompositeKey (§2.2) —
    ValueTuple dict keys are encoded by RedbKeySerializer to Base64-JSON
    consistently on the write side and in
    BaseFilterExpressionParser L602.
  • ObjectRef_CurrentProject_NotNull_Filters /
    ObjectRef_CurrentProject_IsNull_Filters (§2.3, null-check path) —
    e.CurrentProject != null / == null on RedbObject<T>? fields works
    via $exists / $ne null.
  • SqlFunction_Coalesce_Filters + SqlFunction_UnknownName_ThrowsWhitelistViolation
    (§2.4) — Sql.Function<T>(name, args) is routed by the parser to
    CustomFunctionExpression, FacetFilterBuilder emits
    {"$<funcname>": [...]}, and pvt_build_scalar_expr
    (17_pvt_expr.sql) implements
    the whitelist with a hardcoded ELSIF chain and RAISE EXCEPTION for
    unknown names.
  • Full PG suite (Free + Pro): 328 passed / 0 failed / 2 skipped. The
    two remaining skips are ObjectRef_CurrentProject_NestedField_Filters
    (cross-scheme JOIN path, confirmed broken in both tiers — requires new
    infrastructure in both PVT and ProQueryProvider).
• ListItem .Value/.Alias OrderBy capability gate — PG Free PVT
  sorts by Status.Value/.Alias correctly (on par with Pro); the
  if (IsPro) guard in ListItem_OrderByValue_SortsAlphabetically /
  ListItem_OrderByAlias_SortsAlphabetically was overly conservative.
  Added virtual SupportsListItemValueAliasOrdering (default = IsPro) in
  ListTestsBase; PostgresListTests overrides to true. Result: PG Free +
  PG Pro + MsSql Pro pass with strict ordering; MsSql Free remains gated
  (insertion-order only — ORDER BY on a JSON expression is ignored).

Documentation

• New section "Schema lifecycle and multi-version deployments" in the
  root README.md: documents read=graceful / write=destructive,
  the services.AddRedb(... .Configure(c => c.DefaultStrictDeleteExtra = false))
  opt-out, the new warning log, and the equivalent cascade semantics across
  backends \u2014 PostgreSQL uses FK ON DELETE CASCADE on
  _values._id_structure, while MSSQL achieves the same effect through the
  TR__structures__cascade_values INSTEAD OF DELETE trigger (MSSQL FK is
  NO ACTION only to work around the multiple-cascade-paths restriction).
• Rewrote docs/FreePvtQuery/FREE-OVER-PRO.md
  §4: marked F0+F1 (this release) as done, demoted F3 (default flip) to a
  major-version task, made the cache-state-dependent nature of the Pro
  ChangeTracking destructiveness explicit (per-instance cache refresh window),
  and corrected §4.1 — the previous "obligatory DefaultStrictDeleteExtra = false"
  guidance was non-functional before v2.0.3 and is now actually wired.
• Updated docs/FreePvtQuery/FREE-OVER-PRO.md:
  H1 (Take(0)) marked fixed; H8 (tree DistinctBy) re-classified as
  already implemented in both tiers; §0 and §2.x updated for §2.2 /
  §2.3-null / §2.4 closures; §1 #5 (Sql.Function) no longer marked
  unimplemented; §2 #3, #5, #6 marked done; added §2 #6b (deferred
  nested-field cross-scheme JOIN — confirmed as a two-sided gap in Free PVT
  and Pro SchemeFieldResolver); added §3 #11 (MsSql Free ignores
  OrderBy(Status.Value)/.Alias).