Forced Conversion Functions

1

CAST()

Supported

• Due to different function execution mechanisms, flags cannot be transferred to the inner function. 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, CAST(expr AS CHAR[(N)] charset_info or CAST(expr AS NCHAR[(N)]) cannot be used to convert character sets.
• 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)

2

CONVERT()

Supported

• In GaussDB, CONVERT(expr, CHAR[(N)] charset_info or CAST(expr, NCHAR[(N)]) cannot be used to convert character sets.
• 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.