Repository: flink Updated Branches: refs/heads/master 7a339a65f -> a062f4b8e
[FLINK-5447] [table] Sync documentation of built-in functions for Table API with SQL This closes #3126. Project: http://git-wip-us.apache.org/repos/asf/flink/repo Commit: http://git-wip-us.apache.org/repos/asf/flink/commit/a062f4b8 Tree: http://git-wip-us.apache.org/repos/asf/flink/tree/a062f4b8 Diff: http://git-wip-us.apache.org/repos/asf/flink/diff/a062f4b8 Branch: refs/heads/master Commit: a062f4b8e6bfd46ad7220b09479ac726d12002a1 Parents: 7a339a6 Author: twalthr <twal...@apache.org> Authored: Fri Jan 13 15:22:25 2017 +0100 Committer: twalthr <twal...@apache.org> Committed: Tue Jan 17 11:13:14 2017 +0100 ---------------------------------------------------------------------- docs/dev/table_api.md | 1286 +++++++++++++++--- .../flink/table/api/scala/expressionDsl.scala | 94 ++ .../table/expressions/ExpressionParser.scala | 4 +- .../table/expressions/ScalarOperatorsTest.scala | 1 + .../table/expressions/SqlExpressionTest.scala | 5 + 5 files changed, 1172 insertions(+), 218 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/flink/blob/a062f4b8/docs/dev/table_api.md ---------------------------------------------------------------------- diff --git a/docs/dev/table_api.md b/docs/dev/table_api.md index acabfcf..78f9a43 100644 --- a/docs/dev/table_api.md +++ b/docs/dev/table_api.md @@ -1512,522 +1512,601 @@ Both the Table API and SQL come with a set of built-in functions for data transf <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 java %} -ANY.as(name [, name ]* ) +ANY === ANY {% endhighlight %} </td> <td> - <p>Specifies a name for an expression i.e. a field. Additional names can be specified if the expression expands to multiple fields.</p> + <p>Equals.</p> </td> </tr> <tr> <td> {% highlight java %} -ANY.isNull +ANY !== ANY {% endhighlight %} </td> <td> - <p>Returns true if the given expression is null.</p> + <p>Not equal.</p> </td> </tr> <tr> <td> {% highlight java %} -ANY.isNotNull +ANY > ANY {% endhighlight %} </td> <td> - <p>Returns true if the given expression is not null.</p> + <p>Greater than.</p> </td> </tr> <tr> <td> {% highlight java %} -BOOLEAN.isTrue +ANY >= ANY {% endhighlight %} </td> <td> - <p>Returns true if the given boolean expression is true. False otherwise (for null and false).</p> + <p>Greater than or equal.</p> </td> </tr> <tr> <td> {% highlight java %} -BOOLEAN.isFalse +ANY < ANY {% endhighlight %} </td> <td> - <p>Returns true if given boolean expression is false. False otherwise (for null and true).</p> + <p>Less than.</p> </td> </tr> <tr> <td> {% highlight java %} -BOOLEAN.isNotTrue +ANY <= ANY {% endhighlight %} </td> <td> - <p>Returns true if the given boolean expression is not true (for null and false). False otherwise.</p> + <p>Less than or equal.</p> </td> </tr> <tr> <td> {% highlight java %} -BOOLEAN.isNotFalse +ANY.isNull {% endhighlight %} </td> <td> - <p>Returns true if given boolean expression is not false (for null and true). False otherwise.</p> + <p>Returns true if the given expression is null.</p> </td> </tr> <tr> <td> {% highlight java %} -NUMERIC.exp() +ANY.isNotNull {% endhighlight %} </td> <td> - <p>Calculates the Euler's number raised to the given power.</p> + <p>Returns true if the given expression is not null.</p> </td> </tr> <tr> <td> {% highlight java %} -NUMERIC.log10() +STRING.like(STRING) {% endhighlight %} </td> <td> - <p>Calculates the base 10 logarithm of given value.</p> + <p>Returns true, if a string matches the specified LIKE pattern. E.g. "Jo_n%" matches all strings that start with "Jo(arbitrary letter)n".</p> </td> </tr> - <tr> <td> {% highlight java %} -NUMERIC.ln() +STRING.similar(STRING) {% endhighlight %} </td> <td> - <p>Calculates the natural logarithm of given value.</p> + <p>Returns true, if a string matches the specified SQL regex pattern. E.g. "A+" matches all strings that consist of at least one "A".</p> </td> </tr> + </tbody> +</table> + +<table class="table table-bordered"> + <thead> + <tr> + <th class="text-left" style="width: 40%">Logical functions</th> + <th class="text-center">Description</th> + </tr> + </thead> + + <tbody> + <tr> <td> {% highlight java %} -NUMERIC.power(NUMERIC) +boolean1 || boolean2 {% endhighlight %} </td> <td> - <p>Calculates the given number raised to the power of the other value.</p> + <p>Returns true if <i>boolean1</i> is true or <i>boolean2</i> is true. Supports three-valued logic.</p> </td> </tr> <tr> <td> {% highlight java %} -NUMERIC.sqrt() +boolean1 && boolean2 {% endhighlight %} </td> <td> - <p>Calculates the square root of a given value.</p> + <p>Returns true if <i>boolean1</i> and <i>boolean2</i> are both true. Supports three-valued logic.</p> </td> </tr> <tr> <td> {% highlight java %} -NUMERIC.abs() +!BOOLEAN {% endhighlight %} </td> <td> - <p>Calculates the absolute value of given value.</p> + <p>Returns true if boolean expression is not true; returns null if boolean is null.</p> </td> </tr> <tr> <td> {% highlight java %} -NUMERIC.floor() +BOOLEAN.isTrue {% endhighlight %} </td> <td> - <p>Calculates the largest integer less than or equal to a given number.</p> + <p>Returns true if the given boolean expression is true. False otherwise (for null and false).</p> </td> </tr> <tr> <td> {% highlight java %} -NUMERIC.ceil() +BOOLEAN.isFalse {% endhighlight %} </td> <td> - <p>Calculates the smallest integer greater than or equal to a given number.</p> + <p>Returns true if given boolean expression is false. False otherwise (for null and true).</p> </td> </tr> <tr> <td> {% highlight java %} -STRING.substring(INT, INT) +BOOLEAN.isNotTrue {% 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>Returns true if the given boolean expression is not true (for null and false). False otherwise.</p> </td> </tr> <tr> <td> {% highlight java %} -STRING.substring(INT) +BOOLEAN.isNotFalse {% 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 true if given boolean expression is not false (for null and true). False otherwise.</p> </td> </tr> + </tbody> +</table> + + +<table class="table table-bordered"> + <thead> <tr> + <th class="text-left" style="width: 40%">Arithmetic functions</th> + <th class="text-center">Description</th> + </tr> + </thead> + + <tbody> + + <tr> <td> {% highlight java %} -STRING.trim(LEADING, STRING) -STRING.trim(TRAILING, STRING) -STRING.trim(BOTH, STRING) -STRING.trim(BOTH) -STRING.trim() ++ numeric {% 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 <i>numeric</i>.</p> </td> </tr> <tr> <td> {% highlight java %} -STRING.charLength() +- numeric {% endhighlight %} </td> <td> - <p>Returns the length of a String.</p> + <p>Returns negative <i>numeric</i>.</p> </td> </tr> - + <tr> <td> {% highlight java %} -STRING.upperCase() +numeric1 + numeric2 {% endhighlight %} </td> <td> - <p>Returns all of the characters in a string in upper case using the rules of the default locale.</p> + <p>Returns <i>numeric1</i> plus <i>numeric2</i>.</p> </td> </tr> <tr> <td> {% highlight java %} -STRING.lowerCase() +numeric1 - numeric2 {% endhighlight %} </td> <td> - <p>Returns all of the characters in a string in lower case using the rules of the default locale.</p> + <p>Returns <i>numeric1</i> minus <i>numeric2</i>.</p> </td> </tr> <tr> <td> {% highlight java %} -STRING.initCap() +numeric1 * numeric2 {% 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 <i>numeric1</i> multiplied by <i>numeric2</i>.</p> </td> </tr> <tr> <td> {% highlight java %} -STRING.like(STRING) +numeric1 / numeric2 {% 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>Returns <i>numeric1</i> divided by <i>numeric2</i>.</p> </td> </tr> <tr> <td> {% highlight java %} -STRING.similar(STRING) +numeric1.power(numeric2) {% 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>Returns <i>numeric1</i> raised to the power of <i>numeric2</i>.</p> </td> </tr> <tr> <td> {% highlight java %} -STRING.position(STRING) +NUMERIC.abs() {% endhighlight %} </td> <td> - <p>Returns the position of string in an other string starting at 1. Returns 0 if string could not be found. E.g. <code>'a'.position('bbbbba')</code> leads to 6.</p> + <p>Calculates the absolute value of given value.</p> </td> </tr> <tr> <td> {% highlight java %} -STRING.overlay(STRING, INT) -STRING.overlay(STRING, INT, INT) +numeric1 % numeric2 {% endhighlight %} </td> <td> - <p>Replaces a substring of string with a string starting at a position (starting at 1). An optional length specifies how many characters should be removed. E.g. <code>'xxxxxtest'.overlay('xxxx', 6)</code> leads to "xxxxxxxxx", <code>'xxxxxtest'.overlay('xxxx', 6, 2)</code> leads to "xxxxxxxxxst".</p> + <p>Returns the remainder (modulus) of <i>numeric1</i> divided by <i>numeric2</i>. The result is negative only if <i>numeric1</i> is negative.</p> </td> </tr> <tr> <td> {% highlight java %} -STRING.toDate() +NUMERIC.sqrt() {% endhighlight %} </td> <td> - <p>Parses a date string in the form "yy-mm-dd" to a SQL date.</p> + <p>Calculates the square root of a given value.</p> </td> </tr> <tr> <td> {% highlight java %} -STRING.toTime() +NUMERIC.ln() {% endhighlight %} </td> <td> - <p>Parses a time string in the form "hh:mm:ss" to a SQL time.</p> + <p>Calculates the natural logarithm of given value.</p> </td> </tr> <tr> <td> {% highlight java %} -STRING.toTimestamp() +NUMERIC.log10() {% endhighlight %} </td> <td> - <p>Parses a timestamp string in the form "yy-mm-dd hh:mm:ss.fff" to a SQL timestamp.</p> + <p>Calculates the base 10 logarithm of given value.</p> </td> </tr> <tr> <td> {% highlight java %} -NUMERIC.year +NUMERIC.exp() {% endhighlight %} </td> <td> - <p>Creates an interval of months for a given number of years.</p> + <p>Calculates the Euler's number raised to the given power.</p> </td> </tr> <tr> <td> {% highlight java %} -NUMERIC.month +NUMERIC.ceil() {% endhighlight %} </td> <td> - <p>Creates an interval of months for a given number of months.</p> + <p>Calculates the smallest integer greater than or equal to a given number.</p> </td> </tr> <tr> <td> {% highlight java %} -NUMERIC.day +NUMERIC.floor() {% endhighlight %} </td> <td> - <p>Creates an interval of milliseconds for a given number of days.</p> + <p>Calculates the largest integer less than or equal to a given number.</p> </td> </tr> + </tbody> +</table> + +<table class="table table-bordered"> + <thead> + <tr> + <th class="text-left" style="width: 40%">String functions</th> + <th class="text-center">Description</th> + </tr> + </thead> + + <tbody> + <tr> <td> {% highlight java %} -NUMERIC.hour +STRING + STRING {% endhighlight %} </td> <td> - <p>Creates an interval of milliseconds for a given number of hours.</p> + <p>Concatenates two character strings.</p> </td> </tr> <tr> <td> {% highlight java %} -NUMERIC.minute +STRING.charLength() {% endhighlight %} </td> <td> - <p>Creates an interval of milliseconds for a given number of minutes.</p> + <p>Returns the length of a String.</p> </td> </tr> <tr> <td> {% highlight java %} -NUMERIC.second +STRING.upperCase() {% endhighlight %} </td> <td> - <p>Creates an interval of milliseconds for a given number of seconds.</p> + <p>Returns all of the characters in a string in upper case using the rules of the default locale.</p> </td> </tr> <tr> <td> {% highlight java %} -NUMERIC.milli +STRING.lowerCase() {% endhighlight %} </td> <td> - <p>Creates an interval of milliseconds.</p> + <p>Returns all of the characters in a string in lower case using the rules of the default locale.</p> </td> </tr> <tr> <td> {% highlight java %} -TEMPORAL.extract(TIMEINTERVALUNIT) +STRING.position(STRING) {% endhighlight %} </td> <td> - <p>Extracts parts of a time point or time interval. Returns the part as a long value. E.g. <code>'2006-06-05'.toDate.extract(DAY)</code> leads to 5.</p> + <p>Returns the position of string in an other string starting at 1. Returns 0 if string could not be found. E.g. <code>'a'.position('bbbbba')</code> leads to 6.</p> </td> </tr> <tr> <td> {% highlight java %} -DATE.quarter() +STRING.trim(LEADING, STRING) +STRING.trim(TRAILING, STRING) +STRING.trim(BOTH, STRING) +STRING.trim(BOTH) +STRING.trim() {% endhighlight %} </td> <td> - <p>Returns the quarter of a year from a SQL date. E.g. <code>'1994-09-27'.toDate.quarter()</code> leads to 3.</p> + <p>Removes leading and/or trailing characters from the given string. By default, whitespaces at both sides are removed.</p> </td> </tr> <tr> <td> {% highlight java %} -TIMEPOINT.floor(TIMEINTERVALUNIT) +STRING.overlay(STRING, INT) +STRING.overlay(STRING, INT, INT) {% endhighlight %} </td> <td> - <p>Rounds a time point down to the given unit. E.g. <code>'12:44:31'.toDate.floor(MINUTE)</code> leads to 12:44:00.</p> + <p>Replaces a substring of string with a string starting at a position (starting at 1). An optional length specifies how many characters should be removed. E.g. <code>'xxxxxtest'.overlay('xxxx', 6)</code> leads to "xxxxxxxxx", <code>'xxxxxtest'.overlay('xxxx', 6, 2)</code> leads to "xxxxxxxxxst".</p> </td> </tr> <tr> <td> {% highlight java %} -TIMEPOINT.ceil(TIMEINTERVALUNIT) +STRING.substring(INT) {% endhighlight %} </td> <td> - <p>Rounds a time point up to the given unit. E.g. <code>'12:44:31'.toTime.floor(MINUTE)</code> leads to 12:45:00.</p> + <p>Creates a substring of the given string beginning at the given index to the end. The start index starts at 1 and is inclusive.</p> </td> </tr> <tr> <td> {% highlight java %} -currentDate() +STRING.substring(INT, INT) {% endhighlight %} </td> <td> - <p>Returns the current SQL date in UTC time zone.</p> + <p>Creates a substring of the given string at the given index for the given length. The index starts at 1 and is inclusive, i.e., the character at the index is included in the substring. The substring has the specified length or less.</p> </td> </tr> <tr> <td> {% highlight java %} -currentTime() +STRING.initCap() {% endhighlight %} </td> + <td> - <p>Returns the current SQL time in UTC time zone.</p> + <p>Converts the initial letter of each word in a string to uppercase. Assumes a string containing only [A-Za-z0-9], everything else is treated as whitespace.</p> </td> </tr> + </tbody> +</table> + +<table class="table table-bordered"> + <thead> + <tr> + <th class="text-left" style="width: 40%">Conditional functions</th> + <th class="text-center">Description</th> + </tr> + </thead> + + <tbody> + <tr> <td> {% highlight java %} -currentTimestamp() +BOOLEAN.?(value1, value2) {% endhighlight %} </td> <td> - <p>Returns the current SQL timestamp in UTC time zone.</p> + <p>Ternary conditional operator that decides which of two other expressions should be evaluated based on a evaluated boolean condition. E.g. <code>(42 > 5).?("A", "B")</code> leads to "A".</p> </td> </tr> + </tbody> +</table> + +<table class="table table-bordered"> + <thead> + <tr> + <th class="text-left" style="width: 40%">Type conversion functions</th> + <th class="text-center">Description</th> + </tr> + </thead> + + <tbody> + <tr> <td> {% highlight java %} -localTime() +ANY.cast(TYPE) {% endhighlight %} </td> <td> - <p>Returns the current SQL time in local time zone.</p> + <p>Converts a value to a given type. E.g. <code>"42".cast(INT)</code> leads to 42.</p> </td> </tr> + </tbody> +</table> + +<table class="table table-bordered"> + <thead> + <tr> + <th class="text-left" style="width: 40%">Value constructor functions</th> + <th class="text-center">Description</th> + </tr> + </thead> + + <tbody> + <tr> <td> {% highlight java %} -localTimestamp() +ARRAY.at(INT) {% endhighlight %} </td> <td> - <p>Returns the current SQL timestamp in local time zone.</p> + <p>Returns the element at a particular position in an array. The index starts at 1.</p> </td> </tr> <tr> <td> {% highlight java %} -temporalOverlaps(TIMEPOINT, TEMPORAL, TIMEPOINT, TEMPORAL) +array(ANY [, ANY ]*) {% endhighlight %} </td> <td> - <p>Determines whether two anchored time intervals overlap. Time point and temporal are transformed into a range defined by two time points (start, end). The function evaluates <code>leftEnd >= rightStart && rightEnd >= leftStart</code>. E.g. <code>temporalOverlaps("2:55:00".toTime, 1.hour, "3:30:00".toTime, 2.hour)</code> leads to true.</p> + <p>Creates an array from a list of values. The array will be an array of objects (not primitives).</p> </td> </tr> @@ -2042,118 +2121,578 @@ NUMERIC.rows </td> </tr> - <tr> - <td> - {% highlight java %} + </tbody> +</table> + +<table class="table table-bordered"> + <thead> + <tr> + <th class="text-left" style="width: 40%">Temporal functions</th> + <th class="text-center">Description</th> + </tr> + </thead> + + <tbody> + + <tr> + <td> + {% highlight java %} +STRING.toDate() +{% endhighlight %} + </td> + <td> + <p>Parses a date string in the form "yy-mm-dd" to a SQL date.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} +STRING.toTime() +{% endhighlight %} + </td> + <td> + <p>Parses a time string in the form "hh:mm:ss" to a SQL time.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} +STRING.toTimestamp() +{% endhighlight %} + </td> + <td> + <p>Parses a timestamp string in the form "yy-mm-dd hh:mm:ss.fff" to a SQL timestamp.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} +NUMERIC.year +NUMERIC.years +{% endhighlight %} + </td> + <td> + <p>Creates an interval of months for a given number of years.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} +NUMERIC.month +NUMERIC.months +{% endhighlight %} + </td> + <td> + <p>Creates an interval of months for a given number of months.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} +NUMERIC.day +NUMERIC.days +{% endhighlight %} + </td> + <td> + <p>Creates an interval of milliseconds for a given number of days.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} +NUMERIC.hour +NUMERIC.hours +{% endhighlight %} + </td> + <td> + <p>Creates an interval of milliseconds for a given number of hours.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} +NUMERIC.minute +NUMERIC.minutes +{% endhighlight %} + </td> + <td> + <p>Creates an interval of milliseconds for a given number of minutes.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} +NUMERIC.second +NUMERIC.seconds +{% endhighlight %} + </td> + <td> + <p>Creates an interval of milliseconds for a given number of seconds.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} +NUMERIC.milli +NUMERIC.millis +{% endhighlight %} + </td> + <td> + <p>Creates an interval of milliseconds.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} +currentDate() +{% endhighlight %} + </td> + <td> + <p>Returns the current SQL date in UTC time zone.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} +currentTime() +{% endhighlight %} + </td> + <td> + <p>Returns the current SQL time in UTC time zone.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} +currentTimestamp() +{% endhighlight %} + </td> + <td> + <p>Returns the current SQL timestamp in UTC time zone.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} +localTime() +{% endhighlight %} + </td> + <td> + <p>Returns the current SQL time in local time zone.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} +localTimestamp() +{% endhighlight %} + </td> + <td> + <p>Returns the current SQL timestamp in local time zone.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} +TEMPORAL.extract(TIMEINTERVALUNIT) +{% endhighlight %} + </td> + <td> + <p>Extracts parts of a time point or time interval. Returns the part as a long value. E.g. <code>'2006-06-05'.toDate.extract(DAY)</code> leads to 5.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} +TIMEPOINT.floor(TIMEINTERVALUNIT) +{% endhighlight %} + </td> + <td> + <p>Rounds a time point down to the given unit. E.g. <code>'12:44:31'.toDate.floor(MINUTE)</code> leads to 12:44:00.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} +TIMEPOINT.ceil(TIMEINTERVALUNIT) +{% endhighlight %} + </td> + <td> + <p>Rounds a time point up to the given unit. E.g. <code>'12:44:31'.toTime.floor(MINUTE)</code> leads to 12:45:00.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} +DATE.quarter() +{% endhighlight %} + </td> + <td> + <p>Returns the quarter of a year from a SQL date. E.g. <code>'1994-09-27'.toDate.quarter()</code> leads to 3.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} +temporalOverlaps(TIMEPOINT, TEMPORAL, TIMEPOINT, TEMPORAL) +{% endhighlight %} + </td> + <td> + <p>Determines whether two anchored time intervals overlap. Time point and temporal are transformed into a range defined by two time points (start, end). The function evaluates <code>leftEnd >= rightStart && rightEnd >= leftStart</code>. E.g. <code>temporalOverlaps("2:55:00".toTime, 1.hour, "3:30:00".toTime, 2.hour)</code> leads to true.</p> + </td> + </tr> + + </tbody> +</table> + +<table class="table table-bordered"> + <thead> + <tr> + <th class="text-left" style="width: 40%">Aggregate functions</th> + <th class="text-center">Description</th> + </tr> + </thead> + + <tbody> + + <tr> + <td> + {% highlight java %} +FIELD.count +{% endhighlight %} + </td> + <td> + <p>Returns the number of input rows for which the field is not null.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} +FIELD.avg +{% endhighlight %} + </td> + <td> + <p>Returns the average (arithmetic mean) of the numeric field across all input values.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} +FIELD.sum +{% endhighlight %} + </td> + <td> + <p>Returns the sum of the numeric field across all input values.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} +FIELD.max +{% endhighlight %} + </td> + <td> + <p>Returns the maximum value of field across all input values.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} +FIELD.min +{% endhighlight %} + </td> + <td> + <p>Returns the minimum value of field across all input values.</p> + </td> + </tr> + + </tbody> +</table> + +<table class="table table-bordered"> + <thead> + <tr> + <th class="text-left" style="width: 40%">Value access functions</th> + <th class="text-center">Description</th> + </tr> + </thead> + + <tbody> + + <tr> + <td> + {% highlight java %} +COMPOSITE.get(STRING) +COMPOSITE.get(INT) +{% endhighlight %} + </td> + <td> + <p>Accesses the field of a Flink composite type (such as Tuple, POJO, etc.) by index or name and returns it's value. E.g. <code>pojo.get('myField')</code> or <code>tuple.get(0)</code>.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} ANY.flatten() {% endhighlight %} </td> <td> - <p>Converts a Flink composite type (such as Tuple, POJO, etc.) and all of its direct subtypes into a flat representation where every subtype is a separate field. In most cases the fields of the flat representation are named similarly to the original fields but with a dollar separator (e.g. <code>mypojo$mytuple$f0</code>).</p> + <p>Converts a Flink composite type (such as Tuple, POJO, etc.) and all of its direct subtypes into a flat representation where every subtype is a separate field. In most cases the fields of the flat representation are named similarly to the original fields but with a dollar separator (e.g. <code>mypojo$mytuple$f0</code>).</p> + </td> + </tr> + + </tbody> +</table> + +<table class="table table-bordered"> + <thead> + <tr> + <th class="text-left" style="width: 40%">Array functions</th> + <th class="text-center">Description</th> + </tr> + </thead> + + <tbody> + + <tr> + <td> + {% highlight java %} +ARRAY.cardinality() +{% endhighlight %} + </td> + <td> + <p>Returns the number of elements of an array.</p> + </td> + </tr> + + <tr> + <td> + {% highlight java %} +ARRAY.element() +{% endhighlight %} + </td> + <td> + <p>Returns the sole element of an array with a single element. Returns <code>null</code> if the array is empty. Throws an exception if the array has more than one element.</p> + </td> + </tr> + + </tbody> +</table> + +<table class="table table-bordered"> + <thead> + <tr> + <th class="text-left" style="width: 40%">Auxiliary functions</th> + <th class="text-center">Description</th> + </tr> + </thead> + + <tbody> + + <tr> + <td> + {% highlight java %} +ANY.as(name [, name ]* ) +{% endhighlight %} + </td> + <td> + <p>Specifies a name for an expression i.e. a field. Additional names can be specified if the expression expands to multiple fields.</p> </td> </tr> + </tbody> +</table> + +</div> +<div data-lang="scala" markdown="1"> + +<table class="table table-bordered"> + <thead> <tr> + <th class="text-left" style="width: 40%">Comparison functions</th> + <th class="text-center">Description</th> + </tr> + </thead> + + <tbody> + + <tr> <td> - {% highlight java %} -COMPOSITE.get(STRING) -COMPOSITE.get(INT) + {% highlight scala %} +ANY === ANY {% endhighlight %} </td> <td> - <p>Accesses the field of a Flink composite type (such as Tuple, POJO, etc.) by index or name and returns it's value. E.g. <code>pojo.get('myField')</code> or <code>tuple.get(0)</code>.</p> + <p>Equals.</p> </td> </tr> <tr> <td> - {% highlight java %} -ARRAY.at(INT) + {% highlight scala %} +ANY !== ANY {% endhighlight %} </td> <td> - <p>Returns the element at a particular position in an array. The index starts at 1.</p> + <p>Not equal.</p> </td> </tr> <tr> <td> - {% highlight java %} -array(ANY [, ANY ]*) + {% highlight scala %} +ANY > ANY {% endhighlight %} </td> <td> - <p>Creates an array from a list of values. The array will be an array of objects (not primitives).</p> + <p>Greater than.</p> </td> </tr> <tr> <td> - {% highlight java %} -ARRAY.cardinality() + {% highlight scala %} +ANY >= ANY {% endhighlight %} </td> <td> - <p>Returns the number of elements of an array.</p> + <p>Greater than or equal.</p> </td> </tr> <tr> <td> {% highlight scala %} -ARRAY.element() +ANY < ANY {% endhighlight %} </td> <td> - <p>Returns the sole element of an array with a single element. Returns <code>null</code> if the array is empty. Throws an exception if the array has more than one element.</p> + <p>Less than.</p> + </td> + </tr> + + <tr> + <td> + {% highlight scala %} +ANY <= ANY +{% endhighlight %} + </td> + <td> + <p>Less than or equal.</p> + </td> + </tr> + + <tr> + <td> + {% highlight scala %} +ANY.isNull +{% endhighlight %} + </td> + <td> + <p>Returns true if the given expression is null.</p> + </td> + </tr> + + <tr> + <td> + {% highlight scala %} +ANY.isNotNull +{% endhighlight %} + </td> + <td> + <p>Returns true if the given expression is not null.</p> + </td> + </tr> + + <tr> + <td> + {% highlight scala %} +STRING.like(STRING) +{% endhighlight %} + </td> + <td> + <p>Returns true, if a string matches the specified LIKE pattern. E.g. "Jo_n%" matches all strings that start with "Jo(arbitrary letter)n".</p> + </td> + </tr> + + <tr> + <td> + {% highlight scala %} +STRING.similar(STRING) +{% endhighlight %} + </td> + <td> + <p>Returns true, if a string matches the specified SQL regex pattern. E.g. "A+" matches all strings that consist of at least one "A".</p> </td> </tr> </tbody> </table> -</div> -<div data-lang="scala" markdown="1"> - <table class="table table-bordered"> <thead> <tr> - <th class="text-left" style="width: 40%">Function</th> + <th class="text-left" style="width: 40%">Logical functions</th> <th class="text-center">Description</th> </tr> </thead> <tbody> + <tr> <td> {% highlight scala %} -ANY.as(name [, name ]* ) +boolean1 || boolean2 {% endhighlight %} </td> <td> - <p>Specifies a name for an expression i.e. a field. Additional names can be specified if the expression expands to multiple fields.</p> + <p>Returns true if <i>boolean1</i> is true or <i>boolean2</i> is true. Supports three-valued logic.</p> </td> </tr> <tr> <td> {% highlight scala %} -ANY.isNull +boolean1 && boolean2 {% endhighlight %} </td> <td> - <p>Returns true if the given expression is null.</p> + <p>Returns true if <i>boolean1</i> and <i>boolean2</i> are both true. Supports three-valued logic.</p> </td> </tr> <tr> <td> {% highlight scala %} -ANY.isNotNull +!BOOLEAN {% endhighlight %} </td> <td> - <p>Returns true if the given expression is not null.</p> + <p>Returns true if boolean expression is not true; returns null if boolean is null.</p> </td> </tr> @@ -2201,48 +2740,115 @@ BOOLEAN.isNotFalse </td> </tr> + </tbody> +</table> + +<table class="table table-bordered"> + <thead> <tr> + <th class="text-left" style="width: 40%">Arithmetic functions</th> + <th class="text-center">Description</th> + </tr> + </thead> + + <tbody> + + <tr> <td> {% highlight scala %} -NUMERIC.exp() ++ numeric {% endhighlight %} </td> <td> - <p>Calculates the Euler's number raised to the given power.</p> + <p>Returns <i>numeric</i>.</p> </td> </tr> <tr> <td> {% highlight scala %} -NUMERIC.log10() +- numeric {% endhighlight %} </td> <td> - <p>Calculates the base 10 logarithm of given value.</p> + <p>Returns negative <i>numeric</i>.</p> + </td> + </tr> + + <tr> + <td> + {% highlight scala %} +numeric1 + numeric2 +{% endhighlight %} + </td> + <td> + <p>Returns <i>numeric1</i> plus <i>numeric2</i>.</p> </td> </tr> + <tr> + <td> + {% highlight scala %} +numeric1 - numeric2 +{% endhighlight %} + </td> + <td> + <p>Returns <i>numeric1</i> minus <i>numeric2</i>.</p> + </td> + </tr> <tr> <td> {% highlight scala %} -NUMERIC.ln() +numeric1 * numeric2 {% endhighlight %} </td> <td> - <p>Calculates the natural logarithm of given value.</p> + <p>Returns <i>numeric1</i> multiplied by <i>numeric2</i>.</p> + </td> + </tr> + + <tr> + <td> + {% highlight scala %} +numeric1 / numeric2 +{% endhighlight %} + </td> + <td> + <p>Returns <i>numeric1</i> divided by <i>numeric2</i>.</p> + </td> + </tr> + + <tr> + <td> + {% highlight scala %} +numeric1.power(numeric2) +{% endhighlight %} + </td> + <td> + <p>Returns <i>numeric1</i> raised to the power of <i>numeric2</i>.</p> + </td> + </tr> + + <tr> + <td> + {% highlight scala %} +NUMERIC.abs() +{% endhighlight %} + </td> + <td> + <p>Calculates the absolute value of given value.</p> </td> </tr> <tr> <td> {% highlight scala %} -NUMERIC.power(NUMERIC) +numeric1 % numeric2 {% endhighlight %} </td> <td> - <p>Calculates the given number raised to the power of the other value.</p> + <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> @@ -2260,55 +2866,123 @@ NUMERIC.sqrt() <tr> <td> {% highlight scala %} -NUMERIC.abs() +NUMERIC.ln() +{% endhighlight %} + </td> + <td> + <p>Calculates the natural logarithm of given value.</p> + </td> + </tr> + + <tr> + <td> + {% highlight scala %} +NUMERIC.log10() +{% endhighlight %} + </td> + <td> + <p>Calculates the base 10 logarithm of given value.</p> + </td> + </tr> + + <tr> + <td> + {% highlight scala %} +NUMERIC.exp() +{% endhighlight %} + </td> + <td> + <p>Calculates the Euler's number raised to the given power.</p> + </td> + </tr> + + <tr> + <td> + {% highlight scala %} +NUMERIC.ceil() +{% endhighlight %} + </td> + <td> + <p>Calculates the smallest integer greater than or equal to a given number.</p> + </td> + </tr> + + <tr> + <td> + {% highlight scala %} +NUMERIC.floor() +{% endhighlight %} + </td> + <td> + <p>Calculates the largest integer less than or equal to a given number.</p> + </td> + </tr> + + </tbody> +</table> + +<table class="table table-bordered"> + <thead> + <tr> + <th class="text-left" style="width: 40%">Arithmetic functions</th> + <th class="text-center">Description</th> + </tr> + </thead> + + <tbody> + + <tr> + <td> + {% highlight scala %} +STRING + STRING {% endhighlight %} </td> <td> - <p>Calculates the absolute value of given value.</p> + <p>Concatenates two character strings.</p> </td> </tr> <tr> <td> {% highlight scala %} -NUMERIC.floor() +STRING.charLength() {% endhighlight %} </td> <td> - <p>Calculates the largest integer less than or equal to a given number.</p> + <p>Returns the length of a String.</p> </td> - </tr> + </tr> <tr> <td> {% highlight scala %} -NUMERIC.ceil() +STRING.upperCase() {% endhighlight %} </td> <td> - <p>Calculates the smallest integer greater than or equal to a given number.</p> + <p>Returns all of the characters in a string in upper case using the rules of the default locale.</p> </td> </tr> <tr> <td> {% highlight scala %} -STRING.substring(INT, INT) +STRING.lowerCase() {% 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>Returns all of the characters in a string in lower case using the rules of the default locale.</p> </td> </tr> <tr> <td> {% highlight scala %} -STRING.substring(INT) +STRING.position(STRING) {% 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 the position of string in an other string starting at 1. Returns 0 if string could not be found. E.g. <code>"a".position("bbbbba")</code> leads to 6.</p> </td> </tr> @@ -2329,33 +3003,34 @@ STRING.trim( <tr> <td> {% highlight scala %} -STRING.charLength() +STRING.overlay(STRING, INT) +STRING.overlay(STRING, INT, INT) {% endhighlight %} </td> <td> - <p>Returns the length of a String.</p> + <p>Replaces a substring of string with a string starting at a position (starting at 1). An optional length specifies how many characters should be removed. E.g. <code>"xxxxxtest".overlay("xxxx", 6)</code> leads to "xxxxxxxxx", <code>"xxxxxtest".overlay('xxxx', 6, 2)</code> leads to "xxxxxxxxxst".</p> </td> </tr> <tr> <td> {% highlight scala %} -STRING.upperCase() +STRING.substring(INT) {% endhighlight %} </td> <td> - <p>Returns all of the characters in a string in upper case using the rules of the default locale.</p> + <p>Creates a substring of the given string beginning at the given index to the end. The start index starts at 1 and is inclusive.</p> </td> </tr> <tr> <td> {% highlight scala %} -STRING.lowerCase() +STRING.substring(INT, INT) {% endhighlight %} </td> <td> - <p>Returns all of the characters in a string in lower case using the rules of the default locale.</p> + <p>Creates a substring of the given string at the given index for the given length. The index starts at 1 and is inclusive, i.e., the character at the index is included in the substring. The substring has the specified length or less.</p> </td> </tr> @@ -2371,51 +3046,113 @@ STRING.initCap() </td> </tr> + </tbody> +</table> + +<table class="table table-bordered"> + <thead> + <tr> + <th class="text-left" style="width: 40%">Conditional functions</th> + <th class="text-center">Description</th> + </tr> + </thead> + + <tbody> + + <tr> + <td> + {% highlight java %} +BOOLEAN.?(value1, value2) +{% endhighlight %} + </td> + <td> + <p>Ternary conditional operator that decides which of two other expressions should be evaluated based on a evaluated boolean condition. E.g. <code>(42 > 5).?("A", "B")</code> leads to "A".</p> + </td> + </tr> + + </tbody> +</table> + +<table class="table table-bordered"> + <thead> + <tr> + <th class="text-left" style="width: 40%">Type conversion functions</th> + <th class="text-center">Description</th> + </tr> + </thead> + + <tbody> + <tr> <td> {% highlight scala %} -STRING.like(STRING) +ANY.cast(TYPE) {% 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>Converts a value to a given type. E.g. <code>"42".cast(Types.INT)</code> leads to 42.</p> </td> </tr> + </tbody> +</table> + +<table class="table table-bordered"> + <thead> + <tr> + <th class="text-left" style="width: 40%">Value constructor functions</th> + <th class="text-center">Description</th> + </tr> + </thead> + + <tbody> + <tr> <td> {% highlight scala %} -STRING.similar(STRING) +ARRAY.at(INT) {% 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>Returns the element at a particular position in an array. The index starts at 1.</p> </td> </tr> <tr> <td> {% highlight scala %} -STRING.position(STRING) +array(ANY [, ANY ]*) {% endhighlight %} </td> <td> - <p>Returns the position of string in an other string starting at 1. Returns 0 if string could not be found. E.g. <code>"a".position("bbbbba")</code> leads to 6.</p> + <p>Creates an array from a list of values. The array will be an array of objects (not primitives).</p> </td> </tr> <tr> <td> {% highlight scala %} -STRING.overlay(STRING, INT) -STRING.overlay(STRING, INT, INT) +NUMERIC.rows {% endhighlight %} </td> <td> - <p>Replaces a substring of string with a string starting at a position (starting at 1). An optional length specifies how many characters should be removed. E.g. <code>"xxxxxtest".overlay("xxxx", 6)</code> leads to "xxxxxxxxx", <code>"xxxxxtest".overlay('xxxx', 6, 2)</code> leads to "xxxxxxxxxst".</p> + <p>Creates an interval of rows.</p> </td> </tr> + </tbody> +</table> + +<table class="table table-bordered"> + <thead> + <tr> + <th class="text-left" style="width: 40%">Temporal functions</th> + <th class="text-center">Description</th> + </tr> + </thead> + + <tbody> + <tr> <td> {% highlight scala %} @@ -2453,6 +3190,7 @@ STRING.toTimestamp <td> {% highlight scala %} NUMERIC.year +NUMERIC.years {% endhighlight %} </td> <td> @@ -2464,6 +3202,7 @@ NUMERIC.year <td> {% highlight scala %} NUMERIC.month +NUMERIC.months {% endhighlight %} </td> <td> @@ -2475,6 +3214,7 @@ NUMERIC.month <td> {% highlight scala %} NUMERIC.day +NUMERIC.days {% endhighlight %} </td> <td> @@ -2486,6 +3226,7 @@ NUMERIC.day <td> {% highlight scala %} NUMERIC.hour +NUMERIC.hours {% endhighlight %} </td> <td> @@ -2497,6 +3238,7 @@ NUMERIC.hour <td> {% highlight scala %} NUMERIC.minute +NUMERIC.minutes {% endhighlight %} </td> <td> @@ -2508,6 +3250,7 @@ NUMERIC.minute <td> {% highlight scala %} NUMERIC.second +NUMERIC.seconds {% endhighlight %} </td> <td> @@ -2519,6 +3262,7 @@ NUMERIC.second <td> {% highlight scala %} NUMERIC.milli +NUMERIC.millis {% endhighlight %} </td> <td> @@ -2529,99 +3273,99 @@ NUMERIC.milli <tr> <td> {% highlight scala %} -TEMPORAL.extract(TimeIntervalUnit) +currentDate() {% endhighlight %} </td> <td> - <p>Extracts parts of a time point or time interval. Returns the part as a long value. E.g. <code>"2006-06-05".toDate.extract(TimeIntervalUnit.DAY)</code> leads to 5.</p> + <p>Returns the current SQL date in UTC time zone.</p> </td> </tr> <tr> <td> {% highlight scala %} -DATE.quarter() +currentTime() {% endhighlight %} </td> <td> - <p>Returns the quarter of a year from a SQL date. E.g. <code>"1994-09-27".toDate.quarter()</code> leads to 3.</p> + <p>Returns the current SQL time in UTC time zone.</p> </td> </tr> <tr> <td> {% highlight scala %} -TIMEPOINT.floor(TimeIntervalUnit) +currentTimestamp() {% endhighlight %} </td> <td> - <p>Rounds a time point down to the given unit. E.g. <code>"12:44:31".toTime.floor(TimeIntervalUnit.MINUTE)</code> leads to 12:44:00.</p> + <p>Returns the current SQL timestamp in UTC time zone.</p> </td> </tr> <tr> <td> {% highlight scala %} -TIMEPOINT.ceil(TimeIntervalUnit) +localTime() {% endhighlight %} </td> <td> - <p>Rounds a time point up to the given unit. E.g. <code>"12:44:31".toTime.floor(TimeIntervalUnit.MINUTE)</code> leads to 12:45:00.</p> + <p>Returns the current SQL time in local time zone.</p> </td> </tr> <tr> <td> {% highlight scala %} -currentDate() +localTimestamp() {% endhighlight %} </td> <td> - <p>Returns the current SQL date in UTC time zone.</p> + <p>Returns the current SQL timestamp in local time zone.</p> </td> </tr> <tr> <td> {% highlight scala %} -currentTime() +TEMPORAL.extract(TimeIntervalUnit) {% endhighlight %} </td> <td> - <p>Returns the current SQL time in UTC time zone.</p> + <p>Extracts parts of a time point or time interval. Returns the part as a long value. E.g. <code>"2006-06-05".toDate.extract(TimeIntervalUnit.DAY)</code> leads to 5.</p> </td> </tr> <tr> <td> {% highlight scala %} -currentTimestamp() +TIMEPOINT.floor(TimeIntervalUnit) {% endhighlight %} </td> <td> - <p>Returns the current SQL timestamp in UTC time zone.</p> + <p>Rounds a time point down to the given unit. E.g. <code>"12:44:31".toTime.floor(TimeIntervalUnit.MINUTE)</code> leads to 12:44:00.</p> </td> </tr> <tr> <td> {% highlight scala %} -localTime() +TIMEPOINT.ceil(TimeIntervalUnit) {% endhighlight %} </td> <td> - <p>Returns the current SQL time in local time zone.</p> + <p>Rounds a time point up to the given unit. E.g. <code>"12:44:31".toTime.floor(TimeIntervalUnit.MINUTE)</code> leads to 12:45:00.</p> </td> </tr> <tr> <td> {% highlight scala %} -localTimestamp() +DATE.quarter() {% endhighlight %} </td> <td> - <p>Returns the current SQL timestamp in local time zone.</p> + <p>Returns the quarter of a year from a SQL date. E.g. <code>"1994-09-27".toDate.quarter()</code> leads to 3.</p> </td> </tr> @@ -2635,63 +3379,124 @@ temporalOverlaps(TIMEPOINT, TEMPORAL, TIMEPOINT, TEMPORAL) <p>Determines whether two anchored time intervals overlap. Time point and temporal are transformed into a range defined by two time points (start, end). The function evaluates <code>leftEnd >= rightStart && rightEnd >= leftStart</code>. E.g. <code>temporalOverlaps('2:55:00'.toTime, 1.hour, '3:30:00'.toTime, 2.hours)</code> leads to true.</p> </td> </tr> + + </tbody> +</table> +<table class="table table-bordered"> + <thead> <tr> + <th class="text-left" style="width: 40%">Aggregate functions</th> + <th class="text-center">Description</th> + </tr> + </thead> + + <tbody> + + <tr> <td> {% highlight scala %} -NUMERIC.rows +FIELD.count {% endhighlight %} </td> <td> - <p>Creates an interval of rows.</p> + <p>Returns the number of input rows for which the field is not null.</p> </td> </tr> <tr> <td> {% highlight scala %} -ANY.flatten() +FIELD.avg {% endhighlight %} </td> <td> - <p>Converts a Flink composite type (such as Tuple, POJO, etc.) and all of its direct subtypes into a flat representation where every subtype is a separate field. In most cases the fields of the flat representation are named similarly to the original fields but with a dollar separator (e.g. <code>mypojo$mytuple$f0</code>).</p> + <p>Returns the average (arithmetic mean) of the numeric field across all input values.</p> </td> </tr> <tr> <td> {% highlight scala %} -COMPOSITE.get(STRING) -COMPOSITE.get(INT) +FIELD.sum {% endhighlight %} </td> <td> - <p>Accesses the field of a Flink composite type (such as Tuple, POJO, etc.) by index or name and returns it's value. E.g. <code>'pojo.get("myField")</code> or <code>'tuple.get(0)</code>.</p> + <p>Returns the sum of the numeric field across all input values.</p> </td> </tr> <tr> <td> {% highlight scala %} -ARRAY.at(INT) +FIELD.max {% endhighlight %} </td> <td> - <p>Returns the element at a particular position in an array. The index starts at 1.</p> + <p>Returns the maximum value of field across all input values.</p> </td> </tr> <tr> <td> {% highlight scala %} -array(ANY [, ANY ]*) +FIELD.min {% endhighlight %} </td> <td> - <p>Creates an array from a list of values. The array will be an array of objects (not primitives).</p> + <p>Returns the minimum value of field across all input values.</p> + </td> + </tr> + + </tbody> +</table> + +<table class="table table-bordered"> + <thead> + <tr> + <th class="text-left" style="width: 40%">Value access functions</th> + <th class="text-center">Description</th> + </tr> + </thead> + + <tbody> + + <tr> + <td> + {% highlight scala %} +COMPOSITE.get(STRING) +COMPOSITE.get(INT) +{% endhighlight %} + </td> + <td> + <p>Accesses the field of a Flink composite type (such as Tuple, POJO, etc.) by index or name and returns it's value. E.g. <code>'pojo.get("myField")</code> or <code>'tuple.get(0)</code>.</p> + </td> + </tr> + + <tr> + <td> + {% highlight scala %} +ANY.flatten() +{% endhighlight %} + </td> + <td> + <p>Converts a Flink composite type (such as Tuple, POJO, etc.) and all of its direct subtypes into a flat representation where every subtype is a separate field. In most cases the fields of the flat representation are named similarly to the original fields but with a dollar separator (e.g. <code>mypojo$mytuple$f0</code>).</p> </td> </tr> + </tbody> +</table> + +<table class="table table-bordered"> + <thead> + <tr> + <th class="text-left" style="width: 40%">Array functions</th> + <th class="text-center">Description</th> + </tr> + </thead> + + <tbody> + <tr> <td> {% highlight scala %} @@ -2716,6 +3521,31 @@ ARRAY.element() </tbody> </table> + + + +<table class="table table-bordered"> + <thead> + <tr> + <th class="text-left" style="width: 40%">Auxiliary functions</th> + <th class="text-center">Description</th> + </tr> + </thead> + + <tbody> + <tr> + <td> + {% highlight scala %} +ANY.as(name [, name ]* ) +{% endhighlight %} + </td> + <td> + <p>Specifies a name for an expression i.e. a field. Additional names can be specified if the expression expands to multiple fields.</p> + </td> + </tr> + + </tbody> +</table> </div> <div data-lang="SQL" markdown="1"> @@ -2811,7 +3641,7 @@ value IS NULL {% endhighlight %} </td> <td> - <p>Whether <i>value</i> is null.</p> + <p>Returns TRUE if <i>value</i> is null.</p> </td> </tr> @@ -2822,7 +3652,7 @@ value IS NOT NULL {% endhighlight %} </td> <td> - <p>Whether <i>value</i> is not null.</p> + <p>Returns TRUE if <i>value</i> is not null.</p> </td> </tr> @@ -2833,7 +3663,7 @@ value1 IS DISTINCT FROM value2 {% endhighlight %} </td> <td> - <p>Whether two values are not equal, treating null values as the same.</p> + <p>Returns TRUE if two values are not equal, treating null values as the same.</p> </td> </tr> @@ -2844,7 +3674,7 @@ value1 IS NOT DISTINCT FROM value2 {% endhighlight %} </td> <td> - <p>Whether two values are equal, treating null values as the same.</p> + <p>Returns TRUE if two values are equal, treating null values as the same.</p> </td> </tr> @@ -2855,7 +3685,7 @@ 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> + <p>Returns TRUE if <i>value1</i> is greater than or equal to <i>value2</i> and less than or equal to <i>value3</i>.</p> </td> </tr> @@ -2866,7 +3696,7 @@ 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> + <p>Returns TRUE if <i>value1</i> is less than <i>value2</i> or greater than <i>value3</i>.</p> </td> </tr> @@ -2877,7 +3707,7 @@ string1 LIKE string2 [ ESCAPE string3 ] {% endhighlight %} </td> <td> - <p>Whether <i>string1</i> matches pattern <i>string2</i>. An escape character can be defined if necessary.</p> + <p>Returns TRUE if <i>string1</i> matches pattern <i>string2</i>. An escape character can be defined if necessary.</p> </td> </tr> @@ -2888,7 +3718,7 @@ string1 NOT LIKE string2 [ ESCAPE string3 ] {% endhighlight %} </td> <td> - <p>Whether <i>string1</i> does not match pattern <i>string2</i>. An escape character can be defined if necessary.</p> + <p>Returns TRUE if <i>string1</i> does not match pattern <i>string2</i>. An escape character can be defined if necessary.</p> </td> </tr> @@ -2899,7 +3729,7 @@ string1 SIMILAR TO string2 [ ESCAPE string3 ] {% endhighlight %} </td> <td> - <p>Whether <i>string1</i> matches regular expression <i>string2</i>. An escape character can be defined if necessary.</p> + <p>Returns TRUE if <i>string1</i> matches regular expression <i>string2</i>. An escape character can be defined if necessary.</p> </td> </tr> @@ -2911,7 +3741,7 @@ string1 NOT SIMILAR TO string2 [ ESCAPE string3 ] {% endhighlight %} </td> <td> - <p>Whether <i>string1</i> does not match regular expression <i>string2</i>. An escape character can be defined if necessary.</p> + <p>Returns TRUE if <i>string1</i> does not match regular expression <i>string2</i>. An escape character can be defined if necessary.</p> </td> </tr> @@ -2923,7 +3753,7 @@ value IN (value [, value]* ) {% endhighlight %} </td> <td> - <p>Whether <i>value</i> is equal to a value in a list.</p> + <p>Returns TRUE if <i>value</i> is equal to a value in a list.</p> </td> </tr> @@ -2934,7 +3764,7 @@ value NOT IN (value [, value]* ) {% endhighlight %} </td> <td> - <p>Whether <i>value</i> is not equal to every value in a list.</p> + <p>Returns TRUE if <i>value</i> is not equal to every value in a list.</p> </td> </tr> @@ -2945,7 +3775,7 @@ EXISTS (sub-query) {% endhighlight %} </td> <td> - <p>Whether <i>sub-query</i> returns at least one row. Only supported if the operation can be rewritten in a join and group operation.</p> + <p>Returns TRUE if <i>sub-query</i> returns at least one row. Only supported if the operation can be rewritten in a join and group operation.</p> </td> </tr> @@ -2957,7 +3787,7 @@ value IN (sub-query) {% endhighlight %} </td> <td> - <p>Whether <i>value</i> is equal to a row returned by sub-query.</p> + <p>Returns TRUE if <i>value</i> is equal to a row returned by sub-query.</p> </td> </tr> @@ -2968,7 +3798,7 @@ value NOT IN (sub-query) {% endhighlight %} </td> <td> - <p>Whether <i>value</i> is not equal to every row returned by sub-query.</p> + <p>Returns TRUE if <i>value</i> is not equal to every row returned by sub-query.</p> </td> </tr> --> @@ -2992,7 +3822,7 @@ boolean1 OR boolean2 {% endhighlight %} </td> <td> - <p>Whether <i>boolean1</i> is TRUE or <i>boolean2</i> is TRUE.</p> + <p>Returns TRUE if <i>boolean1</i> is TRUE or <i>boolean2</i> is TRUE. Supports three-valued logic.</p> </td> </tr> @@ -3003,7 +3833,7 @@ boolean1 AND boolean2 {% endhighlight %} </td> <td> - <p>Whether <i>boolean1</i> and <i>boolean2</i> are both TRUE.</p> + <p>Returns TRUE if <i>boolean1</i> and <i>boolean2</i> are both TRUE. Supports three-valued logic.</p> </td> </tr> @@ -3014,7 +3844,7 @@ NOT boolean {% endhighlight %} </td> <td> - <p>Whether <i>boolean</i> is not TRUE; returns UNKNOWN if <i>boolean</i> is UNKNOWN.</p> + <p>Returns TRUE if <i>boolean</i> is not TRUE; returns UNKNOWN if <i>boolean</i> is UNKNOWN.</p> </td> </tr> @@ -3025,7 +3855,7 @@ boolean IS FALSE {% endhighlight %} </td> <td> - <p>Whether <i>boolean</i> is FALSE; returns FALSE if <i>boolean</i> is UNKNOWN.</p> + <p>Returns TRUE if <i>boolean</i> is FALSE; returns FALSE if <i>boolean</i> is UNKNOWN.</p> </td> </tr> @@ -3036,7 +3866,7 @@ boolean IS NOT FALSE {% endhighlight %} </td> <td> - <p>Whether <i>boolean</i> is not FALSE; returns TRUE if <i>boolean</i> is UNKNOWN.</p> + <p>Returns TRUE if <i>boolean</i> is not FALSE; returns TRUE if <i>boolean</i> is UNKNOWN.</p> </td> </tr> @@ -3047,7 +3877,7 @@ boolean IS TRUE {% endhighlight %} </td> <td> - <p>Whether <i>boolean</i> is TRUE; returns FALSE if <i>boolean</i> is UNKNOWN.</p> + <p>Returns TRUE if <i>boolean</i> is TRUE; returns FALSE if <i>boolean</i> is UNKNOWN.</p> </td> </tr> @@ -3058,7 +3888,7 @@ boolean IS NOT TRUE {% endhighlight %} </td> <td> - <p>Whether <i>boolean</i> is not TRUE; returns TRUE if <i>boolean</i> is UNKNOWN.</p> + <p>Returns TRUE if <i>boolean</i> is not TRUE; returns TRUE if <i>boolean</i> is UNKNOWN.</p> </td> </tr> @@ -3069,7 +3899,7 @@ boolean IS UNKNOWN {% endhighlight %} </td> <td> - <p>Whether <i>boolean</i> is UNKNOWN.</p> + <p>Returns TRUE if <i>boolean</i> is UNKNOWN.</p> </td> </tr> @@ -3080,7 +3910,7 @@ boolean IS NOT UNKNOWN {% endhighlight %} </td> <td> - <p>Whether <i>boolean</i> is not UNKNOWN.</p> + <p>Returns TRUE if <i>boolean</i> is not UNKNOWN.</p> </td> </tr> @@ -3681,6 +4511,28 @@ CEIL(timepoint TO timeintervalunit) <p>Rounds a time point up to the given unit. E.g. <code>CEIL(TIME '12:44:31' TO MINUTE)</code> leads to 12:45:00.</p> </td> </tr> + + <tr> + <td> + {% highlight text %} +QUARTER(date) +{% endhighlight %} + </td> + <td> + <p>Returns the quarter of a year from a SQL date. E.g. <code>QUARTER(DATE '1994-09-27')</code> leads to 3.</p> + </td> + </tr> + + <tr> + <td> + {% highlight text %} +(timepoint, temporal) OVERLAPS (timepoint, temporal) +{% endhighlight %} + </td> + <td> + <p>Determines whether two anchored time intervals overlap. Time point and temporal are transformed into a range defined by two time points (start, end). The function evaluates <code>leftEnd >= rightStart && rightEnd >= leftStart</code>. E.g. <code>(TIME '2:55:00', INTERVAL '1' HOUR) OVERLAPS (TIME '3:30:00', INTERVAL '2' HOUR)</code> leads to true; <code>(TIME '9:00:00', TIME '10:00:00') OVERLAPS (TIME '10:15:00', INTERVAL '3' HOUR)</code> leads to false.</p> + </td> + </tr> </tbody> </table> http://git-wip-us.apache.org/repos/asf/flink/blob/a062f4b8/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/api/scala/expressionDsl.scala ---------------------------------------------------------------------- diff --git a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/api/scala/expressionDsl.scala b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/api/scala/expressionDsl.scala index 0634f0b..06d46e3 100644 --- a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/api/scala/expressionDsl.scala +++ b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/api/scala/expressionDsl.scala @@ -47,21 +47,69 @@ trait ImplicitExpressionOperations { */ def toExpr: Expression = expr + /** + * Boolean AND in three-valued logic. + */ def && (other: Expression) = And(expr, other) + + /** + * Boolean OR in three-valued logic. + */ def || (other: Expression) = Or(expr, other) + /** + * Greater than. + */ def > (other: Expression) = GreaterThan(expr, other) + + /** + * Greater than or equal. + */ def >= (other: Expression) = GreaterThanOrEqual(expr, other) + + /** + * Less than. + */ def < (other: Expression) = LessThan(expr, other) + + /** + * Less than or equal. + */ def <= (other: Expression) = LessThanOrEqual(expr, other) + /** + * Equals. + */ def === (other: Expression) = EqualTo(expr, other) + + /** + * Not equal. + */ def !== (other: Expression) = NotEqualTo(expr, other) + /** + * Whether boolean expression is not true; returns null if boolean is null. + */ def unary_! = Not(expr) + + /** + * Returns negative numeric. + */ def unary_- = UnaryMinus(expr) + /** + * Returns numeric. + */ + def unary_+ = expr + + /** + * Returns true if the given expression is null. + */ def isNull = IsNull(expr) + + /** + * Returns true if the given expression is not null. + */ def isNotNull = IsNotNull(expr) /** @@ -84,18 +132,64 @@ trait ImplicitExpressionOperations { */ def isNotFalse = IsNotFalse(expr) + /** + * Returns left plus right. + */ def + (other: Expression) = Plus(expr, other) + + /** + * Returns left minus right. + */ def - (other: Expression) = Minus(expr, other) + + /** + * Returns left divided by right. + */ def / (other: Expression) = Div(expr, other) + + /** + * Returns left multiplied by right. + */ def * (other: Expression) = Mul(expr, other) + + /** + * Returns the remainder (modulus) of left divided by right. + * The result is negative only if left is negative. + */ def % (other: Expression) = mod(other) + /** + * Returns the sum of the numeric field across all input values. + */ def sum = Sum(expr) + + /** + * Returns the minimum value of field across all input values. + */ def min = Min(expr) + + /** + * Returns the maximum value of field across all input values. + */ def max = Max(expr) + + /** + * Returns the number of input rows for which the field is not null. + */ def count = Count(expr) + + /** + * Returns the average (arithmetic mean) of the numeric field across all input values. + */ def avg = Avg(expr) + /** + * Converts a value to a given type. + * + * e.g. "42".cast(Types.INT) leads to 42. + * + * @return casted expression + */ def cast(toType: TypeInformation[_]) = Cast(expr, toType) /** http://git-wip-us.apache.org/repos/asf/flink/blob/a062f4b8/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/expressions/ExpressionParser.scala ---------------------------------------------------------------------- diff --git a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/expressions/ExpressionParser.scala b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/expressions/ExpressionParser.scala index d85540a..ed0b16e 100644 --- a/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/expressions/ExpressionParser.scala +++ b/flink-libraries/flink-table/src/main/scala/org/apache/flink/table/expressions/ExpressionParser.scala @@ -392,7 +392,9 @@ object ExpressionParser extends JavaTokenParsers with PackratParsers { lazy val unaryMinus: PackratParser[Expression] = "-" ~> composite ^^ { e => UnaryMinus(e) } - lazy val unary = composite | unaryNot | unaryMinus | + lazy val unaryPlus: PackratParser[Expression] = "+" ~> composite ^^ { e => e } + + lazy val unary = composite | unaryNot | unaryMinus | unaryPlus | failure("Unary expression expected.") // arithmetic http://git-wip-us.apache.org/repos/asf/flink/blob/a062f4b8/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/ScalarOperatorsTest.scala ---------------------------------------------------------------------- diff --git a/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/ScalarOperatorsTest.scala b/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/ScalarOperatorsTest.scala index 098feba..ea8ac8a 100644 --- a/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/ScalarOperatorsTest.scala +++ b/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/ScalarOperatorsTest.scala @@ -86,6 +86,7 @@ class ScalarOperatorsTest extends ExpressionTestBase { testTableApi('f8 * 2, "f8 * 2", "10") testTableApi('f8 % 2, "f8 % 2", "1") testTableApi(-'f8, "-f8", "-5") + testTableApi( +'f8, "+f8", "5") // additional space before "+" required because of checkstyle testTableApi(3.toExpr + 'f8, "3 + f8", "8") // boolean arithmetic http://git-wip-us.apache.org/repos/asf/flink/blob/a062f4b8/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/SqlExpressionTest.scala ---------------------------------------------------------------------- diff --git a/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/SqlExpressionTest.scala b/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/SqlExpressionTest.scala index e0f45d4..76e95ff 100644 --- a/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/SqlExpressionTest.scala +++ b/flink-libraries/flink-table/src/test/scala/org/apache/flink/table/expressions/SqlExpressionTest.scala @@ -154,7 +154,12 @@ class SqlExpressionTest extends ExpressionTestBase { 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("FLOOR(TIME '12:44:31' TO MINUTE)", "12:44:00") + testSqlApi("CEIL(TIME '12:44:31' TO MINUTE)", "12:45:00") testSqlApi("QUARTER(DATE '2016-04-12')", "2") + testSqlApi( + "(TIME '2:55:00', INTERVAL '1' HOUR) OVERLAPS (TIME '3:30:00', INTERVAL '2' HOUR)", + "true") } @Test
