On this page

Differences in Implicit Type Conversion
Differences in Explicit Type Conversion
Differences Between UNION, CASE, and Related Structures
Differences in Double Colon Conversion
Differences in Decimal Types

Show all

Help Center/ GaussDB/ MySQL Compatibility(Centralized)/ MySQL Compatibility in M-Compatible Mode/ Data Types/ Data Type Conversion

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.
When the collation of a common character string is BINARY, the character string is converted to the corresponding binary type (for example, TEXT is converted to BLOB, and VARCHAR is converted to VARBINARY).

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

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 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;

When CREATE TABLE AS is used with UNION, GaussDB does not distinguish the sequence of the left and right subnodes, but MySQL distinguishes the sequence of the left and right subnodes. If the left and right subnodes are exchanged, the results are different.

Example:

m_db=# CREATE TABLE test2(
	F1 FLOAT,
	I1 TINYINT,
	I2 SMALLINT,
	DTT1 DATETIME(6),
	DEC3 DECIMAL(32, 15),
	JS1 JSON,
	D2 DOUBLE,
	CH1 CHAR(255),
	D3 DOUBLE,
	TX1 TINYTEXT
);
m_db=# CREATE TABLE test1 SELECT DISTINCT concat((F1 + I1 - DTT1) * DEC3 % D2 / CH1) a from test2 UNION ALL SELECT sqrt((DEC3 + DTT1 - JS1) * D3 / - TX1 % I2) FROM test2;
m_db=# DESC test1;
 Field | Type | Null | Key | Default | Extra 
-------+------+------+-----+---------+-------
 a     | text | YES  |     |         | 
(1 row)

m_db=# CREATE TABLE test3 SELECT DISTINCT sqrt((DEC3 + DTT1 - JS1) * D3 / - TX1 % I2) a from test2 UNION ALL SELECT concat((F1 + I1 - DTT1) * DEC3 % D2 / CH1) FROM test2;
m_db=# DESC test3;
 Field | Type | Null | Key | Default | Extra 
-------+------+------+-----+---------+-------
 a     | text | YES  |     |         | 
(1 row)

m_db=# DROP TABLE test1, test2, test3;

mysql> CREATE TABLE test2(
	F1 FLOAT,
	I1 TINYINT,
	I2 SMALLINT,
	DTT1 DATETIME(6),
	DEC3 DECIMAL(32, 15),
	JS1 JSON,
	D2 DOUBLE,
	CH1 CHAR(255),
	D3 DOUBLE,
	TX1 TINYTEXT
);
mysql> CREATE TABLE test1 SELECT DISTINCT concat((F1 + I1 - DTT1) * DEC3 % D2 / CH1) a from test2 UNION ALL SELECT sqrt((DEC3 + DTT1 - JS1) * D3 / - TX1 % I2) FROM test2;
mysql> DESC test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | varchar(53) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> CREATE TABLE test3 SELECT DISTINCT sqrt((DEC3 + DTT1 - JS1) * D3 / - TX1 % I2) a from test2 UNION ALL SELECT concat((F1 + I1 - DTT1) * DEC3 % D2 / CH1) FROM test2;
mysql> DESC test3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | varchar(23) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> DROP TABLE test1, test2, test3;

In the function nesting scenarios in GaussDB, if aggregate functions (such as max, min, sum, and avg) contain non-numeric characters in the string type, the characters of this type are truncated or set to zeros during implicit conversion to the numeric type. If operator comparison and HAVING comparison are also involved, GaussDB converts types and generates alarms in a unified manner, but MySQL may not generate alarms in the same scenarios.

Example:

m_db=# SET m_format_behavior_compat_options= 'enable_precision_decimal';
SET
m_db=# SELECT max(c4) <> 0 FROM ((SELECT 2.22 id, '2006-04-27 20:19:02.132' c4)) tb_1;
WARNING:  Truncated incorrect double value: '2006-04-27 20:19:02.132'
 ?column?
----------
 t
(1 row)

m_db=# SELECT sum(c4) <> 0 FROM ((SELECT 2.22 id, '2006-04-27 20:19:02.132' c4)) tb_1;
WARNING:  Truncated incorrect double value: '2006-04-27 20:19:02.132'
 ?column?
----------
 t
(1 row)

m_db=# SELECT (SELECT max(c4) f5 FROM ((SELECT 2.22 id, '2006-04-27 20:19:08.132' c4) UNION all (SELECT 2.22 id, '1985-09-01 07:59:59' c4)) tb_1
m_db(# WHERE exists (SELECT max(c4) FROM ((SELECT 2.22 id, '2006-04-27 20:19:08.132' c4) UNION all (SELECT 2.22 id, '1985-09-01 07:59:59' c4)) tb_2)
m_db(# GROUP BY id WITH rollup HAVING f5<>0 LIMIT 0,1) + INTERVAL '33.22' SECOND_MICROSECOND col5;
WARNING:  Truncated incorrect double value: '2006-04-27 20:19:08.132'
CONTEXT:  referenced column: col5
            col5
----------------------------
 2006-04-27 20:19:41.352000
(1 row)

m_db=# SELECT (SELECT sum(c4) f5 FROM ((SELECT 2.22 id, '2006-04-27 20:19:08.132' c4) UNION all (SELECT 2.22 id, '1985-09-01 07:59:59' c4)) tb_1
m_db(# WHERE exists (SELECT sum(c4) FROM ((select 2.22 id, '2006-04-27 20:19:08.132' c4) UNION all (select 2.22 id, '1985-09-01 07:59:59' c4)) tb_2)
m_db(# GROUP BY id WITH rollup HAVING f5<>0 LIMIT 0,1) + INTERVAL '33.22' SECOND_MICROSECOND col5;
WARNING:  Truncated incorrect double value: '2006-04-27 20:19:08.132'
CONTEXT:  referenced column: col5
WARNING:  Truncated incorrect double value: '1985-09-01 07:59:59'
CONTEXT:  referenced column: col5
WARNING:  Truncated incorrect double value: '2006-04-27 20:19:08.132'
CONTEXT:  referenced column: col5
WARNING:  Truncated incorrect double value: '2006-04-27 20:19:08.132'
CONTEXT:  referenced column: col5
WARNING:  Truncated incorrect double value: '1985-09-01 07:59:59'
CONTEXT:  referenced column: col5
WARNING:  Truncated incorrect double value: '1985-09-01 07:59:59'
CONTEXT:  referenced column: col5
WARNING:  Incorrect datetime value: '3991'
CONTEXT:  referenced column: col5
 col5
------

(1 row)

mysql> SELECT max(c4) <> 0 FROM ((SELECT 2.22 id, '2006-04-27 20:19:02.132' c4)) tb_1;
+--------------+
| max(c4) <> 0 |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT sum(c4) <> 0 FROM ((SELECT 2.22 id, '2006-04-27 20:19:02.132' c4)) tb_1;
+--------------+
| sum(c4) <> 0 |
+--------------+
|            1 |
+--------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW warnings;
+---------+------+-------------------------------------------------------------+
| Level   | Code | Message                                                     |
+---------+------+-------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '2006-04-27 20:19:02.132' |
+---------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT (SELECT max(c4) f5 FROM ((SELECT 2.22 id, '2006-04-27 20:19:08.132' c4) UNION all (select 2.22 id, '1985-09-01 07:59:59' c4)) tb_1
    -> WHERE exists (SELECT max(c4) FROM ((SELECT 2.22 id, '2006-04-27 20:19:08.132' c4) UNION all (SELECT 2.22 id, '1985-09-01 07:59:59' c4)) tb_2)
    -> GROUP BY id WITH rollup HAVING f5<>0 limit 0,1) + INTERVAL '33.22' SECOND_MICROSECOND col5;
+----------------------------+
| col5                       |
+----------------------------+
| 2006-04-27 20:19:41.352000 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT (SELECT sum(c4) f5 FROM ((SELECT 2.22 id, '2006-04-27 20:19:08.132' c4) UNION all (SELECT 2.22 id, '1985-09-01 07:59:59' c4)) tb_1
    -> WHERE exists (SELECT sum(c4) FROM ((SELECT 2.22 id, '2006-04-27 20:19:08.132' c4) UNION all (select 2.22 id, '1985-09-01 07:59:59' c4)) tb_2)
    -> GROUP BY id WITH rollup HAVING f5<>0 LIMIT 0,1) + INTERVAL '33.22' SECOND_MICROSECOND col5;
+------+
| col5 |
+------+
| NULL |
+------+
1 row in set, 7 warnings (0.01 sec)

mysql> SHOW warnings;
+---------+------+-------------------------------------------------------------+
| Level   | Code | Message                                                     |
+---------+------+-------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '2006-04-27 20:19:08.132' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '1985-09-01 07:59:59'     |
| Warning | 1292 | Truncated incorrect DOUBLE value: '2006-04-27 20:19:08.132' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '2006-04-27 20:19:08.132' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '1985-09-01 07:59:59'     |
| Warning | 1292 | Truncated incorrect DOUBLE value: '1985-09-01 07:59:59'     |
| Warning | 1292 | Incorrect datetime value: '3991'                            |
+---------+------+-------------------------------------------------------------+
7 rows in set (0.00 sec)

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 |
+---------------------------------------------------------+

In GaussDB, the BLOB, TINYBLOB, MEDIUMBLOB, LONGBLOB, BINARY, VARBINARY, BIT, and YEAR types are explicitly converted to the JSON type. The result is different from that in MySQL.

Example:

m_db=# CREATE TABLE test_blob (c1 BLOB, c2 TINYBLOB, c3 MEDIUMBLOB, c4 LONGBLOB, c5 BINARY(32), c6 VARBINARY(100), c7 BIT(64), c8 YEAR);
CREATE TABLE
m_db=# INSERT INTO test_blob VALUES('[1, "json"]', 'true', 'abc', '{"jsnid": 1, "tag": "ab"}', '[1, "json"]', '{"jsnid": 1, "tag": "ab"}', '20', '2020');
INSERT 0 1
m_db=# SELECT CAST(c1 AS JSON), CAST(c2 AS JSON), CAST(c3 AS JSON), CAST(c4 AS JSON), CAST(c5 AS JSON), CAST(c6 AS JSON), CAST(c7 AS JSON), CAST(c8 AS JSON) FROM test_blob;
      CAST       |  CAST  | CAST  |               CAST                |                 CAST                 |               CAST                | CAST |  CAST  
-----------------+--------+-------+-----------------------------------+--------------------------------------+-----------------------------------+------+--------
 "[1, \"json\"]" | "true" | "abc" | "{\"jsnid\": 1, \"tag\": \"ab\"}" | "[1, \"json\"]                     " | "{\"jsnid\": 1, \"tag\": \"ab\"}" | "20" | "2020"
(1 row)

mysql> CREATE TABLE test_blob (c1 BLOB, c2 TINYBLOB, c3 MEDIUMBLOB, c4 LONGBLOB, c5 BINARY(32), c6 VARBINARY(100), c7 BIT(64), c8 YEAR);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO test_blob VALUES('[1, "json"]', 'true', 'abc', '{"jsnid": 1, "tag": "ab"}', '[1, "json"]', '{"jsnid": 1, "tag": "ab"}', '20', '2020');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT CAST(c1 AS JSON), CAST(c2 AS JSON), CAST(c3 AS JSON), CAST(c4 AS JSON), CAST(c5 AS JSON), CAST(c6 AS JSON), CAST(c7 AS JSON), CAST(c8 AS JSON) FROM test_blob;
+-----------------------------------+---------------------------+-----------------------+-------------------------------------------------------+---------------------------------------------------------------+------------------------------------------------------+------------------------------+--------------------------+
| CAST(c1 AS JSON)                  | CAST(c2 AS JSON)          | CAST(c3 AS JSON)      | CAST(c4 AS JSON)                                      | CAST(c5 AS JSON)                                              | CAST(c6 AS JSON)                                     | CAST(c7 AS JSON)             | CAST(c8 AS JSON)         |
+-----------------------------------+---------------------------+-----------------------+-------------------------------------------------------+---------------------------------------------------------------+------------------------------------------------------+------------------------------+--------------------------+
| "base64:type252:WzEsICJqc29uIl0=" | "base64:type249:dHJ1ZQ==" | "base64:type250:YWJj" | "base64:type251:eyJqc25pZCI6IDEsICJ0YWciOiAiYWIifQ==" | "base64:type254:WzEsICJqc29uIl0AAAAAAAAAAAAAAAAAAAAAAAAAAAA=" | "base64:type15:eyJqc25pZCI6IDEsICJ0YWciOiAiYWIifQ==" | "base64:type16:AAAAAAAAMjA=" | "base64:type13:MjAyMA==" |
+-----------------------------------+---------------------------+-----------------------+-------------------------------------------------------+---------------------------------------------------------------+------------------------------------------------------+------------------------------+--------------------------+
1 row in set (0.00 sec)

When the converted JSON data type is used for precision calculation, the precision of GaussDB is the same as that of the JSON table, which is different from that of MySQL 5.7 but the same as that of MySQL 8.0.

Example:

test=# drop table tt01;
DROP TABLE
test=# create table tt01 as select -cast('98.7654321' as json) as c1;
INSERT 0 1
test=# desc tt01;
 Field |  Type  | Null | Key | Default | Extra 
-------+--------+------+-----+---------+-------
 c1    | double | YES  |     |         | 
(1 row)

test=# select * from tt01;
     c1      
-------------
 -98.7654321
(1 row)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.7.44-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> drop table tt01;
Query OK, 0 rows affected (0.02 sec)

mysql> create table tt01 as select -cast('98.7654321' as json) as c1;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc tt01;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| c1    | double(17,0) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from tt01;
+------+
| c1   |
+------+
|  -99 |
+------+
1 row in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 8.0.36-debug |
+--------------+
1 row in set (0.00 sec)

mysql> drop table tt01;
Query OK, 0 rows affected (0.05 sec)

mysql> create table tt01 as select -cast('98.7654321' as json) as c1;
Query OK, 1 row affected (0.12 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc tt01;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| c1    | double | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> select * from tt01;
+-------------+
| c1          |
+-------------+
| -98.7654321 |
+-------------+
1 row in set (0.00 sec)

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.
For UNION or UNION ALL that combines the JSON and binary types (BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB), the LONGBLOB type is returned in MySQL and the JSON type is returned in GaussDB. In addition, binary types (BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB) can be implicitly converted to JSON.
If m_format_behavior_compat_options is not set to enable_precision_decimal, 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.
If m_format_behavior_compat_options is not set to enable_precision_decimal, when fixed-point constants and types without precision constraints (non-string types such as int, bool, and year, and the fixed-point type of aggregation result type) are aggregated, the precision constraint is output based on the default precision 31 of fixed-point numbers.
Differences in merge rules:
MySQL 5.7 has some improper type derivation. For example, the VARBINARY type is derived from the BIT type and integer/YEAR type, and the UNSIGNED type is derived from the UNSIGNED type and non-UNSIGNED type. In addition, the aggregation results of CASE WHEN and UNION are different. If the type derivation result is too small, data overflow may occur. The preceding issues have been resolved in MySQL 8.0. Therefore, the merge rule in MySQL 8.0 prevails.
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.

In the precision transfer scenario, when the CASE WHEN statement is used, type conversion and precision recalculation are performed. As a result, trailing zeros may be inconsistent with those in the output result of the CASE clause.

More trailing zeros: The CASE node calculates the precision of the CASE node based on the precision of the CASE clause. If the precision of the THEN clause is lower than that of the CASE node, zeros are added to the end of the CASE node.
Less trailing zeros: When multiple layers of CASE WHEN are nested, only the precision of the inner CASE is retained after the inner CASE performs type conversion. The outer CASE cannot obtain the precision information of the THEN clause. Therefore, the outer CASE performs type conversion based on the precision calculated according to that of the inner CASE. When the outer CASE clause is converted, if the precision of the inner CASE clause is less than that of the THEN clause, there will be less trailing zeros.

Example:

-- Trailing zeros

-- More trailing zeros
m_db=# SELECT 15.6 AS result;
 result 
--------
   15.6
(1 row)

m_db=# SELECT CASE WHEN 1 < 2 THEN 15.6 ELSE  23.578 END AS result;
 result 
--------
 15.600
(1 row)

m_db=# SELECT  greatest(12, 3.4, 15.6) AS result;
 result 
--------
   15.6
(1 row)

m_db=# SELECT CASE WHEN 1 < 2 THEN greatest(12, 3.4, 15.6) ELSE greatest(123.4, 23.578, 36) END AS result;
 result 
--------
 15.600
(1 row)

-- Less trailing zeros
m_db=# create table t1 as select (false/-timestamp '2008-12-31 23:59:59.678') as result;
INSERT 0  1
m_db=# desc t1;
 Field  |    Type     | Null | Key | Default | Extra 
--------+-------------+------+-----+---------+-------
 result | double(8,7) | YES  |     |         | 
(1 row)

m_db=# select (false/-timestamp '2008-12-31 23:59:59.678') as result;
   result   
------------
 -0.0000000
(1 row)

m_db=# create table t1 as select (case when 1<2 then false/-timestamp '2008-12-31 23:59:59.678' else 0016.11e3/'22.2' end) as result;
INSERT 0 1
m_db=# desc t1;
 Field  |  Type  | Null | Key | Default | Extra 
--------+--------+------+-----+---------+-------
 result | double | YES  |     |         | 
(1 row)

m_db=# select (case when 1<2 then false/-timestamp '2008-12-31 23:59:59.678' else 0016.11e3/'22.2' end) as result;
 result 
--------
     -0
(1 row)

m_db=# drop table t1;
DROP TABLE
m_db=# create table t1 as select (case when 1+1=2 then case when 1<2 then false/-timestamp '2008-12-31 23:59:59.678' else 0016.11e3/'22.2' end else 'test' end) as result;
INSERT 0 1
m_db=# desc t1;
 Field  |    Type     | Null | Key | Default | Extra 
--------+-------------+------+-----+---------+-------
 result | varchar(23) | YES  |     |         | 
(1 row)

m_db=# select (case when 1+1=2 then case when 1<2 then false/-timestamp '2008-12-31 23:59:59.678' else 0016.11e3/'22.2' end else 'test' end) as result;
 result 
--------
 -0
(1 row)

When the precision transfer parameter is enabled, set operations (UNION, MINUS, EXCEPT, and INTERSECT) are used. If the fields queried by the query statements involved in set operations are functions and expressions instead of directly using fields in the table, if the data type of the query result is INT or INT UNSIGNED, the return data type is different. In MySQL, the returned data type is BIGINT or BIGINT UNSIGNED. In GaussDB, the returned data type is INT/INT UNSIGNED.

-- Execution result in GaussDB
m_db=# SET m_format_behavior_compat_options='select_column_name,enable_precision_decimal';
SET
m_db=# DROP TABLE IF EXISTS t1,t2,ctas1,ctas2;
DROP TABLE
m_db=# CREATE TABLE t1(a INT, b INT);
CREATE TABLE
m_db=# CREATE TABLE t2(c INT UNSIGNED, d INT UNSIGNED);
CREATE TABLE
m_db=# CREATE TABLE ctas1 AS (SELECT a, ABS(a) FROM t1) UNION (SELECT b, ABS(b) FROM t1);
INSERT 0 0
m_db=# DESC ctas1;
 Field  |    Type     | Null | Key | Default | Extra 
--------+-------------+------+-----+---------+-------
 a      | integer(11) | YES  |     |         | 
 ABS(a) | integer(11) | YES  |     |         | 
(2 rows)

m_db=# CREATE TABLE ctas2 AS (SELECT c, ABS(c) FROM t2) UNION (SELECT d, ABS(d) FROM t2);
INSERT 0 0
m_db=# DESC ctas2;
 Field  |         Type         | Null | Key | Default | Extra 
--------+----------------------+------+-----+---------+-------
 c      | integer(11) unsigned | YES  |     |         | 
 ABS(c) | integer(11) unsigned | YES  |     |         | 
(2 rows)

m_db=# DROP TABLE IF EXISTS t1,t2,ctas1,ctas2;
DROP TABLE

-- Execution result in MySQL
mysql> DROP TABLE IF EXISTS t1,t2,ctas1,ctas2;
Query OK, 0 rows affected, 4 warnings (0.00 sec)

mysql> CREATE TABLE t1(a INT, b INT);
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE t2(c INT UNSIGNED, d INT UNSIGNED);
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE ctas1 AS (SELECT a, ABS(a) FROM t1) UNION (SELECT b, ABS(b) FROM t1);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC ctas1;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| a      | int(11)    | YES  |     | NULL    |       |
| ABS(a) | bigint(20) | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> CREATE TABLE ctas2 AS (SELECT c, ABS(c) FROM t2) UNION (SELECT d, ABS(d) FROM t2);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC ctas2;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| c      | int(11) unsigned    | YES  |     | NULL    |       |
| ABS(c) | bigint(20) unsigned | YES  |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> DROP TABLE IF EXISTS t1,t2,ctas1,ctas2;
Query OK, 0 rows affected (0.07 sec)

When precision transfer is enabled, the result in the CASE WHEN nesting scenario is different from that in MySQL. In MySQL, a type can be directly converted despite multiple layers. However, in GaussDB, the result precision is determined and the type is converted layer by layer. As a result, the decimal places or carry of the result may be inconsistent with that of MySQL.

-- GaussDB:
m_db=# SET m_format_behavior_compat_options='enable_precision_decimal';
SET
m_db=# SELECT (CASE WHEN 1+1=3 THEN 'test' ELSE CASE WHEN 1>2 THEN '-1.5'%06.6600e1 ELSE -TIME '10:10:10.456'%2.2 END END) RES;
         res
---------------------
 -1.8559999999974321
(1 row)

-- MySQL:
mysql> SELECT (CASE WHEN 1+1=3 THEN 'test' ELSE CASE WHEN 1>2 THEN '-1.5'%06.6600e1 ELSE -TIME '10:10:10.456'%2.2 END END) RES;
+--------+
| res    |
+--------+
| -1.856 |
+--------+
1 row in set (0.00 sec)

If operators of the int type (such as ~, &, |, <<, and >>) are nested in a CASE WHEN statement and the return type of the CASE WHEN statement is VARCHAR, truncation may occur in actual situations (you can determine whether truncation will occur by analyzing the original table data). In GaussDB, an error will be reported (a warning is reported when SELECT is used for query and an error is reported when a table is created). MySQL does not report an error. (If you want to CREATE TABLE in GaussDB, you can set sql_mode to disable the strict mode.)

-- GaussDB:
m_db=# create table t_base (num_var numeric(20, 10), time_var time(6));
CREATE TABLE
m_db=# insert into t_base values ('-2514.1441000000','12:10:10.125000'),('-417.2147000000',' 11:30:25.258000');
INSERT 0 2
m_db=# select (~(case when false then time_var else num_var end)) as res2 from t_base;
WARNING:  Truncated incorrect INTEGER value: '-2514.1441000000'
CONTEXT:  referenced column: res2
WARNING:  Truncated incorrect INTEGER value: '-417.2147000000'
CONTEXT:  referenced column: res2
 res2 
------
 2513
 416
(2 rows)
m_db=# create table t1 as select (~(case when false then time_var else num_var end)) as res2 from t_base;
ERROR:  Truncated incorrect INTEGER value: '-2514.1441000000'
CONTEXT:  referenced column: res2
m_db=# set sql_mode="";
SET
m_db=# create table t1 as select (~(case when false then time_var else num_var end)) as res2 from t_base;
WARNING:  Truncated incorrect INTEGER value: '-2514.1441000000'
CONTEXT:  referenced column: res2
WARNING:  Truncated incorrect INTEGER value: '-417.2147000000'
CONTEXT:  referenced column: res2
INSERT 0 2
m_db=# desc t1;
 Field |        Type         | Null | Key | Default | Extra 
-------+---------------------+------+-----+---------+-------
 res2  | bigint(21) unsigned | YES  |     |         | 
(1 row)

-- Mysql:
mysql> create table t_base (num_var numeric(20, 10), time_var time(6));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t_base values ('-2514.1441000000','12:10:10.125000'),('-417.2147000000',' 11:30:25.258000');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select (~(case when false then time_var else num_var end)) as res2 from t_base;
+------+
| res2 |
+------+
| 2513 |
|  416 |
+------+
2 rows in set (0.00 sec)
mysql> create table t1 as select (~(case when false then time_var else num_var end)) as res2 from t_base;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| res2  | bigint(21) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

When precision transfer is enabled, if constants are nested in CREATE VIEW AS SELECT CASE WHEN and SELECT CASE WHEN statements (including constant calculation and nesting functions with constants), the values in GaussDB are the same. In MySQL, some precision may be lost in SELECT CASE WHEN statements.

-- GaussDB:
m_db=# CREATE OR REPLACE VIEW test_view AS
m_db-# SELECT (CASE WHEN 1<2 THEN 3.33/4.46 ELSE 003.3630/002.2600 END) c1,(CASE WHEN 1>2 THEN IFNULL(null,3.363/2.2) ELSE NULLIF(3.33/4.46,3.363/2.2) END) c2;
CREATE VIEW
m_db=# SELECT * FROM test_view;
     c1     |    c2     
------------+-----------
 0.74663677 | 0.7466368
(1 row)
m_db=# SELECT (CASE WHEN 1<2 THEN 3.33/4.46 ELSE 003.3630/002.2600 END) c1,(CASE WHEN 1>2 THEN IFNULL(null,3.363/2.2) ELSE NULLIF(3.33/4.46,3.363/2.2) END) c2;
     c1     |    c2     
------------+-----------
 0.74663677 | 0.7466368
(1 row)

-- MySQL:
mysql> CREATE OR REPLACE VIEW test_view AS
    -> SELECT (CASE WHEN 1<2 THEN 3.33/4.46 ELSE 003.3630/002.2600 END) c1,(CASE WHEN 1>2 THEN IFNULL(null,3.363/2.2) ELSE NULLIF(3.33/4.46,3.363/2.2) END) c2;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test_view;
+------------+-----------+
| c1         | c2        |
+------------+-----------+
| 0.74663677 | 0.7466368 |
+------------+-----------+
1 row in set (0.00 sec)
mysql> SELECT (CASE WHEN 1<2 THEN 3.33/4.46 ELSE 003.3630/002.2600 END) c1,(CASE WHEN 1>2 THEN IFNULL(null,3.363/2.2) ELSE NULLIF(3.33/4.46,3.363/2.2) END) c2;
+----------+----------+
| c1       | c2       |
+----------+----------+
| 0.746637 | 0.746637 |
+----------+----------+
1 row in set (0.00 sec)

When precision transfer is enabled, an M-compatible database supports table creation using the UNION/CASE WHEN statement. However, due to different architectures, the database does not ensure that all types of created tables are the same as those of MySQL 8.0. The scenarios where character strings and binary-related types are returned and some function nesting scenarios in MySQL are different from those in GaussDB.

-- GaussDB:
m_db=# CREATE TABLE IF NOT EXISTS testcase (id int, col_text1 tinytext, col_text2 text, col_blob1 tinyblob, col_blob2 blob, col_blob3 mediumblob, col_blob4 longblob);
CREATE TABLE
m_db=# CREATE TABLE t1 AS SELECT id,(CASE WHEN id=2 THEN col_text1 ELSE 'test' END) f35,  (CASE WHEN id=2 THEN col_text2 ELSE 'test' END) f36,(CASE WHEN id=2 THEN col_blob1 ELSE 'test' END) f41,  (CASE WHEN id=2 THEN col_blob2 ELSE 'test' END) f42,  (CASE WHEN id=2 THEN col_blob3 ELSE 'test' END) f43,  (CASE WHEN id=2 THEN col_blob4 ELSE 'test' END) f44 FROM testcase;
INSERT 0 0
m_db=# DESC t1;
Field |      Type      | Null | Key | Default | Extra
-------+----------------+------+-----+---------+-------
id    | integer(11)    | YES  |     |         |
f35   | varchar(255)   | YES  |     |         |
f36   | mediumtext     | YES  |     |         |
f41   | varbinary(255) | YES  |     |         |
f42   | blob           | YES  |     |         |
f43   | mediumblob     | YES  |     |         |
f44   | longblob       | YES  |     |         |
(7 rows)

m_db=# CREATE TABLE IF NOT EXISTS testtext1 (col10 text);
CREATE TABLE
m_db=# CREATE TABLE IF NOT EXISTS testtext2 (col10 text);
CREATE TABLE
m_db=# CREATE TABLE testtext AS (select * from testtext1) UNION (select * from testtext2);
CREATE TABLE
m_db=# desc testtext;
m_db=# 
 Field | Type | Null | Key | Default | Extra 
-------+------+------+-----+---------+-------
 col10 | text | YES  |     |         | 
(1 row)

m_db=# create table testchar as  select (select lcase(-6873.4354)) a, (select sec_to_time(-485769.567)) b union all select (select bin(-58768923.21321)), (select asin(-0.7237465));
INSERT 0 2
m_db=# desc testchar;
 Field |    Type     | Null | Key | Default | Extra 
-------+-------------+------+-----+---------+-------
 a     | text        | YES  |     |         | 
 b     | varchar(23) | YES  |     |         | 
(2 rows)

m_db=# CREATE TABLE test_func (col_text char(29));
CREATE TABLE
m_db=# CREATE TABLE test1 AS SELECT * FROM ( SELECT 
        GREATEST(2.22, col_text) f1, LEAST(2.22, col_text) f2,
        ADDDATE(col_text, INTERVAL '1.28.16.31' HOUR_MICROSECOND) f3,
        SUBDATE(col_text, INTERVAL '39.49.15' MINUTE_MICROSECOND) f4,
        DATE_SUB(col_text, INTERVAL '45' MICROSECOND) f5,
        DATE_ADD(col_text, INTERVAL '12.00.00.00.001' DAY_MICROSECOND) f6,
        ADDTIME(col_text, '8:20:20.3554') f7,
        SUBTIME(col_text, '8:20:20.3554') f8 from test_func) t1
UNION ALL 
        SELECT * FROM ( SELECT
        GREATEST(2.22, col_text) f1, LEAST(2.22, col_text) f2,
        ADDDATE(col_text, INTERVAL '1.28.16.31' HOUR_MICROSECOND) f3,
        SUBDATE(col_text, INTERVAL '39.49.15' MINUTE_MICROSECOND) f4,
        DATE_SUB(col_text, INTERVAL '45' MICROSECOND) f5,
        DATE_ADD(col_text, INTERVAL '12.00.00.00.001' DAY_MICROSECOND) f6,
        ADDTIME(col_text, '8:20:20.3554') f7,
        SUBTIME(col_text, '8:20:20.3554') f8 from test_func) t2;
INSERT 0 0
m_db=# DESC test1;
 Field |    Type     | Null | Key | Default | Extra 
-------+-------------+------+-----+---------+-------
 f1    | double      | YES  |     |         | 
 f2    | double      | YES  |     |         | 
 f3    | varchar(29) | YES  |     |         | 
 f4    | varchar(29) | YES  |     |         | 
 f5    | varchar(29) | YES  |     |         | 
 f6    | varchar(29) | YES  |     |         | 
 f7    | varchar(29) | YES  |     |         | 
 f8    | varchar(29) | YES  |     |         | 
(8 rows)

-- MySQL:
mysql> CREATE TABLE IF NOT EXISTS testcase (id int, col_text1 tinytext, col_text2 text, col_blob1 tinyblob, col_blob2 blob, col_blob3 mediumblob, col_blob4 longblob);
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE t1 AS SELECT id,(CASE WHEN id=2 THEN col_text1 ELSE 'test' END) f35,  (CASE WHEN id=2 THEN col_text2 ELSE 'test' END) f36,(CASE WHEN id=2 THEN col_blob1 ELSE 'test' END) f41,  (CASE WHEN id=2 THEN col_blob2 ELSE 'test' END) f42,  (CASE WHEN id=2 THEN col_blob3 else 'test' END) f43,  (CASE WHEN id=2 THEN col_blob4 ELSE 'test' END) f44 FROM testcase;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| f35   | longtext | YES  |     | NULL    |       |
| f36   | longtext | YES  |     | NULL    |       |
| f41   | longblob | YES  |     | NULL    |       |
| f42   | longblob | YES  |     | NULL    |       |
| f43   | longblob | YES  |     | NULL    |       |
| f44   | longblob | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS testtext1 (col10 text);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS testtext2 (col10 text);
Query OK, 0 rows affected (0.02 sec)

mysql>  CREATE TABLE testtext AS (select * from testtext1) UNION (select * from testtext2);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc testtext;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col10 | mediumtext | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create table testchar as  select (select lcase(-6873.4354)) a, (select sec_to_time(-485769.567)) b union all select (select bin(-58768923.21321)), (select asin(-0.7237465));
Query OK, 2 rows affected, 1 warning (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> desc testchar;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | varchar(21) | YES  |     | NULL    |       |
| b     | varchar(53) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> CREATE TABLE test_func (col_text char(29));
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE test1 AS SELECT * FROM ( SELECT 
    -> GREATEST(2.22, col_text) f1, LEAST(2.22, col_text) f2,
    -> ADDDATE(col_text, INTERVAL '1.28.16.31' HOUR_MICROSECOND) f3,
    -> SUBDATE(col_text, INTERVAL '39.49.15' MINUTE_MICROSECOND) f4,
    -> DATE_SUB(col_text, INTERVAL '45' MICROSECOND) f5,
    -> DATE_ADD(col_text, INTERVAL '12.00.00.00.001' DAY_MICROSECOND) f6,
    -> ADDTIME(col_text, '8:20:20.3554') f7,
    -> SUBTIME(col_text, '8:20:20.3554') f8 from test_func) t1
    -> UNION ALL 
    -> SELECT * FROM ( SELECT
    -> GREATEST(2.22, col_text) f1, LEAST(2.22, col_text) f2,
    -> ADDDATE(col_text, INTERVAL '1.28.16.31' HOUR_MICROSECOND) f3,
    -> SUBDATE(col_text, INTERVAL '39.49.15' MINUTE_MICROSECOND) f4,
    -> DATE_SUB(col_text, INTERVAL '45' MICROSECOND) f5,
    -> DATE_ADD(col_text, INTERVAL '12.00.00.00.001' DAY_MICROSECOND) f6,
    -> ADDTIME(col_text, '8:20:20.3554') f7,
    -> SUBTIME(col_text, '8:20:20.3554') f8 from test_func) t2;
    -> SUBTIME(col_text, '8:20:20.3554') f8 from test_func) t1
    -> UNION ALL 
    -> SELECT * FROM ( SELECT
    -> GREATEST(2.22, col_text) f1, LEAST(2.22, col_text) f2,
    -> ADDDATE(col_text, INTERVAL '1.28.16.31' HOUR_MICROSECOND) f3,
    -> SUBDATE(col_text, INTERVAL '39.49.15' MINUTE_MICROSECOND) f4,
    -> DATE_SUB(col_text, INTERVAL '45' MICROSECOND) f5,
    -> DATE_ADD(col_text, INTERVAL '12.00.00.00.001' DAY_MICROSECOND) f6,
    -> ADDTIME(col_text, '8:20:20.3554') f7,
    -> SUBTIME(col_text, '8:20:20.3554') f8 from test_func) t2;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> DESC test1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| f1    | binary(23) | YES  |     | NULL    |       |
| f2    | binary(23) | YES  |     | NULL    |       |
| f3    | char(29)   | YES  |     | NULL    |       |
| f4    | char(29)   | YES  |     | NULL    |       |
| f5    | char(29)   | YES  |     | NULL    |       |
| f6    | char(29)   | YES  |     | NULL    |       |
| f7    | char(29)   | YES  |     | NULL    |       |
| f8    | char(29)   | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
8 rows in set (0.01 sec)

In the scenario where precision transfer is enabled, for the CREATE TABLE AS SELECT A % (CASE WHEN) statement, if A is of the DECIMAL type and the result of CASE WHEN is of the date type (DATE, TIME, or DATETIME), the two databases are different in the precision obtained by performing the modulo operation (%). The precision obtained by GaussDB is the same as that obtained by performing modulo operations on the decimal type and date type.

-- GaussDB: (decimal % date type case) and (numeric%date) have the same precision, that is, decimal(24,10).
m_db=# SET m_format_behavior_compat_options = 'enable_precision_decimal';
SET
m_db=# DROP TABLE IF EXISTS t1, t2;
DROP TABLE
m_db=# CREATE TABLE t1 (num_var numeric(20, 10), date_var date, time_var time(6), dt_var datetime(6));
CREATE TABLE
m_db=# CREATE TABLE t2 AS SELECT num_var % (CASE WHEN true THEN dt_var ELSE dt_var END) AS res1 FROM t1;
INSERT 0 0
m_db=# DESC t2;
 Field |      Type      | Null | Key | Default | Extra
-------+----------------+------+-----+---------+-------
 res1  | decimal(24,10) | YES  |     |         |
(1 row)

m_db=# DROP TABLE IF EXISTS t1, t2;
DROP TABLE
m_db=# CREATE TABLE t1 (num_var numeric(20, 10), date_var date, time_var time(6), dt_var datetime(6));
CREATE TABLE
m_db=# CREATE TABLE t2 AS SELECT num_var % dt_var AS RES1 from t1;
INSERT 0 0
m_db=# DESC t2;
 Field |      Type      | Null | Key | Default | Extra
-------+----------------+------+-----+---------+-------
 res1  | decimal(24,10) | YES  |     |         |
(1 row)

-- MySQL 5.7: The precision is different. The precision of (decimal % date type case) is decimal(65,10), and that of (numeric%date) is decimal(24,10).
mysql> DROP TABLE IF EXISTS t1, t2;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t1 (num_var numeric(20, 10), date_var date, time_var time(6), dt_var datetime(6));
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t2 AS SELECT num_var % (CASE WHEN true THEN dt_var ELSE dt_var END) AS res1 FROM t1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t2;

+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| res1  | decimal(65,10) | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> DROP TABLE IF EXISTS t1, t2;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t1 (num_var numeric(20, 10), date_var date, time_var time(6), dt_var datetime(6));
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t2 AS SELECT num_var % dt_var AS res1 FROM t1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t2;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| res1  | decimal(24,10) | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.00 sec)

-- MySQL 8.0: The precision of (decimal % date type case) and (numeric%date) is decimal(20,10).
mysql> DROP TABLE IF EXISTS t1, t2;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t1 (num_var numeric(20, 10), date_var date, time_var time(6), dt_var datetime(6));
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t2 AS SELECT num_var % (CASE WHEN true THEN dt_var ELSE dt_var END) AS res1 FROM t1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t2;

+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| res1  | decimal(20,10) | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> DROP TABLE IF EXISTS t1, t2;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t1 (num_var numeric(20, 10), date_var date, time_var time(6), dt_var datetime(6));

Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t2 AS SELECT num_var % dt_var AS res1 FROM t1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t2;
+-------+----------------+------+-----+---------+-------+
| Field | Type           | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| res1  | decimal(20,10) | YES  |     | NULL    |       |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.00 sec)

When precision transfer is enabled and UNION is used, if the query statement participates in set calculation, the queried column is a constant, and the query result data type is INT or DECIMAL, the returned precision is different. In MySQL 5.7, the returned precision is related to the left/right sequence of UNION. In MySQL 8.0 and GaussDB, they are irrelevant.

-- GaussDB:
m_db=# CREATE TABLE t1 AS (SELECT -23.45 c2) UNION ALL (SELECT -45.678 c2);
INSERT 0 2
m_db=# DESC t1;
Field |     Type     | Null | Key | Default | Extra
-------+--------------+------+-----+---------+-------
c2    | decimal(5,3) | YES  |     |         |        
(1 row)
m_db=# CREATE TABLE t2 AS (SELECT -45.678 c2) UNION ALL (SELECT -23.45 c2);
INSERT 0 2
m_db=# DESC t2;
Field |     Type     | Null | Key | Default | Extra
-------+--------------+------+-----+---------+-------
c2    | decimal(5,3) | YES  |     |         |        
(1 row)

-- Mysql5.7:
mysql> CREATE TABLE t1 AS (SELECT -23.45 c2) UNION ALL (SELECT -45.678 c2);
Query OK, 2 rows affected (2.28 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> DESC t1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| c2    | decimal(6,3) | NO   |     | 0.000   |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> CREATE TABLE t2 AS (SELECT -45.678 c2) UNION ALL (SELECT -23.45 c2);
Query OK, 2 rows affected (2.22 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> DESC t2;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| c2    | decimal(5,3) | NO   |     | 0.000   |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

-- Mysql8.0:
mysql> CREATE TABLE t1 AS (SELECT -23.45 c2) UNION ALL (SELECT -45.678 c2);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> DESC t1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| c2    | decimal(5,3) | NO   |     | 0.000   |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.03 sec)
mysql>  CREATE TABLE t2 AS (SELECT -45.678 c2) UNION ALL (SELECT -23.45 c2);   
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> DESC t2;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| c2    | decimal(5,3) | NO   |     | 0.000   |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.02 sec)

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 Decimal Types

In Create table... In the AS (select...) statement, if the decimal data type is used and there are 0s in the prefix, 0s are ignored in M-compatible mode, and the length calculation excludes 0s. In MySQL 5.7, the number of 0s in the prefix is added to the total length. In MySQL 8.0, despite the numbers of 0s in the prefix, only 1 is added to the total length.

m_db=# create table test as select 004.01 col1;
INSERT 0 1
m_db=# desc test;
 Field |     Type     | Null | Key | Default | Extra 
-------+--------------+------+-----+---------+-------
 col1  | decimal(3,2) | YES  |     |         | 
(1 row)

mysql 5.7
mysql> create table test as select 004.01 col1;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| col1  | decimal(5,2) | NO   |     | 0.00    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql 8.0
mysql> create table test as select 004.01 col1;
Query OK, 1 row affected (0.23 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> desc test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| col1  | decimal(4,2) | NO   |     | 0.00    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.01 sec)

Parent topic: Data Types

Previous topic: Attributes Supported by Data Types

Next topic: System Functions

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot