The mailing list strips attachments, btw. Anyways, looking at that, yeah, they're all text values:
sqlite> SELECT bookIsbn, typeof(bookIsbn) FROM books WHERE bookAuthor LIKE '%Ambrose%'; bookIsbn typeof(bookIsbn) ---------- ---------------- 0486280381 text 9781496030 text 9781496030 text There are other problems with the database too: sqlite> PRAGMA integrity_check; integrity_check --------------------------------------------------- row 649 missing from index sqlite_autoindex_books_1 row 659 missing from index sqlite_autoindex_books_1 row 665 missing from index sqlite_autoindex_books_1 row 667 missing from index sqlite_autoindex_books_1 row 674 missing from index sqlite_autoindex_books_1 row 676 missing from index sqlite_autoindex_books_1 I'd start going through https://www.sqlite.org/howtocorrupt.html and trying to figure out if anything there might have happened. On Sat, Dec 21, 2019 at 1:43 PM Michael Walker (barrucadu) < m...@barrucadu.co.uk> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
