本文分享自华为云社区《GaussDB(DWS)函数结果差异案例之greatest》，作者： 你是猴子请来的救兵吗。
GaussDB(DWS)支持多种兼容模式，为了兼容目标数据库，各模式之间或多或少存在一些行为差异。这里分享一个 mysql 兼容模式下的表达式函数因不同写法引发的结果差异案例。
问题背景
问题版本 GaussDB 8.1.1
问题描述
用户反馈mysql兼容模式下，以下两条sql的执行结果存在差异：
select greatest(1,2,100,-1,0,nvl(null,0)) 出来的结果是 2
select greatest(1,2,100,-1,0) 出来结果是 100
复制代码
场景再现
mysql=# select greatest(1,2,100,-1,nvl(null,0));
greatest
----------
2
(1 row)
mysql=# select greatest(1,2,100,-1,0,0);
greatest
----------
100
(1 row)
复制代码
根因分析
1，不知道小伙伴们有没有注意到，这两个结果集的显示一个是靠左的一个是靠右的；ok，我们先来确认下这两个结果的数据类型：
mysql=# select pg_typeof(greatest(1,2,100,-1,nvl(null,0)));
pg_typeof
-----------
text
(1 row)
mysql=# select pg_typeof(greatest(1,2,100,-1,0));
pg_typeof
-----------
integer
(1 row)
复制代码
2，依靠 pg_typeof 我们拿到了返回结果的数据类型；这就说明第一条语句是以 text 类型进行排序选择最大值的，依次为（‘0’,‘1’,’-1’,‘100’,‘2’），因此我们得到最大值是字符串类型的’2’。
3，依次类推，第二条语句是以 int 类型进行排序选择最大值的，依次为（-1,0,1,2,100），因此我们得到最大值是数值类型的 100。
4，表达式函数 greatest 的返回类型是基于入参类型确定的，这里的差异是由于第五个入参类型导致的结果差异。
mysql=# select pg_typeof(nvl(null,0));
pg_typeof
-----------
text
(1 row)
mysql=# select pg_typeof(0);
pg_typeof
-----------
integer
(1 row)
复制代码
5，而 nvl/greatest 之所以会出现不同的返回类型，是由 mysql 兼容模式下的类型匹配规则决定的。
具体规则可参考：UNION，CASE和相关构造。
修改建议
针对此差异场景，建议在不确定返回类型时显式指定其入参类型，将 nvl(null,0)改为 nvl(null,0)::int，这样结果就是已 int 排序的，与另一台语句预期相符。
mysql=# select greatest(1,2,100,-1,nvl(null,0)::int);
greatest
----------
100
(1 row)
复制代码
知识剖析
SQL UNION 构造把不相同的数据类型进行匹配输出为统一的数据类型结果集。因为 SELECT UNION 语句中的所有查询结果必须在一列里显示出来，所以每个 SELECT 子句中的元素类型必须相互匹配并转换成一个统一的数据类型。同样的要求广泛存在于 UNION、ARRAY 和 CASE、COALESCE、IF、IFNULL 和 GREATEST、LEAST 和 NVL 等表达式和函数中。
GaussDB(DWS)支持多种兼容模式，不同兼容模式下的类型匹配规则也不尽相同。为了便于理解，这里仅以 mysql 兼容模式下 IFNULL 的类型匹配规则进行举例说明，它与 GREATEST 在 mysql 兼容模式下的规则是一致的。
规则 1： 如果所有输入都是相同的类型，不包括 unknown 类型，那么解析成所输入的相同数据类型。
mysql=# select pg_typeof(1),pg_typeof(2);
pg_typeof | pg_typeof
-----------+-----------
integer | integer
(1 row)
mysql=# select ifnull(1,2),pg_typeof(ifnull(1,2));
ifnull | pg_typeof
--------+-----------
1 | integer
(1 row)
复制代码
规则 2： 如果所有输入都是 unknown 类型则解析成 text 类型。（常量字符串就是 unknow 类型）
mysql=# select pg_typeof('1'),pg_typeof('2');
pg_typeof | pg_typeof
-----------+-----------
unknown | unknown
(1 row)
mysql=# select ifnull('1','2'),pg_typeof(ifnull('1','2'));
ifnull | pg_typeof
--------+-----------
1 | text
(1 row)
复制代码
规则 3： 如果输入是 unknown 类型和某一非 unknown 类型，则解析成该非 unknown 类型。
mysql=# select pg_typeof(current_date),pg_typeof('20230801');
pg_typeof | pg_typeof
-----------+-----------
date | unknown
(1 row)
mysql=# select ifnull(current_date,'20230801'),pg_typeof(ifnull(current_date,'20230801'));
ifnull | pg_typeof
------------+-----------
2023-08-10 | date
(1 row)
复制代码
规则 4： 如果存在多种非 unknown 类型，将 enum 类型当做 text 类型，再进行比较。
mysql=# create type gender as enum('boy','girl');
CREATE TYPE
mysql=# select pg_typeof('boy'::gender),pg_typeof('girl'::varchar);
pg_typeof | pg_typeof
-----------+-------------------
gender | character varying
(1 row)
mysql=# select ifnull('boy'::gender,'girl'::varchar),pg_typeof(ifnull('boy'::gender,'girl'::varchar));
ifnull | pg_typeof
--------+-----------
boy | text
(1 row)
复制代码
规则 5： 如果输入类型是同一个类型范畴，则选择该类型的优先级较高的类型。如果是不同的类型范畴，则解析成 text 类型。
--相同类型范畴
mysql=# select pg_typeof(1),pg_typeof(2.0);
pg_typeof | pg_typeof
-----------+-----------
integer | numeric
(1 row)
mysql=# select ifnull(1,2.0),pg_typeof(ifnull(1,2.0));
ifnull | pg_typeof
--------+-----------
1 | numeric
(1 row)
--不同类型范畴
mysql=# select pg_typeof(1),pg_typeof(current_date);
pg_typeof | pg_typeof
-----------+-----------
integer | date
(1 row)
mysql=# select ifnull(1,current_date),pg_typeof(ifnull(1,current_date));
ifnull | pg_typeof
--------+-----------
1 | text
(1 row)
复制代码
规则 6： 把所有输入转换为所选的类型。如果从给定的输入到所选的类型没有隐式转换则失败。
--json不存在到text的隐式转换
mysql=# select pg_typeof(1),pg_typeof('{"a":1}'::json);
pg_typeof | pg_typeof
-----------+-----------
integer | json
(1 row)
mysql=# select ifnull(1,'{"a":1}'::json),pg_typeof(ifnull(1,'{"a":1}'::json));
ERROR: IFNULL could not convert type json to text
LINE 1: select ifnull(1,'{"a":1}'::json),pg_typeof(ifnull(1,'{"a":1}...
^
CONTEXT: referenced column: ifnull
--可以尝试显式指定类型转换
mysql=# select ifnull(1,'{"a":1}'::json::text);
ifnull
--------
1
(1 row)
复制代码
点击关注，第一时间了解华为云新鲜技术~
评论