文档首页/ 云数据库 GaussDB/ 产品介绍/ 与MySQL兼容性说明/ 集中式版/ MySQL数据库兼容性M-Compatibility模式/ 数据类型/ 数据类型转换
更新时间：2024-11-11 GMT+08:00
查看PDF
分享

数据类型转换

不同的数据类型之间支持转换。有如下场景涉及到数据类型转换：

  • 操作符（比较操作符、运算操作符等）的操作数的数据类型不一致。常见于查询条件或者关联条件中的比较运算。
  • 函数调用时实参和形参的数据类型不一致。
  • DML语句要更新（包括INSERT、UPDATE、MERGE、REPLACE等）的目标列，数据的类型和列的定义类型不一致。
  • 显式的类型转换：CAST(expr AS datatype)，将expr表达式类型转换为datatype类型。
  • 集合运算（UNION、MINUS、EXCEPT、INTERSECT）确定最终投影列的目标数据类型后，各个SELECT查询的投影列的类型和目标数据类型不一致。
  • 其他表达式计算场景，根据不同表达式的数据类型， 来决定用于比较或者最终结果的目标数据类型。
  • 普通的字符串类型当字符序为BINARY时，将转换成对应的二进制类型（TEXT转换成BLOB，VARCHAR转换成VARBINARY等）。

数据类型转换差异点主要分为：隐式转换，显式转换、UNION/CASE、decimal类型。

隐式类型转换差异点

  • GaussDB中统一平铺成小类型到小类型的转换规则，MySQL中使用小类型转大类型，大类型转小类型的转换规则。
  • GaussDB中隐式转换因数据类型本身差异点，输出格式存在部分行为不一致。
  • GaussDB中的隐式转换，BIT数据类型到字符数据类型和二进制数据类型转换，输出存在部分行为不一致。GaussDB输出为十六进制，MySQL中根据ASCII码表转义，无法转义的输出为空。

    示例：

    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;
  • 中间带有0x00的二进制或十六进制字符串插入二进制数据类型时，GaussDB部分插入，0x00后的字符会截断，MySQL可以完整插入。
    示例： 
    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;
  • 中间带有0x00的二进制或十六进制字符串插入字符串数据类型时，GaussDB部分插入，0x00后的字符会截断，MySQL严格模式不允许插入，宽松模式插入空字符串。
    示例： 
    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;
  • WHERE子句中只带有普通字符串，GaussDB中't'、'true'、'yes'、 'y'、'on'返回TRUE，'no'、'f'、'off'、'false'、'n'返回FALSE，其余字符串报错。MySQL通过字符串转换为INT1判断返回TRUE/FALSE。

    示例：

    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;
  • 对于YEAR类型的输入，在将字符串转换为整型的过程中，MySQL考虑科学计数法，GaussDB暂不支持，统一做截断处理。

    示例：

    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;
  • 对于UNION的CREATE TABLE AS场景，GaussDB不区分左右子节点的顺序，MySQL区分左右子节点的顺序，左右子节点互换会导致结果不同。

    示例：

    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;
  • GaussDB中函数嵌套场景下，涉及到聚合函数（如max、min、sum和avg）中存在字符串类型包含非数值字符，隐式转换到数值类型发生截断或置零，且包含操作符比较、having比较的场景时，GaussDB统一进行类型转换并产生告警，MySQL在相同场景下不会全部产生告警。

    示例：

    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)

显式类型转换差异点

  • GaussDB中平铺成对各目标类型的转换规则，MySQL中使用C++多态重载函数，在嵌套场景中存在不一致行为。
    示例： 
    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 |
+---------------------------------------------------------+
  • 在GaussDB中，BLOB、TINYBLOB、MEDIUMBLOB、LONGBLOB、BINARY、VARBINARY、BIT、及YEAR类型显式转换为JSON类型，结果与MySQL不同。

    示例：

    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)
  • GaussDB在JSON数据类型显式转换后运用于精度计算时，与MySQL5.7不一致，与MySQL8.0一致，计算时精度与使用JSON类型表中数据精度保持一致。

    示例：

    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)

UNION，CASE和相关构造差异点

  • POLYGON + NULL、POINT + NULL、POLYGON + POINT组合在MySQL中均返回GEOMETRY类型，GaussDB中未涉及，暂时当做报错处理。
  • SET和ENUM两种类型暂未支持，暂时当做报错处理。
  • JSON和二进制类型（BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB）的UNION和UNION ALL组合，MySQL中返回LONGBLOB类型，GaussDB中返回JSON类型，同时支持二进制类型（BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB）到JSON的隐式类型转换。
  • 未设置m_format_behavior_compat_options为enable_precision_decimal时，常量类型和其他类型做类型聚合的时候，输出类型的精度为其他类型的精度。如“select "helloworld" union select p from t;”的结果的精度为属性p的精度。
  • 未设置m_format_behavior_compat_options为enable_precision_decimal时，定点常量和不带精度约束的类型（非字符串类型如int、bool、year等，聚合结果类型为定点类型）聚合时，精度约束会按照定点数默认精度31输出。
  • merge rule差异：

    MySQL 5.7中存在部分不合理的类型推导，如BIT类型和整型/YEAR类型推导会得出VARBINARY类型，UNSIGNED类型和非UNSIGNED类型推导会得到带UNSIGNED的类型等，同时CASE WHEN和UNION的聚合结果也存在差异，类型推导结果太小时存在数据溢出风险。在MySQL 8.0版本修复了上述相关的问题，因此merge rule聚合规则以8.0为准。

  • MySQL中BINARY和CHAR填充字符不相同，BINARY填充'\0'，CHAR填充空格，GaussDB中BINARY和CHAR都是填充空格。
  • 在精度传递场景下，使用CASE WHEN语句时，会进行类型转换和精度重新计算，导致最终的输出结果与CASE子句对比会出现末尾多零场景或末尾少零场景：
    • 末尾多零场景：CASE节点会根据CASE子句的精度计算CASE节点精度，当THEN子句的精度比CASE节点的精度小时，会在CASE节点末尾补零。
    • 末尾少零场景：多层CASE WHEN嵌套时，内层CASE执行类型转换之后，只保留内层CASE的精度，外层CASE无法得到THEN子句的精度信息，因此外层CASE会根据内层CASE的精度计算的精度进行类型转换。当外层CASE类型转换时内层CASE精度比THEN子句少，会出现末尾少零场景。

    示例：

    • 末尾多零少零场景。 
      -- 末尾多零场景。
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)

-- 末尾少零场景。
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)
    • 在开启精度传递的场景下，使用集合运算（UNION、MINUS、EXCEPT、INTERSECT），如果参与集合运算的查询语句，其查询的字段为函数、表达式而不是直接使用表中的字段，且查询的结果数据类型为INT/INT UNSIGNED，则最后返回的数据类型存在差异。在MySQL中，返回的数据类型为BIGINT/BIGINT UNSIGNED；在GaussDB中，返回的数据类型为INT/INT UNSIGNED。 
      -- 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

-- 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)
    • 在开启精度传递的场景下，case when被嵌套场景的结果与MySQL保持差异。MySQL的类型可以透过多层直接转换，而GaussDB结果精度是逐层确定并且逐层转换的，因此可能导致结果小数位或进位和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)
    • 对于需要int类型的运算符（如 ~，&，|，<<，>>）嵌套case when语句，若case when语句返回的是varchar类型，则实际情况可以会发生截断(根据原表数据分析是否会发生截断)，GaussDB会报出相应错误（select查询warning告警，create建表error报错），Mysql不会报错。（若GaussDB想要完成create table建表操作，可以通过设置sql_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)
    • 在开启精度传递的场景下，对于CREATE VIEW AS SELECT CASE WHEN语句和SELECT CASE WHEN语句嵌套常量（包括常量计算、函数嵌套常量等）的情况，GaussDB在该情况下值保持一致，MySQL在SELECT CASE WHEN语句中可能会丢失部分精度。 
      -- 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)
    • 在开启精度传递的场景下，M-Compatibility模式数据库支持UNION/CASE WHEN语句建表，但是由于架构不同，M-Compatibility模式数据库无法保证创建的表的所有类型与MySQL 8.0完全相同。MySQL返回字符串、二进制相关类型的场景，以及部分函数嵌套场景，与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)
    • 在开启精度传递的场景下，对于CREATE TABLE AS SELECT A % (CASE WHEN)语句，如果A是DECIMAL类型，case when结果为日期类型（DATE、TIME、DATETIME），两者进行取模运算（%）得到的精度保持差异。GaussDB得到的精度跟decimal类型与日期类型直接取模运算得到的精度保持一致。 
      -- GaussDB：(decimal % date类型case)与(numeric%date)， 精度一致，都是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)

-- MySQL5.7，精度存在差异。(decimal % date类型case)精度为decimal(65,10)，(numeric%date)精度为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)

-- MySQL8.0，(decimal % date类型case)和(numeric%date)精度都为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)
    • 在开启精度传递的场景下，使用UNION，如果参与集合运算的查询语句，其查询的字段为常量，且查询的结果数据类型为INT/DECIMAL，则最后返回的精度存在差异。在MySQL 5.7中，返回的精度与UNION左右两侧的顺序有关；在MySQL 8.0中修复了这个问题，返回的精度与UNION左右两侧的顺序无关；在GaussDB中，返回的精度与UNION左右两侧的顺序无关，与MySQL 8.0一致，与MySQL 5.7不一致。 
      -- 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)

双冒号转换差异点

GaussDB中使用双冒号将函数入参转换为期望类型可能导致结果超出预期；MySQL中无双冒号功能。

示例： 
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)

decimal类型差异点

在Create table ... AS (select ...)语句中，使用decimal数据类型，若含有前缀0，M-Compatibility兼容模式下忽略前缀0，长度计算不包括0，在MySQL 5.7下，长度计算加上前缀0的数量，在MySQL 8.0下，无论存在多少个前缀0，长度计算只加上1。 
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)
父主题： 数据类型

相关文档