You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2015/05/29 13:48:35 UTC
svn commit: r1682430 [5/9] - in /incubator/calcite/site: ./ css/ develop/
docs/ downloads/ fonts/ help/ img/ js/ news/ news/2015/ news/2015/01/
news/2015/01/31/ news/2015/01/31/release-1.0.0-incubating/ news/2015/03/
news/2015/03/13/ news/2015/03/13/re...
Added: incubator/calcite/site/docs/model.html
URL: http://svn.apache.org/viewvc/incubator/calcite/site/docs/model.html?rev=1682430&view=auto
==============================================================================
--- incubator/calcite/site/docs/model.html (added)
+++ incubator/calcite/site/docs/model.html Fri May 29 11:48:34 2015
@@ -0,0 +1,1546 @@
+<!DOCTYPE HTML>
+<html lang="en-US">
+<head>
+ <meta charset="UTF-8">
+ <title>JSON models</title>
+ <meta name="viewport" content="width=device-width,initial-scale=1">
+ <meta name="generator" content="Jekyll v2.4.0">
+ <link rel="stylesheet" href="//fonts.googleapis.com/css?family=Lato:300,300italic,400,400italic,700,700italic,900">
+ <link rel="stylesheet" href="/css/screen.css">
+ <link rel="icon" type="image/x-icon" href="/favicon.ico">
+ <!--[if lt IE 9]>
+ <script src="/js/html5shiv.min.js"></script>
+ <script src="/js/respond.min.js"></script>
+ <![endif]-->
+</head>
+
+
+<body class="wrap">
+ <header role="banner">
+ <nav class="mobile-nav show-on-mobiles">
+ <ul>
+ <li class="">
+ <a href="/">Home</a>
+ </li>
+ <li class="current">
+ <a href="/docs/">Documentation</a>
+ </li>
+ <li class="">
+ <a href="/talks/">Talks</a>
+ </li>
+ <li class="">
+ <a href="/news/">News</a>
+ </li>
+ <li class="">
+ <a href="/help/">Help</a>
+ </li>
+ <li class="">
+ <a href="/develop/">Develop</a>
+ </li>
+</ul>
+
+ </nav>
+ <div class="grid">
+ <div class="unit one-third center-on-mobiles">
+ <h1>
+ <a href="/">
+ <span class="sr-only">Apache Calcite</span>
+ <img src="/img/logo.png" width="226" height="140" alt="Calcite Logo">
+ </a>
+ </h1>
+ </div>
+ <nav class="main-nav unit two-thirds hide-on-mobiles">
+ <ul>
+ <li class="">
+ <a href="/">Home</a>
+ </li>
+ <li class="current">
+ <a href="/docs/">Documentation</a>
+ </li>
+ <li class="">
+ <a href="/talks/">Talks</a>
+ </li>
+ <li class="">
+ <a href="/news/">News</a>
+ </li>
+ <li class="">
+ <a href="/help/">Help</a>
+ </li>
+ <li class="">
+ <a href="/develop/">Develop</a>
+ </li>
+</ul>
+
+ </nav>
+ </div>
+</header>
+
+
+ <section class="docs">
+ <div class="grid">
+
+ <div class="docs-nav-mobile unit whole show-on-mobiles">
+ <select onchange="if (this.value) window.location.href=this.value">
+ <option value="">Navigate the docsâ¦</option>
+
+ <optgroup label="Overview">
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <option value="/docs/index.html">Background</option>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <option value="/docs/tutorial.html">Tutorial</option>
+
+
+
+
+
+
+
+
+
+ <option value="/docs/algebra.html">Algebra</option>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ </optgroup>
+
+ <optgroup label="Advanced">
+
+
+
+
+
+
+
+ <option value="/docs/adapter.html">Adapters</option>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <option value="/docs/avatica.html">Avatica</option>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <option value="/docs/stream.html">Streaming</option>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <option value="/docs/lattice.html">Lattices</option>
+
+
+
+
+
+
+
+
+
+
+
+
+ </optgroup>
+
+ <optgroup label="Reference">
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <option value="/docs/reference.html">SQL language</option>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <option value="/docs/model.html">JSON models</option>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <option value="/docs/howto.html">HOWTO</option>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ </optgroup>
+
+ <optgroup label="Meta">
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <option value="/docs/downloads.html">Downloads</option>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <option value="/docs/history.html">History</option>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <option value="/docs/contributing.html">Contributing</option>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <option value="/docs/api.html">API</option>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ </optgroup>
+
+ </select>
+</div>
+
+
+ <div class="unit four-fifths">
+ <article>
+ <h1>JSON models</h1>
+ <!--
+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.
+-->
+<p>Calcite models can be represented as JSON files.
+This page describes the structure of those files.</p>
+
+<p>Models can also be built programmatically using the <code>Schema</code> SPI.</p>
+
+<h2 id="elements">Elements</h2>
+
+<h3 id="root">Root</h3>
+
+<p><code>json
+{
+ version: '1.0',
+ defaultSchema: 'mongo',
+ schemas: [ Schema... ]
+}
+</code></p>
+
+<p><code>version</code> (required string) must have value <code>1.0</code>.</p>
+
+<p><code>defaultSchema</code> (optional string). If specified, it is
+the name (case-sensitive) of a schema defined in this model, and will
+become the default schema for connections to Calcite that use this model.</p>
+
+<p><code>schemas</code> (optional list of <a href="#schema">Schema</a> elements).</p>
+
+<h3 id="schema">Schema</h3>
+
+<p>Occurs within <code>root.schemas</code>.</p>
+
+<p><code>json
+{
+ name: 'foodmart',
+ path: ['lib'],
+ cache: true,
+ materializations: [ Materialization... ]
+}
+</code></p>
+
+<p><code>name</code> (required string) is the name of the schema.</p>
+
+<p><code>type</code> (optional string, default <code>map</code>) indicates sub-type. Values are:
+* <code>map</code> for <a href="#map-schema">Map Schema</a>
+* <code>custom</code> for <a href="#custom-schema">Custom Schema</a>
+* <code>jdbc</code> for <a href="#jdbc-schema">JDBC Schema</a></p>
+
+<p><code>path</code> (optional list) is the SQL path that is used to
+resolve functions used in this schema. If specified it must be a list,
+and each element of the list must be either a string or a list of
+strings. For example,</p>
+
+<p><code>json
+ path: [ ['usr', 'lib'], 'lib' ]
+</code></p>
+
+<p>declares a path with two elements: the schema â/usr/libâ and the
+schema â/libâ. Most schemas are at the top level, so you can use a
+string.</p>
+
+<p><code>materializations</code> (optional list of
+<a href="#materialization">Materialization</a>) defines the tables
+in this schema that are materializations of queries.</p>
+
+<p><code>cache</code> (optional boolean, default true) tells Calcite whether to
+cache metadata (tables, functions and sub-schemas) generated
+by this schema.</p>
+
+<ul>
+ <li>
+ <p>If <code>false</code>, Calcite will go back to the schema each time it needs
+metadata, for example, each time it needs a list of tables in order to
+validate a query against the schema.</p>
+ </li>
+ <li>
+ <p>If <code>true</code>, Calcite will cache the metadata the first time it reads
+it. This can lead to better performance, especially if name-matching is
+case-insensitive.</p>
+ </li>
+</ul>
+
+<p>However, it also leads to the problem of cache staleness.
+A particular schema implementation can override the
+<code>Schema.contentsHaveChangedSince</code> method to tell Calcite
+when it should consider its cache to be out of date.</p>
+
+<p>Tables, functions and sub-schemas explicitly created in a schema are
+not affected by this caching mechanism. They always appear in the schema
+immediately, and are never flushed.</p>
+
+<h3 id="map-schema">Map Schema</h3>
+
+<p>Like base class <a href="#schema">Schema</a>, occurs within <code>root.schemas</code>.</p>
+
+<p><code>json
+{
+ name: 'foodmart',
+ type: 'map',
+ tables: [ Table... ],
+ functions: [ Function... ]
+}
+</code></p>
+
+<p><code>name</code>, <code>type</code>, <code>path</code>, <code>cache</code>, <code>materializations</code> inherited from
+<a href="#schema">Schema</a>.</p>
+
+<p><code>tables</code> (optional list of <a href="#table">Table</a> elements)
+defines the tables in this schema.</p>
+
+<p><code>functions</code> (optional list of <a href="#function">Function</a> elements)
+defines the functions in this schema.</p>
+
+<h3 id="custom-schema">Custom Schema</h3>
+
+<p>Like base class <a href="#schema">Schema</a>, occurs within <code>root.schemas</code>.</p>
+
+<p><code>json
+{
+ name: 'mongo',
+ type: 'custom',
+ factory: 'org.apache.calcite.adapter.mongodb.MongoSchemaFactory',
+ operand: {
+ host: 'localhost',
+ database: 'test'
+ }
+}
+</code></p>
+
+<p><code>name</code>, <code>type</code>, <code>path</code>, <code>cache</code>, <code>materializations</code> inherited from
+<a href="#schema">Schema</a>.</p>
+
+<p><code>factory</code> (required string) is the name of the factory class for this
+schema. Must implement interface <code>org.apache.calcite.schema.SchemaFactory</code>
+and have a public default constructor.</p>
+
+<p><code>operand</code> (optional map) contains attributes to be passed to the
+factory.</p>
+
+<h3 id="jdbc-schema">JDBC Schema</h3>
+
+<p>Like base class <a href="#schema">Schema</a>, occurs within <code>root.schemas</code>.</p>
+
+<p><code>json
+{
+ name: 'foodmart',
+ type: 'jdbc',
+ jdbcDriver: TODO,
+ jdbcUrl: TODO,
+ jdbcUser: TODO,
+ jdbcPassword: TODO,
+ jdbcCatalog: TODO,
+ jdbcSchema: TODO
+}
+</code></p>
+
+<p><code>name</code>, <code>type</code>, <code>path</code>, <code>cache</code>, <code>materializations</code> inherited from
+<a href="#schema">Schema</a>.</p>
+
+<p><code>jdbcDriver</code> (optional string) is the name of the JDBC driver class. It not
+specified, uses whichever class the JDBC DriverManager chooses.</p>
+
+<p><code>jdbcUrl</code> (optional string) is the JDBC connect string, for example
+âjdbc:mysql://localhost/foodmartâ.</p>
+
+<p><code>jdbcUser</code> (optional string) is the JDBC user name.</p>
+
+<p><code>jdbcPassword</code> (optional string) is the JDBC password.</p>
+
+<p><code>jdbcCatalog</code> (optional string) is the name of the initial catalog in the JDBC
+data source.</p>
+
+<p><code>jdbcSchema</code> (optional string) is the name of the initial schema in the JDBC
+data source.</p>
+
+<h3 id="materialization">Materialization</h3>
+
+<p>Occurs within <code>root.schemas.materializations</code>.</p>
+
+<p><code>json
+{
+ view: 'V',
+ table: 'T',
+ sql: 'select deptno, count(*) as c, sum(sal) as s from emp group by deptno'
+}
+</code></p>
+
+<p><code>view</code> (optional string) TODO</p>
+
+<p><code>table</code> (optional string) TODO</p>
+
+<p><code>sql</code> (optional string, or list of strings that will be concatenated as a
+ multi-line string) is the SQL definition of the materialization.</p>
+
+<h3 id="table">Table</h3>
+
+<p>Occurs within <code>root.schemas.tables</code>.</p>
+
+<p><code>json
+{
+ name: 'sales_fact',
+ columns: [ Column... ]
+}
+</code></p>
+
+<p><code>name</code> (required string) is the name of this table. Must be unique within the schema.</p>
+
+<p><code>type</code> (optional string, default <code>custom</code>) indicates sub-type. Values are:
+* <code>custom</code> for <a href="#custom-table">Custom Table</a>
+* <code>view</code> for <a href="#view">View</a></p>
+
+<p><code>columns</code> (optional list of <a href="#column">Column</a> elements)</p>
+
+<h3 id="view">View</h3>
+
+<p>Like base class <a href="#table">Table</a>, occurs within <code>root.schemas.tables</code>.</p>
+
+<p><code>json
+{
+ name: 'female_emps',
+ type: 'view',
+ sql: "select * from emps where gender = 'F'",
+ modifiable: true
+}
+</code></p>
+
+<p><code>name</code>, <code>type</code>, <code>columns</code> inherited from <a href="#table">Table</a>.</p>
+
+<p><code>sql</code> (required string, or list of strings that will be concatenated as a
+ multi-line string) is the SQL definition of the view.</p>
+
+<p><code>path</code> (optional list) is the SQL path to resolve the query. If not
+specified, defaults to the current schema.</p>
+
+<p><code>modifiable</code> (optional boolean) is whether the view is modifiable.
+If null or not specified, Calcite deduces whether the view is modifiable.</p>
+
+<p>A view is modifiable if contains only SELECT, FROM, WHERE (no JOIN, aggregation
+or sub-queries) and every column:
+* is specified once in the SELECT clause; or
+* occurs in the WHERE clause with a <code>column = literal</code> predicate; or
+* is nullable.</p>
+
+<p>The second clause allows Calcite to automatically provide the correct value for
+hidden columns. It is useful in multi-tenant environments, where the <code>tenantId</code>
+column is hidden, mandatory (NOT NULL), and has a constant value for a
+particular view.</p>
+
+<p>Errors regarding modifiable views:
+* If a view is marked <code>modifiable: true</code> and is not modifiable, Calcite throws
+ an error while reading the schema.
+* If you submit an INSERT, UPDATE or UPSERT command to a non-modifiable view,
+ Calcite throws an error when validating the statement.
+* If a DML statement creates a row that would not appear in the view
+ (for example, a row in <code>female_emps</code>, above, with <code>gender = 'M'</code>),
+ Calcite throws an error when executing the statement.</p>
+
+<h3 id="custom-table">Custom Table</h3>
+
+<p>Like base class <a href="#table">Table</a>, occurs within <code>root.schemas.tables</code>.</p>
+
+<p><code>json
+{
+ name: 'female_emps',
+ type: 'custom',
+ factory: 'TODO',
+ operand: {
+ todo: 'TODO'
+ }
+}
+</code></p>
+
+<p><code>name</code>, <code>type</code>, <code>columns</code> inherited from <a href="#table">Table</a>.</p>
+
+<p><code>factory</code> (required string) is the name of the factory class for this
+table. Must implement interface <code>org.apache.calcite.schema.TableFactory</code>
+and have a public default constructor.</p>
+
+<p><code>operand</code> (optional map) contains attributes to be passed to the
+factory.</p>
+
+<h3 id="column">Column</h3>
+
+<p>Occurs within <code>root.schemas.tables.columns</code>.</p>
+
+<p><code>json
+{
+ name: 'empno'
+}
+</code></p>
+
+<p><code>name</code> (required string) is the name of this column.</p>
+
+<h3 id="function">Function</h3>
+
+<p>Occurs within <code>root.schemas.functions</code>.</p>
+
+<p><code>json
+{
+ name: 'MY_PLUS',
+ className: 'com.example.functions.MyPlusFunction',
+ methodName: 'apply',
+ path: []
+}
+</code></p>
+
+<p><code>name</code> (required string) is the name of this function.</p>
+
+<p><code>className</code> (required string) is the name of the class that implements this
+function.</p>
+
+<p><code>methodName</code> (optional string) is the name of the method that implements this
+function.</p>
+
+<p><code>path</code> (optional list of string) is the path for resolving this function.</p>
+
+<h3 id="lattice">Lattice</h3>
+
+<p>Occurs within <code>root.schemas.lattices</code>.</p>
+
+<p><code>json
+{
+ name: 'star',
+ sql: [
+ 'select 1 from "foodmart"."sales_fact_1997" as "s"',
+ 'join "foodmart"."product" as "p" using ("product_id")',
+ 'join "foodmart"."time_by_day" as "t" using ("time_id")',
+ 'join "foodmart"."product_class" as "pc" on "p"."product_class_id" = "pc"."product_class_id"'
+ ],
+ auto: false,
+ algorithm: true,
+ algorithmMaxMillis: 10000,
+ rowCountEstimate: 86837,
+ defaultMeasures: [ {
+ agg: 'count'
+ } ],
+ tiles: [ {
+ dimensions: [ 'the_year', ['t', 'quarter'] ],
+ measures: [ {
+ agg: 'sum',
+ args: 'unit_sales'
+ }, {
+ agg: 'sum',
+ args: 'store_sales'
+ }, {
+ agg: 'count'
+ } ]
+ } ]
+}
+</code></p>
+
+<p><code>name</code> (required string) is the name of this lattice.</p>
+
+<p><code>sql</code> (required string, or list of strings that will be concatenated as a
+multi-line string) is the SQL statement that defines the fact table, dimension
+tables, and join paths for this lattice.</p>
+
+<p><code>auto</code> (optional boolean, default true) is whether to materialize tiles on need
+as queries are executed.</p>
+
+<p><code>algorithm</code> (optional boolean, default false) is whether to use an optimization
+algorithm to suggest and populate an initial set of tiles.</p>
+
+<p><code>algorithmMaxMillis</code> (optional long, default -1, meaning no limit) is the
+maximum number of milliseconds for which to run the algorithm. After this point,
+takes the best result the algorithm has come up with so far.</p>
+
+<p><code>rowCountEstimate</code> (optional double, default 1000.0) estimated number of rows in
+the star</p>
+
+<p><code>tiles</code> (optional list of <a href="#tile">Tile</a> elements) is a list of
+materialized aggregates to create up front.</p>
+
+<p><code>defaultMeasures</code> (optional list of <a href="#measure">Measure</a> elements)
+is a list of measures that a tile should have by default.
+Any tile defined in <code>tiles</code> can still define its own measures, including
+measures not on this list. If not specified, the default list of measures is
+just âcount(*)â:</p>
+
+<p><code>json
+[ { name: 'count' } ]
+</code></p>
+
+<p>See also: <a href="lattice.md">Lattices</a>.</p>
+
+<h3 id="tile">Tile</h3>
+
+<p>Occurs within <code>root.schemas.lattices.tiles</code>.</p>
+
+<p><code>json
+{
+ dimensions: [ 'the_year', ['t', 'quarter'] ],
+ measures: [ {
+ agg: 'sum',
+ args: 'unit_sales'
+ }, {
+ agg: 'sum',
+ args: 'store_sales'
+ }, {
+ agg: 'count'
+ } ]
+}
+</code></p>
+
+<p><code>dimensions</code> is a list of dimensions (columns from the star), like a <code>GROUP BY</code>
+clause. Each element is either a string (the unique label of the column within
+the star) or a string list (a column name qualified by a table name).</p>
+
+<p><code>measures</code> (optional list of <a href="#measure">Measure</a> elements) is a list
+of aggregate functions applied to arguments. If not specified, uses the
+latticeâs default measure list.</p>
+
+<h3 id="measure">Measure</h3>
+
+<p>Occurs within <code>root.schemas.lattices.defaultMeasures</code>
+and <code>root.schemas.lattices.tiles.measures</code>.</p>
+
+<p><code>json
+{
+ agg: 'sum',
+ args: [ 'unit_sales' ]
+}
+</code></p>
+
+<p><code>agg</code> is the name of an aggregate function (usually âcountâ, âsumâ, âminâ,
+âmaxâ).</p>
+
+<p><code>args</code> (optional) is a column label (string), or list of zero or more columns.
+If a list, each element is either a string (the unique label of the column
+within the star) or a string list (a column name qualified by a table name).</p>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <div class="section-nav">
+ <div class="left align-right">
+
+
+
+ <a href="/docs/reference.html" class="prev">Back</a>
+
+ </div>
+ <div class="right align-left">
+
+
+
+ <a href="/docs/howto.html" class="next">Next</a>
+
+ </div>
+ </div>
+ <div class="clear"></div>
+
+
+ </article>
+ </div>
+
+ <div class="unit one-fifth hide-on-mobiles">
+ <aside>
+
+ <h4>Overview</h4>
+
+
+<ul>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/index.html">Background</a></li>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/tutorial.html">Tutorial</a></li>
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/algebra.html">Algebra</a></li>
+
+
+
+</ul>
+
+
+ <h4>Advanced</h4>
+
+
+<ul>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/adapter.html">Adapters</a></li>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/avatica.html">Avatica</a></li>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/stream.html">Streaming</a></li>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/lattice.html">Lattices</a></li>
+
+
+
+</ul>
+
+
+ <h4>Reference</h4>
+
+
+<ul>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/reference.html">SQL language</a></li>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class="current"><a href="/docs/model.html">JSON models</a></li>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/howto.html">HOWTO</a></li>
+
+
+
+</ul>
+
+
+ <h4>Meta</h4>
+
+
+<ul>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/downloads.html">Downloads</a></li>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/history.html">History</a></li>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/contributing.html">Contributing</a></li>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/api.html">API</a></li>
+
+
+
+</ul>
+
+
+ </aside>
+</div>
+
+
+ <div class="clear"></div>
+
+ </div>
+ </section>
+
+
+ <footer role="contentinfo">
+ <p>The contents of this website are © 2015
+ <a href="https://www.apache.org/">Apache Software Foundation</a>
+ under the terms of
+ the <a href="https://www.apache.org/licenses/LICENSE-2.0.html">
+ Apache License v2</a>. Apache Calcite and its logo are
+ trademarks of the Apache Software Foundation.</p>
+
+ <p>Apache Calcite is an effort undergoing incubation at The Apache
+ Software Foundation (ASF), sponsored by the Apache
+ Incubator. Incubation is required of all newly accepted projects
+ until a further review indicates that the infrastructure,
+ communications, and decision making process have stabilized in a
+ manner consistent with other successful ASF projects. While
+ incubation status is not necessarily a reflection of the
+ completeness or stability of the code, it does indicate that the
+ project has yet to be fully endorsed by the ASF.</p>
+
+</footer>
+
+ <script>
+ var anchorForId = function (id) {
+ var anchor = document.createElement("a");
+ anchor.className = "header-link";
+ anchor.href = "#" + id;
+ anchor.innerHTML = "<span class=\"sr-only\">Permalink</span><i class=\"fa fa-link\"></i>";
+ anchor.title = "Permalink";
+ return anchor;
+ };
+
+ var linkifyAnchors = function (level, containingElement) {
+ var headers = containingElement.getElementsByTagName("h" + level);
+ for (var h = 0; h < headers.length; h++) {
+ var header = headers[h];
+
+ if (typeof header.id !== "undefined" && header.id !== "") {
+ header.appendChild(anchorForId(header.id));
+ }
+ }
+ };
+
+ document.onreadystatechange = function () {
+ if (this.readyState === "complete") {
+ var contentBlock = document.getElementsByClassName("docs")[0] || document.getElementsByClassName("news")[0];
+ if (!contentBlock) {
+ return;
+ }
+ for (var level = 1; level <= 6; level++) {
+ linkifyAnchors(level, contentBlock);
+ }
+ }
+ };
+</script>
+
+
+</body>
+</html>
Added: incubator/calcite/site/docs/reference.html
URL: http://svn.apache.org/viewvc/incubator/calcite/site/docs/reference.html?rev=1682430&view=auto
==============================================================================
--- incubator/calcite/site/docs/reference.html (added)
+++ incubator/calcite/site/docs/reference.html Fri May 29 11:48:34 2015
@@ -0,0 +1,2284 @@
+<!DOCTYPE HTML>
+<html lang="en-US">
+<head>
+ <meta charset="UTF-8">
+ <title>SQL language</title>
+ <meta name="viewport" content="width=device-width,initial-scale=1">
+ <meta name="generator" content="Jekyll v2.4.0">
+ <link rel="stylesheet" href="//fonts.googleapis.com/css?family=Lato:300,300italic,400,400italic,700,700italic,900">
+ <link rel="stylesheet" href="/css/screen.css">
+ <link rel="icon" type="image/x-icon" href="/favicon.ico">
+ <!--[if lt IE 9]>
+ <script src="/js/html5shiv.min.js"></script>
+ <script src="/js/respond.min.js"></script>
+ <![endif]-->
+</head>
+
+
+<body class="wrap">
+ <header role="banner">
+ <nav class="mobile-nav show-on-mobiles">
+ <ul>
+ <li class="">
+ <a href="/">Home</a>
+ </li>
+ <li class="current">
+ <a href="/docs/">Documentation</a>
+ </li>
+ <li class="">
+ <a href="/talks/">Talks</a>
+ </li>
+ <li class="">
+ <a href="/news/">News</a>
+ </li>
+ <li class="">
+ <a href="/help/">Help</a>
+ </li>
+ <li class="">
+ <a href="/develop/">Develop</a>
+ </li>
+</ul>
+
+ </nav>
+ <div class="grid">
+ <div class="unit one-third center-on-mobiles">
+ <h1>
+ <a href="/">
+ <span class="sr-only">Apache Calcite</span>
+ <img src="/img/logo.png" width="226" height="140" alt="Calcite Logo">
+ </a>
+ </h1>
+ </div>
+ <nav class="main-nav unit two-thirds hide-on-mobiles">
+ <ul>
+ <li class="">
+ <a href="/">Home</a>
+ </li>
+ <li class="current">
+ <a href="/docs/">Documentation</a>
+ </li>
+ <li class="">
+ <a href="/talks/">Talks</a>
+ </li>
+ <li class="">
+ <a href="/news/">News</a>
+ </li>
+ <li class="">
+ <a href="/help/">Help</a>
+ </li>
+ <li class="">
+ <a href="/develop/">Develop</a>
+ </li>
+</ul>
+
+ </nav>
+ </div>
+</header>
+
+
+ <section class="docs">
+ <div class="grid">
+
+ <div class="docs-nav-mobile unit whole show-on-mobiles">
+ <select onchange="if (this.value) window.location.href=this.value">
+ <option value="">Navigate the docsâ¦</option>
+
+ <optgroup label="Overview">
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <option value="/docs/index.html">Background</option>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <option value="/docs/tutorial.html">Tutorial</option>
+
+
+
+
+
+
+
+
+
+ <option value="/docs/algebra.html">Algebra</option>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ </optgroup>
+
+ <optgroup label="Advanced">
+
+
+
+
+
+
+
+ <option value="/docs/adapter.html">Adapters</option>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <option value="/docs/avatica.html">Avatica</option>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <option value="/docs/stream.html">Streaming</option>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <option value="/docs/lattice.html">Lattices</option>
+
+
+
+
+
+
+
+
+
+
+
+
+ </optgroup>
+
+ <optgroup label="Reference">
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <option value="/docs/reference.html">SQL language</option>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <option value="/docs/model.html">JSON models</option>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <option value="/docs/howto.html">HOWTO</option>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ </optgroup>
+
+ <optgroup label="Meta">
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <option value="/docs/downloads.html">Downloads</option>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <option value="/docs/history.html">History</option>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <option value="/docs/contributing.html">Contributing</option>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <option value="/docs/api.html">API</option>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ </optgroup>
+
+ </select>
+</div>
+
+
+ <div class="unit four-fifths">
+ <article>
+ <h1>SQL language</h1>
+ <!--
+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.
+-->
+<p>## SQL constructs</p>
+
+<p>```SQL
+statement:
+ setStatement
+ | explain
+ | insert
+ | update
+ | merge
+ | delete
+ | query</p>
+
+<p>setStatement:
+ ALTER ( SYSTEM | SESSION ) SET identifier = expression</p>
+
+<p>explain:
+ EXPLAIN PLAN
+ [ WITH TYPE | WITH IMPLEMENTATION | WITHOUT IMPLEMENTATION ]
+ [ EXCLUDING ATTRIBUTES | INCLUDING [ ALL ] ATTRIBUTES ]
+ FOR ( insert | update | merge | delete | query )</p>
+
+<p>insert:
+ ( INSERT | UPSERT ) INTO tablePrimary
+ [ â(â column [, column ]* â)â ]
+ query</p>
+
+<p>update:
+ UPDATE tablePrimary
+ SET assign [, assign ]*
+ [ WHERE booleanExpression ]</p>
+
+<p>assign:
+ identifier â=â expression</p>
+
+<p>merge:
+ MERGE INTO tablePrimary [ [ AS ] alias ]
+ USING tablePrimary
+ ON booleanExpression
+ [ WHEN MATCHED THEN UPDATE SET assign [, assign ]* ]
+ [ WHEN NOT MATCHED THEN INSERT VALUES â(â value [ , value ]* â)â ]</p>
+
+<p>delete:
+ DELETE FROM tablePrimary [ [ AS ] alias ]
+ [ WHERE booleanExpression ]</p>
+
+<p>query:
+ [ WITH withItem [ , withItem ]* query ]
+ | {
+ select
+ | query UNION [ ALL ] query
+ | query EXCEPT query
+ | query INTERSECT query
+ }
+ [ ORDER BY orderItem [, orderItem ]* ]
+ [ LIMIT { count | ALL } ]
+ [ OFFSET start { ROW | ROWS } ]
+ [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ]</p>
+
+<p>withItem:
+ name
+ [ â(â column [, column ]* â)â ]
+ AS â(â query â)â</p>
+
+<p>orderItem:
+ expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]</p>
+
+<p>select:
+ SELECT [ STREAM ] [ ALL | DISTINCT ]
+ { * | projectItem [, projectItem ]* }
+ FROM tableExpression
+ [ WHERE booleanExpression ]
+ [ GROUP BY { groupItem [, groupItem ]* } ]
+ [ HAVING booleanExpression ]
+ [ WINDOW windowName AS windowSpec [, windowName AS windowSpec ]* ]</p>
+
+<p>projectItem:
+ expression [ [ AS ] columnAlias ]
+ | tableAlias . *</p>
+
+<p>tableExpression:
+ tableReference [, tableReference ]*
+ | tableExpression [ NATURAL ] [ LEFT | RIGHT | FULL ] JOIN tableExpression [ joinCondition ]</p>
+
+<p>joinCondition:
+ ON booleanExpression
+ | USING â(â column [, column ]* â)â</p>
+
+<p>tableReference:
+ [ LATERAL ]
+ tablePrimary
+ [ [ AS ] alias [ â(â columnAlias [, columnAlias ]* â)â ] ]</p>
+
+<p>tablePrimary:
+ [ TABLE ] [ [ catalogName . ] schemaName . ] tableName
+ | â(â query â)â
+ | values
+ | UNNEST â(â expression â)â
+ | â(â TABLE expression â)â</p>
+
+<p>values:
+ VALUES expression [, expression ]*</p>
+
+<p>groupItem:
+ expression
+ | â(â â)â
+ | â(â expression [, expression ]* â)â
+ | CUBE â(â expression [, expression ]* â)â
+ | ROLLUP â(â expression [, expression ]* â)â
+ | GROUPING SETS â(â groupItem [, groupItem ]* â)â</p>
+
+<p>windowRef:
+ windowName
+ | windowSpec</p>
+
+<p>windowSpec:
+ [ windowName ]
+ â(â
+ [ ORDER BY orderItem [, orderItem ]* ]
+ [ PARTITION BY expression [, expression ]* ]
+ [
+ RANGE numericOrIntervalExpression { PRECEDING | FOLLOWING }
+ | ROWS numericExpression { PRECEDING | FOLLOWING }
+ ]
+ â)â
+```</p>
+
+<p>In <em>merge</em>, at least one of the WHEN MATCHED and WHEN NOT MATCHED clauses must
+be present.</p>
+
+<p>In <em>orderItem</em>, if <em>expression</em> is a positive integer <em>n</em>, it denotes
+the <em>n</em>th item in the SELECT clause.</p>
+
+<p>An aggregate query is a query that contains a GROUP BY or a HAVING
+clause, or aggregate functions in the SELECT clause. In the SELECT,
+HAVING and ORDER BY clauses of an aggregate query, all expressions
+must be constant within the current group (that is, grouping constants
+as defined by the GROUP BY clause, or constants), or aggregate
+functions, or a combination of constants and aggregate
+functions. Aggregate and grouping functions may only appear in an
+aggregate query, and only in a SELECT, HAVING or ORDER BY clause.</p>
+
+<p>A scalar sub-query is a sub-query used as an expression. It can occur
+in most places where an expression can occur (such as the SELECT
+clause, WHERE clause, or as an argument to an aggregate
+function). If the sub-query returns no rows, the value is NULL; if it
+returns more than one row, it is an error.</p>
+
+<p>A sub-query can occur in the FROM clause of a query and also in IN
+and EXISTS expressions. A sub-query that occurs in IN and
+EXISTS expressions may be correlated; that is, refer to tables in
+the FROM clause of an enclosing query.</p>
+
+<h2 id="identifiers">Identifiers</h2>
+
+<p>Identifiers are the names of tables, columns and other metadata
+elements used in a SQL query.</p>
+
+<p>Unquoted identifiers, such as emp, must start with a letter and can
+only contain letters, digits, and underscores. They are implicitly
+converted to upper case.</p>
+
+<p>Quoted identifiers, such as âEmployee Nameâ, start and end with
+double quotes. They may contain virtually any character, including
+spaces and other punctuation. If you wish to include a double quote
+in an identifier, use another double quote to escape it, like this:
+âAn employee called ââFredââ.â</p>
+
+<p>In Calcite, matching identifiers to the name of the referenced object is
+case-sensitive. But remember that unquoted identifiers are implicitly
+converted to upper case before matching, and if the object it refers
+to was created using an unquoted identifier for its name, then its
+name will have been converted to upper case also.</p>
+
+<h2 id="data-types">Data types</h2>
+
+<h3 id="scalar-types">Scalar types</h3>
+
+<table>
+ <thead>
+ <tr>
+ <th>Data type</th>
+ <th>Description</th>
+ <th>Range and examples</th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>BOOLEAN</td>
+ <td>Logical values</td>
+ <td>Values: TRUE, FALSE, UNKNOWN</td>
+ </tr>
+ <tr>
+ <td>TINYINT</td>
+ <td>1 byte signed integer</td>
+ <td>Range is -255 to 256</td>
+ </tr>
+ <tr>
+ <td>SMALLINT</td>
+ <td>2 byte signed integer</td>
+ <td>Range is -32768 to 32767</td>
+ </tr>
+ <tr>
+ <td>INTEGER, INT</td>
+ <td>4 byte signed integer</td>
+ <td>Range is -2147483648 to 2147483647</td>
+ </tr>
+ <tr>
+ <td>BIGINT</td>
+ <td>8 byte signed integer</td>
+ <td>Range is -9223372036854775808 to 9223372036854775807</td>
+ </tr>
+ <tr>
+ <td>DECIMAL(p, s)</td>
+ <td>Fixed point</td>
+ <td>Example: 123.45 is a DECIMAL(5, 2) value.</td>
+ </tr>
+ <tr>
+ <td>NUMERIC</td>
+ <td>Fixed point</td>
+ <td>Â </td>
+ </tr>
+ <tr>
+ <td>REAL, FLOAT</td>
+ <td>4 byte floating point</td>
+ <td>6 decimal digits precision</td>
+ </tr>
+ <tr>
+ <td>DOUBLE</td>
+ <td>8 byte floating point</td>
+ <td>15 decimal digits precision</td>
+ </tr>
+ <tr>
+ <td>CHAR(n), CHARACTER(n)</td>
+ <td>Fixed-width character string</td>
+ <td>âHelloâ, ââ (empty string), _latin1âHelloâ, nâHelloâ, _UTF16âHelloâ, âHelloâ âthereâ (literal split into multiple parts)</td>
+ </tr>
+ <tr>
+ <td>VARCHAR(n), CHARACTER VARYING(n)</td>
+ <td>Variable-length character string</td>
+ <td>As CHAR(n)</td>
+ </tr>
+ <tr>
+ <td>BINARY(n)</td>
+ <td>Fixed-width binary string</td>
+ <td>xâ45F0ABâ, xââ (empty binary string), xâABâ âCDâ (multi-part binary string literal)</td>
+ </tr>
+ <tr>
+ <td>VARBINARY(n), BINARY VARYING(n)</td>
+ <td>Variable-length binary string</td>
+ <td>As BINARY(n)</td>
+ </tr>
+ <tr>
+ <td>DATE</td>
+ <td>Date</td>
+ <td>Example: DATE â1969-07-20â</td>
+ </tr>
+ <tr>
+ <td>TIME</td>
+ <td>Time of day</td>
+ <td>Example: TIME â20:17:40â</td>
+ </tr>
+ <tr>
+ <td>TIMESTAMP [ WITHOUT TIME ZONE ]</td>
+ <td>Date and time</td>
+ <td>Example: TIMESTAMP â1969-07-20 20:17:40â</td>
+ </tr>
+ <tr>
+ <td>TIMESTAMP WITH TIME ZONE</td>
+ <td>Date and time with time zone</td>
+ <td>Example: TIMESTAMP â1969-07-20 20:17:40 America/Los Angelesâ</td>
+ </tr>
+ <tr>
+ <td>INTERVAL timeUnit [ TO timeUnit ]</td>
+ <td>Date time interval</td>
+ <td>Examples: INTERVAL â1:5â YEAR TO MONTH, INTERVAL â45â DAY</td>
+ </tr>
+ <tr>
+ <td>Anchored interval</td>
+ <td>Date time interval</td>
+ <td>Example: (DATE â1969-07-20â, DATE â1972-08-29â)</td>
+ </tr>
+ </tbody>
+</table>
+
+<p>Where:
+<code>SQL
+timeUnit:
+ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND
+</code></p>
+
+<p>Note:
+* DATE, TIME and TIMESTAMP have no time zone. There is not even an implicit
+ time zone, such as UTC (as in Java) or the local time zone. It is left to
+ the user or application to supply a time zone.</p>
+
+<h3 id="non-scalar-types">Non-scalar types</h3>
+
+<table>
+ <thead>
+ <tr>
+ <th>Type</th>
+ <th>Description</th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>ANY</td>
+ <td>A value of an unknown type</td>
+ </tr>
+ <tr>
+ <td>ROW</td>
+ <td>Row with 1 or more columns</td>
+ </tr>
+ <tr>
+ <td>MAP</td>
+ <td>Collection of keys mapped to values</td>
+ </tr>
+ <tr>
+ <td>MULTISET</td>
+ <td>Unordered collection that may contain duplicates</td>
+ </tr>
+ <tr>
+ <td>ARRAY</td>
+ <td>Ordered, contiguous collection that may contain duplicates</td>
+ </tr>
+ <tr>
+ <td>CURSOR</td>
+ <td>Cursor over the result of executing a query</td>
+ </tr>
+ </tbody>
+</table>
+
+<h2 id="operators-and-functions">Operators and functions</h2>
+
+<h3 id="comparison-operators">Comparison operators</h3>
+
+<table>
+ <thead>
+ <tr>
+ <th>Operator syntax</th>
+ <th>Description</th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>value1 = value2</td>
+ <td>Equals</td>
+ </tr>
+ <tr>
+ <td>value1 <> value2</td>
+ <td>Not equal</td>
+ </tr>
+ <tr>
+ <td>value1 > value2</td>
+ <td>Greater than</td>
+ </tr>
+ <tr>
+ <td>value1 >= value2</td>
+ <td>Greater than or equal</td>
+ </tr>
+ <tr>
+ <td>value1 < value2</td>
+ <td>Less than</td>
+ </tr>
+ <tr>
+ <td>value1 <= value2</td>
+ <td>Less than or equal</td>
+ </tr>
+ <tr>
+ <td>value IS NULL</td>
+ <td>Whether <em>value</em> is null</td>
+ </tr>
+ <tr>
+ <td>value IS NOT NULL</td>
+ <td>Whether <em>value</em> is not null</td>
+ </tr>
+ <tr>
+ <td>value1 IS DISTINCT FROM value2</td>
+ <td>Whether two values are not equal, treating null values as the same</td>
+ </tr>
+ <tr>
+ <td>value1 IS NOT DISTINCT FROM value2</td>
+ <td>Whether two values are equal, treating null values as the same</td>
+ </tr>
+ <tr>
+ <td>value1 BETWEEN value2 AND value3</td>
+ <td>Whether <em>value1</em> is greater than or equal to <em>value2</em> and less than or equal to <em>value3</em></td>
+ </tr>
+ <tr>
+ <td>value1 NOT BETWEEN value2 AND value3</td>
+ <td>Whether <em>value1</em> is less than <em>value2</em> or greater than <em>value3</em></td>
+ </tr>
+ <tr>
+ <td>string1 LIKE string2 [ ESCAPE string3 ]</td>
+ <td>Whether <em>string1</em> matches pattern <em>string2</em></td>
+ </tr>
+ <tr>
+ <td>string1 NOT LIKE string2 [ ESCAPE string3 ]</td>
+ <td>Whether <em>string1</em> does not match pattern <em>string2</em></td>
+ </tr>
+ <tr>
+ <td>string1 SIMILAR TO string2 [ ESCAPE string3 ]</td>
+ <td>Whether <em>string1</em> matches regular expression <em>string2</em></td>
+ </tr>
+ <tr>
+ <td>string1 NOT SIMILAR TO string2 [ ESCAPE string3 ]</td>
+ <td>Whether <em>string1</em> does not match regular expression <em>string2</em></td>
+ </tr>
+ <tr>
+ <td>value IN (value [, value]* )</td>
+ <td>Whether <em>value</em> is equal to a value in a list</td>
+ </tr>
+ <tr>
+ <td>value NOT IN (value [, value]* )</td>
+ <td>Whether <em>value</em> is not equal to every value in a list</td>
+ </tr>
+ <tr>
+ <td>value IN (sub-query)</td>
+ <td>Whether <em>value</em> is equal to a row returned by <em>sub-query</em></td>
+ </tr>
+ <tr>
+ <td>value NOT IN (sub-query)</td>
+ <td>Whether <em>value</em> is not equal to every row returned by <em>sub-query</em></td>
+ </tr>
+ <tr>
+ <td>EXISTS (sub-query)</td>
+ <td>Whether <em>sub-query</em> returns at least one row</td>
+ </tr>
+ </tbody>
+</table>
+
+<h3 id="logical-operators">Logical operators</h3>
+
+<table>
+ <thead>
+ <tr>
+ <th>Operator syntax</th>
+ <th>Description</th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>boolean1 OR boolean2</td>
+ <td>Whether <em>boolean1</em> is TRUE or <em>boolean2</em> is TRUE</td>
+ </tr>
+ <tr>
+ <td>boolean1 AND boolean2</td>
+ <td>Whether <em>boolean1</em> and <em>boolean2</em> are both TRUE</td>
+ </tr>
+ <tr>
+ <td>NOT boolean</td>
+ <td>Whether <em>boolean</em> is not TRUE; returns UNKNOWN if <em>boolean</em> is UNKNOWN</td>
+ </tr>
+ <tr>
+ <td>boolean IS FALSE</td>
+ <td>Whether <em>boolean</em> is FALSE; returns FALSE if <em>boolean</em> is UNKNOWN</td>
+ </tr>
+ <tr>
+ <td>boolean IS NOT FALSE</td>
+ <td>Whether <em>boolean</em> is not FALSE; returns TRUE if <em>boolean</em> is UNKNOWN</td>
+ </tr>
+ <tr>
+ <td>boolean IS TRUE</td>
+ <td>Whether <em>boolean</em> is TRUE; returns FALSE if <em>boolean</em> is UNKNOWN</td>
+ </tr>
+ <tr>
+ <td>boolean IS NOT TRUE</td>
+ <td>Whether <em>boolean</em> is not TRUE; returns TRUE if <em>boolean</em> is UNKNOWN</td>
+ </tr>
+ <tr>
+ <td>boolean IS UNKNOWN</td>
+ <td>Whether <em>boolean</em> is UNKNOWN</td>
+ </tr>
+ <tr>
+ <td>boolean IS NOT UNKNOWN</td>
+ <td>Whether <em>boolean</em> is not UNKNOWN</td>
+ </tr>
+ </tbody>
+</table>
+
+<h3 id="arithmetic-operators-and-functions">Arithmetic operators and functions</h3>
+
+<table>
+ <thead>
+ <tr>
+ <th>Operator syntax</th>
+ <th>Description</th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>+ numeric</td>
+ <td>Returns <em>numeric</em></td>
+ </tr>
+ <tr>
+ <td>- numeric</td>
+ <td>Returns negative <em>numeric</em></td>
+ </tr>
+ <tr>
+ <td>numeric1 + numeric2</td>
+ <td>Returns <em>numeric1</em> plus <em>numeric2</em></td>
+ </tr>
+ <tr>
+ <td>numeric1 - numeric2</td>
+ <td>Returns <em>numeric1</em> minus <em>numeric2</em></td>
+ </tr>
+ <tr>
+ <td>numeric1 * numeric2</td>
+ <td>Returns <em>numeric1</em> multiplied by <em>numeric2</em></td>
+ </tr>
+ <tr>
+ <td>numeric1 / numeric2</td>
+ <td>Returns <em>numeric1</em> divided by <em>numeric2</em></td>
+ </tr>
+ <tr>
+ <td>POWER(numeric1, numeric2)</td>
+ <td>Returns <em>numeric1</em> raised to the power of <em>numeric2</em></td>
+ </tr>
+ <tr>
+ <td>ABS(numeric)</td>
+ <td>Returns the absolute value of <em>numeric</em></td>
+ </tr>
+ <tr>
+ <td>MOD(numeric, numeric)</td>
+ <td>Returns the remainder (modulus) of <em>numeric1</em> divided by <em>numeric2</em>. The result is negative only if <em>numeric1</em> is negative</td>
+ </tr>
+ <tr>
+ <td>SQRT(numeric)</td>
+ <td>Returns the square root of <em>numeric</em></td>
+ </tr>
+ <tr>
+ <td>LN(numeric)</td>
+ <td>Returns the natural logarithm (base <em>e</em>) of <em>numeric</em></td>
+ </tr>
+ <tr>
+ <td>LOG10(numeric)</td>
+ <td>Returns the base 10 logarithm of <em>numeric</em></td>
+ </tr>
+ <tr>
+ <td>EXP(numeric)</td>
+ <td>Returns <em>e</em> raised to the power of <em>numeric</em></td>
+ </tr>
+ <tr>
+ <td>CEIL(numeric)</td>
+ <td>Rounds <em>numeric</em> up, and returns the smallest number that is greater than or equal to <em>numeric</em></td>
+ </tr>
+ <tr>
+ <td>FLOOR(numeric)</td>
+ <td>Rounds <em>numeric</em> down, and returns the largest number that is less than or equal to <em>numeric</em></td>
+ </tr>
+ </tbody>
+</table>
+
+<h3 id="character-string-operators-and-functions">Character string operators and functions</h3>
+
+<table>
+ <thead>
+ <tr>
+ <th>Operator syntax</th>
+ <th>Description</th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>string || string</td>
+ <td>Concatenates two character strings.</td>
+ </tr>
+ <tr>
+ <td>CHAR_LENGTH(string)</td>
+ <td>Returns the number of characters in a character string</td>
+ </tr>
+ <tr>
+ <td>CHARACTER_LENGTH(string)</td>
+ <td>As CHAR_LENGTH(<em>string</em>)</td>
+ </tr>
+ <tr>
+ <td>UPPER(string)</td>
+ <td>Returns a character string converted to upper case</td>
+ </tr>
+ <tr>
+ <td>LOWER(string)</td>
+ <td>Returns a character string converted to lower case</td>
+ </tr>
+ <tr>
+ <td>POSITION(string1 IN string2)</td>
+ <td>Returns the position of the first occurrence of <em>string1</em> in <em>string2</em></td>
+ </tr>
+ <tr>
+ <td>TRIM( { BOTH ;| LEADING ;| TRAILING } string1 FROM string2)</td>
+ <td>Removes the longest string containing only the characters in <em>string1</em> from the start/end/both ends of <em>string1</em></td>
+ </tr>
+ <tr>
+ <td>OVERLAY(string1 PLACING string2 FROM integer [ FOR integer2 ])</td>
+ <td>Replaces a substring of <em>string1</em> with <em>string2</em></td>
+ </tr>
+ <tr>
+ <td>SUBSTRING(string FROM integer)</td>
+ <td>Returns a substring of a character string starting at a given point.</td>
+ </tr>
+ <tr>
+ <td>SUBSTRING(string FROM integer FOR integer)</td>
+ <td>Returns a substring of a character string starting at a given point with a given length.</td>
+ </tr>
+ <tr>
+ <td>INITCAP(string)</td>
+ <td>Returns <em>string</em> with the first letter of each word converter to upper case and the rest to lower case. Words are sequences of alphanumeric characters separated by non-alphanumeric characters.</td>
+ </tr>
+ </tbody>
+</table>
+
+<p>Not implemented:
+* SUBSTRING(string FROM regexp FOR regexp)</p>
+
+<h3 id="binary-string-operators-and-functions">Binary string operators and functions</h3>
+
+<table>
+ <thead>
+ <tr>
+ <th>Operator syntax</th>
+ <th>Description</th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>binary || binary</td>
+ <td>Concatenates two binary strings.</td>
+ </tr>
+ <tr>
+ <td>POSITION(binary1 IN binary2)</td>
+ <td>Returns the position of the first occurrence of <em>binary1</em> in <em>binary2</em></td>
+ </tr>
+ <tr>
+ <td>OVERLAY(binary1 PLACING binary2 FROM integer [ FOR integer2 ])</td>
+ <td>Replaces a substring of <em>binary1</em> with <em>binary2</em></td>
+ </tr>
+ <tr>
+ <td>SUBSTRING(binary FROM integer)</td>
+ <td>Returns a substring of <em>binary</em> starting at a given point</td>
+ </tr>
+ <tr>
+ <td>SUBSTRING(binary FROM integer FOR integer)</td>
+ <td>Returns a substring of <em>binary</em> starting at a given point with a given length</td>
+ </tr>
+ </tbody>
+</table>
+
+<h3 id="datetime-functions">Date/time functions</h3>
+
+<table>
+ <thead>
+ <tr>
+ <th>Operator syntax</th>
+ <th>Description</th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>LOCALTIME</td>
+ <td>Returns the current date and time in the session time zone in a value of datatype TIME</td>
+ </tr>
+ <tr>
+ <td>LOCALTIME(precision)</td>
+ <td>Returns the current date and time in the session time zone in a value of datatype TIME, with <em>precision</em> digits of precision</td>
+ </tr>
+ <tr>
+ <td>LOCALTIMESTAMP</td>
+ <td>Returns the current date and time in the session time zone in a value of datatype TIMESTAMP</td>
+ </tr>
+ <tr>
+ <td>LOCALTIMESTAMP(precision)</td>
+ <td>Returns the current date and time in the session time zone in a value of datatype TIMESTAMP, with <em>precision</em> digits of precision</td>
+ </tr>
+ <tr>
+ <td>CURRENT_TIME</td>
+ <td>Returns the current time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE</td>
+ </tr>
+ <tr>
+ <td>CURRENT_DATE</td>
+ <td>Returns the current date in the session time zone, in a value of datatype DATE</td>
+ </tr>
+ <tr>
+ <td>CURRENT_TIMESTAMP</td>
+ <td>Returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE</td>
+ </tr>
+ <tr>
+ <td>EXTRACT(timeUnit FROM datetime)</td>
+ <td>Extracts and returns the value of a specified datetime field from a datetime value expression</td>
+ </tr>
+ <tr>
+ <td>FLOOR(datetime TO timeUnit)</td>
+ <td>Rounds <em>datetime</em> down to <em>timeUnit</em></td>
+ </tr>
+ <tr>
+ <td>CEIL(datetime TO timeUnit)</td>
+ <td>Rounds <em>datetime</em> up to <em>timeUnit</em></td>
+ </tr>
+ </tbody>
+</table>
+
+<p>Not implemented:
+* EXTRACT(timeUnit FROM interval)
+* CEIL(interval)
+* FLOOR(interval)
+* datetime - datetime timeUnit [ TO timeUnit ]
+* interval OVERLAPS interval
+* + interval
+* - interval
+* interval + interval
+* interval - interval
+* interval / interval
+* datetime + interval
+* datetime - interval</p>
+
+<h3 id="system-functions">System functions</h3>
+
+<table>
+ <thead>
+ <tr>
+ <th>Operator syntax</th>
+ <th>Description</th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>USER</td>
+ <td>Equivalent to CURRENT_USER</td>
+ </tr>
+ <tr>
+ <td>CURRENT_USER</td>
+ <td>User name of current execution context</td>
+ </tr>
+ <tr>
+ <td>SESSION_USER</td>
+ <td>Session user name</td>
+ </tr>
+ <tr>
+ <td>SYSTEM_USER</td>
+ <td>Returns the name of the current data store user as identified by the operating system</td>
+ </tr>
+ <tr>
+ <td>CURRENT_PATH</td>
+ <td>Returns a character string representing the current lookup scope for references to user-defined routines and types</td>
+ </tr>
+ <tr>
+ <td>CURRENT_ROLE</td>
+ <td>Returns the current active role</td>
+ </tr>
+ </tbody>
+</table>
+
+<h3 id="conditional-functions-and-operators">Conditional functions and operators</h3>
+
+<table>
+ <thead>
+ <tr>
+ <th>Operator syntax</th>
+ <th>Description</th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>CASE value<br />WHEN value1 [, value11 ]* THEN result1<br />[ WHEN valueN [, valueN1 ]* THEN resultN ]*<br />[ ELSE resultZ ]<br /> END</td>
+ <td>Simple case</td>
+ </tr>
+ <tr>
+ <td>CASE<br />WHEN condition1 THEN result1<br />[ WHEN conditionN THEN resultN ]*<br />[ ELSE resultZ ]<br />END</td>
+ <td>Searched case</td>
+ </tr>
+ <tr>
+ <td>NULLIF(value, value)</td>
+ <td>Returns NULL if the values are the same. For example, <code>NULLIF(5, 5)</code> returns NULL; <code>NULLIF(5, 0)</code> returns 5.</td>
+ </tr>
+ <tr>
+ <td>COALESCE(value, value [, value]* )</td>
+ <td>Provides a value if the first value is null. For example, <code>COALESCE(NULL, 5)</code> returns 5.</td>
+ </tr>
+ </tbody>
+</table>
+
+<h3 id="type-conversion">Type conversion</h3>
+
+<table>
+ <thead>
+ <tr>
+ <th>Operator syntax</th>
+ <th>Description</th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>CAST(value AS type)</td>
+ <td>Converts a value to a given type.</td>
+ </tr>
+ </tbody>
+</table>
+
+<h3 id="value-constructors">Value constructors</h3>
+
+<table>
+ <thead>
+ <tr>
+ <th>Operator syntax</th>
+ <th>Description</th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>ROW (value [, value]* )</td>
+ <td>Creates a row from a list of values.</td>
+ </tr>
+ <tr>
+ <td>(value [, value]* )</td>
+ <td>Creates a row from a list of values.</td>
+ </tr>
+ <tr>
+ <td>map [ key ]</td>
+ <td>Returns the element of a map with a particular key.</td>
+ </tr>
+ <tr>
+ <td>array [ index ]</td>
+ <td>Returns the element at a particular location in an array.</td>
+ </tr>
+ <tr>
+ <td>ARRAY [ value [, value ]* ]</td>
+ <td>Creates an array from a list of values.</td>
+ </tr>
+ <tr>
+ <td>MAP [ key, value [, key, value ]* ]</td>
+ <td>Creates a map from a list of key-value pairs.</td>
+ </tr>
+ </tbody>
+</table>
+
+<h3 id="collection-functions">Collection functions</h3>
+
+<table>
+ <thead>
+ <tr>
+ <th>Operator syntax</th>
+ <th>Description</th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>ELEMENT(value)</td>
+ <td>Returns the sole element of a array or multiset; null if the collection is empty; throws if it has more than one element.</td>
+ </tr>
+ <tr>
+ <td>CARDINALITY(value)</td>
+ <td>Returns the number of elements in an array or multiset.</td>
+ </tr>
+ </tbody>
+</table>
+
+<p>See also: UNNEST relational operator converts a collection to a relation.</p>
+
+<h3 id="jdbc-function-escape">JDBC function escape</h3>
+
+<h4 id="numeric">Numeric</h4>
+
+<table>
+ <thead>
+ <tr>
+ <th>Operator syntax</th>
+ <th>Description</th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>{fn LOG10(numeric)}</td>
+ <td>Returns the base-10 logarithm of <em>numeric</em></td>
+ </tr>
+ <tr>
+ <td>{fn POWER(numeric1, numeric2)}</td>
+ <td>Returns <em>numeric1</em> raised to the power of <em>numeric2</em></td>
+ </tr>
+ </tbody>
+</table>
+
+<p>Not implemented:
+* {fn ABS(numeric)} - Returns the absolute value of <em>numeric</em>
+* {fn ACOS(numeric)} - Returns the arc cosine of <em>numeric</em>
+* {fn ASIN(numeric)} - Returns the arc sine of <em>numeric</em>
+* {fn ATAN(numeric)} - Returns the arc tangent of <em>numeric</em>
+* {fn ATAN2(numeric, numeric)}
+* {fn CEILING(numeric)} - Rounds <em>numeric</em> up, and returns the smallest number that is greater than or equal to <em>numeric</em>
+* {fn COS(numeric)} - Returns the cosine of <em>numeric</em>
+* {fn COT(numeric)}
+* {fn DEGREES(numeric)} - Converts <em>numeric</em> from radians to degrees
+* {fn EXP(numeric)} - Returns <em>e</em> raised to the power of <em>numeric</em>
+* {fn FLOOR(numeric)} - Rounds <em>numeric</em> down, and returns the largest number that is less than or equal to <em>numeric</em>
+* {fn LOG(numeric)} - Returns the natural logarithm (base <em>e</em>) of <em>numeric</em>
+* {fn MOD(numeric1, numeric2)} - Returns the remainder (modulus) of <em>numeric1</em> divided by <em>numeric2</em>. The result is negative only if <em>numeric1</em> is negative
+* {fn PI()} - Returns a value that is closer than any other value to <em>pi</em>
+* {fn RADIANS(numeric)} - Converts <em>numeric</em> from degrees to radians
+* {fn RAND(numeric)}
+* {fn ROUND(numeric, numeric)}
+* {fn SIGN(numeric)}
+* {fn SIN(numeric)} - Returns the sine of <em>numeric</em>
+* {fn SQRT(numeric)} - Returns the square root of <em>numeric</em>
+* {fn TAN(numeric)} - Returns the tangent of <em>numeric</em>
+* {fn TRUNCATE(numeric, numeric)}</p>
+
+<h4 id="string">String</h4>
+
+<table>
+ <thead>
+ <tr>
+ <th>Operator syntax</th>
+ <th>Description</th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>{fn LOCATE(string1, string2)}</td>
+ <td>Returns the position in <em>string2</em> of the first occurrence of <em>string1</em>. Searches from the beginning of the second CharacterExpression, unless the startIndex parameter is specified.</td>
+ </tr>
+ <tr>
+ <td>{fn INSERT(string1, start, length, string2)}</td>
+ <td>Inserts <em>string2</em> into a slot in <em>string1</em></td>
+ </tr>
+ <tr>
+ <td>{fn LCASE(string)}</td>
+ <td>Returns a string in which all alphabetic characters in <em>string</em> have been converted to lower case</td>
+ </tr>
+ </tbody>
+</table>
+
+<p>Not implemented:
+* {fn ASCII(string)} - Convert a single-character string to the corresponding ASCII code, an integer between 0 and 255
+* {fn CHAR(string)}
+* {fn CONCAT(character, character)} - Returns the concatenation of character strings
+* {fn DIFFERENCE(string, string)}
+* {fn LEFT(string, integer)}
+* {fn LENGTH(string)}
+* {fn LOCATE(string1, string2 [, integer])} - Returns the position in <em>string2</em> of the first occurrence of <em>string1</em>. Searches from the beginning of <em>string2</em>, unless <em>integer</em> is specified.
+* {fn LTRIM(string)}
+* {fn REPEAT(string, integer)}
+* {fn REPLACE(string, string, string)}
+* {fn RIGHT(string, integer)}
+* {fn RTRIM(string)}
+* {fn SOUNDEX(string)}
+* {fn SPACE(integer)}
+* {fn SUBSTRING(string, integer, integer)}
+* {fn UCASE(string)} - Returns a string in which all alphabetic characters in <em>string</em> have been converted to upper case</p>
+
+<h4 id="datetime">Date/time</h4>
+
+<p>Not implemented:
+* {fn CURDATE()}
+* {fn CURTIME()}
+* {fn DAYNAME(date)}
+* {fn DAYOFMONTH(date)}
+* {fn DAYOFWEEK(date)}
+* {fn DAYOFYEAR(date)}
+* {fn HOUR(time)}
+* {fn MINUTE(time)}
+* {fn MONTH(date)}
+* {fn MONTHNAME(date)}
+* {fn NOW()}
+* {fn QUARTER(date)}
+* {fn SECOND(time)}
+* {fn TIMESTAMPADD(interval, count, timestamp)}
+* {fn TIMESTAMPDIFF(interval, timestamp, timestamp)}
+* {fn WEEK(date)}
+* {fn YEAR(date)}</p>
+
+<h4 id="system">System</h4>
+
+<p>Not implemented:
+* {fn DATABASE()}
+* {fn IFNULL(value, value)}
+* {fn USER(value, value)}
+* {fn CONVERT(value, type)}</p>
+
+<h3 id="aggregate-functions">Aggregate functions</h3>
+
+<p>Syntax:</p>
+
+<p><code>SQL
+aggregateCall:
+ agg( [ DISTINCT ] value [, value]* ) [ FILTER ( WHERE condition ) ]
+ | agg(*) [ FILTER ( WHERE condition ) ]
+</code></p>
+
+<p>If <code>FILTER</code> is present, the aggregate function only considers rows for which
+<em>condition</em> evaluates to TRUE.</p>
+
+<p>If <code>DISTINCT</code> is present, duplicate argument values are eliminated before being
+passed to the aggregate function.</p>
+
+<table>
+ <thead>
+ <tr>
+ <th>Operator syntax</th>
+ <th>Description</th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>COUNT( [ DISTINCT ] value [, value]* )</td>
+ <td>Returns the number of input rows for which <em>value</em> is not null (wholly not null if <em>value</em> is composite)</td>
+ </tr>
+ <tr>
+ <td>COUNT(*)</td>
+ <td>Returns the number of input rows</td>
+ </tr>
+ <tr>
+ <td>AVG( [ DISTINCT ] numeric)</td>
+ <td>Returns the average (arithmetic mean) of <em>numeric</em> across all input values</td>
+ </tr>
+ <tr>
+ <td>SUM( [ DISTINCT ] numeric)</td>
+ <td>Returns the sum of <em>numeric</em> across all input values</td>
+ </tr>
+ <tr>
+ <td>MAX( [ DISTINCT ] value)</td>
+ <td>Returns the maximum value of <em>value</em> across all input values</td>
+ </tr>
+ <tr>
+ <td>MIN( [ DISTINCT ] value)</td>
+ <td>Returns the minimum value of <em>value</em> across all input values</td>
+ </tr>
+ <tr>
+ <td>STDDEV_POP( [ DISTINCT ] numeric)</td>
+ <td>Returns the population standard deviation of <em>numeric</em> across all input values</td>
+ </tr>
+ <tr>
+ <td>STDDEV_SAMP( [ DISTINCT ] numeric)</td>
+ <td>Returns the sample standard deviation of <em>numeric</em> across all input values</td>
+ </tr>
+ <tr>
+ <td>VAR_POP( [ DISTINCT ] value)</td>
+ <td>Returns the population variance (square of the population standard deviation) of <em>numeric</em> across all input values</td>
+ </tr>
+ <tr>
+ <td>VAR_SAMP( [ DISTINCT ] numeric)</td>
+ <td>Returns the sample variance (square of the sample standard deviation) of <em>numeric</em> across all input values</td>
+ </tr>
+ <tr>
+ <td>COVAR_POP(numeric1, numeric2)</td>
+ <td>Returns the population covariance of the pair (<em>numeric1</em>, <em>numeric2</em>) across all input values</td>
+ </tr>
+ <tr>
+ <td>COVAR_SAMP(numeric1, numeric2)</td>
+ <td>Returns the sample covariance of the pair (<em>numeric1</em>, <em>numeric2</em>) across all input values</td>
+ </tr>
+ <tr>
+ <td>REGR_SXX(numeric1, numeric2)</td>
+ <td>Returns the sum of squares of the dependent expression in a linear regression model</td>
+ </tr>
+ <tr>
+ <td>REGR_SYY(numeric1, numeric2)</td>
+ <td>Returns the sum of squares of the independent expression in a linear regression model</td>
+ </tr>
+ </tbody>
+</table>
+
+<p>Not implemented:
+* REGR_AVGX(numeric1, numeric2)
+* REGR_AVGY(numeric1, numeric2)
+* REGR_COUNT(numeric1, numeric2)
+* REGR_INTERCEPT(numeric1, numeric2)
+* REGR_R2(numeric1, numeric2)
+* REGR_SLOPE(numeric1, numeric2)
+* REGR_SXY(numeric1, numeric2)</p>
+
+<h3 id="window-functions">Window functions</h3>
+
+<table>
+ <thead>
+ <tr>
+ <th>Operator syntax</th>
+ <th>Description</th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>COUNT(value [, value ]* ) OVER window</td>
+ <td>Returns the number of rows in <em>window</em> for which <em>value</em> is not null (wholly not null if <em>value</em> is composite)</td>
+ </tr>
+ <tr>
+ <td>COUNT(*) OVER window</td>
+ <td>Returns the number of rows in <em>window</em></td>
+ </tr>
+ <tr>
+ <td>AVG(numeric) OVER window</td>
+ <td>Returns the average (arithmetic mean) of <em>numeric</em> across all values in <em>window</em></td>
+ </tr>
+ <tr>
+ <td>SUM(numeric) OVER window</td>
+ <td>Returns the sum of <em>numeric</em> across all values in <em>window</em></td>
+ </tr>
+ <tr>
+ <td>MAX(value) OVER window</td>
+ <td>Returns the maximum value of <em>value</em> across all values in <em>window</em></td>
+ </tr>
+ <tr>
+ <td>MIN(value) OVER window</td>
+ <td>Returns the minimum value of <em>value</em> across all values in <em>window</em></td>
+ </tr>
+ <tr>
+ <td>RANK() OVER window</td>
+ <td>Returns the rank of the current row with gaps; same as ROW_NUMBER of its first peer</td>
+ </tr>
+ <tr>
+ <td>DENSE_RANK() OVER window</td>
+ <td>Returns the rank of the current row without gaps; this function counts peer groups</td>
+ </tr>
+ <tr>
+ <td>ROW_NUMBER() OVER window</td>
+ <td>Returns the number of the current row within its partition, counting from 1</td>
+ </tr>
+ <tr>
+ <td>FIRST_VALUE(value) OVER window</td>
+ <td>Returns <em>value</em> evaluated at the row that is the first row of the window frame</td>
+ </tr>
+ <tr>
+ <td>LAST_VALUE(value) OVER window</td>
+ <td>Returns <em>value</em> evaluated at the row that is the last row of the window frame</td>
+ </tr>
+ <tr>
+ <td>LEAD(value, offset, default) OVER window</td>
+ <td>Returns <em>value</em> evaluated at the row that is <em>offset</em> rows after the current row within the partition; if there is no such row, instead returns <em>default</em>. Both <em>offset</em> and <em>default</em> are evaluated with respect to the current row. If omitted, <em>offset</em> defaults to 1 and <em>default</em> to NULL</td>
+ </tr>
+ <tr>
+ <td>LAG(value, offset, default) OVER window</td>
+ <td>Returns <em>value</em> evaluated at the row that is <em>offset</em> rows before the current row within the partition; if there is no such row, instead returns <em>default</em>. Both <em>offset</em> and <em>default</em> are evaluated with respect to the current row. If omitted, <em>offset</em> defaults to 1 and <em>default</em> to NULL</td>
+ </tr>
+ <tr>
+ <td>NTILE(value) OVER window</td>
+ <td>Returns an integer ranging from 1 to <em>value</em>, dividing the partition as equally as possible</td>
+ </tr>
+ </tbody>
+</table>
+
+<p>Not implemented:
+* COUNT(DISTINCT value) OVER window
+* FIRST_VALUE(value) IGNORE NULLS OVER window
+* LAST_VALUE(value) IGNORE NULLS OVER window
+* PERCENT_RANK(value) OVER window
+* CUME_DIST(value) OVER window
+* NTH_VALUE(value, nth) OVER window</p>
+
+<h3 id="grouping-functions">Grouping functions</h3>
+
+<table>
+ <thead>
+ <tr>
+ <th>Operator syntax</th>
+ <th>Description</th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>GROUPING(expression)</td>
+ <td>Returns 1 if expression is rolled up in the current rowâs grouping set, 0 otherwise</td>
+ </tr>
+ <tr>
+ <td>GROUP_ID()</td>
+ <td>Returns an integer that uniquely identifies the combination of grouping keys</td>
+ </tr>
+ <tr>
+ <td>GROUPING_ID(expression [, expression ] * )</td>
+ <td>Returns a bit vector of the given grouping expressions</td>
+ </tr>
+ </tbody>
+</table>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <div class="section-nav">
+ <div class="left align-right">
+
+
+
+ <a href="/docs/lattice.html" class="prev">Back</a>
+
+ </div>
+ <div class="right align-left">
+
+
+
+ <a href="/docs/model.html" class="next">Next</a>
+
+ </div>
+ </div>
+ <div class="clear"></div>
+
+
+ </article>
+ </div>
+
+ <div class="unit one-fifth hide-on-mobiles">
+ <aside>
+
+ <h4>Overview</h4>
+
+
+<ul>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/index.html">Background</a></li>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/tutorial.html">Tutorial</a></li>
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/algebra.html">Algebra</a></li>
+
+
+
+</ul>
+
+
+ <h4>Advanced</h4>
+
+
+<ul>
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/adapter.html">Adapters</a></li>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/avatica.html">Avatica</a></li>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/stream.html">Streaming</a></li>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/lattice.html">Lattices</a></li>
+
+
+
+</ul>
+
+
+ <h4>Reference</h4>
+
+
+<ul>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class="current"><a href="/docs/reference.html">SQL language</a></li>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/model.html">JSON models</a></li>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/howto.html">HOWTO</a></li>
+
+
+
+</ul>
+
+
+ <h4>Meta</h4>
+
+
+<ul>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/downloads.html">Downloads</a></li>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/history.html">History</a></li>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/contributing.html">Contributing</a></li>
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ <li class=""><a href="/docs/api.html">API</a></li>
+
+
+
+</ul>
+
+
+ </aside>
+</div>
+
+
+ <div class="clear"></div>
+
+ </div>
+ </section>
+
+
+ <footer role="contentinfo">
+ <p>The contents of this website are © 2015
+ <a href="https://www.apache.org/">Apache Software Foundation</a>
+ under the terms of
+ the <a href="https://www.apache.org/licenses/LICENSE-2.0.html">
+ Apache License v2</a>. Apache Calcite and its logo are
+ trademarks of the Apache Software Foundation.</p>
+
+ <p>Apache Calcite is an effort undergoing incubation at The Apache
+ Software Foundation (ASF), sponsored by the Apache
+ Incubator. Incubation is required of all newly accepted projects
+ until a further review indicates that the infrastructure,
+ communications, and decision making process have stabilized in a
+ manner consistent with other successful ASF projects. While
+ incubation status is not necessarily a reflection of the
+ completeness or stability of the code, it does indicate that the
+ project has yet to be fully endorsed by the ASF.</p>
+
+</footer>
+
+ <script>
+ var anchorForId = function (id) {
+ var anchor = document.createElement("a");
+ anchor.className = "header-link";
+ anchor.href = "#" + id;
+ anchor.innerHTML = "<span class=\"sr-only\">Permalink</span><i class=\"fa fa-link\"></i>";
+ anchor.title = "Permalink";
+ return anchor;
+ };
+
+ var linkifyAnchors = function (level, containingElement) {
+ var headers = containingElement.getElementsByTagName("h" + level);
+ for (var h = 0; h < headers.length; h++) {
+ var header = headers[h];
+
+ if (typeof header.id !== "undefined" && header.id !== "") {
+ header.appendChild(anchorForId(header.id));
+ }
+ }
+ };
+
+ document.onreadystatechange = function () {
+ if (this.readyState === "complete") {
+ var contentBlock = document.getElementsByClassName("docs")[0] || document.getElementsByClassName("news")[0];
+ if (!contentBlock) {
+ return;
+ }
+ for (var level = 1; level <= 6; level++) {
+ linkifyAnchors(level, contentBlock);
+ }
+ }
+ };
+</script>
+
+
+</body>
+</html>