ACE/Jet 的 IN 运算符或 CHECK 约束是否“损坏”？

Question

考虑下表的约束，虽然有点愚蠢，但足够简单来证明我的观点。 请注意，为了使事情变得非常简单，约束条件仅涉及文字值。 ID 列之所以存在，是因为表必须至少有一列 (!!)，但该列不参与约束。 虽然有点愚蠢（因此得名），但这是完全合法的语法，类似于将 WHERE 0 = 1 添加到 SELECT 查询以确保它返回零行。

（标准 SQL DDL 代码，将在 ACE/Jet 的 ANSI-92 查询模式下执行）

CREATE TABLE Test1 
(
   ID INTEGER NOT NULL, 
   CONSTRAINT daft_1 CHECK (5 = NULL)
);

以下 INSERT 成功：

INSERT INTO Test1 (ID) VALUES (1);

这是预期的行为。 谓词 5 = NULL 的计算结果应为 UNKNOWN。 INSERT 被“假定是无罪的”并成功。 那里没问题。

考虑使用 IN 运算符的类似示例：

CREATE TABLE Test2 
(
   ID INTEGER NOT NULL, 
   CONSTRAINT daft_2 CHECK (5 IN (0, 1, NULL))
);

以下 INSERT 失败，因为约束被咬住：

INSERT INTO Test2 (ID) VALUES (1);

这是意外的行为，至少对我来说是这样。 我希望 5 IN (0, 1, NULL) 再次被评估为 UNKNOWN 并且 INSERT 成功，原因与第一个例子。

我希望第二个示例中的逻辑与以下第三个示例相同：

CREATE TABLE Test3
(
   ID INTEGER NOT NULL, 
   CONSTRAINT daft_3 CHECK((5 = 0) OR (5 = 1) OR (5 = NULL))
);

以下 INSERT 成功：

INSERT INTO Test3 (ID) VALUES (1);

这是预期的行为。

我已经在 SQL Server 上测试了所有三个示例，并且一切都按我的预期工作，即所有三个 INSERT 语句都成功。 事实上，检查信息架构表明，对于第二个示例，SQL Server “有帮助”(grrr) 重写了约束子句，将 IN 运算符替换为

((5)=NULL OR (5)=(1) OR (5)=(0))

因此，对于 ACE/Jet，什么是“损坏” ' 这里：IN 运算符还是 CHECK 约束？

下面是一些使用 NULLable 列重现问题的 VBA 代码； 还演示了删除约束可以使 INSERT 成功：

Sub TestJetInCheck()

  On Error Resume Next
  Kill Environ$("temp") & "\DropMe.mdb"
  On Error GoTo 0

  Dim cat
  Set cat = CreateObject("ADOX.Catalog")

  With cat
    .Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"

    With .ActiveConnection

      Dim Sql As String
      Sql = _
          "CREATE TABLE Test" & vbCr & _
          "(" & vbCr & _
          "   ID INTEGER, " & vbCr & _
          "   CONSTRAINT daft_constraint " & vbCr & _
          "      CHECK (5 IN (0, 1, NULL))" & vbCr & _
          ");"
      .Execute Sql

      Sql = "INSERT INTO Test (ID) VALUES (1);"

      On Error Resume Next
      .Execute Sql
      If Err.Number <> 0 Then
        MsgBox Err.Description
      Else
        MsgBox "{{no error}}"
      End If
      On Error GoTo 0

      .Execute "ALTER TABLE Test DROP CONSTRAINT daft_constraint;"

      On Error Resume Next
      .Execute Sql
      If Err.Number <> 0 Then
        MsgBox Err.Description
      Else
        MsgBox "{{no error}}"
      End If
      On Error GoTo 0

    End With

    Set .ActiveConnection = Nothing
  End With
End Sub

编辑：我只是想尝试一下：

SELECT NULL IN (1); -- 返回 NULL

SELECT 1 IN (NULL) -- 返回零，即 FALSE

Answer 1

我可以通过创建验证规则来专门消除 CHECK 约束（@David W. Fenton：抱歉，我发现 SQL DDL 和 ADO 比 DAO 更容易编写，但感谢您的启发）：

Sub TestJetInValidationRule()

  On Error Resume Next
  Kill Environ$("temp") & "\DropMe.mdb"
  On Error GoTo 0

  Dim cat
  Set cat = CreateObject("ADOX.Catalog")

  With cat
    .Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"

    With .ActiveConnection

      Dim Sql As String
      Sql = _
          "CREATE TABLE Test" & vbCr & _
          "(" & vbCr & _
          "   ID INTEGER" & vbCr & _
          ");"
      .Execute Sql
    End With

    ' Create Validation Rules
    Dim jeng
    Set jeng = CreateObject("JRO.JetEngine")
    jeng.RefreshCache .ActiveConnection

    .Tables("Test").Columns("ID") _
    .Properties("Jet OLEDB:Column Validation Rule").value = _
    "5 IN (0, 1, NULL)"

    jeng.RefreshCache .ActiveConnection

    With .ActiveConnection

      Sql = "INSERT INTO Test (ID) VALUES (1);"

      On Error Resume Next
      .Execute Sql
      If Err.Number <> 0 Then
        MsgBox Err.Description
      Else
        MsgBox "{{no error}}"
      End If
      On Error GoTo 0

    End With

    Set .ActiveConnection = Nothing
  End With
End Sub

验证规则咬住并且 INSERT 失败。 因此，我怀疑 IN 子句的行为异常。 我将来将使用嵌套 OR 子句！