Text set ==, != and Count through a scalar subquery skip #485's rewrite and compare the unsorted array_cat #505

Found by a pre-release review of release/10.x @ a958bef (an outside review, and the sweep's long-method lens on ValueSetSqlGuard); reproduced against PostgreSQL 16 in a scratch test. Also on main @ 2949415.

Problem

ValueSetSqlGuard's two predicates for "may be an unsorted text set" disagree on a scalar subquery:

• Walker.Derived treats a ScalarSubqueryExpression whose projection is a text Union/Add/Intersect/Except as derived (src/CodoMetis.ValueRanges.EFCore.PostgreSQL/Query/ValueSetSqlGuard.cs:374), so it lets ==, !=, NULLIF and Count through. It does that because the expander promises to rewrite them as mutual containment and count(DISTINCT …) (Translate text value set Intersect, Except, Add, Count and equality where the result needs no order (StringSet, StringSet<T>) #485/10.x: run text value set operations as SQL where order and repeats don't count (port of #485) #497).
• CanonicalExpander.IsDerived (ValueSetSqlGuard.cs:640) has no arm for a scalar subquery and answers false, so the rewrite never happens. The raw =, <> and cardinality then compare an array_cat that keeps order and repeats.

Nothing throws. Rows are dropped or counted wrong.

Scenario

A row has Tags = {a,c}, and other = {a,b}. In memory, {a,c}.Union({a,b}) == {a,b,c} is true.

context.Reservations.Where(r => context.Reservations
    .Where(s => s.Id == r.Id).Select(s => s.Tags.Union(other)).FirstOrDefault() == expected)

WHERE (SELECT array_cat(r0."Tags", @other) FROM "Reservations" AS r0 … LIMIT 1) = @expected

On the server this compares {a,c,a,b} with {a,b,c}, so the row is excluded.

Measured forms (PostgreSQL 16; the first live, the other two as translation tests):

• ==: no rows, where memory returns the row.
• !=: raw <>.
• (subquery ?? b.Tags).Count: cardinality(COALESCE((SELECT array_cat(…) …), b."Tags")), which counts the repeated element: {a} ∪ {a,b} gives 3 where memory gives 2.

.FirstOrDefault().Count with no COALESCE is correct, because EF moves Count inside the subquery, where the rewrite applies.

Traced, not run: NULLIF (ValueSetSqlGuard.cs:506), and CASE over a scalar subquery.

Change

Make the two predicates agree. Two ways:

• Give IsDerived a ScalarSubqueryExpression arm: the subquery's single projection is a placeholder, is derived, or is a derived column.
• Or have the walker record the derived scalar-subquery nodes for the expander, as it already does with DerivedColumns.

A convention or sweep that holds Walker.Derived and IsDerived arm for arm would catch the next divergence. The walker's NULLIF arm also requires IsSet(kept), which IsDerived's does not.

Test

In UnionCanonicalityTests (integration), with its existing rows: ==, != and Count over COALESCE through a scalar subquery, each asserting the server answer equals LINQ over the loaded rows. Revert the arm to prove each test fails (verify-the-guard), and add a seed under test/oracles/seeds/.

Release

silent, so it goes under ### Changed answers in 10.4.0 on release/10.x (#485's text forms first ship there), main first, then a port.