IN and NOT IN Operator Tests

Relevant source files

• test/evidence/in1.test
• test/evidence/in2.test

Purpose and Scope

This page documents the test corpus for SQL IN and NOT IN operators, specifically covering the behavior defined by the SQL specification and SQLite's extensions. These tests validate how IN and NOT IN operators behave when operating on empty sets, NULL values, and various combinations of left and right operands across different data types.

The tests verify compliance with four key SQL language specifications regarding IN/NOT IN operator behavior, particularly focusing on:

• Empty set handling (a SQLite-specific extension)
• NULL value interactions
• Scalar vs. row value expressions
• Cross-database compatibility differences

For information about aggregate functions tested with these operators, see Aggregate Function Tests. For broader query operator testing, see Query Operator Tests.

Sources: test/evidence/in1.test:1-1156 test/evidence/in2.test:1-314

Evidence Specifications Tested

The test files validate four specific evidence codes from the SQLite documentation that define the normative behavior of IN and NOT IN operators:

Sources: test/evidence/in1.test:11-19 test/evidence/in2.test:2-21 test/evidence/in2.test:292-294

Behavior Matrix

The IN and NOT IN operators follow a deterministic five-row decision matrix that determines result values based on the characteristics of the left and right operands:

Sources: test/evidence/in1.test:329-336 test/evidence/in2.test:14-21

stateDiagram-v2
    [*] --> CheckRHS
    
    state CheckRHS {
        [*] --> IsEmptySet : Right operand
        IsEmptySet --> Row2 : Yes - Empty set
        IsEmptySet --> CheckLHSNull : No - Has values
        
        CheckLHSNull --> Row5 : LHS is NULL
        CheckLHSNull --> CheckFound : LHS is not NULL
        
        CheckFound --> CheckRHSNull : LHS not found in RHS
        CheckFound --> Row3 : LHS found in RHS
        
        CheckRHSNull --> Row4 : RHS contains NULL
        CheckRHSNull --> Row1 : RHS has no NULL
    }
    
    Row1 --> Result1["IN : false NOT IN - true"]
    Row2 --> Result2["IN : false NOT IN - true"]
    Row3 --> Result3["IN : true NOT IN - false"]
    Row4 --> Result4["IN : NULL NOT IN - NULL"]
    Row5 --> Result5["IN : NULL NOT IN - NULL"]
    
    Result1 --> [*]
    Result2 --> [*]
    Result3 --> [*]
    Result4 --> [*]
    Result5 --> [*]
    
    note right of Row1
        LHS not NULL, not found,
        RHS has no NULL, non-empty
    end note
    
    note right of Row2
        RHS is empty set
        (SQLite extension)
    end note
    
    note right of Row3
        LHS not NULL, found in RHS
        (RHS may or may not have NULL)
    end note
    
    note right of Row4
        LHS not NULL, not found,
        but RHS contains NULL
    end note
    
    note right of Row5
        LHS is NULL
        (regardless of RHS content)
    end note

Empty Set Handling (SQLite Extension)

SQLite allows empty lists () as the right-hand side of IN and NOT IN operators, which is an extension beyond SQL92 and most other database implementations. This behavior is tested extensively as Row 2 of the behavior matrix.

Empty Set Test Directives

The tests use conditional execution to handle databases that do not support empty sets:

flowchart TD
 
   Start["Test: x IN ()"] --> CheckDB{"Database\nplatform?"}
CheckDB -->|SQLite| ExecuteTest["Execute:\nonlyif sqlite\nquery I nosort"]
CheckDB -->|MySQL| Skip1["Skip:\nskipif mysql\n(empty RHS)"]
CheckDB -->|MSSQL| Skip2["Skip:\nskipif mssql\n(empty RHS)"]
CheckDB -->|Oracle| Skip3["Skip:\nskipif oracle\n(empty RHS)"]
ExecuteTest --> Verify["Expect: 0 for IN\nExpect: 1 for NOT IN"]
Skip1 --> NextTest["Continue to next test"]
Skip2 --> NextTest
 
   Skip3 --> NextTest
 
   Verify --> NextTest

Examples of Empty Set Tests

Sources: test/evidence/in1.test:22-65 test/evidence/in1.test:227-327 test/evidence/in2.test:76-145

Table-as-Operand Syntax (SQLite Extension)

SQLite supports a non-standard syntax where a table name can be used directly as the right operand of IN/NOT IN, which is semantically equivalent to (SELECT * FROM table). This is tested extensively in in1.test.

graph TB
    subgraph "SQLite-Specific Syntax"
        TableDirect["x IN table_name"]
end
    
    subgraph "Standard SQL Equivalent"
        Subquery["x IN (SELECT * FROM table_name)"]
end
    
    subgraph "Test Coverage"
        EmptyTable["Empty tables:\nt1, t2, t3"]
PopulatedTable["Populated tables:\nt4, t5, t6"]
NullTable["Tables with NULL:\nt4n, t6n, t7n, t8n"]
end
    
 
   TableDirect -.->|SQLite converts to| Subquery
    
 
   EmptyTable --> TestEmpty["Labels 1-18:\nTest against empty sets"]
PopulatedTable --> TestFound["Labels 19-46:\nTest match/no-match"]
NullTable --> TestNull["Labels 47-72:\nTest NULL handling"]
style TableDirect fill:#f9f9f9
    style Subquery fill:#f9f9f9

Test Table Schema

The test files create multiple tables with different configurations to test various scenarios:

Sources: test/evidence/in1.test:66-206 test/evidence/in1.test:359-439

Test Structure and Organization

The tests are organized by the five rows of the behavior matrix, with each row testing specific combinations of conditions:

Sources: test/evidence/in1.test:1-1156 test/evidence/in2.test:1-314

graph LR
    subgraph "in1.test Structure"
        Header["File header:\nLines 1-10\nCross-DB directives"]
Row2Tests["Row 2: Empty set\nLines 11-327\nEvidence R-52275-55503\nR-64309-54027"]
MatrixHeader["Matrix documentation\nLines 329-336\nEvidence R-50221-42915"]
Row1Tests["Row 1: No match, no NULL\nLines 337-549"]
Row3Tests["Row 3: Match found\nLines 551-792"]
Row4Tests["Row 4: No match, has NULL\nLines 793-902"]
Row5Tests["Row 5: LHS is NULL\nLines 903-1156"]
end
    
 
   Header --> Row2Tests
 
   Row2Tests --> MatrixHeader
 
   MatrixHeader --> Row1Tests
 
   Row1Tests --> Row3Tests
 
   Row3Tests --> Row4Tests
 
   Row4Tests --> Row5Tests

Test Label System

The in1.test file uses a label system to group equivalent tests that should produce the same results. Labels range from label-1 to label-72 and allow cross-database validation where the same label should yield identical results across different syntactic forms.

Label Categories

Example Label Usage

Both queries with label-1 expect the same result (0), demonstrating that the table-as-operand syntax is equivalent to the subquery syntax.

Sources: test/evidence/in1.test:69-158

Cross-Database Compatibility

The tests handle significant differences between database platforms regarding IN/NOT IN operator support:

Platform Support Matrix

Database-Specific Directives

MySQL-Specific Handling

MySQL has additional compatibility issues noted in the test file with skip directives:

These failures are documented at specific lines where MySQL's NULL handling in subqueries diverges from the expected behavior.

Sources: test/evidence/in1.test:1-10 test/evidence/in1.test:951-975 test/evidence/in2.test:78-80 test/evidence/in2.test:303-310

graph TB
    subgraph "Literal Value Tests"
        IntLit["Integer: 1, 2, 3, 4"]
FloatLit["Float: 1.23"]
StringLit["String: 'hello', 'a', 'b'"]
BlobLit["Blob: x'303132'"]
NullLit["NULL literal"]
end
    
    subgraph "Table Column Tests"
        IntCol["INTEGER columns\nt1-t6, t4n, t6n"]
TextCol["TEXT columns\nt7, t8, t7n, t8n"]
MixedCol["VARCHAR (Oracle)\nOracle compatibility"]
end
    
 
   IntLit --> EmptySetTests["Empty set tests\nlines 22-327"]
FloatLit --> EmptySetTests
 
   StringLit --> EmptySetTests
 
   BlobLit --> EmptySetTests
 
   NullLit --> EmptySetTests
    
 
   IntCol --> MatrixTests["Matrix behavior tests\nlines 337-1156"]
TextCol --> MatrixTests
 
   MixedCol --> MatrixTests

Data Type Coverage

The tests validate IN/NOT IN behavior across multiple data types to ensure type-agnostic operator behavior:

Tested Data Types

Type-Specific Test Examples

Sources: test/evidence/in1.test:22-327 test/evidence/in2.test:23-29 test/evidence/in2.test:398-404 test/evidence/in2.test:421-427

Scalar Subquery Validation

The test file in2.test includes validation that subqueries on the right side of IN/NOT IN must return a single column (scalar subquery) when the left expression is not a row value:

Valid and Invalid Subqueries

All multi-column subquery tests are expected to produce a statement error, validating evidence code R-35033-20570.

Sources: test/evidence/in2.test:292-314

sequenceDiagram
    participant Runner as "Test Runner"
    participant in1 as "in1.test"
    participant in2 as "in2.test"
    participant DB as "Database Engine"
    
    Runner->>in1: Start execution
    in1->>in1: Check: onlyif mssql (line 4)
    in1->>in1: Check: onlyif oracle (line 8)
    
    alt MSSQL or Oracle detected
        in1->>Runner: halt - skip entire file
    else SQLite or MySQL
        in1->>DB: CREATE TABLE t1, t2, t3...
        in1->>DB: Test empty set cases\n(onlyif sqlite directives)
        DB-->>in1: Results for SQLite only
        
        in1->>DB: INSERT test data into t4-t8
        in1->>DB: Test matrix rows 1-5
        DB-->>in1: Results
        
        in1->>Runner: Complete - 1156 lines
    end
    
    Runner->>in2: Start execution
    in2->>DB: CREATE TABLE t1(x, y)
    in2->>DB: INSERT test data
    
    in2->>DB: Test each matrix row\nwith WHERE clauses
    DB-->>in2: Result counts
    
    in2->>DB: Test scalar subquery validation\n(expect errors for multi-column)
    DB-->>in2: Errors for invalid queries
    
    in2->>Runner: Complete - 314 lines

Test Execution Flow

The following diagram shows how the test runner processes in1.test and in2.test:

Sources: test/evidence/in1.test:1-1156 test/evidence/in2.test:1-314

Summary of Test Coverage

The IN and NOT IN operator tests provide comprehensive validation across:

• 4 evidence specifications from SQLite documentation
• 5 rows in the behavior matrix covering all operator outcomes
• 12 test tables with varying schema configurations (primary keys, unique constraints, NULL values)
• 4 data types (integer, float, text, blob) plus NULL
• 72 labeled test groups ensuring syntax equivalence
• 300+ individual test queries across both test files
• 4 database platforms (full support on SQLite, partial on MySQL, excluded on MSSQL/Oracle)

The tests validate both standard SQL behavior and SQLite-specific extensions, ensuring compatibility and correctness across different database implementations.

Sources: test/evidence/in1.test:1-1156 test/evidence/in2.test:1-314