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

Package

CodoMetis.ValueRanges.EFCore.PostgreSQL

Version

10.3.0 (measured). main translates the same operators.

How does it fail?

Silently: a wrong answer, and a wrong write, with no exception.

What you ran

A StringSet column with a nondeterministic (case-insensitive) collation, holding {A}:

// CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
modelBuilder.Entity<Post>().Property(p => p.Tags).UseCollation("case_insensitive");
// → "Tags" text[] COLLATE case_insensitive NOT NULL

db.Posts.Where(p => p.Tags.Contains("a"))                  // p."Tags" @> ARRAY['a']::text[]
db.Posts.Where(p => p.Tags.IsSubsetOf(StringSet.From("a"))) // p."Tags" <@ @a
db.Posts.Where(p => p.Tags.Overlaps(StringSet.From("a")))   // p."Tags" && @a
db.Posts.Where(p => p.Tags == StringSet.From("a"))          // p."Tags" = @a
db.Posts.Select(p => p.Tags.Remove("a"))                    // array_remove(p."Tags", 'a')
db.Posts.ExecuteUpdate(s => s.SetProperty(p => p.Tags, p => p.Tags.Remove("a")))

What you got

The last row deletes an element memory keeps. The array operators compare elements with the column's collation, and a nondeterministic collation makes A equal to a. count(DISTINCT x) over {A,a} is 1 under it too (COLLATE "C" gives 2), which matters to any future rewrite that counts or compares elements in SQL.

What you expected

The in-memory answers. String-backed value sets compare ordinally (AGENTS.md: "String-backed families sort ordinal — never culture"), and docs/efcore.md#what-runs-where says the translated members give the same answers in the database as in memory. No guide mentions collations.

Environment

.NET 10 (net10.0), macOS 27 (arm64), PostgreSQL 16.15 and 18.1 (postgres:*-alpine), Npgsql.EntityFrameworkCore.PostgreSQL 10.0.0, EF Core 10.0.0. Reproduced with the 10.3.0 packages from nuget.org in a standalone console app.

Reach

• A deterministic collation, case-sensitive or not by ordering, compares bytewise for equality, so it doesn't disagree. Only a nondeterministic one does, and the consumer has to configure it on the column (UseCollation), or in a query.
• To check: EF.Functions.Collate(p.Tags, "case_insensitive"), a domain over text with a collation, and whether a database default can be nondeterministic on 16 to 18.
• Range types aren't affected: none has a text element.

Options

1. Translate with COLLATE "C" on the text operands of @>, <@, &&, =, <> and array_remove, so the database compares as memory does whatever the column says. To measure: whether a GIN index on the column still serves the operator with the operand collated, and the cost.
2. Refuse at model validation a string-backed value set property with a collation other than "C"/"POSIX"/the default. Loud, but it can't tell deterministic from nondeterministic by name, so it also refuses the harmless ones.
3. Document that the column's collation decides element equality in SQL. Leaves the Remove write as it is.

Options 1 and 2 change answers, so the fix ships as a minor on every supported line under ### Changed answers (CONTRIBUTING.md#a-fix-that-changes-an-answer).

Done when

• the option is decided here
• a live test with a nondeterministic column collation asserts the in-memory answer for every text operator the plugin writes, including Remove through ExecuteUpdate, on PostgreSQL 16 and 18
• a seed that drops the fix is caught
• the docs say what a collation on a value set column does