Test Corpus Reference

Relevant source files

• test/evidence/in1.test
• test/evidence/slt_lang_aggfunc.test
• test/evidence/slt_lang_createtrigger.test
• test/index/between/1/slt_good_0.test

Purpose and Scope

This document provides a comprehensive reference for the test corpus contained in the test/ directory. The corpus consists of SQL Logic Test files organized into two primary categories: evidence tests that validate SQL language specification compliance, and index tests that verify query optimization behavior.

For information about the test file format syntax and directives, see Test File Format Specification. For guidance on running and integrating these tests, see Usage Guide.

Corpus Organization

The test corpus is organized into a two-tier directory structure based on test purpose and functionality:

Sources: test/evidence/in1.test:1-1156 test/evidence/slt_lang_aggfunc.test:1-500 test/evidence/slt_lang_createtrigger.test:1-220

graph TB
    subgraph "test/ Directory"
        ROOT["test/"]
subgraph "Evidence Tests"
            EV_DIR["test/evidence/"]
EV_DDL["DDL Tests\nCREATE/DROP statements"]
EV_DML["DML Tests\nUPDATE/REPLACE"]
EV_DQL["Query Tests\nIN/NOT IN, BETWEEN"]
EV_AGG["Aggregate Functions\ncount, avg, sum, etc."]
end
        
        subgraph "Index Tests"
            IDX_DIR["test/index/"]
IDX_BETWEEN["BETWEEN Tests\nOperator optimization"]
IDX_VARIANTS["Test Variants\nbetween/1/, between/10/, etc."]
end
    end
    
 
   ROOT --> EV_DIR
 
   ROOT --> IDX_DIR
 
   EV_DIR --> EV_DDL
 
   EV_DIR --> EV_DML
 
   EV_DIR --> EV_DQL
 
   EV_DIR --> EV_AGG
 
   IDX_DIR --> IDX_BETWEEN
 
   IDX_BETWEEN --> IDX_VARIANTS

Test Corpus Structure

The following diagram maps the logical test categories to their physical file locations and key test constructs:

Sources: test/evidence/in1.test:1-1156 test/evidence/slt_lang_aggfunc.test:1-500 test/evidence/slt_lang_createtrigger.test:1-220

graph LR
    subgraph "Evidence Tests - DDL"
        CREATE_TRIG["slt_lang_createtrigger.test\nCREATE TRIGGER statements"]
CREATE_VIEW["slt_lang_createview.test\nCREATE VIEW statements"]
DROP_INDEX["slt_lang_dropindex.test\nDROP INDEX statements"]
DROP_TABLE["slt_lang_droptable.test\nDROP TABLE statements"]
DROP_TRIG["slt_lang_droptrigger.test\nDROP TRIGGER statements"]
DROP_VIEW["slt_lang_dropview.test\nDROP VIEW statements"]
REINDEX["slt_lang_reindex.test\nREINDEX command"]
end
    
    subgraph "Evidence Tests - DML"
        REPLACE["slt_lang_replace.test\nREPLACE/INSERT OR REPLACE"]
UPDATE["slt_lang_update.test\nUPDATE statements"]
end
    
    subgraph "Evidence Tests - Query Operators"
        IN1["in1.test\nIN/NOT IN edge cases"]
IN2["in2.test\nIN/NOT IN validation"]
AGGFUNC["slt_lang_aggfunc.test\nAggregate functions"]
end
    
    subgraph "Index Tests"
        BETWEEN_DIR["index/between/"]
BETWEEN_1["between/1/slt_good_0.test"]
BETWEEN_10["between/10/slt_good_0-5.test"]
BETWEEN_100["between/100/slt_good_0.test"]
end
    
 
   BETWEEN_DIR --> BETWEEN_1
 
   BETWEEN_DIR --> BETWEEN_10
 
   BETWEEN_DIR --> BETWEEN_100

Evidence Tests

Evidence tests validate SQL language specification compliance by testing specific behaviors documented in the SQLite specification. Each test file is organized around a particular SQL construct or operator, with inline references to the specification using EVIDENCE-OF markers.

Evidence Test File Structure

Evidence tests follow a consistent pattern with specification references and cross-database compatibility directives:

IN/NOT IN Operator Tests

The in1.test and in2.test files provide comprehensive coverage of IN and NOT IN operators, including edge cases with NULL values and empty sets.

Key Test Scenarios:

stateDiagram-v2
    [*] --> EmptySet : Empty RHS test
    [*] --> NullHandling : NULL value test
    [*] --> TableOperand : Table as operand
    
    EmptySet --> ValidateIN : SELECT 1 IN ()
    EmptySet --> ValidateNOTIN : SELECT 1 NOT IN ()
    
    NullHandling --> NullInEmpty : SELECT null IN ()
    NullHandling --> NullInList : SELECT 1 IN (2,3,null)
    
    TableOperand --> DirectTable : SELECT 1 IN t1
    TableOperand --> Subquery : SELECT 1 IN (SELECT * FROM t1)
    
    ValidateIN --> Result["Result : false (0)"]
    ValidateNOTIN --> Result2["Result : true (1)"]
    NullInEmpty --> Result3["Result : false (0)"]
    NullInList --> Result4["Result : NULL"]

Sources: test/evidence/in1.test:11-26 test/evidence/in1.test:329-336

Example Test Structure:

From test/evidence/in1.test:11-26:

# EVIDENCE-OF: R-52275-55503 When the right operand is an empty set, the
# result of IN is false and the result of NOT IN is true, regardless of
# the left operand and even if the left operand is NULL.

onlyif sqlite # empty RHS
query I nosort
SELECT 1 IN ()
----
0

Cross-Database Compatibility:

The IN/NOT IN tests demonstrate platform-specific behavior handling:

Sources: test/evidence/in1.test:1-10 test/evidence/in1.test:22-26 test/evidence/in1.test:69-73

graph TB
    subgraph "Test Setup"
        TABLE["CREATE TABLE t1(x INTEGER, y VARCHAR(8))"]
DATA["INSERT VALUES: (1,'true'), (0,'false'), (NULL,'NULL')"]
end
    
    subgraph "Aggregate Functions"
        COUNT["count(x)\ncount(DISTINCT x)"]
AVG["avg(x)\navg(DISTINCT x)"]
SUM["sum(x)\nsum(DISTINCT x)"]
TOTAL["total(x)\ntotal(DISTINCT x)"]
MIN["min(x)\nmin(DISTINCT x)"]
MAX["max(x)\nmax(DISTINCT x)"]
CONCAT["group_concat(x)\ngroup_concat(x,':')"]
end
    
    subgraph "Test Scenarios"
        NULL_HANDLING["NULL value handling"]
DISTINCT["DISTINCT keyword behavior"]
TYPE_COERCION["String to number coercion"]
end
    
 
   TABLE --> COUNT
 
   TABLE --> AVG
 
   TABLE --> SUM
 
   TABLE --> TOTAL
 
   TABLE --> MIN
 
   TABLE --> MAX
 
   TABLE --> CONCAT
    
 
   COUNT --> NULL_HANDLING
 
   AVG --> DISTINCT
 
   SUM --> TYPE_COERCION

Aggregate Function Tests

The slt_lang_aggfunc.test file validates aggregate function behavior including count(), avg(), sum(), total(), min(), max(), and group_concat().

Aggregate Functions Test Coverage:

Sources: test/evidence/slt_lang_aggfunc.test:1-16 test/evidence/slt_lang_aggfunc.test:21-61

Key Behavioral Rules:

From test/evidence/slt_lang_aggfunc.test:24-31:

# EVIDENCE-OF: R-00466-56349 In any aggregate function that takes a
# single argument, that argument can be preceded by the keyword
# DISTINCT.

query I nosort
SELECT count(DISTINCT x) FROM t1
----
2

Aggregate Function Behavior Matrix:

Sources: test/evidence/slt_lang_aggfunc.test:83-104 test/evidence/slt_lang_aggfunc.test:198-210 test/evidence/slt_lang_aggfunc.test:372-418

graph LR
    subgraph "Timing Options"
        BEFORE["BEFORE"]
AFTER["AFTER"]
INSTEAD["INSTEAD OF"]
end
    
    subgraph "Events"
        INSERT["INSERT"]
UPDATE["UPDATE"]
DELETE["DELETE"]
end
    
    subgraph "Trigger Examples"
        T1["t1r1: UPDATE ON t1"]
T2["t1r2: DELETE ON t1"]
T3["t1r3: INSERT ON t1"]
T5["t1r5: AFTER DELETE"]
T6["t1r6: AFTER INSERT"]
T7["t1r7: AFTER UPDATE"]
T8["t1r8: BEFORE DELETE"]
T9["t1r9: BEFORE INSERT"]
T10["t1r10: BEFORE UPDATE"]
end
    
 
   BEFORE --> DELETE
 
   BEFORE --> INSERT
 
   BEFORE --> UPDATE
 
   AFTER --> DELETE
 
   AFTER --> INSERT
 
   AFTER --> UPDATE

DDL Tests - CREATE TRIGGER

The slt_lang_createtrigger.test file validates trigger creation and behavior across different timing options and events.

Trigger Timing and Event Matrix:

Sources: test/evidence/slt_lang_createtrigger.test:21-96

Example Test Pattern:

From test/evidence/slt_lang_createtrigger.test:21-29:

# EVIDENCE-OF: R-10346-40046 The CREATE TRIGGER statement is used to add
# triggers to the database schema.

statement ok
CREATE TRIGGER t1r1 UPDATE ON t1 BEGIN SELECT 1; END;

# already exists
statement error
CREATE TRIGGER t1r1 UPDATE ON t1 BEGIN SELECT 1; END;

Platform Support:

From test/evidence/slt_lang_createtrigger.test:18-19:

onlyif mssql
halt

The CREATE TRIGGER tests are skipped on MSSQL due to syntax differences in trigger creation.

Sources: test/evidence/slt_lang_createtrigger.test:18-19 test/evidence/slt_lang_createtrigger.test:75-96

Index and Optimization Tests

Index tests focus on query optimization behavior, particularly how the query planner handles indexed columns with various operators. These tests are organized by operator type and data characteristics.

BETWEEN Operator Tests

The test/index/between/ directory contains test variants that exercise BETWEEN operator optimization with different data distributions and index configurations.

Test Variant Organization:

These variants test how SQLite optimizes BETWEEN queries across different data scales and index configurations.

Sources: Based on system architecture diagrams and corpus organization patterns

Test Directive Usage Patterns

All test files use a consistent set of directives to control execution and validate results:

Common Directives:

Sources: test/evidence/in1.test:4-9 test/evidence/slt_lang_aggfunc.test1 test/evidence/slt_lang_createtrigger.test:18-19

Label-Based Result Validation

Test files use labels to group related assertions that should produce identical results:

Label Pattern:

From test/evidence/in1.test:69-78:

onlyif sqlite
query I nosort label-1
SELECT 1 IN t1
----
0

query I nosort label-1
SELECT 1 IN (SELECT * FROM t1)
----
0

This pattern validates that SELECT 1 IN t1 (SQLite-specific syntax) produces the same result as SELECT 1 IN (SELECT * FROM t1) (standard SQL syntax).

Sources: test/evidence/in1.test:69-111 test/evidence/slt_lang_aggfunc.test:187-196

flowchart TD
 
   START["Test File Start"] --> CHECK_PLATFORM{"Platform-specific\nhalt directives"}
CHECK_PLATFORM -->|onlyif mssql halt| HALT_MSSQL["Skip entire file\nfor MSSQL"]
CHECK_PLATFORM -->|onlyif oracle halt| HALT_ORACLE["Skip entire file\nfor Oracle"]
CHECK_PLATFORM --> CONTINUE["Continue processing"]
CONTINUE --> TEST_BLOCK["Test Block"]
TEST_BLOCK --> SQLITE_ONLY{"onlyif sqlite"}
TEST_BLOCK --> SKIP_MYSQL{"skipif mysql"}
SQLITE_ONLY -->|Yes| SQLITE_TEST["Execute SQLite-specific test"]
SQLITE_ONLY -->|No| PORTABLE["Execute portable test"]
SKIP_MYSQL -->|Match| SKIP["Skip test block"]
SKIP_MYSQL -->|No match| EXECUTE["Execute test"]
SQLITE_TEST --> NEXT["Next test"]
PORTABLE --> NEXT
 
   SKIP --> NEXT
 
   EXECUTE --> NEXT
    
 
   HALT_MSSQL --> END["End of file"]
HALT_ORACLE --> END
 
   NEXT --> END

Cross-Database Compatibility Patterns

The corpus demonstrates sophisticated cross-database testing using conditional execution directives:

Sources: test/evidence/in1.test:1-10 test/evidence/in1.test:22-26 test/evidence/in1.test:69-73

Test Corpus Statistics

File Distribution:

Evidence Reference Format:

All evidence tests include specification references in the format:

# EVIDENCE-OF: R-XXXXX-XXXXX <description>

For example, from test/evidence/in1.test:11-13:

# EVIDENCE-OF: R-52275-55503 When the right operand is an empty set, the
# result of IN is false and the result of NOT IN is true, regardless of
# the left operand and even if the left operand is NULL.

These references link directly to the SQLite specification document.

Sources: test/evidence/in1.test:11-20 test/evidence/slt_lang_aggfunc.test:24-26 test/evidence/slt_lang_createtrigger.test:21-22

Summary

The test corpus provides comprehensive coverage of SQL language features through two complementary categories:

1. Evidence Tests (test/evidence/): Validate SQL specification compliance across DDL, DML, and query operations with explicit specification references
2. Index Tests (test/index/): Verify query optimization behavior across different data scales and index configurations

For detailed information about specific test categories, see:

• SQL Language Evidence Tests: Section 3.1
• Index and Optimization Tests: Section 3.2
• Test file format specification: Section 6
• Running and integrating tests: Section 5

Sources: test/evidence/in1.test:1-1156 test/evidence/slt_lang_aggfunc.test:1-500 test/evidence/slt_lang_createtrigger.test:1-220