VACUUM INTO fails on DB with virtual colums

(1) By ddevienne on 2024-04-08 17:37:47 [source]

Hi. We're having a weird issue. The table and file names have been changed, but nothing else.

D:\>sqlite3 z.db
SQLite version 3.44.2 2023-11-24 11:41:44 (UTF-16 console I/O)
Enter ".help" for usage hints.
sqlite> vacuum into 'copy.db';
Runtime error: table vacuum_db.Foo has 15 columns but 17 values were supplied

I've tried doing a small repro, with similar generated-always-virtual columns
having cascading FKs, but so far, I can't reproduce. The DB is not huge, but
still 12 MB, and proprietary, so can't easily share it publicly.

Is this a known bug already fixed? (if it is a bug, not sure yet).
The real DB passes pragma integrity_check and pragma foreign_key_check.

The table Foo it complains about have 17 columns indeed, two of them virtual.
Foo has two FKs, one to another table, the other self-referential.
If I DROP all other tables from the DB, except those two, the failure goes away.
So even though the error message says Foo, the other tables somehow participate?
It's really weird.

Above, after drop'ing the table, I did a vacuum, to make the DB smaller.
This gave me the idea of just vacuum'ing the DB in-place (not dropping anything),
after which vacuum into works! Yet as I wrote, that DB passes CHECKs, so is apparently not corrupt.
What is surprising to me, is the fact vacuum into (KO) somehow differs from vacuum (OK) on the same DB.

Richard, are you interested in having a look at that 12 MB DB as-is?

(2) By Stephan Beal (stephan) on 2024-04-08 19:33:47 in reply to 1 [link] [source]

Richard, are you interested in having a look at that 12 MB DB as-is?

Please post a link to the db to support at this domain and reference this forum post in the mail or subject line. (Or, if you prefer, just post it here in the forum.)

(3) By ddevienne on 2024-04-09 11:16:36 in reply to 2 [link] [source]

Hi Stephan,

Mail sent as instructed, from email associated to my Forum account.
I attached the DB directly, which thanks to 7Zip shrunk to 1.5MB.
I further confirmed that the latest release SQLite (3.45.2) also shows the issue.

Thanks, --DD

(4) By Stephan Beal (stephan) on 2024-04-09 11:23:31 in reply to 3 [link] [source]

Mail sent as instructed, from email associated to my Forum account.

It's arrived and is in the queue for poking a stick at :).

(5) By Richard Hipp (drh) on 2024-04-09 14:11:48 in reply to 1 [link] [source]

Tricky. Problem fixed on trunk.

Repro case for the CLI running on unix:

.shell rm -f x1.db x2.db
.open x1.db
CREATE TABLE t1(a INT, b INT, c AS (a+b), CHECK(a IN (1,2,3)));
INSERT INTO t1 VALUES(1,2),(3,4);
.open :memory:
.shell chmod 444 x1.db
.open x1.db
VACUUM INTO 'x2.db';

(6) By ddevienne on 2024-04-09 15:08:51 in reply to 5 [link] [source]

Excellent, thank you Richard.

I think this is the very first genuine bug we find.

But after reading your description, there's still one thing I don't understand.

my small repro had both vcolumns and check constraints, yet did not fail
on the bigger DB, running vacuum in-place before made vacuum-into work.

Neither symptoms are explained away from your comments, that I can fathom at least. So what am I missing, please?