You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@flink.apache.org by tw...@apache.org on 2016/09/23 15:36:23 UTC
flink git commit: [FLINK-4549] [table] Test and document implicitly
supported SQL functions
Repository: flink
Updated Branches:
refs/heads/master ecbccd940 -> 9a1bc021a
[FLINK-4549] [table] Test and document implicitly supported SQL functions
This closes #2500.
Project: http://git-wip-us.apache.org/repos/asf/flink/repo
Commit: http://git-wip-us.apache.org/repos/asf/flink/commit/9a1bc021
Tree: http://git-wip-us.apache.org/repos/asf/flink/tree/9a1bc021
Diff: http://git-wip-us.apache.org/repos/asf/flink/diff/9a1bc021
Branch: refs/heads/master
Commit: 9a1bc021aed0a3eec8c6eabb843d15b8c2b0b43f
Parents: ecbccd9
Author: twalthr <tw...@apache.org>
Authored: Fri Sep 2 11:00:09 2016 +0200
Committer: twalthr <tw...@apache.org>
Committed: Fri Sep 23 17:25:06 2016 +0200
----------------------------------------------------------------------
docs/dev/table_api.md | 873 +++++++++++++++++--
.../flink/api/table/codegen/CodeGenerator.scala | 26 +-
.../scala/expression/TemporalTypesTest.scala | 389 ---------
.../table/expressions/SqlExpressionTest.scala | 161 ++++
.../table/expressions/TemporalTypesTest.scala | 389 +++++++++
5 files changed, 1360 insertions(+), 478 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/flink/blob/9a1bc021/docs/dev/table_api.md
----------------------------------------------------------------------
diff --git a/docs/dev/table_api.md b/docs/dev/table_api.md
index 72b88a6..bb083ff 100644
--- a/docs/dev/table_api.md
+++ b/docs/dev/table_api.md
@@ -1212,10 +1212,10 @@ Advanced types such as generic types, composite types (e.g. POJOs or Tuples), an
{% top %}
-Scalar Functions
+Built-in Functions
----------------
-Both the Table API and SQL come with a set of built-in scalar functions for data transformations. This section gives a brief overview of the available scalar function so far.
+Both the Table API and SQL come with a set of built-in functions for data transformations. This section gives a brief overview of the available functions so far.
<div class="codetabs" markdown="1">
<div data-lang="java" markdown="1">
@@ -2057,12 +2057,554 @@ temporalOverlaps(TIMEPOINT, TEMPORAL, TIMEPOINT, TEMPORAL)
</div>
<div data-lang="SQL" markdown="1">
+
+
+<!--
+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).
+
<br />
<table class="table table-bordered">
<thead>
<tr>
- <th class="text-left" style="width: 40%">Function</th>
+ <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>Whether <i>value</i> is null.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+value IS NOT NULL
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Whether <i>value</i> is not null.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+value1 IS DISTINCT FROM value2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Whether 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>Whether 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>Whether <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>Whether <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
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Whether <i>string1</i> matches pattern <i>string2</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+string1 NOT LIKE string2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Whether <i>string1</i> does not match pattern <i>string2</i>.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+string1 SIMILAR TO string2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Whether <i>string1</i> matches regular expression <i>string2</i>.</p>
+ </td>
+ </tr>
+
+
+ <tr>
+ <td>
+ {% highlight text %}
+string1 NOT SIMILAR TO string2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Whether <i>string1</i> does not match regular expression <i>string2</i>.</p>
+ </td>
+ </tr>
+
+
+ <tr>
+ <td>
+ {% highlight text %}
+value IN (value [, value]* )
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Whether <i>value</i> is equal to a value in a list.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+value NOT IN (value [, value]* )
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Whether <i>value</i> is not equal to every value in a list.</p>
+ </td>
+ </tr>
+<!-- NOT SUPPORTED SO FAR
+ <tr>
+ <td>
+ {% highlight text %}
+value IN (sub-query)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Whether <i>value</i> is equal to a row returned by sub-query.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+value NOT IN (sub-query)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Whether <i>value</i> is not equal to every row returned by sub-query.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+EXISTS (sub-query)
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Whether sub-query returns at least one row.</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>Whether <i>boolean1</i> is TRUE or <i>boolean2</i> is TRUE.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+boolean1 AND boolean2
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Whether <i>boolean1</i> and <i>boolean2</i> are both TRUE.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+NOT boolean
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Whether <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>Whether <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>Whether <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>Whether <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>Whether <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>Whether <i>boolean</i> is UNKNOWN.</p>
+ </td>
+ </tr>
+
+ <tr>
+ <td>
+ {% highlight text %}
+boolean IS NOT UNKNOWN
+{% endhighlight %}
+ </td>
+ <td>
+ <p>Whether <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 %}
+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)
+{% 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>
+
+ </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>
@@ -2070,256 +2612,303 @@ temporalOverlaps(TIMEPOINT, TEMPORAL, TIMEPOINT, TEMPORAL)
<tbody>
<tr>
<td>
- {% highlight sql %}
-EXP(NUMERIC)
+ {% highlight text %}
+string || string
{% endhighlight %}
</td>
<td>
- <p>Calculates the Euler's number raised to the given power.</p>
+ <p>Concatenates two character strings.</p>
</td>
</tr>
<tr>
<td>
- {% highlight sql %}
-LOG10(NUMERIC)
+ {% highlight text %}
+CHAR_LENGTH(string)
{% endhighlight %}
</td>
<td>
- <p>Calculates the base 10 logarithm of given value.</p>
+ <p>Returns the number of characters in a character string.</p>
</td>
</tr>
-
<tr>
<td>
- {% highlight sql %}
-LN(NUMERIC)
+ {% highlight text %}
+CHARACTER_LENGTH(string)
{% endhighlight %}
</td>
<td>
- <p>Calculates the natural logarithm of given value.</p>
+ <p>As CHAR_LENGTH(<i>string</i>).</p>
</td>
</tr>
<tr>
<td>
- {% highlight sql %}
-POWER(NUMERIC, NUMERIC)
+ {% highlight text %}
+UPPER(string)
{% endhighlight %}
</td>
<td>
- <p>Calculates the given number raised to the power of the other value.</p>
+ <p>Returns a character string converted to upper case.</p>
</td>
</tr>
<tr>
<td>
- {% highlight sql %}
-ABS(NUMERIC)
+ {% highlight text %}
+LOWER(string)
{% endhighlight %}
</td>
<td>
- <p>Calculates the absolute value of given value.</p>
+ <p>Returns a character string converted to lower case.</p>
</td>
</tr>
<tr>
<td>
- {% highlight sql %}
-FLOOR(NUMERIC)
+ {% highlight text %}
+POSITION(string1 IN string2)
{% endhighlight %}
</td>
<td>
- <p>Calculates the largest integer less than or equal to a given number.</p>
+ <p>Returns the position of the first occurrence of <i>string1</i> in <i>string2</i>.</p>
</td>
</tr>
<tr>
<td>
- {% highlight sql %}
-CEIL(NUMERIC)
+ {% highlight text %}
+TRIM( { BOTH | LEADING | TRAILING } string1 FROM string2)
{% endhighlight %}
</td>
<td>
- <p>Calculates the smallest integer greater than or equal to a given number.</p>
+ <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 sql %}
-SUBSTRING(VARCHAR, INT, INT)
-SUBSTRING(VARCHAR FROM INT FOR INT)
+ {% highlight text %}
+OVERLAY(string1 PLACING string2 FROM integer [ FOR integer2 ])
{% 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>
+ <p>Replaces a substring of <i>string1</i> with <i>string2</i>.</p>
</td>
</tr>
<tr>
<td>
- {% highlight sql %}
-SUBSTRING(VARCHAR, INT)
-SUBSTRING(VARCHAR FROM INT)
+ {% highlight text %}
+SUBSTRING(string FROM integer)
{% 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>
+ <p>Returns a substring of a character string starting at a given point.</p>
</td>
</tr>
<tr>
<td>
- {% highlight sql %}
-TRIM(LEADING VARCHAR FROM VARCHAR)
-TRIM(TRAILING VARCHAR FROM VARCHAR)
-TRIM(BOTH VARCHAR FROM VARCHAR)
-TRIM(VARCHAR)
+ {% highlight text %}
+SUBSTRING(string FROM integer FOR integer)
{% endhighlight %}
</td>
<td>
- <p>Removes leading and/or trailing characters from the given string. By default, whitespaces at both sides are removed.</p>
+ <p>Returns a substring of a character string starting at a given point with a given length.</p>
</td>
</tr>
<tr>
<td>
- {% highlight sql %}
-CHAR_LENGTH(VARCHAR)
+ {% highlight text %}
+INITCAP(string)
{% endhighlight %}
</td>
<td>
- <p>Returns the length of a String.</p>
+ <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>
+ </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 sql %}
-UPPER(VARCHAR)
+ {% highlight text %}
+CASE value
+WHEN value1 [, value11 ]* THEN result1
+[ WHEN valueN [, valueN1 ]* THEN resultN ]*
+[ ELSE resultZ ]
+END
{% endhighlight %}
</td>
<td>
- <p>Returns all of the characters in a string in upper case using the rules of the default locale.</p>
+ <p>Simple case.</p>
</td>
</tr>
<tr>
<td>
- {% highlight sql %}
-LOWER(VARCHAR)
+ {% highlight text %}
+CASE
+WHEN condition1 THEN result1
+[ WHEN conditionN THEN resultN ]*
+[ ELSE resultZ ]
+END
{% endhighlight %}
</td>
<td>
- <p>Returns all of the characters in a string in lower case using the rules of the default locale.</p>
+ <p>Searched case.</p>
</td>
</tr>
<tr>
<td>
- {% highlight sql %}
-INITCAP(VARCHAR)
+ {% highlight text %}
+NULLIF(value, value)
{% 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>
+ <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 sql %}
-VARCHAR LIKE VARCHAR
+ {% highlight text %}
+COALESCE(value, value [, value ]* )
{% 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>
+ <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 sql %}
-VARCHAR SIMILAR TO VARCHAR
+ {% highlight text %}
+CAST(value AS type)
{% 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>
+ <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%">Value constructor functions</th>
+ <th class="text-center">Description</th>
+ </tr>
+ </thead>
+ <tbody>
<tr>
<td>
- {% highlight sql %}
-DATE VARCHAR
+ {% highlight text %}
+ROW (value [, value]* )
{% endhighlight %}
</td>
<td>
- <p>Parses a date string in the form "yy-mm-dd" to a SQL date.</p>
+ <p>Creates a row from a list of values.</p>
</td>
</tr>
<tr>
<td>
- {% highlight sql %}
-TIME VARCHAR
+ {% highlight text %}
+(value [, value]* )
{% endhighlight %}
</td>
<td>
- <p>Parses a time string in the form "hh:mm:ss" to a SQL time.</p>
+ <p>Creates a row from a list of values.</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 sql %}
-TIMESTAMP VARCHAR
+ {% highlight text %}
+DATE string
{% endhighlight %}
</td>
<td>
- <p>Parses a timestamp string in the form "yy-mm-dd hh:mm:ss.fff" to a SQL timestamp.</p>
+ <p>Parses a date string in the form "yy-mm-dd" to a SQL date.</p>
</td>
</tr>
<tr>
<td>
- {% highlight sql %}
-EXTRACT(TIMEINTERVALUNIT FROM TEMPORAL)
+ {% highlight text %}
+TIME string
{% 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>
+ <p>Parses a time <i>string</i> in the form "hh:mm:ss" to a SQL time.</p>
</td>
</tr>
<tr>
<td>
- {% highlight sql %}
-FLOOR(TIMEPOINT TO TIMEINTERVALUNIT)
+ {% highlight text %}
+TIMESTAMP string
{% 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>
+ <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 sql %}
-CEIL(TIMEPOINT TO TIMEINTERVALUNIT)
+ {% highlight text %}
+INTERVAL string range
{% 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>
+ <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 '2-10' YEAR TO MONTH</code> return intervals.</p>
</td>
</tr>
<tr>
<td>
- {% highlight sql %}
+ {% highlight text %}
CURRENT_DATE
{% endhighlight %}
</td>
@@ -2330,7 +2919,7 @@ CURRENT_DATE
<tr>
<td>
- {% highlight sql %}
+ {% highlight text %}
CURRENT_TIME
{% endhighlight %}
</td>
@@ -2341,7 +2930,7 @@ CURRENT_TIME
<tr>
<td>
- {% highlight sql %}
+ {% highlight text %}
CURRENT_TIMESTAMP
{% endhighlight %}
</td>
@@ -2352,7 +2941,7 @@ CURRENT_TIMESTAMP
<tr>
<td>
- {% highlight sql %}
+ {% highlight text %}
LOCALTIME
{% endhighlight %}
</td>
@@ -2363,7 +2952,7 @@ LOCALTIME
<tr>
<td>
- {% highlight sql %}
+ {% highlight text %}
LOCALTIMESTAMP
{% endhighlight %}
</td>
@@ -2372,8 +2961,118 @@ LOCALTIMESTAMP
</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 %}
+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>
+ </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.</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.</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>
</tbody>
</table>
+
</div>
</div>
@@ -2464,6 +3163,18 @@ object TimestampModifier extends ScalarFunction {
</div>
</div>
+### Limitations
+
+The following operations are not supported yet:
+
+- Binary string operators and functions
+- System functions
+- Collection functions
+- Aggregate functions like STDDEV_xxx, VAR_xxx, and REGR_xxx
+- Distinct aggregate functions like COUNT DISTINCT
+- Window functions
+- Grouping functions
+
{% top %}
http://git-wip-us.apache.org/repos/asf/flink/blob/9a1bc021/flink-libraries/flink-table/src/main/scala/org/apache/flink/api/table/codegen/CodeGenerator.scala
----------------------------------------------------------------------
diff --git a/flink-libraries/flink-table/src/main/scala/org/apache/flink/api/table/codegen/CodeGenerator.scala b/flink-libraries/flink-table/src/main/scala/org/apache/flink/api/table/codegen/CodeGenerator.scala
index 39ee26c..e5a07b1 100644
--- a/flink-libraries/flink-table/src/main/scala/org/apache/flink/api/table/codegen/CodeGenerator.scala
+++ b/flink-libraries/flink-table/src/main/scala/org/apache/flink/api/table/codegen/CodeGenerator.scala
@@ -545,7 +545,9 @@ class CodeGenerator(
generateInputAccess(input._1, input._2, index)
}
- override def visitFieldAccess(rexFieldAccess: RexFieldAccess): GeneratedExpression = ???
+ override def visitFieldAccess(rexFieldAccess: RexFieldAccess): GeneratedExpression =
+ throw new CodeGenException("Accesses to fields are not supported yet.")
+
override def visitLiteral(literal: RexLiteral): GeneratedExpression = {
val resultType = FlinkTypeFactory.toTypeInfo(literal.getType)
@@ -657,13 +659,17 @@ class CodeGenerator(
}
}
- override def visitCorrelVariable(correlVariable: RexCorrelVariable): GeneratedExpression = ???
+ override def visitCorrelVariable(correlVariable: RexCorrelVariable): GeneratedExpression =
+ throw new CodeGenException("Correlating variables are not supported yet.")
- override def visitLocalRef(localRef: RexLocalRef): GeneratedExpression = ???
+ override def visitLocalRef(localRef: RexLocalRef): GeneratedExpression =
+ throw new CodeGenException("Local variables are not supported yet.")
- override def visitRangeRef(rangeRef: RexRangeRef): GeneratedExpression = ???
+ override def visitRangeRef(rangeRef: RexRangeRef): GeneratedExpression =
+ throw new CodeGenException("Range references are not supported yet.")
- override def visitDynamicParam(dynamicParam: RexDynamicParam): GeneratedExpression = ???
+ override def visitDynamicParam(dynamicParam: RexDynamicParam): GeneratedExpression =
+ throw new CodeGenException("Dynamic parameter references are not supported yet.")
override def visitCall(call: RexCall): GeneratedExpression = {
val operands = call.getOperands.map(_.accept(this))
@@ -853,7 +859,9 @@ class CodeGenerator(
operands.map(_.resultType),
resultType)
callGen
- .getOrElse(throw new CodeGenException(s"Unsupported call: $sqlOperator"))
+ .getOrElse(throw new CodeGenException(s"Unsupported call: $sqlOperator \n" +
+ s"If you think this function should be supported, " +
+ s"you can create an issue and start a discussion for it."))
.generate(this, operands)
// unknown or invalid
@@ -862,9 +870,11 @@ class CodeGenerator(
}
}
- override def visitOver(over: RexOver): GeneratedExpression = ???
+ override def visitOver(over: RexOver): GeneratedExpression =
+ throw new CodeGenException("Aggregate functions over windows are not supported yet.")
- override def visitSubQuery(subQuery: RexSubQuery): GeneratedExpression = ???
+ override def visitSubQuery(subQuery: RexSubQuery): GeneratedExpression =
+ throw new CodeGenException("Subqueries are not supported yet.")
// ----------------------------------------------------------------------------------------------
// generator helping methods
http://git-wip-us.apache.org/repos/asf/flink/blob/9a1bc021/flink-libraries/flink-table/src/test/scala/org/apache/flink/api/scala/expression/TemporalTypesTest.scala
----------------------------------------------------------------------
diff --git a/flink-libraries/flink-table/src/test/scala/org/apache/flink/api/scala/expression/TemporalTypesTest.scala b/flink-libraries/flink-table/src/test/scala/org/apache/flink/api/scala/expression/TemporalTypesTest.scala
deleted file mode 100644
index 63d6346..0000000
--- a/flink-libraries/flink-table/src/test/scala/org/apache/flink/api/scala/expression/TemporalTypesTest.scala
+++ /dev/null
@@ -1,389 +0,0 @@
-/*
- * 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.
- */
-
-package org.apache.flink.api.scala.expression
-
-import java.sql.{Date, Time, Timestamp}
-
-import org.apache.flink.api.common.typeinfo.TypeInformation
-import org.apache.flink.api.scala.table._
-import org.apache.flink.api.table.expressions.utils.ExpressionTestBase
-import org.apache.flink.api.table.typeutils.RowTypeInfo
-import org.apache.flink.api.table.{Row, Types}
-import org.junit.Test
-
-class TemporalTypesTest extends ExpressionTestBase {
-
- @Test
- def testTimePointLiterals(): Unit = {
- testAllApis(
- "1990-10-14".toDate,
- "'1990-10-14'.toDate",
- "DATE '1990-10-14'",
- "1990-10-14")
-
- testTableApi(
- Date.valueOf("2040-09-11"),
- "'2040-09-11'.toDate",
- "2040-09-11")
-
- testAllApis(
- "1500-04-30".cast(Types.DATE),
- "'1500-04-30'.cast(DATE)",
- "CAST('1500-04-30' AS DATE)",
- "1500-04-30")
-
- testAllApis(
- "15:45:59".toTime,
- "'15:45:59'.toTime",
- "TIME '15:45:59'",
- "15:45:59")
-
- testTableApi(
- Time.valueOf("00:00:00"),
- "'00:00:00'.toTime",
- "00:00:00")
-
- testAllApis(
- "1:30:00".cast(Types.TIME),
- "'1:30:00'.cast(TIME)",
- "CAST('1:30:00' AS TIME)",
- "01:30:00")
-
- testAllApis(
- "1990-10-14 23:00:00.123".toTimestamp,
- "'1990-10-14 23:00:00.123'.toTimestamp",
- "TIMESTAMP '1990-10-14 23:00:00.123'",
- "1990-10-14 23:00:00.123")
-
- testTableApi(
- Timestamp.valueOf("2040-09-11 00:00:00.000"),
- "'2040-09-11 00:00:00.000'.toTimestamp",
- "2040-09-11 00:00:00.0")
-
- testAllApis(
- "1500-04-30 12:00:00".cast(Types.TIMESTAMP),
- "'1500-04-30 12:00:00'.cast(TIMESTAMP)",
- "CAST('1500-04-30 12:00:00' AS TIMESTAMP)",
- "1500-04-30 12:00:00.0")
- }
-
- @Test
- def testTimeIntervalLiterals(): Unit = {
- testAllApis(
- 1.year,
- "1.year",
- "INTERVAL '1' YEAR",
- "+1-00")
-
- testAllApis(
- 1.month,
- "1.month",
- "INTERVAL '1' MONTH",
- "+0-01")
-
- testAllApis(
- 12.day,
- "12.day",
- "INTERVAL '12' DAY",
- "+12 00:00:00.000")
-
- testAllApis(
- 1.hour,
- "1.hour",
- "INTERVAL '1' HOUR",
- "+0 01:00:00.000")
-
- testAllApis(
- 3.minute,
- "3.minute",
- "INTERVAL '3' MINUTE",
- "+0 00:03:00.000")
-
- testAllApis(
- 3.second,
- "3.second",
- "INTERVAL '3' SECOND",
- "+0 00:00:03.000")
-
- testAllApis(
- 3.milli,
- "3.milli",
- "INTERVAL '0.003' SECOND",
- "+0 00:00:00.003")
- }
-
- @Test
- def testTimePointInput(): Unit = {
- testAllApis(
- 'f0,
- "f0",
- "f0",
- "1990-10-14")
-
- testAllApis(
- 'f1,
- "f1",
- "f1",
- "10:20:45")
-
- testAllApis(
- 'f2,
- "f2",
- "f2",
- "1990-10-14 10:20:45.123")
- }
-
- @Test
- def testTimeIntervalInput(): Unit = {
- testAllApis(
- 'f9,
- "f9",
- "f9",
- "+2-00")
-
- testAllApis(
- 'f10,
- "f10",
- "f10",
- "+0 00:00:12.000")
- }
-
- @Test
- def testTimePointCasting(): Unit = {
- testAllApis(
- 'f0.cast(Types.TIMESTAMP),
- "f0.cast(TIMESTAMP)",
- "CAST(f0 AS TIMESTAMP)",
- "1990-10-14 00:00:00.0")
-
- testAllApis(
- 'f1.cast(Types.TIMESTAMP),
- "f1.cast(TIMESTAMP)",
- "CAST(f1 AS TIMESTAMP)",
- "1970-01-01 10:20:45.0")
-
- testAllApis(
- 'f2.cast(Types.DATE),
- "f2.cast(DATE)",
- "CAST(f2 AS DATE)",
- "1990-10-14")
-
- testAllApis(
- 'f2.cast(Types.TIME),
- "f2.cast(TIME)",
- "CAST(f2 AS TIME)",
- "10:20:45")
-
- testAllApis(
- 'f2.cast(Types.TIME),
- "f2.cast(TIME)",
- "CAST(f2 AS TIME)",
- "10:20:45")
-
- testTableApi(
- 'f7.cast(Types.DATE),
- "f7.cast(DATE)",
- "2002-11-09")
-
- testTableApi(
- 'f7.cast(Types.DATE).cast(Types.INT),
- "f7.cast(DATE).cast(INT)",
- "12000")
-
- testTableApi(
- 'f7.cast(Types.TIME),
- "f7.cast(TIME)",
- "00:00:12")
-
- testTableApi(
- 'f7.cast(Types.TIME).cast(Types.INT),
- "f7.cast(TIME).cast(INT)",
- "12000")
-
- testTableApi(
- 'f8.cast(Types.TIMESTAMP),
- "f8.cast(TIMESTAMP)",
- "2016-06-27 07:23:33.0")
-
- testTableApi(
- 'f8.cast(Types.TIMESTAMP).cast(Types.LONG),
- "f8.cast(TIMESTAMP).cast(LONG)",
- "1467012213000")
- }
-
- @Test
- def testTimeIntervalCasting(): Unit = {
- testTableApi(
- 'f7.cast(Types.INTERVAL_MONTHS),
- "f7.cast(INTERVAL_MONTHS)",
- "+1000-00")
-
- testTableApi(
- 'f8.cast(Types.INTERVAL_MILLIS),
- "f8.cast(INTERVAL_MILLIS)",
- "+16979 07:23:33.000")
- }
-
- @Test
- def testTimePointComparison(): Unit = {
- testAllApis(
- 'f0 < 'f3,
- "f0 < f3",
- "f0 < f3",
- "false")
-
- testAllApis(
- 'f0 < 'f4,
- "f0 < f4",
- "f0 < f4",
- "true")
-
- testAllApis(
- 'f1 < 'f5,
- "f1 < f5",
- "f1 < f5",
- "false")
-
- testAllApis(
- 'f0.cast(Types.TIMESTAMP) !== 'f2,
- "f0.cast(TIMESTAMP) !== f2",
- "CAST(f0 AS TIMESTAMP) <> f2",
- "true")
-
- testAllApis(
- 'f0.cast(Types.TIMESTAMP) === 'f6,
- "f0.cast(TIMESTAMP) === f6",
- "CAST(f0 AS TIMESTAMP) = f6",
- "true")
- }
-
- @Test
- def testTimeIntervalArithmetic(): Unit = {
- testAllApis(
- 12.month < 24.month,
- "12.month < 24.month",
- "INTERVAL '12' MONTH < INTERVAL '24' MONTH",
- "true")
-
- testAllApis(
- 8.milli > 10.milli,
- "8.milli > 10.milli",
- "INTERVAL '0.008' SECOND > INTERVAL '0.010' SECOND",
- "false")
-
- testAllApis(
- 8.year === 8.year,
- "8.year === 8.year",
- "INTERVAL '8' YEAR = INTERVAL '8' YEAR",
- "true")
-
- testAllApis(
- 8.year + 10.month,
- "8.year + 10.month",
- "INTERVAL '8' YEAR + INTERVAL '10' MONTH",
- "+8-10")
-
- testAllApis(
- 8.hour + 10.minute + 12.second + 5.milli,
- "8.hour + 10.minute + 12.second + 5.milli",
- "INTERVAL '8' HOUR + INTERVAL '10' MINUTE + INTERVAL '12.005' SECOND",
- "+0 08:10:12.005")
-
- testAllApis(
- 1.minute - 10.second,
- "1.minute - 10.second",
- "INTERVAL '1' MINUTE - INTERVAL '10' SECOND",
- "+0 00:00:50.000")
-
- testAllApis(
- 2.year - 12.month,
- "2.year - 12.month",
- "INTERVAL '2' YEAR - INTERVAL '12' MONTH",
- "+1-00")
-
- testAllApis(
- -'f9.cast(Types.INTERVAL_MONTHS),
- "-f9.cast(INTERVAL_MONTHS)",
- "-CAST(f9 AS INTERVAL YEAR)",
- "-2-00")
-
- testAllApis(
- 'f0 + 2.day,
- "f0 + 2.day",
- "f0 + INTERVAL '2' DAY",
- "1990-10-16")
-
- testAllApis(
- 30.day + 'f0,
- "30.day + f0",
- "INTERVAL '30' DAY + f0",
- "1990-11-13")
-
- testAllApis(
- 'f1 + 12.hour,
- "f1 + 12.hour",
- "f1 + INTERVAL '12' HOUR",
- "22:20:45")
-
- testAllApis(
- 24.hour + 'f1,
- "24.hour + f1",
- "INTERVAL '24' HOUR + f1",
- "10:20:45")
-
- testAllApis(
- 'f2 + 10.day + 4.milli,
- "f2 + 10.day + 4.milli",
- "f2 + INTERVAL '10 00:00:00.004' DAY TO SECOND",
- "1990-10-24 10:20:45.127")
- }
-
- // ----------------------------------------------------------------------------------------------
-
- def testData = {
- val testData = new Row(11)
- testData.setField(0, Date.valueOf("1990-10-14"))
- testData.setField(1, Time.valueOf("10:20:45"))
- testData.setField(2, Timestamp.valueOf("1990-10-14 10:20:45.123"))
- testData.setField(3, Date.valueOf("1990-10-13"))
- testData.setField(4, Date.valueOf("1990-10-15"))
- testData.setField(5, Time.valueOf("00:00:00"))
- testData.setField(6, Timestamp.valueOf("1990-10-14 00:00:00.0"))
- testData.setField(7, 12000)
- testData.setField(8, 1467012213000L)
- testData.setField(9, 24)
- testData.setField(10, 12000L)
- testData
- }
-
- def typeInfo = {
- new RowTypeInfo(Seq(
- Types.DATE,
- Types.TIME,
- Types.TIMESTAMP,
- Types.DATE,
- Types.DATE,
- Types.TIME,
- Types.TIMESTAMP,
- Types.INT,
- Types.LONG,
- Types.INTERVAL_MONTHS,
- Types.INTERVAL_MILLIS)).asInstanceOf[TypeInformation[Any]]
- }
-}
http://git-wip-us.apache.org/repos/asf/flink/blob/9a1bc021/flink-libraries/flink-table/src/test/scala/org/apache/flink/api/table/expressions/SqlExpressionTest.scala
----------------------------------------------------------------------
diff --git a/flink-libraries/flink-table/src/test/scala/org/apache/flink/api/table/expressions/SqlExpressionTest.scala b/flink-libraries/flink-table/src/test/scala/org/apache/flink/api/table/expressions/SqlExpressionTest.scala
new file mode 100644
index 0000000..cae4388
--- /dev/null
+++ b/flink-libraries/flink-table/src/test/scala/org/apache/flink/api/table/expressions/SqlExpressionTest.scala
@@ -0,0 +1,161 @@
+/*
+ * 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.
+ */
+
+package org.apache.flink.api.table.expressions
+
+import org.apache.flink.api.common.typeinfo.TypeInformation
+import org.apache.flink.api.table.Row
+import org.apache.flink.api.table.expressions.utils.ExpressionTestBase
+import org.apache.flink.api.table.typeutils.RowTypeInfo
+import org.junit.Test
+
+/**
+ * Tests all SQL expressions that are currently supported according to the documentation.
+ * This tests should be kept in sync with the documentation to reduce confusion due to the
+ * large amount of SQL functions.
+ *
+ * The tests do not test every parameter combination of a function.
+ * They are rather a function existence test and simple functional test.
+ *
+ * The tests are split up and ordered like the sections in the documentation.
+ */
+class SqlExpressionTest extends ExpressionTestBase {
+
+ @Test
+ def testComparisonFunctions(): Unit = {
+ testSqlApi("1 = 1", "true")
+ testSqlApi("1 <> 1", "false")
+ testSqlApi("5 > 2", "true")
+ testSqlApi("2 >= 2", "true")
+ testSqlApi("5 < 2", "false")
+ testSqlApi("2 <= 2", "true")
+ testSqlApi("1 IS NULL", "false")
+ testSqlApi("1 IS NOT NULL", "true")
+ testSqlApi("NULLIF(1,1) IS DISTINCT FROM NULLIF(1,1)", "false")
+ testSqlApi("NULLIF(1,1) IS NOT DISTINCT FROM NULLIF(1,1)", "true")
+ testSqlApi("NULLIF(1,1) IS NOT DISTINCT FROM NULLIF(1,1)", "true")
+ testSqlApi("12 BETWEEN 11 AND 13", "true")
+ testSqlApi("12 BETWEEN ASYMMETRIC 13 AND 11", "false")
+ testSqlApi("12 BETWEEN SYMMETRIC 13 AND 11", "true")
+ testSqlApi("12 NOT BETWEEN 11 AND 13", "false")
+ testSqlApi("12 NOT BETWEEN ASYMMETRIC 13 AND 11", "true")
+ testSqlApi("12 NOT BETWEEN SYMMETRIC 13 AND 11", "false")
+ testSqlApi("'TEST' LIKE '%EST'", "true")
+ //testSqlApi("'%EST' LIKE '.%EST' ESCAPE '.'", "true") // TODO
+ testSqlApi("'TEST' NOT LIKE '%EST'", "false")
+ //testSqlApi("'%EST' NOT LIKE '.%EST' ESCAPE '.'", "false") // TODO
+ testSqlApi("'TEST' SIMILAR TO '.EST'", "true")
+ //testSqlApi("'TEST' SIMILAR TO ':.EST' ESCAPE ':'", "true") // TODO
+ testSqlApi("'TEST' NOT SIMILAR TO '.EST'", "false")
+ //testSqlApi("'TEST' NOT SIMILAR TO ':.EST' ESCAPE ':'", "false") // TODO
+ testSqlApi("'TEST' IN ('west', 'TEST', 'rest')", "true")
+ testSqlApi("'TEST' IN ('west', 'rest')", "false")
+ testSqlApi("'TEST' NOT IN ('west', 'TEST', 'rest')", "false")
+ testSqlApi("'TEST' NOT IN ('west', 'rest')", "true")
+
+ // sub-query functions are not listed here
+ }
+
+ @Test
+ def testLogicalFunctions(): Unit = {
+ testSqlApi("TRUE OR FALSE", "true")
+ testSqlApi("TRUE AND FALSE", "false")
+ testSqlApi("NOT TRUE", "false")
+ testSqlApi("TRUE IS FALSE", "false")
+ testSqlApi("TRUE IS NOT FALSE", "true")
+ testSqlApi("TRUE IS TRUE", "true")
+ testSqlApi("TRUE IS NOT TRUE", "false")
+ testSqlApi("NULLIF(TRUE,TRUE) IS UNKNOWN", "true")
+ testSqlApi("NULLIF(TRUE,TRUE) IS NOT UNKNOWN", "false")
+ }
+
+ @Test
+ def testArithmeticFunctions(): Unit = {
+ testSqlApi("+5", "5")
+ testSqlApi("-5", "-5")
+ testSqlApi("5+5", "10")
+ testSqlApi("5-5", "0")
+ testSqlApi("5*5", "25")
+ testSqlApi("5/5", "1")
+ testSqlApi("POWER(5, 5)", "3125.0")
+ testSqlApi("ABS(-5)", "5")
+ testSqlApi("MOD(-26, 5)", "-1")
+ testSqlApi("SQRT(4)", "2.0")
+ testSqlApi("LN(1)", "0.0")
+ testSqlApi("LOG10(1)", "0.0")
+ testSqlApi("EXP(0)", "1.0")
+ testSqlApi("CEIL(2.5)", "3")
+ testSqlApi("FLOOR(2.5)", "2")
+ }
+
+ @Test
+ def testStringFunctions(): Unit = {
+ testSqlApi("'test' || 'string'", "teststring")
+ testSqlApi("CHAR_LENGTH('string')", "6")
+ testSqlApi("CHARACTER_LENGTH('string')", "6")
+ testSqlApi("UPPER('string')", "STRING")
+ testSqlApi("LOWER('STRING')", "string")
+ testSqlApi("POSITION('STR' IN 'STRING')", "1")
+ testSqlApi("TRIM(BOTH ' STRING ')", "STRING")
+ testSqlApi("TRIM(LEADING 'x' FROM 'xxxxSTRINGxxxx')", "STRINGxxxx")
+ testSqlApi("TRIM(TRAILING 'x' FROM 'xxxxSTRINGxxxx')", "xxxxSTRING")
+ testSqlApi(
+ "OVERLAY('This is a old string' PLACING 'new' FROM 11 FOR 3)",
+ "This is a new string")
+ testSqlApi("SUBSTRING('hello world', 2)", "ello world")
+ testSqlApi("SUBSTRING('hello world', 2, 3)", "ell")
+ testSqlApi("INITCAP('hello world')", "Hello World")
+ }
+
+ @Test
+ def testConditionalFunctions(): Unit = {
+ testSqlApi("CASE 2 WHEN 1, 2 THEN 2 ELSE 3 END", "2")
+ testSqlApi("CASE WHEN 1 = 2 THEN 2 WHEN 1 = 1 THEN 3 ELSE 3 END", "3")
+ testSqlApi("NULLIF(1, 1)", "null")
+ testSqlApi("COALESCE(NULL, 5)", "5")
+ }
+
+ @Test
+ def testTypeConversionFunctions(): Unit = {
+ testSqlApi("CAST(2 AS DOUBLE)", "2.0")
+ }
+
+ @Test
+ def testValueConstructorFunctions(): Unit = {
+ testSqlApi("ROW('hello world', 12)", "hello world") // test base only returns field 0
+ testSqlApi("('hello world', 12)", "hello world") // test base only returns field 0
+ }
+
+ @Test
+ def testDateTimeFunctions(): Unit = {
+ testSqlApi("DATE '1990-10-14'", "1990-10-14")
+ testSqlApi("TIME '12:12:12'", "12:12:12")
+ testSqlApi("TIMESTAMP '1990-10-14 12:12:12.123'", "1990-10-14 12:12:12.123")
+ testSqlApi("INTERVAL '10 00:00:00.004' DAY TO SECOND", "+10 00:00:00.004")
+ testSqlApi("INTERVAL '10 00:12' DAY TO MINUTE", "+10 00:12:00.000")
+ testSqlApi("INTERVAL '2-10' YEAR TO MONTH", "+2-10")
+ testSqlApi("EXTRACT(DAY FROM DATE '1990-12-01')", "1")
+ testSqlApi("EXTRACT(DAY FROM INTERVAL '19 12:10:10.123' DAY TO SECOND(3))", "19")
+ testSqlApi("QUARTER(DATE '2016-04-12')", "2")
+ }
+
+ override def testData: Any = new Row(0)
+
+ override def typeInfo: TypeInformation[Any] =
+ new RowTypeInfo(Seq()).asInstanceOf[TypeInformation[Any]]
+}
http://git-wip-us.apache.org/repos/asf/flink/blob/9a1bc021/flink-libraries/flink-table/src/test/scala/org/apache/flink/api/table/expressions/TemporalTypesTest.scala
----------------------------------------------------------------------
diff --git a/flink-libraries/flink-table/src/test/scala/org/apache/flink/api/table/expressions/TemporalTypesTest.scala b/flink-libraries/flink-table/src/test/scala/org/apache/flink/api/table/expressions/TemporalTypesTest.scala
new file mode 100644
index 0000000..24ff51c
--- /dev/null
+++ b/flink-libraries/flink-table/src/test/scala/org/apache/flink/api/table/expressions/TemporalTypesTest.scala
@@ -0,0 +1,389 @@
+/*
+ * 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.
+ */
+
+package org.apache.flink.api.table.expressions
+
+import java.sql.{Date, Time, Timestamp}
+
+import org.apache.flink.api.common.typeinfo.TypeInformation
+import org.apache.flink.api.scala.table._
+import org.apache.flink.api.table.expressions.utils.ExpressionTestBase
+import org.apache.flink.api.table.typeutils.RowTypeInfo
+import org.apache.flink.api.table.{Row, Types}
+import org.junit.Test
+
+class TemporalTypesTest extends ExpressionTestBase {
+
+ @Test
+ def testTimePointLiterals(): Unit = {
+ testAllApis(
+ "1990-10-14".toDate,
+ "'1990-10-14'.toDate",
+ "DATE '1990-10-14'",
+ "1990-10-14")
+
+ testTableApi(
+ Date.valueOf("2040-09-11"),
+ "'2040-09-11'.toDate",
+ "2040-09-11")
+
+ testAllApis(
+ "1500-04-30".cast(Types.DATE),
+ "'1500-04-30'.cast(DATE)",
+ "CAST('1500-04-30' AS DATE)",
+ "1500-04-30")
+
+ testAllApis(
+ "15:45:59".toTime,
+ "'15:45:59'.toTime",
+ "TIME '15:45:59'",
+ "15:45:59")
+
+ testTableApi(
+ Time.valueOf("00:00:00"),
+ "'00:00:00'.toTime",
+ "00:00:00")
+
+ testAllApis(
+ "1:30:00".cast(Types.TIME),
+ "'1:30:00'.cast(TIME)",
+ "CAST('1:30:00' AS TIME)",
+ "01:30:00")
+
+ testAllApis(
+ "1990-10-14 23:00:00.123".toTimestamp,
+ "'1990-10-14 23:00:00.123'.toTimestamp",
+ "TIMESTAMP '1990-10-14 23:00:00.123'",
+ "1990-10-14 23:00:00.123")
+
+ testTableApi(
+ Timestamp.valueOf("2040-09-11 00:00:00.000"),
+ "'2040-09-11 00:00:00.000'.toTimestamp",
+ "2040-09-11 00:00:00.0")
+
+ testAllApis(
+ "1500-04-30 12:00:00".cast(Types.TIMESTAMP),
+ "'1500-04-30 12:00:00'.cast(TIMESTAMP)",
+ "CAST('1500-04-30 12:00:00' AS TIMESTAMP)",
+ "1500-04-30 12:00:00.0")
+ }
+
+ @Test
+ def testTimeIntervalLiterals(): Unit = {
+ testAllApis(
+ 1.year,
+ "1.year",
+ "INTERVAL '1' YEAR",
+ "+1-00")
+
+ testAllApis(
+ 1.month,
+ "1.month",
+ "INTERVAL '1' MONTH",
+ "+0-01")
+
+ testAllApis(
+ 12.day,
+ "12.day",
+ "INTERVAL '12' DAY",
+ "+12 00:00:00.000")
+
+ testAllApis(
+ 1.hour,
+ "1.hour",
+ "INTERVAL '1' HOUR",
+ "+0 01:00:00.000")
+
+ testAllApis(
+ 3.minute,
+ "3.minute",
+ "INTERVAL '3' MINUTE",
+ "+0 00:03:00.000")
+
+ testAllApis(
+ 3.second,
+ "3.second",
+ "INTERVAL '3' SECOND",
+ "+0 00:00:03.000")
+
+ testAllApis(
+ 3.milli,
+ "3.milli",
+ "INTERVAL '0.003' SECOND",
+ "+0 00:00:00.003")
+ }
+
+ @Test
+ def testTimePointInput(): Unit = {
+ testAllApis(
+ 'f0,
+ "f0",
+ "f0",
+ "1990-10-14")
+
+ testAllApis(
+ 'f1,
+ "f1",
+ "f1",
+ "10:20:45")
+
+ testAllApis(
+ 'f2,
+ "f2",
+ "f2",
+ "1990-10-14 10:20:45.123")
+ }
+
+ @Test
+ def testTimeIntervalInput(): Unit = {
+ testAllApis(
+ 'f9,
+ "f9",
+ "f9",
+ "+2-00")
+
+ testAllApis(
+ 'f10,
+ "f10",
+ "f10",
+ "+0 00:00:12.000")
+ }
+
+ @Test
+ def testTimePointCasting(): Unit = {
+ testAllApis(
+ 'f0.cast(Types.TIMESTAMP),
+ "f0.cast(TIMESTAMP)",
+ "CAST(f0 AS TIMESTAMP)",
+ "1990-10-14 00:00:00.0")
+
+ testAllApis(
+ 'f1.cast(Types.TIMESTAMP),
+ "f1.cast(TIMESTAMP)",
+ "CAST(f1 AS TIMESTAMP)",
+ "1970-01-01 10:20:45.0")
+
+ testAllApis(
+ 'f2.cast(Types.DATE),
+ "f2.cast(DATE)",
+ "CAST(f2 AS DATE)",
+ "1990-10-14")
+
+ testAllApis(
+ 'f2.cast(Types.TIME),
+ "f2.cast(TIME)",
+ "CAST(f2 AS TIME)",
+ "10:20:45")
+
+ testAllApis(
+ 'f2.cast(Types.TIME),
+ "f2.cast(TIME)",
+ "CAST(f2 AS TIME)",
+ "10:20:45")
+
+ testTableApi(
+ 'f7.cast(Types.DATE),
+ "f7.cast(DATE)",
+ "2002-11-09")
+
+ testTableApi(
+ 'f7.cast(Types.DATE).cast(Types.INT),
+ "f7.cast(DATE).cast(INT)",
+ "12000")
+
+ testTableApi(
+ 'f7.cast(Types.TIME),
+ "f7.cast(TIME)",
+ "00:00:12")
+
+ testTableApi(
+ 'f7.cast(Types.TIME).cast(Types.INT),
+ "f7.cast(TIME).cast(INT)",
+ "12000")
+
+ testTableApi(
+ 'f8.cast(Types.TIMESTAMP),
+ "f8.cast(TIMESTAMP)",
+ "2016-06-27 07:23:33.0")
+
+ testTableApi(
+ 'f8.cast(Types.TIMESTAMP).cast(Types.LONG),
+ "f8.cast(TIMESTAMP).cast(LONG)",
+ "1467012213000")
+ }
+
+ @Test
+ def testTimeIntervalCasting(): Unit = {
+ testTableApi(
+ 'f7.cast(Types.INTERVAL_MONTHS),
+ "f7.cast(INTERVAL_MONTHS)",
+ "+1000-00")
+
+ testTableApi(
+ 'f8.cast(Types.INTERVAL_MILLIS),
+ "f8.cast(INTERVAL_MILLIS)",
+ "+16979 07:23:33.000")
+ }
+
+ @Test
+ def testTimePointComparison(): Unit = {
+ testAllApis(
+ 'f0 < 'f3,
+ "f0 < f3",
+ "f0 < f3",
+ "false")
+
+ testAllApis(
+ 'f0 < 'f4,
+ "f0 < f4",
+ "f0 < f4",
+ "true")
+
+ testAllApis(
+ 'f1 < 'f5,
+ "f1 < f5",
+ "f1 < f5",
+ "false")
+
+ testAllApis(
+ 'f0.cast(Types.TIMESTAMP) !== 'f2,
+ "f0.cast(TIMESTAMP) !== f2",
+ "CAST(f0 AS TIMESTAMP) <> f2",
+ "true")
+
+ testAllApis(
+ 'f0.cast(Types.TIMESTAMP) === 'f6,
+ "f0.cast(TIMESTAMP) === f6",
+ "CAST(f0 AS TIMESTAMP) = f6",
+ "true")
+ }
+
+ @Test
+ def testTimeIntervalArithmetic(): Unit = {
+ testAllApis(
+ 12.month < 24.month,
+ "12.month < 24.month",
+ "INTERVAL '12' MONTH < INTERVAL '24' MONTH",
+ "true")
+
+ testAllApis(
+ 8.milli > 10.milli,
+ "8.milli > 10.milli",
+ "INTERVAL '0.008' SECOND > INTERVAL '0.010' SECOND",
+ "false")
+
+ testAllApis(
+ 8.year === 8.year,
+ "8.year === 8.year",
+ "INTERVAL '8' YEAR = INTERVAL '8' YEAR",
+ "true")
+
+ testAllApis(
+ 8.year + 10.month,
+ "8.year + 10.month",
+ "INTERVAL '8' YEAR + INTERVAL '10' MONTH",
+ "+8-10")
+
+ testAllApis(
+ 8.hour + 10.minute + 12.second + 5.milli,
+ "8.hour + 10.minute + 12.second + 5.milli",
+ "INTERVAL '8' HOUR + INTERVAL '10' MINUTE + INTERVAL '12.005' SECOND",
+ "+0 08:10:12.005")
+
+ testAllApis(
+ 1.minute - 10.second,
+ "1.minute - 10.second",
+ "INTERVAL '1' MINUTE - INTERVAL '10' SECOND",
+ "+0 00:00:50.000")
+
+ testAllApis(
+ 2.year - 12.month,
+ "2.year - 12.month",
+ "INTERVAL '2' YEAR - INTERVAL '12' MONTH",
+ "+1-00")
+
+ testAllApis(
+ -'f9.cast(Types.INTERVAL_MONTHS),
+ "-f9.cast(INTERVAL_MONTHS)",
+ "-CAST(f9 AS INTERVAL YEAR)",
+ "-2-00")
+
+ testAllApis(
+ 'f0 + 2.day,
+ "f0 + 2.day",
+ "f0 + INTERVAL '2' DAY",
+ "1990-10-16")
+
+ testAllApis(
+ 30.day + 'f0,
+ "30.day + f0",
+ "INTERVAL '30' DAY + f0",
+ "1990-11-13")
+
+ testAllApis(
+ 'f1 + 12.hour,
+ "f1 + 12.hour",
+ "f1 + INTERVAL '12' HOUR",
+ "22:20:45")
+
+ testAllApis(
+ 24.hour + 'f1,
+ "24.hour + f1",
+ "INTERVAL '24' HOUR + f1",
+ "10:20:45")
+
+ testAllApis(
+ 'f2 + 10.day + 4.milli,
+ "f2 + 10.day + 4.milli",
+ "f2 + INTERVAL '10 00:00:00.004' DAY TO SECOND",
+ "1990-10-24 10:20:45.127")
+ }
+
+ // ----------------------------------------------------------------------------------------------
+
+ def testData = {
+ val testData = new Row(11)
+ testData.setField(0, Date.valueOf("1990-10-14"))
+ testData.setField(1, Time.valueOf("10:20:45"))
+ testData.setField(2, Timestamp.valueOf("1990-10-14 10:20:45.123"))
+ testData.setField(3, Date.valueOf("1990-10-13"))
+ testData.setField(4, Date.valueOf("1990-10-15"))
+ testData.setField(5, Time.valueOf("00:00:00"))
+ testData.setField(6, Timestamp.valueOf("1990-10-14 00:00:00.0"))
+ testData.setField(7, 12000)
+ testData.setField(8, 1467012213000L)
+ testData.setField(9, 24)
+ testData.setField(10, 12000L)
+ testData
+ }
+
+ def typeInfo = {
+ new RowTypeInfo(Seq(
+ Types.DATE,
+ Types.TIME,
+ Types.TIMESTAMP,
+ Types.DATE,
+ Types.DATE,
+ Types.TIME,
+ Types.TIMESTAMP,
+ Types.INT,
+ Types.LONG,
+ Types.INTERVAL_MONTHS,
+ Types.INTERVAL_MILLIS)).asInstanceOf[TypeInformation[Any]]
+ }
+}