Documentation Source Text

Check-in [7b2b285803]
Login

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

Overview
Comment:Updates, clarifications, and typo fixes in the SQL language documentation.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7b2b285803fb5c88917254a42dd818d9d8ac3717
User & Date: drh 2009-08-26 02:00:30.000
Context
2009-08-26
13:13
Tweaks to the testing documentation. (check-in: 3b5097e255 user: drh tags: trunk)
02:00
Updates, clarifications, and typo fixes in the SQL language documentation. (check-in: 7b2b285803 user: drh tags: trunk)
00:29
Updates to the testing.html and different.html documents. (check-in: 197d293511 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/lang.in.
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144

BubbleDiagram alter-table-stmt 1
</tcl>

<p>SQLite supports a limited subset of ALTER TABLE.
The ALTER TABLE command in SQLite allows the user to rename a table
or to add a new column to an existing table. It is not possible
to rename a colum, remove a column, or add or remove constraints from a table.
</p>

<p>The RENAME TO syntax is used to rename the table identified by 
<i>&#91;database-name.&#93;table-name</i> to <i>new-table-name</i>.
This command 
cannot be used to move a table between attached databases, only to rename 
a table within the same database.</p>







|







130
131
132
133
134
135
136
137
138
139
140
141
142
143
144

BubbleDiagram alter-table-stmt 1
</tcl>

<p>SQLite supports a limited subset of ALTER TABLE.
The ALTER TABLE command in SQLite allows the user to rename a table
or to add a new column to an existing table. It is not possible
to rename a column, remove a column, or add or remove constraints from a table.
</p>

<p>The RENAME TO syntax is used to rename the table identified by 
<i>&#91;database-name.&#93;table-name</i> to <i>new-table-name</i>.
This command 
cannot be used to move a table between attached databases, only to rename 
a table within the same database.</p>
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
</p>

<p>
The ROLLBACK will fail with an error code [SQLITE_BUSY] if there
are any pending queries.  Both read-only and read/write queries will
cause a ROLLBACK to fail.  A ROLLBACK must fail if there are pending
read operations (unlike COMMIT which can succeed) because bad things
will happen if memory image of the database is changed out from under
an active query.
</p>

<p>
If [PRAGMA journal_mode] is set to OFF (thus disabling the rollback journal
file) then the behavior of the ROLLBACK command is undefined.
</p>







|







354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
</p>

<p>
The ROLLBACK will fail with an error code [SQLITE_BUSY] if there
are any pending queries.  Both read-only and read/write queries will
cause a ROLLBACK to fail.  A ROLLBACK must fail if there are pending
read operations (unlike COMMIT which can succeed) because bad things
will happen if the in-memory image of the database is changed out from under
an active query.
</p>

<p>
If [PRAGMA journal_mode] is set to OFF (thus disabling the rollback journal
file) then the behavior of the ROLLBACK command is undefined.
</p>
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
will result in an error.  For the purposes of unique indices, all NULL values
are considered to different from all other NULL values and are thus unique.
This is one of the two possible interpretations of the SQL-92 standard
(the language in the standard is ambiguious) and is the interpretation
followed by PostgreSQL, MySQL, Firebird, and Oracle.  Informix and
Microsoft SQL Server follow the other interpretation of the standard.</p>

<p>The text
of each CREATE INDEX statement is stored in the <b>sqlite_master</b>
or <b>sqlite_temp_master</b> table, depending on whether the table
being indexed is temporary.  Every time the database is opened,
all CREATE INDEX statements
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the index layout.</p>

<p>If the optional IF NOT EXISTS clause is present and another index
with the same name aleady exists, then this command becomes a no-op.</p>

<p>Indexes are removed with the <a href="lang_dropindex.html">DROP INDEX</a> 
command.</p>









<
<
<
<
<
<
<
<







588
589
590
591
592
593
594








595
596
597
598
599
600
601
will result in an error.  For the purposes of unique indices, all NULL values
are considered to different from all other NULL values and are thus unique.
This is one of the two possible interpretations of the SQL-92 standard
(the language in the standard is ambiguious) and is the interpretation
followed by PostgreSQL, MySQL, Firebird, and Oracle.  Informix and
Microsoft SQL Server follow the other interpretation of the standard.</p>









<p>If the optional IF NOT EXISTS clause is present and another index
with the same name aleady exists, then this command becomes a no-op.</p>

<p>Indexes are removed with the <a href="lang_dropindex.html">DROP INDEX</a> 
command.</p>


619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642

643
644
645
646
647
648
649
BubbleDiagram foreign-key-clause
</tcl>

<p>A CREATE TABLE statement is basically the keywords "CREATE TABLE"
followed by the name of a new table and a parenthesized list of column
definitions and constraints.  
Tables names that begin with "<b>sqlite_</b>" are reserved
for use by the engine.</p>

<p>Each column definition is the name of the column optionally followed by the
[datatype] for that column, then one or more optional column constraints.
SQLite uses [dynamic typing]; 
the datatype for the column does not restrict what data may be put
in that column.
The UNIQUE constraint causes an unique index to be created on the specified
columns.  All NULL values are considered different from each other and from
all other values for the purpose of determining uniqueness, hence a UNIQUE
column may contain multiple entries with the value of NULL.
The COLLATE clause specifies what text 
[collating function] to use when comparing text entries for the column.  
The built-in [BINARY] collating function is used by default.
<p>
The DEFAULT constraint specifies a default value to use when doing an [INSERT].
The value may be NULL, a string constant or a number.

The default value may also be one of the special case-independant
keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. If the value is
NULL, a string constant or number, it is literally inserted into the column
whenever an INSERT statement that does not specify a value for the column is
executed. If the value is CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then
the current UTC date and/or time is inserted into the columns. For
CURRENT_TIME, the format is HH:MM:SS. For CURRENT_DATE, YYYY-MM-DD. The format







|















|
>







611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
BubbleDiagram foreign-key-clause
</tcl>

<p>A CREATE TABLE statement is basically the keywords "CREATE TABLE"
followed by the name of a new table and a parenthesized list of column
definitions and constraints.  
Tables names that begin with "<b>sqlite_</b>" are reserved
for use by SQLite itself and cannot appears in a CREATE TABLE statement.</p>

<p>Each column definition is the name of the column optionally followed by the
[datatype] for that column, then one or more optional column constraints.
SQLite uses [dynamic typing]; 
the datatype for the column does not restrict what data may be put
in that column.
The UNIQUE constraint causes an unique index to be created on the specified
columns.  All NULL values are considered different from each other and from
all other values for the purpose of determining uniqueness, hence a UNIQUE
column may contain multiple entries with the value of NULL.
The COLLATE clause specifies what text 
[collating function] to use when comparing text entries for the column.  
The built-in [BINARY] collating function is used by default.
<p>
The DEFAULT constraint specifies a default value to use when doing an [INSERT].
The value may be NULL, a string constant, a number, or a constant expression
enclosed in parentheses.
The default value may also be one of the special case-independant
keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. If the value is
NULL, a string constant or number, it is literally inserted into the column
whenever an INSERT statement that does not specify a value for the column is
executed. If the value is CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then
the current UTC date and/or time is inserted into the columns. For
CURRENT_TIME, the format is HH:MM:SS. For CURRENT_DATE, YYYY-MM-DD. The format
686
687
688
689
690
691
692

693
694
695
696

697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
and the TEMP keyword, unless the &lt;database-name&gt; is "temp". If no
database name is specified, and the TEMP keyword is not present,
the table is created in the main database.</p>

<p>The optional [conflict clause] following each constraint
allows the specification of an alternative default
constraint conflict resolution algorithm for that constraint.

The default is abort ABORT.  Different constraints within the same
table may have different default conflict resolution algorithms.
If an [INSERT] or [UPDATE] statement specifies a different conflict
resolution algorithm, then that algorithm is used in place of the

default algorithm specified in the CREATE TABLE statement.
See the section titled
[ON CONFLICT] for additional information.</p>

<p>CHECK constraints are supported as of [version 3.3.0].  Prior
to version 3.3.0, CHECK constraints were parsed but not enforced.</p>

<p>The number of columns in a table is limited by the
[SQLITE_MAX_COLUMN] compile-time parameter.
A single row of a table cannot store more than
[SQLITE_MAX_LENGTH] bytes of data.
Both of these limits can be lowered at runtime using the
[sqlite3_limit()] C/C++ interface.</p>


<p>The CREATE TABLE AS form defines the table to be
the result set of a query.  The names of the table columns are
the names of the columns in the result.</p>

<p>The text
of each CREATE TABLE statement is stored in the <b>sqlite_master</b>
table.  Every time the database is opened, all CREATE TABLE statements
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the table layout.
If the original command was a CREATE TABLE AS then then an equivalent
CREATE TABLE statement is synthesized and store in <b>sqlite_master</b>
in place of the original command.
The text of CREATE TEMPORARY TABLE statements are stored in the
<b>sqlite_temp_master</b> table.
</p>

<p>If the optional IF NOT EXISTS clause is present and another table
with the same name aleady exists, then this command becomes a no-op.</p>

<p>Tables are removed using the <a href="lang_droptable.html">DROP TABLE</a> 
statement.  </p>

<tcl>hd_fragment rowid {INTEGER PRIMARY KEY} ROWID rowid</tcl>







>
|
|
|
|
>
|


















<
<
<
<
<
<
<
<
<
<
<
<







679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710












711
712
713
714
715
716
717
and the TEMP keyword, unless the &lt;database-name&gt; is "temp". If no
database name is specified, and the TEMP keyword is not present,
the table is created in the main database.</p>

<p>The optional [conflict clause] following each constraint
allows the specification of an alternative default
constraint conflict resolution algorithm for that constraint.
If no conflict clause is specified, the ABORT algorithm is used.
Different constraints within the same
table may have different conflict resolution algorithms.
If an [INSERT] or [UPDATE] statement specifies a conflict
resolution algorithm, then the algorithm specified on the INSERT or
UPDATE statement overrides the algorithm specified in the 
CREATE TABLE statement.
See the section titled
[ON CONFLICT] for additional information.</p>

<p>CHECK constraints are supported as of [version 3.3.0].  Prior
to version 3.3.0, CHECK constraints were parsed but not enforced.</p>

<p>The number of columns in a table is limited by the
[SQLITE_MAX_COLUMN] compile-time parameter.
A single row of a table cannot store more than
[SQLITE_MAX_LENGTH] bytes of data.
Both of these limits can be lowered at runtime using the
[sqlite3_limit()] C/C++ interface.</p>


<p>The CREATE TABLE AS form defines the table to be
the result set of a query.  The names of the table columns are
the names of the columns in the result.</p>













<p>If the optional IF NOT EXISTS clause is present and another table
with the same name aleady exists, then this command becomes a no-op.</p>

<p>Tables are removed using the <a href="lang_droptable.html">DROP TABLE</a> 
statement.  </p>

<tcl>hd_fragment rowid {INTEGER PRIMARY KEY} ROWID rowid</tcl>
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
<p>The CREATE TRIGGER statement is used to add triggers to the 
database schema. Triggers are database operations 
that are automatically performed when a specified database event
occurs.  </p>

<p>A trigger may be specified to fire whenever a [DELETE], [INSERT],
or [UPDATE] of a
particular database table occurs, or whenever an [UPDATE] of one or more
specified columns of a table are updated.</p>

<p>At this time SQLite supports only FOR EACH ROW triggers, not FOR EACH
STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is optional.  FOR
EACH ROW implies that the SQL statements specified in the trigger
may be executed (depending on the WHEN clause) for each database row being
inserted, updated or deleted by the statement causing the trigger to fire.</p>








|
|







790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
<p>The CREATE TRIGGER statement is used to add triggers to the 
database schema. Triggers are database operations 
that are automatically performed when a specified database event
occurs.  </p>

<p>A trigger may be specified to fire whenever a [DELETE], [INSERT],
or [UPDATE] of a
particular database table occurs, or whenever an [UPDATE] occurs on
on one or more specified columns of a table.</p>

<p>At this time SQLite supports only FOR EACH ROW triggers, not FOR EACH
STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is optional.  FOR
EACH ROW implies that the SQL statements specified in the trigger
may be executed (depending on the WHEN clause) for each database row being
inserted, updated or deleted by the statement causing the trigger to fire.</p>

1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
<p>A [virtual table] is an interface to an external storage or computation
engine that appears to be a table but does not actually store information
in the database file.</p>

<p>In general, you can do anything with a [virtual table] that can be done
with an ordinary table, except that you cannot create indices or triggers on a
virtual table.  Some virtual table implementations might impose additional
restrictions.  For example, many virtual tables are read-only.
Virtual tables cannot be used in
[sqlite3_enable_shared_cache | shared cache mode].</p>

<p>The &lt;module-name&gt; is the name of an object that implements
the virtual table.  The &lt;module-name&gt; must be registered with
the SQLite database connection using
[sqlite3_create_module()] or [sqlite3_create_module_v2()]
prior to issuing the CREATE VIRTUAL TABLE statement.
The module takes zero or more comma-separated arguments.







|
<
<







1001
1002
1003
1004
1005
1006
1007
1008


1009
1010
1011
1012
1013
1014
1015
<p>A [virtual table] is an interface to an external storage or computation
engine that appears to be a table but does not actually store information
in the database file.</p>

<p>In general, you can do anything with a [virtual table] that can be done
with an ordinary table, except that you cannot create indices or triggers on a
virtual table.  Some virtual table implementations might impose additional
restrictions.  For example, many virtual tables are read-only.</p>



<p>The &lt;module-name&gt; is the name of an object that implements
the virtual table.  The &lt;module-name&gt; must be registered with
the SQLite database connection using
[sqlite3_create_module()] or [sqlite3_create_module_v2()]
prior to issuing the CREATE VIRTUAL TABLE statement.
The module takes zero or more comma-separated arguments.
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
error message.  If a user-defined function named "regexp"
is added at run-time, that function will be called in order
to implement the REGEXP operator.</p>

<tcl>hd_fragment match MATCH</tcl>
<p>The MATCH operator is a special syntax for the match()
user function.  The default match() function implementation
raises and exception and is not really useful for anything.
But extensions can override the match() function with more
helpful logic.</p>

<tcl>hd_fragment between BETWEEN</tcl>
<h3>The BETWEEN operator</h3>
<p>The BETWEEN operator is equivalent to a pair of comparisons.
"<i>a</i> <b>BETWEEN</b> <i>b</i> <b>AND</b> <i>c</i>" is 
equivalent to 
"<i>a</i><b>&gt;=</b><i>b</i> <b>AND</b> <i>a</i><b>&lt;=</b><i>c</i>".
The precedence of the BETWEEN operator is the same as the precedence
as operators <b>==</b> and <b>!=</b> and <b>LIKE</b> and groups left to right.

<h3>Table Column Names</h3>
<p>A column name can be any of the names defined in the [CREATE TABLE]
statement or one of the following special identifiers: "<b>ROWID</b>",
"<b>OID</b>", or "<b>_ROWID_</b>".
These special identifiers all describe the
unique integer key (the [rowid]) associated with every 
row of every table.
The special identifiers only refer to the row key if the [CREATE TABLE]
statement does not define a real column with the same name.  Row keys
act like read-only columns.  A row key can be used anywhere a regular
column can be used, except that you cannot change the value
of a row key in an [UPDATE] or [INSERT] statement.
"SELECT * ..." does not return the row key.</p>

<h3>Subqueries</h3>
<p>[SELECT] statements can appear in expressions as either the
right-hand operand of the IN operator, as a scalar quantity, or
as the operand of an EXISTS operator.
As a scalar quantity or the operand of an IN operator,
the SELECT should have only a single column in its
result.  Compound SELECTs (connected with keywords like UNION or
EXCEPT) are allowed.
With the EXISTS operator, the columns in the result set of the [SELECT] are
ignored and the expression returns TRUE if one or more rows exist
and FALSE if the result set is empty.
If no terms in the [SELECT] expression refer to value in the containing
query, then the expression is evaluated once prior to any other
processing and the result is reused as necessary.  If the [SELECT] expression
does contain variables from the outer query, then the [SELECT] is reevaluated
every time it is needed.</p>

<p>When a SELECT is the right operand of the IN operator, the IN
operator returns TRUE if the result of the left operand is any of







|




















|
|
|
<
<












|







1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426


1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
error message.  If a user-defined function named "regexp"
is added at run-time, that function will be called in order
to implement the REGEXP operator.</p>

<tcl>hd_fragment match MATCH</tcl>
<p>The MATCH operator is a special syntax for the match()
user function.  The default match() function implementation
raises an exception and is not really useful for anything.
But extensions can override the match() function with more
helpful logic.</p>

<tcl>hd_fragment between BETWEEN</tcl>
<h3>The BETWEEN operator</h3>
<p>The BETWEEN operator is equivalent to a pair of comparisons.
"<i>a</i> <b>BETWEEN</b> <i>b</i> <b>AND</b> <i>c</i>" is 
equivalent to 
"<i>a</i><b>&gt;=</b><i>b</i> <b>AND</b> <i>a</i><b>&lt;=</b><i>c</i>".
The precedence of the BETWEEN operator is the same as the precedence
as operators <b>==</b> and <b>!=</b> and <b>LIKE</b> and groups left to right.

<h3>Table Column Names</h3>
<p>A column name can be any of the names defined in the [CREATE TABLE]
statement or one of the following special identifiers: "<b>ROWID</b>",
"<b>OID</b>", or "<b>_ROWID_</b>".
These special identifiers all describe the
unique integer key (the [rowid]) associated with every 
row of every table.
The special identifiers only refer to the row key if the [CREATE TABLE]
statement does not define a real column with the same name.
The rowid can be used anywhere a regular
column can be used.</p>



<h3>Subqueries</h3>
<p>[SELECT] statements can appear in expressions as either the
right-hand operand of the IN operator, as a scalar quantity, or
as the operand of an EXISTS operator.
As a scalar quantity or the operand of an IN operator,
the SELECT should have only a single column in its
result.  Compound SELECTs (connected with keywords like UNION or
EXCEPT) are allowed.
With the EXISTS operator, the columns in the result set of the [SELECT] are
ignored and the expression returns TRUE if one or more rows exist
and FALSE if the result set is empty.
If no terms in the [SELECT] expression refer to values in the containing
query, then the expression is evaluated once prior to any other
processing and the result is reused as necessary.  If the [SELECT] expression
does contain variables from the outer query, then the [SELECT] is reevaluated
every time it is needed.</p>

<p>When a SELECT is the right operand of the IN operator, the IN
operator returns TRUE if the result of the left operand is any of
1778
1779
1780
1781
1782
1783
1784



1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
<p>
All five functions take a time string as an argument. The time string
is followed by zero or more modifiers. 
The strftime() function also takes a format string as its first argument.
</p>

<p>



The date() function returns the date in this format: YYYY-MM-DD. 
The time() function returns the time as HH:MM:SS. 
The datetime() function returns "YYYY-MM-DD HH:MM:SS". 
The julianday() function returns the 
<a href="http://en.wikipedia.org/wiki/Julian_day">Julian day</a> - 
number of days since noon in Greenwich on November 24, 4714 B.C. 
(<a href="http://en.wikipedia.org/wiki/Proleptic_Gregorian_calendar">Proleptic
Gregorian calendar</a>). 
The strftime() routine returns the date formatted according to 
the format string specified as the first argument.
The format string supports the most common substitutions found in the 
<a href="http://opengroup.org/onlinepubs/007908799/xsh/strftime.html">







>
>
>




|







1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
<p>
All five functions take a time string as an argument. The time string
is followed by zero or more modifiers. 
The strftime() function also takes a format string as its first argument.
</p>

<p>
The date and time functions use a subset of
<a href="http://en.wikipedia.org/wiki/IOS_8601">IS0-8601</a> date and time
formats.
The date() function returns the date in this format: YYYY-MM-DD. 
The time() function returns the time as HH:MM:SS. 
The datetime() function returns "YYYY-MM-DD HH:MM:SS". 
The julianday() function returns the 
<a href="http://en.wikipedia.org/wiki/Julian_day">Julian day</a> - the
number of days since noon in Greenwich on November 24, 4714 B.C. 
(<a href="http://en.wikipedia.org/wiki/Proleptic_Gregorian_calendar">Proleptic
Gregorian calendar</a>). 
The strftime() routine returns the date formatted according to 
the format string specified as the first argument.
The format string supports the most common substitutions found in the 
<a href="http://opengroup.org/onlinepubs/007908799/xsh/strftime.html">
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065

2066
2067
2068
2069
2070
2071
2072
<tcl>
funcdef {avg(X)} {} {
  Return the average value of all non-NULL <i>X</i> within a
  group.  String and BLOB values that do not look like numbers are
  interpreted as 0.
  The result of avg() is always a floating point value as long as
  at there is at least one non-NULL input even if all
  inputs are integers.  The result of avg() NULL if and only if
  there are no non-NULL inputs.  
}

funcdef {count(X) count(*)} {} {
  The first form return a count of the number of times
  that <i>X</i> is not NULL in a group.  The second form (with no argument)
  returns the total number of rows in the group.
}

funcdef {group_concat(X) group_concat(X,Y)} {} {
  The result is a string which is the concatenation of
  all non-NULL values of <i>X</i>.  If parameter <i>Y</i> is the separator

  between instances of <i>X</i>.  A comma (",") is used as the separator
  if <i>Y</i> is omitted.  The order of the concatenated elements is
  arbitrary.
}

funcdef {max(X)} {maxAggFunc agg_max} {
  Return the maximum value of all values in the group.







|











|
>







2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
<tcl>
funcdef {avg(X)} {} {
  Return the average value of all non-NULL <i>X</i> within a
  group.  String and BLOB values that do not look like numbers are
  interpreted as 0.
  The result of avg() is always a floating point value as long as
  at there is at least one non-NULL input even if all
  inputs are integers.  The result of avg() is NULL if and only if
  there are no non-NULL inputs.  
}

funcdef {count(X) count(*)} {} {
  The first form return a count of the number of times
  that <i>X</i> is not NULL in a group.  The second form (with no argument)
  returns the total number of rows in the group.
}

funcdef {group_concat(X) group_concat(X,Y)} {} {
  The result is a string which is the concatenation of
  all non-NULL values of <i>X</i>.  If parameter <i>Y</i> is present then
  is is used as the separator
  between instances of <i>X</i>.  A comma (",") is used as the separator
  if <i>Y</i> is omitted.  The order of the concatenated elements is
  arbitrary.
}

funcdef {max(X)} {maxAggFunc agg_max} {
  Return the maximum value of all values in the group.
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
  If any input to sum() is neither an integer or a NULL
  then sum() returns a floating point value
  which might be an approximation to the true sum.</p>

  <p>Sum() will throw an "integer overflow" exception if all inputs
  are integers or NULL
  and an integer overflow occurs at any point during the computation.
  Total() never throws and integer overflow.
}
</tcl>
</table>

<tcl>
##############################################################################
Section INSERT insert {INSERT INSERTs}







|







2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
  If any input to sum() is neither an integer or a NULL
  then sum() returns a floating point value
  which might be an approximation to the true sum.</p>

  <p>Sum() will throw an "integer overflow" exception if all inputs
  are integers or NULL
  and an integer overflow occurs at any point during the computation.
  Total() never throws an integer overflow.
}
</tcl>
</table>

<tcl>
##############################################################################
Section INSERT insert {INSERT INSERTs}