Data Type Conversion

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.
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, 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.

In a WHERE clause with only character strings, GaussDB identifies 't', 'true', 'y', 'yes', 'on', '1', and their uppercase counterpart as TRUE and a query result can be obtained. Conversely, 'f', 'false', 'n', 'no', 'off', and '0', including their uppercase counterparts, are identified as FALSE and a query result cannot be obtained. Errors are reported for other character strings. In MySQL, the query result can be obtained only when the first digit of a character string is not 0; otherwise, the query result cannot be obtained.

Example:

-- GaussDB
m_db=# CREATE TABLE test_where(a int);
CREATE TABLE

m_db=# INSERT INTO test_where VALUES(1);
INSERT 0 1

m_db=# SELECT * FROM test_where WHERE 't';
 a 
---
 1
(1 row)

m_db=# SELECT * FROM test_where WHERE '1';
 a 
---
 1
(1 row)

m_db=# SELECT * FROM test_where WHERE '1a';
ERROR:  invalid input syntax for type boolean: "1a"
LINE 1: SELECT * FROM test_where WHERE '1a';
                                       ^
m_db=# SELECT * FROM test_where WHERE 'f';
 a 
---
(0 rows)

m_db=# SELECT * FROM test_where WHERE '0';
 a 
---
(0 rows)

m_db=# DROP TABLE test_where;
DROP TABLE

-- MySQL
mysql> CREATE TABLE test_where(a int);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test_where VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test_where WHERE 't';
Empty set, 1 warning (0.00 sec)

mysql> SELECT * FROM test_where WHERE '1';
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test_where WHERE '1a';
+------+
| a    |
+------+
|    1 |
+------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT * FROM test_where WHERE 'f';
Empty set, 1 warning (0.00 sec)

mysql> SELECT * FROM test_where WHERE '0';
Empty set (0.00 sec)

mysql> DROP TABLE test_where;
Query OK, 0 rows affected (0.01 sec)

For a YEAR type table, if a character string contains 'e' or 'E', MySQL processes it using scientific notation, while GaussDB reports an error or truncates it.

Example:

-- GaussDB
m_db=# SET SQL_MODE='';
SET

m_db=# CREATE TABLE test_year(a year);
CREATE TABLE

m_db=# INSERT INTO test_year VALUES('2E3');
WARNING:  Data truncated for column.
LINE 1: INSERT INTO test_year VALUES('2E3');
                                     ^
CONTEXT:  referenced column: a
INSERT 0 1
m_db=# SELECT * FROM test_year;
  a   
------
 2002
(1 row)

m_db=# DROP TABLE test_year;
DROP TABLE

-- MySQL
mysql> CREATE TABLE test_year(a year);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test_year VALUES('2E3');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test_year;
+------+
| a    |
+------+
| 2000 |
+------+
1 row in set (0.00 sec)

mysql> DROP TABLE test_year;
Query OK, 0 rows affected (0.01 sec)

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:

-- GaussDB
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;
 ?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:  The double value '2006-04-27 20:19:02.132' is incorrect.
 ?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 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)

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 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;
WARNING:  The double value '2006-04-27 20:19:08.132' is incorrect.
CONTEXT:  referenced column: col5
WARNING:  The double value '1985-09-01 07:59:59' is incorrect.
CONTEXT:  referenced column: col5
WARNING:  The double value '2006-04-27 20:19:08.132' is incorrect.
CONTEXT:  referenced column: col5
WARNING:  The double value '2006-04-27 20:19:08.132' is incorrect.
CONTEXT:  referenced column: col5
WARNING:  The double value '1985-09-01 07:59:59' is incorrect.
CONTEXT:  referenced column: col5
WARNING:  The double value '1985-09-01 07:59:59' is incorrect.
CONTEXT:  referenced column: col5
WARNING:  Incorrect datetime value: '3991'
CONTEXT:  referenced column: col5
 col5 
------

(1 row)

-- MySQL
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 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), BIT, or YEAR, MySQL returns the LONGBLOB or LONGTEXT type while GaussDB returns the JSON type. In addition, binary types (BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB), BIT, or YEAR, 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, a warning is reported when SELECT is used for query and an error is reported when a table is created by CREATE. MySQL does not report an error in this case. (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, GaussDB supports table creation using the UNION/CASE WHEN statement. However, due to different architectures, GaussDB cannot 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> 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)

-- MySQL 5.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)

-- MySQL 8.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 ... AS (SELECT ...) statement, if data of the decimal type has 0s in the prefix, GaussDB ignores and excludes 0s during the length calculation. In MySQL 5.7, the number of 0s in the prefix is added to the total length. In MySQL 8.0, despite the number of 0s in the prefix, only 1 is added to the total length.

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