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)
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 我与微信审核的“相爱相杀”看个人小程序副业
2022-08-11 老忘记带伞,自己动手做一个雨天提醒打伞小工具
2022-08-11 云图说丨初识云速建站服务
2022-08-11 编译器优化:何为SLP矢量化
2022-08-11 案例复现,带你分析Priority Blocking Queue比较器异常导致的NPE问题
2021-08-11 5招教你实现多线程场景下的线程安全!
2021-08-11 跟我读论文丨ACL2021 NER BERT化隐马尔可夫模型用于多源弱监督命名实体识别
2021-08-11 大数据集群跨多版本升级、业务0中断,只因背后有TA