[TL;DR]要使其按预期工作,请使用CAST
为文本提供显式数据类型.比如CAST('a ' AS VARCHAR2(10))
或CAST('a ' AS CHAR(2))
.
默认的文本数据类型出现了问题,似乎是一个bug.
如果定义一个表:
CREATE TABLE data (lhs CHAR(2), rhs CHAR(1));
INSERT INTO data VALUES ('a ', 'a');
然后运行:
select case when lhs > rhs then 't' else 'f' end AS gt_ch,
case when lhs < rhs then 't' else 'f' end AS lt_ch,
case when lhs = rhs then 't' else 'f' end AS eq_ch,
case
when lhs > rhs then 'gt'
when lhs < rhs then 'lt'
when lhs = rhs then 'eq'
else NULL
end AS comp,
case
when CAST(lhs AS VARCHAR2(10)) > CAST(rhs AS VARCHAR2(10))
then 't'
else 'f'
end AS gt_vc,
case
when CAST(lhs AS VARCHAR2(10)) < CAST(rhs AS VARCHAR2(10))
then 't'
else 'f'
end AS lt_vc,
case
when CAST(lhs AS VARCHAR2(10)) = CAST(rhs AS VARCHAR2(10))
then 't'
else 'f'
end AS eq_vc,
DUMP(lhs) AS dmp_lsh_raw,
DUMP(CAST(lhs AS CHAR(2))) AS dmp_lhs_ch,
DUMP(CAST(lhs AS VARCHAR2(10))) AS dmp_lhs_vc,
DUMP(rhs) AS dmp_rsh_raw,
DUMP(CAST(rhs AS CHAR(2))) AS dmp_rhs_ch,
DUMP(CAST(rhs AS VARCHAR2(10))) AS dmp_rhs_vc
from data;
那么输出是:
GT_CH LT_CH EQ_CH COMP GT_VC LT_VC EQ_VC DMP_LSH_RAW DMP_LHS_CH DMP_LHS_VC DMP_RSH_RAW DMP_RHS_CH DMP_RHS_VC f f t eq t f f Typ=96 Len=2: 97,32 Typ=96 Len=2: 97,32 Typ=1 Len=2: 97,32 Typ=96 Len=1: 97 Typ=96 Len=2: 97,32 Typ=1 Len=1: 97
这是有效的,将它们作为默认数据类型CHAR
进行比较,使用Blank-Padding Comparison Semantics,那么'a '
和'a'
是相等的,但将它们作为VARCHAR2
个数据类型进行比较,使用非填充比较语义,那么'a '
大于'a'
.这是预期的行为.
如果对子查询分解子句(也称CTE)中生成的CHAR
种数据类型的文本执行相同的查询:
WITH cte (lhs, rhs) AS (
SELECT CAST('a ' AS CHAR(2)),
CAST('a' AS CHAR(1))
FROM DUAL
)
select case when lhs > rhs then 't' else 'f' end AS gt_ch,
case when lhs < rhs then 't' else 'f' end AS lt_ch,
case when lhs = rhs then 't' else 'f' end AS eq_ch,
case
when lhs > rhs then 'gt'
when lhs < rhs then 'lt'
when lhs = rhs then 'eq'
else NULL
end AS comp,
case
when CAST(lhs AS VARCHAR2(10)) > CAST(rhs AS VARCHAR2(10))
then 't'
else 'f'
end AS gt_vc,
case
when CAST(lhs AS VARCHAR2(10)) < CAST(rhs AS VARCHAR2(10))
then 't'
else 'f'
end AS lt_vc,
case
when CAST(lhs AS VARCHAR2(10)) = CAST(rhs AS VARCHAR2(10))
then 't'
else 'f'
end AS eq_vc,
DUMP(lhs) AS dmp_lsh_raw,
DUMP(CAST(lhs AS CHAR(2))) AS dmp_lhs_ch,
DUMP(CAST(lhs AS VARCHAR2(10))) AS dmp_lhs_vc,
DUMP(rhs) AS dmp_rsh_raw,
DUMP(CAST(rhs AS CHAR(2))) AS dmp_rhs_ch,
DUMP(CAST(rhs AS VARCHAR2(10))) AS dmp_rhs_vc
from cte;
然后,输出与数据存储在表中时相同,这也是预期的行为.
但是,如果删除显式强制转换并使用文本的默认数据类型:
WITH cte_default_data_type (lhs, rhs) AS (
SELECT 'a ',
'a'
FROM DUAL
)
select case when lhs > rhs then 't' else 'f' end AS gt_ch,
case when lhs < rhs then 't' else 'f' end AS lt_ch,
case when lhs = rhs then 't' else 'f' end AS eq_ch,
case
when lhs > rhs then 'gt'
when lhs < rhs then 'lt'
when lhs = rhs then 'eq'
else NULL
end AS comp,
case
when CAST(lhs AS VARCHAR2(10)) > CAST(rhs AS VARCHAR2(10))
then 't'
else 'f'
end AS gt_vc,
case
when CAST(lhs AS VARCHAR2(10)) < CAST(rhs AS VARCHAR2(10))
then 't'
else 'f'
end AS lt_vc,
case
when CAST(lhs AS VARCHAR2(10)) = CAST(rhs AS VARCHAR2(10))
then 't'
else 'f'
end AS eq_vc,
DUMP(lhs) AS dmp_lsh_raw,
DUMP(CAST(lhs AS CHAR(2))) AS dmp_lhs_ch,
DUMP(CAST(lhs AS VARCHAR2(10))) AS dmp_lhs_vc,
DUMP(rhs) AS dmp_rsh_raw,
DUMP(CAST(rhs AS CHAR(2))) AS dmp_rhs_ch,
DUMP(CAST(rhs AS VARCHAR2(10))) AS dmp_rhs_vc
from cte_default_data_type;
那么输出是:
GT_CH LT_CH EQ_CH COMP GT_VC LT_VC EQ_VC DMP_LSH_RAW DMP_LHS_CH DMP_LHS_VC DMP_RSH_RAW DMP_RHS_CH DMP_RHS_VC f f f eq t f f Typ=96 Len=2: 97,32 Typ=96 Len=2: 97,32 Typ=1 Len=2: 97,32 Typ=96 Len=1: 97 Typ=96 Len=2: 97,32 Typ=1 Len=1: 97
前3CASE
个表达中没有一个是正确的(如问题所示).然而,DUMP
的输出与前面的查询相同.
这种情况不应该发生,因为这些值要么作为CHAR
个数据类型进行比较,在这种情况下,应该应用空白填充比较语义,要么作为VARCHAR2
个数据类型进行比较,在这种情况下,应该应用非填充比较语义,在这种情况下,它似乎在使用其他内容,不遵循任何规则,并且不是预期的行为.
为了解决这个问题,使用CAST
为文本提供一个显式的数据类型,因为这似乎"解决"了这个问题.
db<>fiddle 100