sql - SQL 或 PostgreSQL 规范中的哪些内容解释了数组与基元的比较？

Question

我想了解为什么您可以将空值作为数组内部的相等值进行比较，而不是将其作为基元进行比较。

例如，下面的查询演示:

SELECT NULL = NULL AS does_not_equal
     , NULL::bool = NULL::bool AS does_equal
     , NULL::int =  NULL::int AS does_equal
     , NULL::text = NULL::text AS does_equal
     , ARRAY[NULL] = ARRAY[NULL] AS does_equal
     , ARRAY[NULL]::bool[] = ARRAY[NULL]::bool[] AS does_equal
     , ARRAY[NULL]::int[] = ARRAY[NULL]::int[] AS does_equal
     , ARRAY[NULL]::text[] = ARRAY[NULL]::text[] AS does_equal
;

我理解为什么 NULL = NULL 对于基元来说是 NULL，我也想我理解为什么比较复合类型会给出一个真正的比较，因为 *= 运算符看起来在类型的二进制值。但是数组似乎不使用 *= 运算符，到目前为止，我在搜索中还没有找到任何解释它的内容。

Answer 1

这是一个复杂的故事。

这是 SQL 标准 (ISO/IEC 9075-2) 在“8.2<比较谓词>”:

Let XV and YV be two values represented by <value expression>s X and Y, respectively. The result of:

<em>X</em> <comp op> <em>Y</em>

is determined as follows:
Case:

1. If either XV or YV is the null value, then

   <em>X</em> <comp op> <em>Y</em>

   is Unknown.

2. Otherwise,

   Case:

   1. If the declared types of XV and YV are row types with degree N, then let X_i, 1 (one) ≤ i ≤ N, denote a <value expression> whose value and declared type is that of the i-th field of XV and let Y_i denote a <value expression> whose value and declared type is that of the i-th field of YV.
      The result of

      <em>X</em> <comp op> <em>Y</em>

      is determined as follows:

      1. X = Y is True if and only if X_i = Y_i is True for all i.
      2. X < Y is True if and only if X_i = Y_i is True for all i < n and X_n = Y_n for some n.
      3. X = Y is False if and only if NOT (X_i = Y_i) is True for some i.
      4. X < Y is False if and only if X = Y is True or Y < X is True.
      5. X <comp op> Y is Unknown if X <comp op> Y is neither True nor False.

   2. If the declared types of XV and YV are array types with cardinalities N1 and N2, respectively, then let X_i, 1 (one) ≤ i ≤ N1, denote a <value expression> whose value and declared type is that of the i-th element of XV and let Y_i denote a <value expression> whose value and declared type is that of the i-th element of YV. The result of

      <em>X</em> <comp op> <em>Y</em>

      is determined as follows:

      1. X = Y is True if N1 = 0 (zero) and N2 = 0 (zero).
      2. X = Y is True if N1 = N2 and, for all i, X_i = Y_i is True.
      3. X = Y is False if and only if N1 ≠ N2 or NOT (X_i = Y_i) is True, for some i.
      4. X <comp op> Y is Unknown if X <comp op> Y is neither True nor False.

这很令人困惑，例如，我看不到任何规范，除了相等运算符之外，我还没有将数组与数组进行比较。

该标准偶尔也会自相矛盾。

*=运算符仅存在于 record ，不适用于数组。它与 = 几乎相同，但效率更高，因为如果值的长度不同，它不会“解冻”值。

最好是按原样接受语义。

PostgreSQL 在这方面似乎没有遵循标准。

NULL 本身令人困惑，复合或数组中的 NULL 更是如此。