On 2015-09-22 06:37 AM, Hugues Bruant wrote: >> If you can capture a malfunctioning trace, and send in the database >> file and the SQL statement that is running, that should allow us to >> localize the problem. >> > Trace for the failing UPDATE: > > cv_s=1 > cv_o=b8b9f4... > cv_t=28 -> 29 > > SQL: [update cv set cv_t=? where cv_s=? and cv_o=?] > VDBE Program Listing: > 0 Init 0 25 0 00 Start at 25 > 1 Null 0 1 2 00 r[1..2]=NULL > 2 OpenWrite 0 74 0 3 00 root=74 iDb=0; cv > 3 OpenWrite 1 75 0 k(2,nil,nil) 02 root=75 iDb=0; > sqlite_autoindex_cv_1 > 4 Explain 0 0 0 SEARCH TABLE cv USING INDEX > sqlite_autoindex_cv_1 (cv_s=? AND cv_o=?) 00 > 5 Variable 2 6 0 00 r[6]=parameter(2,) > 6 IsNull 6 15 0 00 if r[6]==NULL goto 15 > 7 Variable 3 7 0 00 r[7]=parameter(3,) > 8 IsNull 7 15 0 00 if r[7]==NULL goto 15 > 9 Affinity 6 1 0 D 00 affinity(r[6]) > 10 SeekGE 1 15 6 2 00 key=r[6..7] > 11 IdxGT 1 15 6 2 00 key=r[6..7] > 12 IdxRowid 1 8 0 00 r[8]=rowid > 13 Seek 0 8 0 00 intkey=r[8] > 14 IdxRowid 1 2 0 00 r[2]=rowid > 15 IsNull 2 24 0 00 if r[2]==NULL goto 24 > 16 Column 0 0 3 00 r[3]=cv.cv_s > 17 Column 0 1 4 00 r[4]=cv.cv_o > 18 Variable 1 5 0 00 r[5]=parameter(1,) > 19 HaltIfNull 1299 2 3 cv.cv_s 01 if r[3]=null halt > 20 HaltIfNull 1299 2 4 cv.cv_o 01 if r[4]=null halt > 21 HaltIfNull 1299 2 5 cv.cv_t 01 if r[5]=null halt > 22 MakeRecord 3 3 9 DAD 00 r[9]=mkrec(r[3..5]) > 23 Insert 0 9 2 cv 05 intkey=r[2] data=r[9] > 24 Halt 0 0 0 00 > 25 Transaction 0 1 56 0 01 > 26 TableLock 0 74 1 cv 00 iDb=0 root=74 write=1 > 27 Goto 0 1 0 00 > VDBE Trace: > 0 Init 0 25 0 00 Start at 25 > SQL-trace: update cv set cv_t=? where cv_s=? and cv_o=? > 25 Transaction 0 1 56 0 01 > 26 TableLock 0 74 1 cv 00 iDb=0 root=74 write=1 > 27 Goto 0 1 0 00 > 1 Null 0 1 2 00 r[1..2]=NULL > REG[1] = NULL > 2 OpenWrite 0 74 0 3 00 root=74 iDb=0; cv > 3 OpenWrite 1 75 0 k(2,nil,nil) 02 root=75 iDb=0; > sqlite_autoindex_cv_1 > 4 Explain 0 0 0 SEARCH TABLE cv USING INDEX > sqlite_autoindex_cv_1 (cv_s=? AND cv_o=?) 00 > 5 Variable 2 6 0 00 r[6]=parameter(2,) > REG[6] = i:0 > 6 IsNull 6 15 0 00 if r[6]==NULL goto 15 > REG[6] = i:0 > 7 Variable 3 7 0 00 r[7]=parameter(3,) > REG[7] = t16[B8B9F4D5F3E8432E86E248C2EBE14D5D......C...H...M]](8) > 8 IsNull 7 15 0 00 if r[7]==NULL goto 15 > REG[7] = t16[B8B9F4D5F3E8432E86E248C2EBE14D5D......C...H...M]](8) > 9 Affinity 6 1 0 D 00 affinity(r[6]) > 10 SeekGE 1 15 6 2 00 key=r[6..7] > REG[6] = i:0 > 11 IdxGT 1 15 6 2 00 key=r[6..7] > 15 IsNull 2 24 0 00 if r[2]==NULL goto 24 > REG[2] = NULL > 24 Halt 0 0 0 00
If I'm reading this right, cv_s looks like 0 and not 1 here (REG[6]). What is the type of the bound variable? Might this be a rounding / truncation inefficiency, perhaps only manifest in OSX? > > > Followed by a failing insert for the same row: > > sqlite:1555: abort at 16 in [insert into cv (cv_s,cv_o,cv_t) > VALUES(?,?,?)]: UNIQUE constraint failed: cv.cv_s, cv.cv_o > > > Trace for the previous successful UPDATE to the same row: > > cv_s=1 > cv_o=b8b9f4... > cv_t=27 -> 28 > > SQL: [update cv set cv_t=? where cv_s=? and cv_o=?] > VDBE Program Listing: > 0 Init 0 25 0 00 Start at 25 > 1 Null 0 1 2 00 r[1..2]=NULL > 2 OpenWrite 0 74 0 3 00 root=74 iDb=0; cv > 3 OpenWrite 1 75 0 k(2,nil,nil) 02 root=75 iDb=0; > sqlite_autoindex_cv_1 > 4 Explain 0 0 0 SEARCH TABLE cv USING INDEX > sqlite_autoindex_cv_1 (cv_s=? AND cv_o=?) 00 > 5 Variable 2 6 0 00 r[6]=parameter(2,) > 6 IsNull 6 15 0 00 if r[6]==NULL goto 15 > 7 Variable 3 7 0 00 r[7]=parameter(3,) > 8 IsNull 7 15 0 00 if r[7]==NULL goto 15 > 9 Affinity 6 1 0 D 00 affinity(r[6]) > 10 SeekGE 1 15 6 2 00 key=r[6..7] > 11 IdxGT 1 15 6 2 00 key=r[6..7] > 12 IdxRowid 1 8 0 00 r[8]=rowid > 13 Seek 0 8 0 00 intkey=r[8] > 14 IdxRowid 1 2 0 00 r[2]=rowid > 15 IsNull 2 24 0 00 if r[2]==NULL goto 24 > 16 Column 0 0 3 00 r[3]=cv.cv_s > 17 Column 0 1 4 00 r[4]=cv.cv_o > 18 Variable 1 5 0 00 r[5]=parameter(1,) > 19 HaltIfNull 1299 2 3 cv.cv_s 01 if r[3]=null halt > 20 HaltIfNull 1299 2 4 cv.cv_o 01 if r[4]=null halt > 21 HaltIfNull 1299 2 5 cv.cv_t 01 if r[5]=null halt > 22 MakeRecord 3 3 9 DAD 00 r[9]=mkrec(r[3..5]) > 23 Insert 0 9 2 cv 05 intkey=r[2] data=r[9] > 24 Halt 0 0 0 00 > 25 Transaction 0 1 56 0 01 > 26 TableLock 0 74 1 cv 00 iDb=0 root=74 write=1 > 27 Goto 0 1 0 00 > VDBE Trace: > 0 Init 0 25 0 00 Start at 25 > SQL-trace: update cv set cv_t=? where cv_s=? and cv_o=? > 25 Transaction 0 1 56 0 01 > 26 TableLock 0 74 1 cv 00 iDb=0 root=74 write=1 > 27 Goto 0 1 0 00 > 1 Null 0 1 2 00 r[1..2]=NULL > REG[1] = NULL > 2 OpenWrite 0 74 0 3 00 root=74 iDb=0; cv > 3 OpenWrite 1 75 0 k(2,nil,nil) 02 root=75 iDb=0; > sqlite_autoindex_cv_1 > 4 Explain 0 0 0 SEARCH TABLE cv USING INDEX > sqlite_autoindex_cv_1 (cv_s=? AND cv_o=?) 00 > 5 Variable 2 6 0 00 r[6]=parameter(2,) > REG[6] = i:1 > 6 IsNull 6 15 0 00 if r[6]==NULL goto 15 > REG[6] = i:1 > 7 Variable 3 7 0 00 r[7]=parameter(3,) > REG[7] = t16[B8B9F4D5F3E8432E86E248C2EBE14D5D......C...H...M]](8) > 8 IsNull 7 15 0 00 if r[7]==NULL goto 15 > REG[7] = t16[B8B9F4D5F3E8432E86E248C2EBE14D5D......C...H...M]](8) > 9 Affinity 6 1 0 D 00 affinity(r[6]) > 10 SeekGE 1 15 6 2 00 key=r[6..7] > REG[6] = i:1 > 11 IdxGT 1 15 6 2 00 key=r[6..7] > 12 IdxRowid 1 8 0 00 r[8]=rowid > REG[8] = i:1007 > 13 Seek 0 8 0 00 intkey=r[8] > REG[8] = i:1007 > 14 IdxRowid 1 2 0 00 r[2]=rowid > REG[2] = i:1007 > 15 IsNull 2 24 0 00 if r[2]==NULL goto 24 > REG[2] = i:1007 > 16 Column 0 0 3 00 r[3]=cv.cv_s > REG[3] = i:1 > 17 Column 0 1 4 00 r[4]=cv.cv_o > REG[4] = s16[B8B9F4D5F3E8432E86E248C2EBE14D5D......C...H...M]](8) > 18 Variable 1 5 0 00 r[5]=parameter(1,) > REG[5] = i:28 > 19 HaltIfNull 1299 2 3 cv.cv_s 01 if r[3]=null halt > REG[3] = i:1 > 20 HaltIfNull 1299 2 4 cv.cv_o 01 if r[4]=null halt > REG[4] = s16[B8B9F4D5F3E8432E86E248C2EBE14D5D......C...H...M]](8) > 21 HaltIfNull 1299 2 5 cv.cv_t 01 if r[5]=null halt > REG[5] = i:28 > 22 MakeRecord 3 3 9 DAD 00 r[9]=mkrec(r[3..5]) > REG[9] = s21[04092C01B8B9F4D5F3E8432E86E248C2..,.......C...H.](8) > 23 Insert 0 9 2 cv 05 intkey=r[2] data=r[9] > REG[9] = s21[04092C01B8B9F4D5F3E8432E86E248C2..,.......C...H.](8) > REG[2] = i:1007 > 24 Halt 0 0 0 00 See, here somehow the value is actually 1 - hence failing.
