-
Notifications
You must be signed in to change notification settings - Fork 3.2k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Invalid SQL with CROSS APPLY generated on SQLite #19178
Comments
This issue would improve the exception message pointing to users that query cannot be translated because it uses cross apply/outer apply. |
Isn't it a bug that SQL is generated for SQLite that isn't SQLite compatible? Is a join not an option? Given that Microsoft recommend using SQLite in unit tests, what is their recommendation for when a query works in SQL Server (in production) but not SQLite (in unit tests). I'm loathe to change code just to satisfy a unit test. |
@Snappyfoo We need to update that guidance to be more detailed. See dotnet/EntityFramework.Docs#430 and dotnet/EntityFramework.Docs#1304, and also the discussion on #18457 Full fidelity when testing is only possible when using the same database backend that you use in production. Every step away from that results in some level of mismatch. The SQLite provider is closer to SQL Server because they are both relational providers, but it still has different capabilities and behaviors in some cases. |
I can understand that the database might behave differently, but what I don’t quite follow in this particular case is why the SQL produced when targeting SQLite isn’t compatible with SQLite. Perhaps I don’t get something about how EF works, but isn’t it open to support for a number of database providers and the process of translating LINQ expressions to SQL is specific to each database provider? Isn’t there a bug in that process for SQLite? I do understand that what I’m asking for is a situation where the SQL executed would be different when against SQL Server vs SQLite but I would expect the result to be the same in most cases, and I’m not trying to test EF in that respect. I’m only aware of the SQL it produces because it doesn’t work. |
@Snappyfoo Yes, this is a bug. (We label bugs with |
Oh, right you are. Sorry, I misinterpreted one of the comments. Thanks for clarifying that. |
Duplicate of #18871 Changing this to enhancement. The reason we generate non-join (outer apply) is non-equality comparison being referenced in predicate. In the absence of which we would generate a proper join. |
Having the same issue in the HealthChecks UI repository, but with OUTER APPLY: Error: Microsoft.EntityFrameworkCore.Database.Command[20102]
Failed executing DbCommand (1ms) [Parameters=[@__item_Name_1='?' (Size = 9), @___settings_MaximumExecutionHistoriesPerEndpoint_0='?'], CommandType='Text', CommandTimeout='30']
SELECT "t"."Id", "t"."DiscoveryService", "t"."LastExecuted", "t"."Name", "t"."OnStateFrom", "t"."Status", "t"."Uri", "h"."Id", "h"."Description", "h"."Duration", "h"."HealthCheckExecutionId", "h"."Name", "h"."Status", "t0"."Id", "t0"."Description", "t0"."HealthCheckExecutionId", "t0"."Name", "t0"."On", "t0"."Status"
FROM (
SELECT "e"."Id", "e"."DiscoveryService", "e"."LastExecuted", "e"."Name", "e"."OnStateFrom", "e"."Status", "e"."Uri"
FROM "Executions" AS "e"
WHERE "e"."Name" = @__item_Name_1
LIMIT 2
) AS "t"
LEFT JOIN "HealthCheckExecutionEntries" AS "h" ON "t"."Id" = "h"."HealthCheckExecutionId"
OUTER APPLY (
SELECT "h0"."Id", "h0"."Description", "h0"."HealthCheckExecutionId", "h0"."Name", "h0"."On", "h0"."Status"
FROM "HealthCheckExecutionHistories" AS "h0"
WHERE "h0"."HealthCheckExecutionId" = "t"."Id"
ORDER BY "h0"."On" DESC
LIMIT @___settings_MaximumExecutionHistoriesPerEndpoint_0
) AS "t0"
ORDER BY "t"."Id", "h"."Id", "t0"."On" DESC, "t0"."Id"
fail: Microsoft.EntityFrameworkCore.Query[10100]
An exception occurred while iterating over the results of a query for context type 'HealthChecks.UI.Core.Data.HealthChecksDb'.
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'near "(": syntax error'.
at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext()
at Microsoft.Data.Sqlite.SqliteCommand.GetStatements(Stopwatch timer)+MoveNext()
at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'near "(": syntax error'. |
@maumar - This should be working now. |
@smitpatel I'm using this to track the exception message improvement (PR coming soon) |
Adding logic that checks for APPLY and throws meaningful exception
Adding logic that checks for APPLY and throws meaningful exception Fixes #19178
Adding logic that checks for APPLY and throws meaningful exception Fixes #19178
Adding logic that checks for APPLY and throws meaningful exception Fixes #19178
I'm pretty sure I just hit this bug but it looks like -preview8 isn't out yet. The newest I see when checking "Include prerelease" in Nuget Package Manager is 5.0.0-preview7.20365.15. Is there an ETA on preview 8 so I can see if it resolves my issue? |
Any day now. We aim for monthly previews. |
After updating EF Core from 2.2.3 to 3.1.0-preview3.19554.8, a specific query started failing when executed via the Sqlite provider. While we can manually work around this issue, the second query should be identical to the first one yet yields different SQL.
To Reproduce
The model in use:
The goal is to return all messages not yet seen by a given participant, noting they may have never seen the conversation. This is our existing implementation:
When run via the SQLite provider, this generates the following query:
In turn throwing the following exception:
More Details
My limited understanding is that the APPLY operator is specific to SQL Server and is not available in SQLite.
When the query is re-written as follows, EF generates a query with a JOIN as expected:
The same happens if the
LastSeen
expression is repeated twice in theWhere
block, instead of selecting it initially.Running the same examples using the SQL Server provider yields similarly looking SQL - first one using APPLY and the second one, a JOIN. I would've naively expected the same output from both queries, considering they are virtually identical.
Additional context
Microsoft.Data.Sqlite version: 3.1.0-preview3.19554.8
Target framework: net461
Operating system: Windows 10
The text was updated successfully, but these errors were encountered: