类型转换函数

CAST()

• 由于函数执行机制不同，在cast函数嵌套其他函数（如greatest、least等）时，内层函数返回小于1的值，结果与MySQL不一致。

  --GaussDB:
  m_db=# SELECT cast(least(1.23, 1.23, 0.23400) AS date);
  WARNING:  Incorrect datetime value: '0.23400'
  CONTEXT:  referenced column: cast
   cast 
  ------
  (1 row)
  
  --MySQL 5.7:
  mysql> SELECT cast(least(1.23, 1.23, 0.23400) AS date);
  +------------------------------------------+
  | cast(least(1.23, 1.23, 0.23400) as date) |
  +------------------------------------------+
  | 0000-00-00                               |
  +------------------------------------------+
  1 row in set (0.00 sec)

• GaussDB支持使用CAST(expr AS FLOAT[(p)])或CAST(expr AS DOUBLE)将表达式转换为浮点类型，MySQL 5.7版本不支持此转换。
• 对于CAST嵌套子查询场景，如果子查询语句返回的是FLOAT类型，GaussDB返回的是准确的数值，MySQL 5.7版本返回失真数值，BINARY函数使用CAST实现，同理。

  --GaussDB
  m_db=# CREATE TABLE sub_query_table(myfloat float);
  CREATE TABLE
  
  m_db=# INSERT INTO sub_query_table(myfloat) VALUES (1.23);
  INSERT 0 1
  
  m_db=# SELECT binary(SELECT myfloat FROM sub_query_table) FROM sub_query_table;
   binary 
  --------
   1.23
  (1 row)
  
  m_db=# SELECT cast((SELECT myfloat FROM sub_query_table) AS char);
   cast 
  ------
   1.23
  (1 row)
  
  --MySQL 5.7
  mysql> CREATE TABLE sub_query_table(myfloat float);
  Query OK, 0 rows affected (0.02 sec)
  
  mysql> INSERT INTO sub_query_table(myfloat) VALUES (1.23);
  Query OK, 1 row affected (0.00 sec)
  
  mysql> SELECT binary(SELECT myfloat FROM sub_query_table) FROM sub_query_table;
  +-----------------------------------------------+
  | binary(SELECT myfloat FROM sub_query_table) |
  +-----------------------------------------------+
  | 1.2300000190734863                            |
  +-----------------------------------------------+
  1 row in set (0.00 sec)
  
  mysql> SELECT cast((SELECT myfloat FROM sub_query_table) AS char);
  +------------------------------------------------------+
  | cast((SELECT myfloat FROM sub_query_table) AS char) |
  +------------------------------------------------------+
  | 1.2300000190734863                                   |
  +------------------------------------------------------+
  1 row in set (0.00 sec)

• GaussDB在JSON数据类型显式转换后运用于精度计算时，与MySQL 5.7不一致，与MySQL 8.0一致，计算时精度与使用JSON类型表中数据精度保持一致。

  示例：

  --GaussDB
  test=# SET m_format_behavior_compat_options='enable_precision_decimal';
  SET
  
  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 5.7
  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 8.0
  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)

CONVERT()

GaussDB支持使用CONVERT(expr, FLOAT[(p)])或CONVERT(expr, DOUBLE)将表达式转换为浮点类型，MySQL 5.7版本不支持此转换。