Hello,

I'm using a script to "rebuild" an existing sqlite3 database with the sql schema in my repository. Afterwards I compare the result of the sqlite3 cli .sha3sum function on the old database file with the new one. If I understand this function correctly, this validates that all data is still there.

This worked great for a while, but now started throwing ".sha3sum failed" errors. It seems to be failing on a few tables, always the same ones. On other tables it works fine. Requesting the sha3sum for the entire database fails when the failing tables are in there.

I can trigger the error with this SQL file in sqlite 3.42.0:

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "food_preferences" (
        "id"    INTEGER NOT NULL UNIQUE,
        "name"  TEXT NOT NULL COLLATE NOCASE,
        "description"   TEXT COLLATE NOCASE,
        "order" INTEGER NOT NULL DEFAULT 100,
        PRIMARY KEY("id")
) STRICT;
INSERT INTO "food_preferences" VALUES (1,'Vegetarisch','foo',300);
INSERT INTO "food_preferences" VALUES (2,'Pescotarisch','bar',300);
INSERT INTO "food_preferences" VALUES (3,'Vegan','baz',300);
INSERT INTO "food_preferences" VALUES (4,'Glutenvrij',NULL,200);
INSERT INTO "food_preferences" VALUES (5,'Lactosevrij',NULL,200);
INSERT INTO "food_preferences" VALUES (6,'Suikervrij',NULL,200);
INSERT INTO "food_preferences" VALUES (7,'Geen olijven',NULL,100);
INSERT INTO "food_preferences" VALUES (8,'Geen ingewanden',NULL,100);
INSERT INTO "food_preferences" VALUES (9,'Geen tomaten',NULL,100);
INSERT INTO "food_preferences" VALUES (10,'Geen wortel',NULL,100);
COMMIT;

With a new sqlite3 database file:

❯ sqlite3 test.db

SQLite version 3.42.0 2023-05-16 12:36:15
Enter ".help" for usage hints.

sqlite> .read test.sql

sqlite> .schema
CREATE TABLE IF NOT EXISTS "food_preferences" (
        "id"    INTEGER NOT NULL UNIQUE,
        "name"  TEXT NOT NULL COLLATE NOCASE,
        "description"   TEXT COLLATE NOCASE,
        "order" INTEGER NOT NULL DEFAULT 100,
        PRIMARY KEY("id")
) STRICT;

sqlite> SELECT * FROM "food_preferences";
1|Vegetarisch|foo|300
2|Pescotarisch|bar|300
3|Vegan|baz|300
4|Glutenvrij||200
5|Lactosevrij||200
6|Suikervrij||200
7|Geen olijven||100
8|Geen ingewanden||100
9|Geen tomaten||100
10|Geen wortel||100

sqlite> .sha3sum
d9ccdc5e0f5071b1aa66b550b620d84c33f865469b6606947597a37d
.sha3sum failed.

sqlite> .sha3sum "food_preferences"
d9ccdc5e0f5071b1aa66b550b620d84c33f865469b6606947597a37d|food_preferences
.sha3sum failed.

What am I doing wrong here?