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.

There are three types of data type conversion differences: implicit conversion, explicit conversion, and UNION/CASE.

Differences in double colon conversion:

In GaussDB, if you use double colons to convert input parameters of a function to another type, the result may be unexpected. In MySQL, double colons do not take effect.

Example:

m_db=# SELECT POW("12"::VARBINARY,"12"::VARBINARY);
ERROR:  value out of range: overflow
CONTEXT:  referenced column: pow

varbinary col
m_db=# CREATE TABLE test_varbinary (
        A VARBINARY(10)
);
m_db=# INSERT INTO test_varbinary VALUES ('12');
m_db=# SELECT POW(A, A) FROM test_varbinary;
      pow      
---------------
 8916100448256
(1 row)

Differences in implicit type conversion:

In GaussDB, the conversion rules from small types to small types are used. In MySQL, the conversion rules from small types to large types and from large types to small types are used.
Due to data type differences, some output formats of implicit conversion in GaussDB are inconsistent.

During implicit conversion from the BIT data type to the character data type and binary data type in GaussDB, some output behaviors are inconsistent. GaussDB outputs a hexadecimal character string, and MySQL escapes the character string based on the ASCII code table. If the character string cannot be escaped, the output is empty.

Example:

m_db=# CREATE TABLE bit_storage (
	VS_COL1 BIT(4),
	VS_COL2 BIT(4),
	VS_COL3 BIT(4),
	VS_COL4 BIT(4),
	VS_COL5 BIT(4),
	VS_COL6 BIT(4),
	VS_COL7 BIT(4),
	VS_COL8 BIT(4)
) DISTRIBUTE BY REPLICATION;
m_db=# CREATE TABLE string_storage (
	VS_COL1 BLOB,
	VS_COL2 TINYBLOB,
	VS_COL3 MEDIUMBLOB,
	VS_COL4 LONGBLOB,
	VS_COL5 TEXT,
	VS_COL6 TINYTEXT,
	VS_COL7 MEDIUMTEXT,
	VS_COL8 LONGTEXT
) DISTRIBUTE BY REPLICATION;
m_db=# INSERT INTO bit_storage VALUES(B'101', B'101', B'101', B'101', B'101', B'101', B'101', B'101');
m_db=# INSERT INTO string_storage SELECT * FROM bit_storage;
m_db=# SELECT * FROM string_storage;
 VS_COL1 | VS_COL2 | VS_COL3 | VS_COL4 | VS_COL5 | VS_COL6 | VS_COL7 | VS_COL8 
---------+---------+---------+---------+---------+---------+---------+---------
 \x05    | \x05    | \x05    | \x05    | \x05    | \x05    | \x05    | \x05
(1 row)
m_db=# DROP TABLE bit_storage, string_storage;

mysql> CREATE TABLE bit_storage (
	VS_COL1 BIT(4),
	VS_COL2 BIT(4),
	VS_COL3 BIT(4),
	VS_COL4 BIT(4),
	VS_COL5 BIT(4),
	VS_COL6 BIT(4),
	VS_COL7 BIT(4),
	VS_COL8 BIT(4)
);
mysql> CREATE TABLE bit_storage (
	VS_COL1 BIT(4),
	VS_COL2 BIT(4),
	VS_COL3 BIT(4),
	VS_COL4 BIT(4),
	VS_COL5 BIT(4),
	VS_COL6 BIT(4),
	VS_COL7 BIT(4),
	VS_COL8 BIT(4)
);
mysql> INSERT INTO bit_storage VALUES(B'101', B'101', B'101', B'101', B'101', B'101', B'101', B'101');
mysql> INSERT INTO string_storage SELECT * FROM bit_storage;
mysql> SELECT * FROM string_storage;
+---------+---------+---------+---------+---------+---------+---------+---------+
| VS_COL1 | VS_COL2 | VS_COL3 | VS_COL4 | VS_COL5 | VS_COL6 | VS_COL7 | VS_COL8 |
+---------+---------+---------+---------+---------+---------+---------+---------+
|        |        |        |        |        |        |        |        |
+---------+---------+---------+---------+---------+---------+---------+---------+
1 row in set (0.00 sec)
mysql> DROP TABLE bit_storage, string_storage;

When a binary or hexadecimal character string with 0x00 is inserted into the binary data type, GaussDB inserts part of the string and truncates the characters following 0x00. MySQL can insert the entire string.

Example:

m_db=# CREATE TABLE blob_storage (
        A BLOB
) DISTRIBUTE BY REPLICATION;
m_db=# INSERT INTO blob_storage VALUES (0xBB00BB);
m_db=# SELECT hex(A) FROM blob_storage;
 hex 
-----
 BB
(1 row)
m_db=# DROP TABLE blob_storage;

mysql> CREATE TABLE blob_storage (
        A BLOB
);
mysql> INSERT INTO blob_storage VALUES (0xBB00BB);
mysql> SELECT hex(A) FROM blob_storage;
+--------+
| hex(a) |
+--------+
| BB00BB |
+--------+
1 row in set (0.01 sec)
mysql> DROP TABLE blob_storage;

When a binary or hexadecimal string with 0x00 in the middle is inserted into the string data type, GaussDB inserts part of the string and truncates the characters following 0x00. In MySQL, the string cannot be inserted in strict mode, and an empty string is inserted in loose mode.

Example:

m_db=# CREATE TABLE text_storage (
        A TEXT
);
m_db=# INSERT INTO text_storage VALUES (b'101110110000000010111011');
m_db=# SELECT hex(A) FROM text_storage;
 hex 
-----
 BB
(1 row)
m_db=# DROP TABLE text_storage;

mysql> CREATE TABLE text_storage (
        A TEXT
);
mysql> INSERT INTO text_storage VALUES (b'101110110000000010111011');
ERROR 1366 (HY000): Incorrect string value: '\xBB\x00\xBB' for column 'A' at row 1
mysql> SELECT hex(A) FROM text_storage;
Empty set (0.00 sec)
mysql> SET SQL_MODE='';
mysql> INSERT INTO text_storage VALUES (b'101110110000000010111011');
mysql> SELECT hex(A) FROM text_storage;
+--------+
| hex(A) |
+--------+
|        |
+--------+
1 row in set (0.01 sec)
mysql> DROP TABLE text_storage;

The WHERE clause contains only common character strings. GaussDB returns TRUE for 't', 'true', 'yes', 'y', and 'on', returns FALSE for 'no', 'f', 'off', 'false', and 'n', and reports an error for other character strings. MySQL determines whether to return TRUE or FALSE by converting a character string to an INT1 value.

Example:

m_db=# CREATE TABLE test_where (
        A INT
);
m_db=# INSERT INTO test_where VALUES (1);
m_db=# SELECT * FROM test_where WHERE '111';
ERROR:  invalid input syntax for type boolean: "111"
LINE 1: select * from test_where where '111';
m_db=# DROP TABLE test_where;

mysql> CREATE TABLE test_where (
        A INT
);
mysql> INSERT INTO test_where VALUES (1);
mysql> SELECT * FROM test_where WHERE '111';
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.01 sec)
mysql> DROP TABLE test_where;

When converting strings of the YEAR type to integers, MySQL uses scientific notation, but GaussDB does not support scientific notation and truncates the strings.

Example:

m_db=# CREATE TABLE test_year (
        A YEAR
);
m_db=# SET sql_mode = '';
m_db=# INSERT INTO test_year VALUES ('2E3x');
WARNING:  Data truncated for column.
LINE 1: insert into t1 values ('2E3x');
                               ^
CONTEXT:  referenced column: a
m_db=# SELECT * FROM test_year ORDER BY A;
  a   
------
 2002
(1 row)
m_db=# DROP TABLE test_year;

mysql> CREATE TABLE test_year (
        A YEAR
);
mysql> INSERT INTO test_year VALUES ('2E3x');
mysql> SELECT * FROM test_year ORDER BY A;
+------+
| a    |
+------+
| 2000 |
+------+
1 row in set (0.01 sec)
mysql> DROP TABLE test_year;

Differences in explicit type conversion:

In GaussDB, the conversion rules for each target type are used. In MySQL, C++ polymorphic overloading functions are used, causing inconsistent behavior in nesting scenarios.

Example:

m_db=# SELECT CAST(GREATEST(date'2023-01-01','2023-01-01') AS SIGNED);
WARNING:  Truncated incorrect INTEGER value: '2023-01-01'
CONTEXT:  referenced column: cast
 cast 
------
 2023
(1 row)

mysql> SELECT CAST(GREATEST(date'2023-01-01','2023-01-01') AS SIGNED);
+---------------------------------------------------------+
| CAST(GREATEST(date'2023-01-01','2023-01-01') AS SIGNED) |
+---------------------------------------------------------+
|                                                20230101 |
+---------------------------------------------------------+

Differences between UNION, CASE, and related structures:

In MySQL, POLYGON+NULL, POINT+NULL, and POLYGON+POINT return the GEOMETRY type. They are not involved in GaussDB and considered as errors.
The SET and ENUM types are not supported currently and are considered as errors.
When the constant type is aggregated with other types, the precision of the output type is the precision of other types. For example, the precision of the result of "select "helloworld" union select p from t;" is the precision of attribute p.
When fixed-point constants and types without precision constraints (non-string types such as int, bool, and year, and the type of the aggregation result is the fixed-point type) are aggregated, the precision constraint is output based on the default precision 31 of fixed-point numbers.
Differences in merge rules:
In MySQL 5.7, if YEAR is aggregated with TINYINT, INT, MEDIUMINT, BIGINT, or BOOL, the result is of the type with UNSIGNED. In GaussDB, it is of the type without UNSIGNED. In MySQL, if BIT is aggregated with a numeric type such as INT, NUMERIC, FLOAT, or DOUBLE, the result type is VARBINARY. In GaussDB, the result type is NUMERIC for aggregation between BIT and INT or NUMERIC, DOUBLE for aggregation between BIT and FLOAT or DOUBLE, and UINT8 for aggregation between BIT and unsigned integers.
In MySQL, BINARY and CHAR use different padding characters. BINARY is padded with '\0', and CHAR is padded with spaces. In GaussDB, BINARY and CHAR are padded with spaces.

Parent topic: Data Types

Previous topic: Attributes Supported by Data Types

Next topic: System Functions