Type Conversion Functions

CAST()

• Due to different function execution mechanisms, when other functions (such as greatest and least) are nested in the cast function, the inner function returns a value less than 1. The result is different from that in 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)

• In GaussDB, you can use CAST(expr AS FLOAT[(p)]) or CAST(expr AS DOUBLE) to convert an expression to the one of the floating-point type. MySQL 5.7 does not support this conversion.
• In the CAST nested subquery scenario, if the subquery statement returns the FLOAT type, an accurate value is returned in GaussDB while a distorted value is returned in MySQL 5.7. The same rule applies to the BINARY function implemented using 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)

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

  Example:

  --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()

In GaussDB, you can use CONVERT(expr, FLOAT[(p)]) or CONVERT(expr, DOUBLE) to convert an expression to the one of the floating-point type. MySQL 5.7 does not support this conversion.