[sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users