Microsoft.Data.Sqlite incorrectly adds to RecordsAffected for additional statements

[jez9999]

Bug description

When doing a multi-statement transaction using Microsoft.Data.Sqlite that causes records to be affected, the provider seems to add the number of records affected on to the total for any statements that don't affect any records. This keeps happening for subsequent statements until there's another statement which affects records, at which point that new number is added for this statement and all subsequent statements that don't affect any records. The result is an incorrect number of records affected at the end of the statement. The repro code below shows this issue; when a DROP statement is added, records affected is incorrectly incremented by 1, the number of records affected by the most recent statement that affected records. When a CREATE statement is also added the results again incorrectly increments by 1.

Your code

using Microsoft.Data.Sqlite;

namespace SqliteBugRepro;

internal class Program {
	private const string _dbFile = "testDb.sqlite";
	private const string _createAndInsert = "CREATE TABLE foo(bar TEXT NOT NULL); CREATE TABLE xyz(aaa TEXT NOT NULL); INSERT INTO foo(bar) VALUES('baz'); INSERT INTO foo(bar) VALUES('baz2');";
	private const string _drop = "DROP TABLE xyz;";
	private const string _create = "CREATE TABLE xyz(aaa TEXT NOT NULL);";

	static void Main(string[] args) {
		Console.WriteLine("Creating new SQLite DB.");
		CreateSqliteDb();

		int recordsAffected;

		Console.WriteLine("Doing DB commands (no DROP).");
		recordsAffected = DoCommandsNoDrop();

		Console.WriteLine($"Records affected: {recordsAffected}"); // 2

		Console.WriteLine("Creating new SQLite DB.");
		CreateSqliteDb();

		Console.WriteLine("Doing DB commands (with DROP).");
		recordsAffected = DoCommandsWithDrop();

		Console.WriteLine($"Records affected: {recordsAffected}"); // 3 (???)

		Console.WriteLine("Creating new SQLite DB.");
		CreateSqliteDb();

		Console.WriteLine("Doing DB commands (with DROP and CREATE).");
		recordsAffected = DoCommandsWithDropAndCreate();

		Console.WriteLine($"Records affected: {recordsAffected}"); // 4 (???)
	}

	private static void CreateSqliteDb() {
		File.WriteAllBytes(_dbFile, []);
	}

	private static int DoCommandsNoDrop() {
		var connStringBuilder = new SqliteConnectionStringBuilder {
			Pooling = false,
			DataSource = _dbFile
		};

		using var conn = new SqliteConnection(connStringBuilder.ConnectionString);
		conn.Open();

		using var cmd = conn.CreateCommand();
		cmd.CommandText = $"{_createAndInsert}";
		using var reader = cmd.ExecuteReader();

		do {
			var currentRecordsAffected = reader.RecordsAffected;
		}
		while (reader.NextResult());

		return reader.RecordsAffected;
	}

	private static int DoCommandsWithDrop() {
		var connStringBuilder = new SqliteConnectionStringBuilder {
			Pooling = false,
			DataSource = _dbFile
		};

		using var conn = new SqliteConnection(connStringBuilder.ConnectionString);
		conn.Open();

		using var cmd = conn.CreateCommand();
		cmd.CommandText = $"{_createAndInsert} {_drop}";
		using var reader = cmd.ExecuteReader();

		do {
			var currentRecordsAffected = reader.RecordsAffected;
		}
		while (reader.NextResult());

		return reader.RecordsAffected;
	}

	private static int DoCommandsWithDropAndCreate() {
		var connStringBuilder = new SqliteConnectionStringBuilder {
			Pooling = false,
			DataSource = _dbFile
		};

		using var conn = new SqliteConnection(connStringBuilder.ConnectionString);
		conn.Open();

		using var cmd = conn.CreateCommand();
		cmd.CommandText = $"{_createAndInsert} {_drop} {_create}";
		using var reader = cmd.ExecuteReader();

		do {
			var currentRecordsAffected = reader.RecordsAffected;
		}
		while (reader.NextResult());

		return reader.RecordsAffected;
	}
}

Stack traces

Microsoft.Data.Sqlite version

9.0.2

Target framework

.NET 9.0

Operating system

Windows 10