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 <twal...@apache.org> Authored: Fri Sep 2 11:00:09 2016 +0200 Committer: twalthr <twal...@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]] + } +}
