Translate value set Intersect, Except, Add and a canonical Union for the non-text families with composed SQL #478

Problem

Value set Intersect, Except and Add run in memory only, and a server-side Union is array_cat, which ValueSetSqlGuard allows only under @>/<@/&&, a null test or the final projection. #407 records why: filtering the left array keeps its order and duplicates, so the result is canonical only when the input is, and Add would have to sort in SQL.

The rule against sorting in SQL exists because of text: ORDER BY follows the database collation, not ordinal order. For the other ten families it doesn't apply. int2, int4, int8, numeric, float4, float8, uuid, date, time, timestamp and timestamptz all have one order, the same in PostgreSQL and .NET (uuid to be confirmed, below). So for them canonical form can be established in plain SQL, with no custom function and no migration.

Change

Translate each operation to one canonicalizing expression over the combined or filtered elements:

ARRAY(SELECT DISTINCT ON (x) x FROM unnest(…) WITH ORDINALITY AS t(x, n) ORDER BY x, n)

• Union canonicalizes array_cat(a, b) in that expression. The simple form (SELECT DISTINCT … ORDER BY) gave {a,b,c} for ARRAY['b','a'] ∪ ARRAY['a','c'] on PostgreSQL 16.
• Add is Union with one element.
• Intersect, Except filter unnest(a) by b @> ARRAY[x] / NOT (b @> ARRAY[x]) inside the same expression. Not = ANY(b) / <> ALL(b): Npgsql's ANY node requires an IEnumerable CLR type, which a value set deliberately isn't, and containment answers false rather than NULL against a NULL element (below).
• ORDER BY x, n keeps the first representation, which is memory's rule: DecimalSet keeps whichever of 1.0 and 1.00 came first, and Float64Set/Float32Set whichever of -0 and 0. A plain DISTINCT keeps either. ValueSetCore sorts stably and keeps the left operand's copy in Union, Intersect and Except, so the ordinality over array_cat(a, b) gives the same copy. Measured on PostgreSQL 16: {1.00,2} ∪ {1.0,2.000} gives {1.00,2}, and {-0,1} ∪ {0} gives {-0,1}.
• The guard builds the expression, and still fails closed (decided by the spike, below). The translator emits one placeholder function per operation (valueranges_canonical_union, _add, _intersect, _except) inside the existing NULL-over-NULL CASE (NullOverNullOperand). The guard expands each into the canonical subquery. Where the consumer ignores order and duplicates, it strips a union or an addition to bare array_cat instead: @>, <@ and && in the spike; cardinality(…) = 0, a null test and the outermost projection, which materialization canonicalizes, still to do. Without the guard a placeholder reaches PostgreSQL as an unknown function, as UnguardedUnion does today. The string-backed families keep UnguardedUnion and today's refusals.
• A per-family sort setting. Build the expression from a property of the set's definition: none means the family can't sort in SQL and keeps refusing, which is text on 10.x. Then Decide the canonical order of string-backed value sets for 11.0.0: code point order, which PostgreSQL can sort, or ordinal #479's option 1 only sets COLLATE "C" on the string families' registration.

How the SQL is built (spike, 2026-10-26)

Settled by a spike on local branches spike/478-canonical-array-10x (EF Core 10.0.0, Npgsql 10.0.0) and spike/478-canonical-array-main (EF Core 11.0.0-rc.2, Npgsql 11.0.0-rc.2). Each holds the prototype and the harness under spike/478/.

• Not at translation time. A method translator gets no SqlAliasManager, so it can't pick an alias that is unique in the query. A custom alias can't be used then either: EF Core's alias pass (SqlAliasManager.PostprocessAliases, in RelationalQueryTranslationPostprocessor) parses every alias as one letter and a number (int.Parse), and it runs before the parameter-based processor the guard runs in.
• In the guard, after that pass. The guard builds ARRAY((SELECT DISTINCT ON (vrN.x) vrN.x FROM unnest(…) WITH ORDINALITY AS vrN(x) [WHERE …] ORDER BY vrN.x, vrN.ordinality)). The two-letter vrN aliases can't collide with EF Core's.
• Nodes: EF Core's immutable SelectExpression constructor (EF1001) with a throwaway SqlAliasManager, Npgsql's PgUnnestExpression (EF1001), and a ScalarSubqueryExpression as the argument of a function named ARRAY. Neither EF Core nor Npgsql has a node for DISTINCT ON, so the projection is a SqlFragmentExpression, which is safe because aliases are final by then. The GROUP BY form doesn't render: Npgsql writes array_agg(…)[1] without the parentheses PostgreSQL needs.
• Both lines: the same code compiles unchanged and gives identical SQL and results on EF Core 10 and 11, on PostgreSQL 16 and 18.
• Matched memory for Int32Set, DecimalSet, Float64Set and GuidSet: ==, cardinality, ORDER BY, GROUP BY, nesting, a correlated EXISTS with three expansions, a NULL operand (CASE) and @> stripped to array_cat. Through ExecuteUpdate, 1.00/1.0 and -0/0 are kept as memory keeps them, including a row another tool wrote out of order. The one difference is the documented one: == against a row stored out of order.
• Cost on 100,000 rows of 10 random integers (PostgreSQL 16): bare array_cat … @> 12 ms, canonical Union under = 182 ms, canonical Intersect under cardinality 144 ms, plain DISTINCT 163 ms. About 1.5 µs a row, roughly 15 times a bare concatenation, which is why stripping matters.
• Test churn: one of 378 no-database translation tests flips (WrapperSets_Union_Count_FailsTranslation, which now translates). The integration tests were not run.
• Not covered by the spike: stripping in the outermost projection and under cardinality(…) = 0 and a null test, the wrapper arities and the NodaTime sets against a live server, and GIN plans. All but GIN plans are covered by the implementation on feat/478-canonical-set-operations (2026-10-26): every discovered family but text, core, wrapper arities and NodaTime, on PostgreSQL 16 and 18.

Out of scope: the string-backed families (StringSet, StringSet<T>). Their order is decided in #479 for 11.0.0; until then they keep today's behaviour. The Tags example in docs/efcore.md#combine-sets-with-union is a StringSet, so it stays refused on 10.x: what 10.4.0 gives is the number, uuid and temporal sets, and ExecuteUpdate adding or removing elements in one statement for them.

To measure first

• Guid.CompareTo against uuid order Measured, they agree. 564 guids (0x01, 0x7f, 0x80 and 0xff at each of the 16 byte positions, plus 500 random) sort the same in memory and in SQL, on PostgreSQL 16 and 18. The translator's remark that uuid "orders byte-wise where Guid.CompareTo orders field-wise" is wrong and goes with this change.

• NULL operands, the same rules as 10.x: value set Union and Remove over a NULL operand project a value instead of NULL (port of #360) #395/Value set Union is declared NULL only for a NULL operand, but array_cat isn't strict: a filter and a projection disagree on a NULL column #360. Measured on PostgreSQL 16: ARRAY(SELECT … FROM unnest(NULL::int[])) is {}, not NULL, and array_cat(NULL, '{1}') is {1}. So every operation needs the NULL-over-NULL wrapper from Value set Union is declared NULL only for a NULL operand, but array_cat isn't strict: a filter and a projection disagree on a NULL column #360/10.x: value set Union and Remove over a NULL operand project a value instead of NULL (port of #360) #395 around the expression.

• NULL elements in a row another tool wrote. Memory can't load such a row, but the SQL answers anyway. Measured on PostgreSQL 16:

  • x <> ALL('{1,NULL}') is NULL, so {1,2} \ {1,NULL} gave {}. With the containment filter it gives {2}.
  • Union passes NULL elements through: {2,NULL} ∪ {1} gives {1,2,NULL}. So does Except from the left operand, with the containment filter too: {1,2,NULL} \ {1} gives {2,NULL}.

  Decided (2026-10-26): dropped. The canonical form filters WHERE x IS NOT NULL; the containment filter ignores them on the right. The multiset form under the containment operators keeps them, as the column itself does, and docs/efcore.md#rows-written-by-other-tools says so.

• The wrapper arities' order. Int32Set<T> and its siblings sort by the wrapper's own IComparable (Comparer<TElement>.Default), and SQL sorts by the primitive. Today nothing translated depends on order (= compares two arrays both written in the wrapper's order), so a wrapper that sorts differently from its primitive is harmless. After this change x.Codes.Add(c) == other would differ from memory silently. Decided (2026-10-26): documented as a requirement in docs/value-sets.md#how-your-element-types-sort-and-serialize and the EF guide; Vogen and StronglyTypedId compare by the underlying value (checked in their docs). Otherwise the wrapper arities and the NodaTime sets go through SetTypeRegistry and should get the same translation with no per-family code. The clone sweep confirms it.

• Plans for a canonical union in a WHERE over a GIN-indexed column: the indexed side stays a bare column.

Does it change an answer?

No. Queries that throw today get the in-memory answer; nothing that runs today answers differently. So it's an addition. It lands on main first and is cherry-picked with -x into release/10.x as a 10.x minor (CONTRIBUTING.md#branches-and-releases).

Done when

• Intersect, Except, Add and a canonical Union translate for every non-text family, core and NodaTime
• UnionCanonicalityTests and the shape/parity sweeps assert results against PostgreSQL 16 and 18, including non-canonical rows written by another tool, NULL operands and NULL elements
• docs/efcore.md#what-runs-where moves the three operations to "Runs as SQL" and narrows the union guard section
• docs/efcore.md#combine-sets-with-union rewords "Sorting in SQL wouldn't fix it. PostgreSQL has no array_distinct" (DISTINCT ON over unnest does that job), and its Union(more).Count projection example says where it runs now
• AGENTS.md narrows to the string-backed families both the intro's "Intersect/Except/Add are deliberately client-side only" and the critical rule "Union translates to array_cat, which does NOT canonicalize … Do not 'fix' this by sorting in SQL"
• claims E17 and E35 in test/oracles/claims.json narrow to the string-backed families, with their holding tests on StringSet
• the wrapper arities' order precondition is documented, or the arities are left out
• a seed shows that a plain DISTINCT (dropping ORDER BY x, n) is caught
• WrapperSets_Union_Count_FailsTranslation and the other refusal tests move to the string-backed families

Split out of #407, which keeps enumeration and the bridge conversions.