Repository navigation
list.Contains(rangeColumn) fails with 42809 'op ANY/ALL (array) requires array on right side' #71
Description
A query that tests whether a range column is one of a captured list of ranges fails on the server:
var ranges = new List<Int32Range> { Int32Range.CreateFinite(1, 5) };
db.Reservations.Where(r => ranges.Contains(r.Seats));SQL: WHERE r."Seats" = ANY (@ranges)
Error: PostgresException 42809: op ANY/ALL (array) requires array on right side
It fails loudly. A captured List<RangeSet<Int32Range, int>> against a multirange column translates the same way (= ANY (@multis)) and answered correctly in the same probe. So it is the range collection's parameter that isn't bound as an array.
Found while pinning down #46, the same shape over value sets, which failed earlier, in Npgsql, with InvalidCastException. Not yet checked: the other range families, a constant list, an inline collection. Either translate it or refuse it in translation with a message that says what to write instead.
Activity
CaffeinatedCoder commentedon Oct 17, 2026
Repro pinned down (live PostgreSQL 17, Npgsql EF 10.0.0)
Only a captured collection fails. Constant and inline collections already translated and answered correctly, and so does every multirange collection.
List<T>/T[]new[] { a, b }Int32Range,Int64Range,DecimalRange,DateRange,DateTimeRange,DateTimeOffsetRange= ANY (@List)→42809 op ANY/ALL (array) requires array on right sideIN ('[1,5]'::int4range, 'empty'::int4range), correctIN (@p, @p0), correctTimeRange(customtimerange)= ANY (@List)→InvalidCastException: Writing values of 'NpgsqlRange<TimeOnly>[]' is not supported…LocalDateRange,LocalDateTimeRange,InstantRange,YearMonthRange(NodaTime)42809, as aboveRangeSet<Int32Range,int>,RangeSet<DateRange,DateOnly>(multirange)= ANY (@List), correctCause: the collection reaches Npgsql as a plain
NpgsqlRange<T>[]with noDataTypeName, and Npgsql infers that CLR type as a multirange. Measured directly with an untypedNpgsqlRange<int>[]parameter:pg_typeof($1)isint4multirange. WithDataTypeName = "int4range[]"it isint4range[]. So the right side of= ANYis a multirange, not an array (42809).NpgsqlRange<TimeOnly>[]has no inferred type at all, hence theInvalidCastException. A multirange collection isNpgsqlRange<T>[][], which Npgsql infers as a multirange array, so it works.Decision: translate, as in #46
Typing the single array parameter as
int4range[]would need two Npgsql-internal types (NpgsqlArrayTypeMapping,INpgsqlTypeMapping), and still couldn't type the customtimerange. Instead, the query interceptor from #75 also sends a captured collection of ranges one parameter per range: EF Core'sMultipleParametersmode,IN (@p1, @p2, …), withVALUESforAny/Count. Each parameter goes through the range's own mapping, which sets itsDataTypeName, so this works fortimerangetoo. It is the same SQL the constant and inline forms already produce, andINis=against each range, which the shape-matrix tests already hold equal to memory.EF.Parameter(list)asks for the untyped single parameter, so it is refused in translation with a message saying to remove it. Multirange collections already work and are left unchanged.CaffeinatedCoder commentedon Oct 17, 2026
Scope change from the plan above. The revert proof showed that
Any/Countalone over a captured range list did not fail before. They ran throughunnestof the multirange-typed parameter and returned answers different from memory without throwing (overlapping ranges merged, empty ones dropped). So #77 switches a range collection only when aContainsreads it, since those queries failed.EF.Parameteris likewise rejected only as the source ofContains. The silentAny/Countdefect is #76 (10.0.0).CaffeinatedCoder commentedon Oct 17, 2026
Oracle check (main at 3ecfa10): no oracle on main catches this (pending). I reverse-applied #77's
src/diff on 3ecfa10 and ran every class inoracles.json, including the live ones. All of them stayed green. The fix's own regression tests did fail (RangeCollectionContainsTests: 7 of 9,42809: op ANY/ALL (array) requires array on right side), which shows the revert put the defect back.Gap: the same as #46. The EF Core Operand position axis in
axes.mdhas no captured collection value, andDifferentialFuzzParityTestsbinds operands only as a column, a constant or a parameter. Claim E37 is held only byRangeCollectionContainsTestsandRangeCollectionTranslationTests, which are regression tests. The missing pair is captured collection × every range type, outside route B steps 2 and 3.CaffeinatedCoder commentedon Oct 17, 2026
Update: would be caught by
CapturedCollectionProbeTests(#138, the probe sweep for the gap named above), methodRanges_AnswerAsInMemory. Verified by a run: I reverse-applied #77'ssrc/diff on the PR branch, and the sweep failed with 1,540 lines, e.g.DateRange over adjacent as List, beside Contains: Contains threw PostgresException: 42809: op ANY/ALL (array) requires array on right side. Labelledfound-by/oracleretroactively.