LightDB对decode函数功能增强

背景

decode函数是oracle数据库中一个功能强大且使用频率极高的函数，先来个decode函数的原型：

decode函数的使用方法是拿expr与search逐个比较，若满足expr与某个search相等，则函数返回该search对应的result；若expr与所有search值都不等，则返回default值，而当default参数未提供时，函数返回null。

注：在decode函数中，两个null比较是认为相等的。

旧版LightDB的oracle模式下，也支持decode函数，但是原先的实现逻辑是在比较前将expr、search统一转成字符串后再进行比较，这样就会出现如下案例中让人琢磨不透的结果：

lightdb@oracle=# select decode(1.0, 1, 2, 3); --转成字符串后，'1.0'和'1'确实不等了
 decode 
--------
      3
(1 row)

LightDB对decode的功能增强

在LightDB的23.4版本中，对decode函数进行了重构和功能增强，尽可能将其功能和行为主要修改点为：

1、当expr与searchn进行等值比较时，会先将expr和searchn（表示第2-n个search条件都要转换类型）转成search1的类型后再进行比较，若不能进行类型转换则直接报错。

lightdb@oracle=# select decode(1.0, 1, 2, 3);    --1.0需先转成int后再与1比较
 decode 
--------
      2
(1 row)

lightdb@oracle=# select decode('x',1,2,3);    --x无法转成int类型，所以报错
ERROR:  invalid input syntax for type numeric: "x"

2、resultn和default都是潜在的返回返回对象，但是在返回前需将类型转成result1的类型，然后再返回，若不能进行类型转换则直接报错。

--第3个参数的类型就是是decode函数的返回类型
lightdb@oracle=# select decode(3,1,'2','x');    --'x'转成字符后输出
 decode 
--------
 x
(1 row)

lightdb@oracle=# select decode(3,1,'2',3,'x','y');     --'x'转成字符后输出
 decode 
--------
 x
(1 row)

lightdb@oracle=# select decode(3,1,2,'x');    --x无法转成int类型则报错
ERROR:  invalid input syntax for type numeric: "x"

lightdb@oracle=# select decode(3,1,2,3,'x','y');    --x无法转成int类型则报错
ERROR:  invalid input syntax for type numeric: "x"

3、search、result和default可以是常量或复杂的表达式，LightDB会对result和default使用“短路”方式计算上述表达式的值，即每个search仅在与expr比较且比较结果为相等时才会计算result或default值，否则无需计算。但是这里有一个重要前提：必须存在源类型到目标类型转换规则，例如text类型的'x'转成numeric类型(decode处理时将所有字符类型转成text处理，数值类型当成numeric处理)，pg_cast中存在转换规则，因此适用于该“短路”规则；而timestamp with time zone转成numeric类型，在pg_cast中不存在转换规则，此时不适用于该“短路"规则，而是直接报错了。另外该“短路”规则对search条件不适用。参考下节内容。

lightdb@oracle=# SELECT  
lightdb@oracle-#     DECODE(  
lightdb@oracle(#         12345,   
lightdb@oracle(#         012345.0,  
lightdb@oracle(#         1234567,    --直接返回了，后面的就跳过不计算了
lightdb@oracle(#         1234567890.1234567890,   
lightdb@oracle(#         9223372036854775807,  
lightdb@oracle(#         123.456,  
lightdb@oracle(#         123456789.9876543210,  
lightdb@oracle(#         'No Match'   --default值原本是无法转成int要报错的，存在text到numeric的类型转换，所以适用于短路规则，不报错
lightdb@oracle(#     ) AS decoded_value  
lightdb@oracle-# FROM  
lightdb@oracle-#     dual;
 decoded_value 
---------------
       1234567
(1 row)

lightdb@oracle=# select decode(3,
lightdb@oracle(# 1,1/0,    --expr与search不等，直接跳过result的计算
lightdb@oracle(# 2,2/0,    --expr与search不等，直接跳过result的计算
lightdb@oracle(# 3,4);
 decode 
--------
      4
(1 row)

lightdb@oracle=# SELECT DECODE(123, 
lightdb@oracle(#               1234, 123, 
lightdb@oracle(#               234, 'Not Match value',
lightdb@oracle(#               now()--不存在timestamp with time zone到numeric的转换规则，不适用于短路规则，报错
lightdb@oracle(#               ) FROM DUAL;
ERROR:  cannot cast type timestamp with time zone to numeric
LINE 4:               now()--不存在timestamp with time zone到numeric...
                   

已知的与Oracle的decode函数的差异

“短路”规则

请注意上述的“短路”操作仅针对result和default有效，对于search而言并不遵循此规则，这是PostgreSQL与Oracle内部实现差异所致，参考如下案例

lightdb@oracle=# SELECT DECODE(123, 
lightdb@oracle(#   123, 'Number Match', 
lightdb@oracle(#   'Not Match', 'Default') --'Not Match'需要转成search1的类型即int，无法转换则报错
lightdb@oracle-#   FROM DUAL;
ERROR:  invalid input syntax for type numeric: "Not Match"
LINE 3:   'Not Match', 'Default') --'Not Match'需要转成search1的类型...
          ^
lightdb@oracle=# 

字符类型转换

Oracle官网上，对于decode的字符比较是这么描述的：If expr and search are character data, then Oracle compares them using nonpadded comparison semantics.

对于这句话，本人的理解是将字符串中存在的padded character去掉后再比较，但好像实际执行结果并不是这样，参考如下实验

在LightDB中，目前遵循的规则是将expr和searchn转成search1的类型后再比较，目前LightDB基于的PG13.8版本中，char和varchar/text的类型转换规则是这样的：1-当char转varchar/text时，空格(padded character)会保留；2-当varchar/text转char时，会自动生成padded character。参考如下实验：

lightdb@oracle=# \d t1
                    Table "public.t1"
 Column |     Type      | Collation | Nullable | Default 
--------+---------------+-----------+----------+---------
 a      | character(10) |           |          | 
 b      | varchar2(10)  |           |          | 

lightdb@oracle=# select * from t1;
     a      | b 
------------+---
 a          | a
(1 row)

lightdb@oracle=# select a::varchar=b, b::char=a from t1;--保留padded char，所以不等
 ?column? | ?column? 
----------+----------
 f        | t
(1 row)

lightdb@oracle=# select a::varchar(10)=b, b::char(10)=a from t1;
 ?column? | ?column? 
----------+----------
 f        | t
(1 row)

lightdb@oracle=# select a::varchar(1)=b, b::char(10)=a from t1;
 ?column? | ?column? 
----------+----------
 t        | t
(1 row)

理解了PostgreSQL中字符串的类型转换后，如下decode函数产生的结果就能理解了：

lightdb@oracle=# \d t1
                    Table "public.t1"
 Column |     Type      | Collation | Nullable | Default 
--------+---------------+-----------+----------+---------
 a      | character(10) |           |          | 
 b      | varchar2(10)  |           |          | 

lightdb@oracle=# select * from t1;
     a      | b 
------------+---
 a          | a
(1 row)

lightdb@oracle=# select decode(a,b,1,2) from t1;    --char转varchar，保留padded char，所以a和b不等，所以返回2
 decode 
--------
      2
(1 row)

lightdb@oracle=# select decode(b,a,1,2) from t1;    --varchar转char，都保留padded char，所以a和b相等，所以返回1
 decode 
--------
      1
(1 row)

lightdb@oracle=# select decode(trim(a),b,1,2) from t1;    --对a先去掉padded char后，再转varchar，此时没有padded char了，a和b相等，所以返回1
 decode 
--------
      1
(1 row)