I am receiving duplicate entries for a given 'row' in a select result. It looks like the select is finding multiple rows with different rowid (the built-in hidden column) for a given primary key. My initial guess is this is due to some sort of database corruption, but any insight would be helpful.
Selecting by id returns only the expected single row: sqlite> select * from mail_item where id=714474; 714474|714463 sqlite> select rowid,id from mail_item where id=714474; 193254|714474 Selecting by parent_id returns multiple records: sqlite> select id from mail_item where parent_id=714463; 714474 714474 714474 714474 714474 714474 sqlite> select rowid,id from mail_item where parent_id=714463; 193249|714474 193250|714474 193251|714474 193252|714474 193253|714474 193254|714474 Here is a simplified version of the table. There are other columns which have been omitted for brevity. CREATE TABLE foo ( id INTEGER UNSIGNED NOT NULL PRIMARY KEY, parent_id INTEGER UNSIGNED, CONSTRAINT fk_mail_item_parent_id FOREIGN KEY (parent_id) REFERENCES foo(id) ON UPDATE CASCADE ); CREATE INDEX i_mail_item_parent_id ON mail_item(parent_id); _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
