Data type conversion rules

Convert data types in expression evaluation

When operators in an expression are used with operands of different data types, one or more data types of the operands are converted to make the operands compatible.

Conversion mode

OceanBase Database supports both explicit and implicit data type conversion.

When an operation requires a parameter of a specified data type but the actual parameter value in the statement is not of the specified data type, OceanBase Database converts the parameter value to the specified data type before proceeding with subsequent operations. This process is known as implicit data type conversion.

In the following example, OceanBase Database automatically converts strings to numbers, or the other way around.

obclient> SELECT 1+'1';
+-------+
| 1+'1' |
+-------+
|     2 |
+-------+
1 row in set

OceanBase Database supports explicit data type conversion by using the CAST() function. Here is an example:

obclient> SELECT 31.4, CAST(31.4 AS TIME);
+------+--------------------+
| 31.4 | CAST(31.4 AS TIME) |
+------+--------------------+
| 31.4 | 00:00:31           |
+------+--------------------+
1 row in set

For more information about the CAST() function, see CAST.

Conversion rules

Conversion rules for comparison operations

• If one or both arguments are NULL, the comparison result is NULL, except for the <=> comparison operator. * The result of the NULL <=> NULL comparison is true. No conversion is required.

• If both arguments that you want to compare are strings, they are compared as strings.

• If both arguments are integers, they are compared as integers.

• If a hexadecimal value is not compared with a number, it is used as a binary string.

• Single-row subqueries from one or more tables are not treated as constants. For example, if a subquery returns an integer for comparison with a DATETIME value, the two operands are compared as integers. Integers cannot be converted to time values. To compare the operands as DATETIME values, use the CAST() function to explicitly convert a subquery value to the DATETIME type.

  If one of the arguments is a TIMESTAMP or DATETIME column, and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. To be safe, always use complete datetime, date, or time strings in comparison operations. For example, when you use the BETWEEN operator with date or time values, you can use the CAST() function to explicitly convert the values to the desired data types to obtain the best results.

• If one of the arguments is a decimal value, the compared types depend on the other argument. If the other argument is a decimal or integer value, the arguments are compared as decimal values. If the other argument is a floating-point value, the arguments are compared as floating-point values.

• In all other scenarios, the arguments are compared as floating-point real numbers. For example, string and numeric operands are compared as floating-point numbers.

  The result of comparison between a floating-point number and a large value of the INTEGER type is an approximate value. This is because an integer is converted to a double-precision floating-point number before the comparison, which cannot precisely represent all 64-bit integers.

Other conversion rules

• The conversion from a string to a floating-point number is not necessarily the same as that from an integer to a floating-point number. The results are affected by factors such as the compiler version. To avoid such an issue, you can use the CAST() function to prevent the value from being implicitly converted to a floating-point number.

• The implicit conversion of a numeric or time value to a string produces a value that has the character set and collation specified by the character_set_connection and collation_connection variables.

  This means that such a conversion produces a non-binary string, such as a CHAR, VARCHAR, or LONGTEXT value. If the connection character set is set to binary, the conversion result is a binary string, such as a BINARY, VARBINARY, or LONGBLOB value.

• The data type conversion is different from the preceding description in the evaluation of an integer expression. For example, if an integer expression is used in the CREATE TABLE statement, the created table may contain an INT or BIGINT column, depending on the type of the expression result:

  CREATE TABLE t SELECT integer_expr;
  

  If the maximum length of the expression does not fit the INT type, the BIGINT type is used. However, you can forcibly use the BIGINT type instead of the INT type by using a sufficiently long expression:

  obclient> CREATE TABLE t SELECT 000000000000000000000;
  Query OK, 1 row affected
  

Implicit data type conversion rules

OceanBase Database automatically converts a value from one data type to another when the conversion makes sense.

The following table lists a matrix of implicit conversions of all data types, without considering the conversion direction or context.

Examples of implicit data type conversion

obclient> SELECT CAST(BOOL_COLUMN AS YEAR) FROM YOUR_TABLE;
+---------------------------+
| CAST(BOOL_COLUMN AS YEAR) |
+---------------------------+
|                      2001 |
+---------------------------+
2 rows in set (0.001 sec)