Re: [sqlite] [EXTERNAL] Re: Possible User Defined Function (UDF) Bug?

[Richard Hipp]

On 12/5/17, no...@null.net  wrote:
> On Tue Nov 28, 2017 at 03:30:54PM +, David Raymond wrote:
>
> SQLite developers, do you recognise this thread as an issue?

Not a serious issue, no.  I might look into it when I have time, but
I'm neck-deep in other issues at the moment.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Possible User Defined Function (UDF) Bug?

[nomad]

On Tue Nov 28, 2017 at 03:30:54PM +, David Raymond wrote:
> 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

That looks like a good test case to me. This is certainly a regression
as earlier versions of SQLite don't show the same behaviour.

SQLite developers, do you recognise this thread as an issue? I've seen
other topics come and go and be fixed in the meantime, but no statement
on this one.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Possible User Defined Function (UDF) Bug?

[David Raymond]

10  key=r[3]
17MakeRecord 2 1 10   00  r[10]=mkrec(r[2])
18Insert 0 101 d2 39  intkey=r[1] data=r[10]
19Halt   0 0 000
20Transaction0 1 390  01  usesStmtJournal=1
21Goto   0 1 000
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 000  r[1]=rowid
3 String80 2 0 d2 00  r[2]='d2'
4 Param  3 3 000  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 000
8 Halt   0 0 000  End: bi_d2.default

sqlite> explain insert into d values (abs(1));
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 20000  Start at 20
1 OpenWrite  0 3 0 1  00  root=3 iDb=0; d
2 SCopy  5 3 000  r[3]=r[5]
3 NotNull3 5 000  if r[3]!=NULL goto 5
4 Integer-13 000  r[3]=-1
5 MustBeInt  3 0 000
6 SCopy  5 4 000  r[4]=r[5]
7 Copy   4 6 000  r[6]=r[4]
8 Affinity   4 1 0 D  00  affinity(r[4])
9 Program1 197 program01  Call: bi_d.default
10Copy   6 1 000  r[1]=r[6]
11NotNull1 13000  if r[1]!=NULL goto 13
12NewRowid   0 1 000  r[1]=rowid
13MustBeInt  1 0 000
14SoftNull   2 0 000  r[2]=NULL
15NotExists  0 17100  intkey=r[1]
16Halt   1555  2 0 d.id   02
17MakeRecord 2 1 8 D  00  r[8]=mkrec(r[2])
18Insert 0 8 1 d  31  intkey=r[1] data=r[8]
19Halt   0 0 000
20Transaction0 1 390  01  usesStmtJournal=1
21Integer1 9 000  r[9]=1
22Function0  1 9 5 abs(1) 01  r[5]=func(r[9])
23Goto   0 1 000
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 000  r[1]=rowid
3 String80 2 0 d  00  r[2]='d'
4 Param  2 3 000  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 000
8 Halt   0 0 000  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   200   00 Start at 20
   1 OpenWrite020 1 00 root=2 iDb=0; d
   2 Function0153 udf(1)01 r[3]=func(r[5])
   3 NotNull  350   

Re: [sqlite] [EXTERNAL] Re: Possible User Defined Function (UDF) Bug?

[Hick Gunter]

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 
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   200   00 Start at 20
   1 OpenWrite020 1 00 root=2 iDb=0; d
   2 Function0153 udf(1)01 r[3]=func(r[5])
   3 NotNull  350   00 if r[3]!=NULL goto 5
   4 Integer -130   00 r[3]=-1
   5 MustBeInt300   00
   6 Function0164 udf(1)01 r[4]=func(r[6])
   7 Copy 470   00 r[7]=r[4]
   8 Affinity 410 D 00 affinity(r[4])
   9 Program  1   198 program   00 Call: bi_d.default
  10 Copy 710   00 r[1]=r[7]
  11 NotNull  1   130   00 if r[1]!=NULL goto 13
  12 NewRowid 010   00 r[1]=rowid
  13 MustBeInt100   00
  14 SoftNull 200   00 r[2]=NULL
  15 NotExists0   171   00 intkey=r[1]
  16 Halt  155520 d.id  02
  17 MakeRecord   219 D 00 r[9]=mkrec(r[2])
  18 Insert   091 d 31 intkey=r[1] data=r[9]
  19 Halt 000   00
  20 Transaction  012 0 01 usesStmtJournal=1
  21 TableLock021 d 00 iDb=0 root=2 write=1
  22 Integer  150   00 r[5]=1
  23 Integer  160   00 r[6]=1
  24 Goto 010   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