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 <[email protected]>
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) <
> [email protected]> 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
> > [email protected]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
