Is ACE/Jet's IN operator or CHECK constraint 'broken'?

Question

Consider the following table with a constraint that's a bit daft but simple enough to demonstrate my point. Note that, to keep things very simple, the constraint's criteria only involve literal values. The column ID only exists because a table must have at least one column (!!) but that column is not involved in the constraint. While a little daft (hence the name) this is perfectly legal syntax and similar to adding WHERE 0 = 1 to a SELECT query to ensure it returns zero rows.

(Standard SQL DDL code, will execute in ACE/Jet's ANSI-92 Query Mode)

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

The following INSERT succeeds:

INSERT INTO Test1 (ID) VALUES (1);

This is expected behaviour. The predicate 5 = NULL should evaluate to UNKNOWN. The INSERT is 'given the benefit of the doubt' and succeeds. No problem there.

Consider this similar example using the IN operator:

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

The following INSERT fails because the constraint bites:

INSERT INTO Test2 (ID) VALUES (1);

This is unexpected behviour, by me at least. I would expect the 5 IN (0, 1, NULL) to again be evaluated as UNKNOWN and the INSERT to succeed for the same reasons as the first example.

I would expect the logic in the second example to be the same as the following third example:

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

The following INSERT succeeds:

INSERT INTO Test3 (ID) VALUES (1);

This is expected behaviour.

I've tested all three examples on SQL Server and the all work as I expect i.e. all three INSERT statements succeed. In fact, examining the INFORMATION SCHEMA reveals that for the second example SQL Server as 'helpfully' (grrr) rewritten the constraint's clause to replace the IN operator with

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

So, for ACE/Jet, what is 'broken' here: the IN operator or the CHECK constraint?

Here's some VBA code to reproduce the problem using a NULLable column; also demonstrates that dropping the constraint allows the INSERT to succeed:

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

EDIT: I just thought to try this:

SELECT NULL IN (1); -- returns NULL

SELECT 1 IN (NULL) -- returns zero i.e. FALSE

Answer 1

I can eliminate the CHECK constraint specifically by creating a Validation Rule (@David W. Fenton: sorry, I find SQL DDL and ADO and easier to write than DAO but thanks for the inspiration):

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

The Validation Rule bites and INSERT fails. Therefore, I suspect the IN clause is behaving unexpectedly. I shall be using nested OR clauses in future!