Documentation Source Text

Check-in [a115a033da]
Login

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.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a115a033da447f55050481769ea991c590693672
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
Hide Diffs Unified Diffs Show Whitespace Changes Patch

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

347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365





366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
  select-stmt {
   loop 
     {or
        {stack
            {opt {line WITH {opt RECURSIVE} {loop common-table-expression ,}}}
            {line SELECT {or nil DISTINCT ALL}
                                           {loop result-column ,}}
            {optx FROM {loop
                          table-or-subquery
                          {join-operator table-or-subquery join-constraint}}}
            {optx WHERE expr}
            {optx GROUP BY {loop expr ,} {optx HAVING expr}}
            {optx ORDER BY {loop ordering-term ,}}
            {optx LIMIT expr {optx {or OFFSET ,} expr}}
        }
        {line VALUES {loop {line ( {loop expr ,} )} ,}}
     }
     compound-operator
  }





  simple-select-stmt {
     or
        {stack
            {line SELECT {or nil DISTINCT ALL}
                                           {loop result-column ,}}
            {optx FROM {loop
                          table-or-subquery
                          {join-operator table-or-subquery join-constraint}}}
            {optx WHERE expr}
            {optx GROUP BY {loop expr ,} {optx HAVING expr}}
        }
        {line VALUES {loop {line ( {loop expr ,} )} ,}}
  }
  extended-simple-select-stmt {
    stack







<
|
<









>
>
>
>
>





<
|
<







347
348
349
350
351
352
353

354

355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373

374

375
376
377
378
379
380
381
  select-stmt {
   loop 
     {or
        {stack
            {opt {line WITH {opt RECURSIVE} {loop common-table-expression ,}}}
            {line SELECT {or nil DISTINCT ALL}
                                           {loop result-column ,}}

            {optx FROM {or {loop table-or-subquery ,} join-clause}}

            {optx WHERE expr}
            {optx GROUP BY {loop expr ,} {optx HAVING expr}}
            {optx ORDER BY {loop ordering-term ,}}
            {optx LIMIT expr {optx {or OFFSET ,} expr}}
        }
        {line VALUES {loop {line ( {loop expr ,} )} ,}}
     }
     compound-operator
  }
  join-clause {
    line
      table-or-subquery
      {opt {loop {line join-operator table-or-subquery join-constraint}}}
  }
  simple-select-stmt {
     or
        {stack
            {line SELECT {or nil DISTINCT ALL}
                                           {loop result-column ,}}

            {optx FROM {or {loop table-or-subquery ,} join-clause}}

            {optx WHERE expr}
            {optx GROUP BY {loop expr ,} {optx HAVING expr}}
        }
        {line VALUES {loop {line ( {loop expr ,} )} ,}}
  }
  extended-simple-select-stmt {
    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
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
set syntax_linkage(drop-trigger-stmt) {{} sql-stmt}
set syntax_linkage(drop-view-stmt) {{} sql-stmt}
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}}
set syntax_linkage(extended-simple-select-stmt) {{common-table-expression expr ordering-term simple-select-stmt} {}}
set syntax_linkage(foreign-key-clause) {{} {column-constraint table-constraint}}
set syntax_linkage(indexed-column) {{} {create-index-stmt table-constraint}}
set syntax_linkage(insert-stmt) {{expr select-stmt with-clause} {create-trigger-stmt sql-stmt}}

set syntax_linkage(join-constraint) {expr {select-stmt simple-select-stmt table-or-subquery}}
set syntax_linkage(join-operator) {{} {select-stmt simple-select-stmt}}
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 extended-simple-select-stmt select-stmt update-stmt-limited}}
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-stmt simple-select-stmt}}
set syntax_linkage(rollback-stmt) {{} sql-stmt}
set syntax_linkage(savepoint-stmt) {{} sql-stmt}
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}}
set syntax_linkage(signed-number) {{} {column-constraint pragma-value type-name}}
set syntax_linkage(simple-select-stmt) {{expr join-constraint join-operator result-column table-or-subquery} {compound-select-stmt extended-simple-select-stmt}}
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) {{join-constraint select-stmt} {select-stmt simple-select-stmt}}
set syntax_linkage(type-name) {signed-number {column-def expr}}
set syntax_linkage(update-stmt) {{expr qualified-table-name with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(update-stmt-limited) {{expr ordering-term qualified-table-name with-clause} sql-stmt}
set syntax_linkage(vacuum-stmt) {{} sql-stmt}
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 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}







>
|
|













|

|



|





|
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
set syntax_linkage(drop-trigger-stmt) {{} sql-stmt}
set syntax_linkage(drop-view-stmt) {{} sql-stmt}
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}}
set syntax_linkage(extended-simple-select-stmt) {{common-table-expression expr ordering-term simple-select-stmt} {}}
set syntax_linkage(foreign-key-clause) {{} {column-constraint table-constraint}}
set syntax_linkage(indexed-column) {{} {create-index-stmt table-constraint}}
set syntax_linkage(insert-stmt) {{expr select-stmt with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(join-clause) {{join-constraint join-operator table-or-subquery} {select-stmt simple-select-stmt}}
set syntax_linkage(join-constraint) {expr {join-clause table-or-subquery}}
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 extended-simple-select-stmt select-stmt update-stmt-limited}}
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-stmt simple-select-stmt}}
set syntax_linkage(rollback-stmt) {{} sql-stmt}
set syntax_linkage(savepoint-stmt) {{} sql-stmt}
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}}
set syntax_linkage(signed-number) {{} {column-constraint pragma-value type-name}}
set syntax_linkage(simple-select-stmt) {{expr join-clause result-column table-or-subquery} {compound-select-stmt extended-simple-select-stmt}}
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) {{join-constraint select-stmt} {join-clause select-stmt simple-select-stmt}}
set syntax_linkage(type-name) {signed-number {column-def expr}}
set syntax_linkage(update-stmt) {{expr qualified-table-name with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(update-stmt-limited) {{expr ordering-term qualified-table-name with-clause} sql-stmt}
set syntax_linkage(vacuum-stmt) {{} sql-stmt}
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 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
3619
3620
3621
3622
3623
3624
3625
3626
3627
3628
3629
3630
3631
3632
....
3832
3833
3834
3835
3836
3837
3838
3839
3840
3841
3842
3843
3844
3845
3846
....
3867
3868
3869
3870
3871
3872
3873
3874
3875
3876
3877
3878
3879
3880
3881
....
4102
4103
4104
4105
4106
4107
4108
4109
4110
4111
4112
4113
4114
4115
4116

<p>The "[select-stmt]" syntax diagram above attempts to show as much of the
SELECT statement syntax as possible in a single diagram, as some readers
find that helpful.  Alternative syntax diagrams that break the syntax down
into smaller chunks are shown elsewhere in this document.

<p>Note that there are paths through the syntax diagrams that
are not allowed in practice.  Some example:
<ul>
<li>An [ORDER BY] or [LIMIT] clause can only occur on the right-most 
    SELECT of a [compound select].
<li>A [VALUES] clause can be the first element in a [compound SELECT]
    that uses a [WITH] clause, but a [simple SELECT] that consists of
    just a [VALUES] clause cannot be preceded by a [WITH] clause.
<li>The [WITH] clause must occur on the first SELECT of a [compound SELECT].
................................................................................

<p> The list of expressions between the SELECT and FROM keywords is known as
the result expression list.  ^If a result expression is the special expression
"*" then all columns in the input data are substituted for that one expression.
^(If the expression is the alias of a table or subquery in the FROM clause
followed by ".*" then all columns from the named table or subquery are
substituted for the single expression.)^ ^(It is an error to use a "*" or
"alias.*" expression in any context other than than a result expression list.)^
^(It is also an error to use a "*" or "alias.*" expression in a simple SELECT
query that does not have a FROM clause.)^

<p> ^(The number of columns in the rows returned by a simple SELECT statement
is equal to the number of expressions in the result expression list after
substitution of * and alias.* expressions.)^ Each result row is calculated by
evaluating the expressions in the result expression list with respect to a
................................................................................
    GROUP BY clause always returns exactly one row of data, even if there are
    zero rows of input data.

  <li><p>^(If the SELECT statement is <b>an aggregate query with a GROUP
    BY</b> clause, then each of the expressions specified as part of the
    GROUP BY clause is evaluated for each row of the dataset. Each row
    is then assigned to a "group" based on the results; rows for which
    the results of evaluating the GROUP BY expressions are the same are
    assigned to the same group.)^ ^For the purposes of grouping rows, NULL 
    values are considered equal. ^The usual rules for [collation|selecting a
    collation sequence] with which to compare text values apply when evaluating
    expressions in a GROUP BY clause.  ^The expressions in the GROUP BY clause
    do <em>not</em> have to be expressions that appear in the result. ^The
    expressions in a GROUP BY clause may not be aggregate expressions.

................................................................................
A VALUES clause cannot be used together with a [WITH] clause in a
[simple SELECT].
</ul>


<h3>The WITH Clause</h3>

<p>SELECT statements may be optional preceded by a single
[WITH clause] that defines one or more [common table expressions]
for use within the SELECT statement.  Contrary to what the
[select-stmt] syntax diagram shows, a [WITH clause] cannot occur
after a [compound-operator].

<tcl>
##############################################################################







|







 







|







 







|







 







|







3618
3619
3620
3621
3622
3623
3624
3625
3626
3627
3628
3629
3630
3631
3632
....
3832
3833
3834
3835
3836
3837
3838
3839
3840
3841
3842
3843
3844
3845
3846
....
3867
3868
3869
3870
3871
3872
3873
3874
3875
3876
3877
3878
3879
3880
3881
....
4102
4103
4104
4105
4106
4107
4108
4109
4110
4111
4112
4113
4114
4115
4116

<p>The "[select-stmt]" syntax diagram above attempts to show as much of the
SELECT statement syntax as possible in a single diagram, as some readers
find that helpful.  Alternative syntax diagrams that break the syntax down
into smaller chunks are shown elsewhere in this document.

<p>Note that there are paths through the syntax diagrams that
are not allowed in practice.  Some examples:
<ul>
<li>An [ORDER BY] or [LIMIT] clause can only occur on the right-most 
    SELECT of a [compound select].
<li>A [VALUES] clause can be the first element in a [compound SELECT]
    that uses a [WITH] clause, but a [simple SELECT] that consists of
    just a [VALUES] clause cannot be preceded by a [WITH] clause.
<li>The [WITH] clause must occur on the first SELECT of a [compound SELECT].
................................................................................

<p> The list of expressions between the SELECT and FROM keywords is known as
the result expression list.  ^If a result expression is the special expression
"*" then all columns in the input data are substituted for that one expression.
^(If the expression is the alias of a table or subquery in the FROM clause
followed by ".*" then all columns from the named table or subquery are
substituted for the single expression.)^ ^(It is an error to use a "*" or
"alias.*" expression in any context other than a result expression list.)^
^(It is also an error to use a "*" or "alias.*" expression in a simple SELECT
query that does not have a FROM clause.)^

<p> ^(The number of columns in the rows returned by a simple SELECT statement
is equal to the number of expressions in the result expression list after
substitution of * and alias.* expressions.)^ Each result row is calculated by
evaluating the expressions in the result expression list with respect to a
................................................................................
    GROUP BY clause always returns exactly one row of data, even if there are
    zero rows of input data.

  <li><p>^(If the SELECT statement is <b>an aggregate query with a GROUP
    BY</b> clause, then each of the expressions specified as part of the
    GROUP BY clause is evaluated for each row of the dataset. Each row
    is then assigned to a "group" based on the results; rows for which
    the results of evaluating the GROUP BY expressions are the same get
    assigned to the same group.)^ ^For the purposes of grouping rows, NULL 
    values are considered equal. ^The usual rules for [collation|selecting a
    collation sequence] with which to compare text values apply when evaluating
    expressions in a GROUP BY clause.  ^The expressions in the GROUP BY clause
    do <em>not</em> have to be expressions that appear in the result. ^The
    expressions in a GROUP BY clause may not be aggregate expressions.

................................................................................
A VALUES clause cannot be used together with a [WITH] clause in a
[simple SELECT].
</ul>


<h3>The WITH Clause</h3>

<p>SELECT statements may be optionally preceded by a single
[WITH clause] that defines one or more [common table expressions]
for use within the SELECT statement.  Contrary to what the
[select-stmt] syntax diagram shows, a [WITH clause] cannot occur
after a [compound-operator].

<tcl>
##############################################################################