Translate text value set Intersect, Except, Add, Count and equality where the result needs no order (StringSet, StringSet<T>) #485

Revised (2026-10-26): no COLLATE "C". Measured on #483: a collated operand turns a GIN-indexed @> into a sequential scan, on an ordinary en_US column too. #483 instead rejects a collation on a string set column that may not compare bytes. So the column's own equality is byte equality, and count(DISTINCT x) and mutual containment need no collation. The seeds are a Count left as cardinality, and equality rewritten only when both sides are a text operation.

Problem

After #478, Union, Add, Intersect and Except translate canonically for every set type but text. Text can't be sorted in SQL the way memory sorts it (#479). So on 10.x, for StringSet and StringSet<T>:

• Intersect, Except and Add don't translate at all.
• Union translates to array_cat. The guard allows it under @>/<@/&&, cardinality(…) = 0, a null test and the outermost projection. Count and == over it throw.

#479 can change the order only in 11.0.0, because the change is breaking. 10.x keeps this for its whole life.

Many of the expressions that use the result don't need order:

• Intersect, Except, Add can compose under the same four uses as Union. Translate value set Intersect, Except, Add and a canonical Union for the non-text families with composed SQL #478's expander already writes their form without sorting there: the plain filter, and array_cat with a one-element array.
• Count is the number of distinct elements: (SELECT count(DISTINCT vrN.x COLLATE "C") FROM unnest(…) AS vrN(x)).
• Equality of two sets is mutual containment, a @> b AND b @> a. @> ignores order and duplicates.

Measured (PostgreSQL 16.15 and 18.1)

• count(DISTINCT x COLLATE "C") over array_cat('{a,😀,｡}', '{｡,b,a}') is 4. The supplementary/U+FF61 pair that breaks sorting (Decide the canonical order of string-backed value sets for 11.0.0: code point order, which PostgreSQL can sort, or ordinal #479) doesn't affect equality.
• Mutual containment: {a,a,b} against {b,a} is true, {a,b} against {a} false, {} against {} true. A NULL operand gives NULL, so it takes the same NULL handling as any other comparison.
• COLLATE "C" is required. Under a nondeterministic column collation, a plain count(DISTINCT x) over {A,a} is 1, and @> compares by that collation (Text value set operators follow a nondeterministic column collation: on a case-insensitive column, Contains("a") is true for {A} and Remove("a") deletes A #483). Both forms collate their text operands to "C"; ARRAY['A'] COLLATE "C" is valid on an array.

Change

• For a set type without CanonicalizesInSql, the translator emits Intersect, Except and Add as values the guard follows, as it follows UnguardedUnion today. They are allowed under @>, <@, &&, cardinality(…) = 0, a null test and the outermost projection (the read canonicalizes), where the expander writes their unsorted form.
• Two more allowed uses for such a value:
  • cardinality(x) becomes the count(DISTINCT … COLLATE "C") subquery, and ValueSetsMemberTranslator stops declining Count over a visible union.
  • x = y and x <> y become mutual containment, with COLLATE "C" on both operands, and so does NULLIF (EF Core 11's a == b ? null : a). This applies only where one side is such a value. Equality between two stored columns keeps =: it is documented to differ for rows another tool stored out of order (docs/efcore.md#rows-written-by-other-tools), and it is what a btree index serves.
• Still refused for these values: GROUP BY, DISTINCT, ORDER BY, IN, a deduplicating set operation, the indexer, and an UPDATE setter, which would store a non-canonical array.
• The wrapper arities (StringSet<T>) go through SetTypeRegistry and need no code of their own.

Order against #479. If #479 picks code point order for 11.0.0, text joins #478's canonical form on main, and these forms are no longer needed there. If this lands on main first, #479 replaces it there. If #479 lands first, this is release/10.x only, and the issue is closed by hand.

Does it change an answer?

No. Every newly translated shape throws today.

Done when

• Intersect, Except and Add on StringSet and StringSet<T> translate under the allowed uses; Count, == and != translate over any such text value; the remaining refusals keep today's messages
• UnionCanonicalityTests (integration) asserts results against PostgreSQL 16 and 18. It covers the supplementary/U+E000–U+FFFF pair, duplicates, NULL operands, rows another tool stored out of order, and a nondeterministic column collation
• seeds that drop COLLATE "C" from the count, and that write equality as =, are each caught
• docs/efcore.md#what-runs-where (the text row and "Count over a text Union computed in the database throws"), #combine-sets-with-union, the AGENTS.md intro and its Union rule, and claims E17 and E35 follow