You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by sv...@apache.org on 2016/01/14 19:18:36 UTC
[32/42] incubator-trafodion git commit: Completd the port of the SQL
Reference Manual.
Completd the port of the SQL Reference Manual.
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/d16c4a1a
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/d16c4a1a
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/d16c4a1a
Branch: refs/heads/master
Commit: d16c4a1a798811ded8d3cabcc16719b8fafda400
Parents: 9bcd0e2
Author: Gunnar Tapper <gt...@esgyn.local>
Authored: Sun Jan 10 16:02:06 2016 -0700
Committer: Gunnar Tapper <gt...@esgyn.local>
Committed: Sun Jan 10 16:02:06 2016 -0700
----------------------------------------------------------------------
.../src/asciidoc/_chapters/cqds.adoc | 349 +++++
.../src/asciidoc/_chapters/limits.adoc | 37 +
.../src/asciidoc/_chapters/olap_functions.adoc | 1079 ++++++++++++++
.../src/asciidoc/_chapters/reserved_words.adoc | 287 ++++
.../src/asciidoc/_chapters/runtime_stats.adoc | 1354 ++++++++++++++++++
docs/sql_reference/src/asciidoc/index.adoc | 6 +-
6 files changed, 3111 insertions(+), 1 deletion(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/d16c4a1a/docs/sql_reference/src/asciidoc/_chapters/cqds.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/cqds.adoc b/docs/sql_reference/src/asciidoc/_chapters/cqds.adoc
new file mode 100644
index 0000000..ab7afe7
--- /dev/null
+++ b/docs/sql_reference/src/asciidoc/_chapters/cqds.adoc
@@ -0,0 +1,349 @@
+////
+/**
+* @@@ START COPYRIGHT @@@
+*
+* Licensed to the Apache Software Foundation (ASF) under one
+* or more contributor license agreements. See the NOTICE file
+* distributed with this work for additional information
+* regarding copyright ownership. The ASF licenses this file
+* to you under the Apache License, Version 2.0 (the
+* "License"); you may not use this file except in compliance
+* with the License. You may obtain a copy of the License at
+*
+* http://www.apache.org/licenses/LICENSE-2.0
+*
+* Unless required by applicable law or agreed to in writing,
+* software distributed under the License is distributed on an
+* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+* KIND, either express or implied. See the License for the
+* specific language governing permissions and limitations
+* under the License.
+*
+* @@@ END COPYRIGHT @@@
+ */
+////
+
+<<<
+<<<
+[[cqd_attributes]]
+= Control Query Default (CQD) Attributes
+
+This section describes CQDs that are used to override system-level
+default settings.
+
+[[hbase_environment_cqds]]
+== HBase Environment CQDs
+
+Defines the HBase interface.
+
+[[cqd_hbase_interface]]
+=== HBASE_INTERFACE
+
+[cols="25%h,75%"]
+|===
+| Category | HBase
+| Description | Interface to use to access HBase.
+| Values | Specify one of these values: +
+ +
+- JNI to use a JNI interface +
+- JNI_TRX to use a transactional interface with HBase-trx via JNI. +
+ +
+ The default value is JNI_TRX.
+|===
+
+[[hive_environment_cqds]]
+== Hive Environment CQDs
+
+[[hive_max_string_length]]
+=== HIVE_MAX_STRING_LENGTH
+
+[cols="25%h,75%"]
+|===
+| Category | Hive
+| Description | Maximum supported string length for the _string_ data type in Hive. All
+string columns in Hive tables get converted to VARCHAR(_n_ BYTES)
+CHARACTER SET UTF8, with _n_ being the value of this CQD.
+| Values | The default value is 32000.
+|===
+
+<<<
+[[managing_histograms]]
+== Managing Histograms
+
+[[cache_histograms_refresh_interval]]
+=== CACHE_HISTOGRAMS_REFRESH_INTERVAL
+
+[cols="25%h,75%"]
+|===
+| Category | Historgrams
+| Description | Defines the time interval after which timestamps for cached histograms
+are checked to be refreshed.
+| Values | Unsigned integer. Unit is seconds. +
+ +
+The default value is '3600' (1 hour).
+| Usage | Histogram statistics are cached so that the compiler can avoid access to
+the metadata tables, thereby reducing compile times. The timestamp of
+the tables are checked against those of the cached histograms at an
+interval specified by this CQD, in order to see if the cached histograms
+need to be refreshed. +
+ +
+You can increase the interval to reduce the impact on compile times as
+long as you do not need to obtain fresh statistics more frequently in
+order to improve query performance. It may
+ +
+be that the default interval is too long and you would rather refresh
+the statistics more frequently than the default one hour, in order to
+improve query performance at the cost of increased compile times.
+ +
+This setting depends on how frequently you are updating statistics on
+tables. There is no point in refreshing statistics frequently when
+statistics are not being updated during that time. On the other hand if
+you are updating statistics, or generating them for the first time on
+freshly loaded tables frequently enough, and you want these to be picked
+up immediately by the compiler because you have seen this to have a
+dramatic impact on plan quality, then you can make the refresh more
+frequent.
+| Production Usage | Not applicable.
+| Impact | Longer histogram refresh intervals can improve compile times. However,
+the longer the refresh interval the more obsolete the histograms. That
+could result in poor performance for queries that could leverage
+recently updated statistics.
+| Level | System or Service Not applicable
+| Addressing the Real Problem | Not applicable.
+|===
+
+<<<
+[[hist_no_stats_refresh_interval]]
+=== HIST_NO_STATS_REFRESH_INTERVAL
+
+[cols="25%h,75%"]
+|===
+| Category | Historgrams
+| Description | Defines the time interval after which the fake histograms in the cache should be refreshed unconditionally.
+| Values | Integer. Unit is seconds. +
+ +
+The default value is '3600' (1 hour).
+| Usage | Histogram statistics are "fake" when update statistics is not being run,
+but instead the customer is updating the histogram tables directly with
+statistics to guide the optimizer. This may be done if the data in the
+table is very volatile (such as for temporary tables), update statistics
+is not possible because of constant flush and fill of the table
+occurring, and statistics are manually set to provide some guidance to
+the optimizer to generate a good plan.
+ +
+If these fake statistics are updated constantly to reflect the data
+churn, this default can be set to 0. This would ensure that the
+histograms with fake statistics are not cached, and are always
+refreshed. If these fake statistics are set and not touched again, then
+this interval could be set very high.
+| Production Usage | Not applicable.
+| Impact | Setting a high interval improves compilation time. However, if statistics are being updated, the compiler may be working with obsolete
+histogram statistics, potentially resulting in poorer plans.
+| Level | Service.
+| Conflicts/Synergies | Not applicable.
+| Addressing the Real Problem | Not applicable.
+|===
+
+[[hist_prefetch]]
+=== HIST_PREFETCH
+
+[cols="25%h,75%"]
+|===
+| Category | Historgrams
+| Description | Influences the compiler to pre-fetch the histograms and save them in cache.
+| Values | 'ON' Pre-fetches the histograms. +
+'OFF' Does not pre-fetch the histograms. +
+ +
+ The default value is 'ON'.
+| Usage | You may want to turn this off if you don't want to pre-fetch a large number of histograms, many of which may not be used.
+| Production Usage | Not applicable.
+| Impact | Though it makes compilation time faster, it may result in the histogram cache to be filled with histograms that may never be used.
+| Level | System or Service.
+| Conflicts/Synergies | Use this CQD with CACHE_HISTOGRAMS. If CACHE_HISTOGRAMS is OFF, then this CQD has no effect.
+| Addressing the Real Problem | Not applicable.
+|===
+
+[[hist_rowcount_requiring_stats]]
+=== HIST_ROWCOUNT_REQUIRING_STATS
+
+[cols="25%h,75%"]
+|===
+| Category | Historgrams
+| Description | Specifies the minimum row count for which the optimizer needs histograms, in order to compute better cardinality estimates. The
+optimizer does not issue any missing statistics warnings for tables whose size is smaller than the value of this CQD.
+| Values | Integer. +
+ +
+The default value is '50000'.
+| Usage | Use this CQD to reduce the number of statistics warnings.
+| Production Usage | Not applicable.
+| Impact | Missing statistics warnings are not displayed for smaller tables, which in most cases don't impact plan quality much.
+However, there may be some exceptions where missing statistics on small tables could result in less than optimal plans.
+| Level | System
+| Conflicts/Synergies | Use this CQD with HIST_MISSING_STATS_WARNING_LEVEL. If the warning level CQD is 0, then this CQD does not have any effect. Also, for tables
+having fewer rows than set in this CQD, no warnings are displayed irrespective of the warning level.
+| Addressing the Real Problem | Not applicable.
+|===
+
+<<<
+[[optimizer]]
+== Optimizer
+
+[[join_order_by_user]]
+=== JOIN_ORDER_BY_USER
+
+[cols="25%h,75%"]
+|===
+| Category | Influencing Query Plans
+| Description | Enables or disables the join order in which the optimizer joins the tables to be the sequence of the tables in the FROM clause of the query.
+| Values | 'ON' Join order is forced. +
+ +
+'OFF' Join order is decided by the optimizer. +
+ +
+The default value is 'OFF'.
+| Usage | When set to ON, the optimizer considers only execution plans that have the join order matching the sequence of the tables in the FROM clause.
+| Production Usage | This setting is to be used only for forcing a desired join order that was not generated by default by the optimizer. It can be used as a
+workaround for query plans with inefficient join order.
+| Impact | Because you are in effect forcing the optimizer to use a plan that joins the table in the order specified in the FROM clause,
+the plan generated may not be the optimal one.
+| Level | Query
+| Conflicts/Synergies | Not applicable.
+| Addressing the Real Problem | Not applicable.
+|===
+
+[[mdam_scan_method]]
+=== MDAM_SCAN_METHOD
+
+[cols="25%h,75%"]
+|===
+| Category | Influencing Query Plans
+| Description | Enables or disables the Multi-Dimensional Access Method.
+| Values | 'ON' MDAM is considered. +
+'OFF' MDAM is disabled. +
+ +
+The default value is 'ON'.
+| Usage | In certain situations, the optimizer might choose MDAM inappropriately, causing poor performance.
+In such situations you may want to turn MDAM OFF for the query it is effecting.
+| Production Usage | Not applicable.
+| Impact | Table scans with predicates on non-leading clustering key column(s) could benefit from MDAM access method if
+the leading column(s) has a small number of distinct values. Turning MDAM off results in a longer scan time for such queries.
+| Level | Set this CQD at the query level when MDAM is not working efficiently for a specific query.
+However, there may be cases (usually a defect) where a larger set of queries is being negatively impacted by MDAM.
+In those cases you may want to set it at the service or system level.
+| Conflicts/Synergies | Not applicable.
+| Addressing the Real Problem | Not applicable.
+|===
+
+[[subquery_unnesting]]
+=== SUBQUERY_UNNESTING
+
+[cols="25%h,75%"]
+|===
+| Category | Influencing Query Plans
+| Description | Controls the optimizer's ability to transform nested sub-queries into regular join trees.
+| Values | 'ON' Subquery un-nesting is considered. +
+'OFF' Subquery un-nesting is disabled. +
+ +
+The default value is 'ON'.
+| Usage | Use this control to disable subquery un-nesting in the rare situation when un-nesting results in an inefficient query execution plan.
+| Production usage | Not applicable.
+| Impact | In general, subquery un-nesting results in more efficient execution plans for queries with nested sub-queries.
+Use only as a workaround for observed problems due to un-nesting.
+| Level | Query
+| Conflicts/Synergies | Not applicable.
+| Addressing the Real Problem | Not applicable.
+|===
+
+<<<
+[[managing_schemas]]
+== Managing Schemas
+
+[[schema]]
+=== SCHEMA
+
+[cols="25%h,75%"]
+|===
+| Category | Schema controls
+| Description | Sets the default schema for the session.
+| Values | SQL identifier. +
+ +
+The default is SEABASE.
+| Usage | A SET SCHEMA statement, or a CONTROL QUERY DEFAULT SCHEMA statement, can be used to override the default schema name.
+| Production Usage | It is a convenience so you do not have to type in two-part names.
+| Impact | Not applicable.
+| Level | Any.
+| Conflicts/Synergies | Alternately you can use the SET SCHEMA statement.
+| Addressing the Real Problem | Not applicable.
+|===
+
+<<<
+[[transaction_control_and_locking]]
+== Transaction Control and Locking
+
+[[block_to_prevent_halloween]]
+=== BLOCK_TO_PREVENT_HALLOWEEN
+
+[cols="25%h,75%"]
+|===
+| Category | Runtime controls
+| Description | A self-referencing insert is one which inserts into a target table and
+also scans from the same target table as part of the query that produces
+rows to be inserted. Inconsistent results are produced by the insert
+statement if the statement scans rows which have been inserted by the
+same statement. This is sometimes called the "Halloween problem."
+Trafodion prevents the Halloween problem using one of two methods: 1)
+the blocking method uses a SORT operation to ensure all rows have been
+scanned before any are inserted, or 2) the disk process (ESAM) locks
+method tracks the rows which have already been inserted and the SCAN
+operator skips these rows. +
+ +
+The compiler chooses the blocking method in cases in which static
+analysis of the plan indicates that the disk process locks method cannot
+be used. However, the compiler does not evaluate one condition that
+would prevent the use of the disk process locks method: the AUTOCOMMIT
+setting in which the statement is executed. Instead the compiler assumes
+that the statement is executed with the default setting for AUTOCOMMIT,
+'ON'. If AUTOCOMMIT is set to 'OFF' and self-referencing insert
+statement which uses the disk process locks method is executed, then a
+runtime error (SQLCODE 8107) is raised. +
+ +
+This CQD is used to force the compiler to use the blocking method to
+prevent error 8107.
+| Values | 'OFF' The compiler is free to choose which method to use to prevent the Halloween problem. +
+'ON' The compiler is forced to use the blocking method. +
+ +
+The default value is 'ON'.
+| Usage | Change this default to 'ON' if error 8107 is raised for a self-referencing insert statement which is
+executed in a session with AUTOCOMMIT set to 'OFF'.
+| Production Usage | Not applicable.
+| Impact | Using the 'ON' value in conditions that require it allows successful completion of the insert statement.
+Using the 'ON' value when not required can decrease performance of some self-referencing insert statements.
+| Level | If self-referencing insert statements which execute with AUTOCOMMIT 'OFF' can be restricted to a service level,
+then this default should be set to 'ON' only for that service level. Otherwise the setting should be made for the system.
+| Conflicts/Synergies | Not applicable.
+| Addressing the Real Problem | Not applicable.
+|===
+
+<<<
+[[upd_ordered]]
+=== UPD_ORDERED
+
+[cols="25%h,75%"]
+|===
+| Category | Influencing Query Plans
+| Description | Controls whether rows should be inserted, updated, or deleted in clustering key order.
+| Values | 'ON' The optimizer generates and considers plans where the rows are inserted, updated, or deleted in clustering key order. +
+'OFF' The optimizer does not generate plans where the rows must be inserted, updated, or deleted in clustering key order.
+ +
+The default value is 'ON'.
+| Usage | Inserting, updating or deleting rows in the clustering key order is most efficient and highly recommended. Turning this CQD OFF may result in
+saving the data sorting cost but at the expense of having less efficient random I/O Insert/Update/Delete operations. +
+ +
+If you know that the data is already sorted in clustering key order, or is mostly in clustering key order,
+so that it would not result in random I/O, you could set this CQD to OFF.
+| Production Usage | Not applicable.
+| Impact | If turned OFF, the system may perform large number of inefficient Random I/Os when performing Insert/Update/Delete operations.
+| Level | Query
+| Conflicts/Synergies | Not applicable.
+| Addressing the Real Problem | Not applicable.
+|===
+
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/d16c4a1a/docs/sql_reference/src/asciidoc/_chapters/limits.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/limits.adoc b/docs/sql_reference/src/asciidoc/_chapters/limits.adoc
new file mode 100644
index 0000000..27af6d0
--- /dev/null
+++ b/docs/sql_reference/src/asciidoc/_chapters/limits.adoc
@@ -0,0 +1,37 @@
+////
+/**
+* @@@ START COPYRIGHT @@@
+*
+* Licensed to the Apache Software Foundation (ASF) under one
+* or more contributor license agreements. See the NOTICE file
+* distributed with this work for additional information
+* regarding copyright ownership. The ASF licenses this file
+* to you under the Apache License, Version 2.0 (the
+* "License"); you may not use this file except in compliance
+* with the License. You may obtain a copy of the License at
+*
+* http://www.apache.org/licenses/LICENSE-2.0
+*
+* Unless required by applicable law or agreed to in writing,
+* software distributed under the License is distributed on an
+* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+* KIND, either express or implied. See the License for the
+* specific language governing permissions and limitations
+* under the License.
+*
+* @@@ END COPYRIGHT @@@
+ */
+////
+
+[[limits]]
+= Limits
+
+This section lists limits for various parts of Trafodion SQL.
+
+[cols="30%h,70%"]
+|===
+| Column Names | Up to 128 characters long, or 256 bytes of UTF8 text, whichever is less.
+| Schema Names | Up to 128 characters long, or 256 bytes of UTF8 text, whichever is less.
+| Table Names | ANSI names are of the form _schema.object_, where each part can be up to 128 characters long,
+or 256 bytes of UTF8 text, whichever is less.
+|===
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/d16c4a1a/docs/sql_reference/src/asciidoc/_chapters/olap_functions.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/olap_functions.adoc b/docs/sql_reference/src/asciidoc/_chapters/olap_functions.adoc
new file mode 100644
index 0000000..32a1efd
--- /dev/null
+++ b/docs/sql_reference/src/asciidoc/_chapters/olap_functions.adoc
@@ -0,0 +1,1079 @@
+////
+/**
+* @@@ START COPYRIGHT @@@
+*
+* Licensed to the Apache Software Foundation (ASF) under one
+* or more contributor license agreements. See the NOTICE file
+* distributed with this work for additional information
+* regarding copyright ownership. The ASF licenses this file
+* to you under the Apache License, Version 2.0 (the
+* "License"); you may not use this file except in compliance
+* with the License. You may obtain a copy of the License at
+*
+* http://www.apache.org/licenses/LICENSE-2.0
+*
+* Unless required by applicable law or agreed to in writing,
+* software distributed under the License is distributed on an
+* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+* KIND, either express or implied. See the License for the
+* specific language governing permissions and limitations
+* under the License.
+*
+* @@@ END COPYRIGHT @@@
+ */
+////
+
+<<<
+[[olap_functions]]
+=== OLAP Functions
+
+This section describes the syntax and semantics of the On Line
+Analytical Process (OLAP) window functions. The OLAP window functions
+are ANSI compliant.
+
+[[considerations_for_window_functions]]
+=== Considerations for Window Functions
+
+These considerations apply to all window functions.
+
+* `_inline-window-specification_`
++
+The window defined by the _inline-window-specification_ consists of the
+rows specified by the _window-frame-clause_, bounded by the current
+partition. If no PARTITION BY clause is specified, the partition is
+defined to be all the rows of the intermediate result. If a PARTITION BY
+clause is specified, the partition is the set of rows which have the
+same values for the expressions specified in the PARTITION clause.
+
+* `_window-frame-clause_`
++
+DISTINCT is not supported for window functions.
++
+Use of a FOLLOWING term is not supported. Using a FOLLOWING term results
+in an error.
++
+If no _window-frame-clause_ is specified, "ROWS BETWEEN UNBOUNDED
+PRECEDING AND UNBOUNDED FOLLOWING" is assumed. This clause is not
+supported because it involves a FOLLOWING term and will result in an
+error.
++
+"ROWS CURRENT ROW" is equivalent to "ROWS BETWEEN CURRENT ROW AND
+CURRENT ROW".
++
+"ROWS _preceding-row_" is equivalent to "ROWS BETWEEN _preceding-row_
+AND CURRENT ROW".
+
+==== Nulls
+
+All nulls are eliminated before the function is applied to the set of
+values. If the window contains all NULL values, the result of the window
+function is NULL.
+
+If the specified window for a particular row consists of rows that are
+all before the first row of the partition (no rows in the window), the
+result of the window function is NULL.
+
+<<<
+[[order_by_clause_supports_expressions_for_olap_functions]]
+== ORDER BY Clause Supports Expressions For OLAP Functions
+
+The ORDER BY clause of the OLAP functions now supports expressions.
+However, use of multiple OLAP functions with different expressions in
+the same query is not supported. The following examples show how
+expressions may be used in the ORDER BY clause.
+
+```
+SELECT
+ -1 * annualsalary neg_total
+, RANK() OVER (ORDER BY -1 * annualsalary) olap_rank
+FROM employee;
+```
+
+Using an aggregate in the ORDER BY clause:
+
+```
+SELECT
+ num
+, RANK() OVER (ORDER BY SUM(annualsalary)) olap_rank
+FROM employee
+GROUP BY num;
+```
+
+Using multiple functions with the same expression in the ORDER BY clause:
+
+```
+SELECT
+ num
+, workgroupnum
+, RANK() OVER (ORDER BY SUM (annualsalary)*num) olap_rank
+, DENSE_RANK() OVER (ORDER BY SUM (annualsalary)*num) olap_drank
+, ROW_NUMBER() OVER (ORDER BY SUM (annualsalary)*num) olap_mum
+FROM employee
+GROUP BY num, workgroupnum, annualsalary;
+```
+
+Using more functions with the same expression in the ORDER BY clause:
+
+```
+SELECT
+ num
+, workgroupnum
+, annualsalary
+, SUM(AnnualSalary) OVER (ORDER BY SUM(annualsalary)*num ROWS UNBOUNDED PRECEDING)
+, AVG(AnnualSalary) OVER (ORDER BY SUM(annualsalary)*num ROWS UNBOUNDED PRECEDING)
+, MIN(AnnualSalary) OVER (ORDER BY SUM(annualsalary)*num ROWS UNBOUNDED PRECEDING)
+, MAX(AnnualSalary) OVER (ORDER BY SUM(annualsalary)*num ROWS UNBOUNDED PRECEDING)
+, VARIANCE(AnnualSalary) OVER (ORDER BY SUM(annualsalary)*num ROWS UNBOUNDED PRECEDING)
+, STDDEV(AnnualSalary) OVER (ORDER BY SUM(annualsalary)*num ROWS UNBOUNDED PRECEDING)
+, COUNT(AnnualSalary) OVER (ORDER BY SUM(annualsalary)*num ROWS UNBOUNDED PRECEDING)
+FROM employee
+GROUP BY num, workgroupnum, annualsalary;
+```
+
+<<<
+[[limitations_for_window_functions]]
+== Limitations for Window Functions
+
+These limitations apply to all window functions.
+
+* The ANSI _window-clause_ is not supported by Trafodion. Only the
+_inline-window-specification_ is supported. An attempt to use an ANSI
+_window-clause_ will result in a syntax error.
+
+* The _window-frame-clause_ cannot contain a FOLLOWING term, either
+explicitly or implicitly. Because the default window frame clause
+contains an implicit FOLLOWING ("ROWS BETWEEN UNBOUNDED PRECEDING AND
+UNBOUNDED FOLLOWING"), the default is not supported. So, practically,
+the _window-frame-clause_ is not optional. An attempt to use a FOLLOWING
+term, either explicitly or implicitly will result in the "4343" error
+message.
+
+* The window frame units can only be ROWS. RANGE is not supported by
+Trafodion. An attempt to use RANGE will result in a syntax error.
+
+* The ANSI _window-frame-exclusion-specification_ is not supported by
+Trafodion. An attempt to use a _window-frame-exclusion-specification_
+will result in a syntax error.
+
+* Multiple _inline-window-specifications_ in a single SELECT clause are
+not supported. For each window function within a SELECT clause, the
+ORDER BY clause and PARTITION BY specifications must be identical. The
+window frame can vary within a SELECT clause. An attempt to use multiple
+_inline-window-specifications_ in a single SELECT clause will result in
+the "4340" error message.
+
+* The ANSI _null-ordering-specification_ within the ORDER BY clause is
+not supported by Trafodion. Null values will always be sorted as if they
+are greater than all non-null values. This is slightly different than a
+null ordering of NULLS LAST. An attempt to use a
+_null-ordering-specification_ will result in a syntax error.
+
+* The ANSI _filter-clause_ is not supported for window functions by
+Trafodion. The _filter-clause_ applies to all aggregate functions
+(grouped and windowed) and that the _filter-clause_ is not currently
+supported for grouped aggregate functions. An attempt to use a
+_filter-clause_ will result in a syntax error.
+
+* The DISTINCT value for the _set-qualifier-clause_ within a window
+function is not supported. Only the ALL value is supported for the
+_set-qualifier-clause_ within a window function. An attempt to use
+DISTINCT in a window function will result in the "4341" error message.
+
+<<<
+[[avg_window_function]]
+== AVG Window Function
+
+AVG is a window function that returns the average of nonnull values of
+the given expression for the current window specified by the
+_inline-window specification_.
+
+```
+AVG ([ALL] expression) OVER (inline-window-specification)
+```
+
+* `_inline-window-specification_` is:
++
+```
+[PARTITION BY expression [, expression]...]
+[ORDER BY expression [ASC[ENDING] | DESC[ENDING]]
+ [,expression [ASC[ENDING] | DESC[ENDING]]]...]
+[ window-frame-clause ]
+```
+
+* `_window-frame-clause_` is:
++
+```
+ ROWS CURRENT ROW
+| ROWS preceding-row
+| ROWS BETWEEN preceding-row AND preceding-row
+| ROWS BETWEEN preceding-row AND CURRENT ROW
+| ROWS BETWEEN preceding-row AND following-row
+| ROWS BETWEEN CURRENT ROW AND CURRENT ROW
+| ROWS BETWEEN CURRENT ROW AND following-row
+| ROWS BETWEEN following-row AND following-row
+```
+
+* `_preceding-row_` is:
++
+```
+ UNBOUNDED PRECEDING
+| unsigned-integer PRECEDING
+```
+
+* `_following-row_` is:
++
+```
+ UNBOUNDED FOLLOWING
+| unsigned-integer FOLLOWING
+```
+
+<<<
+* `ALL`
++
+specifies whether duplicate values are included in the computation of
+the AVG of the _expression_. The default option is ALL, which causes
+duplicate values to be included.
+
+* `_expression_`
++
+specifies a numeric or interval value _expression_ that determines the
+values to average. See <<numeric_value_expressions,Numeric Value Expressions>>
+and <<interval_value_expressions,Interval Value Expressions>>.
+
+* `_inline-window-specification_`
++
+specifies_the_window_over_which_the_avg_is_computed. The
+_inline-window-specification_ can contain an optional partition by
+clause, an optional ORDER BY clause and an optional window frame clause.
+The PARTITION BY clause specifies how the intermediate result is
+partitioned and the ORDER BY clause specifies how the rows are ordered
+within each partition.
+
+* `_window-frame-clause_`
++
+specifies the window within the partition over which the AVG is
+computed.
+
+<<<
+[[examples_of_avg_window_function]]
+=== Examples of AVG Window Function
+
+* Return the running average value of the SALARY column:
++
+```
+SELECT
+ empnum
+, AVG(salary) OVER (ORDER BY empnum ROWS UNBOUNDED PRECEDING)
+FROM persnl.employee;
+```
+
+* Return the running average value of the SALARY column within each
+department:
++
+```
+SELECT
+ deptnum
+, empnum
+, AVG(salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS UNBOUNDED PRECEDING)
+FROM persnl.employee;
+```
+
+* Return the moving average of salary within each department over a
+window of the last 4 rows:
++
+```
+SELECT
+ deptnum
+, empnum
+, AVG(SALARY) OVER (PARTITION BY deptnum ORDER BY empnum ROWS 3 PRECEDING)
+FROM persnl.employee;
+```
+
+<<<
+[[count_window_function]]
+== COUNT Window Function
+
+COUNT is a window function that returns the count of the non null values
+of the given expression for the current window specified by the
+inline-window-specification.
+
+```
+COUNT {(*) | ([ALL] expression) } OVER inline-window-specification
+```
+
+* `_inline-window-specification_` is:
++
+```
+[PARTITION BY expression [, expression]...]
+[ORDER BY expression [ASC[ENDING] | DESC[ENDING]]
+ [,expression [ASC[ENDING] | DESC[ENDING]]]...]
+[ window-frame-clause ]
+```
+
+* `_window-frame-clause_` is:
++
+```
+ ROW CURRENT ROW
+| ROW preceding-row
+| ROW BETWEEN preceding-row AND preceding-row
+| ROW BETWEEN preceding-row AND CURRENT ROW
+| ROW BETWEEN preceding-row AND following-row
+| ROW BETWEEN CURRENT ROW AND CURRENT ROW
+| ROW BETWEEN CURRENT ROW AND following-row
+| ROW BETWEEN following-row AND following-row
+```
+
+* `_preceding-row_` is:
++
+```
+ UNBOUNDED PRECEDING
+| unsigned-integer PRECEDING
+```
+
+* `_following-row_` is:
+```
+ UNBOUNDED FOLLOWING
+| unsigned-integer FOLLOWING
+```
+
+* `ALL`
++
+specifies whether duplicate values are included in the computation of
+the COUNT of the _expression_. The default option is ALL, which causes
+duplicate values to be included.
+
+<<<
+* `_expression_`
++
+specifies a value _expression_ that is to be counted. See
+<<expressions,Expressions>>.
+
+* `_inline-window-specification_`
++
+specifies the window over which the COUNT is computed. The
+_inline-window-specification_ can contain an optional PARTITION BY
+clause, an optional ORDER BY clause and an optional window frame clause.
+The PARTITION BY clause specifies how the intermediate result is
+partitioned and the ORDER BY clause specifies how the rows are ordered
+within each partition.
+
+* `_window-frame-clause_`
++
+specifies the window within the partition over which the COUNT is
+computed.
+
+<<<
+[[examples_of_count_window_function]]
+=== Examples of COUNT Window Function
+
+* Return the running count of the SALARY column:
++
+```
+SELECT
+ empnum
+, COUNT(salary) OVER (ORDER BY empnum ROWS UNBOUNDED PRECEDING)
+FROM persnl.employee;
+```
+
+* Return the running count of the SALARY column within each department:
++
+```
+SELECT
+ deptnum
+, empnum
+, COUNT(salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS UNBOUNDED PRECEDING)
+FROM persnl.employee;
+```
+
+* Return the moving count of salary within each department over a window
+of the last 4 rows:
++
+```
+SELECT
+ deptnum
+, empnum
+, COUNT(salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS 3 PRECEDING)
+FROM persnl.employee;
+```
+
+* Return the running count of employees within each department:
++
+```
+SELECT
+ deptnum
+, empnum
+, COUNT(*) OVER (PARTITION BY deptnum ORDER BY empnum ROWS UNBOUNDED PRECEDING)
+FROM persnl.employee;
+```
+
+
+<<<
+[[dense_rank_window_function]]
+== DENSE_RANK Window Function
+
+DENSE_RANK is a window function that returns the ranking of each row of
+the current partition specified by the inline-window-specification. The
+ranking is relative to the ordering specified in the
+inline-window-specification. The return value of DENSE_RANK starts at 1
+for the first row of the window. Values of the given expression that are
+equal have the same rank. The value of DENSE_RANK advances 1 when the
+value of the given expression changes.
+
+```
+DENSERANK() OVER (inline-window-specification)
+```
+
+* `_inline-window-specification_` is:
++
+```
+[PARTITION BY expression [, expression]...]
+[ORDER BY expression [ASC[ENDING] | DESC[ENDING]]
+ [,expression [ASC[ENDING] | DESC[ENDING]]]...]
+```
+
+* `_inline-window-specification_`
++
+specifies the window over which the DENSE_RANK is computed. The
+_inline-window-specification_ can contain an optional PARTITION BY
+clause and an optional ORDER BY clause. The PARTITION BY clause
+specifies how the intermediate result is partitioned and the ORDER BY
+clause specifies how the rows are ordered within each partition.
+
+[[examples_of_dense_rank_window_function]]
+=== Examples of DENSE_RANK Window Function
+
+* Return the dense rank for each employee based on employee number:
++
+```
+SELECT
+ DENSE_RANK() OVER (ORDER BY empnum)
+, *
+FROM persnl.employee;
+```
+
+* Return the dense rank for each employee within each department based
+on salary:
++
+```
+SELECT
+ DENSE_RANK() OVER (PARTITION BY deptnum ORDER BY salary)
+, *
+FROM persnl.employee;
+```
+
+<<<
+[[max_window_function]]
+=== MAX Window Function
+
+MAX is a window function that returns the maximum value of all non null
+values of the given expression for the current window specified by the
+inline-window-specification.
+
+```
+MAX ([ALL] expression) OVER (inline-window-specification)
+```
+
+* `_inline-window-specification_` is:
++
+```
+[PARTITION BY expression [, expression]...]
+[ORDER BY expression [ASC[ENDING] | DESC[ENDING]]
+ [,expression [ASC[ENDING] | DESC[ENDING]]]...]
+[ window-frame-clause ]
+```
+
+* `_window-frame-clause_` is:
++
+```
+ ROWS CURRENT ROW
+| ROWS preceding-row
+| ROWS BETWEEN preceding-row AND preceding-row
+| ROWS BETWEEN preceding-row AND CURRENT ROW
+| ROWS BETWEEN preceding-row AND following-row
+| ROWS BETWEEN CURRENT ROW AND CURRENT ROW
+| ROWS BETWEEN CURRENT ROW AND following-row
+| ROWS BETWEEN following-row AND following-row
+```
+
+* `_preceding-row_` is:
++
+```
+ UNBOUNDED PRECEDING
+| unsigned-integer PRECEDING
+```
+
+* `_following-row_` is:
++
+```
+ UNBOUNDED FOLLOWING
+| unsigned-integer FOLLOWING
+```
+
+* `ALL`
++
+specifies whether duplicate values are included in the computation of
+the MAX of the _expression_. The default option is ALL, which causes
+duplicate values to be included.
+
+<<<
+* `_expression_`
++
+specifies an expression that determines the values over which the MAX is
+computed. See <<expressions,Expressions>>.
+
+* `_inline-window-specification_`
++
+specifies the window over which the MAX is computed. The
+_inline-window-specification_ can contain an optional PARTITION BY
+clause, an optional ORDER BY clause and an optional window frame clause.
+The PARTITION BY clause specifies how the intermediate result is
+partitioned and the ORDER BY clause specifies how the rows are ordered
+within each partition.
+
+* `_window-frame-clause_`
++
+specifies the window within the partition over which the MAX is
+computed.
+
+<<<
+[[examples_of_max_window_function]]
+=== Examples of MAX Window Function
+
+* Return the running maximum of the SALARY column:
++
+```
+SELECT
+ empnum
+, MAX(salary) OVER (ORDER BY empnum ROWS UNBOUNDED PRECEDING)
+FROM persnl.employee;
+```
+
+* Return the running maximum of the SALARY column within each department:
++
+```
+SELECT
+ deptnum
+, empnum, MAX(salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS UNBOUNDED PRECEDING)
+FROM persnl.employee;
+```
+
+* Return the moving maximum of salary within each department over a window of the last 4 rows:
++
+```
+SELECT
+ deptnum
+, empnum
+, MAX(salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS 3 PRECEDING)
+FROM persnl.employee;
+```
+
+<<<
+[[min_window_function]]
+== MIN Window Function
+
+MIN is a window function that returns the minimum value of all non null
+values of the given expression for the current window specified by the
+inline-window-specification.
+
+```
+MIN ([ALL] expression) OVER (inline-window-specification)
+```
+
+* `_inline-window-specification_` is:
++
+```
+[PARTITION BY expression [, expression]...]
+[ORDER BY expression [ASC[ENDING] | DESC[ENDING]]
+ [,expression [ASC[ENDING] | DESC[ENDING]]]...]
+[ window-frame-clause ]
+```
+
+* `_window-frame-clause_` is:
++
+```
+ ROWS CURRENT ROW
+| ROWS preceding-row
+| ROWS BETWEEN preceding-row AND preceding-row
+| ROWS BETWEEN preceding-row AND CURRENT ROW
+| ROWS BETWEEN preceding-row AND following-row
+| ROWS BETWEEN CURRENT ROW AND CURRENT ROW
+| ROWS BETWEEN CURRENT ROW AND following-row
+| ROWS BETWEEN following-row AND following-row
+```
+
+* `_preceding-row_` is:
++
+```
+ UNBOUNDED PRECEDING
+| unsigned-integer PRECEDING
+```
+
+* `_following-row_` is:
++
+```
+ UNBOUNDED FOLLOWING
+| unsigned-integer FOLLOWING
+```
+
+* `ALL1
++
+specifies whether duplicate values are included in the computation of
+the MIN of the _expression_. The default option is ALL, which causes
+duplicate values to be included.
+
+<<<
+* `_expression_`
++
+specifies an expression that determines the values over which the MIN is
+computed See <<expressions,Expressions>>.
+
+* `_inline-window-specification_`
++
+specifies the window over which the MIN is computed. The
+_inline-window-specification_ can contain an optional PARTITION BY
+clause, an optional ORDER BY clause and an optional window frame clause.
+The PARTITION BY clause specifies how the intermediate result is
+partitioned and the ORDER BY clause specifies how the rows are ordered
+within each partition.
+
+* `_window-frame-clause_`
++
+specifies the window within the partition over which the MIN is
+computed.
+
+<<<
+[[examples_of_min_window_function]]
+=== Examples of MIN Window Function
+
+* Return the running minimum of the SALARY column:
++
+```
+SELECT
+ empnum
+, MIN(salary) OVER (ORDER BY empnum ROWS UNBOUNDED PRECEDING)
+FROM persnl.employee;
+```
+
+* Return the running minimum of the SALARY column within each department:
++
+```
+SELECT
+ deptnum
+, empnum
+, MIN(salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS UNBOUNDED PRECEDING)
+FROM persnl.employee;
+```
+
+* Return the moving minimum of salary within each department over a window of the last 4 rows:
++
+```
+SELECT
+ deptnum
+, empnum
+, MIN(salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS 3 PRECEDING)
+FROM persnl.employee;
+```
+
+<<<
+[[rank_window_function]]
+== RANK Window Function
+
+RANK is a window function that returns the ranking of each row of the
+current partition specified by the inline-window-specification. The
+ranking is relative to the ordering specified in the
+_inline-window-specification_. The return value of RANK starts at 1 for
+the first row of the window. Values that are equal have the same rank.
+The value of RANK advances to the relative position of the row in the
+window when the value changes.
+
+```
+RANK() OVER (inline-window-specification)
+```
+
+* `_inline-window-specification_` is:
++
+```
+[PARTITION BY expression [, expression]...]
+[ORDER BY expression [ASC[ENDING] | DESC[ENDING]]
+ [,expression [ASC[ENDING] | DESC[ENDING]]]...]
+```
+
+* `_inline-window-specification_`
++
+specifies the window over which the RANK is computed. The
+_inline-window-specification_ can contain an optional PARTITION BY
+clause and an optional ORDER BY clause. The PARTITION BY clause
+specifies how the intermediate result is partitioned and the ORDER BY
+clause specifies how the rows are ordered within each partition.
+
+[[examples_of_rank_window_function]]
+=== Examples of RANK Window Function
+
+* Return the rank for each employee based on employee number:
++
+```
+SELECT
+ RANK() OVER (ORDER BY empnum)
+, *
+FROM persnl.employee;
+```
+
+* Return the rank for each employee within each department based on salary:
++
+```
+SELECT
+ RANK() OVER (PARTITION BY deptnum ORDER BY salary)
+, *
+FROM persnl.employee;
+```
+
+<<<
+[[row_number_window_function]]
+=== ROW_NUMBER Window Function
+
+ROW_NUMBER is a window function that returns the row number of each row
+of the current window specified by the inline-window-specification.
+
+```
+ROWNUMBER () OVER (inline-window-specification)
+```
+
+* `_inline-window-specification_` is:
++
+```
+[PARTITION BY expression [, expression]...]
+[ORDER BY expression [ASC[ENDING] | DESC[ENDING]]
+ [,expression [ASC[ENDING] | DESC[ENDING]]]...]
+```
+
+* `_inline-window-specification_`
++
+specifies the window over which the ROW_NUMBER is computed. The
+_inline-window-specification_ can contain an optional PARTITION BY
+clause and an optional ORDER BY clause. The PARTITION BY clause
+specifies how the intermediate result is partitioned and the ORDER BY
+clause specifies how the rows are ordered within each partition.
+
+* `_window-frame-clause_`
++
+specifies the window within the partition over which the ROW_NUMBER is
+computed.
+
+[[examples_of_row_number_window_function]]
+=== Examples of ROW_NUMBER Window Function
+
+* Return the row number for each row of the employee table:
++
+```
+SELECT
+ ROW_NUMBER () OVER(ORDER BY empnum)
+, *
+FROM persnl.employee;
+```
+
+* Return the row number for each row within each department:
++
+```
+SELECT
+ ROW_NUMBER () OVER(PARTITION BY deptnum ORDER BY empnum)
+, *
+FROM persnl.employee;
+```
+
+<<<
+[[stddev_window_function]]
+=== STDDEV Window Function
+
+STDDEV is a window function that returns the standard deviation of non
+null values of the given expression for the current window specified by
+the inline-window-specification.
+
+```
+STDDEV ([ALL] expression) OVER (inline-window-specification)
+```
+
+* `_inline-window-specification_` is:
++
+```
+[PARTITION BY expression [, expression]...]
+[ORDER BY expression [ASC[ENDING] | DESC[ENDING]]
+ [,expression [ASC[ENDING] | DESC[ENDING]]]...]
+[ window-frame-clause ]
+```
+
+* `_window-frame-clause_` is:
++
+```
+ ROWS CURRENT ROW
+| ROWS preceding-row
+| ROWS BETWEEN preceding-row AND preceding-row
+| ROWS BETWEEN preceding-row AND CURRENT ROW
+| ROWS BETWEEN preceding-row AND following-row
+| ROWS BETWEEN CURRENT ROW AND CURRENT ROW
+| ROWS BETWEEN CURRENT ROW AND following-row
+| ROWS BETWEEN following-row AND following-row
+```
+
+* `_preceding-row_` is:
++
+```
+ UNBOUNDED PRECEDING
+| unsigned-integer PRECEDING
+```
+
+* `_following-row_` is:
++
+```
+ UNBOUNDED FOLLOWING
+| unsigned-integer FOLLOWING
+```
+
+<<<
+* `ALL`
++
+specifies whether duplicate values are included in the computation of
+the STDDEV of the _expression_. The default option is ALL, which causes
+duplicate values to be included.
+
+* `_expression_`
++
+specifies a numeric or interval value _expression_ that determines the
+values over which STDDEV is computed.
+
+* `_inline-window-specification_`
++
+specifies the window over which the STDDEV is computed. The
+_inline-window-specification_ can contain an optional PARTITION BY
+clause, an optional ORDER BY clause and an optional window frame clause.
+The PARTITION BY clause specifies how the intermediate result is
+partitioned and the ORDER BY clause specifies how the rows are ordered
+within each partition.
+
+* `_window-frame-clause_`
++
+specifies the window within the partition over which the STDDEV is
+computed.
+
+[[examples_of_stddev]]
+=== Examples of STDDEV
+
+* Return the standard deviation of the salary for each row of the
+employee table:
++
+```
+SELECT
+ STDDEV(salary) OVER(ORDER BY empnum ROWS UNBOUNDED PRECEDING)
+, *
+FROM persnl.employee;
+```
+
+* Return the standard deviation for each row within each department:
++
+```
+SELECT
+ STDDEV() OVER(PARTITION BY deptnum ORDER BY empnum ROWS UNBOUNDED PRECEDING)
+, *
+FROM persnl.employee;
+```
+
+<<<
+[[sum_window_function]]
+== SUM Window Function
+
+SUM is a window function that returns the sum of non null values of the
+given expression for the current window specified by the
+inline-window-specification.
+
+```
+SUM ([ALL] expression) OVER (inline-window-specification)
+```
+
+* `_inline-window-specification_` is:
++
+```
+[PARTITION BY expression [, expression]...]
+[ORDER BY expression [ASC[ENDING] | DESC[ENDING]]
+ [,expression [ASC[ENDING] | DESC[ENDING]]]...]
+[ window-frame-clause ]
+```
+
+* `_window-frame-clause_` is:
++
+```
+ ROWS CURRENT ROW
+| ROWS preceding-row
+| ROWS BETWEEN preceding-row AND preceding-row
+| ROWS BETWEEN preceding-row AND CURRENT ROW
+| ROWS BETWEEN preceding-row AND following-row
+| ROWS BETWEEN CURRENT ROW AND CURRENT ROW
+| ROWS BETWEEN CURRENT ROW AND following-row
+| ROWS BETWEEN following-row AND following-row
+```
+* `_preceding-row_` is:
++
+```
+ UNBOUNDED PRECEDING
+| unsigned-integer PRECEDING
+```
+
+* `_following-row_` is:
++
+```
+ UNBOUNDED FOLLOWING
+| unsigned-integer FOLLOWING
+```
+
+<<<
+* `ALL`
++
+specifies whether duplicate values are included in the computation of
+the SUM of the _expression_. The default option is ALL, which causes
+duplicate values to be included.
+
+* `_expression_`
++
+specifies a numeric or interval value expression that determines the
+values to sum. See <<expressions,Expressions>>.
+
+* `_inline-window-specification_`
++
+specifies the window over which the SUM is computed. The
+_inline-window-specification_ can contain an optional PARTITION BY
+clause, an optional ORDER BY clause and an optional window frame clause.
+The PARTITION BY clause specifies how the intermediate result is
+partitioned and the ORDER BY clause specifies how the rows are ordered
+within each partition.
+
+* `_window-frame-clause_`
++
+specifies the window within the partition over which the SUM is computed.
+
+<<<
+[[examples_of_sum_window_function]]
+=== Examples of SUM Window Function
+
+* Return the running sum value of the SALARY column:
++
+```
+SELECT
+ empnum
+, SUM (salary) OVER (ORDER BY empnum ROWS UNBOUNDED PRECEDING)
+FROM persnl.employee;
+```
+
+* Return the running sum of the SALARY column within each department:
++
+```
+SELECT
+ deptnum
+, empnum, SUM (salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS UNBOUNDED PRECEDING)
+FROM persnl.employee;
+```
+
+* Return the moving sum of the SALARY column within each department over a window of the last 4 rows:
++
+```
+SELECT
+ deptnum
+, empnum
+, SUM (salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS 3 PRECEDING)
+FROM persnl.employee;
+```
+
+<<<
+[[variance_window_function]]
+== VARIANCE Window Function
+
+VARIANCE is a window function that returns the variance of non null
+values of the given expression for the current window specified by the
+inline-window-specification.
+
+```
+VARIANCE ([ALL] expression) OVER (inline-window-specification)
+```
+
+* `_inline-window-specification_` is:
++
+```
+[PARTITION BY expression [, expression]...]
+[ORDER BY expression [ASC[ENDING] | DESC[ENDING]]
+ [,expression [ASC[ENDING] | DESC[ENDING]]]...]
+[ window-frame-clause ]
+```
+* `_window-frame-clause_` is:
++
+```
+ ROWS CURRENT ROW
+| ROWS preceding-row
+| ROWS BETWEEN preceding-row AND preceding-row
+| ROWS BETWEEN preceding-row AND CURRENT ROW
+| ROWS BETWEEN preceding-row AND following-row
+| ROWS BETWEEN CURRENT ROW AND CURRENT ROW
+| ROWS BETWEEN CURRENT ROW AND following-row
+| ROWS BETWEEN following-row AND following-row
+```
+
+* `_preceding-row_` is:
++
+```
+ UNBOUNDED PRECEDING
+| unsigned-integer PRECEDING
+```
+
+* `_following-row_` is:
++
+```
+ UNBOUNDED FOLLOWING
+| unsigned-integer FOLLOWING
+```
+
+<<<
+* `ALL`
++
+specifies whether duplicate values are included in the computation of
+the VARIANCE of the _expression_. The default option is ALL, which causes
+duplicate values to be included.
+
+* `_expression_`
++
+specifies a numeric or interval value expression that determines the
+values over which the variance is computed.
+See <<expressions,Expressions>>.
+
+* `_inline-window-specification_`
++
+specifies the window over which the VARIANCE is computed. The
+_inline-window-specification_ can contain an optional PARTITION BY
+clause, an optional ORDER BY clause and an optional window frame clause.
+The PARTITION BY clause specifies how the intermediate result is
+partitioned and the ORDER BY clause specifies how the rows are ordered
+within each partition.
+
+* `_window-frame-clause_`
++
+specifies the window within the partition over which the VARIANCE is
+computed.
+
+[[examples_of_variance_window_function]]
+=== Examples of VARIANCE Window Function
+
+* Return the variance of the SALARY column:
++
+```
+SELECT
+ empnum
+, VARIANCE (salary) OVER (ORDER BY empnum ROWS UNBOUNDED PRECEDING)
+FROM persnl.employee;
+```
+
+* Return the variance of the SALARY column within each department:
++
+```
+SELECT
+ deptnum
+, empnum
+, VARIANCE (salary) OVER (PARTITION BY deptnum ORDER BY empnum ROWS UNBOUNDED PRECEDING)
+FROM persnl.employee;
+```
+
+
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/d16c4a1a/docs/sql_reference/src/asciidoc/_chapters/reserved_words.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/reserved_words.adoc b/docs/sql_reference/src/asciidoc/_chapters/reserved_words.adoc
new file mode 100644
index 0000000..478a2e5
--- /dev/null
+++ b/docs/sql_reference/src/asciidoc/_chapters/reserved_words.adoc
@@ -0,0 +1,287 @@
+////
+/**
+* @@@ START COPYRIGHT @@@
+*
+* Licensed to the Apache Software Foundation (ASF) under one
+* or more contributor license agreements. See the NOTICE file
+* distributed with this work for additional information
+* regarding copyright ownership. The ASF licenses this file
+* to you under the Apache License, Version 2.0 (the
+* "License"); you may not use this file except in compliance
+* with the License. You may obtain a copy of the License at
+*
+* http://www.apache.org/licenses/LICENSE-2.0
+*
+* Unless required by applicable law or agreed to in writing,
+* software distributed under the License is distributed on an
+* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+* KIND, either express or implied. See the License for the
+* specific language governing permissions and limitations
+* under the License.
+*
+* @@@ END COPYRIGHT @@@
+ */
+////
+
+<<<
+[[reserved_words]]
+= Reserved Words
+The words listed in this appendix are reserved for use by Trafodion SQL.
+To prevent syntax errors, avoid using these words as identifiers in
+Trafodion SQL. In Trafodion SQL, if an operating system name contains a
+reserved word, you must enclose the reserved word in double quotes (")
+to access that column or object.
+
+NOTE: In Trafodion SQL, ABSOLUTE, DATA, EVERY, INITIALIZE, OPERATION,
+PATH, SPACE, STATE, STATEMENT, STATIC, and START are not reserved words.
+
+Trafodion SQL treats these words as reserved when they are part of
+Trafodion SQL stored text. They cannot be used as identifiers unless you
+enclose them in double quotes.
+
+[[reserved_sql_identifiers_a]]
+== Reserved SQL Identifiers: A
+
+
+[cols="5*l"]
+|===
+| ACTION | ADD | ADMIN | AFTER | AGGREGATE
+| ALIAS| | ALL | ALLOCATE | ALTER | AND
+| ANY | ARE | ARRAY | AS | ASC
+| ASSERTION| ASYNC | AT | AUTHORIZATION | AVG
+|===
+
+
+[[reserved_sql_identifiers_b]]
+== Reserved SQL Identifiers: B
+
+
+[cols="5*l"]
+|===
+| BEFORE | BEGIN | BETWEEN | BINARY | BIT
+| BIT_LENGTH | BLOB | BOOLEAN | BOTH | BREADTH
+| BY | | | |
+|===
+
+[[reserved_sql_identifiers_c]]
+== Reserved SQL Identifiers: C
+
+
+[cols="5*l"]
+|===
+| CALL | CASCADE | CASCADED | CASE | CAST
+| CATALOG | CHAR | CHARACTER | CHARACTER_LENGTH | CHAR_LENGTH
+| CHECK | CLASS | CLOB | CLOSE | COALESCE
+| COLLATE | COLLATION | COLUMN | COMMIT | COMPLETION
+| CONNECT | CONNECTION | CONSTRAINT | CONSTRAINTS | CONSTRUCTOR
+| CONTINUE | CONVERT | CORRESPONDING | COUNT | CREATE
+| CROSS | CUBE | CURRENT | CURRENT_DATE | CURRENT_PATH
+| CURRENT_ROLE | CURRENT_TIME | CURRENT_TIMESTAMP | CURRENT_USER | CURRNT_USR_INTN
+| CURSOR | CYCLE | | |
+|===
+
+
+[[reserved_sql_identifiers_d]]
+== Reserved SQL Identifiers: D
+
+[cols="5*l"]
+|===
+| DATE | DATETIME | DAY | DEALLOCATE | DEC
+| DECIMAL | DECLARE | DEFAULT | DEFERRABLE | DEFERRED
+| DELETE | DEPTH | DEREF | DESC | DESCRIBE
+| DESCRIPTOR | DESTROY | DESTRUCTOR | DETERMINISTIC | DIAGNOSTICS
+| DICTIONARY | DISCONNECT | DISTINCT | DOMAIN | DOUBLE
+| DROP | DYNAMIC | | |
+|===
+
+
+[[reserved_sql_identifiers_e]]
+== Reserved SQL Identifiers: E
+
+
+[cols="5*l"]
+|===
+| EACH | ELSE | ELSEIF | END | END-EXEC
+| EQUALS | ESCAPE | EXCEPT | EXCEPTION | EXEC
+| EXECUTE | EXISTS | EXTERNAL | EXTRACT |
+|===
+
+
+== Reserved SQL Identifers: F
+
+[cols="5*l"]
+|===
+| FALSE | FETCH | FIRST | FLOAT | FOR
+| FOREIGN | FOUND | FRACTION | FREE | FROM
+| FULL | FUNCTION | | |
+|===
+
+
+[[reserved_sql_identifiers_g]]
+== Reserved SQL Identifiers G
+
+[cols="5*l"]
+|===
+| GENERAL | GET | GLOBAL | GO | GOTO
+| GRANT | GROUP | GROUPING | |
+|===
+
+[[reserved_sql_identifiers_h]]
+== Reserved SQL Identifiers: H
+
+[[reserved_sql_identifiers_i]]
+== Reserved SQL Identifiers: I
+
+
+[cols="5*l"]
+|===
+| IDENTITY | IF | IGNORE | IMMEDIATE | IN
+| INDICATOR | INITIALLY | INNER | INOUT | INPUT
+| INSENSITIVE | INSERT | INT | INTEGER | INTERSECT
+| INTERVAL | INTO | IS | ISOLATION | ITERATE
+|===
+
+
+[[reserved_sql_identifiers_j]]
+== Reserved SQL Identifiers J
+
+[[reserved_sql_identifiers_k]]
+== Reserved SQL Identifiers: K
+
+[[reserved_sql_identifiers_l]]
+== Reserved SQL Identifiers: L
+
+[cols="5*l"]
+|===
+| LANGUAGE | LARGE | LAST | LATERAL | LEADING
+| LEAVE | LEFT | LESS | LEVEL | LIKE
+| LIMIT | LOCAL | LOCALTIME | LOCALTIMESTAMP | LOCATOR
+| LOOP | LOWER | | |
+|===
+
+
+[[reserved_sql_identifiers_m]]
+== Reserved SQL Identifiers: M
+
+[cols="5*l"]
+|===
+| MAINTAIN | MAP | MATCH | MATCHED | MAX
+| MERGE | MIN | MINUTE | MODIFIES | MODIFY
+| MODULE | MONTH | | |
+|===
+
+
+[[reserved_sql_identifiers_n]]
+== Reserved SQL Identifiers: N
+
+[cols="5*l"]
+|===
+| NAMES | NATIONAL | NATURAL | NCHAR | NCLOB
+| NEW | NEXT | NO | NONE | NOT
+| NULL | NULLIF | NUMERIC | |
+|===
+
+[[reserved_sql_identifiers_o]]
+== Reserved SQL Identifiers: O
+
+[cols="5*l"]
+|===
+| OCTET_LENGTH | OF | OFF | OID | OLD
+| ON | ONLY | OPEN | OPERATORS | OPTION
+| OPTIONS | OR | ORDER | ORDINALITY | OTHERS
+| OUT | OUTER | OUTPUT | OVERLAPS |
+|===
+
+
+[[reserved_sql_identifiers_p]]
+== Reserved SQL Identifiers: P
+
+[cols="5*l"]
+|===
+| PAD | PARAMETER | PARAMETERS | PARTIAL | PENDANT
+| POSITION | POSTFIX | PRECISION | PREFIX | PREORDER
+| PREPARE | PRESERVE | PRIMARY | PRIOR | PRIVATE
+| PRIVILEGES | PROCEDURE | PROTECTED | PROTOTYPE | PUBLIC
+|===
+
+
+[[reserved_sql_identifiers_q]]
+== Reserved SQL Identifiers: Q
+
+[[reserved_sql_identifiers_r]]
+== Reserved SQL Identifiers: R
+
+[cols="5*l"]
+|===
+| READ | READS | REAL | RECURSIVE | REF
+| REFERENCES | REFERENCING | RELATIVE | REORG | REORGANIZE
+| REPLACE | RESIGNAL | RESTRICT | RESULT | RETURN
+| RETURNS | REVOKE | RIGHT | ROLLBACK | ROLLUP
+| ROUTINE | ROW | ROWS | |
+|===
+
+[[reserved_sql_identifiers_s]]
+== Reserved SQL Identifiers: S
+
+[cols="5*l"]
+|===
+| SAVEPOINT | SCHEMA | SCOPE | SCROLL | SEARCH
+| SECOND | SECTION | SELECT | SENSITIVE | SESSION
+| SESSION_USER | SESSN_USR_INTN | SET | SETS | SIGNAL
+| SIMILAR | SIZE | SMALLINT | SOME | SPECIFIC
+| SPECIFICTYPE | SQL | SQL_CHAR | SQL_DATE | SQL_DECIMAL
+| SQL_DOUBLE | SQL_FLOAT | SQL_INT | SQL_INTEGER | SQL_REAL
+| SQL_SMALLINT | SQL_TIME | SQL_TIMESTAMP | SQL_VARCHAR | SQLCODE
+| SQLERROR | SQLEXCEPTION | SQLSTATE | SQLWARNING | STRUCTURE
+| SUBSTRING | SUM | SYNONYM | SYSTEM_USER |
+|===
+
+
+[[reserved_sql_identifiers_t]]
+== Reserved SQL Identifiers: T
+
+[cols="5*l"]
+|===
+| TABLE | TEMPORARY | TERMINATE | TEST | THAN
+| THEN | THERE | TIME | TIMESTAMP | TIMEZONE_HOUR
+| TIMEZONE_MINUTE | TO | TRAILING | TRANSACTION | TRANSLATE
+| TRANSLATION | TRANSPOSE | TREAT | TRIGGER | TRIM
+| TRUE | | | |
+|===
+
+[[reserved_sql_identifiers_u]]
+== Reserved SQL Identifiers: U
+
+[cols="5*l"]
+|===
+| UNDER | UNION | UNIQUE | UNKNOWN | UNNEST
+| UPDATE | UPPER | UPSHIFT | USAGE | USER
+| USING | | | |
+|===
+
+[[reserved_sql_identifiers_v]]
+== Reserved SQL Identifiers: V
+
+[cols="5*l"]
+|===
+| VALUE | VALUES | VARCHAR | VARIABLE | VARYING
+| VIEW | VIRTUAL | VISIBLE | |
+|===
+
+[[reserved_sql_identifiers_w]]
+== Reserved SQL Identifiers: W
+
+[cols="5*l"]
+|===
+| WAIT | WHEN | WHENEVER | WHERE | WHILE
+| WITH | WITHOUT | WORK | WRITE |
+|===
+
+
+[[reserved_sql_identifiers_y]]
+== Reserved SQL Identifiers Y
+
+[[reserved_sql_identifiers_z]]
+== Reserved SQL Identifiers: Z
+
+