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

手机

顺络电子：董事长部分股权办理股票质押业务

深圳7月二手住宅成交2259套，中介称近期咨询客户开始增加

家电

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

2023-08-11 15:05:03 来源：博客园

本文分享自华为云社区《GaussDB(DWS)函数结果差异案例之greatest》，作者：你是猴子请来的救兵吗。

GaussDB(DWS)支持多种兼容模式，为了兼容目标数据库，各模式之间或多或少存在一些行为差异。这里分享一个mysql兼容模式下的表达式函数因不同写法引发的结果差异案例。

【资料图】

问题背景

问题版本GaussDB 8.1.1

问题描述

用户反馈mysql兼容模式下，以下两条sql的执行结果存在差异：select greatest(1,2,100,-1,0,nvl(null,0)) 出来的结果是 2select 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’。

01-11002

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

-1012100

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 TYPEmysql=# 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 textLINE 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)

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

关键词：