With an integer primary key, not just any primary key. Probably something to do with the deterministic flag as well. Looks like in checking if it's gonna be a good integer for a rowid it calls it twice. Below you can see where random() gets called twice, so what the before trigger inserts into another table is different than what gets put into the original table. If you have a deterministic function like abs() it just calls it once, or if you have a non-integer-primary-key table it just calls it once.
sqlite> create table d (id integer primary key); sqlite> create table d2 (id int primary key); sqlite> create table t (tableName text, id int); sqlite> create trigger bi_d before insert on d begin insert into t values ('d', new.id); end; sqlite> create trigger bi_d2 before insert on d2 begin insert into t values ('d2', new.id); end; sqlite> insert into d values (random()); sqlite> insert into d2 values (random()); sqlite> select * from t; tableName|id d|-5810358455625904630 d2|-3456845157187719103 sqlite> select * from d; id 6606271909038536929 sqlite> select * from d2; id -3456845157187719103 sqlite> explain insert into d values (random()); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 20 0 00 Start at 20 1 OpenWrite 0 3 0 1 00 root=3 iDb=0; d 2 Function0 0 0 3 random(0) 00 r[3]=func(r[0]) 3 NotNull 3 5 0 00 if r[3]!=NULL goto 5 4 Integer -1 3 0 00 r[3]=-1 5 MustBeInt 3 0 0 00 6 Function0 0 0 4 random(0) 00 r[4]=func(r[0]) 7 Copy 4 5 0 00 r[5]=r[4] 8 Affinity 4 1 0 D 00 affinity(r[4]) 9 Program 1 19 6 program 01 Call: bi_d.default 10 Copy 5 1 0 00 r[1]=r[5] 11 NotNull 1 13 0 00 if r[1]!=NULL goto 13 12 NewRowid 0 1 0 00 r[1]=rowid 13 MustBeInt 1 0 0 00 14 SoftNull 2 0 0 00 r[2]=NULL 15 NotExists 0 17 1 00 intkey=r[1] 16 Halt 1555 2 0 d.id 02 17 MakeRecord 2 1 7 D 00 r[7]=mkrec(r[2]) 18 Insert 0 7 1 d 31 intkey=r[1] data=r[7] 19 Halt 0 0 0 00 20 Transaction 0 1 39 0 01 usesStmtJournal=1 21 Goto 0 1 0 00 0 Init 0 1 0 -- TRIGGER bi_d 00 Start at 1; Start: bi_d.default (BEFORE INSERT ON d) 1 OpenWrite 0 2 0 2 00 root=2 iDb=0; t 2 NewRowid 0 1 0 00 r[1]=rowid 3 String8 0 2 0 d 00 r[2]='d' 4 Param 2 3 0 00 new.rowid -> $3 5 MakeRecord 2 2 4 BD 00 r[4]=mkrec(r[2..3]) 6 Insert 0 4 1 t 39 intkey=r[1] data=r[4] 7 ResetCount 0 0 0 00 8 Halt 0 0 0 00 End: bi_d.default sqlite> explain insert into d2 values (random()); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 20 0 00 Start at 20 1 OpenWrite 0 4 0 1 00 root=4 iDb=0; d2 2 OpenWrite 1 5 0 k(2,,) 00 root=5 iDb=0; sqlite_autoindex_d2_1 3 Integer -1 6 0 00 r[6]=-1 4 Function0 0 0 7 random(0) 00 r[7]=func(r[0]) 5 Copy 7 8 0 00 r[8]=r[7] 6 Affinity 7 1 0 D 00 affinity(r[7]) 7 Program 4 19 9 program 01 Call: bi_d2.default 8 NewRowid 0 1 0 00 r[1]=rowid 9 Copy 8 2 0 00 r[2]=r[8] 10 Affinity 2 1 0 D 00 affinity(r[2]) 11 SCopy 2 4 0 00 r[4]=r[2]; id 12 IntCopy 1 5 0 00 r[5]=r[1]; rowid 13 MakeRecord 4 2 3 00 r[3]=mkrec(r[4..5]); for sqlite_autoindex_d2_1 14 NoConflict 1 16 4 1 00 key=r[4] 15 Halt 1555 2 0 d2.id 02 16 IdxInsert 1 3 4 2 10 key=r[3] 17 MakeRecord 2 1 10 00 r[10]=mkrec(r[2]) 18 Insert 0 10 1 d2 39 intkey=r[1] data=r[10] 19 Halt 0 0 0 00 20 Transaction 0 1 39 0 01 usesStmtJournal=1 21 Goto 0 1 0 00 0 Init 0 1 0 -- TRIGGER bi_d2 00 Start at 1; Start: bi_d2.default (BEFORE INSERT ON d2) 1 OpenWrite 0 2 0 2 00 root=2 iDb=0; t 2 NewRowid 0 1 0 00 r[1]=rowid 3 String8 0 2 0 d2 00 r[2]='d2' 4 Param 3 3 0 00 new.id -> $3 5 MakeRecord 2 2 4 BD 00 r[4]=mkrec(r[2..3]) 6 Insert 0 4 1 t 39 intkey=r[1] data=r[4] 7 ResetCount 0 0 0 00 8 Halt 0 0 0 00 End: bi_d2.default sqlite> explain insert into d values (abs(1)); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 20 0 00 Start at 20 1 OpenWrite 0 3 0 1 00 root=3 iDb=0; d 2 SCopy 5 3 0 00 r[3]=r[5] 3 NotNull 3 5 0 00 if r[3]!=NULL goto 5 4 Integer -1 3 0 00 r[3]=-1 5 MustBeInt 3 0 0 00 6 SCopy 5 4 0 00 r[4]=r[5] 7 Copy 4 6 0 00 r[6]=r[4] 8 Affinity 4 1 0 D 00 affinity(r[4]) 9 Program 1 19 7 program 01 Call: bi_d.default 10 Copy 6 1 0 00 r[1]=r[6] 11 NotNull 1 13 0 00 if r[1]!=NULL goto 13 12 NewRowid 0 1 0 00 r[1]=rowid 13 MustBeInt 1 0 0 00 14 SoftNull 2 0 0 00 r[2]=NULL 15 NotExists 0 17 1 00 intkey=r[1] 16 Halt 1555 2 0 d.id 02 17 MakeRecord 2 1 8 D 00 r[8]=mkrec(r[2]) 18 Insert 0 8 1 d 31 intkey=r[1] data=r[8] 19 Halt 0 0 0 00 20 Transaction 0 1 39 0 01 usesStmtJournal=1 21 Integer 1 9 0 00 r[9]=1 22 Function0 1 9 5 abs(1) 01 r[5]=func(r[9]) 23 Goto 0 1 0 00 0 Init 0 1 0 -- TRIGGER bi_d 00 Start at 1; Start: bi_d.default (BEFORE INSERT ON d) 1 OpenWrite 0 2 0 2 00 root=2 iDb=0; t 2 NewRowid 0 1 0 00 r[1]=rowid 3 String8 0 2 0 d 00 r[2]='d' 4 Param 2 3 0 00 new.rowid -> $3 5 MakeRecord 2 2 4 BD 00 r[4]=mkrec(r[2..3]) 6 Insert 0 4 1 t 39 intkey=r[1] data=r[4] 7 ResetCount 0 0 0 00 8 Halt 0 0 0 00 End: bi_d.default sqlite> -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter Sent: Tuesday, November 28, 2017 9:35 AM To: 'SQLite mailing list' Subject: Re: [sqlite] [EXTERNAL] Re: Possible User Defined Function (UDF) Bug? I suspect that the udf() function is called once to build the record and once again to build the parameter list for the trigger program. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von no...@null.net Gesendet: Dienstag, 28. November 2017 15:04 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] Re: [sqlite] Possible User Defined Function (UDF) Bug? Here is a trimmed-down test case for my issue: CREATE TABLE d ( id INTEGER NOT NULL PRIMARY KEY ); CREATE TRIGGER bi_d BEFORE INSERT ON d FOR EACH ROW BEGIN select 1; END; INSERT INTO d VALUES( udf(1) ); VDBE Program Listing: 0 Init 0 20 0 00 Start at 20 1 OpenWrite 0 2 0 1 00 root=2 iDb=0; d 2 Function0 1 5 3 udf(1) 01 r[3]=func(r[5]) 3 NotNull 3 5 0 00 if r[3]!=NULL goto 5 4 Integer -1 3 0 00 r[3]=-1 5 MustBeInt 3 0 0 00 6 Function0 1 6 4 udf(1) 01 r[4]=func(r[6]) 7 Copy 4 7 0 00 r[7]=r[4] 8 Affinity 4 1 0 D 00 affinity(r[4]) 9 Program 1 19 8 program 00 Call: bi_d.default 10 Copy 7 1 0 00 r[1]=r[7] 11 NotNull 1 13 0 00 if r[1]!=NULL goto 13 12 NewRowid 0 1 0 00 r[1]=rowid 13 MustBeInt 1 0 0 00 14 SoftNull 2 0 0 00 r[2]=NULL 15 NotExists 0 17 1 00 intkey=r[1] 16 Halt 1555 2 0 d.id 02 17 MakeRecord 2 1 9 D 00 r[9]=mkrec(r[2]) 18 Insert 0 9 1 d 31 intkey=r[1] data=r[9] 19 Halt 0 0 0 00 20 Transaction 0 1 2 0 01 usesStmtJournal=1 21 TableLock 0 2 1 d 00 iDb=0 root=2 write=1 22 Integer 1 5 0 00 r[5]=1 23 Integer 1 6 0 00 r[6]=1 24 Goto 0 1 0 00 Note the two calls to Function0 in the above. If you create a udf() function that prints a message you will see that it does in fact get called twice. I only see this happening when all three of the following statements are true: - The UDF is used in a VALUES() statement - The destination table "d" has a PRIMARY KEY - There is a BEFORE INSERT trigger on "d" -- Mark Lawrence _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users