FOREIGN KEY referencing column in vec0 virtual table results in malformed database

[dymk]

The following SQL, when ran against sqlite-vec 1.6.0, will result in a malformed database -

CREATE VIRTUAL TABLE IF NOT EXISTS embeddings USING vec0 (
    id INTEGER PRIMARY KEY,
    embedding float [4]
);

CREATE TABLE IF NOT EXISTS
    widgets (
        widget_id INTEGER NOT NULL,
        embedding_id INTEGER NOT NULL,
        FOREIGN KEY (embedding_id) REFERENCES embeddings (id)
    );

INSERT INTO embeddings (embedding) VALUES ('[1, 2, 3, 4]');
INSERT INTO widgets (widget_id, embedding_id) VALUES (1, (SELECT last_insert_rowid()));

The final INSERT will result in a database malformed error:

Error: SqliteFailure(Error { code: DatabaseCorrupt, extended_code: 11 }, Some("database disk image is malformed"))

If the foreign key constraint is removed (FOREIGN KEY (embedding_id) REFERENCES embeddings (id)), then there is no malformed disk image error.

[asg017]

@dymk thanks for reporting! Can you share the version of SQLite you're on?

I'm trying to reproduce on my machine on SQLite 3.41, but seems to work as expected:

.load vec0
.mode qbox

select sqlite_version(), vec_version();

CREATE VIRTUAL TABLE IF NOT EXISTS embeddings USING vec0 (
    id INTEGER PRIMARY KEY,
    embedding float [4]
);

CREATE TABLE IF NOT EXISTS
    widgets (
        widget_id INTEGER NOT NULL,
        embedding_id INTEGER NOT NULL,
        FOREIGN KEY (embedding_id) REFERENCES embeddings (id)
    );

INSERT INTO embeddings (embedding) VALUES ('[1, 2, 3, 4]');
INSERT INTO widgets (widget_id, embedding_id) VALUES (1, (SELECT last_insert_rowid()));

select * from widgets;
select * from embeddings;

Output:

┌──────────────────┬───────────────┐
│ sqlite_version() │ vec_version() │
├──────────────────┼───────────────┤
│ '3.41.0'         │ 'v0.1.6'      │
└──────────────────┴───────────────┘
┌───────────┬──────────────┐
│ widget_id │ embedding_id │
├───────────┼──────────────┤
│ 1         │ 1            │
└───────────┴──────────────┘
┌────┬─────────────────────────────────────┐
│ id │              embedding              │
├────┼─────────────────────────────────────┤
│ 1  │ x'0000803f000000400000404000008040' │
└────┴─────────────────────────────────────┘

[doronnac]

Hi, I've encountered this bug as well. Happens after around 20 insertions. I'll try to create a reproduction soon.

EDIT: Read some docs, this seems to not be possible in SQLite.