SQL Language Evidence Tests

Relevant source files

• test/evidence/slt_lang_createtrigger.test
• test/evidence/slt_lang_createview.test
• test/evidence/slt_lang_update.test

Purpose and Scope

SQL Language Evidence Tests validate that SQLite's implementation conforms to its documented SQL language specification. These tests verify behavior for Data Definition Language (DDL), Data Manipulation Language (DML), and Data Query Language (DQL) commands by executing SQL statements and comparing results against expected outcomes. Each test includes citations to SQLite's official documentation using evidence markers (e.g., EVIDENCE-OF: R-xxxxx-xxxxx), establishing traceability between test cases and specification requirements.

This page provides an overview of the evidence test system. For detailed coverage of specific command categories, see:

For tests focused on query optimization and index behavior, see Index and Optimization Tests #3.2.

Evidence Test Organization

Evidence tests are located in the test/evidence/ directory and follow the naming convention slt_lang_<command>.test. Each file contains comprehensive test cases for a specific SQL language feature, organized with evidence citations that map to SQLite's documentation.

Evidence Test Files by Category

Sources: test/evidence/slt_lang_createtrigger.test:1-220 test/evidence/slt_lang_createview.test:1-129 test/evidence/slt_lang_update.test:1-198

Evidence Citation System

Evidence Marker Taxonomy

Evidence Marker Format: Each marker follows the pattern R-{5-digit}-{5-digit} where the numbers serve as unique identifiers in SQLite's documentation system. Test files use comment syntax to associate evidence markers with test cases.

Sources: test/evidence/slt_lang_createtrigger.test:21-22 test/evidence/slt_lang_createtrigger.test:31-32 test/evidence/slt_lang_createview.test:42-45 test/evidence/slt_lang_createview.test:87-88

Evidence-to-Test Mapping Pattern

Evidence tests follow a consistent pattern where documentation citations precede the corresponding test directives:

# EVIDENCE-OF: R-xxxxx-xxxxx <Documentation excerpt>
statement ok
<SQL command to validate the documented behavior>

Example from CREATE TRIGGER tests:

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

Test Execution Flow for Evidence Tests

The execution model processes test files sequentially, evaluating platform directives before each test block. Evidence citations are parsed as documentation but do not affect test execution—they serve as traceability markers linking test cases to specification requirements.

Sources: test/evidence/slt_lang_createtrigger.test:1-16 test/evidence/slt_lang_createtrigger.test:18-19 test/evidence/slt_lang_createview.test:68-86

Common Test Schema Initialization Pattern

Evidence tests establish a consistent baseline schema used across multiple test files:

This schema pattern appears in test/evidence/slt_lang_createtrigger.test:3-16 test/evidence/slt_lang_createview.test:3-16 and test/evidence/slt_lang_update.test:3-16 providing a consistent testing foundation across evidence test files.

Sources: test/evidence/slt_lang_createtrigger.test:3-16 test/evidence/slt_lang_createview.test:3-16 test/evidence/slt_lang_update.test:3-16

DDL Command Coverage

CREATE Statement Evidence Tests

Evidence tests for CREATE commands validate object creation semantics, error handling for duplicate objects, and platform-specific variations:

MSSQL Platform Behavior: The CREATE TRIGGER tests include onlyif mssql followed by halt at test/evidence/slt_lang_createtrigger.test:18-19 indicating that MSSQL does not support the full trigger syntax used in subsequent tests. Views in MSSQL allow UPDATE and DELETE operations (test/evidence/slt_lang_createview.test:68-85), diverging from SQLite's read-only view behavior.

graph LR
    subgraph "CREATE TRIGGER Tests"
        CT_FILE["slt_lang_createtrigger.test"]
CT_BASIC["Basic trigger creation\nstatement ok at line 25"]
CT_DUP["Duplicate detection\nstatement error at line 28-29"]
CT_TIMING["BEFORE/AFTER timing\nlines 81-96"]
CT_EVENTS["INSERT/UPDATE/DELETE events\nlines 40-46"]
CT_FILE --> CT_BASIC
 
       CT_FILE --> CT_DUP
 
       CT_FILE --> CT_TIMING
 
       CT_FILE --> CT_EVENTS
    end
    
    subgraph "CREATE VIEW Tests"
        CV_FILE["slt_lang_createview.test"]
CV_BASIC["Basic view creation\nstatement ok at line 23"]
CV_DUP["Duplicate detection\nstatement error at line 26-27"]
CV_TEMP["TEMP/TEMPORARY views\nlines 48-53"]
CV_READONLY["Read-only enforcement\nlines 68-103"]
CV_FILE --> CV_BASIC
 
       CV_FILE --> CV_DUP
 
       CV_FILE --> CV_TEMP
 
       CV_FILE --> CV_READONLY
    end
    
    style CT_FILE fill:#f9f9f9
    style CV_FILE fill:#f9f9f9
    style CT_BASIC fill:#e8f5e9
    style CV_BASIC fill:#e8f5e9
    style CT_DUP fill:#ffebee
    style CV_DUP fill:#ffebee

Sources: test/evidence/slt_lang_createtrigger.test:18-46 test/evidence/slt_lang_createview.test:23-53 test/evidence/slt_lang_createview.test:68-103

DROP Statement Evidence Tests

DROP statement tests verify object deletion, error handling for non-existent objects, and cleanup behavior:

The CREATE TRIGGER test file demonstrates the DROP pattern at test/evidence/slt_lang_createtrigger.test:192-219 where all created triggers are dropped sequentially. CREATE VIEW tests show error handling for DROP operations at test/evidence/slt_lang_createview.test:112-128

Sources: test/evidence/slt_lang_createtrigger.test:192-219 test/evidence/slt_lang_createview.test:112-128

DML Command Coverage

graph TB
    subgraph "UPDATE Test Scenarios"
        UPDATE_FILE["slt_lang_update.test"]
subgraph "Basic Operations"
            UPDATE_WHERE["UPDATE with WHERE clause\nR-58095-46013\nlines 54-62"]
UPDATE_NOWHERE["UPDATE without WHERE\naffects all rows\nlines 42-51"]
UPDATE_EXPR["Scalar expressions\nR-40598-36595\nlines 71-81"]
end
        
        subgraph "Column Assignment Rules"
            UPDATE_MULTI["Multiple assignments\nrightmost wins\nR-34751-18293\nlines 84-107"]
UPDATE_UNMOD["Unmodified columns\nretain values\nR-40472-60438\nlines 109-123"]
UPDATE_SELF["Self-referential updates\nR-36239-04077\nlines 126-137"]
end
        
        subgraph "Error Handling"
            UPDATE_BADCOL["Invalid column error\nlines 36-40"]
UPDATE_EMPTY["Empty WHERE clause\naffects zero rows\nR-58129-20729\nlines 64-69"]
end
    end
    
 
   UPDATE_FILE --> UPDATE_WHERE
 
   UPDATE_FILE --> UPDATE_NOWHERE
 
   UPDATE_FILE --> UPDATE_EXPR
 
   UPDATE_FILE --> UPDATE_MULTI
 
   UPDATE_FILE --> UPDATE_UNMOD
 
   UPDATE_FILE --> UPDATE_SELF
 
   UPDATE_FILE --> UPDATE_BADCOL
 
   UPDATE_FILE --> UPDATE_EMPTY
    
    style UPDATE_FILE fill:#f9f9f9
    style UPDATE_WHERE fill:#e8f5e9
    style UPDATE_MULTI fill:#fff9c4
    style UPDATE_SELF fill:#e1f5fe

UPDATE Statement Evidence Tests

UPDATE tests validate row modification behavior, WHERE clause evaluation, and column assignment semantics:

Multiple Assignment Behavior: Evidence marker R-34751-18293 at test/evidence/slt_lang_update.test:83-107 documents that when a column appears multiple times in the assignment list, only the rightmost assignment takes effect. This behavior is tested with UPDATE t1 SET x=3, x=4, x=5 where only x=5 is applied, but these tests are skipped on MSSQL via skipif mssql directives.

Self-Referential Updates: Evidence marker R-04558-24451 at test/evidence/slt_lang_update.test:129-137 validates that all scalar expressions are evaluated before any assignments occur, enabling updates like UPDATE t1 SET x=x+2 to work correctly.

Sources: test/evidence/slt_lang_update.test:19-137

REPLACE Statement Tests

The REPLACE command (INSERT OR REPLACE) tests are located in test/evidence/slt_lang_replace.test and validate SQLite's REPLACE syntax. This is a SQLite-specific extension that may not be available on all database platforms.

Sources: Referenced in file list but detailed content not provided

Cross-Platform Compatibility in Evidence Tests

Platform-Specific Directive Usage

Evidence tests include conditional directives to handle database platform differences:

Platform Behavior Matrix

The MSSQL platform demonstrates significant SQL dialect differences, particularly in trigger syntax (causing complete test halt) and view mutability (allowing UPDATE/DELETE operations that SQLite prohibits).

Sources: test/evidence/slt_lang_createtrigger.test:18-19 test/evidence/slt_lang_createview.test:47-103 test/evidence/slt_lang_update.test:87-107

Evidence Test Execution Model

Evidence tests maintain traceability throughout execution by tracking which evidence markers are covered by test cases. This enables coverage reporting that maps test results back to specification requirements.

Sources: test/evidence/slt_lang_createtrigger.test:21-96 test/evidence/slt_lang_createview.test:19-128 test/evidence/slt_lang_update.test:19-198

Test File Structure Pattern

All evidence test files follow a consistent structure:

1. Hash threshold declaration: hash-threshold 8 at file start
2. Schema initialization: CREATE TABLE, INSERT, CREATE INDEX (lines 3-16)
3. Platform compatibility checks: onlyif, skipif, halt directives
4. Evidence-annotated test blocks: Evidence citations followed by test directives
5. Cleanup operations: DROP statements to remove created objects

This pattern is consistent across test/evidence/slt_lang_createtrigger.test:1-220 test/evidence/slt_lang_createview.test:1-129 and test/evidence/slt_lang_update.test:1-198 facilitating automated test parsing and execution.

Sources: test/evidence/slt_lang_createtrigger.test:1-220 test/evidence/slt_lang_createview.test:1-129 test/evidence/slt_lang_update.test:1-198