Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix FROM clause in the SELECT syntax diagrams. Also fix typos in the text. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
a115a033da447f55050481769ea991c5 |
| User & Date: | drh 2014-01-30 17:41:13 |
Context
|
2014-01-30
| ||
| 19:39 | Make the "select-stmt" syntax diagram a closer fit to reality. (check-in: 0c43e7efb4 user: drh tags: trunk) | |
| 17:41 | Fix FROM clause in the SELECT syntax diagrams. Also fix typos in the text. (check-in: a115a033da user: drh tags: trunk) | |
| 15:56 | Update the mmap documentation to describe the problem of Windows not truncating memory mapped files. (check-in: fa77748670 user: drh tags: trunk) | |
Changes
Changes to art/syntax/bubble-generator-data.tcl.
347 347 select-stmt { 348 348 loop 349 349 {or 350 350 {stack 351 351 {opt {line WITH {opt RECURSIVE} {loop common-table-expression ,}}} 352 352 {line SELECT {or nil DISTINCT ALL} 353 353 {loop result-column ,}} 354 - {optx FROM {loop 355 - table-or-subquery 356 - {join-operator table-or-subquery join-constraint}}} 354 + {optx FROM {or {loop table-or-subquery ,} join-clause}} 357 355 {optx WHERE expr} 358 356 {optx GROUP BY {loop expr ,} {optx HAVING expr}} 359 357 {optx ORDER BY {loop ordering-term ,}} 360 358 {optx LIMIT expr {optx {or OFFSET ,} expr}} 361 359 } 362 360 {line VALUES {loop {line ( {loop expr ,} )} ,}} 363 361 } 364 362 compound-operator 365 363 } 364 + join-clause { 365 + line 366 + table-or-subquery 367 + {opt {loop {line join-operator table-or-subquery join-constraint}}} 368 + } 366 369 simple-select-stmt { 367 370 or 368 371 {stack 369 372 {line SELECT {or nil DISTINCT ALL} 370 373 {loop result-column ,}} 371 - {optx FROM {loop 372 - table-or-subquery 373 - {join-operator table-or-subquery join-constraint}}} 374 + {optx FROM {or {loop table-or-subquery ,} join-clause}} 374 375 {optx WHERE expr} 375 376 {optx GROUP BY {loop expr ,} {optx HAVING expr}} 376 377 } 377 378 {line VALUES {loop {line ( {loop expr ,} )} ,}} 378 379 } 379 380 extended-simple-select-stmt { 380 381 stack
Added art/syntax/join-clause.gif.
cannot compute difference between binary files
Changes to art/syntax/select-stmt.gif.
cannot compute difference between binary files
Changes to art/syntax/simple-select-stmt.gif.
cannot compute difference between binary files
Changes to art/syntax/syntax_linkage.tcl.
24 24 set syntax_linkage(drop-trigger-stmt) {{} sql-stmt} 25 25 set syntax_linkage(drop-view-stmt) {{} sql-stmt} 26 26 set syntax_linkage(expr) {{literal-value raise-function select-stmt type-name} {attach-stmt column-constraint compound-select-stmt create-index-stmt create-trigger-stmt delete-stmt delete-stmt-limited extended-simple-select-stmt insert-stmt join-constraint ordering-term result-column select-stmt simple-select-stmt table-constraint update-stmt update-stmt-limited}} 27 27 set syntax_linkage(extended-simple-select-stmt) {{common-table-expression expr ordering-term simple-select-stmt} {}} 28 28 set syntax_linkage(foreign-key-clause) {{} {column-constraint table-constraint}} 29 29 set syntax_linkage(indexed-column) {{} {create-index-stmt table-constraint}} 30 30 set syntax_linkage(insert-stmt) {{expr select-stmt with-clause} {create-trigger-stmt sql-stmt}} 31 -set syntax_linkage(join-constraint) {expr {select-stmt simple-select-stmt table-or-subquery}} 32 -set syntax_linkage(join-operator) {{} {select-stmt simple-select-stmt}} 31 +set syntax_linkage(join-clause) {{join-constraint join-operator table-or-subquery} {select-stmt simple-select-stmt}} 32 +set syntax_linkage(join-constraint) {expr {join-clause table-or-subquery}} 33 +set syntax_linkage(join-operator) {{} join-clause} 33 34 set syntax_linkage(literal-value) {{} {column-constraint expr}} 34 35 set syntax_linkage(numeric-literal) {{} {}} 35 36 set syntax_linkage(ordering-term) {expr {compound-select-stmt delete-stmt-limited extended-simple-select-stmt select-stmt update-stmt-limited}} 36 37 set syntax_linkage(pragma-stmt) {pragma-value sql-stmt} 37 38 set syntax_linkage(pragma-value) {signed-number pragma-stmt} 38 39 set syntax_linkage(qualified-table-name) {{} {delete-stmt delete-stmt-limited update-stmt update-stmt-limited}} 39 40 set syntax_linkage(raise-function) {{} expr} 40 41 set syntax_linkage(recursive-cte) {cte-table-name {}} 41 42 set syntax_linkage(reindex-stmt) {{} sql-stmt} 42 43 set syntax_linkage(release-stmt) {{} sql-stmt} 43 44 set syntax_linkage(result-column) {expr {select-stmt simple-select-stmt}} 44 45 set syntax_linkage(rollback-stmt) {{} sql-stmt} 45 46 set syntax_linkage(savepoint-stmt) {{} sql-stmt} 46 -set syntax_linkage(select-stmt) {{common-table-expression compound-operator expr join-constraint join-operator ordering-term result-column table-or-subquery} {common-table-expression create-table-stmt create-trigger-stmt create-view-stmt expr insert-stmt sql-stmt table-or-subquery with-clause}} 47 +set syntax_linkage(select-stmt) {{common-table-expression compound-operator expr join-clause ordering-term result-column table-or-subquery} {common-table-expression create-table-stmt create-trigger-stmt create-view-stmt expr insert-stmt sql-stmt table-or-subquery with-clause}} 47 48 set syntax_linkage(signed-number) {{} {column-constraint pragma-value type-name}} 48 -set syntax_linkage(simple-select-stmt) {{expr join-constraint join-operator result-column table-or-subquery} {compound-select-stmt extended-simple-select-stmt}} 49 +set syntax_linkage(simple-select-stmt) {{expr join-clause result-column table-or-subquery} {compound-select-stmt extended-simple-select-stmt}} 49 50 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} 50 51 set syntax_linkage(sql-stmt-list) {sql-stmt {}} 51 52 set syntax_linkage(table-constraint) {{conflict-clause expr foreign-key-clause indexed-column} create-table-stmt} 52 -set syntax_linkage(table-or-subquery) {{join-constraint select-stmt} {select-stmt simple-select-stmt}} 53 +set syntax_linkage(table-or-subquery) {{join-constraint select-stmt} {join-clause select-stmt simple-select-stmt}} 53 54 set syntax_linkage(type-name) {signed-number {column-def expr}} 54 55 set syntax_linkage(update-stmt) {{expr qualified-table-name with-clause} {create-trigger-stmt sql-stmt}} 55 56 set syntax_linkage(update-stmt-limited) {{expr ordering-term qualified-table-name with-clause} sql-stmt} 56 57 set syntax_linkage(vacuum-stmt) {{} sql-stmt} 57 58 set syntax_linkage(with-clause) {{cte-table-name select-stmt} {delete-stmt delete-stmt-limited insert-stmt update-stmt update-stmt-limited}} 58 -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 pragma-stmt pragma-value reindex-stmt select-stmt simple-select-stmt extended-simple-select-stmt compound-select-stmt table-or-subquery result-column join-operator join-constraint ordering-term compound-operator update-stmt update-stmt-limited qualified-table-name vacuum-stmt comment-syntax} 59 +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 pragma-stmt pragma-value reindex-stmt select-stmt join-clause simple-select-stmt extended-simple-select-stmt compound-select-stmt table-or-subquery result-column join-operator join-constraint ordering-term compound-operator update-stmt update-stmt-limited qualified-table-name vacuum-stmt comment-syntax}
Changes to pages/lang.in.
3618 3618 3619 3619 <p>The "[select-stmt]" syntax diagram above attempts to show as much of the 3620 3620 SELECT statement syntax as possible in a single diagram, as some readers 3621 3621 find that helpful. Alternative syntax diagrams that break the syntax down 3622 3622 into smaller chunks are shown elsewhere in this document. 3623 3623 3624 3624 <p>Note that there are paths through the syntax diagrams that 3625 -are not allowed in practice. Some example: 3625 +are not allowed in practice. Some examples: 3626 3626 <ul> 3627 3627 <li>An [ORDER BY] or [LIMIT] clause can only occur on the right-most 3628 3628 SELECT of a [compound select]. 3629 3629 <li>A [VALUES] clause can be the first element in a [compound SELECT] 3630 3630 that uses a [WITH] clause, but a [simple SELECT] that consists of 3631 3631 just a [VALUES] clause cannot be preceded by a [WITH] clause. 3632 3632 <li>The [WITH] clause must occur on the first SELECT of a [compound SELECT]. ................................................................................ 3832 3832 3833 3833 <p> The list of expressions between the SELECT and FROM keywords is known as 3834 3834 the result expression list. ^If a result expression is the special expression 3835 3835 "*" then all columns in the input data are substituted for that one expression. 3836 3836 ^(If the expression is the alias of a table or subquery in the FROM clause 3837 3837 followed by ".*" then all columns from the named table or subquery are 3838 3838 substituted for the single expression.)^ ^(It is an error to use a "*" or 3839 -"alias.*" expression in any context other than than a result expression list.)^ 3839 +"alias.*" expression in any context other than a result expression list.)^ 3840 3840 ^(It is also an error to use a "*" or "alias.*" expression in a simple SELECT 3841 3841 query that does not have a FROM clause.)^ 3842 3842 3843 3843 <p> ^(The number of columns in the rows returned by a simple SELECT statement 3844 3844 is equal to the number of expressions in the result expression list after 3845 3845 substitution of * and alias.* expressions.)^ Each result row is calculated by 3846 3846 evaluating the expressions in the result expression list with respect to a ................................................................................ 3867 3867 GROUP BY clause always returns exactly one row of data, even if there are 3868 3868 zero rows of input data. 3869 3869 3870 3870 <li><p>^(If the SELECT statement is <b>an aggregate query with a GROUP 3871 3871 BY</b> clause, then each of the expressions specified as part of the 3872 3872 GROUP BY clause is evaluated for each row of the dataset. Each row 3873 3873 is then assigned to a "group" based on the results; rows for which 3874 - the results of evaluating the GROUP BY expressions are the same are 3874 + the results of evaluating the GROUP BY expressions are the same get 3875 3875 assigned to the same group.)^ ^For the purposes of grouping rows, NULL 3876 3876 values are considered equal. ^The usual rules for [collation|selecting a 3877 3877 collation sequence] with which to compare text values apply when evaluating 3878 3878 expressions in a GROUP BY clause. ^The expressions in the GROUP BY clause 3879 3879 do <em>not</em> have to be expressions that appear in the result. ^The 3880 3880 expressions in a GROUP BY clause may not be aggregate expressions. 3881 3881 ................................................................................ 4102 4102 A VALUES clause cannot be used together with a [WITH] clause in a 4103 4103 [simple SELECT]. 4104 4104 </ul> 4105 4105 4106 4106 4107 4107 <h3>The WITH Clause</h3> 4108 4108 4109 -<p>SELECT statements may be optional preceded by a single 4109 +<p>SELECT statements may be optionally preceded by a single 4110 4110 [WITH clause] that defines one or more [common table expressions] 4111 4111 for use within the SELECT statement. Contrary to what the 4112 4112 [select-stmt] syntax diagram shows, a [WITH clause] cannot occur 4113 4113 after a [compound-operator]. 4114 4114 4115 4115 <tcl> 4116 4116 ##############################################################################