GaussDB(DWS)函数不同写法引发的结果差异

本文分享自华为云社区《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’。

0

1

-1

100

2

3,依次类推,第二条语句是以int类型进行排序选择最大值的,依次为(-1,0,1,2,100),因此我们得到最大值是数值类型的100。

-1

0

1

2

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)

 

点击关注,第一时间了解华为云新鲜技术~

热门相关:我的治愈系游戏   今天也没变成玩偶呢   不科学御兽   买妻种田:山野夫君,强势宠!   豪门情变,渣总裁滚远点!