Yuriy Kaminskiy wrote: > 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.
Okey, I've noticed http://www.sqlite.org/src/info/6f9898db7f Won't that result in performance regression in VACUUM? [only on tables with INTEGER PRIMARY KEY ON REPLACE xxx, obviously; so not *terrible* big deal - but still] When we insert into empty table, ON CONFLICT will never trigger, so we can safely use optimized xfer. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
