[FLINK-6810] Add a set of built-in scalar functions to Table API & SQL

• Type: New Feature
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.4.0
• Component/s: Table API & SQL
• Labels: starter
• Assignee: sunjincheng
• Reporter: sunjincheng
• Created: 02/Jun/17 08:02
• Updated: 14/Aug/18 12:22

Description

In this JIRA, we will create some sub-tasks for adding specific scalar functions such as mathematical-function LOG, date-functions
DATEADD, string-functions LPAD, etc.

How to contribute a built-in scalar function
Thank you very much for contributing a built-in function. In order to make sure your contributions are in a good direction, it is recommended to read the following instructions.

1. Investigate the behavior of the function that you are going to contribute in major DBMSs. This is very important since we have to understand the exact semantics of the function.
2. It is recommended to add function for both SQL and table-API (Java and Scala).
3. For every scalar function, add corresponding docs which should include a SQL, a Java and a Scala version in ./docs/dev/table/functions.md. Make sure your description of the function is accurate. Please do not simply copy documentation from other projects, especially if the projects are not Apache licensed.
4. Take overflow, NullPointerException and other exceptions into consideration.
5. Add unit tests for every new function and its supported APIs. Have a look at ScalarFunctionsTest, SqlExpressionTest, ScalaFunctionsValidationTest, etc. for how to implement function tests.
   
   Welcome anybody to add the sub-task about standard database scalar function.

Note: Usually, adding a runtime function to ScalarFunctions.scala is sufficient. However, sometimes it makes sense to implement a CallGenerator for FunctionGenerator to leverage object reuse. E.g., HashCalcCallGen creates a MessageDigest only once for SHA2 if the parameters are literals.

Sub-Tasks

• Add STR_TO_DATE supported in SQL: In Progress
• Add STR_TO_DATE supported in TableAPI: Open
• Add ASCII/CHR function for table/sql API: In Progress
• Add ISNUMERIC supported in Table API/SQL: In Progress
• Add reverse supported in TableAPI and SQL: In Progress
• Add REGEXP_MATCH in TableAPI and SQL: Open
• Add Sinh math function supported in Table API and SQL: In Progress
• Add COTH math function supported in Table API and SQL: Open
• Add CBRT math function supported in Table API and SQL: Open
• Add HYPOT math function supported in Table API and SQL: Open
• Add REMAINDER math function supported in Table API and SQL: In Progress