# 数据类型转换
不同的数据类型之间支持转换。有如下场景涉及到数据类型转换：
- 操作符（比较操作符、运算操作符等）的操作数的数据类型不一致。常见于查询条件或者关联条件中的比较运算。
- 函数调用时实参和形参的数据类型不一致。
- DML语句要更新（包括INSERT、UPDATE、MERGE、REPLACE等）的目标列，数据的类型和列的定义类型不一致。
- 集合运算（UNION以及EXCEPT）确定最终投影列的目标数据类型后，各个SELECT查询的投影列的类型和目标数据类型不一致。
- 其他表达式计算场景，根据不同表达式的数据类型， 来决定用于比较或者最终结果的目标数据类型。
- 普通的字符串类型当字符序为BINARY时，将转换成对应的二进制类型（TEXT转换成BLOB，VARCHAR转换成VARBINARY等）。
数据类型转换差异点主要分为：隐式转换，UNION/CASE、decimal类型。
#### 隐式类型转换差异点
- GaussDB中统一平铺成小类型到小类型的转换规则，MySQL中使用小类型转大类型，大类型转小类型的转换规则。
 
- WHERE条件子句中只有字符串的场景，GaussDB中't'、'true'、'y'、'yes'、'on'、'1'以及其大写版本会被识别为TRUE，能得到查询结果，'f'、'false'、'n'、'no'、'off'、'0'以及其大写版本会被识别为FALSE，无法得到查询结果。除上述字符串外，其余字符串均报错。MySQL中字符串首位为非0的数字即可得到查询结果，其余场景均无法得到查询结果。 示例：
  ```
  -- 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)
  ```
  
- 对于YEAR类型的表，输入字符串时若遇到'e'或'E'，MySQL会以科学计数法来处理，GaussDB直接报错或截断。 示例：
  ```
  -- 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)
  ```
  

- GaussDB中函数嵌套场景下，涉及到聚合函数（如max、min、sum和avg）中存在字符串类型包含非数值字符，隐式转换到数值类型发生截断或置零，且包含操作符比较、having比较的场景时，GaussDB统一进行类型转换并产生告警，MySQL在相同场景下不会全部产生告警。 示例：
  ```
  -- 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)
  ```
  
#### UNION，CASE和相关构造差异点
- POLYGON + NULL、POINT + NULL、POLYGON + POINT组合在MySQL中均返回GEOMETRY类型，GaussDB中未涉及，暂时当做报错处理。
- SET和ENUM两种类型暂未支持，暂时当做报错处理。
- JSON和二进制类型（BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB）、BIT或者YEAR类型的UNION和UNION ALL组合，MySQL中返回LONGBLOB或者LONGTEXT类型，GaussDB中返回JSON类型，同时支持二进制类型（BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB）、BIT或者YEAR类型到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以及EXCEPT），如果参与集合运算的查询语句，其查询的字段为函数、表达式而不是直接使用表中的字段，且查询的结果数据类型为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)
    ```
    
  
  - 在开启精度传递的场景下，GaussDB数据库支持UNION/CASE WHEN语句建表，但是由于架构不同，GaussDB数据库无法保证创建的表的所有类型与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> 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)
    -- MySQL 5.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)
    -- MySQL 8.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)
    -- 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)
    ```
    
   
 
#### 双冒号转换差异点
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，GaussDB数据库下忽略前缀0，长度计算不包括0，在MySQL 5.7下，长度计算加上前缀0的位数，在MySQL 8.0下，无论存在多少个前缀0，长度计算只加上1。
```
-- 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)
```