Data Type Conversion

Updated on 2024-12-06 GMT+08:00

View PDF

Conversion between different data types is supported. Data type conversion is involved in the following scenarios:

The data types of operands of operators (such as comparison and arithmetic operators) are inconsistent. It is commonly used for comparison operations in query conditions or join conditions.
The data types of arguments and parameters are inconsistent when a function is called.
The data types of target columns to be updated by DML statements (including INSERT, UPDATE, MERGE, and REPLACE) and the defined column types are inconsistent.
Explicit type conversion: cast(expr as datatype), which converts an expression to a data type.
After the target data type of the final projection column is determined by set operations (UNION, MINUS, EXCEPT, and INTERSECT), the type of the projection column in each SELECT statement is inconsistent with the target data type.
In other expression calculation scenarios, the target data type used for comparison or final result is determined based on the data type of different expressions.
- DECODE
- CASE WHEN
- lexpr [ NOT ] IN (expr_list)
- BETWEEN AND
- JOIN USING(a,b)
- GREATEST and LEAST
- NVL and COALESCE

GaussDB and MySQL have different rules for data type conversion and target data types. The following examples show the differences between the two processing modes:

   
    
      
      -- MySQL: The execution result of IN is 0, indicating false. According to the rule, '1970-01-01' is compared with the expressions in the list in sequence. The results are all 0s. Therefore, the final result is 0.
mysql> select '1970-01-01' in ('1970-01-02', 1, '1970-01-02');
+-------------------------------------------------+
| '1970-01-01' in ('1970-01-02', 1, '1970-01-02') |
+-------------------------------------------------+
|                                               0 |
+-------------------------------------------------+

-- GaussDB: The execution result of IN is true, which is opposite to the MySQL result. The common type selected based on the rule is int. Therefore, the left expression '1970-01-01' is converted to the int type and compared with the value after the expression in the list is converted to the int type.
-- When '1970-01-01' and '1970-01-02' are converted to the int type, the values are 1970. (In MySQL-compatible mode, invalid characters and the following content are ignored during conversion, and the previous part is converted to the int type.) The comparison result is equal. Therefore, the returned result is true.
gaussdb=# select '1970-01-01' in ('1970-01-02', 1::int, '1970-01-02') as result;
 result 
--------
 t
(1 row)

     

   
  

1. Differences in data type conversion rules:

The GaussDB clearly defines the conversion rules between different data types.
- Whether to support conversion: Conversion is supported only when the conversion path of two types is defined in the pg_cast system catalog.
- Conversion scenarios: conversion in any scenario, conversion only in CAST expressions, and conversion only during value assignment. In scenarios that are not supported, data type conversion cannot be performed even if the conversion path is defined.
MySQL supports conversion between any two data types.

Due to the preceding differences, when MySQL-based applications are migrated to GaussDB, an error may be reported because the SQL statement does not support the conversion between different data types. In the scenario where conversion is supported, different conversion rules result in different execution results of SQL statements.

You are advised to use the same data type in SQL statements for comparison or value assignment to avoid unexpected results or performance loss caused by data type conversion.

2. Differences in target data type selection rules:

In some scenarios, the data type to be compared or returned can be determined only after the types of multiple expressions are considered. For example, in the UNION operation, projection columns at the same position in different SELECT statements are of different data types. The final data type of the query result needs to be determined based on the data type of the projection columns in each SELECT statement.

GaussDB and MySQL have different rules for determining the target data types.

GaussDB rules:
- If the operand types of operators are inconsistent, the operand types are not converted to the target type before calculation. Instead, operators of two data types are directly registered, and two types of processing rules are defined during operator processing. In this mode, implicit type conversion does not exist, but the customized processing rule implies the conversion operation.
- Rules for determining the target data type in the set operation and expression scenarios:
  - If all types are the same, it is the target type.
  - If the two data types are different, check whether the data types are of the same type, such as the numeric type, character type, and date and time type. If they do not belong to the same type, the target type cannot be determined. In this case, an error is reported during SQL statement execution.
  - For data types with the same category attribute (defined in the pg_type system catalog), the data type with the preferred attribute (defined in the pg_type system catalog) is selected as the target type. If operand 1 can be converted to operand 2 (no conversion path), but operand 2 cannot be converted to operand 1 or the priority of the numeric type is lower than that of operand 2, then operand 2 is selected as the target type.
  - If three or more data types are involved, the rule for determining the target type is as follows: common_type(type1,type2,type3) = common_type(common_type(type1,type2),type3). Perform iterative processing in sequence to obtain the final result.
  - For IN and NOT IN expressions, if the target type cannot be determined based on the preceding rules, each expression in lexpr and expr_list is compared one by one based on the equivalent operator (=).
  - Precision determination: The precision of the finally selected expression is used as the final result.
MySQL rules:
- If the operand types of operators are inconsistent, determine the target type based on the following rules. Then, convert the inconsistent operand types to the target type and then process the operands.
  - If both parameters are of the string type, they are compared based on the string type.
  - If both parameters are of the integer type, they are compared based on the integer type.
  - If a hexadecimal value is not compared with a numeric value, they are compared based on the binary string.
  - If one parameter is of the datetime/timestamp type, and the other parameter is a constant, the constant is converted to the timestamp type for comparison.
  - If one parameter is of the decimal type, the data type used for comparison depends on the other parameter. If the other type is decimal or integer, the decimal type is used. If the other type is not decimal, the real type is used.
  - In other scenarios, the data type is converted to the real type for comparison.
- Rules for determining the target data type in the set operation and expression scenarios:
  - Establish a target type matrix between any two types. Given two types, the target type can be determined by using the matrix.
  - If three or more data types are involved, the rule for determining the target type is as follows: common_type(type1,type2,type3) = common_type(common_type(type1,type2),type3). Perform iterative processing in sequence to obtain the final result.
  - If the target type is integer and each expression type contains signed and unsigned integers, the type is promoted to an integer type with higher precision. The result is unsigned only when all expressions are unsigned. Otherwise, the result is signed.
  - The highest precision in the expression is used as the final result.

According to the preceding rules, GaussDB and MySQL differ greatly in data type conversion rules and types cannot be directly compared. In the preceding scenario, the execution result of SQL statements may be different from that in MySQL. In the current version, you are advised to use the same type for all expressions or use CAST to convert the type to the required type in advance to avoid differences.

Parent topic: Data Types

Previous topic: Attributes Supported by Data Types

Next topic: System Functions