A case-insensitive collation on the element side still makes string set Contains and Remove compare case-insensitively #507

Found by a pre-release sweep of release/10.x @ a958bef (lens: new-check-vs-invariant); reproduced against PostgreSQL 16 in a scratch test. Also on main @ 2949415. The behaviour predates 10.4.0, but 10.4.0's changelog row for #483/#496 says this answer is fixed.

Problem

#483/#496 refuse a non-byte-order collation in two places:

• on a StringSet/StringSet<T> property (StringSetCollationConvention, src/CodoMetis.ValueRanges.EFCore.PostgreSQL/Infrastructure/StringSetCollation.cs);
• in EF.Functions.Collate over a set (ValueSetSqlGuard.cs:340, when StringSetCollation.IsStringSet(collated.Type)).

PostgreSQL derives an operator's collation from both operands. An implicit ci collation on the element side wins over the array column's default. So @>, <@, && and array_remove still compare case-insensitively when the element comes from a string column with UseCollation("ci"), or is wrapped in EF.Functions.Collate(…, "ci"). This is exactly the configuration the remedy in the exception message and docs/efcore.md leaves you with: the set column without a collation.

Scenario

modelBuilder.HasCollation("ci", locale: "und-u-ks-level2", provider: "icu", deterministic: false);
modelBuilder.Entity<Group>().Property(g => g.Owner).UseCollation("ci");
// row: Members = {A,b}, Owner = "a"

• Groups.Where(g => g.Members.Contains(g.Owner)) translates to WHERE g."Members" @> ARRAY[g."Owner"]::text[] and returns the row. Memory: {A,b}.Contains("a") is false.
• ExecuteUpdate(s => s.SetProperty(g => g.Members, g => g.Members.Remove(g.Owner))) stores {b}. Memory keeps {A,b}. This is silent data loss.
• Members.Contains(EF.Functions.Collate("a", "ci")) is true, where Collate over the set is refused.

Traced, not run: Overlaps, IsSubsetOf/IsSupersetOf against a set built from ci columns, text Add(g.Owner).Count (count(DISTINCT …) under ci), and #485's ==/!= mutual containment.

A plausible consumer: a Members set of usernames, joined to a Users.Username column made case-insensitive as Npgsql's docs recommend.

Change

Refuse at query time, in the guard. An operator over a string set (@>, <@, &&, array_remove, equality, the #485 rewrites) is refused when its element or other operand has a collation that may not compare bytes: a CollateExpression anywhere under the operands, or a ColumnExpression whose property carries such a collation. An alternative is to write COLLATE "C" on the element operand only, which #483 measured as killing the GIN index when written on the column side. Re-measure that on the element side before choosing it. Extend the EF guide's Good to know entry and claim E86 to the element side.

Test

StringSetCollationIntegrationTests: Contains(owner), ExecuteUpdate … Remove(owner) and Contains(Collate("a", "ci")) each throw, naming the collation, or answer as memory does. Revert to prove each fails, and add a seed.

Release

silent: ### Changed answers in 10.4.0, main first, then a port. The 10.4.0 row and the migration section for #496 should cover the element side.