diff options
Diffstat (limited to 'www/deterministic.html')
| -rw-r--r-- | www/deterministic.html | 255 |
1 files changed, 255 insertions, 0 deletions
diff --git a/www/deterministic.html b/www/deterministic.html new file mode 100644 index 0000000..885ed9f --- /dev/null +++ b/www/deterministic.html @@ -0,0 +1,255 @@ +<!DOCTYPE html> +<html><head> +<meta name="viewport" content="width=device-width, initial-scale=1.0"> +<meta http-equiv="content-type" content="text/html; charset=UTF-8"> +<link href="sqlite.css" rel="stylesheet"> +<title>Deterministic SQL Functions</title> +<!-- path= --> +</head> +<body> +<div class=nosearch> +<a href="index.html"> +<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0"> +</a> +<div><!-- IE hack to prevent disappearing logo --></div> +<div class="tagline desktoponly"> +Small. Fast. Reliable.<br>Choose any three. +</div> +<div class="menu mainmenu"> +<ul> +<li><a href="index.html">Home</a> +<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a> +<li class='wideonly'><a href='about.html'>About</a> +<li class='desktoponly'><a href="docs.html">Documentation</a> +<li class='desktoponly'><a href="download.html">Download</a> +<li class='wideonly'><a href='copyright.html'>License</a> +<li class='desktoponly'><a href="support.html">Support</a> +<li class='desktoponly'><a href="prosupport.html">Purchase</a> +<li class='search' id='search_menubutton'> +<a href="javascript:void(0)" onclick='toggle_search()'>Search</a> +</ul> +</div> +<div class="menu submenu" id="submenu"> +<ul> +<li><a href='about.html'>About</a> +<li><a href='docs.html'>Documentation</a> +<li><a href='download.html'>Download</a> +<li><a href='support.html'>Support</a> +<li><a href='prosupport.html'>Purchase</a> +</ul> +</div> +<div class="searchmenu" id="searchmenu"> +<form method="GET" action="search"> +<select name="s" id="searchtype"> +<option value="d">Search Documentation</option> +<option value="c">Search Changelog</option> +</select> +<input type="text" name="q" id="searchbox" value=""> +<input type="submit" value="Go"> +</form> +</div> +</div> +<script> +function toggle_div(nm) { +var w = document.getElementById(nm); +if( w.style.display=="block" ){ +w.style.display = "none"; +}else{ +w.style.display = "block"; +} +} +function toggle_search() { +var w = document.getElementById("searchmenu"); +if( w.style.display=="block" ){ +w.style.display = "none"; +} else { +w.style.display = "block"; +setTimeout(function(){ +document.getElementById("searchbox").focus() +}, 30); +} +} +function div_off(nm){document.getElementById(nm).style.display="none";} +window.onbeforeunload = function(e){div_off("submenu");} +/* Disable the Search feature if we are not operating from CGI, since */ +/* Search is accomplished using CGI and will not work without it. */ +if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){ +document.getElementById("search_menubutton").style.display = "none"; +} +/* Used by the Hide/Show button beside syntax diagrams, to toggle the */ +function hideorshow(btn,obj){ +var x = document.getElementById(obj); +var b = document.getElementById(btn); +if( x.style.display!='none' ){ +x.style.display = 'none'; +b.innerHTML='show'; +}else{ +x.style.display = ''; +b.innerHTML='hide'; +} +return false; +} +var antiRobot = 0; +function antiRobotGo(){ +if( antiRobot!=3 ) return; +antiRobot = 7; +var j = document.getElementById("mtimelink"); +if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href"); +} +function antiRobotDefense(){ +document.body.onmousedown=function(){ +antiRobot |= 2; +antiRobotGo(); +document.body.onmousedown=null; +} +document.body.onmousemove=function(){ +antiRobot |= 2; +antiRobotGo(); +document.body.onmousemove=null; +} +setTimeout(function(){ +antiRobot |= 1; +antiRobotGo(); +}, 100) +antiRobotGo(); +} +antiRobotDefense(); +</script> +<div class=fancy> +<div class=nosearch> +<div class="fancy_title"> +Deterministic SQL Functions +</div> +</div> + + + + + +<h1 id="overview"><span>1. </span>Overview</h1> + +<p> +SQL functions in SQLite can be either "deterministic" or "non-deterministic". + +</p><p> +A deterministic function always gives the same answer when it has +the same inputs. Most built-in SQL functions in SQLite are +deterministic. For example, the <a href="lang_corefunc.html#abs">abs(X)</a> function always returns +the same answer as long as its input X is the same. + +</p><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: + +</p><ul> +<li> <a href="lang_corefunc.html#random">random()</a> +</li><li> <a href="lang_corefunc.html#changes">changes()</a> +</li><li> <a href="lang_corefunc.html#last_insert_rowid">last_insert_rowid()</a> +</li><li> <a href="c3ref/libversion.html">sqlite3_version()</a> +</li></ul> + +<p> +The <a href="lang_corefunc.html#random">random()</a> function is obviously non-deterministic because it gives +a different answer every time it is invoked. The answers from <a href="lang_corefunc.html#changes">changes()</a> +and <a href="lang_corefunc.html#last_insert_rowid">last_insert_rowid()</a> depend on prior SQL statements, and so they +are also non-deterministic. The +<a href="c3ref/libversion.html">sqlite3_version()</a> 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. + + +</p><h1 id="restrictions_on_the_use_of_non_deterministic_functions"><span>2. </span>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: + +</p><ul> +<li>In the expression of a <a href="lang_createtable.html#ckconst">CHECK constraint</a>. +</li><li>In the WHERE clause of a <a href="partialindex.html">partial index</a>. +</li><li>In an expression used as part of an <a href="expridx.html">expression index</a>. +</li><li>In the expression of a <a href="gencol.html">generated column</a>. +</li></ul> + +<p> +In the cases above, the values returned by the function affects the +information stored in the database file. The values of functions +in CHECK constraints determines which entries are valid for a table, +and functions in the WHERE clause of a partial index or in an index on +an expression compute values stored in the index b-tree. +If any of these functions later returns a different +value, then the database might no longer be well-formed. +Hence, to avoid database corruption, +only deterministic functions can be used in the contexts +above. + +<a name="dtexception"></a> + +</p><h1 id="special_case_processing_for_date_time_functions"><span>3. </span>Special-case Processing For Date/Time Functions</h1> + +<p> +The built-in <a href="lang_datefunc.html">date and time functions</a> 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 <a href="lang_datefunc.html#localtime">localtime modifier</a> or the <a href="lang_datefunc.html#localtime">utc modifier</a>, then they are +considered non-deterministic. Because the function inputs 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 allowed. + +</p><p> +Prior to SQLite 3.20.0 (2017-08-01) 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. + +</p><h2 id="bug_fix_in_version_3_35_2"><span>3.1. </span>Bug fix in version 3.35.2</h2> + +<p> +When the enhancement was made to SQLite 3.20.0 such that date/time +functions would be considered deterministic as they do not depend +on the current time, one case was overlooked: +Many of the date/time functions can be called +with no arguments at all. These no-argument date/time functions +behave as if they had a single "<tt>'now'</tt>" argument. +Thus "<tt>datetime()</tt>" and +"<tt>datetime('now')</tt>" both yield the current date and time. +However, only the second form was recognized as non-deterministic. +This meant that developers could sneak the non-deterministic +"<tt>datetime()</tt>" form into CHECK constraints, index +expressions, generated column expressions, and similar places +where non-deterministic functions make no sense. +This oversight was fixed in version 3.35.2 (2021-03-17). +However, there may be legacy databases in circulation that were created +by SQLite version 3.20.0 through 3.35.1 that have non-deterministic +date/time functions in their schemas. + +</p><h1 id="application_defined_deterministic_functions"><span>4. </span>Application-defined deterministic functions</h1> + +<p> +By default, <a href="appfunc.html">application-defined SQL functions</a> are considered to +be non-deterministic. However, if the 4th parameter to +<a href="c3ref/create_function.html">sqlite3_create_function_v2()</a> is OR-ed with +<a href="c3ref/c_deterministic.html#sqlitedeterministic">SQLITE_DETERMINISTIC</a>, then SQLite will treat that function as if it +were deterministic. + +</p><p> +Note that if a non-deterministic function is tagged with +<a href="c3ref/c_deterministic.html#sqlitedeterministic">SQLITE_DETERMINISTIC</a> and if that function ends up being used in +the WHERE clause of a <a href="partialindex.html">partial index</a> or in an +<a href="expridx.html">expression index</a>, 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 <a href="lang_corefunc.html#sqlite_version">sqlite_version()</a>) and it is used in an index that becomes +corrupt, the corruption can be fixed by running <a href="lang_reindex.html">REINDEX</a>. + +</p><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 <a href="appfunc.html">application-defined SQL functions</a> must +be always deterministic or always non-deterministic. +</p><p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/deterministic.in?m=72aa24619c">2022-01-08 05:02:57</a> UTC </small></i></p> + |