SQLITE_DETERMINISTIC sticky in sqlite3_create_function?

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

SQLITE_DETERMINISTIC sticky in sqlite3_create_function?

Nelson, Erik - 2
If I'm redefining a user-defined function as SQLITE_DETERMINISTIC before each query, will the function still get called for subsequent queries?  For example, if I had

void user_func(sqlite3_context *context, int argc, sqlite3_value **argv){ return random() }

sqlite3_create_function(db, "user", 1, SQLITE_UTF8 | SQLITE_DETERMINISTIC, NULL, &user_func, NULL, NULL))

> select user()
.54

sqlite3_create_function(db, "user", 1, SQLITE_UTF8 | SQLITE_DETERMINISTIC, NULL, &user_func, NULL, NULL))

> select user()
.54  <-- should this result be the same as the previous call to user()?  Or would it get evaluated once after each sqlite3_create_function() call?

----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
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: SQLITE_DETERMINISTIC sticky in sqlite3_create_function?

Jens Alfke-2


> On Sep 12, 2017, at 12:23 PM, Nelson, Erik - 2 <[hidden email]> wrote:
>
>> select user()
> .54  <-- should this result be the same as the previous call to user()?  Or would it get evaluated once after each sqlite3_create_function() call?

It gets evaluated whenever the SELECT runs. The value isn't cached anywhere.

The only difference between a deterministic and a regular function currently (AFAIK) is that the former can be used in a CREATE INDEX statement. This allows a query that makes an equivalent call to use the index. TL;DR: it's how you can do efficient queries on derived values like JSON properties.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users