Documentation Source Text

Check-in [a7856e4125]
Login

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

Overview
Comment:Add a description of NULLS FIRST and NULLS LAST. Add a note about the addition of sqlite3_drop_modules().
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: a7856e4125c57d75eb8e6c957734f05c87affeb50f7281bc959333dba2bb73bc
User & Date: drh 2019-08-29 17:19:33
Context
2019-09-04
15:18
Update the speed-and-size spreadsheet with the latest figures. check-in: 33e05d1299 user: drh tags: trunk
2019-08-29
17:19
Add a description of NULLS FIRST and NULLS LAST. Add a note about the addition of sqlite3_drop_modules(). check-in: a7856e4125 user: drh tags: trunk
2019-08-15
23:05
Mention new defensive capabilities in the security.html page. check-in: b714ca6b96 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to art/syntax/bubble-generator-data.tcl.

   468    468        or
   469    469           {line ON expr}
   470    470           {line USING ( {loop /column-name ,} )}
   471    471           nil
   472    472     }
   473    473     ordering-term {
   474    474         line expr {opt COLLATE /collation-name} {or nil ASC DESC} 
          475  +                {or nil {line NULLS FIRST} {line NULLS LAST}}
   475    476     }
   476    477     compound-operator {
   477    478        or UNION {line UNION ALL} INTERSECT EXCEPT
   478    479     }
   479    480     update-stmt {
   480    481        rightstack
   481    482           {line {opt with-clause} UPDATE {or {} {line OR ROLLBACK}

Changes to art/syntax/ordering-term.gif.

cannot compute difference between binary files

Changes to pages/changes.in.

    19     19     set aChng($nChng) [list $date $desc $options]
    20     20     set xrefChng($date) $nChng
    21     21     incr nChng
    22     22   }
    23     23   
    24     24   chng {2019-10-00 (3.30.0)} {
    25     25   <li> Add support for the [FILTER clause on aggregate functions].
           26  +<li> Add support for the [NULLS FIRST] and [NULLS LAST] syntax in [ORDER BY] clauses.
    26     27   <li> The [index_info] and [index_xinfo] pragmas are enhanced to provide
    27     28        information about the on-disk representation of [WITHOUT ROWID]
    28     29        tables.
           30  +<li> Add the [sqlite3_drop_modules()] interface, allowing applications
           31  +     to disable automatically loaded virtual tables that they do not
           32  +     need.
    29     33   <li> Improvements to the [.recover dot-command] in the [CLI] so that
    30     34        it recovers more content from corrupt database files.
    31     35   <li> Enhance the [RBU] extension to support [indexes on expressions].
    32     36   <li> Change the schema parser so that it will error out if any of
    33     37        the type, name, and tbl_name columns of the [sqlite_master table]
    34     38        have been corrupted and the database connection is not in
    35     39        [writable_schema] mode.

Changes to pages/datatype3.in.

   396    396   <tcl>hd_fragment comparisons {comparison expressions}</tcl>
   397    397   <h1>Comparison Expressions</h1>
   398    398   
   399    399   <p>SQLite version 3 has the usual set of SQL comparison operators
   400    400   including "=", "==", "&lt;", "&lt;=", "&gt;", "&gt;=", "!=", "<>",
   401    401   "IN", "NOT IN", "BETWEEN", "IS", and "IS NOT", .
   402    402   
          403  +<tcl>hd_fragment sortorder {sort order}</tcl>
   403    404   <h2>Sort Order</h2>
   404    405   
   405    406   <p>The results of a comparison depend on the storage classes of the
   406    407   operands, according to the following rules:</p>
   407    408   <ul>
   408    409     <li><p>^A value with storage class NULL is considered less than any
   409    410     other value (including another value with storage class NULL).</p>

Changes to pages/lang.in.

   938    938   and later are able to understand
   939    939   the descending index format. For compatibility, version of SQLite between 3.3.0
   940    940   and 3.7.9 use the legacy schema format by default.  The newer schema format is
   941    941   used by default in version 3.7.10 ([dateof:3.7.10]) and later.
   942    942   ^The [legacy_file_format pragma] can be used to change set the specific
   943    943   behavior for any version of SQLite.</p>
   944    944   
          945  +<p>The NULLS FIRST and NULLS LAST predicates are not supported
          946  +for indexes.  For [sort order|sorting purposes], SQLite considers NULL values 
          947  +to be smaller than all other values.  Hences NULL values always appear at
          948  +the beginning of an ASC index and at the end of a DESC index.</p>
          949  +
   945    950   <tcl>hd_fragment collidx {COLLATE}</tcl>
   946    951   <p>^The COLLATE clause optionally following each column name
   947    952   or expression defines a
   948    953   collating sequence used for text entries in that column.
   949    954   ^The default collating
   950    955   sequence is the collating sequence defined for that column in the
   951    956   [CREATE TABLE] statement.  ^Or if no collating sequence is otherwise defined,
................................................................................
  4830   4835   evaluating the left-most expression in the ORDER BY list, then ties are broken
  4831   4836   by evaluating the second left-most expression and so on. The order in which
  4832   4837   two rows for which all ORDER BY expressions evaluate to equal values are
  4833   4838   returned is undefined. ^Each ORDER BY expression may be optionally followed
  4834   4839   by one of the keywords ASC (smaller values are returned first) or DESC (larger
  4835   4840   values are returned first). ^If neither ASC or DESC are specified, rows
  4836   4841   are sorted in ascending (smaller values first) order by default.
         4842  +
         4843  +<tcl>hd_fragment nullslast {NULLS FIRST} {NULLS LAST}</tcl>
         4844  +<p>SQLite considers NULL values to be smaller than any other values
         4845  +for sorting purposes.  Hence, NULLs naturally appear at the beginning
         4846  +of an ASC order-by and at the end of a DESC order-by.  This can be changed
         4847  +using the "ASC NULLS LAST" or "DESC NULLS FIRST" syntax.
  4837   4848   
  4838   4849   <p>Each ORDER BY expression is processed as follows:</p>
  4839   4850   
  4840   4851   <ol>
  4841   4852   <li><p>^If the ORDER BY expression is a constant integer K then the
  4842   4853   expression is considered an alias for the K-th column of the result set
  4843   4854   (columns are numbered from left to right starting with 1).