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.
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> 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);
Disclaimer: /me is not sqlite dev team member, review carefully, use with care.
The author or authors of this code dedicate any and all copyright interest
in this code to the public domain. We make this dedication for the benefit
of the public at large and to the detriment of our heirs and successors.
We intend this dedication to be an overt act of relinquishment in perpetuity
of all present and future rights to this code under copyright law.
Signed-off-by: Yuriy M. Kaminskiy <yum...@gmail.com>
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
@@ -1626,6 +1626,7 @@ static int xferOptimization(
int regAutoinc; /* Memory register used by AUTOINC */
int destHasUniqueIdx = 0; /* True if pDest has a UNIQUE index */
int regData, regRowid; /* Registers holding data and rowid */
+ int keyConf = pDest->iPKey>=0 ? pDest->keyConf : OE_Default;
if( pSelect==0 ){
return 0; /* Must be of the form INSERT INTO ... SELECT ... */
@@ -1639,7 +1640,22 @@ static int xferOptimization(
}
#endif
if( onError==OE_Default ){
- onError = OE_Abort;
+ if( keyConf==OE_Abort || keyConf==OE_Rollback )
+ /* can be handled - take ON CONFLICT from table declaration */
+ onError = keyConf;
+ else {
+ if( keyConf==OE_Default )
+ keyConf = OE_Abort;
+ else {
+ /* Not default and cannot be handled;
+ ** fallback to regular transfer if destination is not empty (below)
+ */
+ }
+ onError = OE_Abort;
+ }
+ } else {
+ /* statement ON CONFLICT overrides table ON CONFLICT */
+ keyConf = onError;
}
if( onError!=OE_Abort && onError!=OE_Rollback ){
return 0; /* Cannot do OR REPLACE or OR IGNORE or OR FAIL */
@@ -1766,7 +1782,8 @@ static int xferOptimization(
iDest = pParse->nTab++;
regAutoinc = autoIncBegin(pParse, iDbDest, pDest);
sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite);
- if( (pDest->iPKey<0 && pDest->pIndex!=0) || destHasUniqueIdx ){
+ if( (pDest->iPKey<0 && pDest->pIndex!=0) || destHasUniqueIdx ||
+ (/*pDest->iPKey>=0 && */keyConf!=onError) ) {
/* If tables do not have an INTEGER PRIMARY KEY and there
** are indices to be copied and the destination is not empty,
** we have to disallow the transfer optimization because the
@@ -1776,6 +1793,9 @@ static int xferOptimization(
** we also disallow the transfer optimization because we cannot
** insure that all entries in the union of DEST and SRC will be
** unique.
+ **
+ ** Or if destination INTEGER PRIMARY KEY has ON CONFLICT clause
+ ** that we cannot handle and destination is not empty.
*/
addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iDest, 0);
emptyDestTest = sqlite3VdbeAddOp2(v, OP_Goto, 0, 0);
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
