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