Yuriy Kaminskiy wrote: > David wrote: >> Simon L wrote 2011-10-25 06:20: >>> To reproduce this problem, enter the following 5 SQL statements at the >>> SQLite command line. >>> >>> create table X(id INTEGER primary key ON CONFLICT REPLACE); >>> create table Y(id INTEGER primary key ON CONFLICT REPLACE); >>> insert into X values (1); >>> insert into Y select * from X; >>> insert into Y select * from X; >>> >>> >>> When I tried to run the last SQL statement twice, SQLite produced the >>> following error message. >>> Error: PRIMARY KEY must be unique >>> >>> >>> Is this a bug? Please advise. Thank you.
>> This certainly looks like a bug. I got a constraint failure when I tried >> it in sqlite 3.7.8. >> >> But it works fine when you state the column name explicitly in the >> select clause. >> >> Like this: >> >> create table X(id INTEGER primary key ON CONFLICT REPLACE); >> create table Y(id INTEGER primary key ON CONFLICT REPLACE); >> insert into X values (1); >> insert into Y select id from X; >> insert into Y select id from X; >> >> I recall the "INSERT INTO ... SELECT * ..." had a bug related to foreign >> key checks, >> which was reported on the mailing list earlier this year: >> >> http://www.sqlite.org/src/tktview?name=6284df89de >> >> Hopefully, a member of the sqlite dev team will acknowledge this bug soon. > > Thanks for pointer; root cause, indeed, transfer optimization (it ignores > table > INTEGER PRIMARY KEY's ON CONFLICT clause, fix was easy, patch below (use > table's > ON CONFLICT clause by default; falls back to regular transfer if destination > table is not empty and we cannot handle ON CONFLICT resolution); > > Index: sqlite3-3.7.8/src/insert.c > =================================================================== > --- sqlite3-3.7.8.orig/src/insert.c 2011-10-25 15:20:26.000000000 +0400 > +++ sqlite3-3.7.8/src/insert.c 2011-10-25 15:54:54.000000000 +0400 Ping. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
