Closed ranma42 closed 2 days ago
@roji
I just happen to be in this section of the codebase today as I am working through a similar issue I am having in the Windowing functions support. I decided to take a quick peek.
The cause of the exception is that the generated sql allows a null to bleed out which then causes a type mismatch. The shaper lambda then throws an exception due to a bad cast of null => boolean
Sqlite is generating the following which allows a null return value.
SELECT CASE
WHEN "e"."Name" = 'paul' THEN 3
ELSE NULL
END >= 3 AS "Id"
FROM "Employees" AS "e"
On Sql Server this is not valid syntax due to the >= 3. The Sql Server code path wraps everything in another case to get this valid syntax.
SELECT CASE
WHEN CASE
WHEN [e].[Name] = N'foo' THEN 3
ELSE NULL
END >= 3 THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END AS [Id]
FROM [Employees] AS [e]
This syntax is also valid on Sqlite and stops the null from escaping. Applying this same manipulation is probably the correct fix - that exercise is left to the reader :)
Sql Server handels this case by overriding the Optimize function in SqlServerParameterBasedSqlProcessor. SqlServerParameterBasedSqlProcessor is then using the SearchConditionConvertingExpressionVisitor which applies the extra case statement via the VisitSqlBinary -> ApplyConversion -> ConvertToValue call chain.
I hope this helps
Paul
@pmiddleton thank you! I tried and moved the SearchConditionConvertingExpressionVisitor.Visit()
call to RelationalParameterBasedSqlProcessor.Optimize()
and indeed the exception is gone!
I will investigate it a little further, because this change (at least in the naive way I applied it) also seems to worsen other queries (introducing stuff like = 1
), but I believe you put me on the right track!
Thanks for the input @pmiddleton, makes sense! @maumar assigning to you as this is in your traditional area of stewardship :)
Comparing the result of a ternary operator can result in inconsistent nullability and exceptional termination while scanning the results of a query. Note that filtering by the same
Expr
does not trigger the issue.An example program that showcases the bug (and can be conveniently run on https://dotnetfiddle.net/ ;) ) is:
Exception
The program terminates with the following exception:
Include provider and version information
EF Core version: 8.0.5 Database provider: Microsoft.EntityFrameworkCore.Sqlite Target framework: .NET 8.0 Operating system: Linux (/WSL) IDE: Visual Studio Code 1.89.1