Documentation Source Text

Check-in [2e3a732177]
Login

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

Overview
Comment:Merge changes from the 3.25 branch.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 2e3a732177acb0835a75ca6c9d40a02fa8ba41555b4013b27ba7c0dff72eb22d
User & Date: drh 2018-09-26 15:19:41
Context
2018-09-27
12:56
Say that the recursive part of a CTE may not use aggregate or window functions. check-in: aa1202143a user: drh tags: trunk
2018-09-26
15:19
Merge changes from the 3.25 branch. check-in: 2e3a732177 user: drh tags: trunk
2018-09-25
19:32
Version 3.25.2 check-in: 8c31c0cd8c user: drh tags: release, version-3.25.2, branch-3.25
2018-09-24
12:40
Document the SQLITE_QUERY_PLANNER_LIMIT and SQLITE_QUERY_PLANNER_LIMIT_INCR compile-time options. check-in: 55fce17e3c user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to common_links.tcl.

     8      8   <li> <a href="chronology.html">Prior Releases</a>
     9      9   <li> <a href="lang.html">SQL Syntax</a>
    10     10   <ul>
    11     11   <li> <a href="pragma.html#toc">Pragmas</a>
    12     12   <li> <a href="lang_corefunc.html">SQL functions</a>
    13     13   <li> <a href="lang_datefunc.html">Date &amp; time functions</a>
    14     14   <li> <a href="lang_aggfunc.html">Aggregate functions</a>
           15  +<li> <a href="windowfunctions.html#biwinfunc">Window functions</a>
    15     16   <li> <a href="json1.html">JSON functions</a>
    16     17   </ul>
    17     18   </li>
    18     19   <li> <a href="c3ref/intro.html">C/C++ Interface Spec</a>
    19     20   <ul>
    20     21   <li> <a href="cintro.html">Introduction</a>
    21     22   <li> <a href="c3ref/funclist.html">List of C-language APIs</a>

Changes to pages/changes.in.

    17     17   proc chng {date desc {options {}}} {
    18     18     global nChng aChng xrefChng
    19     19     set aChng($nChng) [list $date $desc $options]
    20     20     set xrefChng($date) $nChng
    21     21     incr nChng
    22     22   }
    23     23   
           24  +chng {2018-09-25 (3.25.2)} {
           25  +<li> Add the [PRAGMA legacy_alter_table=ON] command that causes the
           26  +     "ALTER TABLE RENAME" command to behave as it did in SQLite versions 3.24.0
           27  +     and earlier: references to the renamed table inside the bodies of triggers
           28  +     and views are not updated.  This new pragma provides a compatibility
           29  +     work around for older programs that expected the older, wonky behavior
           30  +     of ALTER TABLE RENAME.
           31  +<li> Fix a problem with the new [window functions] implementation that caused
           32  +     a malfunction when complicated expressions involving window functions were used
           33  +     inside of a view.
           34  +<li> Fixes for various other compiler warnings and minor problems associated
           35  +     with obscure configurations.
           36  +<p><b>Hashes:</b>
           37  +<li>SQLITE_SOURCE_ID: "2018-09-25 19:08:10 fb90e7189ae6d62e77ba3a308ca5d683f90bbe633cf681865365b8e92792d1c7"
           38  +<li>SHA3-256 for sqlite3.c: 34c23ff91631ae10354f8c9d62fd7d65732b3d7f3acfd0bbae31ff4a62fe28af
           39  +} {patchagainst 1 patchagainst 2}
           40  +
    24     41   chng {2018-09-18 (3.25.1)} {
    25     42   <li> Extra sanity checking added to ALTER TABLE in the 3.25.0 release
    26     43        sometimes raises a false-positive
    27     44        when the table being modified has a trigger that
    28     45        updates a virtual table.  The false-positive caused the ALTER
    29     46        TABLE to rollback, thus leaving the schema unchanged.
    30     47        Ticket [https://sqlite.org/src/info/b41031ea2b537237|b41031ea2b537237].

Changes to pages/chronology.in.

    24     24   #    ORDER BY mtime DESC;
    25     25   #
    26     26   # A small amount of manual editing and de-duplication followed.
    27     27   #
    28     28   # Manually edit the list for each subsequent release.
    29     29   #      
    30     30   foreach line [split {
           31  +fb90e7189a|2018-09-25|Version 3.25.2
    31     32   2ac9003de4|2018-09-18|Version 3.25.1
    32     33   b63af6c3bd|2018-09-15|Version 3.25.0
    33     34   c7ee083322|2018-06-04|Version 3.24.0
    34     35   4bb2294022|2018-04-10|Version 3.23.1
    35     36   736b53f57f|2018-04-02|Version 3.23.0
    36     37   0c55d17973|2018-01-22|Version 3.22.0
    37     38   1a584e4999|2017-10-24|Version 3.21.0

Changes to pages/index.in.

    10     10   [full-featured SQL|full-featured], [public-domain],
    11     11   SQL database engine.
    12     12   SQLite is the [most used] database engine in the world.
    13     13   <a class="button" href="about.html">More Info</a></p>
    14     14   
    15     15   <hr class="xhr">
    16     16   <span class="hdrfont">Latest Release:&nbsp;&nbsp;</span>
    17         -<a href="releaselog/3_25_1.html">Version 3.25.1</a> ([dateof:3.25.1]).
           17  +<a href="releaselog/3_25_2.html">Version 3.25.2</a> ([dateof:3.25.2]).
    18     18   <a class="button" href="download.html">Download</a>
    19     19   <a class="button" href="chronology.html">Prior Releases</a>
    20     20   
    21     21   <div class="mobileonly">
    22     22   <hr class="xhr">
    23     23   <h3>Common Links</h3>
    24     24   <tcl>common_links</tcl>

Changes to pages/lang.in.

  2386   2386   <p>The boolean identifiers TRUE and FALSE are usually just aliases for
  2387   2387   the integer values 1 and 0, respectively.  However, if TRUE or FALSE
  2388   2388   occur on the right-hand side of an IS operator, then they form new
  2389   2389   unary postfix operators "IS TRUE" and "IS FALSE" which test the boolean
  2390   2390   value of the operand on the left.
  2391   2391   
  2392   2392   <h3>Functions</h3>
  2393         -<p>SQLite supports many [corefunc|simple] and [aggfunc|aggregate]
         2393  +<p>SQLite supports many [corefunc|simple], [aggfunc|aggregate],
         2394  +and [window functions|window]
  2394   2395   SQL functions.  For presentation purposes, simple functions are further
  2395         -subdivided into [corefunc | core functions] and [datefunc|date-time functions].
         2396  +subdivided into [corefunc | core functions], [datefunc|date-time functions],
         2397  +and [json1|JSON functions].
  2396   2398   Applications can add new functions, written in C/C++, using the
  2397   2399   [sqlite3_create_function()] interface.
  2398   2400   </p>
  2399   2401   
  2400   2402   <p>^It is possible to have an aggregate function with the same name as a
  2401   2403   simple function, as long as the number of arguments for the two forms of the
  2402   2404   function are different.  ^For example, the [agg_max|max()] function with a
................................................................................
  2440   2442     2 arguments.
  2441   2443   }
  2442   2444   
  2443   2445   funcdef {glob(X,Y)} {} {
  2444   2446     ^The glob(X,Y) function is equivalent to the
  2445   2447     expression "<b>Y GLOB X</b>".
  2446   2448     Note that the X and Y arguments are reversed in the glob() function
  2447         -  relative to the infix [GLOB] operator.
  2448         -  ^If the [sqlite3_create_function()] interface is used to
         2449  +  relative to the infix [GLOB] operator.  Y is the string and X is the
         2450  +  pattern.  So, for example, the following expressions are equivalent:
         2451  +  <blockquote><pre>
         2452  +     name LIKE '*helium*'
         2453  +     glob('*helium*',name)
         2454  +  </pre></blockquote>
         2455  +  <p>^If the [sqlite3_create_function()] interface is used to
  2449   2456     override the glob(X,Y) function with an alternative implementation then
  2450   2457     the [GLOB] operator will invoke the alternative implementation.
  2451   2458   }
  2452   2459   
  2453   2460   funcdef {ifnull(X,Y)} {} {
  2454   2461     ^The ifnull() function returns a copy of its first non-NULL argument, or
  2455   2462     NULL if both arguments are NULL.  ^Ifnull() must have exactly 2 arguments.
................................................................................
  2496   2503   funcdef {like(X,Y) like(X,Y,Z)} {} {
  2497   2504     ^The like() function is used to implement the
  2498   2505     "<b>Y LIKE X &#91;ESCAPE Z&#93;</b>" expression. 
  2499   2506     ^If the optional ESCAPE clause is present, then the
  2500   2507     like() function is invoked with three arguments.  ^Otherwise, it is
  2501   2508     invoked with two arguments only. Note that the X and Y parameters are
  2502   2509     reversed in the like() function relative to the infix [LIKE] operator.
         2510  +  X is the pattern and Y is the string to match against that pattern.
         2511  +  Hence, the following expressions are equivalent:
         2512  +  <blockquote><pre>
         2513  +     name LIKE '%neon%'
         2514  +     like('%neon%',name)
         2515  +  </pre></blockquote>
  2503   2516     ^The [sqlite3_create_function()] interface can be used to override the
  2504   2517     like() function and thereby change the operation of the
  2505   2518     [LIKE] operator.  When overriding the like() function, it may be important
  2506   2519     to override both the two and three argument versions of the like() 
  2507   2520     function. Otherwise, different code may be called to implement the
  2508   2521     [LIKE] operator depending on whether or not an ESCAPE clause was 
  2509   2522     specified.
................................................................................
  2785   2798     ^This SQL function is implemented using the [sqlite3_result_zeroblob()]
  2786   2799     routine from the C/C++ interface.
  2787   2800   }
  2788   2801   </tcl>
  2789   2802   
  2790   2803   <p>The core functions shown below are available by default. 
  2791   2804   [datefunc | Date &amp; Time functions],
  2792         -[aggfunc | aggregate functions], and
         2805  +[aggfunc | aggregate functions],
         2806  +[builtin window functions|window functions], and
  2793   2807   [json1 | JSON functions] are documented separately.  An
  2794   2808   application may define additional
  2795   2809   functions written in C and added to the database engine using
  2796   2810   the [sqlite3_create_function()] API.</p>
  2797   2811   
  2798   2812   <tcl>
  2799   2813   set lx {}
................................................................................
  3010   3024   so the date is normalized to 2001-05-01.  ^A similar effect occurs when
  3011   3025   the original date is February 29 of a leapyear and the modifier is
  3012   3026   &plusmn;N years where N is not a multiple of four.</p>
  3013   3027   
  3014   3028   <p>^The "start of" modifiers (7 through 9) shift the date backwards 
  3015   3029   to the beginning of the current month, year or day.</p>
  3016   3030   
  3017         -<p>^The "weekday" modifier advances the date forward to the next date 
  3018         -where the weekday number is N. Sunday is 0, Monday is 1, and so forth.</p>
         3031  +<p>^The "weekday" modifier advances the date forward, if necessary,
         3032  +to the next date where the weekday number is N. Sunday is 0, Monday is 1,
         3033  +and so forth.
         3034  +^If the date is already on the desired weekday, the "weekday" modifier
         3035  +leaves the date unchanged.  </p>
  3019   3036   
  3020   3037   <p>^The "unixepoch" modifier (11) only works if it immediately follows 
  3021   3038   a timestring in the DDDDDDDDDD format. 
  3022   3039   ^This modifier causes the DDDDDDDDDD to be interpreted not 
  3023   3040   as a Julian day number as it normally would be, but as
  3024   3041   [http://en.wikipedia.org/wiki/Unix_time | Unix Time] - the 
  3025   3042   number of seconds since 1970.  If the "unixepoch" modifier does not

Changes to pages/news.in.

    14     14     hd_puts "<h3>$date - $title</h3>"
    15     15     regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
    16     16     regsub -all {[Tt]icket #(\d+)} $txt \
    17     17         {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
    18     18     hd_resolve "<blockquote>$txt</blockquote>"
    19     19     hd_puts "<hr width=\"50%\">"
    20     20   }
           21  +
           22  +newsitem {2018-09-25} {Release 3.25.2} {
           23  +SQLite [version 3.25.2] is another patch against 3.25.0 that fixes still
           24  +more problems associated with the new [window function] feature and the
           25  +[ALTER TABLE] enhancements.  Of particular note is the new
           26  +[PRAGMA legacy_alter_table=ON] command, which causes the ALTER TABLE RENAME
           27  +command to behave in the same goofy way that it did before the enhancements
           28  +found in version 3.25.0 &rarr; references to renamed tables that are inside
           29  +the bodies of triggers and views are not updated.  The legacy behavior is
           30  +arguably a bug, but some programs depend on the older buggy behavior.  The
           31  +3.25.2 release also contains a fix to [window function] processing for
           32  +VIEWs.  There also a slew of other minor fixes that affect obscure
           33  +compile-time options.  See the
           34  +[https://sqlite.org/src/timeline?r=branch-3.25|Fossil Timeline] for
           35  +details.
           36  +}
    21     37   
    22     38   newsitem {2018-09-18} {Release 3.25.1} {
    23     39   SQLite [version 3.25.1] is a patch against version 3.25.0 that contains
    24     40   two one-line fixes for bug that were introduced in version 3.25.0.
    25     41   See the change log for details.  Upgrading from 3.25.0 is recommended.
    26     42   }
    27     43   

Changes to pages/pragma.in.

   766    766     <p>^This pragma only operates on the single database specified prior
   767    767     to the pragma name (or on the "main" database if no database is specified.)
   768    768     There is no way to change the journal size limit on all attached databases
   769    769     using a single PRAGMA statement.  The size limit must be set separately for
   770    770     each attached database.
   771    771   }
   772    772   
          773  +
          774  +Pragma legacy_alter_table {
          775  +   <p>^(<b>PRAGMA legacy_alter_table;
          776  +       <br>PRAGMA legacy_alter_table = <i>boolean</i></b></p>
          777  +    <p>This pragma sets or queries the value of the legacy_alter_table
          778  +    flag.)^  ^(When this flag is on, the ALTER TABLE RENAME
          779  +    command (for changing the name of a table) works as it did
          780  +    in SQLite 3.24.0 ([dateof:3.24.0]) and earlier.)^  More specifically,
          781  +    when this flag is on
          782  +    the ALTER TABLE RENAME command only rewrites the initial occurrence
          783  +    of the table name in its CREATE TABLE statement and in any associated
          784  +    CREATE INDEX and CREATE TRIGGER statements.  Other references to the
          785  +    table are unmodifed, including:
          786  +    <ul>
          787  +    <li> References to the table within the bodies of triggers and views.
          788  +    <li> References to the table within CHECK constraints in the original
          789  +         CREATE TABLE statement.
          790  +    <li> References to the table within the WHERE clauses of [partial indexes].
          791  +    </ul>
          792  +    The default setting for this pragma is OFF, which means that all
          793  +    references to the table anywhere in the schema are converted to the new name.
          794  +    <p>This pragma is provided as a work-around for older programs that
          795  +    contain code that expect the incomplete behavior
          796  +    of ALTER TABLE RENAME found in older versions of SQLite.
          797  +    New applications should leave this flag turned off.
          798  +    <p>For compability with older [virtual table] implementations,
          799  +    this flag is turned on temporarily while the [sqlite3_module.xRename]
          800  +    method is being run.  The value of this flag is restore after the 
          801  +    [sqlite3_module.xRename] method finishes.
          802  +}
   773    803   
   774    804   Pragma legacy_file_format {
   775    805      <p>^(<b>PRAGMA legacy_file_format;
   776    806          <br>PRAGMA legacy_file_format = <i>boolean</i></b></p>
   777    807       <p>This pragma sets or queries the value of the legacy_file_format
   778    808       flag.)^  ^(When this flag is on, new SQLite databases are created in
   779    809       a file format that is readable and writable by all versions of

Changes to pages/vtab.in.

  1373   1373   </codeblock>
  1374   1374   
  1375   1375   <p>This method provides notification that the virtual table implementation
  1376   1376   that the virtual table will be given a new name. 
  1377   1377   If this method returns [SQLITE_OK] then SQLite renames the table.
  1378   1378   If this method returns an [error code] then the renaming is prevented.
  1379   1379   
  1380         -<p>The xRename method is required for every virtual table implementation.
         1380  +<p>The xRename method is optional.  If omitted, then the virtual
         1381  +table may not be renamed using the ALTER TABLE RENAME command.
         1382  +
         1383  +<p>The [PRAGMA legacy_alter_table] setting is enabled prior to invoking this
         1384  +method, and the value for legacy_alter_table is restored after this
         1385  +method finishes.  This is necessary for the correct operation of virtual
         1386  +tables that make use of [shadow tables] where the shadow tables must be
         1387  +renamed to match the new virtual table name.  If the legacy_alter_format is
         1388  +off, then the xConnect method will be invoked for the virtual table every
         1389  +time the xRename method tries to change the name of the shadow table.
  1381   1390   
  1382   1391   <tcl>############################################################# xSavepoint
  1383   1392   hd_fragment xsavepoint {sqlite3_module.xSavepoint} {xSavepoint}\
  1384   1393     xRelease xRollbackTo</tcl>
  1385   1394   <h2>The xSavepoint, xRelease, and xRollbackTo Methods</h2>
  1386   1395   
  1387   1396   <codeblock>

Changes to pages/windowfunctions.in.

     1         -<tcl>hd_keywords {window functions}</tcl>
            1  +<tcl>hd_keywords {window functions} {window function}</tcl>
     2      2   
     3      3   <title>Window Functions</title>
     4      4   <table_of_contents>
     5      5   
     6      6   <h1>Introduction to Window Functions</h1>
     7      7   
     8      8   <p>A window function is a special SQL function where the input
................................................................................
   313    313   for all ORDER BY expressions are considered peers regardless of the frame type.
   314    314   
   315    315   <p> Most built-in window functions ignore the
   316    316   <yynonterm>frame-spec</yynonterm>, the exceptions being first_value(),
   317    317   last_value() and nth_value(). It is a syntax error to specify a FILTER 
   318    318   clause as part of a built-in window function invocation.
   319    319   
          320  +<tcl>hd_fragment biwinfunc {builtin window functions}</tcl>
   320    321   <p> SQLite supports the following 11 built-in window functions:
   321    322   
   322    323   <dl>
   323    324     <dt><p><b>row_number()</b>
   324    325     <dd><p> The number of the row within the current partition. Rows are
   325    326          numbered starting from 1 in the order defined by the ORDER BY clause in
   326    327          the window definition, or in arbitrary order otherwise.