Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Comment: | Update syntax diagrams to show the FILTER clause on aggregate functions. Start a change log for version 3.30.0. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
ba9800704d740c02a9d82320715d95eb |
User & Date: | drh 2019-07-15 19:25:49.060 |
2019-07-17
| ||
13:02 | Preliminary documentation for the enhancements to index_info and index_xinfo to support viewing the on-disk representation of WITHOUT ROWID tables. (check-in: 296e23a4b8 user: drh tags: trunk) | |
2019-07-15
| ||
19:25 | Update syntax diagrams to show the FILTER clause on aggregate functions. Start a change log for version 3.30.0. (check-in: ba9800704d user: drh tags: trunk) | |
17:48 | Minor typos fixes in the lemon documentation. (check-in: bd1181fb39 user: drh tags: trunk) | |
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
︙ | ︙ | |||
254 255 256 257 258 259 260 | 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} | | > < < | 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 | 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 } } ) } |
︙ | ︙ | |||
517 518 519 520 521 522 523 | comment-syntax { or {line -- {loop nil /anything-except-newline} {or /newline /end-of-input}} {line /* {loop nil /anything-except-*/} {or */ /end-of-input}} } | | | > > > > > > > > > > | 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 | 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} |
︙ | ︙ | |||
553 554 555 556 557 558 559 | {line EXCLUDE NO OTHERS} {line EXCLUDE CURRENT ROW} {line EXCLUDE GROUP} {line EXCLUDE TIES} } } } | | > > > | > | | 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 | {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} } } |
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
1 2 3 4 5 6 7 | 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}} | > | 1 2 3 4 5 6 7 8 | 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}} |
︙ | ︙ | |||
20 21 22 23 24 25 26 | 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} | | | | < | > > | | | | 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 | 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} |
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
cannot compute difference between binary files
︙ | ︙ | |||
16 17 18 19 20 21 22 23 24 25 26 27 28 29 | 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-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. | > > > > | 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | 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. |
︙ | ︙ |
︙ | ︙ | |||
24 25 26 27 28 29 30 31 32 33 34 35 36 37 | # 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 { 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 | > | 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | # 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 |
︙ | ︙ |
︙ | ︙ | |||
38 39 40 41 42 43 44 | <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> | | | 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | <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> |
︙ | ︙ |
︙ | ︙ | |||
2554 2555 2556 2557 2558 2559 2560 2561 2562 2563 2564 | 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>^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 | > > > > > > > > > > > > > > > > > > | > | 2554 2555 2556 2557 2558 2559 2560 2561 2562 2563 2564 2565 2566 2567 2568 2569 2570 2571 2572 2573 2574 2575 2576 2577 2578 2579 2580 2581 2582 2583 2584 2585 2586 2587 2588 2589 2590 2591 | 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 |
︙ | ︙ | |||
3387 3388 3389 3390 3391 3392 3393 3394 3395 3396 3397 3398 3399 3400 3401 3402 3403 3404 3405 3406 3407 3408 3409 3410 3411 3412 3413 3414 | are integers or NULL and an integer overflow occurs at any point during the computation. ^Total() never throws an integer overflow. } </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> set lx {} foreach basesyntax [array names corefuncset] { foreach {syntax keywords desc} $corefuncset($basesyntax) break regexp {^[a-z_]+} $basesyntax fragment foreach coresyntax $syntax { | > > > > > > > > > > | 3406 3407 3408 3409 3410 3411 3412 3413 3414 3415 3416 3417 3418 3419 3420 3421 3422 3423 3424 3425 3426 3427 3428 3429 3430 3431 3432 3433 3434 3435 3436 3437 3438 3439 3440 3441 3442 3443 | 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 { |
︙ | ︙ |
︙ | ︙ | |||
15 16 17 18 19 20 21 | 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> | | | 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | 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. |
︙ | ︙ | |||
471 472 473 474 475 476 477 | ) AS ties FROM t1 ORDER BY c, a; </codeblock> <h2>The FILTER Clause</h2> <tcl> | | | 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 | ) 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: |
︙ | ︙ |