sqlite 3.26.0 db corruption regression

up.sql

-- # steps to reproduce
-- rm -f foo.db; sqlite3 foo.db < up.sql; echo '.schema' | sqlite3 foo.db
--
-- debian stable (works as expected):
-- 3.16.2 2017-01-06 16:32:41 a65a62893ca8319e89e48b8a38cf8a59c69a8209
--
-- archlinux (corrupted database):
-- 3.26.0 2018-12-01 12:34:55 bf8c1b2b7a5960c282e543b9c293686dccff272512d08865f4600fb58238b4f9

CREATE TABLE foo (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
);

CREATE TABLE bar (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    foo_id INTEGER NOT NULL,
    FOREIGN KEY(foo_id) REFERENCES foo(id) ON DELETE CASCADE
);

-- this is the recommended way for some alter table operations
-- https://www.sqlite.org/lang_altertable.html
PRAGMA foreign_keys=off;

ALTER TABLE foo RENAME TO _foo_old;

CREATE TABLE foo (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
);

DROP TABLE _foo_old;

PRAGMA foreign_keys=on;

-- your database is now corrupted, bar has a foreign key constraint to _foo_old:
/*
CREATE TABLE bar (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    foo_id INTEGER NOT NULL,
    FOREIGN KEY(foo_id) REFERENCES "_foo_old"(id) ON DELETE CASCADE
);
*/

I've bisected this problem using this script:

#!/bin/sh
set -eu
rm -rf bld
echo 6fcf9dfbd479ed82697fee719b9f8c610a11ff2a > manifest.uuid
echo 6fcf9dfbd479ed82697fee719b9f8c610a11ff2a > manifest
mkdir bld
cd bld
../configure
make sqlite3
echo '[*] build successful'

rm -f foo.db
echo '[*] setting up database'
./sqlite3 foo.db < ../bisect-up.sql
echo '[*] checking schema'

if echo '.schema' | ./sqlite3 foo.db | grep -q '_foo_old'; then
    echo db corrupted
    exit 1
fi

and found this commit as the root cause: mackyle/sqlite@2defce4

$ git bisect good version-3.16.2
$ git bisect bad version-3.26.0
$ git bisect run ./bisect-test.sh
[...]
[*] build successful
[*] setting up database
[*] checking schema
2defce45c5670e0258374eeb2e293fcf6f82bddd is the first bad commit
commit 2defce45c5670e0258374eeb2e293fcf6f82bddd
Author: Dan Kennedy <danielk1977@gmail.com>
Date:   Fri Nov 9 20:04:05 2018 +0000

    When a table is renamed using "ALTER TABLE RENAME TO", update any REFERENCES
    clauses that refer to the table, unless "PRAGMA legacy_alter_table" is true
    and "PRAGMA foreign_keys" is set to false (i.e. so that when "PRAGMA
    legacy_alter_table" is set behaviour is still compatible with versions 3.24
    and earlier).

:040000 040000 8bf439a32f5f8f53da47c26f4d477d2f3c5b5812 46ab9d64d29f21cfc859956070acae47b858b5cf M	src
:040000 040000 8d6a053b4b84965df3b3be666c916dae4e3d181d f89365c68c92e673b337e83a5f44d60521e9943d M	test
bisect run success

Hi,

This is as expected. As of 3.26.0, when a table is renamed using ALTER TABLE RENAME TO then all references to it in REFERENCES clauses that are part of other tables are also updated. You can turn this behaviour off (restoring the 3.24.0 behaviour) using "PRAGMA legacy_alter_table = ON". This probably should be documented as part of the ALTER TABLE documentation.

Technically speaking, the procedure used in the script is not actually what is recommended on sqlite.org. The recommendation would be:

 CREATE TABLE new_foo (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL);
 INSERT INTO new_foo SELECT * FROM foo;
 DROP TABLE foo;
 ALTER TABLE new_foo RENAME TO foo;

which I think would probably work.

Regards,
Dan Kennedy.