[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 SQL / API
• Assignee: sunjincheng
• Reporter: sunjincheng
• Created: 02/Jun/17 08:02
• Updated: 08/Jan/21 10:06

Description

Umbrella issue for adding 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 and type inference of the function.
2. It is recommended to add function for both SQL and Table API (Java, Scala, Python).
3. For every scalar function, add corresponding docs which should include a SQL, a Java/Scala/Python version in ./docs/dev/table/functions/systemFunctions.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, handling of NULL, other data types and exceptions into consideration.

There are different generations of function stacks in the Flink code base. The newest generation has been added in FLINK-20522 and is the recommended way of implementing a function similar to a regular user-defined function.

Adding a built-in function requires changes in only 2 classes: BuiltInFunctionDefinitions for the definition and the class that contains the actual runtime logic.

Tests should be added in org.apache.flink.table.planner.functions.

See the reference implementation of the IFNULL function here:

https://github.com/apache/flink/commit/df46f8948279d1ed89d7113919b4c4f720cf4199

Sub-Tasks

• Add TIMESTAMPADD supported in SQL: Resolved
• Add TIMESTAMPADD supported in TableAPI: Resolved
• Add TIMESTAMPDIFF supported in SQL: Resolved
• Add TIMESTAMPDIFF supported in TableAPI: Resolved
• Add LOG(X) supported in SQL: Resolved
• ADD LOG(X) supported in TableAPI: Closed
• Add L/RPAD supported in SQL: Resolved
• Add L/RPAD supported in TableAPI: Resolved
• Add BIN supported in SQL & Table API: Resolved
• Add BIN supported in TableAPI: Resolved
• Add STR_TO_DATE supported in SQL: In Progress
• Add STR_TO_DATE supported in TableAPI: Open
• Add CONCAT/CONCAT_WS supported in SQL: Closed
• Add CONCAT/CONCAT_WS supported in TableAPI: Closed
• Add support for MD5, SHA1 and SHA2: Closed
• Add MD5/SHA1/SHA2 supported in TableAPI: Closed
• Add E() supported in SQL: Closed
• Add E() supported in TableAPI: Resolved
• Add DATE_FORMAT supported in SQL: Closed
• Add UUID supported in TableAPI/SQL: Resolved
• Fix Mod scala function bug: Resolved
• Add ASCII/CHR function for Table API: In Progress
• Add regexp_extract supported in TableAPI and SQL: Resolved
• Add regexp_replace supported in TableAPI and SQL: Resolved
• Add ISNUMERIC supported in Table API/SQL: Closed
• Add LTRIM supported in Table API and SQL: Resolved
• Add RTRIM supported in Table API and SQL: Resolved
• Add FROM_BASE64 function for table/sql API: Resolved
• Add TO_BASE64 function for table/sql API: Resolved
• Add REPEAT supported in Table API and SQL: Resolved
• Add replace supported in TableAPI and SQL: Resolved
• Add reverse supported in TableAPI and SQL: In Progress
• Add LOG2 function for table/sql API: Resolved
• Add REGEXP_MATCH in TableAPI and SQL: Open
• Add Cosh math function supported in Table API and SQL: Closed
• Add Sinh math function supported in Table API and SQL: Closed
• Add Tanh math function supported in Table API and SQL: Closed
• Add COTH math function supported in Table API and SQL: Closed
• Add CBRT math function supported in Table API and SQL: Closed
• Add HYPOT math function supported in Table API and SQL: Closed
• Add REMAINDER math function supported in Table API and SQL: In Progress
• Add support for IS DISTINCT FROM in code generator: Open
• Add support for IS NOT DISTINCT FROM in code generator: Open
• Add Reverse function supported in Table API and SQL: Open
• [Bitwise Functions] Add BIT_AND, BIT_OR functions supported in Table API and SQL: Open
• [Bitwise Functions] Add BIT_LSHIFT, BIT_RSHIFT functions supported in Table API and SQL: Open
• [Bitwise Functions] Add BIT_XOR function supported in Table API and SQL: Open
• Add contain operator for the array datatype to SQL: Open