当前位置：首页 > news >正文

LightDB 24.1 UNION支持null类型匹配

news 2025/8/26 13:47:02

背景介绍

在LightDB 24.1之前的版本，UNION null类型匹配会报错。

lightdb@postgres=# select null l_zqlbmx2          
union all
select null l_zqlbmx2          
union all
select sysdate l_zqlbmx;
ERROR:  UNION types text and timestamp without time zone cannot be matched
LINE 5: select sysdate l_zqlbmx;lightdb@postgres=# select null l_zqlbmx2
union all
select null l_zqlbmx2
union all
select 2 l_zqlbmx;
ERROR:  UNION types text and integer cannot be matched
LINE 5: select 2 l_zqlbmx;^

为了兼容Oracle数据库的使用，在LightDB24.1版本中，支持了UNION null 类型与其他类型的强转。
使用约束：

在Oracle模式下生效；
null类型在LightDB中是text类型，如果匹配的类型无法强转成text类型，则UNION语句仍然会报错;

使用示例

null 和 int

lightdb@oracle_test=# select null l_zqlbmx2 from dual
lightdb@oracle_test-# union all
lightdb@oracle_test-# select null l_zqlbmx2 from dual
lightdb@oracle_test-# union all
lightdb@oracle_test-# select 0 l_zqlbmx from dual;l_zqlbmx2 
-----------0
(3 rows)lightdb@oracle_test=# \gdescColumn   | Type 
-----------+------l_zqlbmx2 | text
(1 row)

null和numeric

lightdb@oracle_test=# select null l_zqlbmx2 from dual
union all
select null l_zqlbmx2 from dual
union all
select 5.55::numeric l_zqlbmx from dual;l_zqlbmx2 
-----------5.55
(3 rows)lightdb@oracle_test=# \gdescColumn   | Type 
-----------+------l_zqlbmx2 | text
(1 row)

null和oracle.date

lightdb@oracle_test=# select null l_zqlbmx2 from dual
union all
select null l_zqlbmx2 from dual
union all
select sysdate l_zqlbmx from dual;l_zqlbmx2      
---------------------2024-01-26 08:23:42
(3 rows)lightdb@oracle_test=# \gdescColumn   | Type 
-----------+------l_zqlbmx2 | text
(1 row)

null和timestamp

lightdb@oracle_test=# select null l_zqlbmx2 from dual
union all
select null l_zqlbmx2 from dual
union all
select current_timestamp l_zqlbmx from dual;l_zqlbmx2           
-------------------------------2024-01-26 16:28:07.278097+08
(3 rows)lightdb@oracle_test=# \gdescColumn   | Type 
-----------+------l_zqlbmx2 | text
(1 row)

null和varchar

lightdb@oracle_test=# select null l_zqlbmx2 from dual
union all
select null l_zqlbmx2 from dual
union all
select 'hello' l_zqlbmx from dual;l_zqlbmx2 
-----------hello
(3 rows)lightdb@oracle_test=# \gdescColumn   | Type 
-----------+------l_zqlbmx2 | text
(1 row)