FLIP-154: SQL Implicit Type Coercion

Background and Motivation

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 Apache Flink user that connects many other engines(i.e. Apache Hive, MySQL, PostgreSQL). Flink would have better compatibility with sql query to the underlying engines it adapters to if it had the built-in support for implicit type coercion.

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

Use Cases of Type Coercion：

• the inter-operation within builtin SQL operators, like binary arithmetic operations (PLUS, MINOUS and so on), binary comparison, IN, CASE WHEN, IF, SET operator (UNION, INTERSECT ..)
• the built in functions type coercion for input parameters
• the SQL INSERT source that target connector row type when there are row elements type mismatches
• the compatibility with other popular DB engines, like MySQL/Hive/PostgreSQL and so on

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.

The following rules govern the direction in which Oracle Database makes implicit data type conversions:

• 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 shows all explicit and implicit data type conversions for SQL Server 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.

Data type conversion behaviors for MS-SQL 

Proposed Design

The Common Type

The whole rules to find the proper conversion type:

• 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 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.

We try the best to find the tightest common type that does not lose precision, for example DOUBLE and INT return DOUBLE, DATE and TIMESTAMP return TIMESTAMP; If we can not find the tightest common type, then try to coerce all the operands to VARCHAR type which may lose some precision(or 2 DECIMALs with wider precision/scale).

Conversion Context and Strategy

Apache Flink SQL follows the behaviors of PostgreSQL and MS-SQL mainly, because their rules are more in line with the SQL standard.

For example, for binary arithmetic with strings, we coerce the string operand to the type of the other numeric operand: for '9' / 2 (INT), we coerce '9' to int type, and the result type is also INT (like PostgreSQL and MS-SQL did). While MySQL and Oracle would coerce all the string operands to DOUBLE.

Another case is the IN operator, we see the IN operands comparison same with the binary comparison: for 1 in ('abc', 2), Apache Flink coerce the 'abc' to INT following PostgreSQL and MS-SQL, while MySQL and Apache Spark coerces 1 and 2 to Char(3).

Type Conversion Matrix

The table below illustrates the implicit type coercion rules for all kinds of engines:

Apache Flink SQL follows the type coercion matrix of MS-SQL and Apache Spark for most of the cases(based on the fact that the conversion does not break the semantics), it also supports additional cases like the conversion between INTERVAL and VARCHAR.

See CalciteImplicitCasts for the details.

Appendix

The proposed design is a pluggable process during AST to relational expression 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:

1. If the validation passes, the validator will just cache the data type (say RelDataType) for the SqlNode it has validated;
2. If the validation fails, the validator will ask for the TypeCoercion component about if we can do an implicit type coercion, if the coercion rules passes, the TypeCoercion component will replace the SqlNode with a coerced one of desired type (the node may be an operand of an operator or a column of selected row);
3. Then the TypeCoercion component would update the inferred type for the casted node and the containing operator/row column type;
4. If the coercion rule fails again, the validator will just throw the exception as is before.

For some cases, although the validation succeed, we still need the type coercion, i.e. for expression 1 > '1', Calcite will just return false without type coercion, we do type coercion eagerly here and the expression would be coerced to 1 > cast('1' as int) whose result evaluates true.

The graph below illustrates how we coerce the operand types of a SqlCall:

The graph below illustrates how we coerce the operand type of a SqlNode with struct type:

Reference

[1] Mysql type conversion

[2] Oracle Datatype Comparison Rules

[3] SqlServer Data type conversion (Database Engine)