Update thinks I'm changing a partition key when I'm not, in very narrow circumstances

[aethertap]

When I try to update non-partition-key fields in a table that has a partition key, and the update query includes a WHERE EXISTS... clause, I get the error message about not supporting update on partition keys. This isn't blocking my development and it seems very... specific, but I do think it's incorrect behavior so I wanted to mention it here.

Here is the smallest reproduction code I could come up with (I tested it with the vec0.so from sqlite-vec-0.1.7-alpha.9)

.load ./vec0

CREATE TABLE images (id INTEGER PRIMARY KEY, orphaned_ts INTEGER);
INSERT INTO images VALUES (1, NULL), (2, 1234567890);

CREATE VIRTUAL TABLE embeddings USING vec0 (
  id INTEGER PRIMARY KEY NOT NULL,
  image_id INTEGER,
  is_orphaned INTEGER NOT NULL,
  image_kind INTEGER PARTITION KEY,
  embedding_vector float[4] distance_metric=cosine
);

INSERT INTO embeddings (image_id, is_orphaned, image_kind, embedding_vector)
VALUES (1, 0, 1, vec_f32(zeroblob(16)));
INSERT INTO embeddings (image_id, is_orphaned, image_kind, embedding_vector)
VALUES (2, 0, 1, vec_f32(zeroblob(16)));

-- Works:
UPDATE embeddings SET is_orphaned = 0 WHERE is_orphaned = 1;

-- Fails with "UPDATE on partition key columns are not supported yet"
-- even though is_orphaned is not the partition key:
UPDATE embeddings SET is_orphaned = 0
WHERE is_orphaned = 0
AND EXISTS (SELECT 1 FROM images WHERE embeddings.image_id = images.id);

[murillodutt]

Confirming awareness of this issue. We recently evaluated upgrading from alpha.2 to alpha.10 and this bug was flagged during our analysis.

While our current queries don't use WHERE EXISTS with vec0 UPDATEs, this is a concern for future development. The false positive on partition key detection could be particularly dangerous because it silently blocks a valid operation rather than corrupting data — meaning users might not realize their UPDATEs are failing until they notice stale data.

Would be helpful to know if this is specific to WHERE EXISTS or if other subquery patterns in WHERE clauses trigger the same false positive.

— Murillo Dutt

[asg017]

@murillodutt just to confirm: Do you have a query that works in v0.1.7-alpha.2 but fails with v0.1.7-alpha.9?

Also: do you mind sharing the exact schema/SQL query you're running, or a minimal example? Can also provide via email, it's in my profile