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 Side-by-Side Diffs Ignore Whitespace Patch

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   ##############################################################################