Page History

Background and Motivation

The implicit Implicit type coercion is almost supported by every production RDBMS: MYSQL[1], ORACLE[2] and MS-SQL[3], also some Hadoop data warehouse facilitates like HIVE.

The SQL is the most popular API of Flink user , as a popular computation engine that connects so many other engines that also have the SQL query entrance(i.e. Apache Hive, MySQL, PostgreSQL). Flink would supply have better compatibility with sql query to the underlying engines it adapter with adapters to if it has the built-in support for implicit type coercion.

There are already some JIRA issues and user mails that are relative with this topic more or less.

Senarios Use Cases of Type Coercion：

• When data from one object is moved to, compared with, or combined with data from another object, the data may need to be converted from the data type of one object to another.
• When data from sql result column is moved into a program variable, the data must be converted from the system data type to the data type of the variable.

...

Popular

...

DB Type Conversion Rules

Oracle

...

DB

Oracle Database automatically converts a value from one datatype to another when such a conversion makes sense. Table 2-11 is a matrix of Oracle implicit conversions. The table shows all possible conversions, without regard to the direction of the conversion or the context in which it is made. The rules governing these details follow the table.

...

• During INSERT and UPDATE operations, Oracle converts the value to the datatype of the affected column.
• During SELECT FROM operations, Oracle converts the data from the column to the type of the target variable.
• When comparing a character value with a numeric value, Oracle converts the character data to a numeric value.
• Conversions between character values or NUMBER values and floating-point number values can be inexact, because the character types and NUMBER use decimal precision to represent the numeric value, and the floating-point numbers use binary precision.
• Conversions from BINARY_FLOAT to BINARY_DOUBLE are exact.
• Conversions from BINARY_DOUBLE to BINARY_FLOAT are inexact if the BINARY_DOUBLE value uses more bits of precision that supported by the BINARY_FLOAT.
• When comparing a character value with a DATE value, Oracle converts the character data to DATE.
• When you use a SQL function or operator with an argument of a data type other than the one it accepts, Oracle converts the argument to the accepted datatype.
• When making assignments, Oracle converts the value on the right side of the equal sign (=) to the datatype of the target of the assignment on the left side.
• During concatenation operations, Oracle converts from noncharacter datatypes to CHAR or NCHAR.
• During arithmetic operations on and comparisons between character and noncharacter datatypes, Oracle converts from any character datatype to a numeric, date, or rowid, as appropriate. In arithmetic operations between CHAR/VARCHAR2 and NCHAR/NVARCHAR2, Oracle converts to a NUMBER.
• Comparisons between CHAR and VARCHAR2 and between NCHAR and NVARCHAR2 types may entail different character sets. The default direction of conversion in such cases is from the database character set to the national character set. Table 2-12 shows the direction of implicit conversions between different character types.
• Most SQL character functions are enabled to accept CLOBs as parameters, and Oracle performs implicit conversions between CLOB and character types. Therefore, functions that are not yet enabled for CLOBs can accept CLOBs through implicit conversion. In such cases, Oracle converts the CLOBs to CHAR or VARCHAR2 before the function is invoked. If the CLOB is larger than 4000 bytes, then Oracle converts only the first 4000 bytes to CHAR.

MS-SQL

...

Here is a graph that

...

The following illustration shows all explicit and implicit data type conversions that are allowed for SQL Server system-supplied builtin data types. These include xml, bigint, and sql_variant. There is no implicit conversion on assignment from the sql_variant data type, but there is implicit conversion to sql_variant.

...

Proposed Design for Flink SQL

...

The proposed design is a pluggable process during sql-to-rel conversion (E.G. AST node to relational  expression).

The work follow describes below mainly happens in Apache Calcite code.

How the Type Coercion Works

The validator will check the operands/return types of all kinds of operators:

...

• If the operator has expected data types, just take them as the desired one. (e.g. the UDF would have eval() method which has reflection argument types)
• If there is no expected data type but the data type families are registered, try to coerce the arguments to the family's default data type, i.e. the String family will have a VARCHAR type.
• If neither expected data type nor families are specified, try to find the tightest common type of the node types, i.e. int and double will return double, the numeric precision does not lose lost for this case.
• If no tightest common type is found, try to find a wider type, i.e. string and int will return int, we allow some precision loss when widening decimal to fractional, or promote to string type.

...