[release/10.0] Fix OPENJSON column name when going over 2100 params#37572
[release/10.0] Fix OPENJSON column name when going over 2100 params#37572roji merged 1 commit intodotnet:release/10.0from
Conversation
There was a problem hiding this comment.
Pull request overview
Fixes a SQL Server case-sensitivity issue in the over-2100-parameters fallback path where EF Core switches from VALUES to OPENJSON and produced mismatched column casing ([value] vs [Value]) under case-sensitive collations.
Changes:
- Update SQL Server nullability processing to align OPENJSON
WITHcolumn name casing withValuesValueColumnName("Value"), with an AppContext switch to opt into old behavior. - Strengthen/update SQL assertions in the SQL Server JSON type primitive collections functional tests to validate the corrected casing.
Reviewed changes
Copilot reviewed 2 out of 2 changed files in this pull request and generated 2 comments.
| File | Description |
|---|---|
| test/EFCore.SqlServer.FunctionalTests/Query/PrimitiveCollectionsQuerySqlServerJsonTypeTest.cs | Updates assertions to expect OPENJSON WITH ([Value] ...) and validates generated SQL for huge parameter collections. |
| src/EFCore.SqlServer/Query/Internal/SqlServerSqlNullabilityProcessor.cs | Uses a consistent column name (Value) for the over-limit OPENJSON path, with an AppContext quirk switch for legacy behavior. |
test/EFCore.SqlServer.FunctionalTests/Query/PrimitiveCollectionsQuerySqlServerJsonTypeTest.cs
Show resolved
Hide resolved
artl93
left a comment
There was a problem hiding this comment.
Customer reported regression. Quirked. Odd case. I'm guessing hard for customer to diagnose on their own?
Approved for me.
Yeah, also not much they can do about it when they do, unfortunately... Changing the database collation to be case-insensitive would work, but is a very big hammer that affects many other aspects of the program. |
|
@roji Do you plan to run SQL Server tests on a case-sensitive collation? (or maybe you alreeady do?) |
|
@ErikEJ not at the moment, no. This is the first time in a very long time that we've encountered a collation-related issue - we could of course change that in the future. |
|
Thanks @roji! |
Fixes #37569
Description
Fixed a case-sensitivity bug in SQL Server's OPENJSON generation when parameter collections exceed the 2098 parameter limit. When VALUES expressions are replaced with OPENJSON to work around SQL Server's parameter limit, the OPENJSON WITH clause defined a column with lowercase
[value]while ColumnExpressions referenced it with uppercase[Value], causing a name mismatch in case-sensitive collations.Customer impact
Applications using SQL Server with case-sensitive collations that query large parameter collections (>2098 elements) or queries with many parameters that trigger OPENJSON usage will fail with column name mismatch errors. The bug specifically affects collection methods like
All(),Contains(), etc. on inline collections.Example failing code:
Workaround: Use databases with case-insensitive collations (the default), or restructure queries to avoid exceeding parameter limits.
How found
Customer reported via GitHub issue #37569. The issue affects customers using case-sensitive collations with large parameter collections - a relatively small subset of SQL Server users, but a critical correctness bug for those affected.
Regression
Yes, regression in EF Core 10.0.
Testing
The fix was tested by building the solution successfully. Existing test coverage includes Parameter_collection_of_ints_Contains_int_parameters_limit which exercises parameter collections of 2098, 2099, and 2100 elements, though it validates no exceptions are thrown rather than asserting specific SQL output. The fix ensures OPENJSON uses uppercase [Value] to match the VALUES column naming convention.
Risk
Very low. The change is a simple two-line fix that ensures consistency in column naming between VALUES and OPENJSON expressions. It only affects the code path where parameter count exceeds 2098 and VALUES is replaced with OPENJSON.
Quirk added.