Documentation Source Text

Check-in [e167521765]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Improved documentation on how EXPLAIN only affects run-time but some PRAGMA statements operate at prepare-time and are thus unaffected by EXPLAIN.
Timelines: family | ancestors | descendants | both | branch-3.29
Files: files | file ages | folders
SHA3-256: e167521765c0e7c79736dddd844d8ff34ab55004ae90f86b83c8a7b0cde642ee
User & Date: drh 2019-08-06 17:02:06
Context
2019-08-12
11:54
Trying to improve the documentation on transactions. check-in: ead5e472f0 user: drh tags: branch-3.29
2019-08-06
17:02
Improved documentation on how EXPLAIN only affects run-time but some PRAGMA statements operate at prepare-time and are thus unaffected by EXPLAIN. check-in: e167521765 user: drh tags: branch-3.29
2019-08-05
20:39
Back out the previous change. We are instead going to modify the code to match the documentation. check-in: 3578d6c47b user: drh tags: branch-3.29
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/lang.in.

  1922   1922   <p>^When the EXPLAIN keyword appears by itself it causes the statement
  1923   1923   to behave as a query that returns the sequence of 
  1924   1924   [virtual machine instructions] it would have used to execute the command had
  1925   1925   the EXPLAIN keyword not been present. ^When the EXPLAIN QUERY PLAN phrase
  1926   1926   appears, the statement returns high-level information regarding the query
  1927   1927   plan that would have been used.
  1928   1928   
  1929         -The EXPLAIN QUERY PLAN command is described in 
         1929  +<p>The EXPLAIN QUERY PLAN command is described in 
  1930   1930   [explain query plan|more detail here].
  1931   1931   
         1932  +<h3>EXPLAIN operates at run-time, not at prepare-time</h3>
         1933  +
         1934  +<p>The EXPLAIN and EXPLAIN QUERY PLAN prefixes affect the behavior of
         1935  +running a [prepared statement] using [sqlite3_step()].  The process of
         1936  +generating a new prepared statement using [sqlite3_prepare()] or similar
         1937  +is (mostly) unaffected by EXPLAIN.  (The exception to the previous sentence
         1938  +is that some special opcodes used by EXPLAIN QUERY PLAN are omitted when
         1939  +building an EXPLAIN QUERY PLAN prepared statement, as a performance
         1940  +optimization.)
         1941  +
         1942  +<p>This means that actions that occur during sqlite3_prepare() are
         1943  +unaffected by EXPLAIN.
         1944  +
         1945  +<ul>
         1946  +<li><p>
         1947  +Some [PRAGMA] statements do their work during sqlite3_prepare() rather
         1948  +than during sqlite3_step().  Those PRAGMA statements are unaffected
         1949  +by EXPLAIN.  They operate the same with or without the EXPLAIN prefix.
         1950  +The set of PRAGMA statements that are unaffected by EXPLAIN can vary
         1951  +from one release to the next.  Some PRAGMA statements operate during
         1952  +sqlite3_prepare() depending on their arguments.  For consistent
         1953  +results, avoid using EXPLAIN on PRAGMA statements.
         1954  +
         1955  +<li><p>
         1956  +The [authorizer callback] is invoked regardless of the presence of
         1957  +EXPLAIN or EXPLAIN QUERY PLAN.
         1958  +</ul>
  1932   1959   <tcl>
  1933   1960   ##############################################################################
  1934   1961   Section expression expr {*expression {expression syntax}}
  1935   1962   
  1936   1963   RecursiveBubbleDiagram expr
  1937   1964   </tcl>
  1938   1965   

Changes to pages/pragma.in.

   104    104   <p>The PRAGMA statement is an SQL extension specific to SQLite and used to 
   105    105   modify the operation of the SQLite library or to query the SQLite library for 
   106    106   internal (non-table) data. The PRAGMA statement is issued using the same
   107    107   interface as other SQLite commands (e.g. [SELECT], [INSERT]) but is
   108    108   different in the following important respects:
   109    109   </p>
   110    110   <ul>
          111  +<li>The pragma command is specific to SQLite and is
          112  +    not compatible with any other SQL database engine.
   111    113   <li>Specific pragma statements may be removed and others added in future
   112    114       releases of SQLite. There is no guarantee of backwards compatibility.
   113    115   <li>^No error messages are generated if an unknown pragma is issued.
   114    116       Unknown pragmas are simply ignored. This means if there is a typo in 
   115    117       a pragma statement the library does not inform the user of the fact.
   116    118   <li>^Some pragmas take effect during the SQL compilation stage, not the
   117    119       execution stage. This means if using the C-language [sqlite3_prepare()], 
................................................................................
   118    120       [sqlite3_step()], [sqlite3_finalize()] API (or similar in a wrapper 
   119    121       interface), the pragma may run during the [sqlite3_prepare()] call,
   120    122       not during the [sqlite3_step()] call as normal SQL statements do.
   121    123       ^Or the pragma might run during sqlite3_step() just like normal
   122    124       SQL statements.  Whether or not the pragma runs during sqlite3_prepare()
   123    125       or sqlite3_step() depends on the pragma and on the specific release
   124    126       of SQLite.
   125         -<li>The pragma command is specific to SQLite and is
   126         -    not compatible with any other SQL database engine.
          127  +<li>The [EXPLAIN] and [EXPLAIN QUERY PLAN] prefixes to SQL statements
          128  +    only affect the behavior of the statement during [sqlite3_step()].
          129  +    That means that PRAGMA statements that take effect during
          130  +    [sqlite3_prepare()] will behave the same way regardless of whether or
          131  +    not they are prefaced by "EXPLAIN".
   127    132   </ul>
   128    133   
   129    134   <p>The C-language API for SQLite provides the [SQLITE_FCNTL_PRAGMA]
   130    135   [sqlite3_file_control | file control] which gives [VFS] implementations the
   131    136   opportunity to add new PRAGMA statements or to override the meaning of
   132    137   built-in PRAGMA statements.</p>
   133    138