flink-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From twal...@apache.org
Subject flink git commit: [FLINK-4549] [table] Test and document implicitly supported SQL functions
Date Fri, 23 Sep 2016 15:36:23 GMT
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 <twalthr@apache.org>
Authored: Fri Sep 2 11:00:09 2016 +0200
Committer: twalthr <twalthr@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]]
+  }
+}


Mime
View raw message