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

Reply via email to