Closed
Description
openedon May 22, 2023
Although our InExpression supports having a subquery (and various visitors handle that), we never actually generate this - instead we always generate an EXISTS subquery with a predicate (based on SQL Server baselines). Translating to InExpression is likely better - it doesn't require a correlated subquery and expresses the intent more succintly, without needing a predicate. For example, instead of this:
SELECT ...
FROM [PrimitiveCollectionsEntity] AS [p]
WHERE EXISTS (
SELECT 1
FROM (
SELECT TOP(2) CAST([i].[value] AS int) AS [c], CAST([i].[key] AS int) AS [c0]
FROM OPENJSON([p].[Ints]) AS [i]
ORDER BY CAST([i].[key] AS int)
) AS [t]
WHERE [t].[c] = 11)
We can translate to this:
SELECT ...
FROM [PrimitiveCollectionsEntity] AS [p]
WHERE 11 IN (
SELECT TOP(2) CAST([i].[value] AS int)
FROM OPENJSON([p].[Ints]) AS [i]
ORDER BY CAST([i].[key] AS int)
)
However, note that our null semantics around InExpression with subquery currently seems broken (fortunately it's dead code): IN returns null when the item is null (or when the values contains null and a match isn't found). We'd need to make that logic actually work.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment