Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add the enhanced ability for date/time functions to be deterministic to the change log and to the "deterministic function" documentation. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
e27224b5265c06262e6d3c54aff7e284 |
User & Date: | drh 2017-07-21 19:32:08.875 |
Context
2017-07-21
| ||
19:39 | Further enhancements to the change log for 3.20.0. (check-in: 4e0e4a8084 user: drh tags: trunk) | |
19:32 | Add the enhanced ability for date/time functions to be deterministic to the change log and to the "deterministic function" documentation. (check-in: e27224b526 user: drh tags: trunk) | |
19:12 | Add documentation for the UNION virtual table. (check-in: 64af4ea91f user: drh tags: trunk) | |
Changes
Changes to pages/changes.in.
︙ | ︙ | |||
37 38 39 40 41 42 43 44 45 46 47 48 49 50 | → requires [sqlite3_bind_pointer()] to set the PTR parameter. </ul> <li> Added the [SQLITE_STMT virtual table] extension. <li> Added the [COMPLETION extension] - designed to suggest <li> Added the [UNION virtual table] extension. tab-completions for interactive user interfaces. This is a work in progress. Expect further enhancements in future releases. <li> Added the [sqlite3_prepare_v3()] and [sqlite3_prepare16_v3()] interfaces with the extra "prepFlags" parameters. <li> Provide the [SQLITE_PREPARE_PERSISTENT] flag [sqlite3_prepare_v3()] and use it to limit [lookaside memory] misuse by [FTS3], [FTS5], and the [R-Tree extension]. <li> Added the [PRAGMA secure_delete=FAST] command. When secure_delete is set to FAST, old content is overwritten with zeros as long as that does | > > > > | 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | → requires [sqlite3_bind_pointer()] to set the PTR parameter. </ul> <li> Added the [SQLITE_STMT virtual table] extension. <li> Added the [COMPLETION extension] - designed to suggest <li> Added the [UNION virtual table] extension. tab-completions for interactive user interfaces. This is a work in progress. Expect further enhancements in future releases. <li> The built-in [date and time functions] have been enhanced so that they can be used within [CHECK constraints], [indexes on expressions], and in the WHERE clause of a [partial index], provided that they do not use the 'now', 'localtime', or 'utc' keywords. [date/time special case|Futher information]. <li> Added the [sqlite3_prepare_v3()] and [sqlite3_prepare16_v3()] interfaces with the extra "prepFlags" parameters. <li> Provide the [SQLITE_PREPARE_PERSISTENT] flag [sqlite3_prepare_v3()] and use it to limit [lookaside memory] misuse by [FTS3], [FTS5], and the [R-Tree extension]. <li> Added the [PRAGMA secure_delete=FAST] command. When secure_delete is set to FAST, old content is overwritten with zeros as long as that does |
︙ | ︙ |
Changes to pages/deterministic.in.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | <title>Deterministic SQL Functions</title> <tcl>hd_keywords {deterministic function} {deterministic functions}\ {deterministic SQL functions} {non-deterministic functions}</tcl> <fancy_format> <p> SQL functions in SQLite can be either "deterministic" or "non-deterministic". <p> A deterministic function always gives the same answer whenever it has the same inputs. Most built-in SQL functions in SQLite are deterministic. For example, the [abs|abs(X)] function always returns the same answer as long as its input X is the same. <p> Non-deterministic functions might give different answers on each invocation, even if the arguments are always the same. The following are examples of non-deterministic functions: <ul> <li> [random()] <li> [changes()] <li> [last_insert_rowid()] <li> [sqlite3_version()] | > > < | < < > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 | <title>Deterministic SQL Functions</title> <tcl>hd_keywords {deterministic function} {deterministic functions}\ {deterministic SQL functions} {non-deterministic functions}</tcl> <fancy_format> <h1>Overview</h1> <p> SQL functions in SQLite can be either "deterministic" or "non-deterministic". <p> A deterministic function always gives the same answer whenever it has the same inputs. Most built-in SQL functions in SQLite are deterministic. For example, the [abs|abs(X)] function always returns the same answer as long as its input X is the same. <p> Non-deterministic functions might give different answers on each invocation, even if the arguments are always the same. The following are examples of non-deterministic functions: <ul> <li> [random()] <li> [changes()] <li> [last_insert_rowid()] <li> [sqlite3_version()] </ul> <p> The [random()] function is obviously non-deterministic because it gives a different answer every time it is invoked. The answers from [changes()] and [last_insert_rowid()] depend on prior SQL statements, and so they are also non-deterministic. The [sqlite3_version()] function is mostly constant, but it can change when SQLite is upgraded, and so even though it always returns the same answer for any particular session, because it can change answers across sessions it is still considered non-deterministic. <h1>Restrictions on the use of non-deterministic functions</h1> <p> There are some contexts in SQLite that do not allow the use of non-deterministic functions: <ul> <li>In the expression of a [CHECK constraint]. <li>In the WHERE clause of a [partial index]. <li>In an expression used as part of an [expression index]. </ul> <p> In the cases above, the values returned by the function is recorded in the index b-tree. If the function later starts returning a different value, then the index will be seen as corrupt. Hence, to avoid corrupt indexes, only deterministic functions can be used. <tcl>hd_fragment dtexception {date/time special case}</tcl> <h1>Special-case Processing For Date/Time Functions</h1> <p> The built-in [date and time functions] of SQLite are a special case. These functions are usually considered deterministic. However, if these functions use the string "now" as the date, or if they use the [localtime modifier] or the [utc modifier], then they are considered non-deterministic. Because the inputs to functions are not necessarily known until run-time, the date/time functions will throw an exception if they encounter any of the non-deterministic features in a context where only deterministic functions are allows. <p> Prior to SQLite 3.20.0 ([dateof:3.20.0]) all date/time functions were always considered non-deterministic. The ability for date/time functions to be deterministic sometimes and non-deterministic at other times, depending on their arguments, was added for the 3.20.0 release. <h1>Application-defined deterministic functions</h1> <p> By default, [application-defined SQL functions] are considered to be non-deterministic. However, if the 4th parameter to [sqlite3_create_function_v2()] is OR-ed with [SQLITE_DETERMINISTIC], then SQLite will treat that function as if it were deterministic. <p> Note that if a non-deterministic function is tagged with [SQLITE_DETERMINISTIC] and if that function ends up being used in the WHERE clause of a [partial index] or in an [expression index], then when the function begins to return different answers, the associated index may become corrupt. If an SQL function is nearly deterministic (which is to say, if it only rarely changes, like [sqlite_version()]) and it is used in an index that becomes corrupt, the corruption can be fixed by running [REINDEX]. <p> The interfaces necessary to construct a function that is sometimes deterministic and sometimes non-deterministic depending on their inputs, such as the built-in date/time functions, are not published. Generic [application-defined SQL functions] must be always deterministic or always non-deterministic. |