sql - 为什么在 where 子句中对 char 字段使用 coalesce 时查询结果会发生变化？

Question

在 Oracle 数据库中的合并字符字段上使用 where 子句进行查询时，我看到了一些意外行为。

好像是

的结果

CASE WHEN COALESCE(char_field, 'some_val') = 'someOtherVal'

不同于

的结果

CASE WHEN char_field = 'someOtherVal'

我注意到这个奇怪输出的具体比较是“介于”、“在”和“等于”。这些是我看到的奇怪输出:

1. Between 在上端似乎是非包容性的
2. In 和 equals 每次比较都返回 false

这里有一些 sql 来复制奇怪的东西:

CREATE TABLE delete_me( some_char CHAR(8) );

INSERT ALL   
  INTO delete_me (some_char) VALUES ('1')
  INTO delete_me (some_char) VALUES ('2')
  INTO delete_me (some_char) VALUES ('4')
  INTO delete_me (some_char) VALUES ('5')
  INTO delete_me (some_char) VALUES ('abc1')
  INTO delete_me (some_char) VALUES (null)
SELECT 1 FROM DUAL;

SELECT some_char,
  COALESCE(some_char, 'wasNull') AS coalesce_some_char,
  CASE
    WHEN (some_char BETWEEN '1' AND '5')
    THEN 'true'
    ELSE 'false'
  END AS between_1_5,
  CASE
    WHEN (COALESCE(some_char, 'wasNull') BETWEEN '1' AND '5')
    THEN 'true'
    ELSE 'false'
  END AS coalesce_between_1_5,
  CASE
    WHEN (some_char IN ('1', '5'))
    THEN 'true'
    ELSE 'false'
  END AS in_1_5,
  CASE
    WHEN (COALESCE(some_char, 'wasNull') IN ('1', '5'))
    THEN 'true'
    ELSE 'false'
  END AS coalesce_in_1_5,
  CASE
    WHEN (some_char = 'abc1')
    THEN 'true'
    ELSE 'false'
  END AS equals_abc1,
  CASE
    WHEN (COALESCE(some_char, 'wasNull') = 'abc1')
    THEN 'true'
    ELSE 'false'
  END AS coalesce_equals_abc1
FROM delete_me;

我希望合并字段的比较输出与非合并字段的比较输出匹配所有运算符(IS NULL 除外)。

有谁知道为什么这些结果不匹配？

Answer 1

您的问题出在 some_char 的数据类型上。当 CHAR 类型的列与字符串进行比较时，Oracle 将字符串填充到列的长度(参见 docs)。在您进行的测试中，值的长度匹配('1' 与 '1')或完全不同('1' vs 'abc1') 所以一切正常。但是，当您在 CHAR 字段上使用 COALESCE 时，COALESCE 的输出是作为 返回的完全空白的填充列值 >VARCHAR(请参阅 docs for NVL)，因此比较字符串不填充空白，然后您比较 '1 ' 与'1'，失败了。有几种方法可以解决这个问题。您可以TRIM COALESCE 的输出，即

TRIM(COALESCE(some_char, 'wasNull'))

或者将 some_char 的数据类型更改为 VARCHAR(8)。

我在 dbfiddle 上做了所有这些的演示