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

[nomad]

My previous explain outputs were probably not quite right. With the
following code inside the previously posted trigger:

INSERT INTO deltas(
id,
change_id,
function
)
VALUES(
nextval('deltas'),
NEW.change_id,
   'update_project'
);

I now see the below explain output with its two Function0 optcodes
where I believe there should only be one.


 140   Insert 0 26   15bifcodes
05  
 141   Program12142  56program 
00  
 142   ResetCount 0 00 
00  
 143   OpenWrite  4 124  0 3   
00  
 144   OpenWrite  5 125  0 k(2,,)  
00  
 145   String80 68   0 deltas  
00  
 146   Function0  1 68   64nextval(1)  
01  
 147   NotNull64149  0 
00  
 148   Integer-164   0 
00  
 149   MustBeInt  6400 
00  
 150   String80 69   0 deltas  
00  
 151   Function0  1 69   65nextval(1)  
01  
 152   Copy   6570   0 
00  
 153   Param  8 66   0 
00  
 154   Copy   6671   0 
00  
 155   String80 67   0 update_project  
00  
 156   Copy   6772   0 
00  
 157   Affinity   6530 DDB 
00  
 158   Program60247  73program 
00  

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

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

[nomad]

On Mon Nov 20, 2017 at 11:04:01AM +, Hick Gunter wrote:
> 
> Can you provide the original SQL (both for the INSERT and the CREATE
> TRIGGER) and the explain output (SQLite byte code, i.e .explain
> followed by explain )?

Here is the trigger code:

CREATE TABLE func_update_project(
change_id INTEGER NOT NULL,
id INTEGER NOT NULL,
name VARCHAR(40),
parent_id INTEGER,
project_status_id INTEGER,
title VARCHAR
);

CREATE TRIGGER
func_update_project_ai_1
AFTER INSERT ON
func_update_project
FOR EACH ROW
BEGIN

--SELECT debug(
--' change_id: ' || NEW.change_id,
--' id: ' || NEW.id,
--' name: ' || COALESCE(NEW.name,''),
--' parent_id: ' || COALESCE(NEW.parent_id,''),
--' project_status_id: ' || COALESCE(NEW.project_status_id,''),
--' title: ' || COALESCE(NEW.title,'')
--);

SELECT
RAISE(ABORT, 'Bif::Error::InvalidName')
WHERE
CAST(NEW.name AS INTEGER) = NEW.name
;

UPDATE
bifcodes
SET
bifcode = bifcode || (
SELECT
'{U1:_,U14:update_project,'
|| 'U4:name,' ||
CASE WHEN
NEW.name IS NOT NULL
THEN
printf('U%d:%s,', LENGTH(CAST(NEW.name AS BLOB)),
CAST(NEW.name AS BLOB))
ELSE
'~'
END
|| 'U11:parent_uuid,' ||
CASE WHEN
pp.uuid IS NOT NULL
THEN
printf('U%d:%s,', LENGTH(CAST(pp.uuid AS BLOB)),
CAST(pp.uuid AS BLOB))
ELSE
'~'
END
|| 'U19:project_status_uuid,' ||
CASE WHEN
ps.uuid IS NOT NULL
THEN
printf('U%d:%s,', LENGTH(CAST(ps.uuid AS BLOB)),
CAST(ps.uuid AS BLOB))
ELSE
'~'
END
|| 'U5:title,' ||
CASE WHEN
NEW.title IS NOT NULL
THEN
printf('U%d:%s,', LENGTH(CAST(NEW.title AS BLOB)),
CAST(NEW.title AS BLOB))
ELSE
'~'
END
|| 'U4:uuid,'
|| printf('U%d:%s,', LENGTH(p.uuid), p.uuid)
|| '}'
FROM
nodes p
LEFT JOIN
nodes pp
ON
pp.id = NEW.parent_id
LEFT JOIN
nodes ps
ON
ps.id = NEW.project_status_id
WHERE
p.id = NEW.id
)
WHERE
change_id = NEW.change_id
;


INSERT INTO deltas(
id,
change_id,
function
)
SELECT
nextval('deltas'),
NEW.change_id,
'update_project'
;


INSERT INTO
node_deltas(
delta_id,
change_id,
node_id,
parent_id,
name
)
SELECT
currval('deltas'),
NEW.change_id,
NEW.id,
NEW.parent_id,
NEW.name
;


INSERT INTO
project_deltas(
delta_id,
change_id,
project_id,
title,
project_status_id
)
SELECT
currval('deltas'),
NEW.change_id,
NEW.id,
NEW.title,
NEW.project_status_id
WHERE
COALESCE(NEW.title,NEW.project_status_id) IS NOT NULL
;

DELETE FROM func_update_project;
END;

The udf that is called twice is the "nextval()" function.

The insert is straightfoward:

INSERT INTO
func_update_project(
change_id,
id,
project_status_id,
title
)
VALUES
(
49,
3,
NULL,
NULL
)
;

I can't use .explain from the sqlite command-line on my database
because of the UDFs, but I can print the formatted output of the
explain command. The VALUES case and the SELECT 

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

[Hick Gunter]

I was unable to replicate a double call of the udf using the sql you provided.

Can you provide the original SQL (both for the INSERT and the CREATE TRIGGER) 
and the explain output (SQLite byte code, i.e .explain followed by explain 
)?


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von no...@null.net
Gesendet: Montag, 20. November 2017 11:53
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Possible User Defined Function (UDF) Bug?

[ 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


___
 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