Hi Shawn, Thanks for your response. Though that doesn't seem to be the case:
sqlite> select bookIsbn, typeof(bookIsbn) from books where bookIsbn = "9781496030825"; 9781496030825|text sqlite> select bookIsbn, typeof(bookIsbn) from books where bookIsbn = "9780099477310"; 9780099477310|text The column is a VARCHAR: CREATE TABLE `books` ( `bookIsbn` VARCHAR NOT NULL PRIMARY KEY, `bookTitle` VARCHAR NOT NULL, `bookSubtitle` VARCHAR NOT NULL, `bookCover` VARCHAR NULL, `bookVolume` VARCHAR NOT NULL, `bookFascicle` VARCHAR NOT NULL, `bookVoltitle` VARCHAR NOT NULL, `bookAuthor` VARCHAR NOT NULL, `bookTranslator` VARCHAR NULL, `bookEditor` VARCHAR NULL, `bookSorting` VARCHAR NULL, `bookRead` BOOLEAN NOT NULL, `bookLastRead` TIMESTAMP NULL, `bookNowReading` BOOLEAN NOT NULL, `bookLocation` VARCHAR NOT NULL, `bookBorrower` VARCHAR NOT NULL, `bookCategoryCode` VARCHAR NOT NULL, FOREIGN KEY(`bookCategoryCode`) REFERENCES `book_categories`(`categoryCode`) ); I'm not sure the attachment to my first email got through, so here's the database: https://misc.barrucadu.co.uk/forever/82e5584a-e4a8-4804-8abe-8f00be73f725/bookdb.sqlite ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Saturday, 21 December 2019 21:37, Shawn Wagner <shawnw.mob...@gmail.com> wrote: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users