Page History

...

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 are are row elements type mismatch
• the compatibility with other popular DB engines, like MySQL/Hive/PostgreSQL and so on

Popular DB Type Conversion Rules

...

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:

...