CAST() |
支持,存在差异 |
- 由于函数执行机制不同,flags无法传递给内层函数,在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 CHAR[(N)] charset_info或者CAST(expr AS NCHAR[(N)])转换字符集。
- 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)
|