Type conversion in expression evaluation
When operators in an expression are used with operands of different types, data type conversion occurs to make the operands compatible.
Conversion methods
OceanBase AI Database supports explicit data type conversion and implicit data type conversion.
Implicit data type conversion occurs in scenarios where an operation requires a parameter of a specified data type, but the actual parameter value is not of that type. In such cases, OceanBase AI Database converts the actual parameter value to the specified data type before performing the operation.
As shown in the following example, OceanBase AI Database automatically converts strings to numbers and vice versa as needed.
obclient> SELECT 1+'1';
+-------+
| 1+'1' |
+-------+
| 2 |
+-------+
1 row in set
Data type conversion for display in OceanBase AI Database is performed using the CAST function. As shown in the following 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 parameters are
NULL, the comparison result isNULL, except for the<=>operator. ForNULL <=> NULL, the result is true and no conversion is required.If both parameters in a comparison operation are strings, they are compared as strings.
If both parameters are integers, they are compared as integers.
If not compared with a number, a hexadecimal value is treated as a binary string.
A single-row subquery from one or more tables is not considered a constant. For example, if an integer returned by a subquery is to be compared with a
DATETIMEvalue, the two operands are compared as integers. Since integers are not converted to time values, useCAST()to explicitly convert the subquery value toDATETIMEif you want to compare the operands asDATETIMEvalues.If one parameter is a
TIMESTAMPorDATETIMEcolumn and the other is a constant, the constant is converted to a timestamp before comparison. For safety, always use complete date-time, date, or time strings when making comparisons. For example, when usingBETWEENwith date or time values, to get the best results, explicitly cast the value to the required data type usingCAST().If one of the parameters is a decimal value, the comparison type depends on the other parameter. If the other parameter is a decimal or integer value, the parameters are compared as decimal values. If the other parameter is a floating-point value, they are compared as floating-point values.
In all other cases, parameters are compared as floating-point (real) numbers. For example, a comparison between a string and a numeric operand is performed as a floating-point comparison.
The comparison between floating-point numbers and large
INTEGERvalues is approximate because integers are converted to double-precision floating-point numbers before comparison, which cannot accurately represent all 64-bit integers.
Type downgrade
When possible, align constants with column types that satisfy the comparison semantics to enable efficient comparisons. Currently, most comparisons between columns and constants under standard semantics are supported. Comparisons that require casting on columns will be listed separately later:
- For
intoruintcolumns, constants of character type,decimaltype, or floating-point types (double/float) can be used as a downgrade to an integer column. - A
decimalcolumn, which supports downgrading floating-point (double/float) types todecimalcolumns. - The
yearcolumn supports downgrading from numeric types (including integer, floating-point, anddecimal), character types, and time types (includingdate,datetime,timestamp, andtime). - The
datecolumn, which supports character,datetime,timestamp, andtimedata types, can be downgraded to thedatetype. - The
timestampcolumn, which supports downgrading fromdatetimetotimestamp.
The following are the scenarios where a CAST operation is performed on columns (excluding complex data types such as arrays, XML, and JSON):
- For
floatcolumns, comparisons are always performed usingdouble. Therefore, acastis required when comparing afloatcolumn with any constant. fixed doublecolumns, which are specified with precision and scale when the table is created, for example,double(10, 2). When a constant has more decimal places, precision comparison is performed with an additional cast to the column.- For character columns, comparisons between character and numeric types are performed in numerical order. The column is
castto a numeric type. Additionally, the collation rules of the character set may also apply acastoperation on the column.
Non-standard comparison
The logic for non-standard comparisons is similar to type downgrade processing: it only handles comparisons between constants and columns, and ignores comparisons between columns. Currently, only integer constants and character columns are supported for comparison. Note that if you set the type to range, the sorting result may not conform to standard semantics. You must choose the appropriate configuration based on your data characteristics.
The following example illustrates:
A table named
t1is created, which contains a column namedbiz_dayof typevarchar(10).obclient> CREATE TABLE t1 (biz_day varchar(10)); Query OK, 0 rows affected (0.216 sec)View the execution plan by default.
obclient> EXPLAIN SELECT * FROM t1 WHERE biz_day BETWEEN 20200101 AND 20200201; +-------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------------------------------------------------------------------------------+ | ====================================================== | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | ------------------------------------------------------ | | |0 |COLUMN TABLE FULL SCAN|T1 |2 |3 | | | ====================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([T1.BIZ_DAY]), filter([cast(T1.BIZ_DAY, NUMBER(-1, -85)) >= 20200101], [cast(T1.BIZ_DAY, NUMBER(-1, -85)) <= 20200201]), rowset=16 | | access([T1.BIZ_DAY]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false,false], | | range_key([T1.__pk_increment]), range(MIN ; MAX)always true | +-------------------------------------------------------------------------------------------------------------------------------------------------+ 11 rows in set (0.004 sec)- Query plan: By default, the query optimizer converts the
biz_daycolumn from a string type to a numeric type before performing the comparison. - Filter condition:
filter([cast(T1.BIZ_DAY, NUMBER(-1, -85)) >= 20200101], [cast(T1.BIZ_DAY, NUMBER(-1, -85)) <= 20200201])convertsbiz_dayto a number and performs a range comparison.
- Query plan: By default, the query optimizer converts the
Set
non_standard_comparison_leveltoequaland then view the execution plan.obclient> EXPLAIN SELECT /*+opt_param('non_standard_comparison_level', 'equal')*/ * FROM t1 WHERE biz_day BETWEEN 20200101 AND 20200201; +----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ====================================================== | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | ------------------------------------------------------ | | |0 |COLUMN TABLE FULL SCAN|t1 |1 |3 | | | ====================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.biz_day]), filter([cast(t1.biz_day, DECIMAL(-1, -1)) >= cast(20200101, DECIMAL(20, 0))], [cast(t1.biz_day, DECIMAL(-1, -1)) <= cast(20200201, | | DECIMAL(20, 0))]), rowset=16 | | access([t1.biz_day]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false,false], | | range_key([t1.__pk_increment]), range(MIN ; MAX)always true | +----------------------------------------------------------------------------------------------------------------------------------------------------------------+ 12 rows in set (0.003 sec)- Query plan: After setting
non_standard_comparison_leveltoequal, the query optimizer converts thebiz_daycolumn from a string type to a decimal type before comparison. - Filter condition:
filter([cast(t1.biz_day, DECIMAL(-1, -1)) >= cast(20200101, DECIMAL(20, 0))], [cast(t1.biz_day, DECIMAL(-1, -1)) <= cast(20200201, DECIMAL(20, 0))])convertsbiz_dayto a decimal and performs a range comparison. Sincebetweenis a range query, the equal comparison rule ofequaldoes not convert constant types.
- Query plan: After setting
Set
non_standard_comparison_leveltorangeand then check the execution plan.obclient> EXPLAIN SELECT /*+opt_param('non_standard_comparison_level', 'range')*/ * FROM t1 WHERE biz_day BETWEEN 20200101 AND 20200201; +-------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------------------------------------------------------------------------------------+ | ====================================================== | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | ------------------------------------------------------ | | |0 |COLUMN TABLE FULL SCAN|t1 |1 |3 | | | ====================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.biz_day]), filter([t1.biz_day >= demote_cast(20200101, VARCHAR(10))], [t1.biz_day <= demote_cast(20200201, VARCHAR(10))]), rowset=16 | | access([t1.biz_day]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false,false], | | range_key([t1.__pk_increment]), range(MIN ; MAX)always true | +-------------------------------------------------------------------------------------------------------------------------------------------------------+ 11 rows in set (0.004 sec)- Query plan: After setting
non_standard_comparison_leveltorange, the query optimizer converts integer constants to string type before comparison. - Filter condition:
filter([t1.biz_day >= demote_cast(20200101, VARCHAR(10))], [t1.biz_day <= demote_cast(20200201, VARCHAR(10))])converts the integer constant to a string before performing a range comparison. After non-standard comparison, it follows lexicographical order. For example,'202002010'is within the lexicographical range['20200101', '20200201'], but not within the numerical range[20200101, 20200201].
- Query plan: After setting
Other related conversion rules
The conversion methods from string to floating-point number and from integer to floating-point number are not necessarily the same. The result may be affected by factors such as the compiler version. One way to avoid such issues is to use
CAST()to prevent the value from being implicitly converted to a floating-point number.Implicit conversion of numeric or temporal values to strings produces a value with the character set and collation determined by the
character_set_connectionandcollation_connectionsystem variables.This means that such conversions generate non-binary strings (
CHAR,VARCHAR, orLONGTEXTvalues). If the connection character set is set to binary, the conversion result is a binary string (BINARY,VARBINARY, orLONGBLOBvalue).Type conversions for integer expression evaluation are slightly different. For example, when you use an integer expression as part of a CREATE TABLE statement, the resulting new table will contain columns of type
INTorBIGINT, depending on the result type of the expression:CREATE TABLE t SELECT integer_expr;If the maximum length of an expression is not suitable for
INT, useBIGINT. However, you can force the use ofBIGINTinstead ofINTby using an expression long enough:obclient> CREATE TABLE t SELECT 000000000000000000000; Query OK, 1 row affected
Rules for implicit data type conversion
When data type conversion is meaningful, OceanBase AI Database automatically converts a value from one data type to another.
The following table describes the implicit conversion matrix for all data types. You do not need to consider the direction or context of conversion.
Data type |
BOOL |
INT |
SMALLINT |
MEDIUMINT |
BIGINT |
SERIAL |
DECIMAL |
NUMERIC |
FLOAT |
DOUBLE |
BIT |
DATETIME |
TIMESTAMP |
DATE |
TIME |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| YEAR | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
| CHAR | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
| VARCHAR | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
| BINARY | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
| VARBINARY | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
| TEXT | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
| TINYBLOB | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
| MEDIUMBLOB | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
| LONGBLOB | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
| TINYTEXT | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
| MEDIUMTEXT | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
| LONGTEXT | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
| ENUM | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
| SET | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
| BLOB | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes |
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)