System.Data.SQLite

This problem occurs, when you rename a column and then select all columns from the affected table without closing the connection in between. The rename commands execute without errors, because a database tool such as DBeaver shows the correct column names. Here is a failing Unit test (NUnit):

        [Test]
        public void RenameColumnBug()
        {
            File.Delete(@"c:\temp\4.db");
            using (var connection = new SQLiteConnection(@"Data Source=c:\temp\4.db"))
            {
                connection.Open();

                // Create a test table
                using (var transaction = connection.BeginTransaction())
                {
                    using (var command = connection.CreateCommand())
                    {
                        command.Transaction = transaction;
                        command.CommandText = "create table test (Id int, Name text, primary key(Id))";
                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }

                // Rename a column according to https://www.sqlite.org/lang_altertable.html
                using (var transaction = connection.BeginTransaction())
                {
                    using (var command = connection.CreateCommand())
                    {
                        command.Transaction = transaction;
                        command.CommandText = "PRAGMA schema_version";
                        var schemaVersion = (long) command.ExecuteScalar();
                        command.CommandText = "PRAGMA writable_schema=ON";
                        command.ExecuteNonQuery();
                        command.CommandText = "UPDATE sqlite_master SET sql='create table test (Id int, RenamedColumn text, primary key(Id))' WHERE type='table' AND name='test'";
                        command.ExecuteNonQuery();
                        ++schemaVersion;
                        command.CommandText = "PRAGMA schema_version=" + schemaVersion;
                        command.ExecuteNonQuery();
                        command.CommandText = "PRAGMA writable_schema=OFF";
                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }

                // NOTE: With the following two lines, the test passes
                //connection.Close();
                //connection.Open();

                // Select all columns, the result returns the old "Name" column
                using (var transaction = connection.BeginTransaction())
                {
                    using (var command = connection.CreateCommand())
                    {
                        command.Transaction = transaction;
                        command.CommandText = "select * from test";
                        using (var reader = command.ExecuteReader())
                        {
                            var table = new DataTable();
                            table.Load(reader);
                            Assert.That(table.Columns.Contains("Name"), Is.False);
                            Assert.That(table.Columns.Contains("RenamedColumn"), Is.True);
                        }
                    }
                    transaction.Commit();
                }
            }
        }

Simplified test case using the SQLite command line shell:

.mode columns
.headers on

BEGIN;
CREATE TABLE t1(x, y, PRIMARY KEY(x));
INSERT INTO t1 VALUES(1, 'one');
COMMIT;

BEGIN;
PRAGMA schema_version;
PRAGMA writable_schema = ON;
UPDATE sqlite_master SET sql = 'CREATE TABLE t1(x, y2, PRIMARY KEY(x))' WHERE type = 'table' AND name = 't1';
PRAGMA schema_version = 2;
PRAGMA writable_schema = OFF;
COMMIT;
BEGIN;
SELECT * FROM t1;
COMMIT;

This is not a bug.

Using PRAGMA writable_schema in this way requires that the connection to be
closed and reopened for the changes to be "visible".