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:[email protected]] 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:[email protected]] Im
Auftrag von [email protected]
Gesendet: Dienstag, 28. November 2017 15:04
An: SQLite mailing list <[email protected]>
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
[email protected]
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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
