Without seeing your table definition, this is just a guess, but maybe the
duplicate keys are stored as different types, with the primary key column
having an affinity that doesn't force one particular storage class:
sqlite> CREATE TABLE test(id PRIMARY KEY);
sqlite> INSERT INTO test VALUES('12345');
sqlite> INSERT INTO test VALUES(12345);
sqlite> SELECT id, typeof(id) FROM test;
id typeof(id)
---------- ----------
12345 text
12345 integer
sqlite> SELECT id, typeof(id) FROM test WHERE id = '12345';
id typeof(id)
---------- ----------
12345 text
On Sat, Dec 21, 2019 at 1:26 PM Michael Walker (barrucadu) <
m...@barrucadu.co.uk> wrote:
> Hi,
>
> I've somehow ended up with a table which contains two records for the same
> primary key - well actually I've got two primary keys like that, so I have
> four records with two primary keys between them.
>
> I've been unable to reproduce this from a clean database, so I attach my
> database file to this email.
>
> Here are some oddities:
>
> ```
> $ sqlite3 bookdb.sqlite
> SQLite version 3.28.0 2019-04-16 19:49:53
> Enter ".help" for usage hints.
> sqlite> select * from books where bookIsbn = "9781496030825";
> 9781496030825|Can Such Things Be?||9781496030825.jpg||||Bierce,
> Ambrose||||0||0|London||F
> sqlite> select * from books where bookIsbn = "9780099477310";
> 9780099477310|Catch-22||9780099477310.jpg||||Heller,
> Joseph||||0||0|London||F
> sqlite> .output books_issue
> sqlite> .dump books
> sqlite> .quit
>
> $ grep "9781496030825" < books_issue
> INSERT INTO books VALUES('9781496030825','Can Such Things
> Be?','','9781496030825.jpg','','','','Bierce,
> Ambrose',NULL,NULL,NULL,0,NULL,0,'London','','F');
> INSERT INTO books VALUES('9781496030825','Can Such Things
> Be?','','9781496030825.jpg','','','','Bierce,
> Ambrose',NULL,NULL,NULL,0,NULL,0,'London','','F');
>
> $ grep "9780099477310" < books_issue
> INSERT INTO books
> VALUES('9780099477310','Catch-22','','9780099477310.jpg','','','','Heller,
> Joseph',NULL,NULL,NULL,0,NULL,0,'London','','F');
> INSERT INTO books
> VALUES('9780099477310','Catch-22','','9780099477310.jpg','','','','Heller,
> Joseph',NULL,NULL,NULL,0,NULL,0,'London','','F');
>
> $ sqlite3 bookdb.sqlite
> SQLite version 3.28.0 2019-04-16 19:49:53
> Enter ".help" for usage hints.
> sqlite> drop table books;
> sqlite>
>
> $ sqlite3 bookdb.sqlite < books_issue
> Error: near line 697: UNIQUE constraint failed: books.bookIsbn
> Error: near line 698: UNIQUE constraint failed: books.bookIsbn
> ```
>
> Updating either affected record results in the second copy in the .dump
> output being updated, the first copy has the original state.
>
> The table has always had a primary key constraint, so I'm not sure how
> it's ended up in its current state. However, even if there were not a
> primary key constraint, there do seem to be two very real bugs here: SELECT
> gives different results to .dump, and .dump is producing output which can't
> be restored.
>
> I'm not sure if you'll be able to make anything of this, as I say I
> haven't been able to reproduce it from a blank database, but I figure
> you'll be better at debugging this than me.
>
> Thanks
>
> --
> Michael Walker (http://www.barrucadu.co.uk)
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
