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, for 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 comparison between a floating-point number and a large value of the INTEGER type is approximate. 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, which is a CHAR, VARCHAR, or LONGTEXT value. If the connection character set is set to binary, the conversion result is a binary string, which is 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, the table in the column that is generated by the expression in the following statement can contain INT or BIGINT type:

  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