You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@flink.apache.org by xc...@apache.org on 2018/08/14 07:20:40 UTC
[flink] branch master updated: [FLINK-9977] [table] [docs] Refine
the SQL/Table built-in function docs.
This is an automated email from the ASF dual-hosted git repository.
xccui pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/flink.git
The following commit(s) were added to refs/heads/master by this push:
new 5ecdfaa [FLINK-9977] [table] [docs] Refine the SQL/Table built-in function docs.
5ecdfaa is described below
commit 5ecdfaa6c1f52a424de7a6bc01c824a0d1f85bf3
Author: Xingcan Cui <xc...@apache.org>
AuthorDate: Sat Jul 28 23:55:44 2018 +0800
[FLINK-9977] [table] [docs] Refine the SQL/Table built-in function docs.
This closes #6535.
---
docs/dev/table/functions.md | 5131 ++++++++++++++++++++
docs/dev/table/sql.md | 1797 -------
docs/dev/table/tableApi.md | 3146 ------------
.../flink/table/api/scala/expressionDsl.scala | 6 +-
.../table/expressions/SqlExpressionTest.scala | 133 +-
5 files changed, 5214 insertions(+), 4999 deletions(-)
diff --git a/docs/dev/table/functions.md b/docs/dev/table/functions.md
new file mode 100644
index 0000000..00a9605
--- /dev/null
+++ b/docs/dev/table/functions.md
@@ -0,0 +1,5131 @@
+---
+title: "Built-In Functions"
+nav-parent_id: tableapi
+nav-pos: 45
+---
+<!--
+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.
+-->
+
+Flink Table API & SQL provides users with a set of built-in functions for data transformations. This page gives a brief overview of them.
+If a function that you need is not supported yet, you can implement a <a href="udfs.html">user-defined function</a>.
+If you think that the function is general enough, please <a href="https://issues.apache.org/jira/secure/CreateIssue!default.jspa">open a Jira issue</a> for it with a detailed description.
+
+* This will be replaced by the TOC
+{:toc}
+
+Scalar Functions
+-----------------------------
+The scalar functions take zero, one or more values as the input and return a single value as the result.
+
+### Comparison Functions
+
+<div class="codetabs" markdown="1">
+
+<div data-lang="SQL" markdown="1">
+ <table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Comparison functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td>
+ {% highlight text %}
+value1 = value2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>value1</i> is equal to <i>value2</i>; returns UNKNOWN if <i>value1</i> or <i>value2</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+value1 <> value2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>value1</i> is not equal to <i>value2</i>; returns UNKNOWN if <i>value1</i> or <i>value2</i> is NULL. </p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+value1 > value2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>value1</i> is greater than <i>value2</i>; returns UNKNOWN if <i>value1</i> or <i>value2</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+value1 >= value2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>value1</i> is greater than or equal to <i>value2</i>; returns UNKNOWN if <i>value1</i> or <i>value2</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+value1 < value2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>value1</i> is less than <i>value2</i>; returns UNKNOWN if <i>value1</i> or <i>value2</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+value1 <= value2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>value1</i> is less than or equal to <i>value2</i>; returns UNKNOWN if <i>value1</i> or <i>value2</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+value IS NULL
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>value</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+value IS NOT NULL
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>value</i> is not NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+value1 IS DISTINCT FROM value2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if two values are not equal. NULL values are treated as identical here.</p>
+ <p>E.g., <code>1 IS DISTINCT FROM NULL</code> returns TRUE;
+ <code>NULL IS DISTINCT FROM NULL</code> returns FALSE.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+value1 IS NOT DISTINCT FROM value2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if two values are equal. NULL values are treated as identical here.</p>
+ <p>E.g., <code>1 IS NOT DISTINCT FROM NULL</code> returns FALSE;
+ <code>NULL IS NOT DISTINCT FROM NULL</code> returns TRUE.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+value1 BETWEEN [ ASYMMETRIC | SYMMETRIC ] value2 AND value3
+{% endhighlight %}
+ </td>
+ <td>
+ <p>By default (or with the ASYMMETRIC keyword), returns TRUE if <i>value1</i> is greater than or equal to <i>value2</i> and less than or equal to <i>value3</i>.
+ With the SYMMETRIC keyword, returns TRUE if <i>value1</i> is inclusively between <i>value2</i> and <i>value3</i>.
+ When either <i>value2</i> or <i>value3</i> is NULL, returns FALSE or UNKNOWN.</p>
+ <p>E.g., <code>12 BETWEEN 15 AND 12</code> returns FALSE;
+ <code>12 BETWEEN SYMMETRIC 15 AND 12</code> returns TRUE;
+ <code>12 BETWEEN 10 AND NULL</code> returns UNKNOWN;
+ <code>12 BETWEEN NULL AND 10</code> returns FALSE;
+ <code>12 BETWEEN SYMMETRIC NULL AND 12</code> returns UNKNOWN.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+value1 NOT BETWEEN [ ASYMMETRIC | SYMMETRIC ] value2 AND value3
+{% endhighlight %}
+ </td>
+ <td>
+ <p>By default (or with the ASYMMETRIC keyword), returns TRUE if <i>value1</i> is less than <i>value2</i> or greater than <i>value3</i>.
+ With the SYMMETRIC keyword, returns TRUE if <i>value1</i> is not inclusively between <i>value2</i> and <i>value3</i>.
+ When either <i>value2</i> or <i>value3</i> is NULL, returns TRUE or UNKNOWN.</p>
+ <p>E.g., <code>12 NOT BETWEEN 15 AND 12</code> returns TRUE;
+ <code>12 NOT BETWEEN SYMMETRIC 15 AND 12</code> returns FALSE;
+ <code>12 NOT BETWEEN NULL AND 15</code> returns UNKNOWN;
+ <code>12 NOT BETWEEN 15 AND NULL</code> returns TRUE;
+ <code>12 NOT BETWEEN SYMMETRIC 12 AND NULL</code> returns UNKNOWN.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+string1 LIKE string2 [ ESCAPE char ]
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>string1</i> matches pattern <i>string2</i>; returns UNKNOWN if <i>string1</i> or <i>string2</i> is NULL. An escape character can be defined if necessary.</p>
+ <p><b>Note:</b> The escape character has not been supported yet.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+string1 NOT LIKE string2 [ ESCAPE char ]
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>string1</i> does not match pattern <i>string2</i>; returns UNKNOWN if <i>string1</i> or <i>string2</i> is NULL. An escape character can be defined if necessary.</p>
+ <p><b>Note:</b> The escape character has not been supported yet.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+string1 SIMILAR TO string2 [ ESCAPE char ]
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>string1</i> matches SQL regular expression <i>string2</i>; returns UNKNOWN if <i>string1</i> or <i>string2</i> is NULL. An escape character can be defined if necessary.</p>
+ <p><b>Note:</b> The escape character has not been supported yet.</p>
+ </td>
+ </tr>
+
+
+ <tr>
+ <td>
+ {% highlight text %}
+string1 NOT SIMILAR TO string2 [ ESCAPE char ]
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>string1</i> does not match SQL regular expression <i>string2</i>; returns UNKNOWN if <i>string1</i> or <i>string2</i> is NULL. An escape character can be defined if necessary.</p>
+ <p><b>Note:</b> The escape character has not been supported yet.</p>
+ </td>
+ </tr>
+
+
+ <tr>
+ <td>
+ {% highlight text %}
+value1 IN (value2 [, value3]* )
+{% endhighlight %}
+ </td>
+ <td>
+ <p> Returns TRUE if <i>value1</i> exists in the given list <i>(value2, value3, ...)</i>.
+ When <i>(value2, value3, ...)</i>. contains NULL, returns TRUE if the element can be found and UNKNOWN otherwise. Always returns UNKNOWN if <i>value1</i> is NULL.</p>
+ <p>E.g., <code>4 IN (1, 2, 3)</code> returns FALSE;
+ <code>1 IN (1, 2, NULL)</code> returns TRUE;
+ <code>4 IN (1, 2, NULL)</code> returns UNKNOWN.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+value1 NOT IN (value2 [, value3]* )
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>value1</i> does not exist in the given list <i>(value2, value3, ...)</i>.
+ When <i>(value2, value3, ...)</i>. contains NULL, returns FALSE if <i>value1</i> can be found and UNKNOWN otherwise. Always returns UNKNOWN if <i>value1</i> is NULL.</p>
+ <p>E.g., <code>4 NOT IN (1, 2, 3)</code> returns TRUE;
+ <code>1 NOT IN (1, 2, NULL)</code> returns FALSE;
+ <code>4 NOT IN (1, 2, NULL)</code> returns UNKNOWN.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+EXISTS (sub-query)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>sub-query</i> returns at least one row. Only supported if the operation can be rewritten in a join and group operation.</p>
+ <p><b>Note:</b> For streaming queries the operation is rewritten in a join and group operation. The required state to compute the query result might grow infinitely depending on the number of distinct input rows. Please provide a query configuration with valid retention interval to prevent excessive state size. See <a href="streaming.html">Streaming Concepts</a> for details.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+{% highlight text %}
+value IN (sub-query)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>value</i> is equal to a row returned by sub-query.</p>
+ <p><b>Note:</b> For streaming queries the operation is rewritten in a join and group operation. The required state to compute the query result might grow infinitely depending on the number of distinct input rows. Please provide a query configuration with valid retention interval to prevent excessive state size. See <a href="streaming.html">Streaming Concepts</a> for details.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+value NOT IN (sub-query)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>value</i> is not equal to every row returned by <i>sub-query</i>.</p>
+ <p><b>Note:</b> For streaming queries the operation is rewritten in a join and group operation. The required state to compute the query result might grow infinitely depending on the number of distinct input rows. Please provide a query configuration with valid retention interval to prevent excessive state size. See <a href="streaming.html">Streaming Concepts</a> for details.</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+
+<div data-lang="java" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Comparison functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight java %}
+ANY1 === ANY2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>ANY1</i> is equal to <i>ANY2</i>; returns UNKNOWN if <i>ANY1</i> or <i>ANY2</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+ANY1 !== ANY2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>ANY1</i> is not equal to <i>ANY2</i>; returns UNKNOWN if <i>ANY1</i> or <i>ANY2</i> is NULL. </p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+ANY1 > ANY2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>ANY1</i> is greater than <i>ANY2</i>; returns UNKNOWN if <i>ANY1</i> or <i>ANY2</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+ANY1 >= ANY2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>ANY1</i> is greater than or equal to <i>ANY2</i>; returns UNKNOWN if <i>ANY1</i> or <i>ANY2</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+ANY1 < ANY2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>ANY1</i> is less than <i>ANY2</i>; returns UNKNOWN if <i>ANY1</i> or <i>ANY2</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+ANY1 <= ANY2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>ANY1</i> is less than or equal to <i>ANY2</i>; returns UNKNOWN if <i>ANY1</i> or <i>ANY2</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+ANY.isNull
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>ANY</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+ANY.isNotNull
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>ANY</i> is not NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+STRING1.like(STRING2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>STRING1</i> matches pattern <i>STRING2</i>; returns UNKNOWN if <i>STRING1</i> or <i>STRING2</i> is NULL.</p>
+ <p>E.g., <code>"JoKn".like("Jo_n%")</code> returns TRUE.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+STRING.similar(STRING)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>STRING1</i> matches SQL regular expression <i>STRING2</i>; returns UNKNOWN if <i>STRING1</i> or <i>STRING2</i> is NULL.</p>
+ <p>E.g., <code>"A".similar("A+")</code> returns TRUE.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+ANY1.in(ANY2, ANY3, ...)
+{% endhighlight %}
+ </td>
+ <td>
+ <p> Returns TRUE if <i>ANY1</i> exists in a given list <i>(ANY2, ANY3, ...)</i>.
+ When <i>(ANY2, ANY3, ...)</i>. contains NULL, returns TRUE if the element can be found and UNKNOWN otherwise. Always returns UNKNOWN if <i>ANY1</i> is NULL.</p>
+ <p>E.g., <code>4.in(1, 2, 3)</code> returns FALSE.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+ANY.in(TABLE)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>ANY</i> is equal to a row returned by sub-query <i>TABLE</i>.</p>
+ <p><b>Note:</b> For streaming queries the operation is rewritten in a join and group operation. The required state to compute the query result might grow infinitely depending on the number of distinct input rows. Please provide a query configuration with valid retention interval to prevent excessive state size. See <a href="streaming.html">Streaming Concepts</a> for details.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+ANY1.between(ANY2, ANY3)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>ANY1</i> is greater than or equal to <i>ANY2</i> and less than or equal to <i>ANY3</i>.
+ When either <i>ANY2</i> or <i>ANY3</i> is NULL, returns FALSE or UNKNOWN.</p>
+ <p>E.g., <code>12.between(15, 12)</code> returns FALSE;
+ <code>12.between(10, Null(INT))</code> returns UNKNOWN;
+ <code>12.between(Null(INT), 10)</code> returns FALSE.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+ANY1.notBetween(ANY2, ANY3)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>ANY1</i> is less than <i>ANY2</i> or greater than <i>ANY3</i>.
+ When either <i>ANY2</i> or <i>ANY3</i> is NULL, returns TRUE or UNKNOWN.</p>
+ <p>E.g., <code>12.notBetween(15, 12)</code> returns TRUE;
+ <code>12.notBetween(Null(INT), 15)</code> returns UNKNOWN;
+ <code>12.notBetween(15, Null(INT))</code> returns TRUE.</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+
+<div data-lang="scala" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Comparison functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight scala %}
+ANY1 === ANY2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>ANY1</i> is equal to <i>ANY2</i>; returns UNKNOWN if <i>ANY1</i> or <i>ANY2</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+ANY1 !== ANY2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>ANY1</i> is not equal to <i>ANY2</i>; returns UNKNOWN if <i>ANY1</i> or <i>ANY2</i> is NULL. </p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+ANY1 > ANY2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>ANY1</i> is greater than <i>ANY2</i>; returns UNKNOWN if <i>ANY1</i> or <i>ANY2</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+ANY1 >= ANY2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>ANY1</i> is greater than or equal to <i>ANY2</i>; returns UNKNOWN if <i>ANY1</i> or <i>ANY2</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+ANY1 < ANY2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>ANY1</i> is less than <i>ANY2</i>; returns UNKNOWN if <i>ANY1</i> or <i>ANY2</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+ANY1 <= ANY2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>ANY1</i> is less than or equal to <i>ANY2</i>; returns UNKNOWN if <i>ANY1</i> or <i>ANY2</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+ANY.isNull
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>ANY</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+ANY.isNotNull
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>ANY</i> is not NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING1.like(STRING2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>STRING1</i> matches pattern <i>STRING2</i>; returns UNKNOWN if <i>STRING1</i> or <i>STRING2</i> is NULL.</p>
+ <p>E.g., <code>"JoKn".like("Jo_n%")</code> returns TRUE.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING1.similar(STRING2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>STRING1</i> matches SQL regular expression <i>STRING2</i>; returns UNKNOWN if <i>STRING1</i> or <i>STRING2</i> is NULL.</p>
+ <p>E.g., <code>"A".similar("A+")</code> returns TRUE.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+ANY1.in(ANY2, ANY3, ...)
+{% endhighlight %}
+ </td>
+ <td>
+ <p> Returns TRUE if <i>ANY1</i> exists in a given list <i>(ANY2, ANY3, ...)</i>.
+ When <i>(ANY2, ANY3, ...)</i>. contains NULL, returns TRUE if the element can be found and UNKNOWN otherwise. Always returns UNKNOWN if <i>ANY1</i> is NULL.</p>
+ <p>E.g., <code>4.in(1, 2, 3)</code> returns FALSE.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+ANY.in(TABLE)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>ANY</i> is equal to a row returned by sub-query <i>TABLE</i>.</p>
+ <p><b>Note:</b> For streaming queries the operation is rewritten in a join and group operation. The required state to compute the query result might grow infinitely depending on the number of distinct input rows. Please provide a query configuration with valid retention interval to prevent excessive state size. See <a href="streaming.html">Streaming Concepts</a> for details.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+ANY1.between(ANY2, ANY3)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>ANY1</i> is greater than or equal to <i>ANY2</i> and less than or equal to <i>ANY3</i>.
+ When either <i>ANY2</i> or <i>ANY3</i> is NULL, returns FALSE or UNKNOWN.</p>
+ <p>E.g., <code>12.between(15, 12)</code> returns FALSE;
+ <code>12.between(10, Null(Types.INT))</code> returns UNKNOWN;
+ <code>12.between(Null(Types.INT), 10)</code> returns FALSE.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+ANY1.notBetween(ANY2, ANY3)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>ANY1</i> is less than <i>ANY2</i> or greater than <i>ANY3</i>.
+ When either <i>ANY2</i> or <i>ANY3</i> is NULL, returns TRUE or UNKNOWN.</p>
+ <p>E.g., <code>12.notBetween(15, 12)</code> returns TRUE;
+ <code>12.notBetween(Null(Types.INT), 15)</code> returns UNKNOWN;
+ <code>12.notBetween(15, Null(Types.INT))</code> returns TRUE.</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+</div>
+
+{% top %}
+
+### Logical Functions
+
+<div class="codetabs" markdown="1">
+<div data-lang="SQL" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Logical functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight text %}
+boolean1 OR boolean2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>boolean1</i> is TRUE or <i>boolean2</i> is TRUE. Supports three-valued logic.</p>
+ <p>E.g., <code>TRUE OR UNKNOWN</code> returns TRUE.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+boolean1 AND boolean2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>boolean1</i> and <i>boolean2</i> are both TRUE. Supports three-valued logic.</p>
+ <p>E.g., <code>TRUE AND UNKNOWN</code> returns UNKNOWN.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+NOT boolean
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>boolean</i> is FALSE; returns FALSE if <i>boolean</i> is TRUE; returns UNKNOWN if <i>boolean</i> is UNKNOWN.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+boolean IS FALSE
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>boolean</i> is FALSE; returns FALSE if <i>boolean</i> is TRUE or UNKNOWN.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+boolean IS NOT FALSE
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>boolean</i> is TRUE or UNKNOWN; returns FALSE if <i>boolean</i> is FALSE.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+boolean IS TRUE
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>boolean</i> is TRUE; returns FALSE if <i>boolean</i> is FALSE or UNKNOWN.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+boolean IS NOT TRUE
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>boolean</i> is FALSE or UNKNOWN; returns FALSE if <i>boolean</i> is FALSE.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+boolean IS UNKNOWN
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>boolean</i> is UNKNOWN; returns FALSE if <i>boolean</i> is TRUE or FALSE.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+boolean IS NOT UNKNOWN
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>boolean</i> is TRUE or FALSE; returns FALSE if <i>boolean</i> is UNKNOWN.</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+
+<div data-lang="java" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Logical functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight java %}
+BOOLEAN1 || BOOLEAN2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>BOOLEAN1</i> is TRUE or <i>BOOLEAN2</i> is TRUE. Supports three-valued logic.</p>
+ <p>E.g., <code>true || Null(BOOLEAN)</code> returns TRUE.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+BOOLEAN1 && BOOLEAN2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>BOOLEAN1</i> and <i>BOOLEAN2</i> are both TRUE. Supports three-valued logic.</p>
+ <p>E.g., <code>true && Null(BOOLEAN)</code> returns UNKNOWN.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+!BOOLEAN
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>BOOLEAN</i> is FALSE; returns FALSE if <i>BOOLEAN</i> is TRUE; returns UNKNOWN if <i>BOOLEAN</i> is UNKNOWN.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+BOOLEAN.isTrue
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>BOOLEAN</i> is TRUE; returns FALSE if <i>BOOLEAN</i> is FALSE or UNKNOWN.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+BOOLEAN.isFalse
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>BOOLEAN</i> is FALSE; returns FALSE if <i>BOOLEAN</i> is TRUE or UNKNOWN.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+BOOLEAN.isNotTrue
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>BOOLEAN</i> is FALSE or UNKNOWN; returns FALSE if <i>BOOLEAN</i> is FALSE.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+BOOLEAN.isNotFalse
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>BOOLEAN</i> is TRUE or UNKNOWN; returns FALSE if <i>BOOLEAN</i> is FALSE.</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+
+<div data-lang="scala" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Logical functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight scala %}
+BOOLEAN1 || BOOLEAN2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>BOOLEAN1</i> is TRUE or <i>BOOLEAN2</i> is TRUE. Supports three-valued logic.</p>
+ <p>E.g., <code>true || Null(Types.BOOLEAN)</code> returns TRUE.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+BOOLEAN1 && BOOLEAN2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>BOOLEAN1</i> and <i>BOOLEAN2</i> are both TRUE. Supports three-valued logic.</p>
+ <p>E.g., <code>true && Null(Types.BOOLEAN)</code> returns UNKNOWN.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+!BOOLEAN
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>BOOLEAN</i> is FALSE; returns FALSE if <i>BOOLEAN</i> is TRUE; returns UNKNOWN if <i>BOOLEAN</i> is UNKNOWN.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+BOOLEAN.isTrue
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>BOOLEAN</i> is TRUE; returns FALSE if <i>BOOLEAN</i> is FALSE or UNKNOWN.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+BOOLEAN.isFalse
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>BOOLEAN</i> is FALSE; returns FALSE if <i>BOOLEAN</i> is TRUE or UNKNOWN.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+BOOLEAN.isNotTrue
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>BOOLEAN</i> is FALSE or UNKNOWN; returns FALSE if <i>BOOLEAN</i> is FALSE.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+BOOLEAN.isNotFalse
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if <i>BOOLEAN</i> is TRUE or UNKNOWN; returns FALSE if <i>BOOLEAN</i> is FALSE.</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+</div>
+
+{% top %}
+
+### Arithmetic Functions
+
+<div class="codetabs" markdown="1">
+<div data-lang="SQL" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Arithmetic functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight text %}
++ numeric
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>numeric</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+- numeric
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns negative <i>numeric</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+numeric1 + numeric2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>numeric1</i> plus <i>numeric2</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+numeric1 - numeric2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>numeric1</i> minus <i>numeric2</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+numeric1 * numeric2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>numeric1</i> multiplied by <i>numeric2</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+numeric1 / numeric2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>numeric1</i> divided by <i>numeric2</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+POWER(numeric1, numeric2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>numeric1</i> raised to the power of <i>numeric2</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+ABS(numeric)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the absolute value of <i>numeric</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+MOD(numeric1, numeric2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the remainder (modulus) of <i>numeric1</i> divided by <i>numeric2</i>. The result is negative only if <i>numeric1</i> is negative.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+SQRT(numeric)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the square root of <i>numeric</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+LN(numeric)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the natural logarithm (base e) of <i>numeric</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+LOG10(numeric)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the base 10 logarithm of <i>numeric</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+LOG2(numeric)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the base 2 logarithm of <i>numeric</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+LOG(numeric2)
+LOG(numeric1, numeric2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>When called with one argument, returns the natural logarithm of <i>numeric2</i>. When called with two arguments, this function returns the logarithm of <i>numeric2</i> to the base <i>numeric1</i>.</p>
+ <p><b>Note:</b> Currently, <i>numeric2</i> must be greater than 0 and <i>numeric1</i> must be greater than 1.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+EXP(numeric)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns e raised to the power of <i>numeric</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+CEIL(numeric)
+CEILING(numeric)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Rounds <i>numeric</i> up, and returns the smallest number that is greater than or equal to <i>numeric</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+FLOOR(numeric)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Rounds <i>numeric</i> down, and returns the largest number that is less than or equal to <i>numeric</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+SIN(numeric)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the sine of <i>numeric</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+COS(numeric)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the cosine of <i>numeric</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+TAN(numeric)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the tangent of <i>numeric</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+COT(numeric)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the cotangent of a <i>numeric</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+ASIN(numeric)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the arc sine of <i>numeric</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+ACOS(numeric)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the arc cosine of <i>numeric</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+ATAN(numeric)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the arc tangent of <i>numeric</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+ATAN2(numeric1, numeric2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the arc tangent of a coordinate <i>(numeric1, numeric2)</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+DEGREES(numeric)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the degree representation of a radian <i>numeric</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+RADIANS(numeric)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the radian representation of a degree <i>numeric</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+SIGN(numeric)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the signum of <i>numeric</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+ROUND(numeric, integer)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a number rounded to <i>integer</i> decimal places for <i>numeric</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+PI
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a value that is closer than any other values to pi.</p>
+ </td>
+ </tr>
+ <tr>
+ <td>
+ {% highlight text %}
+E()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a value that is closer than any other values to e.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+RAND()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive).</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+RAND(integer)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive) with an initial seed <i>integer</i>. Two RAND functions will return identical sequences of numbers if they have the same initial seed.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+RAND_INTEGER(integer)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a pseudorandom integer value between 0 (inclusive) and <i>integer</i> (exclusive).</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+RAND_INTEGER(integer1, integer2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a pseudorandom integer value between 0 (inclusive) and the specified value (exclusive) with an initial seed. Two RAND_INTEGER functions will return identical sequences of numbers if they have the same initial seed and bound.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+BIN(integer)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a string representation of <i>integer</i> in binary format. Returns NULL if <i>integer</i> is NULL.</p>
+ <p>E.g. <code>BIN(4)</code> returns '100' and <code>BIN(12)</code> returns '1100'.</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+
+<div data-lang="java" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Arithmetic functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight java %}
++ NUMERIC
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+- NUMERIC
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns negative <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC1 + NUMERIC2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>NUMERIC1</i> plus <i>NUMERIC2</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC1 - NUMERIC2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>NUMERIC1</i> minus <i>NUMERIC2</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC1 * NUMERIC2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>NUMERIC1</i> multiplied by <i>NUMERIC2</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC1 / NUMERIC2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>NUMERIC1</i> divided by <i>NUMERIC2</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC1.power(NUMERIC2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>NUMERIC1</i> raised to the power of <i>NUMERIC2</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.abs()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the absolute value of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC1 % NUMERIC2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the remainder (modulus) of <i>NUMERIC1</i> divided by <i>NUMERIC2</i>. The result is negative only if <i>numeric1</i> is negative.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.sqrt()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the square root of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.ln()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the natural logarithm (base e) of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.log10()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the base 10 logarithm of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.log2()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the base 2 logarithm of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC1.log()
+NUMERIC1.log(NUMERIC2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>When called without argument, returns the natural logarithm of <i>NUMERIC1</i>. When called with an argument, returns the logarithm of <i>NUMERIC1</i> to the base <i>NUMERIC2</i>.</p>
+ <p><b>Note:</b> Currently, <i>NUMERIC1</i> must be greater than 0 and <i>NUMERIC2</i> must be greater than 1.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.exp()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns e raised to the power of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.ceil()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Rounds <i>NUMERIC</i> up, and returns the smallest number that is greater than or equal to <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.floor()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Rounds <i>NUMERIC</i> down, and returns the largest number that is less than or equal to <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.sin()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the sine of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.cos()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the cosine of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.tan()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the tangent of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.cot()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the cotangent of a <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.asin()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the arc sine of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.acos()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the arc cosine of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.atan()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the arc tangent of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+atan2(NUMERIC1, NUMERIC2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the arc tangent of a coordinate <i>(NUMERIC1, NUMERIC2)</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.degrees()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the degree representation of a radian <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.radians()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the radian representation of a degree <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.sign()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the signum of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.round(INT)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a number rounded to <i>INT</i> decimal places for <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+pi()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a value that is closer than any other values to pi.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+e()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a value that is closer than any other values to e.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+rand()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive).</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+rand(INTEGER)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive) with an initial seed <i>INTEGER</i>. Two RAND functions will return identical sequences of numbers if they have the same initial seed.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+randInteger(INTEGER)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a pseudorandom integer value between 0 (inclusive) and <i>INTEGER</i> (exclusive).</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+randInteger(INTEGER1, INTEGER2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a pseudorandom integer value between 0 (inclusive) and <i>INTEGER2</i> (exclusive) with an initial seed <i>INTEGER1</i>. Two randInteger functions will return identical sequences of numbers if they have same initial seed and bound.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+INTEGER.bin()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a string representation of <i>INTEGER</i> in binary format. Returns NULL if <i>INTEGER</i> is NULL.</p>
+ <p>E.g., <code>4.bin()</code> returns "100" and <code>12.bin()</code> returns "1100".</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+
+<div data-lang="scala" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Arithmetic functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight scala %}
++ NUMERIC
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+- NUMERIC
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns negative <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC1 + NUMERIC2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>NUMERIC1</i> plus <i>NUMERIC2</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC1 - NUMERIC2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>NUMERIC1</i> minus <i>NUMERIC2</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC1 * NUMERIC2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>NUMERIC1</i> multiplied by <i>NUMERIC2</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC1 / NUMERIC2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>NUMERIC1</i> divided by <i>NUMERIC2</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC1.power(NUMERIC2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>NUMERIC1</i> raised to the power of <i>NUMERIC2</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.abs()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the absolute value of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC1 % NUMERIC2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the remainder (modulus) of <i>NUMERIC1</i> divided by <i>NUMERIC2</i>. The result is negative only if <i>numeric1</i> is negative.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.sqrt()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the square root of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.ln()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the natural logarithm (base e) of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.log10()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the base 10 logarithm of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.log2()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the base 2 logarithm of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC1.log()
+NUMERIC1.log(NUMERIC2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>When called without argument, returns the natural logarithm of <i>NUMERIC1</i>. When called with an argument, returns the logarithm of <i>NUMERIC1</i> to the base <i>NUMERIC2</i>.</p>
+ <p><b>Note:</b> Currently, <i>NUMERIC1</i> must be greater than 0 and <i>NUMERIC2</i> must be greater than 1.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.exp()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns e raised to the power of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.ceil()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Rounds <i>NUMERIC</i> up, and returns the smallest number that is greater than or equal to <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.floor()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Rounds <i>NUMERIC</i> down, and returns the largest number that is less than or equal to <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.sin()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the sine of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.cos()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the cosine of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.tan()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the tangent of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.cot()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the cotangent of a <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.asin()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the arc sine of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.acos()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the arc cosine of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.atan()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the arc tangent of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+atan2(NUMERIC1, NUMERIC2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the arc tangent of a coordinate <i>(NUMERIC1, NUMERIC2)</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.degrees()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the degree representation of a radian <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.radians()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the radian representation of a degree <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.sign()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the signum of <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.round(INT)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a number rounded to <i>INT</i> decimal places for <i>NUMERIC</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+pi()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a value that is closer than any other values to pi.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+e()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a value that is closer than any other values to e.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+rand()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive).</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+rand(INTEGER)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive) with an initial seed <i>INTEGER</i>. Two RAND functions will return identical sequences of numbers if they have the same initial seed.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+randInteger(INTEGER)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a pseudorandom integer value between 0 (inclusive) and <i>INTEGER</i> (exclusive).</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+randInteger(INTEGER1, INTEGER2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a pseudorandom integer value between 0 (inclusive) and <i>INTEGER2</i> (exclusive) with an initial seed <i>INTEGER1</i>. Two randInteger functions will return identical sequences of numbers if they have same initial seed and bound.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+INTEGER.bin()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a string representation of <i>INTEGER</i> in binary format. Returns NULL if <i>INTEGER</i> is NULL.</p>
+ <p>E.g., <code>4.bin()</code> returns "100" and <code>12.bin()</code> returns "1100".</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+</div>
+
+{% top %}
+
+### String Functions
+
+<div class="codetabs" markdown="1">
+<div data-lang="SQL" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">String functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight text %}
+string1 || string2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the concatenation of <i>string1</i> and <i>string2</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+CHAR_LENGTH(string)
+CHARACTER_LENGTH(string)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the number of characters in <i>string</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+UPPER(string)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>string</i> in uppercase.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+LOWER(string)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>string</i> in lowercase.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+POSITION(string1 IN string2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the position (start from 1) of the first occurrence of <i>string1</i> in <i>string2</i>; returns 0 if <i>string1</i> cannot be found in <i>string2</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+TRIM([ BOTH | LEADING | TRAILING ] string1 FROM string2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a string that removes leading and/or trailing characters <i>string1</i> from <i>string2</i>. By default, whitespaces at both sides are removed.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+OVERLAY(string1 PLACING string2 FROM integer1 [ FOR integer2 ])
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a string that replaces <i>integer2</i> (<i>string2</i>'s length by default) characters of <i>string1</i> with <i>string2</i> from position <i>integer1</i>.</p>
+ <p>E.g., <code>OVERLAY('This is an old string' PLACING ' new' FROM 10 FOR 5)</code> returns "This is a new string"</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+SUBSTRING(string FROM integer1 [ FOR integer2 ])
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a substring of <i>string</i> starting from position <i>integer1</i> with length <i>integer2</i> (to the end by default).</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+INITCAP(string)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a new form of <i>string</i> with the first character of each word converted to uppercase and the rest characters to lowercase. Here a word means a sequences of alphanumeric characters.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+CONCAT(string1, string2,...)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a string that concatenates <i>string1, string2, ...</i>. Returns NULL if any argument is NULL.</p>
+ <p>E.g., <code>CONCAT('AA', 'BB', 'CC')</code> returns "AABBCC".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+CONCAT_WS(string1, string2, string3,...)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a string that concatenates <i>string2, string3, ...</i> with a separator <i>string1</i>. The separator is added between the strings to be concatenated. Returns NULL If <i>string1</i> is NULL. Compared with <code>CONCAT()</code>, <code>CONCAT_WS()</code> automatically skips NULL arguments.</p>
+ <p>E.g., <code>CONCAT_WS('~', 'AA', NULL, 'BB', '', 'CC')</code> returns "AA~BB~~CC".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+LPAD(string1, integer, string2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a new string from <i>string1</i> left-padded with <i>string2</i> to a length of <i>integer</i> characters. If the length of <i>string1</i> is shorter than <i>integer</i>, returns <i>string1</i> shortened to <i>integer</i> characters.</p>
+ <p>E.g., <code>LPAD('hi',4,'??')</code> returns "??hi"; <code>LPAD('hi',1,'??')</code> returns "h".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+RPAD(string1, integer, string2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a new string from <i>string1</i> right-padded with <i>string2</i> to a length of <i>integer</i> characters. If the length of <i>string1</i> is shorter than <i>integer</i>, returns <i>string1</i> shortened to <i>integer</i> characters.</p>
+ <p>E.g., <code>RPAD('hi',4,'??')</code> returns "hi??", <code>RPAD('hi',1,'??')</code> returns "h".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+FROM_BASE64(string)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the base64-decoded result from <i>string</i>; returns NULL if <i>string</i> is NULL.</p>
+ <p>E.g., <code>FROM_BASE64('aGVsbG8gd29ybGQ=')</code> returns "hello world".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+TO_BASE64(string)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the base64-encoded result from <i>string</i>; returns NULL if <i>string</i> is NULL.</p>
+ <p>E.g., <code>TO_BASE64('hello world')</code> returns "aGVsbG8gd29ybGQ=".</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+
+<div data-lang="java" markdown="1">
+
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">String functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight java %}
+STRING1 + STRING2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the concatenation of <i>STRING1</i> and <i>STRING2</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+STRING.charLength()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the number of characters in <i>STRING</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+STRING.upperCase()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>STRING</i> in uppercase.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+STRING.lowerCase()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>STRING</i> in lowercase.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+STRING1.position(STRING2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the position (start from 1) of the first occurrence of <i>STRING1</i> in <i>STRING2</i>; returns 0 if <i>STRING1</i> cannot be found in <i>STRING2</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+STRING1.trim(LEADING, STRING2)
+STRING1.trim(TRAILING, STRING2)
+STRING1.trim(BOTH, STRING2)
+STRING1.trim(BOTH)
+STRING1.trim()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a string that removes leading and/or trailing characters <i>STRING2</i> from <i>STRING1</i>. By default, whitespaces at both sides are removed.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+STRING1.overlay(STRING2, INT1)
+STRING1.overlay(STRING2, INT1, INT2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a string that replaces <i>INT2</i> (<i>STRING2</i>'s length by default) characters of <i>STRING1</i> with <i>STRING2</i> from position <i>INT1</i>.</p>
+ <p>E.g., <code>'xxxxxtest'.overlay('xxxx', 6)</code> returns "xxxxxxxxx"; <code>'xxxxxtest'.overlay('xxxx', 6, 2)</code> returns "xxxxxxxxxst".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+STRING.substring(INT1)
+STRING.substring(INT1, INT2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a substring of <i>STRING</i> starting from position <i>INT1</i> with length <i>INT2</i> (to the end by default).</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+STRING.initCap()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a new form of <i>STRING</i> with the first character of each word converted to uppercase and the rest characters to lowercase. Here a word means a sequences of alphanumeric characters.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+concat(STRING1, STRING2, ...)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a string that concatenates <i>STRING1, STRING2, ...</i>. Returns NULL if any argument is NULL.</p>
+ <p>E.g., <code>concat('AA', 'BB', 'CC')</code> returns "AABBCC".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+concat_ws(STRING1, STRING2, STRING3, ...)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a string that concatenates <i>STRING2, STRING3, ...</i> with a separator <i>STRING1</i>. The separator is added between the strings to be concatenated. Returns NULL If <i>STRING1</i> is NULL. Compared with <code>concat()</code>, <code>concat_ws()</code> automatically skips NULL arguments.</p>
+ <p>E.g., <code>concat_ws('~', 'AA', Null(STRING), 'BB', '', 'CC')</code> returns "AA~BB~~CC".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+STRING1.lpad(INT, STRING2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a new string from <i>STRING1</i> left-padded with <i>STRING2</i> to a length of <i>INT</i> characters. If the length of <i>STRING1</i> is shorter than <i>INT</i>, returns <i>STRING1</i> shortened to <i>INT</i> characters.</p>
+ <p>E.g., <code>'hi'.lpad(4, '??')</code> returns "??hi"; <code>'hi'.lpad(1, '??')</code> returns "h".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+STRING1.rpad(INT, STRING2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a new string from <i>STRING1</i> right-padded with <i>STRING2</i> to a length of <i>INT</i> characters. If the length of <i>STRING1</i> is shorter than <i>INT</i>, returns <i>STRING1</i> shortened to <i>INT</i> characters.</p>
+ <p>E.g., <code>'hi'.rpad(4, '??')</code> returns "hi??"; <code>'hi'.rpad(1, '??')</code> returns "h".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+STRING.fromBase64()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the base64-decoded result from <i>STRING</i>; returns NULL if <i>STRING</i> is NULL.</p>
+ <p>E.g., <code>'aGVsbG8gd29ybGQ='.fromBase64()</code> returns "hello world".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+STRING.toBase64()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the base64-encoded result from <i>STRING</i>; returns NULL if <i>STRING</i> is NULL.</p>
+ <p>E.g., <code>'hello world'.toBase64()</code> returns "aGVsbG8gd29ybGQ=".</p>
+ </td>
+ </tr>
+
+ </tbody>
+</table>
+</div>
+
+<div data-lang="scala" markdown="1">
+
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">String functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING1 + STRING2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the concatenation of <i>STRING1</i> and <i>STRING2</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING.charLength()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the number of characters in <i>STRING</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING.upperCase()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>STRING</i> in uppercase.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING.lowerCase()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>STRING</i> in lowercase.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING1.position(STRING2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the position (start from 1) of the first occurrence of <i>STRING1</i> in <i>STRING2</i>; returns 0 if <i>STRING1</i> cannot be found in <i>STRING2</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING.trim(
+ leading = true,
+ trailing = true,
+ character = " ")
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a string that removes leading and/or trailing characters from <i>STRING</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING1.overlay(STRING2, INT1)
+STRING1.overlay(STRING2, INT1, INT2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a string that replaces <i>INT2</i> (<i>STRING2</i>'s length by default) characters of <i>STRING1</i> with <i>STRING2</i> from position <i>INT1</i>.</p>
+ <p>E.g., <code>"xxxxxtest".overlay("xxxx", 6)</code> returns "xxxxxxxxx"; <code>"xxxxxtest".overlay("xxxx", 6, 2)</code> returns "xxxxxxxxxst".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING.substring(INT1)
+STRING.substring(INT1, INT2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a substring of <i>STRING</i> starting from position <i>INT1</i> with length <i>INT2</i> (to the end by default).</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING.initCap()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a new form of <i>STRING</i> with the first character of each word converted to uppercase and the rest characters to lowercase. Here a word means a sequences of alphanumeric characters.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+concat(STRING1, STRING2, ...)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a string that concatenates <i>STRING1, STRING2, ...</i>. Returns NULL if any argument is NULL.</p>
+ <p>E.g., <code>concat("AA", "BB", "CC")</code> returns "AABBCC".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+concat_ws(STRING1, STRING2, STRING3, ...)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a string that concatenates <i>STRING2, STRING3, ...</i> with a separator <i>STRING1</i>. The separator is added between the strings to be concatenated. Returns NULL If <i>STRING1</i> is NULL. Compared with <code>concat()</code>, <code>concat_ws()</code> automatically skips NULL arguments.</p>
+ <p>E.g., <code>concat_ws("~", "AA", Null(Types.STRING), "BB", "", "CC")</code> returns "AA~BB~~CC".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING1.lpad(INT, STRING2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a new string from <i>STRING1</i> left-padded with <i>STRING2</i> to a length of <i>INT</i> characters. If the length of <i>STRING1</i> is shorter than <i>INT</i>, returns <i>STRING1</i> shortened to <i>INT</i> characters.</p>
+ <p>E.g., <code>"hi".lpad(4, "??")</code> returns "??hi"; <code>"hi".lpad(1, "??")</code> returns "h".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING1.rpad(INT, STRING2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a new string from <i>STRING1</i> right-padded with <i>STRING2</i> to a length of <i>INT</i> characters. If the length of <i>STRING1</i> is shorter than <i>INT</i>, returns <i>STRING1</i> shortened to <i>INT</i> characters.</p>
+ <p>E.g., <code>"hi".rpad(4, "??")</code> returns "hi??"; <code>"hi".rpad(1, "??")</code> returns "h".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING.fromBase64()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the base64-decoded result from <i>STRING</i>; returns null If <i>STRING</i> is NULL.</p>
+ <p>E.g., <code>"aGVsbG8gd29ybGQ=".fromBase64()</code> returns "hello world".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING.toBase64()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the base64-encoded result from <i>STRING</i>; returns NULL if <i>STRING</i> is NULL.</p>
+ <p>E.g., <code>"hello world".toBase64()</code> returns "aGVsbG8gd29ybGQ=".</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+</div>
+
+{% top %}
+
+### Temporal Functions
+
+<div class="codetabs" markdown="1">
+<div data-lang="SQL" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Temporal functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight text %}
+DATE string
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a SQL date parsed from <i>string</i> in form of "yyyy-MM-dd".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+TIME string
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a SQL time parsed from <i>string</i> in form of "HH:mm:ss".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+TIMESTAMP string
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a SQL timestamp parsed from <i>string</i> in form of "yyyy-MM-dd HH:mm:ss[.SSS]".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+INTERVAL string range
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Parses an interval <i>string</i> in the form "dd hh:mm:ss.fff" for SQL intervals of milliseconds or "yyyy-mm" for SQL intervals of months. An interval range might be <code>DAY</code>, <code>MINUTE</code>, <code>DAY TO HOUR</code>, or <code>DAY TO SECOND</code> for intervals of milliseconds; <code>YEAR</code> or <code>YEAR TO MONTH</code> for intervals of months.</p>
+ <p>E.g., <code>INTERVAL '10 00:00:00.004' DAY TO SECOND</code>, <code>INTERVAL '10' DAY</code>, or <code>INTERVAL '2-10' YEAR TO MONTH</code> return intervals.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+CURRENT_DATE
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the current SQL date in the UTC time zone.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+CURRENT_TIME
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the current SQL time in the UTC time zone.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+CURRENT_TIMESTAMP
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the current SQL timestamp in the UTC time zone.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+LOCALTIME
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the current SQL time in local time zone.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+LOCALTIMESTAMP
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the current SQL timestamp in local time zone.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+EXTRACT(timeintervalunit FROM temporal)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a long value extracted from the <i>timeintervalunit</i> part of <i>temporal</i>.</p>
+ <p>E.g., <code>EXTRACT(DAY FROM DATE '2006-06-05')</code> returns 5.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+YEAR(date)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the year from SQL date <i>date</i>. Equivalent to EXTRACT(YEAR FROM date).</p>
+ <p>E.g., <code>YEAR(DATE '1994-09-27')</code> returns 1994.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+QUARTER(date)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the quarter of a year (an integer between 1 and 4) from SQL date <i>date</i>. Equivalent to <code>EXTRACT(QUARTER FROM date)</code>.</p>
+ <p>E.g., <code>QUARTER(DATE '1994-09-27')</code> returns 3.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+MONTH(date)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the month of a year (an integer between 1 and 12) from SQL date <i>date</i>. Equivalent to <code>EXTRACT(MONTH FROM date)</code>.</p>
+ <p>E.g., <code>MONTH(DATE '1994-09-27')</code> returns 9.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+WEEK(date)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the week of a year (an integer between 1 and 53) from SQL date <i>date</i>. Equivalent to <code>EXTRACT(WEEK FROM date)</code>.</p>
+ <p>E.g., <code>WEEK(DATE '1994-09-27')</code> returns 39.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+DAYOFYEAR(date)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the day of a year (an integer between 1 and 366) from SQL date <i>date</i>. Equivalent to <code>EXTRACT(DOY FROM date)</code>.</p>
+ <p>E.g., <code>DAYOFYEAR(DATE '1994-09-27')</code> returns 270.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+DAYOFMONTH(date)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the day of a month (an integer between 1 and 31) from SQL date <i>date</i>. Equivalent to <code>EXTRACT(DAY FROM date)</code>.</p>
+ <p>E.g., <code>DAYOFMONTH(DATE '1994-09-27')</code> returns 27.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+DAYOFWEEK(date)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the day of a week (an integer between 1 and 7; Sunday = 1) from SQL date <i>date</i>.Equivalent to <code>EXTRACT(DOW FROM date)</code>.</p>
+ <p>E.g., <code>DAYOFWEEK(DATE '1994-09-27')</code> returns 3.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+HOUR(timestamp)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the hour of a day (an integer between 0 and 23) from SQL timestamp <i>timestamp</i>. Equivalent to <code>EXTRACT(HOUR FROM timestamp)</code>.</p>
+ <p>E.g., <code>HOUR(TIMESTAMP '1994-09-27 13:14:15')</code> returns 13.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+MINUTE(timestamp)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the minute of an hour (an integer between 0 and 59) from SQL timestamp <i>timestamp</i>. Equivalent to <code>EXTRACT(MINUTE FROM timestamp)</code>.</p>
+ <p>E.g., <code>MINUTE(TIMESTAMP '1994-09-27 13:14:15')</code> returns 14.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+SECOND(timestamp)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the second of a minute (an integer between 0 and 59) from SQL timestamp. Equivalent to <code>EXTRACT(SECOND FROM timestamp)</code>.</p>
+ <p>E.g., <code>SECOND(TIMESTAMP '1994-09-27 13:14:15')</code> returns 15.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+FLOOR(timepoint TO timeintervalunit)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a value that rounds <i>timepoint</i> down to the time unit <i>timeintervalunit</i>.</p>
+ <p>E.g., <code>FLOOR(TIME '12:44:31' TO MINUTE)</code> returns 12:44:00.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+CEIL(timepoint TO timeintervalunit)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a value that rounds <i>timepoint</i> up to the time unit <i>timeintervalunit</i>.</p>
+ <p>E.g., <code>CEIL(TIME '12:44:31' TO MINUTE)</code> returns 12:45:00.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+(timepoint1, temporal1) OVERLAPS (timepoint2, temporal2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if two time intervals defined by (<i>timepoint1</i>, <i>temporal1</i>) and (<i>timepoint2</i>, <i>temporal2</i>) overlap. The temporal values could be either a time point or a time interval.</p>
+ <p>E.g., <code>(TIME '2:55:00', INTERVAL '1' HOUR) OVERLAPS (TIME '3:30:00', INTERVAL '2' HOUR)</code> returns TRUE; <code>(TIME '9:00:00', TIME '10:00:00') OVERLAPS (TIME '10:15:00', INTERVAL '3' HOUR)</code> returns FALSE.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+DATE_FORMAT(timestamp, string)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a string that formats <i>timestamp</i> with a specified format <i>string</i>. The format specification is given in the <a href="sql.html#date-format-specifier">Date Format Specifier table</a>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+TIMESTAMPADD(unit, interval, timevalue)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a new time value that adds a (signed) integer interval to <i>timevalue</i>. The unit for <i>interval</i> is given by the unit argument, which should be one of the following values: <code>SECOND</code>, <code>MINUTE</code>, <code>HOUR</code>, <code>DAY</code>, <code>WEEK</code>, <code>MONTH</code>, <code>QUARTER</code>, or <code>YEAR</code>.</p>
+ <p>E.g., <code>TIMESTAMPADD(WEEK, 1, DATE '2003-01-02')</code> returns <code>2003-01-09</code>.</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+
+<div data-lang="java" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Temporal functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight java %}
+STRING.toDate()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a SQL date parsed from <i>STRING</i> in form of "yyyy-MM-dd".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+STRING.toTime()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a SQL time parsed from <i>STRING</i> in form of "HH:mm:ss".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+STRING.toTimestamp()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a SQL timestamp parsed from <i>STRING</i> in form of "yyyy-MM-dd HH:mm:ss[.SSS]".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.year
+NUMERIC.years
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Creates an interval of months for <i>NUMERIC</i> years.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.month
+NUMERIC.months
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Creates an interval of <i>NUMERIC</i> months.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.day
+NUMERIC.days
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Creates an interval of milliseconds for <i>NUMERIC</i> days.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.hour
+NUMERIC.hours
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Creates an interval of milliseconds for <i>NUMERIC</i> hours.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.minute
+NUMERIC.minutes
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Creates an interval of milliseconds for <i>NUMERIC</i> minutes.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.second
+NUMERIC.seconds
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Creates an interval of milliseconds for <i>NUMERIC</i> seconds.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.milli
+NUMERIC.millis
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Creates an interval of <i>NUMERIC</i> milliseconds.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+currentDate()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the current SQL date in the UTC time zone.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+currentTime()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the current SQL time in the UTC time zone.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+currentTimestamp()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the current SQL timestamp in the UTC time zone.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+localTime()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the current SQL time in local time zone.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+localTimestamp()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the current SQL timestamp in local time zone.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+TEMPORAL.extract(TIMEINTERVALUNIT)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a long value extracted from the <i>TIMEINTERVALUNIT</i> part of <i>temporal</i>.</p>
+ <p>E.g., <code>'2006-06-05'.toDate.extract(DAY)</code> returns 5; <code>'2006-06-05'.toDate.extract(QUARTER)</code> returns 2.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+TIMEPOINT.floor(TIMEINTERVALUNIT)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a value that rounds <i>TIMEPOINT</i> down to the time unit <i>TIMEINTERVALUNIT</i>.</p>
+ <p>E.g., <code>'12:44:31'.toDate.floor(MINUTE)</code> returns 12:44:00.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+TIMEPOINT.ceil(TIMEINTERVALUNIT)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a value that rounds <i>TIMEPOINT</i> up to the time unit <i>TIMEINTERVALUNIT</i>.</p>
+ <p>E.g., <code>'12:44:31'.toTime.floor(MINUTE)</code> returns 12:45:00.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+temporalOverlaps(TIMEPOINT1, TEMPORAL1, TIMEPOINT2, TEMPORAL2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if two time intervals defined by (<i>TIMEPOINT1</i>, <i>TEMPORAL1</i>) and (<i>TIMEPOINT2</i>, <i>TEMPORAL2</i>) overlap. The temporal values could be either a time point or a time interval.</p>
+ <p>E.g., <code>temporalOverlaps('2:55:00'.toTime, 1.hour, '3:30:00'.toTime, 2.hour)</code> returns TRUE.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+dateFormat(TIMESTAMP, STRING)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a string that formats <i>TIMESTAMP</i> with a specified format <i>STRING</i>. The format specification is given in the <a href="sql.html#date-format-specifier">Date Format Specifier table</a>.</p>
+ <p>E.g., <code>dateFormat(ts, '%Y, %d %M')</code> results in strings formatted as "2017, 05 May".</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+
+<div data-lang="scala" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Temporal functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING.toDate
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a SQL date parsed from <i>STRING</i> in form of "yyyy-MM-dd".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING.toTime
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a SQL time parsed from <i>STRING</i> in form of "HH:mm:ss".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING.toTimestamp
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a SQL timestamp parsed from <i>STRING</i> in form of "yyyy-MM-dd HH:mm:ss[.SSS]".</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.year
+NUMERIC.years
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Creates an interval of months for <i>NUMERIC</i> years.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.month
+NUMERIC.months
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Creates an interval of <i>NUMERIC</i> months.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.day
+NUMERIC.days
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Creates an interval of milliseconds for <i>NUMERIC</i> days.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.hour
+NUMERIC.hours
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Creates an interval of milliseconds for <i>NUMERIC</i> hours.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.minute
+NUMERIC.minutes
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Creates an interval of milliseconds for <i>NUMERIC</i> minutes.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.second
+NUMERIC.seconds
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Creates an interval of milliseconds for <i>NUMERIC</i> seconds.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.milli
+NUMERIC.millis
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Creates an interval of <i>NUMERIC</i> milliseconds.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+currentDate()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the current SQL date in the UTC time zone.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+currentTime()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the current SQL time in the UTC time zone.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+currentTimestamp()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the current SQL timestamp in the UTC time zone.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+localTime()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the current SQL time in local time zone.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+localTimestamp()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the current SQL timestamp in local time zone.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+TEMPORAL.extract(TIMEINTERVALUNIT)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a long value extracted from the <i>TIMEINTERVALUNIT</i> part of <i>temporal</i>.</p>
+ <p>E.g., <code>"2006-06-05".toDate.extract(TimeIntervalUnit.DAY)</code> returns 5; <code>"2006-06-05".toDate.extract(QUARTER)</code> returns 2.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+TIMEPOINT.floor(TIMEINTERVALUNIT)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a value that rounds <i>TIMEPOINT</i> down to the time unit <i>TIMEINTERVALUNIT</i>.</p>
+ <p>E.g., <code>"12:44:31".toDate.floor(TimeIntervalUnit.MINUTE)</code> returns 12:44:00.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+TIMEPOINT.ceil(TIMEINTERVALUNIT)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a value that rounds <i>TIMEPOINT</i> up to the time unit <i>TIMEINTERVALUNIT</i>.</p>
+ <p>E.g., <code>"12:44:31".toTime.floor(TimeIntervalUnit.MINUTE)</code> returns 12:45:00.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+temporalOverlaps(TIMEPOINT1, TEMPORAL1, TIMEPOINT2, TEMPORAL2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns TRUE if two time intervals defined by (<i>TIMEPOINT1</i>, <i>TEMPORAL1</i>) and (<i>TIMEPOINT2</i>, <i>TEMPORAL2</i>) overlap. The temporal values could be either a time point or a time interval.</p>
+ <p>E.g., <code>temporalOverlaps("2:55:00".toTime, 1.hour, "3:30:00".toTime, 2.hour)</code> returns TRUE.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+dateFormat(TIMESTAMP, STRING)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a string that formats <i>TIMESTAMP</i> with a specified format <i>STRING</i>. The format specification is given in the <a href="sql.html#date-format-specifier">Date Format Specifier table</a>.</p>
+ <p>E.g., <code>dateFormat('ts, "%Y, %d %M")</code> results in strings formatted as "2017, 05 May".</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+</div>
+
+{% top %}
+
+### Conditional Functions
+
+<div class="codetabs" markdown="1">
+
+<div data-lang="SQL" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Conditional functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight text %}
+CASE value
+WHEN value1_1 [, value1_2 ]* THEN result1
+[ WHEN value2_1 [, value2_2 ]* THEN result2 ]*
+[ ELSE resultZ ]
+END
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>resultX</i> when the first time <i>value</i> is contained in (<i>valueX_1, valueX_2, ...</i>).
+ When no value matches, returns <i>resultZ</i> if it is provided and returns NULL otherwise.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+CASE
+WHEN condition1 THEN result1
+[ WHEN condition2 THEN result2 ]*
+[ ELSE resultZ ]
+END
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>resultX</i> when the first <i>conditionX</i> is met.
+ When no condition is met, returns <i>resultZ</i> if it is provided and returns NULL otherwise.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+NULLIF(value1, value2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns NULL if <i>value1</i> is equal to <i>value2</i>; returns <i>value1</i> otherwise.</p>
+ <p>E.g., <code>NULLIF(5, 5)</code> returns NULL; <code>NULLIF(5, 0)</code> returns 5.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+COALESCE(value1, value2 [, value3 ]* )
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the first value that is not NULL from <i>value1, value2, ...</i>.</p>
+ <p>E.g., <code>COALESCE(NULL, 5)</code> returns 5.</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+
+<div data-lang="java" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Conditional functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight java %}
+BOOLEAN.?(VALUE1, VALUE2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>VALUE1</i> if <i>BOOLEAN</i> evaluates to TRUE; returns <i>VALUE2</i> otherwise.</p>
+ <p>E.g., <code>(42 > 5).?('A', 'B')</code> returns "A".</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+
+<div data-lang="scala" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Conditional functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight scala %}
+BOOLEAN.?(VALUE1, VALUE2)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns <i>VALUE1</i> if <i>BOOLEAN</i> evaluates to TRUE; returns <i>VALUE2</i> otherwise.</p>
+ <p>E.g., <code>(42 > 5).?("A", "B")</code> returns "A".</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+</div>
+
+{% top %}
+
+### Type Conversion Functions
+
+<div class="codetabs" markdown="1">
+
+<div data-lang="SQL" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Type conversion functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight text %}
+CAST(value AS type)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a new <i>value</i> being cast to type <i>type</i>. See the supported types <a href="sql.html#data-types">here</a>.</p>
+ <p>E.g., <code>CAST('42' AS INT)</code> returns 42.</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+
+<div data-lang="java" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Type conversion functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight java %}
+ANY.cast(TYPE)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a new <i>ANY</i> being cast to type <i>TYPE</i>. See the supported types <a href="sql.html#data-types">here</a>.</p>
+ <p>E.g., <code>'42'.cast(INT)</code> returns 42.</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+
+<div data-lang="scala" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Type conversion functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight scala %}
+ANY.cast(TYPE)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a new <i>ANY</i> being cast to type <i>TYPE</i>. See the supported types <a href="sql.html#data-types">here</a>.</p>
+ <p>E.g., <code>"42".cast(Types.INT)</code> returns 42.</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+
+</div>
+</div>
+
+{% top %}
+
+### Collection Functions
+
+<div class="codetabs" markdown="1">
+
+<div data-lang="SQL" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Collection functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight text %}
+CARDINALITY(array)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the number of elements in <i>array</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+array ‘[’ integer ‘]’
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the element at position <i>integer</i> in <i>array</i>. The index starts from 1.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+ELEMENT(array)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the sole element of <i>array</i> (whose cardinality should be one); returns NULL if <i>array</i> is empty. Throws an exception if <i>array</i> has more than one element.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+CARDINALITY(map)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the number of entries in <i>map</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+map ‘[’ value ‘]’
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the value specified by key <i>value</i> in <i>map</i>.</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+
+<div data-lang="java" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Collection functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight java %}
+ARRAY.cardinality()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the number of elements in <i>ARRAY</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+ARRAY.at(INT)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the element at position <i>INT</i> in <i>ARRAY</i>. The index starts from 1.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+ARRAY.element()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the sole element of <i>ARRAY</i> (whose cardinality should be one); returns NULL if <i>ARRAY</i> is empty. Throws an exception if <i>ARRAY</i> has more than one element.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+MAP.cardinality()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the number of entries in <i>MAP</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+MAP.at(ANY)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the value specified by key <i>ANY</i> in <i>MAP</i>.</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+
+<div data-lang="scala" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Collection functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight scala %}
+ARRAY.cardinality()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the number of elements in <i>ARRAY</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+ARRAY.at(INT)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the element at position <i>INT</i> in <i>ARRAY</i>. The index starts from 1.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+ARRAY.element()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the sole element of <i>ARRAY</i> (whose cardinality should be one); returns NULL if <i>ARRAY</i> is empty. Throws an exception if <i>ARRAY</i> has more than one element.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+MAP.cardinality()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the number of entries in <i>MAP</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+MAP.at(ANY)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the value specified by key <i>ANY</i> in <i>MAP</i>.</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+</div>
+
+
+### Value Construction Functions
+
+<div class="codetabs" markdown="1">
+<div data-lang="SQL" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Value construction functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight text %}
+ROW(value1, [, value2]*)
+(value1, [, value2]*)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a row created from a list of values (<i>value1, value2,</i>...).</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+ARRAY ‘[’ value1 [, value2 ]* ‘]’
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns an array created from a list of values (<i>value1, value2</i>, ...).</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+MAP ‘[’ value1, value2 [, value3, value4 ]* ‘]’
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a map created from a list of key-value pairs ((<i>value1, value2</i>), <i>(value3, value4)</i>, ...).</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+
+</div>
+<div data-lang="java" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Value constructor functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight java %}
+row(ANY1, ANY2, ...)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a row created from a list of object values (<i>ANY1, ANY2</i>, ...). Row is composite type that can be access via <a href="#value-access-functions">value access functions</a>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+array(ANY1, ANY2, ...)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns an array created from a list of object values (<i>ANY1, ANY2</i>, ...).</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+map(ANY1, ANY2, ANY3, ANY4, ...)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a map created from a list of key-value pairs ((<i>ANY1, ANY2</i>), <i>(ANY3, ANY4)</i>, ...).</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+NUMERIC.rows
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Creates a <i>NUMERIC</i> interval of rows (commonly used in window creation).</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+
+</div>
+<div data-lang="scala" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Value constructor functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight scala %}
+row(ANY1, ANY2, ...)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a row created from a list of object values (<i>ANY1, ANY2</i>, ...). Row is composite type that can be access via <a href="#value-access-functions">value access functions</a>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+array(ANY1, ANY2, ...)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns an array created from a list of object values (<i>ANY1, ANY2</i>, ...).</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+map(ANY1, ANY2, ANY3, ANY4, ...)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a map created from a list of key-value pairs ((<i>ANY1, ANY2</i>), <i>(ANY3, ANY4)</i>, ...).</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+NUMERIC.rows
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Creates a <i>NUMERIC</i> interval of rows (commonly used in window creation).</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+
+</div>
+</div>
+
+{% top %}
+
+### Value Access Functions
+
+<div class="codetabs" markdown="1">
+<div data-lang="SQL" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Value access functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight text %}
+tableName.compositeType.field
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the value of a field from a Flink composite type (e.g., Tuple, POJO) by name.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+tableName.compositeType.*
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a flat representation of a Flink composite type (e.g., Tuple, POJO) that converts each of its direct subtype into a separate field.
+ In most cases the fields of the flat representation are named similarly to the original fields but with a dollar separator (e.g., <code>mypojo$mytuple$f0</code>).</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+
+<div data-lang="java" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Value access functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight java %}
+COMPOSITE.get(STRING)
+COMPOSITE.get(INT)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the value of a field from a Flink composite type (e.g., Tuple, POJO) by name or index.</p>
+ <p>E.g., <code>pojo.get('myField')</code> or <code>tuple.get(0)</code>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+ANY.flatten()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a flat representation of a Flink composite type (e.g., Tuple, POJO) that converts each of its direct subtype into a separate field.
+ In most cases the fields of the flat representation are named similarly to the original fields but with a dollar separator (e.g., <code>mypojo$mytuple$f0</code>).</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+<div data-lang="scala" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Value access functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight scala %}
+COMPOSITE.get(STRING)
+COMPOSITE.get(INT)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the value of a field from a Flink composite type (e.g., Tuple, POJO) by name or index.</p>
+ <p>E.g., <code>'pojo.get("myField")</code> or <code>'tuple.get(0)</code>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+ANY.flatten()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a flat representation of a Flink composite type (e.g., Tuple, POJO) that converts each of its direct subtype into a separate field.
+ In most cases the fields of the flat representation are named similarly to the original fields but with a dollar separator (e.g., <code>mypojo$mytuple$f0</code>).</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+
+</div>
+</div>
+
+{% top %}
+
+### Grouping Functions
+
+<div class="codetabs" markdown="1">
+<div data-lang="SQL" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Grouping functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight text %}
+GROUP_ID()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns an integer that uniquely identifies the combination of grouping keys.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+GROUPING(expression1 [, expression2]* )
+GROUPING_ID(expression1 [, expression2]* )
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a bit vector of the given grouping expressions.</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+
+<div data-lang="Java" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Grouping functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+ <tbody>
+ </tbody>
+</table>
+</div>
+
+<div data-lang="Scala" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Grouping functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+ <tbody>
+ </tbody>
+</table>
+</div>
+</div>
+
+### Hash Functions
+
+<div class="codetabs" markdown="1">
+<div data-lang="SQL" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Hash functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight text %}
+MD5(string)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the MD5 hash of <i>string</i> as a string of 32 hexadecimal digits; returns NULL if <i>string</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+SHA1(string)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the SHA-1 hash of <i>string</i> as a string of 40 hexadecimal digits; returns NULL if <i>string</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+SHA224(string)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the SHA-224 hash of <i>string</i> as a string of 56 hexadecimal digits; returns NULL if <i>string</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+SHA256(string)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the SHA-256 hash of <i>string</i> as a string of 64 hexadecimal digits; returns NULL if <i>string</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+SHA384(string)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the SHA-384 hash of <i>string</i> as a string of 96 hexadecimal digits; returns NULL if <i>string</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+SHA512(string)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the SHA-512 hash of <i>string</i> as a string of 128 hexadecimal digits; returns NULL if <i>string</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+SHA2(string, hashLength)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the hash using the SHA-2 family of hash functions (SHA-224, SHA-256, SHA-384, or SHA-512). The first argument <i>string</i> is the string to be hashed and the second argument <i>hashLength</i> is the bit length of the result (224, 256, 384, or 512). Returns NULL if <i>string</i> or <i>hashLength</i> is NULL.
+ </p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+
+<div data-lang="java" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Hash functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight java %}
+STRING.md5()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the MD5 hash of <i>STRING</i> as a string of 32 hexadecimal digits; returns NULL if <i>STRING</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+STRING.sha1()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the SHA-1 hash of <i>STRING</i> as a string of 40 hexadecimal digits; returns NULL if <i>STRING</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+STRING.sha224()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the SHA-224 hash of <i>STRING</i> as a string of 56 hexadecimal digits; returns NULL if <i>STRING</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+STRING.sha256()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the SHA-256 hash of <i>STRING</i> as a string of 64 hexadecimal digits; returns NULL if <i>STRING</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+STRING.sha384()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the SHA-384 hash of <i>STRING</i> as a string of 96 hexadecimal digits; returns NULL if <i>STRING</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+STRING.sha512()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the SHA-512 hash of <i>STRING</i> as a string of 128 hexadecimal digits; returns NULL if <i>STRING</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+STRING.sha2(INT)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the SHA-2 family (SHA-224, SHA-256, SHA-384, or SHA-512) hashed value specified by <i>INT</i> (which could be 224, 256, 384, or 512) for <i>STRING</i>. Returns NULL if <i>STRING</i> or <i>INT</i> is NULL.
+ </p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+
+<div data-lang="scala" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Hash functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING.md5()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the MD5 hash of <i>STRING</i> as a string of 32 hexadecimal digits; returns NULL if <i>STRING</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING.sha1()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the SHA-1 hash of <i>STRING</i> as a string of 40 hexadecimal digits; returns NULL if <i>STRING</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING.sha224()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the SHA-224 hash of <i>STRING</i> as a string of 56 hexadecimal digits; returns NULL if <i>STRING</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING.sha256()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the SHA-256 hash of <i>STRING</i> as a string of 64 hexadecimal digits; returns NULL if <i>STRING</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING.sha384()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the SHA-384 hash of <i>STRING</i> as a string of 96 hexadecimal digits; returns NULL if <i>STRING</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING.sha512()
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the SHA-512 hash of <i>STRING</i> as a string of 128 hexadecimal digits; returns NULL if <i>STRING</i> is NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+STRING.sha2(INT)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the SHA-2 family (SHA-224, SHA-256, SHA-384, or SHA-512) hashed value specified by <i>INT</i> (which could be 224, 256, 384, or 512) for <i>STRING</i>. Returns NULL if <i>STRING</i> or <i>INT</i> is NULL.
+ </p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+</div>
+
+{% top %}
+
+### Auxiliary Functions
+
+<div class="codetabs" markdown="1">
+
+<div data-lang="SQL" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Auxiliary functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ </tbody>
+</table>
+</div>
+
+<div data-lang="java" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Auxiliary functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight java %}
+ANY.as(NAME1, NAME2, ...)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Specifies a name for <i>ANY</i> (a field). Additional names can be specified if the expression expands to multiple fields.</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+
+</div>
+<div data-lang="scala" markdown="1">
+
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Auxiliary functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight scala %}
+ANY.as(NAME1, NAME2, ...)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Specifies a name for <i>ANY</i> (a field). Additional names can be specified if the expression expands to multiple fields.</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+
+</div>
+
+Aggregate Functions
+-----------------------------
+The aggregate functions take an expression across all the rows as the input and return a single aggregated value as the result.
+
+<div class="codetabs" markdown="1">
+<div data-lang="SQL" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Aggregate functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight text %}
+COUNT([ ALL ] expression | DISTINCT expression1 [, expression2]*)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>By default or with ALL, returns the number of input rows for which <i>expression</i> is not NULL. Use DISTINCT for one unique instance of each value.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+COUNT(*)
+COUNT(1)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the number of input rows.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+AVG([ ALL | DISTINCT ] expression)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>By default or with keyword ALL, returns the average (arithmetic mean) of <i>expression</i> across all input rows. Use DISTINCT for one unique instance of each value.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+SUM([ ALL | DISTINCT ] expression)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>By default or with keyword ALL, returns the sum of <i>expression</i> across all input rows. Use DISTINCT for one unique instance of each value.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+MAX([ ALL | DISTINCT ] expression)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>By default or with keyword ALL, returns the maximum value of <i>expression</i> across all input rows. Use DISTINCT for one unique instance of each value.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+MIN([ ALL | DISTINCT ] expression)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>By default or with keyword ALL, returns the minimum value of <i>expression</i> across all input rows. Use DISTINCT for one unique instance of each value.</p>
+ </td>
+ </tr>
+ <tr>
+ <td>
+ {% highlight text %}
+STDDEV_POP([ ALL | DISTINCT ] expression)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>By default or with keyword ALL, returns the population standard deviation of <i>expression</i> across all input rows. Use DISTINCT for one unique instance of each value.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+STDDEV_SAMP([ ALL | DISTINCT ] expression)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>By default or with keyword ALL, returns the sample standard deviation of <i>expression</i> across all input rows. Use DISTINCT for one unique instance of each value.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+VAR_POP([ ALL | DISTINCT ] expression)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>By default or with keyword ALL, returns the population variance (square of the population standard deviation) of <i>expression</i> across all input rows. Use DISTINCT for one unique instance of each value.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+VAR_SAMP([ ALL | DISTINCT ] expression)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>By default or with keyword ALL, returns the sample variance (square of the sample standard deviation) of <i>expression</i> across all input rows. Use DISTINCT for one unique instance of each value.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+COLLECT([ ALL | DISTINCT ] expression)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>By default or with keyword ALL, returns a multiset of <i>expression</i> across all input rows. NULL values will be ignored. Use DISTINCT for one unique instance of each value.</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+
+</div>
+
+<div data-lang="java" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Aggregate functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight java %}
+FIELD.count
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the number of input rows for which <i>FIELD</i> is not NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+FIELD.avg
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the average (arithmetic mean) of <i>FIELD</i> across all input rows.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+FIELD.sum
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the sum of numeric field <i>FIELD</i> across all input rows. If all values are NULL, returns NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+FIELD.sum0
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the sum of numeric field <i>FIELD</i> across all input rows. If all values are NULL, returns 0.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+FIELD.max
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the maximum value of numeric field <i>FIELD</i> across all input rows.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+FIELD.min
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the minimum value of numeric field <i>FIELD</i> across all input rows.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+FIELD.stddevPop
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the population standard deviation of numeric field <i>FIELD</i> across all input rows.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+FIELD.stddevSamp
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the sample standard deviation of numeric field <i>FIELD</i> across all input rows.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+FIELD.varPop
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the population variance (square of the population standard deviation) of numeric field <i>FIELD</i> across all input rows.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+FIELD.varSamp
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the sample variance (square of the sample standard deviation) of numeric field <i>FIELD</i> across all input rows.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight java %}
+FIELD.collect
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a multiset of <i>FIELD</i> across all input rows.</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+
+<div data-lang="scala" markdown="1">
+<table class="table table-bordered">
+ <thead>
+ <tr>
+ <th class="text-left" style="width: 40%">Aggregate functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ <tr>
+ <td>
+ {% highlight scala %}
+FIELD.count
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the number of input rows for which <i>FIELD</i> is not NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+FIELD.avg
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the average (arithmetic mean) of <i>FIELD</i> across all input rows.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+FIELD.sum
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the sum of numeric field <i>FIELD</i> across all input rows. If all values are NULL, returns NULL.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+FIELD.sum0
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the sum of numeric field <i>FIELD</i> across all input rows. If all values are NULL, returns 0.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+FIELD.max
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the maximum value of numeric field <i>FIELD</i> across all input rows.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+FIELD.min
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the minimum value of numeric field <i>FIELD</i> across all input rows.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+FIELD.stddevPop
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the population standard deviation of numeric field <i>FIELD</i> across all input rows.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+FIELD.stddevSamp
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the sample standard deviation of numeric field <i>FIELD</i> across all input rows.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+FIELD.varPop
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the population variance (square of the population standard deviation) of numeric field <i>FIELD</i> across all input rows.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+FIELD.varSamp
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns the sample variance (square of the sample standard deviation) of numeric field <i>FIELD</i> across all input rows.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight scala %}
+FIELD.collect
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Returns a multiset of <i>FIELD</i> across all input rows.</p>
+ </td>
+ </tr>
+ </tbody>
+</table>
+</div>
+</div>
+
+{% top %}
diff --git a/docs/dev/table/sql.md b/docs/dev/table/sql.md
index 148f307..60f07ef 100644
--- a/docs/dev/table/sql.md
+++ b/docs/dev/table/sql.md
@@ -896,1802 +896,6 @@ Generic types and composite types (e.g., POJOs or Tuples) can be fields of a row
{% top %}
-Built-In Functions
-------------------
-
-Flink's SQL support comes with a set of built-in functions for data transformations. This section gives a brief overview of the available functions.
-
-<!--
-This list of SQL functions should be kept in sync with SqlExpressionTest to reduce confusion due to the large amount of SQL functions.
-The documentation is split up and ordered like the tests in SqlExpressionTest.
--->
-
-The Flink SQL functions (including their syntax) are a subset of Apache Calcite's built-in functions. Most of the documentation has been adopted from the [Calcite SQL reference](https://calcite.apache.org/docs/reference.html).
-
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Comparison functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
- <tr>
- <td>
- {% highlight text %}
-value1 = value2
-{% endhighlight %}
- </td>
- <td>
- <p>Equals.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-value1 <> value2
-{% endhighlight %}
- </td>
- <td>
- <p>Not equal.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-value1 > value2
-{% endhighlight %}
- </td>
- <td>
- <p>Greater than.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-value1 >= value2
-{% endhighlight %}
- </td>
- <td>
- <p>Greater than or equal.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-value1 < value2
-{% endhighlight %}
- </td>
- <td>
- <p>Less than.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-value1 <= value2
-{% endhighlight %}
- </td>
- <td>
- <p>Less than or equal.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-value IS NULL
-{% endhighlight %}
- </td>
- <td>
- <p>Returns TRUE if <i>value</i> is null.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-value IS NOT NULL
-{% endhighlight %}
- </td>
- <td>
- <p>Returns TRUE if <i>value</i> is not null.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-value1 IS DISTINCT FROM value2
-{% endhighlight %}
- </td>
- <td>
- <p>Returns TRUE if two values are not equal, treating null values as the same.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-value1 IS NOT DISTINCT FROM value2
-{% endhighlight %}
- </td>
- <td>
- <p>Returns TRUE if two values are equal, treating null values as the same.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-value1 BETWEEN [ASYMMETRIC | SYMMETRIC] value2 AND value3
-{% endhighlight %}
- </td>
- <td>
- <p>Returns TRUE if <i>value1</i> is greater than or equal to <i>value2</i> and less than or equal to <i>value3</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-value1 NOT BETWEEN value2 AND value3
-{% endhighlight %}
- </td>
- <td>
- <p>Returns TRUE if <i>value1</i> is less than <i>value2</i> or greater than <i>value3</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-string1 LIKE string2 [ ESCAPE string3 ]
-{% endhighlight %}
- </td>
- <td>
- <p>Returns TRUE if <i>string1</i> matches pattern <i>string2</i>. An escape character can be defined if necessary.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-string1 NOT LIKE string2 [ ESCAPE string3 ]
-{% endhighlight %}
- </td>
- <td>
- <p>Returns TRUE if <i>string1</i> does not match pattern <i>string2</i>. An escape character can be defined if necessary.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-string1 SIMILAR TO string2 [ ESCAPE string3 ]
-{% endhighlight %}
- </td>
- <td>
- <p>Returns TRUE if <i>string1</i> matches regular expression <i>string2</i>. An escape character can be defined if necessary.</p>
- </td>
- </tr>
-
-
- <tr>
- <td>
- {% highlight text %}
-string1 NOT SIMILAR TO string2 [ ESCAPE string3 ]
-{% endhighlight %}
- </td>
- <td>
- <p>Returns TRUE if <i>string1</i> does not match regular expression <i>string2</i>. An escape character can be defined if necessary.</p>
- </td>
- </tr>
-
-
- <tr>
- <td>
- {% highlight text %}
-value IN (value [, value]* )
-{% endhighlight %}
- </td>
- <td>
- <p> Returns TRUE if an expression exists in a given list of expressions. This is a shorthand for multiple OR conditions. If the testing set contains NULL, the result will be NULL if the element can not be found and TRUE if it can be found. If the element is NULL, the result is always NULL. E.g. "42 IN (1, 2, 3)" leads to FALSE.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-value NOT IN (value [, value]* )
-{% endhighlight %}
- </td>
- <td>
- <p>Returns TRUE if <i>value</i> is not equal to every value in a list.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-EXISTS (sub-query)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns TRUE if <i>sub-query</i> returns at least one row. Only supported if the operation can be rewritten in a join and group operation.</p>
- <p><b>Note:</b> For streaming queries the operation is rewritten in a join and group operation. The required state to compute the query result might grow infinitely depending on the number of distinct input rows. Please provide a query configuration with valid retention interval to prevent excessive state size. See <a href="streaming.html">Streaming Concepts</a> for details.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-value IN (sub-query)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns TRUE if <i>value</i> is equal to a row returned by sub-query.</p>
- <p><b>Note:</b> For streaming queries the operation is rewritten in a join and group operation. The required state to compute the query result might grow infinitely depending on the number of distinct input rows. Please provide a query configuration with valid retention interval to prevent excessive state size. See <a href="streaming.html">Streaming Concepts</a> for details.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-value NOT IN (sub-query)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns TRUE if <i>value</i> is not equal to every row returned by sub-query.</p>
- <p><b>Note:</b> For streaming queries the operation is rewritten in a join and group operation. The required state to compute the query result might grow infinitely depending on the number of distinct input rows. Please provide a query configuration with valid retention interval to prevent excessive state size. See <a href="streaming.html">Streaming Concepts</a> for details.</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Logical functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
- <tr>
- <td>
- {% highlight text %}
-boolean1 OR boolean2
-{% endhighlight %}
- </td>
- <td>
- <p>Returns TRUE if <i>boolean1</i> is TRUE or <i>boolean2</i> is TRUE. Supports three-valued logic.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-boolean1 AND boolean2
-{% endhighlight %}
- </td>
- <td>
- <p>Returns TRUE if <i>boolean1</i> and <i>boolean2</i> are both TRUE. Supports three-valued logic.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-NOT boolean
-{% endhighlight %}
- </td>
- <td>
- <p>Returns TRUE if <i>boolean</i> is not TRUE; returns UNKNOWN if <i>boolean</i> is UNKNOWN.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-boolean IS FALSE
-{% endhighlight %}
- </td>
- <td>
- <p>Returns TRUE if <i>boolean</i> is FALSE; returns FALSE if <i>boolean</i> is UNKNOWN.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-boolean IS NOT FALSE
-{% endhighlight %}
- </td>
- <td>
- <p>Returns TRUE if <i>boolean</i> is not FALSE; returns TRUE if <i>boolean</i> is UNKNOWN.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-boolean IS TRUE
-{% endhighlight %}
- </td>
- <td>
- <p>Returns TRUE if <i>boolean</i> is TRUE; returns FALSE if <i>boolean</i> is UNKNOWN.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-boolean IS NOT TRUE
-{% endhighlight %}
- </td>
- <td>
- <p>Returns TRUE if <i>boolean</i> is not TRUE; returns TRUE if <i>boolean</i> is UNKNOWN.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-boolean IS UNKNOWN
-{% endhighlight %}
- </td>
- <td>
- <p>Returns TRUE if <i>boolean</i> is UNKNOWN.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-boolean IS NOT UNKNOWN
-{% endhighlight %}
- </td>
- <td>
- <p>Returns TRUE if <i>boolean</i> is not UNKNOWN.</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Arithmetic functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
- <tr>
- <td>
- {% highlight text %}
-+ numeric
-{% endhighlight %}
- </td>
- <td>
- <p>Returns <i>numeric</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-- numeric
-{% endhighlight %}
- </td>
- <td>
- <p>Returns negative <i>numeric</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-numeric1 + numeric2
-{% endhighlight %}
- </td>
- <td>
- <p>Returns <i>numeric1</i> plus <i>numeric2</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-numeric1 - numeric2
-{% endhighlight %}
- </td>
- <td>
- <p>Returns <i>numeric1</i> minus <i>numeric2</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-numeric1 * numeric2
-{% endhighlight %}
- </td>
- <td>
- <p>Returns <i>numeric1</i> multiplied by <i>numeric2</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-numeric1 / numeric2
-{% endhighlight %}
- </td>
- <td>
- <p>Returns <i>numeric1</i> divided by <i>numeric2</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-POWER(numeric1, numeric2)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns <i>numeric1</i> raised to the power of <i>numeric2</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-ABS(numeric)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the absolute value of <i>numeric</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-MOD(numeric1, numeric2)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the remainder (modulus) of <i>numeric1</i> divided by <i>numeric2</i>. The result is negative only if <i>numeric1</i> is negative.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-SQRT(numeric)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the square root of <i>numeric</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-LN(numeric)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the natural logarithm (base e) of <i>numeric</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-LOG10(numeric)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the base 10 logarithm of <i>numeric</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-LOG2(numeric)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the base 2 logarithm of <i>numeric</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-LOG(x numeric)
-LOG(b numeric, x numeric)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the logarithm of a <i>numeric</i>.</p>
- <p>If called with one parameter, this function returns the natural logarithm of <code>x</code>. If called with two parameters, this function returns the logarithm of <code>x</code> to the base <code>b</code>. <code>x</code> must be greater than 0. <code>b</code> must be greater than 1.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-EXP(numeric)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns e raised to the power of <i>numeric</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-CEIL(numeric)
-CEILING(numeric)
-{% endhighlight %}
- </td>
- <td>
- <p>Rounds <i>numeric</i> up, and returns the smallest number that is greater than or equal to <i>numeric</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-FLOOR(numeric)
-{% endhighlight %}
- </td>
- <td>
- <p>Rounds <i>numeric</i> down, and returns the largest number that is less than or equal to <i>numeric</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-SIN(numeric)
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the sine of a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-COS(numeric)
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the cosine of a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-TAN(numeric)
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the tangent of a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-COT(numeric)
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the cotangent of a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-ATAN2(numeric, numeric)
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the arc tangent of a given coordinate.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-ASIN(numeric)
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the arc sine of a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-ACOS(numeric)
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the arc cosine of a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-ATAN(numeric)
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the arc tangent of a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-DEGREES(numeric)
-{% endhighlight %}
- </td>
- <td>
- <p>Converts <i>numeric</i> from radians to degrees.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-RADIANS(numeric)
-{% endhighlight %}
- </td>
- <td>
- <p>Converts <i>numeric</i> from degrees to radians.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-SIGN(numeric)
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the signum of a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-ROUND(numeric, int)
-{% endhighlight %}
- </td>
- <td>
- <p>Rounds the given number to <i>integer</i> places right to the decimal point.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-PI()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a value that is closer than any other value to pi.</p>
- </td>
- </tr>
- <tr>
- <td>
- {% highlight text %}
-E()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a value that is closer than any other value to e.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-RAND()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive).</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-RAND(seed integer)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive) with a initial seed. Two RAND functions will return identical sequences of numbers if they have same initial seed.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-RAND_INTEGER(bound integer)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a pseudorandom integer value between 0.0 (inclusive) and the specified value (exclusive).</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-RAND_INTEGER(seed integer, bound integer)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a pseudorandom integer value between 0.0 (inclusive) and the specified value (exclusive) with a initial seed. Two RAND_INTEGER functions will return identical sequences of numbers if they have same initial seed and same bound.</p>
- </td>
- </tr>
-
- <tr>
- <td>
-{% highlight text %}
-BIN(numeric)
- {% endhighlight %}
- </td>
- <td>
- <p>Returns a string representation of an integer numeric value in binary format. Returns null if numeric is null. E.g. "4" leads to "100", "12" leads to "1100".</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">String functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
- <tr>
- <td>
- {% highlight text %}
-string || string
-{% endhighlight %}
- </td>
- <td>
- <p>Concatenates two character strings.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-CHAR_LENGTH(string)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the number of characters in a character string.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-CHARACTER_LENGTH(string)
-{% endhighlight %}
- </td>
- <td>
- <p>As CHAR_LENGTH(<i>string</i>).</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-UPPER(string)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a character string converted to upper case.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-LOWER(string)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a character string converted to lower case.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-POSITION(string1 IN string2)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the position of the first occurrence of <i>string1</i> in <i>string2</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-TRIM( { BOTH | LEADING | TRAILING } string1 FROM string2)
-{% endhighlight %}
- </td>
- <td>
- <p>Removes leading and/or trailing characters from <i>string2</i>. By default, whitespaces at both sides are removed.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-OVERLAY(string1 PLACING string2 FROM integer [ FOR integer2 ])
-{% endhighlight %}
- </td>
- <td>
- <p>Replaces a substring of <i>string1</i> with <i>string2</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-SUBSTRING(string FROM integer)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a substring of a character string starting at a given point.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-SUBSTRING(string FROM integer FOR integer)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a substring of a character string starting at a given point with a given length.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-INITCAP(string)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns string 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.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-CONCAT(string1, string2,...)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the string that results from concatenating the arguments. Returns NULL if any argument is NULL. E.g. <code>CONCAT("AA", "BB", "CC")</code> returns <code>AABBCC</code>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-CONCAT_WS(separator, string1, string2,...)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the string that results from concatenating the arguments using a separator. The separator is added between the strings to be concatenated. Returns NULL If the separator is NULL. CONCAT_WS() does not skip empty strings. However, it does skip any NULL argument. E.g. <code>CONCAT_WS("~", "AA", "BB", "", "CC")</code> returns <code>AA~BB~~CC</code></p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-LPAD(text string, len integer, pad string)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the string text left-padded with the string pad to a length of len characters. If text is longer than len, the return value is shortened to len characters. E.g. <code>LPAD('hi',4,'??')</code> returns <code>??hi</code>, <code>LPAD('hi',1,'??')</code> returns <code>h</code>.</p>
- </td>
- </tr>
- <tr>
- <td>
- {% highlight text %}
-RPAD(text string, len integer, pad string)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the string text right-padded with the string pad to a length of len characters. If text is longer than len, the return value is shortened to len characters. E.g. <code>RPAD('hi',4,'??')</code> returns <code>hi??</code>, <code>RPAD('hi',1,'??')</code> returns <code>h</code>.</p>
- </td>
- </tr>
- <tr>
- <td>
- {% highlight text %}
-FROM_BASE64(text string)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the base string decoded with base64, if text is NULL, returns NULL. E.g. <code>FROM_BASE64('aGVsbG8gd29ybGQ=')</code> returns <code>hello world</code>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-TO_BASE64(string)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the base64-encoded result of <i>string</i>; returns NULL if <i>string</i> is NULL.</p>
- <p>E.g., <code>TO_BASE64("hello world")</code> returns "aGVsbG8gd29ybGQ=".</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Conditional functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
- <tr>
- <td>
- {% highlight text %}
-CASE value
-WHEN value1 [, value11 ]* THEN result1
-[ WHEN valueN [, valueN1 ]* THEN resultN ]*
-[ ELSE resultZ ]
-END
-{% endhighlight %}
- </td>
- <td>
- <p>Simple case.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-CASE
-WHEN condition1 THEN result1
-[ WHEN conditionN THEN resultN ]*
-[ ELSE resultZ ]
-END
-{% endhighlight %}
- </td>
- <td>
- <p>Searched case.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-NULLIF(value, value)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns NULL if the values are the same. For example, <code>NULLIF(5, 5)</code> returns NULL; <code>NULLIF(5, 0)</code> returns 5.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-COALESCE(value, value [, value ]* )
-{% endhighlight %}
- </td>
- <td>
- <p>Provides a value if the first value is null. For example, <code>COALESCE(NULL, 5)</code> returns 5.</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Type conversion functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
- <tr>
- <td>
- {% highlight text %}
-CAST(value AS type)
-{% endhighlight %}
- </td>
- <td>
- <p>Converts a value to a given type.</p>
- </td>
- </tr>
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Temporal functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
- <tr>
- <td>
- {% highlight text %}
-DATE string
-{% endhighlight %}
- </td>
- <td>
- <p>Parses a date string in the form "yy-mm-dd" to a SQL date.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-TIME string
-{% endhighlight %}
- </td>
- <td>
- <p>Parses a time <i>string</i> in the form "hh:mm:ss" to a SQL time.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-TIMESTAMP string
-{% endhighlight %}
- </td>
- <td>
- <p>Parses a timestamp <i>string</i> in the form "yy-mm-dd hh:mm:ss.fff" to a SQL timestamp.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-INTERVAL string range
-{% endhighlight %}
- </td>
- <td>
- <p>Parses an interval <i>string</i> in the form "dd hh:mm:ss.fff" for SQL intervals of milliseconds or "yyyy-mm" for SQL intervals of months. An interval range might be e.g. <code>DAY</code>, <code>MINUTE</code>, <code>DAY TO HOUR</code>, or <code>DAY TO SECOND</code> for intervals of milliseconds; <code>YEAR</code> or <code>YEAR TO MONTH</code> for intervals of months. E.g. <code>INTERVAL '10 00:00:00.004' DAY TO SECOND</code>, <code>INTERVAL '10' DAY</code>, or <code>INTERVAL ' [...]
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-CURRENT_DATE
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the current SQL date in UTC time zone.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-CURRENT_TIME
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the current SQL time in UTC time zone.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-CURRENT_TIMESTAMP
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the current SQL timestamp in UTC time zone.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-LOCALTIME
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the current SQL time in local time zone.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-LOCALTIMESTAMP
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the current SQL timestamp in local time zone.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-EXTRACT(timeintervalunit FROM temporal)
-{% endhighlight %}
- </td>
- <td>
- <p>Extracts parts of a time point or time interval. Returns the part as a long value. E.g. <code>EXTRACT(DAY FROM DATE '2006-06-05')</code> leads to 5.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-YEAR(date)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the year from a SQL date. Equivalent to <code>EXTRACT(YEAR FROM date)</code>. E.g. <code>YEAR(DATE '1994-09-27')</code> leads to 1994.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-FLOOR(timepoint TO timeintervalunit)
-{% endhighlight %}
- </td>
- <td>
- <p>Rounds a time point down to the given unit. E.g. <code>FLOOR(TIME '12:44:31' TO MINUTE)</code> leads to 12:44:00.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-CEIL(timepoint TO timeintervalunit)
-{% endhighlight %}
- </td>
- <td>
- <p>Rounds a time point up to the given unit. E.g. <code>CEIL(TIME '12:44:31' TO MINUTE)</code> leads to 12:45:00.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-QUARTER(date)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the quarter of a year from a SQL date (an integer between 1 and 4). Equivalent to <code>EXTRACT(QUARTER FROM date)</code>. E.g. <code>QUARTER(DATE '1994-09-27')</code> leads to 3. </p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-MONTH(date)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the month of a year from a SQL date (an integer between 1 and 12). Equivalent to <code>EXTRACT(MONTH FROM date)</code>. E.g. <code>MONTH(DATE '1994-09-27')</code> leads to 9. </p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-WEEK(date)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the week of a year from a SQL date (an integer between 1 and 53). Equivalent to <code>EXTRACT(WEEK FROM date)</code>. E.g. <code>WEEK(DATE '1994-09-27')</code> leads to 39. </p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-DAYOFYEAR(date)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the day of a year from a SQL date (an integer between 1 and 366). Equivalent to <code>EXTRACT(DOY FROM date)</code>. E.g. <code>DAYOFYEAR(DATE '1994-09-27')</code> leads to 270. </p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-DAYOFMONTH(date)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the day of a month from a SQL date (an integer between 1 and 31). Equivalent to <code>EXTRACT(DAY FROM date)</code>. E.g. <code>DAYOFMONTH(DATE '1994-09-27')</code> leads to 27. </p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-DAYOFWEEK(date)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the day of a week from a SQL date (an integer between 1 and 7; Sunday = 1). Equivalent to <code>EXTRACT(DOW FROM date)</code>. E.g. <code>DAYOFWEEK(DATE '1994-09-27')</code> leads to 3. </p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-HOUR(timestamp)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the hour of a day from a SQL timestamp (an integer between 0 and 23). Equivalent to <code>EXTRACT(HOUR FROM timestamp)</code>. E.g. <code>HOUR(TIMESTAMP '1994-09-27 13:14:15')</code> leads to 13. </p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-MINUTE(timestamp)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the minute of an hour from a SQL timestamp (an integer between 0 and 59). Equivalent to <code>EXTRACT(MINUTE FROM timestamp)</code>. E.g. <code>MINUTE(TIMESTAMP '1994-09-27 13:14:15')</code> leads to 14. </p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-SECOND(timestamp)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the second of a minute from a SQL timestamp (an integer between 0 and 59). Equivalent to <code>EXTRACT(SECOND FROM timestamp)</code>. E.g. <code>SECOND(TIMESTAMP '1994-09-27 13:14:15')</code> leads to 15. </p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-(timepoint, temporal) OVERLAPS (timepoint, temporal)
-{% endhighlight %}
- </td>
- <td>
- <p>Determines whether two anchored time intervals overlap. Time point and temporal are transformed into a range defined by two time points (start, end). The function evaluates <code>leftEnd >= rightStart && rightEnd >= leftStart</code>. E.g. <code>(TIME '2:55:00', INTERVAL '1' HOUR) OVERLAPS (TIME '3:30:00', INTERVAL '2' HOUR)</code> leads to true; <code>(TIME '9:00:00', TIME '10:00:00') OVERLAPS (TIME '10:15:00', INTERVAL '3' HOUR)</code> leads to false.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-DATE_FORMAT(timestamp, format)
-{% endhighlight %}
- </td>
- <td>
- <p>Formats <code>timestamp</code> as a string using a specified <code>format</code> string. The format must be compatible with MySQL's date formatting syntax as used by the <code>date_parse</code> function. The format specification is given in the <a href="#date-format-specifier">Date Format Specifier table</a> below.</p>
- <p>For example <code>DATE_FORMAT(ts, '%Y, %d %M')</code> results in strings formatted as <code>"2017, 05 May"</code>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-TIMESTAMPADD(unit, interval, timestamp)
-{% endhighlight %}
- </td>
- <td>
- <p>Adds a (signed) integer interval to a timestamp. The unit for the interval is given by the unit argument, which should be one of the following values: <code>SECOND</code>, <code>MINUTE</code>, <code>HOUR</code>, <code>DAY</code>, <code>WEEK</code>, <code>MONTH</code>, <code>QUARTER</code>, or <code>YEAR</code>. E.g. <code>TIMESTAMPADD(WEEK, 1, '2003-01-02')</code> leads to <code>2003-01-09</code>.</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Aggregate functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
- <tr>
- <td>
- {% highlight text %}
-COUNT(value [, value]* )
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the number of input rows for which <i>value</i> is not null. Use <code>COUNT(DISTINCT value)</code> for the number of unique values in the column or expression.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-COUNT(*)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the number of input rows.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-AVG(numeric)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the average (arithmetic mean) of <i>numeric</i> across all input values.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-SUM(numeric)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the sum of <i>numeric</i> across all input values. Use <code>SUM(DISTINCT value)</code> for the sum of unique values in the column or expression.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-MAX(value)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the maximum value of <i>value</i> across all input values.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-MIN(value)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the minimum value of <i>value</i> across all input values.</p>
- </td>
- </tr>
- <tr>
- <td>
- {% highlight text %}
-STDDEV_POP(value)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the population standard deviation of the numeric field across all input values.</p>
- </td>
- </tr>
-
-<tr>
- <td>
- {% highlight text %}
-STDDEV_SAMP(value)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the sample standard deviation of the numeric field across all input values.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-VAR_POP(value)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the population variance (square of the population standard deviation) of the numeric field across all input values.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-VAR_SAMP(value)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the sample variance (square of the sample standard deviation) of the numeric field across all input values.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-COLLECT(value)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a multiset of the <i>value</i>s. null input <i>value</i> will be ignored. Return an empty multiset if only null values are added. </p>
- </td>
- </tr>
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Grouping functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
- <tr>
- <td>
- {% highlight text %}
-GROUP_ID()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns an integer that uniquely identifies the combination of grouping keys.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-GROUPING(expression)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns 1 if <i>expression</i> is rolled up in the current row’s grouping set, 0 otherwise.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-GROUPING_ID(expression [, expression]* )
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a bit vector of the given grouping expressions.</p>
- </td>
- </tr>
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Value access functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
- <tr>
- <td>
- {% highlight text %}
-tableName.compositeType.field
-{% endhighlight %}
- </td>
- <td>
- <p>Accesses the field of a Flink composite type (such as Tuple, POJO, etc.) by name and returns it's value.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-tableName.compositeType.*
-{% endhighlight %}
- </td>
- <td>
- <p>Converts a Flink composite type (such as Tuple, POJO, etc.) and all of its direct subtypes into a flat representation where every subtype is a separate field.</p>
- </td>
- </tr>
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Value constructor functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight text %}
-(value, [, value]*)
-{% endhighlight %}
- </td>
- <td>
- <p>Creates a row from a list of values.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-ROW(value, [, value]*)
-{% endhighlight %}
- </td>
- <td>
- <p>Creates a row from a list of values.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-ARRAY ‘[’ value [, value ]* ‘]’
-{% endhighlight %}
- </td>
- <td>
- <p>Creates an array from a list of values.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-MAP ‘[’ key, value [, key, value ]* ‘]’
-{% endhighlight %}
- </td>
- <td>
- <p>Creates a map from a list of key-value pairs.</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Array functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight text %}
-CARDINALITY(ARRAY)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the number of elements of an array.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-array ‘[’ index ‘]’
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the element at a particular position in an array. The index starts at 1.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-ELEMENT(ARRAY)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the sole element of an array with a single element. Returns <code>null</code> if the array is empty. Throws an exception if the array has more than one element.</p>
- </td>
- </tr>
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Map functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight text %}
-CARDINALITY(MAP)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the number of entries of a map.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-map ‘[’ key ‘]’
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the value specified by a particular key in a map.</p>
- </td>
- </tr>
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Hash functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
- <tr>
- <td>
- {% highlight text %}
-MD5(string)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the MD5 hash of the <i>string</i> argument as a string of 32 hexadecimal digits; null if <i>string</i> is null.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-SHA1(string)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the SHA-1 hash of the <i>string</i> argument as a string of 40 hexadecimal digits; null if <i>string</i> is null.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-SHA224(string)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the SHA-224 hash of the <i>string</i> argument as a string of 56 hexadecimal digits; null if <i>string</i> is null.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-SHA256(string)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the SHA-256 hash of the <i>string</i> argument as a string of 64 hexadecimal digits; null if <i>string</i> is null.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-SHA384(string)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the SHA-384 hash of the <i>string</i> argument as a string of 96 hexadecimal digits; null if <i>string</i> is null.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-SHA512(string)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the SHA-512 hash of the <i>string</i> argument as a string of 128 hexadecimal digits; null if <i>string</i> is null.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-SHA2(string, hashLength)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the hash using the SHA-2 family of hash functions (SHA-224, SHA-256, SHA-384, or SHA-512). The first argument <i>string</i> is the string to be hashed. <i>hashLength</i> is the bit length of the result (either 224, 256, 384, or 512). Returns <i>null</i> if <i>string</i> or <i>hashLength</i> is <i>null</i>.
- </p>
- </td>
- </tr>
- </tbody>
-</table>
-
-### Unsupported Functions
-
-The following functions are not supported yet:
-
-- Binary string operators and functions
-- System functions
-
-{% top %}
-
Reserved Keywords
-----------------
@@ -2817,4 +1021,3 @@ A, ABS, ABSOLUTE, ACTION, ADA, ADD, ADMIN, AFTER, ALL, ALLOCATE, ALLOW, ALTER, A
{% top %}
-
diff --git a/docs/dev/table/tableApi.md b/docs/dev/table/tableApi.md
index b702ddd..08ea36d 100644
--- a/docs/dev/table/tableApi.md
+++ b/docs/dev/table/tableApi.md
@@ -1655,3149 +1655,3 @@ In order to work with temporal values the Table API supports Java SQL's Date, Ti
Temporal intervals can be represented as number of months (`Types.INTERVAL_MONTHS`) or number of milliseconds (`Types.INTERVAL_MILLIS`). Intervals of same type can be added or subtracted (e.g. `1.hour + 10.minutes`). Intervals of milliseconds can be added to time points (e.g. `"2016-08-10".toDate + 5.days`).
{% top %}
-
-Built-In Functions
-------------------
-
-The Table API comes with a set of built-in functions for data transformations. This section gives a brief overview of the available functions.
-
-<div class="codetabs" markdown="1">
-<div data-lang="java" markdown="1">
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Comparison functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight java %}
-ANY === ANY
-{% endhighlight %}
- </td>
- <td>
- <p>Equals.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-ANY !== ANY
-{% endhighlight %}
- </td>
- <td>
- <p>Not equal.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-ANY > ANY
-{% endhighlight %}
- </td>
- <td>
- <p>Greater than.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-ANY >= ANY
-{% endhighlight %}
- </td>
- <td>
- <p>Greater than or equal.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-ANY < ANY
-{% endhighlight %}
- </td>
- <td>
- <p>Less than.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-ANY <= ANY
-{% endhighlight %}
- </td>
- <td>
- <p>Less than or equal.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-ANY.isNull
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true if the given expression is null.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-ANY.isNotNull
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true if the given expression is not null.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-STRING.like(STRING)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true, if a string matches the specified LIKE pattern. E.g. "Jo_n%" matches all strings that start with "Jo(arbitrary letter)n".</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-STRING.similar(STRING)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true, if a string matches the specified SQL regex pattern. E.g. "A+" matches all strings that consist of at least one "A".</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-ANY.in(ANY, ANY, ...)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true if an expression exists in a given list of expressions. This is a shorthand for multiple OR conditions. If the testing set contains null, the result will be null if the element can not be found and true if it can be found. If element is null, the result is always null. E.g. "42.in(1, 2, 3)" leads to false.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-ANY.in(TABLE)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true if an expression exists in a given table sub-query. The sub-query table must consist of one column. This column must have the same data type as the expression.</p>
- <p><b>Note:</b> For streaming queries the operation is rewritten in a join and group operation. The required state to compute the query result might grow infinitely depending on the number of distinct input rows. Please provide a query configuration with valid retention interval to prevent excessive state size. See <a href="streaming.html">Streaming Concepts</a> for details.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-ANY.between(lowerBound, upperBound)
- {% endhighlight %}
- </td>
- <td>
- <p>Returns true if the given expression is between <i>lowerBound</i> and <i>upperBound</i> (both inclusive). False otherwise. The parameters must be numeric types or identical comparable types.
- </p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-ANY.notBetween(lowerBound, upperBound)
- {% endhighlight %}
- </td>
- <td>
- <p>Returns true if the given expression is not between <i>lowerBound</i> and <i>upperBound</i> (both inclusive). False otherwise. The parameters must be numeric types or identical comparable types.
- </p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Logical functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight java %}
-boolean1 || boolean2
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true if <i>boolean1</i> is true or <i>boolean2</i> is true. Supports three-valued logic.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-boolean1 && boolean2
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true if <i>boolean1</i> and <i>boolean2</i> are both true. Supports three-valued logic.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-!BOOLEAN
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true if boolean expression is not true; returns null if boolean is null.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-BOOLEAN.isTrue
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true if the given boolean expression is true. False otherwise (for null and false).</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-BOOLEAN.isFalse
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true if given boolean expression is false. False otherwise (for null and true).</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-BOOLEAN.isNotTrue
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true if the given boolean expression is not true (for null and false). False otherwise.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-BOOLEAN.isNotFalse
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true if given boolean expression is not false (for null and true). False otherwise.</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Arithmetic functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight java %}
-+ numeric
-{% endhighlight %}
- </td>
- <td>
- <p>Returns <i>numeric</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-- numeric
-{% endhighlight %}
- </td>
- <td>
- <p>Returns negative <i>numeric</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-numeric1 + numeric2
-{% endhighlight %}
- </td>
- <td>
- <p>Returns <i>numeric1</i> plus <i>numeric2</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-numeric1 - numeric2
-{% endhighlight %}
- </td>
- <td>
- <p>Returns <i>numeric1</i> minus <i>numeric2</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-numeric1 * numeric2
-{% endhighlight %}
- </td>
- <td>
- <p>Returns <i>numeric1</i> multiplied by <i>numeric2</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-numeric1 / numeric2
-{% endhighlight %}
- </td>
- <td>
- <p>Returns <i>numeric1</i> divided by <i>numeric2</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-numeric1.power(numeric2)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns <i>numeric1</i> raised to the power of <i>numeric2</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.abs()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the absolute value of given value.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-numeric1 % numeric2
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the remainder (modulus) of <i>numeric1</i> divided by <i>numeric2</i>. The result is negative only if <i>numeric1</i> is negative.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.sqrt()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the square root of a given value.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.ln()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the natural logarithm of given value.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.log10()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the base 10 logarithm of given value.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.log2()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the base 2 logarithm of given value.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-numeric1.log()
-numeric1.log(numeric2)
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the logarithm of a given numeric value.</p>
- <p>If called without a parameter, this function returns the natural logarithm of <code>numeric1</code>. If called with a parameter <code>numeric2</code>, this function returns the logarithm of <code>numeric1</code> to the base <code>numeric2</code>. <code>numeric1</code> must be greater than 0. <code>numeric2</code> must be greater than 1.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.exp()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the Euler's number raised to the given power.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.ceil()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the smallest integer greater than or equal to a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.floor()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the largest integer less than or equal to a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.sin()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the sine of a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.cos()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the cosine of a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.tan()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the tangent of a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.cot()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the cotangent of a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.asin()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the arc sine of a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-atan2(NUMERIC, NUMERIC)
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the arc tangent of a given coordinate.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.acos()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the arc cosine of a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.atan()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the arc tangent of a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.degrees()
-{% endhighlight %}
- </td>
- <td>
- <p>Converts <i>numeric</i> from radians to degrees.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.radians()
-{% endhighlight %}
- </td>
- <td>
- <p>Converts <i>numeric</i> from degrees to radians.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.sign()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the signum of a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.round(INT)
-{% endhighlight %}
- </td>
- <td>
- <p>Rounds the given number to <i>integer</i> places right to the decimal point.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-pi()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a value that is closer than any other value to pi.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-e()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a value that is closer than any other value to e.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-rand()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive).</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-rand(seed integer)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive) with a initial seed. Two rand functions will return identical sequences of numbers if they have same initial seed.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-randInteger(bound integer)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a pseudorandom integer value between 0.0 (inclusive) and the specified value (exclusive).</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-randInteger(seed integer, bound integer)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a pseudorandom integer value between 0.0 (inclusive) and the specified value (exclusive) with a initial seed. Two randInteger functions will return identical sequences of numbers if they have same initial seed and same bound.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.bin()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a string representation of an integer numeric value in binary format. Returns null if <i>numeric</i> is null. E.g. "4" leads to "100", "12" leads to "1100".</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">String functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight java %}
-STRING + STRING
-{% endhighlight %}
- </td>
- <td>
- <p>Concatenates two character strings.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-STRING.charLength()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the length of a String.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-STRING.upperCase()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns all of the characters in a string in upper case using the rules of the default locale.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-STRING.lowerCase()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns all of the characters in a string in lower case using the rules of the default locale.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-STRING.position(STRING)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the position of string in an other string starting at 1. Returns 0 if string could not be found. E.g. <code>'a'.position('bbbbba')</code> leads to 6.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-STRING.trim(LEADING, STRING)
-STRING.trim(TRAILING, STRING)
-STRING.trim(BOTH, STRING)
-STRING.trim(BOTH)
-STRING.trim()
-{% endhighlight %}
- </td>
- <td>
- <p>Removes leading and/or trailing characters from the given string. By default, whitespaces at both sides are removed.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-STRING.overlay(STRING, INT)
-STRING.overlay(STRING, INT, INT)
-{% endhighlight %}
- </td>
- <td>
- <p>Replaces a substring of string with a string starting at a position (starting at 1). An optional length specifies how many characters should be removed. E.g. <code>'xxxxxtest'.overlay('xxxx', 6)</code> leads to "xxxxxxxxx", <code>'xxxxxtest'.overlay('xxxx', 6, 2)</code> leads to "xxxxxxxxxst".</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-STRING.substring(INT)
-{% endhighlight %}
- </td>
- <td>
- <p>Creates a substring of the given string beginning at the given index to the end. The start index starts at 1 and is inclusive.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-STRING.substring(INT, INT)
-{% endhighlight %}
- </td>
- <td>
- <p>Creates a substring of the given string at the given index for the given length. The index starts at 1 and is inclusive, i.e., the character at the index is included in the substring. The substring has the specified length or less.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-STRING.initCap()
-{% endhighlight %}
- </td>
-
- <td>
- <p>Converts the initial letter of each word in a string to uppercase. Assumes a string containing only [A-Za-z0-9], everything else is treated as whitespace.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-STRING.lpad(len INT, pad STRING)
-{% endhighlight %}
- </td>
-
- <td>
- <p>Returns a string left-padded with the given pad string to a length of len characters. If the string is longer than len, the return value is shortened to len characters. E.g. "hi".lpad(4, '??') returns "??hi", "hi".lpad(1, '??') returns "h".</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-STRING.rpad(len INT, pad STRING)
-{% endhighlight %}
- </td>
-
- <td>
- <p>Returns a string right-padded with the given pad string to a length of len characters. If the string is longer than len, the return value is shortened to len characters. E.g. "hi".rpad(4, '??') returns "hi??", "hi".rpad(1, '??') returns "h".</p>
- </td>
- </tr>
- <tr>
- <td>
- {% highlight java %}
-STRING.fromBase64()
-{% endhighlight %}
- </td>
-
- <td>
- <p>Returns the base string decoded with base64, if string is null, returns null. E.g. "aGVsbG8gd29ybGQ=".fromBase64() returns "hello world".</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-STRING.toBase64()
-{% endhighlight %}
- </td>
-
- <td>
- <p>Returns the base64-encoded result of <i>STRING</i>; retruns NULL if <i>STRING</i> is NULL.</p>
- <p>E.g., <code>"hello world".toBase64()</code> returns "aGVsbG8gd29ybGQ=".</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-concat(string1, string2,...)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the string that results from concatenating the arguments. Returns NULL if any argument is NULL. E.g. <code>concat("AA", "BB", "CC")</code> returns <code>AABBCC</code>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight text %}
-concat_ws(separator, string1, string2,...)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the string that results from concatenating the arguments using a separator. The separator is added between the strings to be concatenated. Returns NULL If the separator is NULL. concat_ws() does not skip empty strings. However, it does skip any NULL argument. E.g. <code>concat_ws("~", "AA", "BB", "", "CC")</code> returns <code>AA~BB~~CC</code></p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Conditional functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight java %}
-BOOLEAN.?(value1, value2)
-{% endhighlight %}
- </td>
- <td>
- <p>Ternary conditional operator that decides which of two other expressions should be evaluated based on a evaluated boolean condition. E.g. <code>(42 > 5).?("A", "B")</code> leads to "A".</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Type conversion functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight java %}
-ANY.cast(TYPE)
-{% endhighlight %}
- </td>
- <td>
- <p>Converts a value to a given type. E.g. <code>"42".cast(INT)</code> leads to 42.</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Value constructor functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.rows
-{% endhighlight %}
- </td>
- <td>
- <p>Creates an interval of rows.</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Temporal functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight java %}
-STRING.toDate()
-{% endhighlight %}
- </td>
- <td>
- <p>Parses a date string in the form "yy-mm-dd" to a SQL date.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-STRING.toTime()
-{% endhighlight %}
- </td>
- <td>
- <p>Parses a time string in the form "hh:mm:ss" to a SQL time.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-STRING.toTimestamp()
-{% endhighlight %}
- </td>
- <td>
- <p>Parses a timestamp string in the form "yy-mm-dd hh:mm:ss.fff" to a SQL timestamp.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.year
-NUMERIC.years
-{% endhighlight %}
- </td>
- <td>
- <p>Creates an interval of months for a given number of years.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.month
-NUMERIC.months
-{% endhighlight %}
- </td>
- <td>
- <p>Creates an interval of months for a given number of months.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.day
-NUMERIC.days
-{% endhighlight %}
- </td>
- <td>
- <p>Creates an interval of milliseconds for a given number of days.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.hour
-NUMERIC.hours
-{% endhighlight %}
- </td>
- <td>
- <p>Creates an interval of milliseconds for a given number of hours.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.minute
-NUMERIC.minutes
-{% endhighlight %}
- </td>
- <td>
- <p>Creates an interval of milliseconds for a given number of minutes.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.second
-NUMERIC.seconds
-{% endhighlight %}
- </td>
- <td>
- <p>Creates an interval of milliseconds for a given number of seconds.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-NUMERIC.milli
-NUMERIC.millis
-{% endhighlight %}
- </td>
- <td>
- <p>Creates an interval of milliseconds.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-currentDate()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the current SQL date in UTC time zone.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-currentTime()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the current SQL time in UTC time zone.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-currentTimestamp()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the current SQL timestamp in UTC time zone.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-localTime()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the current SQL time in local time zone.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-localTimestamp()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the current SQL timestamp in local time zone.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-TEMPORAL.extract(TIMEINTERVALUNIT)
-{% endhighlight %}
- </td>
- <td>
- <p>Extracts parts of a time point or time interval. Returns the part as a long value. E.g. <code>'2006-06-05'.toDate.extract(DAY)</code> leads to 5 or <code>'2006-06-05'.toDate.extract(QUARTER)</code> leads to 2.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-TIMEPOINT.floor(TIMEINTERVALUNIT)
-{% endhighlight %}
- </td>
- <td>
- <p>Rounds a time point down to the given unit. E.g. <code>'12:44:31'.toDate.floor(MINUTE)</code> leads to 12:44:00.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-TIMEPOINT.ceil(TIMEINTERVALUNIT)
-{% endhighlight %}
- </td>
- <td>
- <p>Rounds a time point up to the given unit. E.g. <code>'12:44:31'.toTime.floor(MINUTE)</code> leads to 12:45:00.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-temporalOverlaps(TIMEPOINT, TEMPORAL, TIMEPOINT, TEMPORAL)
-{% endhighlight %}
- </td>
- <td>
- <p>Determines whether two anchored time intervals overlap. Time point and temporal are transformed into a range defined by two time points (start, end). The function evaluates <code>leftEnd >= rightStart && rightEnd >= leftStart</code>. E.g. <code>temporalOverlaps("2:55:00".toTime, 1.hour, "3:30:00".toTime, 2.hour)</code> leads to true.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-dateFormat(TIMESTAMP, STRING)
-{% endhighlight %}
- </td>
- <td>
- <p>Formats <code>timestamp</code> as a string using a specified <code>format</code>. The format must be compatible with MySQL's date formatting syntax as used by the <code>date_parse</code> function. The format specification is given in the <a href="sql.html#date-format-specifier">Date Format Specifier table</a> below.</p>
- <p>For example <code>dateFormat(ts, '%Y, %d %M')</code> results in strings formatted as <code>"2017, 05 May"</code>.</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Aggregate functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight java %}
-FIELD.count
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the number of input rows for which the field is not null.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-FIELD.avg
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the average (arithmetic mean) of the numeric field across all input values.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-FIELD.sum
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the sum of the numeric field across all input values. If all values are null, null is returned.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-FIELD.sum0
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the sum of the numeric field across all input values. If all values are null, 0 is returned.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-FIELD.max
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the maximum value of field across all input values.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-FIELD.min
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the minimum value of field across all input values.</p>
- </td>
- </tr>
-
-
- <tr>
- <td>
- {% highlight java %}
-FIELD.stddevPop
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the population standard deviation of the numeric field across all input values.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-FIELD.stddevSamp
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the sample standard deviation of the numeric field across all input values.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-FIELD.varPop
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the population variance (square of the population standard deviation) of the numeric field across all input values.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-FIELD.varSamp
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the sample variance (square of the sample standard deviation) of the numeric field across all input values.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-FIELD.collect
- {% endhighlight %}
- </td>
- <td>
- <p>Returns the multiset aggregate of the input value.</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Value access functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight java %}
-COMPOSITE.get(STRING)
-COMPOSITE.get(INT)
-{% endhighlight %}
- </td>
- <td>
- <p>Accesses the field of a Flink composite type (such as Tuple, POJO, etc.) by index or name and returns it's value. E.g. <code>pojo.get('myField')</code> or <code>tuple.get(0)</code>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-ANY.flatten()
-{% endhighlight %}
- </td>
- <td>
- <p>Converts a Flink composite type (such as Tuple, POJO, etc.) and all of its direct subtypes into a flat representation where every subtype is a separate field. In most cases the fields of the flat representation are named similarly to the original fields but with a dollar separator (e.g. <code>mypojo$mytuple$f0</code>).</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Array functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight java %}
-array(ANY [, ANY ]*)
-{% endhighlight %}
- </td>
- <td>
- <p>Creates an array from a list of values. The array will be an array of objects (not primitives).</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-ARRAY.cardinality()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the number of elements of an array.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-ARRAY.at(INT)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the element at a particular position in an array. The index starts at 1.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-ARRAY.element()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the sole element of an array with a single element. Returns <code>null</code> if the array is empty. Throws an exception if the array has more than one element.</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Map functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight java %}
-map(ANY, ANY [, ANY, ANY ]*)
-{% endhighlight %}
- </td>
- <td>
- <p>Creates a map from a list of key-value pairs.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-MAP.cardinality()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the number of entries of a map.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-MAP.at(ANY)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the value specified by a particular key in a map.</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Hash functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight java %}
-STRING.md5()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the MD5 hash of the string argument as a string of 32 hexadecimal digits; null if <i>string</i> is null.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-STRING.sha1()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the SHA-1 hash of the string argument as a string of 40 hexadecimal digits; null if <i>string</i> is null.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-STRING.sha224()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the SHA-224 hash of the string argument as a string of 56 hexadecimal digits; null if <i>string</i> is null.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-STRING.sha256()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the SHA-256 hash of the string argument as a string of 64 hexadecimal digits; null if <i>string</i> is null.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-STRING.sha384()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the SHA-384 hash of the string argument as a string of 96 hexadecimal digits; null if <i>string</i> is null.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-STRING.sha512()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the SHA-512 hash of the string argument as a string of 128 hexadecimal digits; null if <i>string</i> is null.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight java %}
-STRING.sha2(INT)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the hash using the SHA-2 family of hash functions (SHA-224, SHA-256, SHA-384, or SHA-512). The first argument <i>string</i> is the string to be hashed. <i>hashLength</i> is the bit length of the result (either 224, 256, 384, or 512). Returns <i>null</i> if <i>string</i> or <i>hashLength</i> is <i>null</i>.
- </p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Row functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight java %}
-row(ANY, [, ANY]*)
-{% endhighlight %}
- </td>
- <td>
- <p>Creates a row from a list of values. Row is composite type and can be access via <a href="tableApi.html#built-in-functions">value access functions</a>.</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Auxiliary functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight java %}
-ANY.as(name [, name ]* )
-{% endhighlight %}
- </td>
- <td>
- <p>Specifies a name for an expression i.e. a field. Additional names can be specified if the expression expands to multiple fields.</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-</div>
-<div data-lang="scala" markdown="1">
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Comparison functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight scala %}
-ANY === ANY
-{% endhighlight %}
- </td>
- <td>
- <p>Equals.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-ANY !== ANY
-{% endhighlight %}
- </td>
- <td>
- <p>Not equal.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-ANY > ANY
-{% endhighlight %}
- </td>
- <td>
- <p>Greater than.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-ANY >= ANY
-{% endhighlight %}
- </td>
- <td>
- <p>Greater than or equal.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-ANY < ANY
-{% endhighlight %}
- </td>
- <td>
- <p>Less than.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-ANY <= ANY
-{% endhighlight %}
- </td>
- <td>
- <p>Less than or equal.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-ANY.isNull
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true if the given expression is null.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-ANY.isNotNull
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true if the given expression is not null.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-STRING.like(STRING)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true, if a string matches the specified LIKE pattern. E.g. "Jo_n%" matches all strings that start with "Jo(arbitrary letter)n".</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-STRING.similar(STRING)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true, if a string matches the specified SQL regex pattern. E.g. "A+" matches all strings that consist of at least one "A".</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-ANY.in(ANY, ANY, ...)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true if an expression exists in a given list of expressions. This is a shorthand for multiple OR conditions. If the testing set contains null, the result will be null if the element can not be found and true if it can be found. If element is null, the result is always null. E.g. "42".in(1, 2, 3) leads to false.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-ANY.in(TABLE)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true if an expression exists in a given table sub-query. The sub-query table must consist of one column. This column must have the same data type as the expression. Note: This operation is not supported in a streaming environment yet.</p>
- <p><b>Note:</b> For streaming queries the operation is rewritten in a join and group operation. The required state to compute the query result might grow infinitely depending on the number of distinct input rows. Please provide a query configuration with valid retention interval to prevent excessive state size. See <a href="streaming.html">Streaming Concepts</a> for details.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-ANY.between(lowerBound, upperBound)
- {% endhighlight %}
- </td>
- <td>
- <p>Returns true if the given expression is between <i>lowerBound</i> and <i>upperBound</i> (both inclusive). False otherwise. The parameters must be numeric types or identical comparable types.
- </p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-ANY.notBetween(lowerBound, upperBound)
- {% endhighlight %}
- </td>
- <td>
- <p>Returns true if the given expression is not between <i>lowerBound</i> and <i>upperBound</i> (both inclusive). False otherwise. The parameters must be numeric types or identical comparable types.
- </p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Logical functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight scala %}
-boolean1 || boolean2
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true if <i>boolean1</i> is true or <i>boolean2</i> is true. Supports three-valued logic.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-boolean1 && boolean2
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true if <i>boolean1</i> and <i>boolean2</i> are both true. Supports three-valued logic.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-!BOOLEAN
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true if boolean expression is not true; returns null if boolean is null.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-BOOLEAN.isTrue
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true if the given boolean expression is true. False otherwise (for null and false).</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-BOOLEAN.isFalse
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true if given boolean expression is false. False otherwise (for null and true).</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-BOOLEAN.isNotTrue
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true if the given boolean expression is not true (for null and false). False otherwise.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-BOOLEAN.isNotFalse
-{% endhighlight %}
- </td>
- <td>
- <p>Returns true if given boolean expression is not false (for null and true). False otherwise.</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Arithmetic functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight scala %}
-+ numeric
-{% endhighlight %}
- </td>
- <td>
- <p>Returns <i>numeric</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-- numeric
-{% endhighlight %}
- </td>
- <td>
- <p>Returns negative <i>numeric</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-numeric1 + numeric2
-{% endhighlight %}
- </td>
- <td>
- <p>Returns <i>numeric1</i> plus <i>numeric2</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-numeric1 - numeric2
-{% endhighlight %}
- </td>
- <td>
- <p>Returns <i>numeric1</i> minus <i>numeric2</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-numeric1 * numeric2
-{% endhighlight %}
- </td>
- <td>
- <p>Returns <i>numeric1</i> multiplied by <i>numeric2</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-numeric1 / numeric2
-{% endhighlight %}
- </td>
- <td>
- <p>Returns <i>numeric1</i> divided by <i>numeric2</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-numeric1.power(numeric2)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns <i>numeric1</i> raised to the power of <i>numeric2</i>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.abs()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the absolute value of given value.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-numeric1 % numeric2
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the remainder (modulus) of <i>numeric1</i> divided by <i>numeric2</i>. The result is negative only if <i>numeric1</i> is negative.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.sqrt()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the square root of a given value.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.ln()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the natural logarithm of given value.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.log10()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the base 10 logarithm of given value.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-numeric1.log()
-numeric1.log(numeric2)
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the logarithm of a given numeric value.</p>
- <p>If called without a parameter, this function returns the natural logarithm of <code>numeric1</code>. If called with a parameter <code>numeric2</code>, this function returns the logarithm of <code>numeric1</code> to the base <code>numeric2</code>. <code>numeric1</code> must be greater than 0. <code>numeric2</code> must be greater than 1.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.exp()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the Euler's number raised to the given power.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.ceil()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the smallest integer greater than or equal to a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.floor()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the largest integer less than or equal to a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.sin()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the sine of a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.cos()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the cosine of a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.tan()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the cotangent of a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.cot()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the arc sine of a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-atan2(NUMERIC, NUMERIC)
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the arc tangent of a given coordinate.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.asin()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the arc cosine of a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.acos()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the arc tangent of a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.atan()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the tangent of a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.degrees()
-{% endhighlight %}
- </td>
- <td>
- <p>Converts <i>numeric</i> from radians to degrees.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.radians()
-{% endhighlight %}
- </td>
- <td>
- <p>Converts <i>numeric</i> from degrees to radians.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.sign()
-{% endhighlight %}
- </td>
- <td>
- <p>Calculates the signum of a given number.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.round(INT)
-{% endhighlight %}
- </td>
- <td>
- <p>Rounds the given number to <i>integer</i> places right to the decimal point.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-pi()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a value that is closer than any other value to pi.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-e()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a value that is closer than any other value to e.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-rand()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive).</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-rand(seed integer)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a pseudorandom double value between 0.0 (inclusive) and 1.0 (exclusive) with a initial seed. Two rand functions will return identical sequences of numbers if they have same initial seed.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-randInteger(bound integer)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a pseudorandom integer value between 0.0 (inclusive) and the specified value (exclusive).</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-randInteger(seed integer, bound integer)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a pseudorandom integer value between 0.0 (inclusive) and the specified value (exclusive) with a initial seed. Two randInteger functions will return identical sequences of numbers if they have same initial seed and same bound.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.bin()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns a string representation of an integer numeric value in binary format. Returns null if <i>numeric</i> is null. E.g. "4" leads to "100", "12" leads to "1100".</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Arithmetic functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight scala %}
-STRING + STRING
-{% endhighlight %}
- </td>
- <td>
- <p>Concatenates two character strings.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-STRING.charLength()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the length of a String.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-STRING.upperCase()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns all of the characters in a string in upper case using the rules of the default locale.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-STRING.lowerCase()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns all of the characters in a string in lower case using the rules of the default locale.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-STRING.position(STRING)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the position of string in an other string starting at 1. Returns 0 if string could not be found. E.g. <code>"a".position("bbbbba")</code> leads to 6.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-STRING.trim(
- leading = true,
- trailing = true,
- character = " ")
-{% endhighlight %}
- </td>
- <td>
- <p>Removes leading and/or trailing characters from the given string.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-STRING.overlay(STRING, INT)
-STRING.overlay(STRING, INT, INT)
-{% endhighlight %}
- </td>
- <td>
- <p>Replaces a substring of string with a string starting at a position (starting at 1). An optional length specifies how many characters should be removed. E.g. <code>"xxxxxtest".overlay("xxxx", 6)</code> leads to "xxxxxxxxx", <code>"xxxxxtest".overlay('xxxx', 6, 2)</code> leads to "xxxxxxxxxst".</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-STRING.substring(INT)
-{% endhighlight %}
- </td>
- <td>
- <p>Creates a substring of the given string beginning at the given index to the end. The start index starts at 1 and is inclusive.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-STRING.substring(INT, INT)
-{% endhighlight %}
- </td>
- <td>
- <p>Creates a substring of the given string at the given index for the given length. The index starts at 1 and is inclusive, i.e., the character at the index is included in the substring. The substring has the specified length or less.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-STRING.initCap()
-{% endhighlight %}
- </td>
-
- <td>
- <p>Converts the initial letter of each word in a string to uppercase. Assumes a string containing only [A-Za-z0-9], everything else is treated as whitespace.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-STRING.toBase64()
-{% endhighlight %}
- </td>
-
- <td>
- <p>Returns the base64-encoded result of <i>STRING</i>; returns NULL if <i>STRING</i> is NULL.</p>
- <p>E.g., <code>"hello world".toBase64()</code> returns "aGVsbG8gd29ybGQ=".</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Conditional functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight java %}
-BOOLEAN.?(value1, value2)
-{% endhighlight %}
- </td>
- <td>
- <p>Ternary conditional operator that decides which of two other expressions should be evaluated based on a evaluated boolean condition. E.g. <code>(42 > 5).?("A", "B")</code> leads to "A".</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Type conversion functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight scala %}
-ANY.cast(TYPE)
-{% endhighlight %}
- </td>
- <td>
- <p>Converts a value to a given type. E.g. <code>"42".cast(Types.INT)</code> leads to 42.</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Value constructor functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.rows
-{% endhighlight %}
- </td>
- <td>
- <p>Creates an interval of rows.</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Temporal functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight scala %}
-STRING.toDate
-{% endhighlight %}
- </td>
- <td>
- <p>Parses a date string in the form "yy-mm-dd" to a SQL date.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-STRING.toTime
-{% endhighlight %}
- </td>
- <td>
- <p>Parses a time string in the form "hh:mm:ss" to a SQL time.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-STRING.toTimestamp
-{% endhighlight %}
- </td>
- <td>
- <p>Parses a timestamp string in the form "yy-mm-dd hh:mm:ss.fff" to a SQL timestamp.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.year
-NUMERIC.years
-{% endhighlight %}
- </td>
- <td>
- <p>Creates an interval of months for a given number of years.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.month
-NUMERIC.months
-{% endhighlight %}
- </td>
- <td>
- <p>Creates an interval of months for a given number of months.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.day
-NUMERIC.days
-{% endhighlight %}
- </td>
- <td>
- <p>Creates an interval of milliseconds for a given number of days.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.hour
-NUMERIC.hours
-{% endhighlight %}
- </td>
- <td>
- <p>Creates an interval of milliseconds for a given number of hours.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.minute
-NUMERIC.minutes
-{% endhighlight %}
- </td>
- <td>
- <p>Creates an interval of milliseconds for a given number of minutes.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.second
-NUMERIC.seconds
-{% endhighlight %}
- </td>
- <td>
- <p>Creates an interval of milliseconds for a given number of seconds.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-NUMERIC.milli
-NUMERIC.millis
-{% endhighlight %}
- </td>
- <td>
- <p>Creates an interval of milliseconds.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-currentDate()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the current SQL date in UTC time zone.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-currentTime()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the current SQL time in UTC time zone.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-currentTimestamp()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the current SQL timestamp in UTC time zone.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-localTime()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the current SQL time in local time zone.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-localTimestamp()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the current SQL timestamp in local time zone.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-TEMPORAL.extract(TimeIntervalUnit)
-{% endhighlight %}
- </td>
- <td>
- <p>Extracts parts of a time point or time interval. Returns the part as a long value. E.g. <code>"2006-06-05".toDate.extract(TimeIntervalUnit.DAY)</code> leads to 5 or <code>'2006-06-05'.toDate.extract(QUARTER)</code> leads to 2.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-TIMEPOINT.floor(TimeIntervalUnit)
-{% endhighlight %}
- </td>
- <td>
- <p>Rounds a time point down to the given unit. E.g. <code>"12:44:31".toTime.floor(TimeIntervalUnit.MINUTE)</code> leads to 12:44:00.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-TIMEPOINT.ceil(TimeIntervalUnit)
-{% endhighlight %}
- </td>
- <td>
- <p>Rounds a time point up to the given unit. E.g. <code>"12:44:31".toTime.floor(TimeIntervalUnit.MINUTE)</code> leads to 12:45:00.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-temporalOverlaps(TIMEPOINT, TEMPORAL, TIMEPOINT, TEMPORAL)
-{% endhighlight %}
- </td>
- <td>
- <p>Determines whether two anchored time intervals overlap. Time point and temporal are transformed into a range defined by two time points (start, end). The function evaluates <code>leftEnd >= rightStart && rightEnd >= leftStart</code>. E.g. <code>temporalOverlaps('2:55:00'.toTime, 1.hour, '3:30:00'.toTime, 2.hours)</code> leads to true.</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Aggregate functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight scala %}
-FIELD.count
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the number of input rows for which the field is not null.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-FIELD.avg
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the average (arithmetic mean) of the numeric field across all input values.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-FIELD.sum
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the sum of the numeric field across all input values. If all values are null, null is returned.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-FIELD.sum0
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the sum of the numeric field across all input values. If all values are null, 0 is returned.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-FIELD.max
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the maximum value of field across all input values.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-FIELD.min
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the minimum value of field across all input values.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-FIELD.stddevPop
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the population standard deviation of the numeric field across all input values.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-FIELD.stddevSamp
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the sample standard deviation of the numeric field across all input values.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-FIELD.varPop
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the population variance (square of the population standard deviation) of the numeric field across all input values.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-FIELD.varSamp
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the sample variance (square of the sample standard deviation) of the numeric field across all input values.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-FIELD.collect
- {% endhighlight %}
- </td>
- <td>
- <p>Returns the multiset aggregate of the input value.</p>
- </td>
- </tr>
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Value access functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight scala %}
-COMPOSITE.get(STRING)
-COMPOSITE.get(INT)
-{% endhighlight %}
- </td>
- <td>
- <p>Accesses the field of a Flink composite type (such as Tuple, POJO, etc.) by index or name and returns it's value. E.g. <code>'pojo.get("myField")</code> or <code>'tuple.get(0)</code>.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-ANY.flatten()
-{% endhighlight %}
- </td>
- <td>
- <p>Converts a Flink composite type (such as Tuple, POJO, etc.) and all of its direct subtypes into a flat representation where every subtype is a separate field. In most cases the fields of the flat representation are named similarly to the original fields but with a dollar separator (e.g. <code>mypojo$mytuple$f0</code>).</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-dateFormat(TIMESTAMP, STRING)
-{% endhighlight %}
- </td>
- <td>
- <p>Formats <code>timestamp</code> as a string using a specified <code>format</code>. The format must be compatible with MySQL's date formatting syntax as used by the <code>date_parse</code> function. The format specification is given in the <a href="sql.html#date-format-specifier">Date Format Specifier table</a> below.</p>
- <p>For example <code>dateFormat('ts, "%Y, %d %M")</code> results in strings formatted as <code>"2017, 05 May"</code>.</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Array functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight scala %}
-array(ANY [, ANY ]*)
-{% endhighlight %}
- </td>
- <td>
- <p>Creates an array from a list of values. The array will be an array of objects (not primitives).</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-ARRAY.cardinality()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the number of elements of an array.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-ARRAY.at(INT)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the element at a particular position in an array. The index starts at 1.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-ARRAY.element()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the sole element of an array with a single element. Returns <code>null</code> if the array is empty. Throws an exception if the array has more than one element.</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Map functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight scala %}
-map(ANY, ANY [, ANY, ANY ]*)
-{% endhighlight %}
- </td>
- <td>
- <p>Creates a map from a list of key-value pairs.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-MAP.cardinality()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the number of entries of a map.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-MAP.at(ANY)
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the value specified by a particular key in a map.</p>
- </td>
- </tr>
-
- </tbody>
-</table>
-
-
-<table class="table table-bordered">
- <thead>
- <tr>
- <th class="text-left" style="width: 40%">Hash functions</th>
- <th class="text-center">Description</th>
- </tr>
- </thead>
-
- <tbody>
-
- <tr>
- <td>
- {% highlight scala %}
-STRING.md5()
-{% endhighlight %}
- </td>
- <td>
- <p>Returns the MD5 hash of the string argument as a string of 32 hexadecimal digits; null if <i>string</i> is null.</p>
- </td>
- </tr>
-
- <tr>
- <td>
- {% highlight scala %}
-STRING.sha1()
-{% endhighlight %}
- </td>
- <td>
... 315 lines suppressed ...