Documentation Source Text

Check-in [1cb27a268e]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Change the documentation for LIMIT and OFFSET clauses to be more testable.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 1cb27a268e961fa4d1494adb7fcde5dc7ab1347c
User & Date: dan 2010-09-16 18:50:40
Context
2010-09-17
00:50
Add coverage percentages to the bottom of the requirements matrix. check-in: 0f0f4c2b6f user: drh tags: trunk
2010-09-16
18:50
Change the documentation for LIMIT and OFFSET clauses to be more testable. check-in: 1cb27a268e user: dan tags: trunk
2010-09-15
22:17
Updates to the documentation for the new sqlite3_soft_heap_limit64() interface. check-in: 5d170249d2 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/lang.in.

2919
2920
2921
2922
2923
2924
2925
2926
2927
2928
2929
2930
2931
2932

2933
2934
2935
2936
2937
2938
2939
2940
2941
2942
2943
2944
2945
2946
2947
2948

2949

















2950
2951
2952
2953
2954
2955
2956
  expression is used.
  <li><p>^Otherwise, if the ORDER BY expression is a column or an alias of
  an expression that is a column, then the default collation sequence for
  the column is used. 
  <li><p>^Otherwise, the [BINARY] collation sequence is used.
</ol>

<p>^In a compound SELECT statement, all ORDER BY expressions are handled
as aliases for one of the result columns of the compound SELECT.
^(If an ORDER BY expression is not an integer alias, then SQLite searches
the left-most SELECT in the compound for a result column that matches either
the second or third rules above. If a match is found, the search stops and
the expression is handled as an alias for the result column that it has been
matched against. Otherwise, the next SELECT to the right is tried, and so on.

This continues until a match is found.)^ ^Each term of the ORDER BY clause is
processed separately and may be matched against result columns from different
SELECT statements in the compound.</p>

<p>^The LIMIT clause places an upper bound on the number of rows returned in
the result. ^A negative LIMIT indicates no upper bound. ^The optional OFFSET
following LIMIT specifies how many rows to skip at the beginning of the result
set.  ^In a compound query, the LIMIT clause may only appear on the final
SELECT statement. ^The limit is applied to the entire query not to the
individual SELECT statement to which it is attached.  ^Note that if the OFFSET
keyword is used in the LIMIT clause, then the limit is the first number and the
offset is the second number.  ^If a comma is used instead of the OFFSET
keyword, then the offset is the first number and the limit is the second
number.  This seeming contradiction is intentional - it maximizes compatibility
with legacy SQL database systems.
</p>




















<tcl>
##############################################################################
Section UPDATE update {UPDATE *UPDATEs}

BubbleDiagram update-stmt 1
BubbleDiagram qualified-table-name







|





|
>
|



|
<
|
|
|
|
|
|
|
|
|
<
>

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







2919
2920
2921
2922
2923
2924
2925
2926
2927
2928
2929
2930
2931
2932
2933
2934
2935
2936
2937
2938

2939
2940
2941
2942
2943
2944
2945
2946
2947

2948
2949
2950
2951
2952
2953
2954
2955
2956
2957
2958
2959
2960
2961
2962
2963
2964
2965
2966
2967
2968
2969
2970
2971
2972
2973
  expression is used.
  <li><p>^Otherwise, if the ORDER BY expression is a column or an alias of
  an expression that is a column, then the default collation sequence for
  the column is used. 
  <li><p>^Otherwise, the [BINARY] collation sequence is used.
</ol>

<p>In a compound SELECT statement, all ORDER BY expressions are handled
as aliases for one of the result columns of the compound SELECT.
^(If an ORDER BY expression is not an integer alias, then SQLite searches
the left-most SELECT in the compound for a result column that matches either
the second or third rules above. If a match is found, the search stops and
the expression is handled as an alias for the result column that it has been
matched against. Otherwise, the next SELECT to the right is tried, and so on.)^
^If no matching expression can be found in the result columns of any
constituent SELECT, it is an error. ^Each term of the ORDER BY clause is
processed separately and may be matched against result columns from different
SELECT statements in the compound.</p>

<p>The LIMIT clause is used to place an upper bound on the number of rows

returned by a SELECT statement. ^Any scalar expression may be used in the 
LIMIT clause, so long as it evaluates to an integer or a value that can be
losslessly converted to an integer. ^If the expression evaluates to a NULL 
value or any other value that cannot be losslessly converted to an integer, an
error is returned. ^If the LIMIT expression evaluates to a negative value,
then there is no upper bound on the number of rows returned. ^Otherwise, the
SELECT returns the first N rows of its result set only, where N is the value
that the LIMIT expression evaluates to. ^Or, if the SELECT statement would
return less than N rows without a LIMIT clause, then the entire result set is

returned. 

<p>^The expression attached to the optional OFFSET clause that may follow a
LIMIT clause must also evaluate to an integer, or a value that can be
losslessly converted to an integer. ^If an expression has an OFFSET clause,
then the first M rows are omitted from the result set returned by the SELECT
statement and the next N rows are returned, where M and N are the values that
the OFFSET and LIMIT clauses evaluate to, respectively. ^Or, if the SELECT
would return less than M+N rows if it did not have a LIMIT clause, then the
first M rows are skipped and the remaining rows (if any) are returned. ^If the
OFFSET clause evaluates to a negative value, the results are the same as if it
had evaluated to zero.

<p>^Instead of a separate OFFSET clause, the LIMIT clause may specify two
scalar expressions separated by a comma. ^In this case, the first expression
is used as the OFFSET expression and the second as the LIMIT expression.
This is counter-intuitive, as when using the OFFSET clause the second of
the two expressions is the OFFSET and the first the LIMIT. This is intentional
- it maximizes compatibility with other SQL database systems.

<tcl>
##############################################################################
Section UPDATE update {UPDATE *UPDATEs}

BubbleDiagram update-stmt 1
BubbleDiagram qualified-table-name