User function's alias

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

User function's alias

korablev
I have noticed strange behaviour of user functions. Consider following
example:

#include <stdio.h>
#include <assert.h>

#include "sqlite3.h"

static int callback(void *NotUsed, int argc, char **argv, char **azColName){
        int i;
        for(i=0; i<argc; i++){
                printf(&quot;%s = %s\n&quot;, azColName[i], argv[i] ? argv[i] :
&quot;NULL&quot;);
        }
        printf(&quot;\n&quot;);
        return 0;
}

int counter = 0;
void change_global_var(sqlite3_context* context,int args,sqlite3_value**
value) {
        assert(args == 0);
        counter++;
        sqlite3_result_int64(context, counter);
}

int main(int argc, char **argv) {
        sqlite3 *db;
        char *zErrMsg = 0;
        sqlite3_open(&quot;test.db&quot;, &amp;db);
        sqlite3_exec(db, &quot;drop table if exists t1;&quot;, callback, 0,
&amp;zErrMsg);
        sqlite3_exec(db, &quot;create table t1(x primary key);&quot;, callback, 0,
&amp;zErrMsg);
    sqlite3_create_function_v2(db, &quot;change_global_var&quot;, 0,
SQLITE_UTF8, NULL, &amp;change_global_var, NULL, NULL, NULL);
    sqlite3_exec(db, &quot;insert into t1 values(1), (2), (3);&quot;,
callback, 0, &amp;zErrMsg);
        sqlite3_exec(db, &quot;SELECT x, change_global_var() AS y FROM t1 WHERE y>0
AND y<100;", callback, 0, &zErrMsg);
        sqlite3_close(db);
        return 0;
}

It prints:
x = 1
y = 3

x = 2
y = 6

x = 3
y = 9

However, I expect y = 1, 2, 3, which seems to be reasonable. Let's look at
vdbe opcodes:

addr = 0
opcode = Init
p1 = 0
p2 = 13
p3 = 0
p4 =
p5 = 00
comment = NULL

addr = 1
opcode = OpenRead
p1 = 0
p2 = 2
p3 = 0
p4 = 1
p5 = 00
comment = NULL

addr = 2
opcode = Rewind
p1 = 0
p2 = 11
p3 = 0
p4 =
p5 = 00
comment = NULL

addr = 3
opcode = Function0
p1 = 0
p2 = 0
p3 = 1
p4 = change_global_var(0)
p5 = 00
comment = NULL

addr = 4
opcode = Le
p1 = 2
p2 = 10
p3 = 1
p4 =
p5 = 51
comment = NULL

addr = 5
opcode = Function0
p1 = 0
p2 = 0
p3 = 1
p4 = change_global_var(0)
p5 = 00
comment = NULL

addr = 6
opcode = Ge
p1 = 3
p2 = 10
p3 = 1
p4 =
p5 = 51
comment = NULL

addr = 7
opcode = Column
p1 = 0
p2 = 0
p3 = 4
p4 =
p5 = 00
comment = NULL

addr = 8
opcode = Function0
p1 = 0
p2 = 0
p3 = 5
p4 = change_global_var(0)
p5 = 00
comment = NULL

addr = 9
opcode = ResultRow
p1 = 4
p2 = 2
p3 = 0
p4 =
p5 = 00
comment = NULL

...
 
So, change_global_var is called 3 times for -- one for inserting and two for
comparison. I expected that it would call function one time, save result and
use saved result for comparisons and insertion. Such behaviour is very
confusing and should be documented or fixed.

Another kind of useful optimization is calling deterministic function
without arguments only once for entire table. Currently, it is called for
each row. It is unlikely to be important optimization, but can be
implemented.




--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: User function's alias

Jens Alfke-2


> On Nov 10, 2017, at 9:51 AM, korablev <[hidden email]> wrote:
>
> I have noticed strange behaviour of user functions. Consider following
> example:

In reports like this, it really helps if you can clearly state the situation at the start, instead of dumping hundreds of lines of code and output, and expecting the reader to figure out what’s going on.

Down at the end:
> So, change_global_var is called 3 times for -- one for inserting and two for
> comparison. I expected that it would call function one time, save result and
> use saved result for comparisons and insertion.

This is what should have been at the top :)

First off, you didn’t register the function as deterministic, so SQLite has to assume it can return a different result every time it’s called, even with the same arguments. That immediately prevents the kind of optimization you wanted.

As for deterministic functions, I asked a similar question a month or two ago, about factoring multiple calls out of a query. You can find list archives and read the thread. It doesn’t sound likely to happen.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: User function's alias

korablev
Jens Alfke-2 wrote
> First off, you didn’t register the function as deterministic, so SQLite
> has to assume it can return a different result every time it’s called,
> even with the same arguments. That immediately prevents the kind of
> optimization you wanted.

I guess, it doesn't really matter whether function is deterministic or not.
It is rather about how aliases should work. To prove it I have found
"alias.test" in test folder. There is a comment:
# Aliases are currently evaluated twice.  We might try to change this
# in the future.  But not now.
return

The test is quite old, but still doesn't work.
Moreover, consider query: SELECT x, rand() as random FROM tab WHERE random >
0.5;
Currently, it would return random numbers which might be less than 0.5 due
to two calls of rand(x).



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: User function's alias

korablev
In reply to this post by korablev
Well, behaviour of deterministic function is really strange.
SELECT x, sequence() AS y FROM t1 WHERE y>0 AND y<99 AND y!=55 AND y NOT IN
(56,57,58) AND y NOT LIKE 'abc%' AND y%10==2 order by x desc
(Example from alias.test, I have declared sequence() with
SQLITE_DETERMINISTIC flag)
Trace for the query above:
...
Function0         0    0    1 sequence(0)   00 r[1]=func(r[0])
Integer            0    2    0                      00 r[2]=0
Integer           99    3    0                     00 r[3]=99
Integer           55    4    0                     00 r[4]=55
String8            0   13    0 abc%            00 r[13]='abc%'
Function0         0    0   14 sequence(0)   00 r[14]=func(r[0])
Function0         3   13    8 like(2)          02 r[8]=func(r[13..14])
Function0         0    0    7 sequence(0)  00 r[7]=func(r[0])
...
sequence() is called 3 times in trace(even without branching), instead of
using register r[1] after first call.





--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: User function's alias

korablev
In reply to this post by korablev
More strange things:

SELECT x, sequence() AS y FROM t1 WHERE y>0 AND y<99  order by x desc;

9, 2
8, 3
7, 4

SELECT x, 0+sequence() AS y FROM t1 WHERE y>0 AND y<99  order by x desc;

9, 6
8, 6
7, 6

Seems that this optimization a little bit broken...



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users