Hi, I have a use case in which a table is being used to associate integers to unique strings. When loading the strings, it's possible there may be some redundancies, which INSERT OR IGNORE successfully ignores. However, redundant values that should have been ignored _sometimes_ have a side effect that is undesired: they consume extra rowids. Here are a couple examples:
-- Undesired behavior, side effect on rowid: CREATE TABLE StringIntern ( id INTEGER, string TEXT, PRIMARY KEY (id) ); CREATE UNIQUE INDEX StringIntern_string ON StringIntern(string); INSERT OR IGNORE INTO StringIntern(string) VALUES ('a'), ('b'); INSERT OR IGNORE INTO StringIntern(string) VALUES ('a'), ('b'), ('c'); -- 'a' and 'b' have a side effect on the rowid .dump # >>> # INSERT INTO "StringIntern" VALUES(1,'a'); # INSERT INTO "StringIntern" VALUES(2,'b'); # INSERT INTO "StringIntern" VALUES(5,'c'); <-- undesired behavior -- Desired behavior: CREATE TABLE StringIntern ( id INTEGER, string TEXT, PRIMARY KEY (id) ); CREATE UNIQUE INDEX StringIntern_string ON StringIntern(string); INSERT OR IGNORE INTO StringIntern(string) VALUES ('a'), ('b'); INSERT OR IGNORE INTO StringIntern(string) VALUES ('a'), ('b'); -- 'a' and 'b' are ignored (all good here!) INSERT OR IGNORE INTO StringIntern(string) VALUES ('c'); .dump # INSERT INTO "StringIntern" VALUES(1,'a'); # INSERT INTO "StringIntern" VALUES(2,'b'); # INSERT INTO "StringIntern" VALUES(3,'c'); <-- desired behavior Is what I call undesired behavior actually intentional by sqlite3? Would it be possible to change it to the desired behavior? Thanks, David