"SELECT 1 FROM ... " with System.Data.OleDb on Jet/Ace gives System.ExecutionEngineException or System.AccessViolationException

[emilsteen]

Description

When doing multiple OleDbCommand update/insert/query on a table in an mdb database (the issue is also confirmed in .accdb), the execution will (sometimes) stop with a System.ExecutionEngineException (or System.AccessViolationException in .NET 8).

Error message .NET 8

Fatal error. System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt. Repeat 2 times: --------------------------------
[call stack below]

Error message .NET 10

Fatal error. 0xC0000005
(System.ExecutionEngineException in Visual Studio debugger.)

Call stack from both .NET versions

at System.Data.Common.UnsafeNativeMethods+ICommandText.Execute(IntPtr, System.Guid ByRef, System.Data.OleDb.tagDBPARAMS, IntPtr ByRef, System.Object ByRef) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(System.Data.OleDb.tagDBPARAMS, System.Object ByRef) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(System.Object ByRef) at System.Data.OleDb.OleDbCommand.ExecuteCommand(System.Data.CommandBehavior, System.Object ByRef) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(System.Data.CommandBehavior, System.String) at System.Data.OleDb.OleDbCommand.ExecuteScalar() at OleDbJetApp.Program.RecordExists(System.Data.OleDb.OleDbConnection, System.String) at OleDbJetApp.Program.Main(System.String[])

The code to reproduce the exception loops SQL-queries with INSERT, SELECT 1, SELECT (full) and UPDATE. The loop has to be repeated at least once before the exception occurs and it is always on "SELECT 1 FROM TestTable WHERE id_column = ?".

The current test code does 10 loops, normally the exception occurs during the first 10 loops or never at all.
The order of the fields does also affects the outcome of the test.

Probable cause

It seems to happen more frequent when there is a "SELECT 1 FROM x WHERE false" and therefor returning null instead of int.
It still happens, but very rarely, if the query is changed to "SELECT 'dummy' FROM x WHERE false".

Reproduction Steps

I've created a GitHub project with the code to reproduce this issue.
https://github.com/emilsteen/ExecutionEngineExceptionInOleDbJetDatabase

ExecutionEngineExceptionInOleDbJetDatabase.zip

• If the pre-processor-if for "sequential" fields is enabled, the issue occurs much less frequent.
• If the code is rewritten to only use a single database connection, it runs much faster and might use up to 100 loops before the issue occurs.
• The exception seems to be much more frequent when running a release build.
• If all the fields in the SQL-queries are [bracketed] (there is a flag for that in the test code), the issue do not seem to be reproducable.

Expected behavior

Please, do not throw a fatal panic exceptions that cannot be caught and handled and therefor needs days to find a workaround for. [=

Actual behavior

System.ExecutionEngineException in .NET 10 and System.AccessViolationException in .NET 8.

Regression?

I have no idea.

Known Workarounds

• Add brackets to all the fields (and tables) in the SQL-queries.
  Of course, since it looks like a memory issue, this workaround might only move data around in memory, or they're accidentally changing the execution timing or code path in the provider in a way that reduces (but doesn't eliminate) the race window, and might crash some other queries instead.
• Another workaround seems to be to replace the failing SQL-query with "SELECT id_column FROM TestTable WHERE id_column = ?", or "SELECT COUNT(*) FROM TestTable WHERE id_column = ?" (instead of "SELECT 1 FROM TestTable WHERE id_column = ?").

Configuration

• This has been tested on .NET 8 (OleDb 8.0.0) and .NET 10 (OleDb 10.0.3) and .NET 10 (OleDb 7.0.0).
• Has only been confirmed on Windows 10, but both in Visual Studio (debug and release) and on installed computer with Microsoft Access Database Engine 2016 Redistributable.
• Tested with x64 (Xeon and on Hyper-V).
• Tested with "Provider=Microsoft.ACE.OLEDB.12.0" and "Provider=Microsoft.ACE.OLEDB.16.0" (the example project uses 12, but I see no difference when changing).

[dotnet-policy-service]

Tagging subscribers to this area: @SamMonoRT, @dotnet/efteam
See info in area-owners.md if you want to be subscribed.

[emilsteen]

I do not expect this to be fixed within reasonable time, and I will probably switch database layer before that.
The workarounds seems to be working, so prioritize it with the lowest priority.