Documentation Source Text

  expr {
    or
     {line literal-value}
     {line bind-parameter}
     {line {optx {optx /schema-name .} /table-name .} /column-name}
     {line /unary-operator expr}
     {line expr /binary-operator expr}
     {line /function-name ( {or {line {optx DISTINCT} {toploop expr ,}} {} *} )
           {opt filter-clause} {opt over-clause}}
     {line ( {toploop expr ,} )}
     {line CAST ( expr AS type-name )}
     {line expr COLLATE /collation-name}
     {line expr {optx NOT} {or LIKE GLOB REGEXP MATCH} expr
           {opt ESCAPE expr}}
     {line expr {or ISNULL NOTNULL {line NOT NULL}}}
     {line expr IS {optx NOT} expr}
     {line expr {optx NOT} BETWEEN expr AND expr}
     {line expr {optx NOT} IN 
            {or
               {line ( {or {} select-stmt {loop expr ,}} )}
               {line {optx /schema-name .} /table-name}
               {line {optx /schema-name .} /table-function
                         ( {or {toploop expr ,} {}} ) }
            }
     }
     {line {optx {optx NOT} EXISTS} ( select-stmt )}
     {line CASE {optx expr} {loop {line WHEN expr THEN expr} {}}
           {optx ELSE expr} END}
     {line raise-function}


  }
  raise-function {
     line RAISE ( 
           {or IGNORE
               {line {or ROLLBACK ABORT FAIL} , /error-message }
           } )
  }

  comment-syntax {
    or
      {line -- {loop nil /anything-except-newline} 
           {or /newline /end-of-input}}
      {line /* {loop nil /anything-except-*/}
           {or */ /end-of-input}}
  }
  filter-clause {
    line FILTER ( WHERE expr )
  }
  window-defn {
    stack {line ( {opt /base-window-name}}
          {opt PARTITION BY {loop expr ,}}
          {opt ORDER BY {loop ordering-term ,}}
          {line {optx frame-spec} )}
  }
  over-clause {
    line OVER
        {or /window-name
    {stack {line ( {opt /base-window-name}}
          {opt PARTITION BY {loop expr ,}}
          {opt ORDER BY {loop ordering-term ,}}
          {line {optx frame-spec} )}}
        }
  }
  frame-spec {
    stack {
      line {or RANGE ROWS GROUPS} {or
         {line BETWEEN {or {line UNBOUNDED PRECEDING}
                           {line expr PRECEDING}
                           {line CURRENT ROW}

         {line EXCLUDE NO OTHERS} 
         {line EXCLUDE CURRENT ROW} 
         {line EXCLUDE GROUP} 
         {line EXCLUDE TIES} 
      }
    }
  }
  simple-function-invocation {
    line /simple-func ( {or {line {toploop expr ,}} {} *} )
  }
  aggregate-function-invocation {
    line /aggregate-func ( {or {line {optx DISTINCT} {toploop expr ,}} {} *} )
           {opt filter-clause}
  }
  window-function-invocation {
    line /window-func ( {or {line {toploop expr ,}} {} *} ) 
         {opt filter-clause} OVER {or window-defn /window-name}
  }
}

set syntax_linkage(aggregate-function-invocation) {{expr filter-clause} {}}
set syntax_linkage(alter-table-stmt) {column-def sql-stmt}
set syntax_linkage(analyze-stmt) {{} sql-stmt}
set syntax_linkage(attach-stmt) {expr sql-stmt}
set syntax_linkage(begin-stmt) {{} sql-stmt}
set syntax_linkage(column-constraint) {{conflict-clause expr foreign-key-clause literal-value signed-number} column-def}
set syntax_linkage(column-def) {{column-constraint type-name} {alter-table-stmt create-table-stmt}}
set syntax_linkage(column-name-list) {{} {update-stmt update-stmt-limited upsert-clause}}

set syntax_linkage(delete-stmt) {{expr qualified-table-name with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(delete-stmt-limited) {{expr ordering-term qualified-table-name with-clause} sql-stmt}
set syntax_linkage(detach-stmt) {{} sql-stmt}
set syntax_linkage(drop-index-stmt) {{} sql-stmt}
set syntax_linkage(drop-table-stmt) {{} sql-stmt}
set syntax_linkage(drop-trigger-stmt) {{} sql-stmt}
set syntax_linkage(drop-view-stmt) {{} sql-stmt}
set syntax_linkage(expr) {{filter-clause literal-value over-clause raise-function select-stmt type-name} {aggregate-function-invocation attach-stmt column-constraint compound-select-stmt create-index-stmt create-trigger-stmt delete-stmt delete-stmt-limited factored-select-stmt filter-clause frame-spec indexed-column insert-stmt join-constraint ordering-term over-clause result-column select-core select-stmt simple-function-invocation simple-select-stmt table-constraint table-or-subquery update-stmt update-stmt-limited upsert-clause window-defn window-function-invocation}}
set syntax_linkage(factored-select-stmt) {{common-table-expression compound-operator expr ordering-term select-core} {}}
set syntax_linkage(filter-clause) {expr {aggregate-function-invocation expr window-function-invocation}}
set syntax_linkage(foreign-key-clause) {{} {column-constraint table-constraint}}
set syntax_linkage(frame-spec) {expr {over-clause window-defn}}

set syntax_linkage(indexed-column) {expr {create-index-stmt table-constraint upsert-clause}}
set syntax_linkage(insert-stmt) {{expr select-stmt upsert-clause with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(join-clause) {{join-constraint join-operator table-or-subquery} {select-core select-stmt table-or-subquery}}
set syntax_linkage(join-constraint) {expr join-clause}
set syntax_linkage(join-operator) {{} join-clause}
set syntax_linkage(literal-value) {{} {column-constraint expr}}
set syntax_linkage(numeric-literal) {{} {}}
set syntax_linkage(ordering-term) {expr {compound-select-stmt delete-stmt-limited factored-select-stmt over-clause select-stmt simple-select-stmt update-stmt-limited window-defn}}
set syntax_linkage(over-clause) {{expr frame-spec ordering-term} expr}
set syntax_linkage(pragma-stmt) {pragma-value sql-stmt}
set syntax_linkage(pragma-value) {signed-number pragma-stmt}
set syntax_linkage(qualified-table-name) {{} {delete-stmt delete-stmt-limited update-stmt update-stmt-limited}}
set syntax_linkage(raise-function) {{} expr}
set syntax_linkage(recursive-cte) {cte-table-name {}}
set syntax_linkage(reindex-stmt) {{} sql-stmt}
set syntax_linkage(release-stmt) {{} sql-stmt}
set syntax_linkage(result-column) {expr {select-core select-stmt}}
set syntax_linkage(rollback-stmt) {{} sql-stmt}
set syntax_linkage(savepoint-stmt) {{} sql-stmt}
set syntax_linkage(select-core) {{expr join-clause result-column table-or-subquery window-defn} {compound-select-stmt factored-select-stmt simple-select-stmt}}
set syntax_linkage(select-stmt) {{common-table-expression compound-operator expr join-clause ordering-term result-column table-or-subquery window-defn} {common-table-expression create-table-stmt create-trigger-stmt create-view-stmt expr insert-stmt sql-stmt table-or-subquery with-clause}}
set syntax_linkage(signed-number) {{} {column-constraint pragma-value type-name}}
set syntax_linkage(simple-function-invocation) {expr {}}
set syntax_linkage(simple-select-stmt) {{common-table-expression expr ordering-term select-core} {}}
set syntax_linkage(sql-stmt) {{alter-table-stmt analyze-stmt attach-stmt begin-stmt commit-stmt create-index-stmt create-table-stmt create-trigger-stmt create-view-stmt create-virtual-table-stmt delete-stmt delete-stmt-limited detach-stmt drop-index-stmt drop-table-stmt drop-trigger-stmt drop-view-stmt insert-stmt pragma-stmt reindex-stmt release-stmt rollback-stmt savepoint-stmt select-stmt update-stmt update-stmt-limited vacuum-stmt} sql-stmt-list}
set syntax_linkage(sql-stmt-list) {sql-stmt {}}
set syntax_linkage(table-constraint) {{conflict-clause expr foreign-key-clause indexed-column} create-table-stmt}
set syntax_linkage(table-or-subquery) {{expr join-clause select-stmt} {join-clause select-core select-stmt}}
set syntax_linkage(type-name) {signed-number {column-def expr}}
set syntax_linkage(update-stmt) {{column-name-list expr qualified-table-name with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(update-stmt-limited) {{column-name-list expr ordering-term qualified-table-name with-clause} sql-stmt}
set syntax_linkage(upsert-clause) {{column-name-list expr indexed-column} insert-stmt}
set syntax_linkage(vacuum-stmt) {{} sql-stmt}
set syntax_linkage(window-defn) {{expr frame-spec ordering-term} {select-core select-stmt window-function-invocation}}
set syntax_linkage(window-function-invocation) {{expr filter-clause window-defn} {}}
set syntax_linkage(with-clause) {{cte-table-name select-stmt} {delete-stmt delete-stmt-limited insert-stmt update-stmt update-stmt-limited}}
set syntax_order {sql-stmt-list sql-stmt alter-table-stmt analyze-stmt attach-stmt begin-stmt commit-stmt rollback-stmt savepoint-stmt release-stmt create-index-stmt indexed-column create-table-stmt column-def type-name column-constraint signed-number table-constraint foreign-key-clause conflict-clause create-trigger-stmt create-view-stmt create-virtual-table-stmt with-clause cte-table-name recursive-cte common-table-expression delete-stmt delete-stmt-limited detach-stmt drop-index-stmt drop-table-stmt drop-trigger-stmt drop-view-stmt expr raise-function literal-value numeric-literal insert-stmt upsert-clause pragma-stmt pragma-value reindex-stmt select-stmt join-clause select-core factored-select-stmt simple-select-stmt compound-select-stmt table-or-subquery result-column join-operator join-constraint ordering-term compound-operator update-stmt column-name-list update-stmt-limited qualified-table-name vacuum-stmt comment-syntax filter-clause window-defn over-clause frame-spec simple-function-invocation aggregate-function-invocation window-function-invocation}

set nChng 0
proc chng {date desc {options {}}} {
  global nChng aChng xrefChng
  set aChng($nChng) [list $date $desc $options]
  set xrefChng($date) $nChng
  incr nChng
}

chng {2019-10-00 (3.30.0)} {
<li> Add support for the [FILTER clause on aggregate functions].
}

chng {2019-07-10 (3.29.0)} {
<li> Added the [SQLITE_DBCONFIG_DQS_DML] and [SQLITE_DBCONFIG_DQS_DDL]
     actions to [sqlite3_db_config()] for activating and deactivating
     the [double-quoted string literal] misfeature.  Both default to "on"
     for legacy compatibility, but developers are encouraged to turn them
     "off", perhaps using the [-DSQLITE_DQS=0] compile-time option.

#    ORDER BY mtime DESC;
#
# A small amount of manual editing and de-duplication followed.
#
# Manually edit the list for each subsequent release.
#      
foreach line [split {
xxxxxxxxxx|pending|Version 3.30.0
fc82b73eaa|2019-07-10|Version 3.29.0
884b4b7e50|2019-04-16|Version 3.28.0
bd49a8271d|2019-02-25|Version 3.27.2
0eca3dd3d3|2019-02-08|Version 3.27.1
97744701c3|2019-02-07|Version 3.27.0
bf8c1b2b7a|2018-12-01|Version 3.26.0
89e099fbe5|2018-11-05|Version 3.25.3

<p>
SQLite [https://sqlite.org/src|source code]
is in the [public-domain] and is free to 
everyone to use for any purpose.

<h3>Latest Release</h3>
<a href="releaselog/3_30_0.html">Version 3.30.0</a> ([dateof:3.30.0]).
<a class="button" href="download.html">Download</a>
<a class="button" href="chronology.html">Prior Releases</a>

<div class="mobileonly">
<h3>Common Links</h3>
<tcl>common_links</tcl>
</div>

SQL functions.  For presentation purposes, simple functions are further
subdivided into [corefunc | core functions], [datefunc|date-time functions],
and [json1|JSON functions].
Applications can add new functions, written in C/C++, using the
[sqlite3_create_function()] interface.
</p>

<p>
The main expression bubble diagram above shows a single syntax for
all function invocations.  But this is merely to simplify the expression
bubble diagram.  In reality, each type of function has a slightly different
syntax, shown below.  The function invocation syntax shown in the main
expression bubble diagram is the union of the three syntaxes shown here:

<tcl>
BubbleDiagram simple-function-invocation
BubbleDiagram aggregate-function-invocation
BubbleDiagram window-function-invocation
</tcl>

<p>
The OVER clause is required for [window functions] and is prohibited
otherwise.  The DISTINCT keyword is only allowed in [aggfunc|aggregate functions].
The FILTER clause may not appear on a [corefunc|simple function].

<p>^It is possible to have an aggregate function with the same name as a
simple function, as long as the number of arguments for the two forms of the
function are different.  ^For example, the [agg_max|max()] function with a
single argument is an aggregate and the [max()] function with two or more
arguments is a simple function.  Aggregate functions can usually also
be used as window functions.

<tcl>
##############################################################################
Section {Core Functions} corefunc {*corefunc}
unset -nocomplain corefuncset
proc funcdef {syntax keywords desc} {
  global corefuncset

  are integers or NULL
  and an integer overflow occurs at any point during the computation.
  ^Total() never throws an integer overflow.
}
</tcl>


<tcl>
RecursiveBubbleDiagram aggregate-function-invocation
</tcl>

<p>
The aggregate functions shown below are available by default.  Additional
aggregate functions written in C may be added using the 
[sqlite3_create_function()]</a>
API.</p>

<p>
^In any aggregate function that takes a single argument, that argument
can be preceded by the keyword DISTINCT.  ^In such cases, duplicate
elements are filtered before being passed into the aggregate function.
^For example, the function "count(distinct X)" will return the number
of distinct values of column X instead of the total number of non-null
values in column X.
</p>

<tcl>hd_fragment aggfilter {FILTER clause on aggregate functions}</tcl>
<p>
If a FILTER clause is provided, then only rows for which the <i>expr</i> is
true are included in the aggregate.
</p>

<tcl>
set lx {}
foreach basesyntax [array names corefuncset] {
  foreach {syntax keywords desc} $corefuncset($basesyntax) break
  regexp {^[a-z_]+} $basesyntax fragment
  foreach coresyntax $syntax {

ordinary aggregate or scalar function. Window functions might also
have a FILTER clause in between the function and the OVER clause.

<p>The syntax for a window function is like this:


<tcl>
RecursiveBubbleDiagram window-function-invocation window-defn frame-spec filter-clause
</tcl>


<p>Unlike ordinary functions, window functions
cannot use the DISTINCT keyword.
Also, Window functions may only appear in the result set and in the
ORDER BY clause of a SELECT statement.

    ) AS ties
  FROM t1 ORDER BY c, a;
</codeblock>

<h2>The FILTER Clause</h2>

<tcl>
RecursiveBubbleDiagram filter-clause
</tcl>

<p>If a FILTER clause is provided, then only rows for which the <i>expr</i> is
true are included in the window frame. The aggregate window still returns a
value for every row, but those for which the FILTER expression evaluates to
other than true are not included in the window frame for any row. For example: