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

2017-11-28 Thread nomad 
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

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

2017-11-20 Thread nomad 
[ version: sqlite-snapshot-201711181730.tar.gz embedded in Perl's
DBD::SQLite module. ]

I have a user-defined function used as follows:

CREATE TRIGGER
after_insert_x
AFTER INSERT ON
x
FOR EACH ROW
BEGIN

INSERT INTO
y(id)
VALUES(
udf()
);

END;

What I am seeing is that for a row inserted into table y the udf() is
called *twice*. This behaviour only seems to occur with the
INSERT/VALUES combination. If I change the trigger contents to be the
following then the udf is only called once:

INSERT INTO
y(id)
SELECT
udf()
;

I'm having a hard time duplicating this with a standalone test case,
and I'm also having difficulty with EXPLAIN QUERY PLAN returning no
data for some statements... so I have a bit of a rabbit hole to explore
before I can come back with more information. But perhaps someone else 
knows what might be going on?

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