Overview

Relevant source files

• .github/workflows/update-corpus.yml
• Dockerfile
• README.md

Purpose and Scope

The sqlite-sqllogictest-corpus repository provides a Git-based mirror of SQLite's official sqllogictest corpus, which is maintained in a Fossil repository. This repository automates the extraction and synchronization of test files, making them accessible to downstream consumers who prefer Git-based workflows over Fossil-based access.

Scope : This page introduces the repository's purpose, architecture components, and automation mechanisms. For detailed information about specific subsystems:

• System architecture and extraction pipeline: see System Architecture
• Test corpus organization and categories: see Test Corpus Reference
• Cross-database compatibility mechanisms: see Cross-Database Compatibility
• Practical usage instructions: see Usage Guide
• Test file format details: see Test File Format Specification

Repository Purpose

This repository serves as a distribution mechanism that:

The repository does not modify or validate test files—it purely extracts and distributes them.

Sources : README.md:1-3 .github/workflows/update-corpus.yml:1-11

System Components

The following diagram maps the repository's components to their implementation in code:

Sources : Dockerfile:1-36 .github/workflows/update-corpus.yml:1-45 README.md:1-27

graph TB
    subgraph "Upstream Source"
        FOSSIL["https://www.sqlite.org/sqllogictest/\nFossil Repository"]
end
    
    subgraph "Docker Image: slt-gen"
        DOCKERFILE["Dockerfile"]
FOSSIL_CLIENT["fossil clone command\nLine 15"]
EXTRACT_SCRIPT["slt-extract script\nLines 20-31"]
end
    
    subgraph "GitHub Actions Workflow"
        WORKFLOW_FILE[".github/workflows/update-corpus.yml"]
BUILD_STEP["docker build -t slt-gen\nLine 25"]
EXTRACT_STEP["docker run --rm -v test:/work/test\nLine 31"]
COMMIT_STEP["git commit/push\nLines 36-44"]
end
    
    subgraph "Output"
        TEST_DIR["test/ directory"]
EVIDENCE_DIR["test/evidence/"]
INDEX_DIR["test/index/"]
end
    
 
   FOSSIL -->|Cloned by| FOSSIL_CLIENT
 
   DOCKERFILE -->|Defines| FOSSIL_CLIENT
 
   DOCKERFILE -->|Defines| EXTRACT_SCRIPT
    
 
   WORKFLOW_FILE -->|Executes| BUILD_STEP
 
   BUILD_STEP -->|Creates| DOCKERFILE
 
   WORKFLOW_FILE -->|Executes| EXTRACT_STEP
 
   EXTRACT_STEP -->|Runs| EXTRACT_SCRIPT
 
   EXTRACT_SCRIPT -->|Writes to| TEST_DIR
    
 
   TEST_DIR -->|Contains| EVIDENCE_DIR
 
   TEST_DIR -->|Contains| INDEX_DIR
    
 
   WORKFLOW_FILE -->|Commits| EVIDENCE_DIR
 
   WORKFLOW_FILE -->|Commits| INDEX_DIR

Automation Mechanism

The repository uses a GitHub Actions workflow to maintain synchronization with the upstream Fossil repository:

Key workflow behaviors :

graph LR
    subgraph "Triggers"
        CRON["Cron Schedule\n0 6 * * 1\nEvery Monday 06:00 UTC"]
MANUAL["workflow_dispatch\nManual trigger"]
PR["pull_request\nValidation on PR"]
end
    
    subgraph "update-corpus.yml Workflow"
        CHECKOUT["Checkout repository\nLine 21-22"]
BUILD["Build extractor image\nLine 24-25"]
REFRESH["Refresh corpus\nLines 27-31"]
COMMIT["Commit and push changes\nLines 33-44"]
end
    
    subgraph "Conditional Logic"
        CHANGE_CHECK["git status --porcelain\nLine 36"]
EVENT_CHECK["github.event_name != pull_request\nLine 34"]
end
    
 
   CRON --> CHECKOUT
 
   MANUAL --> CHECKOUT
 
   PR --> CHECKOUT
    
 
   CHECKOUT --> BUILD
 
   BUILD --> REFRESH
 
   REFRESH --> EVENT_CHECK
 
   EVENT_CHECK -->|Not PR| CHANGE_CHECK
 
   EVENT_CHECK -->|Is PR| SKIP["Skip commit"]
CHANGE_CHECK -->|Changes detected| COMMIT
 
   CHANGE_CHECK -->|No changes| SKIP

Sources : .github/workflows/update-corpus.yml:3-44

Docker Extraction Process

The slt-gen Docker image performs the extraction:

The slt-extract script [Dockerfile:20-31]] implements a simple copy operation:

• Source: /src/test (extracted from Fossil)
• Destination: /work/test (mounted from host)
• Operation: cp -R to recursively copy all files

Sources : Dockerfile:1-36

Test Corpus Structure

The extracted corpus follows this organization:

The directory structure mirrors the upstream Fossil repository exactly [\\README.md21](), preserving file paths and organization for compatibility with existing tooling.

For detailed documentation of test organization, see Test Organization Structure.

Sources : README.md:1-27 High-level diagrams

Downstream Use Cases

This repository enables multiple consumption patterns:

Primary use cases :

1. SQL Compliance Testing : Database vendors validate their SQL implementations against SQLite's test corpus
2. Continuous Integration : CI/CD pipelines incorporate tests for automated regression detection
3. Research : Academic and industry researchers study SQL semantics and edge cases
4. Development : Developers building SQL engines use tests for validation

For integration examples, see Integrating with CI/CD Systems.

Sources : README.md:1-27 High-level diagrams

References

• Upstream Fossil repository: https://www.sqlite.org/sqllogictest/
• SQLite sqllogictest documentation: https://sqlite.org/sqllogictest/doc/trunk/about.wiki
• SQLite GitHub mirror: https://github.com/sqlite/sqlite

Sources : README.md:23-26

System Architecture

Relevant source files

• .github/workflows/update-corpus.yml
• Dockerfile
• README.md

Purpose and Scope

This document describes the overall architecture of the sqlite-sqllogictest-corpus system, which provides automated synchronization of SQLite's official sqllogictest corpus from a Fossil repository to a Git repository. The architecture consists of three primary layers: source repository access, Docker-based extraction, and GitHub Actions automation.

For detailed information about specific subsystems:

• Extraction implementation details: see Corpus Extraction Pipeline
• Workflow scheduling and commit logic: see Automated Update Workflow
• Test directory structure: see Test Organization Structure

Architectural Overview

The system implements a scheduled pull-based synchronization model that extracts test files from SQLite's Fossil repository and commits them to a Git repository. The architecture eliminates manual intervention through containerization and scheduled automation.

graph TB
    subgraph Source["Fossil Source Repository"]
FOSSIL["sqlite.org/sqllogictest/\nFossil SCM"]
end
    
    subgraph Automation["GitHub Actions Layer"]
WORKFLOW["update-corpus.yml\nWorkflow Definition"]
SCHEDULE["Cron: 0 6 * * 1\nMonday 06:00 UTC"]
DISPATCH["workflow_dispatch\nManual Trigger"]
end
    
    subgraph Container["Docker Build & Extract Layer"]
DOCKERFILE["Dockerfile\nImage Definition"]
IMAGE["slt-gen\nDocker Image"]
EXTRACT["slt-extract\nBash Script"]
end
    
    subgraph Storage["Filesystem Storage"]
TEST_DIR["test/\nMounted Volume"]
EVIDENCE["test/evidence/\nSQL Tests"]
INDEX["test/index/\nOptimization Tests"]
end
    
    subgraph VCS["Version Control"]
REPO["Git Repository\nsqlite-sqllogictest-corpus"]
end
    
 
   FOSSIL -->|fossil clone| IMAGE
 
   SCHEDULE -->|triggers| WORKFLOW
 
   DISPATCH -->|triggers| WORKFLOW
 
   WORKFLOW -->|docker build -t slt-gen .| DOCKERFILE
 
   DOCKERFILE -->|creates| IMAGE
 
   IMAGE -->|contains| EXTRACT
 
   WORKFLOW -->|docker run --rm -v| IMAGE
 
   IMAGE -->|executes| EXTRACT
 
   EXTRACT -->|cp -R /src/test/.| TEST_DIR
 
   TEST_DIR -->|contains| EVIDENCE
 
   TEST_DIR -->|contains| INDEX
 
   WORKFLOW -->|git add/commit/push| REPO
 
   TEST_DIR -.->|persisted to| REPO

System Components

Sources: .github/workflows/update-corpus.yml:1-44 Dockerfile:1-36 README.md:1-27

Component Architecture

Fossil Repository Access Layer

The system accesses SQLite's canonical sqllogictest repository hosted on Fossil SCM. The repository URL https://www.sqlite.org/sqllogictest/ contains the upstream test corpus that serves as the single source of truth.

graph LR
    APT["apt-get install fossil"]
CLONE["fossil clone\nhttps://www.sqlite.org/sqllogictest/"]
FOSSIL_FILE["/src/sqllogictest.fossil\nLocal Clone"]
OPEN["fossil open\n--user root"]
CHECKOUT["/src/test/\nWorking Directory"]
APT -->|installs| CLONE
 
   CLONE -->|creates| FOSSIL_FILE
 
   FOSSIL_FILE -->|input to| OPEN
 
   OPEN -->|populates| CHECKOUT

The Docker image clones this repository during build time using the fossil client installed via Debian's package manager:

Sources: Dockerfile:5-17

Docker Container Architecture

The Dockerfile defines a Debian-based image that encapsulates the entire extraction process. The image is tagged as slt-gen during the build phase.

graph TD
    ENTRY["ENTRYPOINT slt-extract"]
ARG["dest_root=${1:-/work/test}"]
SRC["src_root=/src/test"]
MKDIR["mkdir -p $dest_root"]
COPY["cp -R $src_root/. $dest_root/"]
ECHO["echo copied corpus..."]
ENTRY -->|executes with argument| ARG
 
   ARG --> SRC
 
   SRC --> MKDIR
 
   MKDIR --> COPY
 
   COPY --> ECHO

The slt-extract script serves as the container's entrypoint and accepts a single optional argument for the destination directory (defaults to /work/test):

Sources: Dockerfile:1-36 Dockerfile:20-35

GitHub Actions Workflow Layer

The update-corpus.yml workflow orchestrates the entire update cycle through four discrete steps executed on an ubuntu-latest runner.

graph TD
    CRON["schedule:\ncron: 0 6 * * 1"]
MANUAL["workflow_dispatch:\nManual Execution"]
PR["pull_request:\npaths filter"]
WORKFLOW["update job\nruns-on: ubuntu-latest"]
CRON -->|Monday 06:00 UTC| WORKFLOW
 
   MANUAL -->|on-demand| WORKFLOW
 
   PR -->|when specific files change| WORKFLOW

Workflow Triggers

The workflow monitors changes to three specific paths in pull requests: .github/workflows/update-corpus.yml, Dockerfile, and README.md.

Sources: .github/workflows/update-corpus.yml:3-12

Execution Steps

The workflow executes four sequential steps:

1. Repository Checkout - Uses actions/checkout@v4 to clone the Git repository
2. Image Build - Executes docker build -t slt-gen . to create the extraction container
3. Corpus Refresh - Removes existing test/ directory, recreates it, and runs the container with volume mount
4. Change Commit - Conditionally commits and pushes changes if files were modified and the event is not a pull request

Sources: .github/workflows/update-corpus.yml:18-44

sequenceDiagram
    participant Runner as "ubuntu-latest runner"
    participant Git as "Git Repository"
    participant Docker as "Docker Engine"
    participant Volume as "test/ directory"
    
    Runner->>Git: actions/checkout@v4
    Git-->>Runner: Repository checked out
    
    Runner->>Docker: docker build -t slt-gen .
    Docker-->>Runner: Image slt-gen created
    
    Runner->>Volume: rm -rf test
    Runner->>Volume: mkdir test
    Runner->>Docker: docker run --rm -v $PWD/test:/work/test slt-gen
    Docker->>Volume: Extract files to mounted volume
    Docker-->>Runner: Container exit
    
    Runner->>Git: git status --porcelain
    
    alt changes detected and not PR
        Runner->>Git: git add test
        Runner->>Git: git commit -m "Update sqllogictest corpus"
        Runner->>Git: git push
    else no changes or is PR
        Runner->>Runner: echo "No updates; skipping commit"
    end

Conditional Commit Logic

The workflow implements intelligent change detection to avoid empty commits:

This conditional executes only when github.event_name != 'pull_request', preventing commits during PR validation runs.

Sources: .github/workflows/update-corpus.yml:34-44

Data Flow Architecture

The complete data flow follows a unidirectional path from the Fossil repository through Docker containerization to the Git repository.

Sources: Dockerfile:14-28 .github/workflows/update-corpus.yml:24-41

flowchart LR
    subgraph Upstream
        FOSSIL_SRC["sqlite.org/sqllogictest\nFossil Repository"]
end
    
    subgraph "Build Phase"
        DOCKER_BUILD["docker build -t slt-gen ."]
FOSSIL_CLONE["fossil clone + fossil open"]
SRC_TEST["/src/test/\nFiles in Image"]
end
    
    subgraph "Extract Phase"
        DOCKER_RUN["docker run --rm -v"]
SLT_EXTRACT["slt-extract script"]
CP_COMMAND["cp -R /src/test/. /work/test/"]
end
    
    subgraph "Persist Phase"
        MOUNTED_VOL["$PWD/test/\nHost Filesystem"]
GIT_ADD["git add test"]
GIT_COMMIT["git commit"]
GIT_PUSH["git push"]
end
    
    subgraph Downstream
        GIT_REMOTE["GitHub Repository\nsqlite-sqllogictest-corpus"]
end
    
 
   FOSSIL_SRC -->|cloned during build| FOSSIL_CLONE
 
   DOCKER_BUILD --> FOSSIL_CLONE
 
   FOSSIL_CLONE --> SRC_TEST
 
   SRC_TEST -.->|embedded in image| DOCKER_RUN
 
   DOCKER_RUN --> SLT_EXTRACT
 
   SLT_EXTRACT --> CP_COMMAND
 
   CP_COMMAND -->|volume mount| MOUNTED_VOL
 
   MOUNTED_VOL --> GIT_ADD
 
   GIT_ADD --> GIT_COMMIT
 
   GIT_COMMIT --> GIT_PUSH
 
   GIT_PUSH --> GIT_REMOTE

Volume Mount Strategy

The system uses Docker volume mounts to bridge container execution with host filesystem persistence. The mount configuration "$PWD/test:/work/test" maps the host's current working directory test/ subdirectory to the container's /work/test path.

This approach ensures:

• Test files persist after container termination (--rm flag removes container but preserves mounted volume data)
• The workflow can detect changes using git status --porcelain
• No data copying between container and host required after extraction

Sources: .github/workflows/update-corpus.yml31 Dockerfile:24-28

Build-Time vs. Runtime Execution

The architecture separates concerns between build-time and runtime operations:

Build-Time Operations

Executed during docker build -t slt-gen .:

• Installation of system dependencies (fossil, bash, build-essential)
• Cloning of Fossil repository to /src/sqllogictest.fossil
• Opening Fossil repository to /src working directory
• Creation of /usr/local/bin/slt-extract script
• Setting script executable permissions

Runtime Operations

Executed during docker run --rm -v "$PWD/test:/work/test" slt-gen:

• Execution of slt-extract entrypoint
• Directory creation: mkdir -p /work/test
• Recursive copy: cp -R /src/test/. /work/test/
• Status output: echo "copied corpus to /work/test"

This separation ensures the expensive Fossil clone operation occurs once during image build, while extraction can execute repeatedly with minimal overhead.

Sources: Dockerfile:5-35 .github/workflows/update-corpus.yml:28-31

System Permissions and Security

The workflow requires write access to the repository through the contents: write permission, enabling automated commits. The commit author is configured as github-actions[bot] with email 41898282+github-actions[bot]@users.noreply.github.com.

The Fossil repository is configured with default user root to avoid interactive prompts during clone and open operations.

Sources: .github/workflows/update-corpus.yml:14-15 .github/workflows/update-corpus.yml:37-38 Dockerfile:15-17

Corpus Extraction Pipeline

Relevant source files

• Dockerfile
• README.md

Purpose and Scope

This document describes the corpus extraction pipeline—the Docker-based system that clones SQLite's official Fossil repository and extracts the sqllogictest corpus into a local filesystem directory. The pipeline consists of a Dockerfile that builds a specialized container image and a bash script (slt-extract) that performs the actual extraction.

For information about the automated workflow that orchestrates this pipeline on a schedule, see Automated Update Workflow. For details on how the extracted tests are organized, see Test Organization Structure.

Pipeline Architecture

The extraction pipeline follows a three-stage architecture: image build, repository cloning, and corpus extraction. The entire process is encapsulated within a Docker container to ensure reproducibility and isolation from the host environment.

Sources: Dockerfile:1-36

flowchart LR
    subgraph "Build Stage"
        DF["Dockerfile"]
BASE["debian:stable-slim\nbase image"]
DEPS["Dependencies:\nfossil, bash, tcl,\nbuild-essential"]
end
    
    subgraph "Clone Stage"
        FOSSIL_CMD["fossil clone\nwww.sqlite.org/sqllogictest"]
FOSSIL_OPEN["fossil open\nsqllogictest.fossil"]
SRC_DIR["/src/test/\ncloned corpus"]
end
    
    subgraph "Extract Stage"
        SCRIPT["/usr/local/bin/\nslt-extract"]
CP_CMD["cp -R /src/test\nto /work/test"]
DEST_DIR["/work/test/\nextracted corpus"]
end
    
 
   DF --> BASE
 
   DF --> DEPS
 
   DEPS --> FOSSIL_CMD
 
   FOSSIL_CMD --> FOSSIL_OPEN
 
   FOSSIL_OPEN --> SRC_DIR
 
   DF --> SCRIPT
 
   SCRIPT --> CP_CMD
 
   SRC_DIR --> CP_CMD
 
   CP_CMD --> DEST_DIR

Docker Image Construction

The extraction pipeline uses a Debian-based Docker image defined in the Dockerfile. The image is built in multiple logical stages, though implemented as a single-stage Dockerfile for simplicity.

Base Image and Dependencies

The image starts from debian:stable-slim and installs the required packages:

The package installation occurs at Dockerfile:5-12 using apt-get with --no-install-recommends to minimize image size. The /var/lib/apt/lists/* cache is removed after installation to further reduce the final image size.

Fossil Repository Cloning

The repository cloning process occurs during the Docker build phase, not at runtime. This design decision ensures that the cloned repository is baked into the image, eliminating the need for network access during extraction.

sequenceDiagram
    participant Build as Docker Build
    participant Fossil as fossil CLI
    participant Remote as www.sqlite.org
    participant FS as /src filesystem
    
    Build->>FS: WORKDIR /src
    Build->>Fossil: fossil clone https://www.sqlite.org/sqllogictest/
    Fossil->>Remote: HTTP GET sqllogictest repository
    Remote-->>Fossil: repository data
    Fossil->>FS: write /src/sqllogictest.fossil
    Build->>Fossil: fossil open sqllogictest.fossil
    Fossil->>FS: extract to /src/test/
    Build->>Fossil: fossil user default root
    Fossil->>FS: set default user

The cloning sequence at Dockerfile:14-17 performs three operations:

1. fossil clone : Downloads the repository from https://www.sqlite.org/sqllogictest/ to /src/sqllogictest.fossil
2. fossil open : Extracts the repository contents to the current working directory (/src)
3. fossil user default root : Sets the default user to root for subsequent operations

The --user root flag in both the clone and open commands ensures consistent user attribution within the Fossil repository.

Sources: Dockerfile:14-17

Extraction Script Implementation

The extraction logic is implemented in a bash script embedded directly in the Dockerfile as a heredoc. The script is written to /usr/local/bin/slt-extract at Dockerfile:20-31 and marked executable at Dockerfile33

Script Components

Script Variables:

The script uses bash strict mode (set -euo pipefail) at Dockerfile22 to ensure:

• -e: Exit immediately if any command fails
• -u: Treat unset variables as errors
• -o pipefail: Return the exit status of the last failed command in a pipe

Extraction Operation

The core extraction operation at Dockerfile28 uses cp -R with the trailing /. syntax:

cp -R "$src_root/." "$dest_root/"

This syntax copies the contents of /src/test/ (including hidden files) rather than the directory itself, resulting in the corpus files appearing directly under dest_root rather than in a nested test/ subdirectory.

Sources: Dockerfile:20-35

Container Execution Model

The container is configured with slt-extract as the ENTRYPOINT at Dockerfile35 This design allows the container to function as a single-purpose executable tool.

flowchart LR
    subgraph "Host Filesystem"
        HOST_TEST["$PWD/test/"]
end
    
    subgraph "Container Filesystem"
        CONTAINER_WORK["/work/test/"]
SRC_TEST["/src/test/\n(baked into image)"]
end
    
 
   HOST_TEST -.->|-v mount| CONTAINER_WORK
 
   SRC_TEST -->|cp -R| CONTAINER_WORK
 
   CONTAINER_WORK -->|persists to| HOST_TEST

Volume Mounting Strategy

The extraction process relies on Docker volume mounting to persist the extracted corpus to the host filesystem:

When the container runs with -v "$PWD/test:/work/test", the host's test/ directory is mounted at /work/test inside the container. The slt-extract script copies from the image's /src/test to the mounted /work/test, making the files appear in the host's test/ directory.

Sources: README.md18 Dockerfile35

Build and Execution Workflow

The complete extraction workflow consists of building the image and running the container:

Image Build Process

The docker build command at README.md10 creates an image tagged as slt-gen. This tag is referenced in the GitHub Actions workflow (see Automated Update Workflow) and in local usage.

Build stages:

1. Pull debian:stable-slim base image
2. Install system packages via apt-get
3. Clone Fossil repository from www.sqlite.org
4. Extract repository to /src/test
5. Write slt-extract script to /usr/local/bin
6. Set script as executable
7. Configure container entrypoint

Extraction Execution

The extraction sequence at README.md:16-18 performs:

1. Remove existing directory : rm -rf test ensures a clean slate
2. Create empty directory : mkdir test creates the mount point
3. Run container : docker run executes the extraction with:
   • --rm: Remove container after execution
   • -v "$PWD/test:/work/test": Mount host directory into container
   • slt-gen: Image name

The container executes slt-extract (the entrypoint), which copies corpus files from /src/test to /work/test, persisting them to the host's test/ directory via the volume mount.

Sources: README.md:6-19 Dockerfile35

graph TB
    subgraph "Upstream Fossil Repository"
        FOSSIL_ROOT["www.sqlite.org/sqllogictest/"]
FOSSIL_TEST["test/ directory"]
FOSSIL_EVIDENCE["test/evidence/"]
FOSSIL_INDEX["test/index/"]
end
    
    subgraph "Docker Image /src"
        IMG_SRC["/src/sqllogictest.fossil"]
IMG_TEST["/src/test/"]
IMG_EV["/src/test/evidence/"]
IMG_IDX["/src/test/index/"]
end
    
    subgraph "Container /work"
        WORK_TEST["/work/test/"]
WORK_EV["/work/test/evidence/"]
WORK_IDX["/work/test/index/"]
end
    
    subgraph "Host Filesystem"
        HOST_TEST["$PWD/test/"]
HOST_EV["$PWD/test/evidence/"]
HOST_IDX["$PWD/test/index/"]
end
    
 
   FOSSIL_ROOT --> FOSSIL_TEST
 
   FOSSIL_TEST --> FOSSIL_EVIDENCE
 
   FOSSIL_TEST --> FOSSIL_INDEX
    
 
   FOSSIL_ROOT -->|fossil clone| IMG_SRC
 
   IMG_SRC -->|fossil open| IMG_TEST
 
   IMG_TEST --> IMG_EV
 
   IMG_TEST --> IMG_IDX
    
 
   IMG_TEST -->|cp -R| WORK_TEST
 
   IMG_EV -->|cp -R| WORK_EV
 
   IMG_IDX -->|cp -R| WORK_IDX
    
 
   WORK_TEST -->|volume mount| HOST_TEST
 
   WORK_EV -->|volume mount| HOST_EV
 
   WORK_IDX -->|volume mount| HOST_IDX

Directory Structure Mapping

The extraction pipeline maintains a direct mirror of the upstream repository structure:

The pipeline preserves the complete directory hierarchy from upstream, including:

• SQL language specification tests in evidence/
• Query optimization tests in index/
• All subdirectory structures and file naming conventions

Sources: Dockerfile:14-28 README.md21

Error Handling and Safety

The extraction script includes several safety mechanisms:

The bash strict mode ensures that any failure in the extraction process (e.g., missing source directory, write permission issues) causes immediate script termination with a non-zero exit code, which propagates to the Docker container exit status.

Sources: Dockerfile:20-31 README.md18

Performance Characteristics

The extraction pipeline exhibits the following performance characteristics:

Build Time: The docker build step includes network I/O to clone the Fossil repository. Repository size is typically 50-200 MB, making build time network-dependent (1-5 minutes on typical connections).

Extraction Time: The docker run step performs only filesystem copy operations from the image to the mounted volume. With typical corpus sizes (10,000+ test files), extraction completes in seconds.

Storage Efficiency: The Fossil repository is cloned once during image build and reused for all subsequent extractions. This avoids redundant network operations when running multiple extractions.

The pipeline's design optimizes for extraction speed at the cost of larger image size. This trade-off is appropriate for automated CI/CD environments where fast extraction is critical.

Sources: Dockerfile:15-17 Dockerfile28

Automated Update Workflow

Relevant source files

• .github/workflows/update-corpus.yml
• Dockerfile

Purpose and Scope

This document describes the GitHub Actions workflow that automatically synchronizes the test corpus from SQLite's Fossil repository on a weekly basis. The workflow orchestrates the Docker-based extraction process (detailed in Corpus Extraction Pipeline) and manages the commit lifecycle to keep the Git repository up-to-date with the upstream source.

For information about the extracted test file organization, see Test Organization Structure. For practical usage of the extraction system outside of automation, see Building and Running the Extractor.

Workflow Triggers

The workflow defined in .github/workflows/update-corpus.yml:3-12 supports three distinct trigger mechanisms:

The scheduled trigger ensures regular synchronization without manual intervention, while the manual trigger allows immediate updates when upstream changes are known. The pull request trigger enables CI validation when modifying the automation infrastructure itself.

Sources: .github/workflows/update-corpus.yml:3-12

Workflow Architecture

The following diagram maps the GitHub Actions job structure to the actual code entities defined in the workflow file:

Sources: .github/workflows/update-corpus.yml:17-44

graph TB
    subgraph "GitHub Actions Workflow: update-corpus.yml"
        TRIGGER[/"Trigger Events"/]
        
        subgraph "Job: update"
            RUNNER["runs-on: ubuntu-latest"]
STEP1["Step: Checkout repository\nuses: actions/checkout@v4"]
STEP2["Step: Build extractor image\ndocker build -t slt-gen ."]
STEP3["Step: Refresh corpus\nrm -rf test; mkdir test\ndocker run --rm -v PWD/test:/work/test slt-gen"]
STEP4["Step: Commit and push changes\nif: github.event_name != 'pull_request'"]
RUNNER --> STEP1
 
           STEP1 --> STEP2
 
           STEP2 --> STEP3
 
           STEP3 --> STEP4
        end
    end
    
 
   TRIGGER -->|schedule workflow_dispatch pull_request| RUNNER
    
 
   STEP2 -.->|Builds from| DOCKERFILE[Dockerfile]
 
   STEP3 -.->|Writes to| TESTDIR["$PWD/test/"]
STEP4 -.->|Commits| GITREPO["Git Repository"]
style TRIGGER fill:#f9f9f9
    style RUNNER fill:#f9f9f9
    style STEP1 fill:#f9f9f9
    style STEP2 fill:#f9f9f9
    style STEP3 fill:#f9f9f9
    style STEP4 fill:#f9f9f9

Execution Pipeline

Step 1: Repository Checkout

The workflow begins by checking out the repository using the actions/checkout@v4 action with write permissions enabled through the permissions: contents: write declaration at .github/workflows/update-corpus.yml:14-15 This permission is critical for the final commit step.

Sources: .github/workflows/update-corpus.yml:21-22 .github/workflows/update-corpus.yml:14-15

Step 2: Docker Image Build

The slt-gen image is built from the Dockerfile in the repository root:

The build command at .github/workflows/update-corpus.yml25 creates an image tagged as slt-gen, which contains the Fossil clone of the sqllogictest repository and the slt-extract utility defined at Dockerfile:20-33

Sources: .github/workflows/update-corpus.yml:24-25 Dockerfile:1-36

Step 3: Corpus Extraction

The extraction step performs a clean refresh of the test corpus:

The commands at .github/workflows/update-corpus.yml:28-31 ensure a clean state by removing the existing test/ directory before extraction. The Docker volume mount (-v "$PWD/test:/work/test") allows the container to write directly to the host filesystem, with files persisting after the container terminates.

Sources: .github/workflows/update-corpus.yml:27-31 Dockerfile:20-35

Step 4: Change Detection and Commit

The workflow employs intelligent change detection to avoid unnecessary commits:

stateDiagram-v2
    [*] --> CheckEventType : Step 4 executes
    
    CheckEventType --> SkipCommit : if pull_request
    CheckEventType --> CheckChanges : if NOT pull_request
    
    CheckChanges --> RunGitStatus : git status --porcelain
    
    RunGitStatus --> EvaluateOutput : Check output
    
    EvaluateOutput --> ConfigureGit : Output non-empty
    EvaluateOutput --> LogSkip : Output empty
    
    ConfigureGit --> SetUserName : git config user.name
    SetUserName --> SetUserEmail : git config user.email
    SetUserEmail --> StageFiles : git add test
    StageFiles --> Commit : git commit -m message
    Commit --> Push : git push
    Push --> [*]
    
    LogSkip --> [*] : Echo skip message
    SkipCommit --> [*] : Conditional skipped

The conditional at .github/workflows/update-corpus.yml34 prevents commits during pull request runs, ensuring the workflow can be safely tested without polluting the repository history. The git status --porcelain command at .github/workflows/update-corpus.yml36 produces machine-readable output that, when piped to grep ., will only succeed if changes exist. This pattern prevents empty commits when the upstream corpus has not changed.

The bot credentials configured at .github/workflows/update-corpus.yml:37-38 identify automated commits using the standard GitHub Actions bot user (github-actions[bot]).

Sources: .github/workflows/update-corpus.yml:33-44

File System Operations

The workflow manipulates the following filesystem locations:

The mapping between container paths and host paths is established through the volume specification: -v "$PWD/test:/work/test" at .github/workflows/update-corpus.yml31

Sources: .github/workflows/update-corpus.yml:28-31 Dockerfile:24-28

Workflow Permissions

The workflow requires elevated permissions to commit and push changes:

This declaration at .github/workflows/update-corpus.yml:14-15 grants the workflow write access to repository contents. Without this permission, the git push operation at .github/workflows/update-corpus.yml41 would fail with an authentication error.

The GITHUB_TOKEN is implicitly available to all workflow steps and provides the authentication credentials needed for the push operation. The token is automatically scoped to the repository and expires when the workflow completes.

Sources: .github/workflows/update-corpus.yml:14-15

graph TB
 
   SHELL["Shell: set -euo pipefail\n(in slt-extract)"] --> DOCKER_BUILD["Docker build failure"]
SHELL --> DOCKER_RUN["Docker run failure"]
SHELL --> GIT_PUSH["Git push failure"]
DOCKER_BUILD --> FAIL["Workflow fails"]
DOCKER_RUN --> FAIL
 
   GIT_PUSH --> FAIL
    
 
   FAIL --> NOTIFY["GitHub Actions UI shows failure"]
FAIL --> EMAIL["Email notification (if configured)"]

Error Handling

The workflow employs a fail-fast approach through shell options and Docker flags:

The --rm flag at .github/workflows/update-corpus.yml31 ensures container cleanup even if the extraction process fails. The slt-extract script uses set -euo pipefail at Dockerfile22 to halt execution on any command failure, undefined variable access, or pipeline errors.

Sources: .github/workflows/update-corpus.yml31 Dockerfile:20-33

Testing Workflow Modifications

The pull request trigger configuration enables safe testing of workflow changes:

When any of these files are modified in a pull request, the workflow executes but skips the commit step due to the conditional at .github/workflows/update-corpus.yml34 This allows verification that:

• The Docker image builds successfully
• The extraction process completes without errors
• The expected test files are generated

The workflow only triggers on changes to infrastructure files, not on modifications to the extracted corpus itself, preventing unnecessary CI runs.

Sources: .github/workflows/update-corpus.yml:8-12 .github/workflows/update-corpus.yml34

Test Organization Structure

Relevant source files

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

Purpose and Scope

This document describes the organizational structure of the SQL Logic Test corpus extracted from SQLite's Fossil repository. It explains how test files are categorized, the directory hierarchy used for storage, and the taxonomy that governs test placement within the test/ directory.

For information about how these tests are extracted from the upstream repository, see Corpus Extraction Pipeline. For details on the file format and directives used within individual test files, see Test File Format Specification.

Test Taxonomy

The corpus employs a two-tier categorization system that separates tests based on their validation purpose:

Evidence tests ensure that SQL statements execute according to specification, verifying the semantic correctness of database operations. Index tests focus on performance-related behaviors, particularly how the query optimizer utilizes indexes to execute queries efficiently.

Sources: Based on system architecture analysis and repository structure.

graph TB
    subgraph "test/ Root Directory"
        ROOT["test/"]
subgraph "Evidence Directory"
            EVIDENCE["test/evidence/"]
subgraph "DDL Tests"
                CREATE_TRIGGER["slt_lang_createtrigger.test"]
CREATE_VIEW["slt_lang_createview.test"]
DROP_INDEX["slt_lang_dropindex.test"]
DROP_TABLE["slt_lang_droptable.test"]
DROP_TRIGGER["slt_lang_droptrigger.test"]
DROP_VIEW["slt_lang_dropview.test"]
REINDEX["slt_lang_reindex.test"]
end
            
            subgraph "DML Tests"
                REPLACE["slt_lang_replace.test"]
UPDATE["slt_lang_update.test"]
end
            
            subgraph "DQL Tests"
                IN1["in1.test"]
IN2["in2.test"]
AGGFUNC["slt_lang_aggfunc.test"]
end
        end
        
        subgraph "Index Directory"
            INDEX["test/index/"]
subgraph "BETWEEN Tests"
                BETWEEN["between/"]
BETWEEN_1["between/1/"]
BETWEEN_10["between/10/"]
BETWEEN_100["between/100/"]
BETWEEN_1_FILE["slt_good_0.test"]
BETWEEN_10_FILES["slt_good_0.test through slt_good_5.test"]
BETWEEN_100_FILE["slt_good_0.test"]
end
        end
    end
    
 
   ROOT --> EVIDENCE
 
   ROOT --> INDEX
    
 
   EVIDENCE --> CREATE_TRIGGER
 
   EVIDENCE --> CREATE_VIEW
 
   EVIDENCE --> DROP_INDEX
 
   EVIDENCE --> DROP_TABLE
 
   EVIDENCE --> DROP_TRIGGER
 
   EVIDENCE --> DROP_VIEW
 
   EVIDENCE --> REINDEX
 
   EVIDENCE --> REPLACE
 
   EVIDENCE --> UPDATE
 
   EVIDENCE --> IN1
 
   EVIDENCE --> IN2
 
   EVIDENCE --> AGGFUNC
    
 
   INDEX --> BETWEEN
 
   BETWEEN --> BETWEEN_1
 
   BETWEEN --> BETWEEN_10
 
   BETWEEN --> BETWEEN_100
    
 
   BETWEEN_1 --> BETWEEN_1_FILE
 
   BETWEEN_10 --> BETWEEN_10_FILES
 
   BETWEEN_100 --> BETWEEN_100_FILE

Directory Structure Overview

Diagram: Test Corpus Directory Hierarchy

This diagram maps the physical directory structure to actual file paths in the repository. The test/evidence/ directory contains flat files named by SQL language feature, while test/index/ uses nested directories to organize tests by operator and configuration parameters.

Sources:

• test/evidence/in1.test:1-1156
• test/evidence/slt_lang_createtrigger.test:1-220

Evidence Tests: SQL Language Specification

Evidence tests validate that SQL statements behave according to documented specifications. These tests are stored as individual files in test/evidence/, with filenames following the pattern slt_lang_<feature>.test for language features or descriptive names for operator tests.

Diagram: Evidence Test File Organization by SQL Category

graph LR
    subgraph "Evidence Test Categories"
        DDL["Data Definition Language"]
DML["Data Manipulation Language"]
DQL["Data Query Language"]
end
    
    subgraph "DDL Test Files"
 
       DDL --> CT["slt_lang_createtrigger.test"]
DDL --> CV["slt_lang_createview.test"]
DDL --> DI["slt_lang_dropindex.test"]
DDL --> DT["slt_lang_droptable.test"]
DDL --> DTR["slt_lang_droptrigger.test"]
DDL --> DV["slt_lang_dropview.test"]
DDL --> RI["slt_lang_reindex.test"]
end
    
    subgraph "DML Test Files"
 
       DML --> REP["slt_lang_replace.test"]
DML --> UPD["slt_lang_update.test"]
end
    
    subgraph "DQL Test Files"
 
       DQL --> IN1_F["in1.test"]
DQL --> IN2_F["in2.test"]
DQL --> AGG["slt_lang_aggfunc.test"]
end

File Naming Convention

Evidence test files follow these naming patterns:

The slt_lang_ prefix indicates tests that validate language specification compliance, often including references to specific evidence markers in the SQLite documentation (e.g., EVIDENCE-OF: R-10346-40046).

Sources:

• test/evidence/slt_lang_createtrigger.test:21-22
• test/evidence/in1.test:11-14

Index Tests: Query Optimization Validation

Index tests are organized hierarchically to support parameterized test generation across different configurations. Unlike evidence tests, index tests use nested directories to represent test variations.

Diagram: Index Test Directory Hierarchy for BETWEEN Operator

graph TB
    INDEX_ROOT["test/index/"]
subgraph "Operator Directories"
        BETWEEN_DIR["between/"]
end
    
    subgraph "Configuration Directories"
        CONFIG_1["1/"]
CONFIG_10["10/"]
CONFIG_100["100/"]
end
    
    subgraph "Test Files"
        FILE_1["slt_good_0.test"]
FILES_10_0["slt_good_0.test"]
FILES_10_1["slt_good_1.test"]
FILES_10_2["slt_good_2.test"]
FILES_10_3["slt_good_3.test"]
FILES_10_4["slt_good_4.test"]
FILES_10_5["slt_good_5.test"]
FILE_100["slt_good_0.test"]
end
    
 
   INDEX_ROOT --> BETWEEN_DIR
    
 
   BETWEEN_DIR --> CONFIG_1
 
   BETWEEN_DIR --> CONFIG_10
 
   BETWEEN_DIR --> CONFIG_100
    
 
   CONFIG_1 --> FILE_1
    
 
   CONFIG_10 --> FILES_10_0
 
   CONFIG_10 --> FILES_10_1
 
   CONFIG_10 --> FILES_10_2
 
   CONFIG_10 --> FILES_10_3
 
   CONFIG_10 --> FILES_10_4
 
   CONFIG_10 --> FILES_10_5
    
 
   CONFIG_100 --> FILE_100
    
    note1["Path: test/index/between/1/slt_good_0.test"]
note2["Path: test/index/between/10/slt_good_0.test"]
note3["Path: test/index/between/100/slt_good_0.test"]
CONFIG_1 -.-> note1
 
   CONFIG_10 -.-> note2
 
   CONFIG_100 -.-> note3

Directory Structure Pattern

Index tests follow a three-level hierarchy:

1. Operator Level (test/index/<operator>/): Top-level directory named after the SQL operator being tested
2. Configuration Level (test/index/<operator>/<config>/): Numeric directories representing test configuration parameters (e.g., data volume, index cardinality)
3. Test Files (test/index/<operator>/<config>/slt_good_<n>.test): Individual test files numbered sequentially

The numeric configuration directories (1, 10, 100) typically represent scaling factors or data set sizes, allowing the same test logic to be validated across different performance scenarios.

Sources: Based on repository structure analysis from high-level architecture diagrams.

Cross-Database Compatibility Markers

Test files in both evidence/ and index/ directories use conditional execution directives to control test execution based on the target database platform. These directives are embedded directly in test files:

Diagram: Conditional Execution Flow in Test Files

flowchart TD
    TEST_START["Test File Execution Begins"]
READ_LINE["Read Next Line"]
CHECK_DIRECTIVE{"Line Type?"}
ONLYIF["onlyif <database>"]
SKIPIF["skipif <database>"]
HALT["halt"]
SQL["SQL Statement or Query"]
EVAL_ONLYIF{"Database\nMatches?"}
EVAL_SKIPIF{"Database\nMatches?"}
EXECUTE["Execute Statement/Query"]
SKIP["Skip Block"]
HALT_EXEC["Stop File Execution"]
TEST_START --> READ_LINE
 
   READ_LINE --> CHECK_DIRECTIVE
    
 
   CHECK_DIRECTIVE -->|Directive| ONLYIF
 
   CHECK_DIRECTIVE -->|Directive| SKIPIF
 
   CHECK_DIRECTIVE -->|Directive| HALT
 
   CHECK_DIRECTIVE -->|Content| SQL
    
 
   ONLYIF --> EVAL_ONLYIF
 
   SKIPIF --> EVAL_SKIPIF
    
 
   EVAL_ONLYIF -->|Yes| READ_LINE
 
   EVAL_ONLYIF -->|No| SKIP
    
 
   EVAL_SKIPIF -->|Yes| SKIP
 
   EVAL_SKIPIF -->|No| READ_LINE
    
 
   HALT --> EVAL_ONLYIF
 
   EVAL_ONLYIF -->|Yes| HALT_EXEC
    
 
   SQL --> EXECUTE
 
   EXECUTE --> READ_LINE
 
   SKIP --> READ_LINE
    
 
   HALT_EXEC -.-> END_FILE["End of File Processing"]
READ_LINE -.->|EOF| END_FILE

Example directives from evidence tests:

• Platform exclusion: test/evidence/in1.test:4-5 demonstrates halting execution for MSSQL
• Platform-specific tests: test/evidence/in1.test:22-26 shows SQLite-only tests for empty RHS in IN operator
• SQLite extensions: test/evidence/in1.test:69-73 demonstrates table-as-operand syntax unique to SQLite

These directives enable a single corpus to serve multiple database platforms while accommodating platform-specific SQL dialects and feature sets.

Sources:

• test/evidence/in1.test:1-10
• test/evidence/in1.test:22-26
• test/evidence/in1.test:69-73
• test/evidence/slt_lang_createtrigger.test:18-19

Evidence Documentation Markers

Evidence tests include special comment markers that reference specific sections of SQLite's documentation. These markers follow the pattern EVIDENCE-OF: R-<number>-<number> and serve as bidirectional traceability links between test assertions and specification requirements:

Diagram: Evidence Marker Traceability

graph LR
    subgraph "Test File Structure"
        COMMENT["# Comment with EVIDENCE-OF marker"]
DIRECTIVE["Conditional execution directive"]
STATEMENT["statement ok / query directive"]
SQL_CODE["SQL code to execute"]
EXPECTED["Expected result"]
end
    
    subgraph "SQLite Documentation"
        SPEC["Specification Requirement"]
REF_ID["Reference ID: R-xxxxx-xxxxx"]
end
    
 
   COMMENT -->|References| REF_ID
 
   REF_ID -->|Documents| SPEC
 
   SPEC -->|Validated by| STATEMENT
 
   STATEMENT --> SQL_CODE
 
   SQL_CODE --> EXPECTED

Example evidence markers from test files:

These markers enable developers to trace test failures back to specific requirements and understand the intended behavior being validated.

Sources:

• test/evidence/in1.test:11-20
• test/evidence/slt_lang_createtrigger.test:21-22
• test/evidence/slt_lang_createtrigger.test:65-68

graph LR
    subgraph "slt_lang Prefix Pattern"
        PREFIX["slt_lang_"]
OPERATION["<operation>"]
EXTENSION[".test"]
PREFIX -->|Concatenate| OPERATION
 
       OPERATION -->|Concatenate| EXTENSION
    end
    
    subgraph "Examples"
        EX1["slt_lang_createtrigger.test"]
EX2["slt_lang_dropindex.test"]
EX3["slt_lang_reindex.test"]
end
    
    subgraph "Operator Pattern"
        OP_NAME["<operator>"]
VARIANT["<variant_number>"]
OP_EXT[".test"]
OP_NAME -->|Concatenate| VARIANT
 
       VARIANT -->|Concatenate| OP_EXT
    end
    
    subgraph "Operator Examples"
        OP1["in1.test"]
OP2["in2.test"]
end

Test File Naming and Identification

The corpus uses distinct naming patterns to support both human readability and automated processing:

Evidence Test Naming

Diagram: Evidence Test Naming Conventions

Index Test Naming

Index tests use a standardized slt_good_<n>.test naming pattern, where <n> is a zero-based sequential number. This pattern supports:

• Automated generation: Sequential numbering allows test generators to create multiple test files programmatically
• Parallel execution: Numbered files can be distributed across multiple test runners
• Configuration grouping: All files in a configuration directory share the same test parameters

The slt_good_ prefix distinguishes these as "good" (expected to pass) test cases, allowing for future expansion with slt_bad_ tests for error condition validation.

Sources: Based on repository structure analysis and naming pattern observations.

Summary

The test organization structure implements a dual taxonomy:

1. Evidence tests (test/evidence/) provide flat-file organization for SQL specification validation, with filenames directly indicating the feature under test
2. Index tests (test/index/) use hierarchical directories to parameterize optimization tests across different configurations

This structure supports:

• Discoverability: Developers can locate tests by SQL feature name or operator
• Maintainability: Related tests are grouped together in predictable locations
• Cross-platform testing: Conditional directives allow platform-specific test execution
• Traceability: Evidence markers link tests to specification requirements
• Scalability: Hierarchical index tests support configuration variations without file proliferation

For details on how to execute these tests, see Usage Guide. For information on the test file format syntax, see Test File Format Specification.

Sources:

• test/evidence/in1.test:1-1156
• test/evidence/slt_lang_createtrigger.test:1-220

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

Data Definition Language (DDL) Tests

Relevant source files

• test/evidence/slt_lang_createtrigger.test
• test/evidence/slt_lang_createview.test
• test/evidence/slt_lang_dropindex.test
• test/evidence/slt_lang_droptable.test
• test/evidence/slt_lang_droptrigger.test
• test/evidence/slt_lang_dropview.test

Purpose and Scope

This page documents the Data Definition Language (DDL) test suite within the SQL Language Evidence Tests category. These tests validate the correctness of CREATE and DROP statements for database objects including tables, views, indexes, and triggers. The tests verify compliance with SQL language specifications and SQLite-specific DDL behaviors.

For detailed coverage of individual CREATE statement types, see CREATE Statement Tests. For detailed coverage of individual DROP statement types, see DROP Statement Tests. For data manipulation tests, see Data Manipulation Language (DML) Tests-tests).

Overview

DDL tests verify the schema manipulation capabilities of SQL implementations. These tests ensure that database objects can be created, exist with the correct properties, and can be removed from the schema. Each test file focuses on a specific DDL command and validates both successful operations and expected error conditions.

The test suite follows a consistent pattern:

1. Setup : Create a base table with sample data
2. Operation : Execute the DDL statement being tested
3. Verification : Confirm the operation succeeded or failed as expected
4. Cleanup : Remove created objects

Test File Locations

All DDL test files are located in the test/evidence/ directory:

Sources: test/evidence/slt_lang_createtrigger.test:1-220 test/evidence/slt_lang_createview.test:1-129 test/evidence/slt_lang_dropindex.test:1-35 test/evidence/slt_lang_droptable.test:1-56 test/evidence/slt_lang_droptrigger.test:1-54 test/evidence/slt_lang_dropview.test:1-60

Test File Structure and Evidence Mapping

Evidence-Based Testing Model

Diagram: Evidence-Based Test Validation Model

Each test file contains inline comments that reference specific evidence identifiers from SQLite's documentation. These identifiers (format: R-XXXXX-XXXXX) trace each test back to the precise specification it validates.

Sources: test/evidence/slt_lang_createtrigger.test:21-22 test/evidence/slt_lang_createview.test:19-20 test/evidence/slt_lang_dropindex.test:19-20 test/evidence/slt_lang_droptable.test:19-20 test/evidence/slt_lang_droptrigger.test:21-22 test/evidence/slt_lang_dropview.test:19-20

DDL Test Coverage Matrix

Database Object Lifecycle Testing

Diagram: Database Object State Transitions and Test Coverage

Sources: test/evidence/slt_lang_createtrigger.test:25-29 test/evidence/slt_lang_createview.test:23-28 test/evidence/slt_lang_droptrigger.test:28-36 test/evidence/slt_lang_dropview.test:31-39

DDL Command Test Matrix

Sources: All six test files in test/evidence/

Common Test Setup Pattern

All DDL test files share a consistent initialization sequence that establishes a baseline table for testing:

CREATE TABLE t1( x INTEGER, y VARCHAR(8) )
INSERT INTO t1 VALUES(1,'true')
INSERT INTO t1 VALUES(0,'false')
INSERT INTO t1 VALUES(NULL,'NULL')
CREATE INDEX t1i1 ON t1(x)

This pattern appears at slt_lang_createtrigger.test:4-16 slt_lang_createview.test:4-16 slt_lang_dropindex.test:4-16 slt_lang_droptable.test:4-16 slt_lang_droptrigger.test:4-16 and slt_lang_dropview.test:4-16

The setup provides:

• A table (t1) with integer and varchar columns
• Sample data including NULL values for comprehensive testing
• An index (t1i1) for testing cascading deletion behavior

Cross-Database Compatibility Handling

Platform-Specific Test Execution

Diagram: Cross-Database Test Execution Control Flow

Sources: test/evidence/slt_lang_createtrigger.test:18-19 test/evidence/slt_lang_dropindex.test:22-24 test/evidence/slt_lang_createview.test:47-53

Platform-Specific Behavior Differences

The test files use three primary conditional directives:

• onlyif <database>: Execute statement only on specified database
• skipif <database>: Skip statement on specified database
• halt: Stop test execution immediately (used when entire test file is incompatible)

Sources: test/evidence/slt_lang_createtrigger.test:18-19 test/evidence/slt_lang_createview.test:47-85 test/evidence/slt_lang_dropindex.test:22-34 test/evidence/slt_lang_droptable.test:49-55

Cascading Deletion and Object Dependencies

Dependency Relationships

Diagram: Object Dependency and Cascading Deletion Behavior

Cascading Deletion Tests

Table Removal Cascades :

• When a table is dropped, all associated indexes are automatically removed (slt_lang_droptable.test:36-41)
• When a table is dropped, all associated triggers are automatically removed (slt_lang_droptrigger.test:42-52)

View Removal Independence :

• When a view is dropped, underlying base tables are not modified (slt_lang_dropview.test:44-59)
• Base table data remains accessible after view deletion (slt_lang_dropview.test:56-59)

Test Verification Examples :

1. Index cascade verification at slt_lang_droptable.test:39-41:

   # this should error, as was dropped with table
   statement error
   DROP INDEX t1i1;
   

2. Trigger cascade verification at slt_lang_droptrigger.test:44-52:

   statement ok
   CREATE TRIGGER t1r1 UPDATE ON t1 BEGIN SELECT 1; END;
   
   statement ok
   DROP TABLE t1
   
   # already deleted when table dropped
   statement error
   DROP TRIGGER t1r1
   

3. View independence verification at slt_lang_dropview.test:48-59:

   query I rowsort label-0
   SELECT x FROM view2
   ----
   0
   
   statement ok
   DROP VIEW view2
   
   query I rowsort label-0
   SELECT x FROM t1 WHERE x=0
   ----
   0
   

Sources: test/evidence/slt_lang_droptable.test:36-41 test/evidence/slt_lang_droptrigger.test:42-52 test/evidence/slt_lang_dropview.test:44-59

Trigger Timing and Event Combinations

The CREATE TRIGGER test validates all combinations of trigger timing (BEFORE/AFTER) and trigger events (INSERT/UPDATE/DELETE):

All triggers are subsequently dropped at slt_lang_createtrigger.test:191-219 to verify cleanup functionality.

Sources: test/evidence/slt_lang_createtrigger.test:25-96 test/evidence/slt_lang_createtrigger.test:191-219

View Mutability Testing

Read-Only Enforcement

Views in SQLite are read-only, meaning INSERT, UPDATE, and DELETE operations are not permitted. The test file validates this behavior at slt_lang_createview.test:65-103:

SQLite-specific read-only enforcement :

onlyif sqlite
statement error
DELETE FROM view1 WHERE x>0

onlyif sqlite
statement error
INSERT INTO view1 VALUES(2,'unknown')

onlyif sqlite
statement error
UPDATE view1 SET x=2

MSSQL exception : MSSQL allows certain DELETE and UPDATE operations on views (slt_lang_createview.test:68-85), demonstrating platform-specific SQL implementation differences.

Sources: test/evidence/slt_lang_createview.test:65-103

Evidence Reference System

Evidence Identifier Format

Test files contain evidence comments that link test cases to specific sections of SQLite's documentation. The format follows the pattern:

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

Where:

• EVIDENCE-OF: Fully implemented and tested
• TBD-EVIDENCE-OF: Evidence identified but test not yet implemented
• PARTIAL-EVIDENCE-OF: Partially tested (some aspects covered)

Key Evidence Points Tested

Sources: All evidence comments across the six DDL test files

Test Execution Workflow

Standard Test Sequence

Each DDL test file follows this execution pattern:

1. Hash threshold declaration : hash-threshold 8 ([Line 1 in all files](https://github.com/jzombie/sqlite-sqllogictest-corpus/blob/8a29188b/Line 1 in all files))
2. Base table creation : Standard t1 table setup ([Lines 3-16](https://github.com/jzombie/sqlite-sqllogictest-corpus/blob/8a29188b/Lines 3-16))
3. Primary DDL operation : Execute the CREATE or DROP statement being tested
4. Duplicate/missing error validation : Verify appropriate errors for invalid operations
5. Cascading behavior validation : Confirm dependent objects are handled correctly
6. Cleanup : Remove all created objects

Error Validation Pattern

Tests verify both successful operations and expected failures:

Success validation :

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

Duplicate creation error :

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

Missing object error :

# never existed
statement error
DROP TRIGGER tXrX

This pattern appears consistently across all DDL test files, establishing a comprehensive validation matrix for each DDL command.

Sources: test/evidence/slt_lang_createtrigger.test:25-29 test/evidence/slt_lang_droptrigger.test:28-36

Related Test Categories

For comprehensive coverage of SQL testing:

• CREATE statement details : See CREATE Statement Tests for in-depth coverage of CREATE TRIGGER and CREATE VIEW syntax variations
• DROP statement details : See DROP Statement Tests for in-depth coverage of all DROP statement types
• DML operations : See Data Manipulation Language (DML) Tests-tests) for INSERT, UPDATE, DELETE, and REPLACE testing
• Query operations : See Query Operator Tests for SELECT statement validation
• Test format specification : See Test File Format Specification for complete DSL syntax reference

CREATE Statement Tests

Relevant source files

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

Purpose and Scope

This page documents the CREATE statement tests in the SQL Logic Test corpus, specifically covering CREATE TRIGGER and CREATE VIEW statements. These tests validate the behavior of database object creation commands according to SQL specifications and SQLite-specific extensions.

For tests covering DROP statements, see DROP Statement Tests. For broader DDL test coverage, see Data Definition Language (DDL) Tests-tests).

Test File Overview

The CREATE statement tests are organized into two primary test files located in the test/evidence/ directory:

Both test files follow the SQL Logic Test format and include evidence references to SQLite documentation sections, cross-database compatibility directives, and comprehensive behavioral validation.

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

Test Execution Model

Diagram: Test Execution Flow for CREATE Statement Tests

This state machine illustrates the standard test pattern used across both slt_lang_createtrigger.test and slt_lang_createview.test. Each test file establishes a base table (t1), populates test data, and then executes CREATE statements with validation of both success and error conditions.

Sources: test/evidence/slt_lang_createtrigger.test:3-16 test/evidence/slt_lang_createview.test:3-16 test/evidence/slt_lang_createtrigger.test:18-19

CREATE TRIGGER Tests

Trigger Timing and Event Matrix

The slt_lang_createtrigger.test file validates all combinations of trigger timing (BEFORE/AFTER) and trigger events (INSERT/UPDATE/DELETE):

Diagram: Trigger Test Coverage Matrix

graph LR
    subgraph "Trigger Events"
        INSERT["INSERT"]
UPDATE["UPDATE"]
DELETE["DELETE"]
end
    
    subgraph "Trigger Timing"
        BEFORE["BEFORE"]
AFTER["AFTER"]
NONE["(no timing)"]
end
    
    subgraph "Test Coverage"
        t1r1["t1r1: UPDATE (no timing)"]
t1r2["t1r2: DELETE (no timing)"]
t1r3["t1r3: INSERT (no timing)"]
t1r4["t1r4: UPDATE (no timing)"]
t1r5["t1r5: AFTER DELETE"]
t1r6["t1r6: AFTER INSERT"]
t1r7["t1r7: AFTER UPDATE"]
t1r8["t1r8: BEFORE DELETE"]
t1r9["t1r9: BEFORE INSERT"]
t1r10["t1r10: BEFORE UPDATE"]
end
    
 
   UPDATE --> t1r1
 
   DELETE --> t1r2
 
   INSERT --> t1r3
 
   UPDATE --> t1r4
    
 
   AFTER --> t1r5
 
   AFTER --> t1r6
 
   AFTER --> t1r7
 
   BEFORE --> t1r8
 
   BEFORE --> t1r9
 
   BEFORE --> t1r10
    
 
   DELETE --> t1r5
 
   INSERT --> t1r6
 
   UPDATE --> t1r7
 
   DELETE --> t1r8
 
   INSERT --> t1r9
 
   UPDATE --> t1r10

This diagram maps the 10 trigger test cases (t1r1 through t1r10) to their respective timing and event combinations, demonstrating comprehensive coverage of the CREATE TRIGGER syntax.

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

Basic Trigger Creation Syntax

The test validates basic CREATE TRIGGER syntax with the following pattern:

CREATE TRIGGER trigger_name [BEFORE|AFTER] event ON table_name 
BEGIN 
  sql_statements; 
END;

Key test cases include:

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

Duplicate Trigger Validation

The test file validates that creating a trigger with an existing name produces an error:

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;

This ensures enforcement of unique trigger names within the database schema.

Sources: test/evidence/slt_lang_createtrigger.test:24-29

OLD and NEW Reference Validation

Diagram: OLD and NEW Reference Validity Matrix

The test validates evidence reference R-63660-13730 which specifies: "OLD and NEW references may only be used in triggers on events for which they are relevant, as follows: INSERT NEW references are valid UPDATE NEW and OLD references are valid DELETE OLD references are valid"

Sources: test/evidence/slt_lang_createtrigger.test:65-68

Evidence References

The slt_lang_createtrigger.test file contains extensive evidence references linking test cases to specific sections of SQLite documentation:

The evidence references follow the format R-XXXXX-XXXXX and correspond to specific requirements in the SQLite documentation.

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

Platform Compatibility Directive

The test includes an early halt for Microsoft SQL Server:

onlyif mssql
halt

This directive causes the test to stop execution on MSSQL platforms at line 19, indicating that MSSQL has significant differences in trigger implementation that make the remaining tests incompatible.

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

Trigger Cleanup

All created triggers are explicitly dropped at the end of the test:

statement ok
DROP TRIGGER t1r1
...
statement ok
DROP TRIGGER t1r10

This cleanup ensures test isolation and verifies that the DROP TRIGGER statement functions correctly.

Sources: test/evidence/slt_lang_createtrigger.test:192-219

CREATE VIEW Tests

Basic View Creation

Diagram: View Creation and Query Equivalence

The test validates that a view acts as a "pre-packaged SELECT statement" by demonstrating query equivalence between direct table queries and view queries. Both SELECT x FROM t1 WHERE x>0 and SELECT x FROM view1 produce identical results.

Sources: test/evidence/slt_lang_createview.test:22-37

View Creation Test Cases

Sources: test/evidence/slt_lang_createview.test:22-53

Temporary View Creation (SQLite-Specific)

The test validates SQLite-specific TEMP and TEMPORARY view syntax:

onlyif sqlite
statement ok
CREATE TEMP VIEW view2 AS SELECT x FROM t1 WHERE x>0

onlyif sqlite
statement ok
CREATE TEMPORARY VIEW view3 AS SELECT x FROM t1 WHERE x>0

These tests are guarded by the onlyif sqlite directive, as temporary views are a SQLite-specific feature. Evidence reference R-48816-31606 specifies that temporary views are "only visible to the process that opened the database and is automatically deleted when the database is closed."

Sources: test/evidence/slt_lang_createview.test:42-53

Read-Only View Constraints

Diagram: Platform-Specific View Modification Behavior

The test validates evidence reference R-16775-34716: "You cannot DELETE, INSERT, or UPDATE a view" and R-05363-17893: "Views are read-only in SQLite."

However, the test demonstrates platform-specific behavior using conditional directives:

SQLite behavior (read-only):

onlyif sqlite
statement error
DELETE FROM view1 WHERE x>0

onlyif sqlite
statement error
UPDATE view1 SET x=2

MSSQL behavior (allows UPDATE/DELETE):

onlyif mssql  # this is allowed
statement ok
DELETE FROM view1 WHERE x>0

onlyif mssql  # this is allowed
statement ok
UPDATE view1 SET x=2

Universal behavior (INSERT always fails):

statement error
INSERT INTO view1 VALUES(2,'unknown')

Sources: test/evidence/slt_lang_createview.test:65-103

View Modification Operations Test Matrix

Sources: test/evidence/slt_lang_createview.test:65-103

View Cleanup and Error Validation

Diagram: View Cleanup and Error Condition Validation

The test validates proper error handling for DROP VIEW operations:

1. Successfully dropping existing views
2. Error when dropping already-dropped views
3. Error when dropping non-existent views

Evidence reference R-10484-47921 states: "Views are removed with the DROP VIEW command."

Sources: test/evidence/slt_lang_createview.test:111-128

Cross-Database Compatibility

Platform-Specific Directives Summary

These directives enable the same test file to validate both standard SQL behavior and platform-specific deviations. For comprehensive information on conditional execution, see Conditional Execution Directives.

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

Known Platform Differences

Diagram: Platform-Specific CREATE Statement Behavior

For detailed platform behavior differences, see Platform-Specific Behaviors.

Sources: test/evidence/slt_lang_createtrigger.test:18-19 test/evidence/slt_lang_createview.test:68-103

Test Setup Pattern

Both test files follow a consistent setup pattern:

1. Hash threshold configuration : hash-threshold 8 enables result hashing for large result sets
2. Base table creation : CREATE TABLE t1( x INTEGER, y VARCHAR(8) )
3. Test data insertion : Three rows representing true, false, and NULL values
4. Index creation : CREATE INDEX t1i1 ON t1(x) for query optimization testing

This standardized setup provides a consistent foundation for testing CREATE statements across different scenarios.

Sources: test/evidence/slt_lang_createtrigger.test:1-16 test/evidence/slt_lang_createview.test:1-16

DROP Statement Tests

Relevant source files

• test/evidence/slt_lang_dropindex.test
• test/evidence/slt_lang_droptable.test
• test/evidence/slt_lang_droptrigger.test
• test/evidence/slt_lang_dropview.test

Purpose and Scope

This document details the DROP statement tests within the SQL Logic Test corpus, which validate the behavior of SQL DROP commands for removing database objects. These tests are located in the test/evidence/ directory and cover four primary DROP statement types: DROP INDEX, DROP TABLE, DROP TRIGGER, and DROP VIEW.

The tests verify SQL specification compliance through evidence markers that reference specific requirements from SQL language documentation. Each test file follows a consistent pattern of creating objects, dropping them successfully, and validating error conditions when attempting to drop non-existent or already-dropped objects.

For information about CREATE statement tests, see CREATE Statement Tests. For broader DDL test coverage, see Data Definition Language (DDL) Tests-tests).

Test File Organization

The DROP statement tests are organized into four evidence test files, each targeting a specific database object type:

Sources: test/evidence/slt_lang_dropindex.test:1-35 test/evidence/slt_lang_droptable.test:1-56 test/evidence/slt_lang_droptrigger.test:1-53 test/evidence/slt_lang_dropview.test:1-60

graph TB
    DROP_ROOT["DROP Statement Tests\ntest/evidence/"]
DROP_INDEX["slt_lang_dropindex.test\nDROP INDEX validation"]
DROP_TABLE["slt_lang_droptable.test\nDROP TABLE validation"]
DROP_TRIGGER["slt_lang_droptrigger.test\nDROP TRIGGER validation"]
DROP_VIEW["slt_lang_dropview.test\nDROP VIEW validation"]
DROP_ROOT --> DROP_INDEX
 
   DROP_ROOT --> DROP_TABLE
 
   DROP_ROOT --> DROP_TRIGGER
 
   DROP_ROOT --> DROP_VIEW
    
 
   DROP_INDEX --> IDX_EV["Evidence: R-42037-15614\nIndex removal behavior"]
DROP_TABLE --> TBL_EV1["Evidence: R-01463-03846\nTable removal behavior"]
DROP_TABLE --> TBL_EV2["Evidence: R-33950-57093\nCascading deletion"]
DROP_TABLE --> TBL_EV3["Evidence: R-57089-01510\nIF EXISTS clause"]
DROP_TRIGGER --> TRG_EV1["Evidence: R-61172-15671\nTrigger removal behavior"]
DROP_TRIGGER --> TRG_EV2["Evidence: R-37808-62273\nAuto-drop with table"]
DROP_VIEW --> VW_EV1["Evidence: R-27002-52307\nView removal behavior"]
DROP_VIEW --> VW_EV2["Evidence: R-00359-41639\nData preservation"]

Evidence Markers

Each test file contains evidence markers that reference specific requirements from SQL language specifications:

Sources: test/evidence/slt_lang_dropindex.test:19-20 test/evidence/slt_lang_droptable.test:19-20 test/evidence/slt_lang_droptable.test:36-37 test/evidence/slt_lang_droptable.test:43-44 test/evidence/slt_lang_droptrigger.test:21-22 test/evidence/slt_lang_droptrigger.test:41-42 test/evidence/slt_lang_dropview.test:19-20 test/evidence/slt_lang_dropview.test:44-46

Common Test Pattern

All DROP statement tests follow a consistent execution pattern:

Sources: test/evidence/slt_lang_dropindex.test:3-34 test/evidence/slt_lang_droptable.test:3-31 test/evidence/slt_lang_droptrigger.test:3-36 test/evidence/slt_lang_dropview.test:3-39

Standard Test Setup

Each DROP statement test file begins with identical setup code:

hash-threshold 8

statement ok
CREATE TABLE t1( x INTEGER, y VARCHAR(8) )

statement ok
INSERT INTO t1 VALUES(1,'true')

statement ok
INSERT INTO t1 VALUES(0,'false')

statement ok
INSERT INTO t1 VALUES(NULL,'NULL')

statement ok
CREATE INDEX t1i1 ON t1(x)

This creates a baseline table t1 with three rows and an index t1i1 on column x.

Sources: test/evidence/slt_lang_dropindex.test:1-16 test/evidence/slt_lang_droptable.test:1-16 test/evidence/slt_lang_droptrigger.test:1-16 test/evidence/slt_lang_dropview.test:1-16

DROP INDEX Tests

The DROP INDEX tests validate index removal behavior and are located in test/evidence/slt_lang_dropindex.test

Basic DROP INDEX Behavior

Sources: test/evidence/slt_lang_dropindex.test:19-34

Cross-Database Compatibility

DROP INDEX syntax differs significantly across database platforms. The tests use skipif mssql directives because Microsoft SQL Server requires different syntax:

skipif mssql
statement ok
DROP INDEX t1i1;

In MSSQL, the syntax would be DROP INDEX t1.t1i1 (table.index format), which is incompatible with the standard SQLite syntax tested here.

Sources: test/evidence/slt_lang_dropindex.test:22-34

DROP TABLE Tests

The DROP TABLE tests validate table removal and cascading deletion behavior, located in test/evidence/slt_lang_droptable.test

graph TB
    DROP_TABLE["DROP TABLE t1"]
subgraph "Affected Objects"
        TABLE["Table t1\nRemoved from schema"]
INDICES["All indices on t1\n(e.g., t1i1)"]
TRIGGERS["All triggers on t1\n(e.g., t1r1)"]
DATA["Table data\nRemoved from disk"]
end
    
 
   DROP_TABLE --> TABLE
 
   DROP_TABLE --> INDICES
 
   DROP_TABLE --> TRIGGERS
 
   DROP_TABLE --> DATA
    
 
   INDICES -.-> IDX_ERROR["Subsequent DROP INDEX t1i1\nproduces error"]
TRIGGERS -.-> TRG_ERROR["Subsequent DROP TRIGGER t1r1\nproduces error"]

Cascading Deletion

A critical aspect of DROP TABLE is the automatic deletion of associated database objects:

Sources: test/evidence/slt_lang_droptable.test:36-41

The test validates cascading deletion by attempting to drop the index after the table:

statement ok
DROP TABLE t1

# this should error, as was dropped with table
statement error
DROP INDEX t1i1;

This verifies evidence marker R-33950-57093: "All indices and triggers associated with the table are also deleted."

Sources: test/evidence/slt_lang_droptable.test:36-41

IF EXISTS Clause

The DROP TABLE tests include validation of the IF EXISTS clause:

statement ok
CREATE TABLE t1( x INTEGER, y VARCHAR(8) )

skipif mssql
statement ok
DROP TABLE IF EXISTS t1

skipif mssql
statement ok
DROP TABLE IF EXISTS t1

The second DROP TABLE IF EXISTS t1 succeeds without error because the IF EXISTS clause suppresses the error that would normally occur when dropping a non-existent table.

Sources: test/evidence/slt_lang_droptable.test:43-55

Error Conditions

The tests validate three error scenarios:

1. Already dropped table : Attempting to drop a table that was previously dropped in the same test session
2. Non-existent table : Attempting to drop a table name that was never created
3. Cascaded object : Attempting to drop an index that was automatically removed when its parent table was dropped

Sources: test/evidence/slt_lang_droptable.test:25-31 test/evidence/slt_lang_droptable.test:39-41

DROP TRIGGER Tests

The DROP TRIGGER tests validate trigger removal and automatic deletion behavior, located in test/evidence/slt_lang_droptrigger.test

Platform Compatibility

DROP TRIGGER tests are not compatible with Microsoft SQL Server:

onlyif mssql
halt

This halts execution on MSSQL platforms because the trigger syntax is significantly different from SQLite.

Sources: test/evidence/slt_lang_droptrigger.test:18-19

Trigger Creation and Deletion

Sources: test/evidence/slt_lang_droptrigger.test:24-36 test/evidence/slt_lang_droptrigger.test:44-52

Automatic Trigger Deletion

The tests validate that triggers are automatically deleted when their associated table is dropped:

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

statement ok
DROP TABLE t1

# already deleted when table dropped
statement error
DROP TRIGGER t1r1

This verifies evidence marker R-37808-62273: "triggers are automatically dropped when the associated table is dropped."

Sources: test/evidence/slt_lang_droptrigger.test:41-52

DROP VIEW Tests

The DROP VIEW tests validate view removal while ensuring underlying data integrity, located in test/evidence/slt_lang_dropview.test

View Definition Removal

Sources: test/evidence/slt_lang_dropview.test:27-31

Data Preservation Validation

The tests explicitly verify that dropping a view does not affect data in the underlying base tables:

statement ok
CREATE VIEW view2 AS SELECT x FROM t1 WHERE x=0

query I rowsort label-0
SELECT x FROM view2
----
0

statement ok
DROP VIEW view2

query I rowsort label-0
SELECT x FROM t1 WHERE x=0
----
0

This sequence:

1. Creates view view2 that filters t1 for rows where x=0
2. Queries through the view, confirming result is 0
3. Drops the view
4. Directly queries the base table with the same filter, confirming data is still 0

This verifies evidence marker R-00359-41639: "no actual data in the underlying base tables is modified."

Sources: test/evidence/slt_lang_dropview.test:44-59

Object Resolution

The tests validate standard object resolution for view names:

statement ok
CREATE VIEW view1 AS SELECT x FROM t1 WHERE x>0

statement ok
DROP VIEW view1

This verifies evidence marker R-18673-21346 regarding view-name and optional schema-name resolution using standard object resolution procedures.

Sources: test/evidence/slt_lang_dropview.test:22-31

Error Testing Matrix

All DROP statement tests validate a consistent set of error conditions:

Sources: test/evidence/slt_lang_dropindex.test:26-34 test/evidence/slt_lang_droptable.test:25-41 test/evidence/slt_lang_droptrigger.test:30-36 test/evidence/slt_lang_dropview.test:33-39

flowchart TD
 
   START["Execute DROP test"] --> CHECK_PLATFORM{"Platform?"}
CHECK_PLATFORM -->|SQLite| RUN_ALL["Run all tests\nStandard DROP syntax"]
CHECK_PLATFORM -->|MSSQL| SKIP_SOME["skipif mssql directives\nSkip incompatible tests"]
SKIP_SOME --> DROP_INDEX_SKIP["DROP INDEX skipped\nDifferent syntax required"]
SKIP_SOME --> IF_EXISTS_SKIP["IF EXISTS clause skipped\nNot supported in MSSQL"]
SKIP_SOME --> DROP_TRIGGER_HALT["DROP TRIGGER halted\nIncompatible syntax"]
RUN_ALL --> COMPLETE["All tests executed"]
DROP_INDEX_SKIP --> COMPLETE
 
   IF_EXISTS_SKIP --> COMPLETE
 
   DROP_TRIGGER_HALT --> HALT["Test execution stopped"]

Cross-Database Compatibility Summary

The DROP statement tests use conditional directives to handle platform-specific differences:

Sources: test/evidence/slt_lang_dropindex.test:22-34 test/evidence/slt_lang_droptable.test:49-55 test/evidence/slt_lang_droptrigger.test:18-19

Platform-Specific Behaviors

Sources: test/evidence/slt_lang_dropindex.test22 test/evidence/slt_lang_droptable.test49 test/evidence/slt_lang_droptrigger.test:18-19

graph TB
    subgraph "Test Coverage"
        BASIC["Basic DROP operations\nSuccess cases"]
ERRORS["Error conditions\nDouble-drop, non-existent"]
CASCADE["Cascading deletion\nIndices, triggers"]
IF_EXISTS["IF EXISTS clause\nError suppression"]
DATA_INTEGRITY["Data integrity\nView removal validation"]
end
    
    subgraph "Test Files"
        DROP_INDEX_FILE["slt_lang_dropindex.test"]
DROP_TABLE_FILE["slt_lang_droptable.test"]
DROP_TRIGGER_FILE["slt_lang_droptrigger.test"]
DROP_VIEW_FILE["slt_lang_dropview.test"]
end
    
 
   DROP_INDEX_FILE --> BASIC
 
   DROP_INDEX_FILE --> ERRORS
    
 
   DROP_TABLE_FILE --> BASIC
 
   DROP_TABLE_FILE --> ERRORS
 
   DROP_TABLE_FILE --> CASCADE
 
   DROP_TABLE_FILE --> IF_EXISTS
    
 
   DROP_TRIGGER_FILE --> BASIC
 
   DROP_TRIGGER_FILE --> ERRORS
 
   DROP_TRIGGER_FILE --> CASCADE
    
 
   DROP_VIEW_FILE --> BASIC
 
   DROP_VIEW_FILE --> ERRORS
 
   DROP_VIEW_FILE --> DATA_INTEGRITY

Test Execution Summary

The DROP statement tests validate the complete lifecycle of database object removal:

Sources: test/evidence/slt_lang_dropindex.test:1-35 test/evidence/slt_lang_droptable.test:1-56 test/evidence/slt_lang_droptrigger.test:1-53 test/evidence/slt_lang_dropview.test:1-60

Data Manipulation Language (DML) Tests

Relevant source files

• test/evidence/slt_lang_replace.test
• test/evidence/slt_lang_update.test

Purpose and Scope

This document describes the Data Manipulation Language (DML) test suite within the SQL Language Evidence Tests category. DML tests validate the behavior of SQL statements that modify data within existing database tables, specifically:

• UPDATE statements for modifying existing rows
• REPLACE and INSERT OR REPLACE statements for insert-or-update operations

These tests ensure compliance with SQL standards and SQLite-specific behaviors for data manipulation operations. For information about creating or dropping database objects, see Data Definition Language (DDL) Tests-tests). For query validation, see Query Operator Tests.

Sources: test/evidence/slt_lang_update.test:1-198 test/evidence/slt_lang_replace.test:1-76

Test File Organization

The DML test suite consists of two primary test files located in the test/evidence/ directory:

Both files employ the SQL Logic Test format with evidence markers that reference specific requirements from the SQLite documentation.

Sources: test/evidence/slt_lang_update.test1 test/evidence/slt_lang_replace.test1

UPDATE Statement Test Coverage

Core UPDATE Functionality

The slt_lang_update.test file validates fundamental UPDATE statement behavior through a series of progressive tests that build upon a simple two-column table schema:

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

graph TD
    Setup["Table Setup\nCREATE TABLE t1(x INTEGER, y VARCHAR(8))\nCREATE INDEX t1i1 ON t1(x)"]
Setup --> InitData["Initial Data\nINSERT INTO t1 VALUES(1,'true')\nINSERT INTO t1 VALUES(0,'false')\nINSERT INTO t1 VALUES(NULL,'NULL')"]
InitData --> BasicUpdate["Basic UPDATE Tests\nEvidence: R-38515-45264"]
InitData --> WhereClause["WHERE Clause Tests\nEvidence: R-58095-46013, R-58129-20729"]
InitData --> MultiAssign["Multiple Assignment Tests\nEvidence: R-34751-18293"]
InitData --> SelfRef["Self-Referencing Tests\nEvidence: R-36239-04077, R-04558-24451"]
BasicUpdate --> Validate["Query and Validate Results"]
WhereClause --> Validate
 
   MultiAssign --> Validate
 
   SelfRef --> Validate

WHERE Clause Behavior

The tests validate three key WHERE clause scenarios:

1. Unconditional UPDATE (no WHERE clause) - All rows affected
2. Conditional UPDATE - Only matching rows affected
3. Non-matching WHERE - Zero rows affected (not an error)

Sources: test/evidence/slt_lang_update.test:43-70

Multiple Column Assignment

Test case at test/evidence/slt_lang_update.test:83-108 validates the behavior when the same column appears multiple times in the assignment list. According to Evidence R-34751-18293, only the rightmost assignment is used:

Note: This test is skipped on MSSQL platforms using the skipif mssql directive, as MSSQL treats multiple assignments to the same column as an error.

Sources: test/evidence/slt_lang_update.test:83-108

Self-Referencing Expressions

Tests validate that scalar expressions in UPDATE statements can reference columns from the row being updated, and that all expressions are evaluated before any assignments are made:

Sources: test/evidence/slt_lang_update.test:127-138

sequenceDiagram
    participant SQL as "UPDATE t1 SET x=x+2"
    participant Eval as "Expression Evaluator"
    participant Store as "Row Storage"
    
    Note over SQL,Store: Evidence: R-36239-04077, R-04558-24451
    
    SQL->>Eval: For each row
    Eval->>Store: Read current x value
    Store-->>Eval: x = 2
    Eval->>Eval: Evaluate x+2 = 4
    Note over Eval: All expressions evaluated\nBEFORE assignments
    Eval->>Store: Write x = 4
    Store-->>SQL: Row updated

Unmodified Columns

Evidence R-40472-60438 validates that columns not mentioned in the assignment list remain unchanged. Test test/evidence/slt_lang_update.test:109-124 verifies this by updating column x while monitoring that column y retains its original value.

Sources: test/evidence/slt_lang_update.test:109-124

REPLACE Statement Test Coverage

REPLACE as INSERT OR REPLACE Alias

The slt_lang_replace.test file validates Evidence R-03421-22330, which states that "The REPLACE command is an alias for the 'INSERT OR REPLACE' variant of the INSERT command."

Sources: test/evidence/slt_lang_replace.test:21-56

graph LR
    subgraph "Test Table Schema"
        T1["t1(x INTEGER PRIMARY KEY, y VARCHAR(16))"]
end
    
    subgraph "Test Progression"
        Insert["INSERT INTO t1 VALUES(2, 'insert')"]
InsertOrReplace["INSERT OR REPLACE INTO t1 VALUES(2, 'insert or replace')"]
Replace["REPLACE INTO t1 VALUES(2, 'replace')"]
Insert -->|Verify: x=2, y='insert'| V1["Query: SELECT x,y FROM t1 WHERE x=2"]
InsertOrReplace -->|Verify: x=2, y='insert or replace'| V2["Query: SELECT x,y FROM t1 WHERE x=2"]
Replace -->|Verify: x=2, y='replace'| V3["Query: SELECT x,y FROM t1 WHERE x=2"]
end
    
 
   T1 --> Insert

Test Execution Pattern

The REPLACE tests follow a consistent pattern validating both update and insert semantics:

Sources: test/evidence/slt_lang_replace.test:24-76

Cross-Platform Compatibility

flowchart TD
    Start["Test File Execution Begins"]
Start --> CheckMSSQL{"Platform\nMSSQL?"}
CheckMSSQL -->|Yes| HaltMSSQL["halt\n(slt_lang_replace.test:1-3)"]
CheckMSSQL -->|No| CheckOracle{"Platform\nOracle?"}
CheckOracle -->|Yes| HaltOracle["halt\n(slt_lang_replace.test:5-7)"]
CheckOracle -->|No| RunTests["Execute Test Cases"]
RunTests --> MultiAssignCheck{"Test: Multiple\ncolumn assignment?"}
MultiAssignCheck -->|Yes| SkipMSSQL["skipif mssql\n(slt_lang_update.test:87)"]
MultiAssignCheck -->|No| Execute["Execute Test"]
SkipMSSQL -->|MSSQL| Skip["Skip Test"]
SkipMSSQL -->|Other DB| Execute
    
 
   RunTests --> ReplaceCheck{"Test: INSERT OR\nREPLACE syntax?"}
ReplaceCheck -->|Yes| SkipMySQL["skipif mysql\n(slt_lang_replace.test:37,57)"]
ReplaceCheck -->|No| Execute
    
 
   SkipMySQL -->|MySQL| Skip
 
   SkipMySQL -->|Other DB| Execute

Platform Exclusions

Both DML test files include conditional execution directives to handle database platform differences:

Sources: test/evidence/slt_lang_replace.test:1-7 test/evidence/slt_lang_update.test87 test/evidence/slt_lang_replace.test:37-57

Platform-Specific Behavior Matrix

Sources: test/evidence/slt_lang_update.test:87-108 test/evidence/slt_lang_replace.test:1-66

Evidence Markers and Validation

UPDATE Statement Evidence Coverage

The UPDATE tests validate the following evidence requirements from SQLite documentation:

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

REPLACE Statement Evidence Coverage

Sources: test/evidence/slt_lang_replace.test:21-76

erDiagram
    t1 {INTEGER x PK\nVARCHAR_8 y}
    
    t1_index["t1i1"] {INDEX_ON x}
    
    t1 ||--o{ t1_index : indexed by

Test Data Patterns

UPDATE Test Dataset

The UPDATE tests operate on a minimal dataset designed to test NULL handling, boolean-like values, and indexed columns:

Initial data:

• Row 1: x=1, y='true'
• Row 2: x=0, y='false'
• Row 3: x=NULL, y='NULL'

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

erDiagram
    t1 {INTEGER x PK "PRIMARY KEY"\nVARCHAR_16 y}

REPLACE Test Dataset

The REPLACE tests use a simpler schema with a primary key constraint to test upsert behavior:

Initial data:

• Row 1: x=1, y='true'
• Row 2: x=0, y='false'

Sources: test/evidence/slt_lang_replace.test:11-18

Hash Threshold Configuration

Both DML test files specify hash-threshold 8 at the beginning:

• test/evidence/slt_lang_update.test1
• test/evidence/slt_lang_replace.test9

This directive controls when the test harness switches from storing individual result rows to using hash-based comparison for large result sets. A threshold of 8 means result sets with more than 8 rows will be validated using hash comparison instead of row-by-row comparison.

Sources: test/evidence/slt_lang_update.test1 test/evidence/slt_lang_replace.test9

Unimplemented Evidence (TBD)

The UPDATE test file contains several evidence markers prefixed with TBD-EVIDENCE-OF, indicating requirements that are documented but not yet tested:

• Conflict clause handling (R-12619-24112)
• Trigger-specific UPDATE restrictions (R-12123-54095, R-09690-36749, R-06085-13761, R-29512-54644)
• INDEXED BY/NOT INDEXED in triggers (R-19619-42762)
• LIMIT and ORDER BY clauses (R-57359-59558, R-59581-44104, R-58862-44169, R-63582-45120, R-18628-11938, R-30955-38324, R-19486-35828, R-10927-26133)

These represent advanced UPDATE features that require additional test infrastructure or are only available with specific compile-time options like SQLITE_ENABLE_UPDATE_DELETE_LIMIT.

Sources: test/evidence/slt_lang_update.test:139-198

Query Operator Tests

Relevant source files

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

Purpose and Scope

This document describes the query operator tests in the evidence test suite, focusing on SQL operators used in SELECT queries. These tests validate the behavior of IN/NOT IN operators and aggregate functions across different database platforms.

For detailed coverage of specific operators:

• IN/NOT IN operator behavior with NULL values and empty sets: see IN and NOT IN Operator Tests
• Aggregate function behavior with DISTINCT and NULL handling: see Aggregate Function Tests
• Data manipulation operations (UPDATE, REPLACE): see Data Manipulation Language (DML) Tests-tests)

Test File Organization

Query operator tests are located in the test/evidence/ directory and organized by operator type:

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

Test File to Operator Mapping

Sources: test/evidence/in1.test:11-20 test/evidence/in1.test:329-336 test/evidence/in2.test:2-21 test/evidence/in2.test:292-295 test/evidence/slt_lang_aggfunc.test:24-27

IN and NOT IN Operator Tests

Test Coverage

The IN/NOT IN operator tests validate compliance with SQL specification evidence tags, particularly focusing on edge cases not commonly tested:

Empty Right-Hand Side (RHS)

• Tests validate SQLite's non-standard support for empty lists: SELECT 1 IN ()
• Most databases (MySQL, MSSQL, Oracle) skip these tests via conditional directives
• Evidence tag: R-52275-55503 and R-64309-54027

Table-as-Operand Syntax

• SQLite allows direct table references: SELECT 1 IN t1
• Other databases require subquery form: SELECT 1 IN (SELECT * FROM t1)
• Tests use onlyif sqlite directives for direct table syntax

NULL Value Handling

• Comprehensive matrix testing of NULL in left operand, right operand, and both
• Tests validate three-valued logic (true, false, NULL) for all combinations

Sources: test/evidence/in1.test:11-20 test/evidence/in1.test:22-26 test/evidence/in1.test:69-73 test/evidence/in2.test:2-21

Behavior Matrix Implementation

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

Test Execution Pattern

The tests systematically cover each row of the behavior matrix documented in evidence tag R-50221-42915:

Sources: test/evidence/in1.test:329-357 test/evidence/in1.test:555-791 test/evidence/in1.test:795-1155

Aggregate Function Tests

graph LR
    subgraph "Aggregate Functions Tested"
        COUNT["count(x)\ncount(DISTINCT x)\ncount(*)"]
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,sep)"]
end
    
    subgraph "Test Aspects"
        DIST["DISTINCT filtering\nR-00466-56349"]
NULL_HAND["NULL handling\nPer-function rules"]
TYPE_CONV["Type conversion\nString to number"]
RETURN_TYPE["Return type\nInteger vs Float"]
end
    
 
   COUNT --> DIST
 
   AVG --> DIST
 
   SUM --> DIST
 
   TOTAL --> DIST
 
   MIN --> DIST
 
   MAX --> DIST
 
   CONCAT --> DIST
    
 
   COUNT --> NULL_HAND
 
   AVG --> NULL_HAND
 
   SUM --> NULL_HAND
    
 
   AVG --> RETURN_TYPE
 
   SUM --> RETURN_TYPE
 
   TOTAL --> RETURN_TYPE

Supported Functions

The slt_lang_aggfunc.test file validates all standard SQL aggregate functions:

Sources: test/evidence/slt_lang_aggfunc.test:21-22 test/evidence/slt_lang_aggfunc.test:24-62

DISTINCT Keyword Validation

All aggregate functions support the DISTINCT keyword, which filters duplicate values before aggregation:

Evidence Tag:R-00466-56349

• Tests verify: count(DISTINCT x), avg(DISTINCT x), sum(DISTINCT x), etc.
• Validation approach: Insert duplicate values, compare results with and without DISTINCT

Example Test Pattern:

INSERT INTO t1 VALUES(2,'true')
INSERT INTO t1 VALUES(2,'true')  -- Duplicate value
SELECT count(DISTINCT x) FROM t1  -- Should count 2 once

Sources: test/evidence/slt_lang_aggfunc.test:24-62 test/evidence/slt_lang_aggfunc.test:71-81

NULL Handling Per Function

Each aggregate function has distinct NULL handling behavior validated by the tests:

Sources: test/evidence/slt_lang_aggfunc.test:198-234 test/evidence/slt_lang_aggfunc.test:311-370 test/evidence/slt_lang_aggfunc.test:396-418

Type Conversion and Return Types

The tests validate type coercion and return type behavior:

String to Number Conversion (Evidence:R-29052-00975):

• Non-numeric strings are interpreted as 0
• Tests use VARCHAR column y with values like 'true', 'false', 'NULL'
• Aggregate functions treat these as 0

Return Type Rules:

• avg(x): Always returns float if any non-NULL input (Evidence: R-17177-10067)
• total(x): Always returns float (Evidence: R-07734-01023)
• sum(x): Returns integer if all inputs are integers, else float (Evidence: R-19660-56479, R-33611-59266)

Test Implementation:

-- avg always returns float
SELECT avg(x) FROM t1  -- Returns 1.250 (float) not 1

-- sum returns integer with integer inputs
SELECT sum(x) FROM t1  -- Returns 5 (integer)

-- sum returns float with any float input
INSERT INTO t1 VALUES(4.0,'true')
SELECT sum(x) FROM t1  -- Returns 9.000 (float)

Sources: test/evidence/slt_lang_aggfunc.test:92-124 test/evidence/slt_lang_aggfunc.test:168-182 test/evidence/slt_lang_aggfunc.test:420-464

flowchart TD
 
   START["Test Execution Begins"] --> CHECK_PLATFORM{"Platform\nDetection"}
CHECK_PLATFORM -->|onlyif mssql halt| SKIP_MSSQL["Skip entire file\n(MSSQL)"]
CHECK_PLATFORM -->|onlyif oracle halt| SKIP_ORACLE["Skip entire file\n(Oracle)"]
CHECK_PLATFORM -->|SQLite or MySQL| CONTINUE["Continue execution"]
CONTINUE --> CHECK_FEATURE{"Feature\nCheck"}
CHECK_FEATURE -->|onlyif sqlite| SQLITE_ONLY["Execute SQLite-only tests\n- Empty RHS\n- Table-as-operand"]
CHECK_FEATURE -->|skipif mysql| SKIP_MYSQL["Skip MySQL-incompatible\n- Empty RHS\n- Some NULL tests"]
CHECK_FEATURE -->|Common SQL| COMMON["Execute cross-platform\ncompatible tests"]
SQLITE_ONLY --> VALIDATE["Validate Results"]
SKIP_MYSQL --> VALIDATE
 
   COMMON --> VALIDATE
    
 
   SKIP_MSSQL --> END["Test Complete"]
SKIP_ORACLE --> END
 
   VALIDATE --> END

Cross-Database Compatibility

Platform-Specific Test Execution

The query operator tests use conditional directives to handle database differences:

Sources: test/evidence/in1.test:3-9 test/evidence/in1.test:22-26 test/evidence/in1.test:69-73

Platform Compatibility Matrix

Sources: test/evidence/in1.test:22-64 test/evidence/in1.test:398-427 test/evidence/slt_lang_aggfunc.test:18-19

MySQL-Specific Workarounds

MySQL requires special handling in several test scenarios:

UNIQUE Index on TEXT Columns:

• SQLite: CREATE TABLE t7(a TEXT UNIQUE)
• MySQL: CREATE TABLE t7(a TEXT, UNIQUE (a(1))) -- Requires length prefix

NULL in Subqueries:

• Some NULL IN (SELECT ...) tests are skipped on MySQL due to known failures
• Tests marked with skipif mysql comments

Sources: test/evidence/in1.test:398-404 test/evidence/in1.test:421-427 test/evidence/in1.test:951-975

Test Result Validation

Label-Based Test Groups

Tests use label markers to group related assertions that should produce identical results:

query I nosort label-1
SELECT 1 IN t1                    -- SQLite-only direct table syntax
----
0

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

Both queries share label-1 and must return the same result (0), validating that SQLite's table-as-operand syntax is equivalent to standard subquery form.

Sources: test/evidence/in1.test:69-78 test/evidence/in1.test:441-461

Test Data Setup Pattern

Query operator tests follow a consistent setup pattern:

1. Create tables with different index configurations (UNIQUE, PRIMARY KEY, no index)
2. Populate with base data and NULL-containing variants
3. Execute operator tests against each table variant
4. Validate results across table types are consistent

Table Types Used:

• t1, t2, t3: Base tables (no index, PRIMARY KEY, UNIQUE)
• t4, t5, t6: Integer tables with different indexes
• t4n, t6n: Same as above but with NULL values added
• t7, t8: Text tables with different indexes
• t7n, t8n: Text tables with NULL values

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

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

Aggregate Function Tests

Relevant source files

• test/evidence/slt_lang_aggfunc.test

Purpose and Scope

This document describes the aggregate function test suite in the SQL Logic Test corpus. The test file validates the behavior of SQLite's aggregate functions including count(), avg(), sum(), total(), min(), max(), and group_concat(). Tests cover standard aggregate operations, the DISTINCT keyword, NULL handling, type coercion, and return type behavior.

For query operator tests including IN/NOT IN operators, see IN and NOT IN Operator Tests. For other SQL language evidence tests, see SQL Language Evidence Tests.

Sources: test/evidence/slt_lang_aggfunc.test:1-500

Test File Overview

The aggregate function tests are located in a single file that validates compliance with SQLite documentation requirements. Each test includes "EVIDENCE-OF" comments referencing specific SQLite documentation clauses.

Platform Execution:

Sources: test/evidence/slt_lang_aggfunc.test:1-19

Test Data Structure

The tests use a simple table structure with carefully designed data to validate edge cases:

Initial Test Data:

erDiagram
    t1 {INTEGER x "Primary test column"\nVARCHAR y "Labels for filtering"}
    
    t1_data {INTEGER x "1, 0, NULL, 2, 2, 4.0, 1<<63, 1<<63, -1"\nVARCHAR y "true, false, NULL, true, true, true, true, true, true"}
    
    t1_index {INDEX t1i1 "ON t1(x) - created then dropped"}
    
    t1 ||--|| t1_data : contains
    t1 ||--o{ t1_index : has

Sources: test/evidence/slt_lang_aggfunc.test:3-16 test/evidence/slt_lang_aggfunc.test:71-77 test/evidence/slt_lang_aggfunc.test:452-478

DISTINCT Keyword Support

All single-argument aggregate functions support the DISTINCT keyword, which filters duplicate values before aggregation.

DISTINCT Behavior Examples:

stateDiagram-v2
    [*] --> ReadRow : Aggregate function execution
    
    ReadRow --> CheckDistinct : Process row
    
    CheckDistinct --> NormalAgg : No DISTINCT
    CheckDistinct --> DistinctFilter : DISTINCT keyword present
    
    DistinctFilter --> SeenBefore{"Value already\nseen?"}
    SeenBefore -->|Yes| ReadRow : Skip row
    SeenBefore -->|No| AddToSet : Add to seen set
    
    AddToSet --> NormalAgg : Process value
    NormalAgg --> Accumulate : Update aggregate
    
    Accumulate --> ReadRow : Next row
    ReadRow --> [*] : End of data

Evidence Reference: R-00466-56349 and R-00171-59428

Sources: test/evidence/slt_lang_aggfunc.test:24-82

NULL Handling Behavior

Each aggregate function handles NULL values differently. NULL values are filtered out before aggregation, but the return value when all inputs are NULL varies by function.

NULL Processing Matrix:

Test Examples:

The test validates NULL handling by querying rows where y='null', which contains only a NULL value for x:

• SELECT avg(x) FROM t1 WHERE y='null' → NULL
• SELECT sum(x) FROM t1 WHERE y='null' → NULL
• SELECT total(x) FROM t1 WHERE y='null' → 0
• SELECT count(x) FROM t1 WHERE y='null' → 0

Sources: test/evidence/slt_lang_aggfunc.test:184-196 test/evidence/slt_lang_aggfunc.test:396-418

Type Coercion and String Handling

Aggregate functions interpret non-numeric strings as 0 when performing arithmetic operations.

String Coercion Test Results:

flowchart LR
    Input["Input Value"]
CheckType{"Value Type"}
IsNumber["Use numeric value"]
IsString["Check if looks\nlike number"]
IsNull["Exclude from\naggregation"]
LooksNumeric{"Parseable\nas number?"}
UseZero["Interpret as 0"]
ParseValue["Parse to number"]
Input --> CheckType
 
   CheckType -->|INTEGER/REAL| IsNumber
 
   CheckType -->|TEXT/BLOB| IsString
 
   CheckType -->|NULL| IsNull
    
 
   IsString --> LooksNumeric
 
   LooksNumeric -->|No| UseZero
 
   LooksNumeric -->|Yes| ParseValue

Testing with y VARCHAR(8) column containing values: 'true', 'false', 'NULL'

Evidence Reference: R-29052-00975

Sources: test/evidence/slt_lang_aggfunc.test:92-166

graph TD
    AvgFunc["avg()"]
SumFunc["sum()"]
TotalFunc["total()"]
CountFunc["count()"]
OtherFunc["min() / max() /\ngroup_concat()"]
AvgAlways["Always REAL\n(floating point)"]
SumCheck{"All inputs\nare INTEGER?"}
SumInt["Return INTEGER"]
SumFloat["Return REAL"]
TotalAlways["Always REAL"]
CountAlways["Always INTEGER"]
OtherMatch["Return type matches\ninput column type"]
AvgFunc --> AvgAlways
 
   SumFunc --> SumCheck
 
   SumCheck -->|Yes| SumInt
 
   SumCheck -->|No| SumFloat
 
   TotalFunc --> TotalAlways
 
   CountFunc --> CountAlways
 
   OtherFunc --> OtherMatch
    
    style AvgAlways fill:#f9f9f9
    style SumInt fill:#f9f9f9
    style SumFloat fill:#f9f9f9
    style TotalAlways fill:#f9f9f9
    style CountAlways fill:#f9f9f9
    style OtherMatch fill:#f9f9f9

Return Type Behavior

Aggregate functions return different types depending on the input data and function semantics.

Return Type Rules:

Type Coercion Examples:

Evidence References:

• R-17177-10067 (avg always returns REAL)
• R-19660-56479 (sum returns INTEGER for all-integer input)
• R-33611-59266 (sum returns REAL if any non-integer input)
• R-07734-01023 (total always returns REAL)

Sources: test/evidence/slt_lang_aggfunc.test:168-182 test/evidence/slt_lang_aggfunc.test:420-464

Aggregate Functions Reference

count()

Syntax: count(x) or count(DISTINCT x) or count(*)

Behavior:

• count(x): Returns the number of non-NULL values in column x
• count(*): Returns the total number of rows in the group
• count(DISTINCT x): Returns the number of distinct non-NULL values

Return Type: INTEGER

NULL Handling: NULLs are excluded; returns 0 if no non-NULL values

Special Cases:

• count(DISTINCT *) is a syntax error
• count(*) counts all rows, including those with all NULL columns

Test Coverage: test/evidence/slt_lang_aggfunc.test:28-31 test/evidence/slt_lang_aggfunc.test:198-234

Evidence References: R-34280-42283, R-13776-21310

avg()

Syntax: avg(x) or avg(DISTINCT x)

Behavior:

• Returns the average of all non-NULL values
• Always returns a floating-point value, even for integer inputs

Return Type: REAL (always)

NULL Handling: Returns NULL if all values are NULL

Type Coercion: Non-numeric strings interpreted as 0

Test Coverage: test/evidence/slt_lang_aggfunc.test:33-36 test/evidence/slt_lang_aggfunc.test:83-90 test/evidence/slt_lang_aggfunc.test:168-182

Evidence References: R-20409-33051, R-17177-10067, R-40597-22164

sum()

Syntax: sum(x) or sum(DISTINCT x)

Behavior:

• Returns the sum of all non-NULL values
• Return type depends on input: INTEGER if all inputs are integers, REAL otherwise
• Throws "integer overflow" exception on overflow with all-integer input

Return Type: INTEGER (all-integer input) or REAL (mixed input)

NULL Handling: Returns NULL if all values are NULL

Overflow Behavior: Integer overflow causes exception; test demonstrates with 1<<63 values

Test Coverage: test/evidence/slt_lang_aggfunc.test:38-41 test/evidence/slt_lang_aggfunc.test:375-394 test/evidence/slt_lang_aggfunc.test:434-464

Evidence References: R-24943-34514, R-19660-56479, R-33611-59266

total()

Syntax: total(x) or total(DISTINCT x)

Behavior:

• Similar to sum() but always returns REAL
• Returns 0.0 instead of NULL when all values are NULL
• Never throws integer overflow exception

Return Type: REAL (always)

NULL Handling: Returns 0.0 if all values are NULL

Comparison with sum():

Test Coverage: test/evidence/slt_lang_aggfunc.test:43-46 test/evidence/slt_lang_aggfunc.test:420-432 test/evidence/slt_lang_aggfunc.test:489-499

Evidence References: R-24943-34514, R-44223-43966, R-07734-01023

min()

Syntax: min(x) or min(DISTINCT x)

Behavior:

• Returns the minimum non-NULL value
• "Minimum" is the first value that would appear in ORDER BY x
• Uses column's collation for TEXT comparisons

Return Type: Matches input column type

NULL Handling: Returns NULL if all values are NULL

DISTINCT Effect: No practical difference (min is min regardless of duplicates)

Test Coverage: test/evidence/slt_lang_aggfunc.test:48-51 test/evidence/slt_lang_aggfunc.test:325-370

Evidence References: R-16028-39081, R-30311-39793, R-10396-30188

max()

Syntax: max(x) or max(DISTINCT x)

Behavior:

• Returns the maximum non-NULL value
• "Maximum" is the last value that would appear in ORDER BY x
• Uses column's collation for TEXT comparisons

Return Type: Matches input column type

NULL Handling: Returns NULL if all values are NULL

DISTINCT Effect: No practical difference (max is max regardless of duplicates)

Test Coverage: test/evidence/slt_lang_aggfunc.test:53-56 test/evidence/slt_lang_aggfunc.test:277-323

Evidence References: R-52585-35928, R-13053-11096, R-50775-16353

group_concat()

Syntax: group_concat(x) or group_concat(x, sep) or group_concat(DISTINCT x)

Behavior:

• Concatenates all non-NULL values into a single string
• Default separator is comma (",")
• Optional second parameter specifies custom separator
• DISTINCT can only be used with single-parameter form

Return Type: TEXT

NULL Handling:

• NULL values are excluded from concatenation
• Returns NULL if all values are NULL

Separator Rules:

• Default: ","
• Custom: second parameter (e.g., group_concat(x, ':'))
• DISTINCT with custom separator is syntax error

Examples:

Test Coverage: test/evidence/slt_lang_aggfunc.test:58-62 test/evidence/slt_lang_aggfunc.test:236-275

Evidence References: R-56088-25150, R-08600-21007, R-39910-14723

Sources: test/evidence/slt_lang_aggfunc.test:1-500

flowchart TD
    LargeInt["Large Integer Input\n(1<<63)"]
SumFunc["sum()
function"]
TotalFunc["total()
function"]
AllInt{"All inputs\nare INTEGER?"}
Overflow["Integer Overflow\nException Thrown"]
FloatResult["Convert to REAL\nReturn approximation"]
NoException["No Exception\nReturn REAL result"]
LargeInt --> SumFunc
 
   LargeInt --> TotalFunc
    
 
   SumFunc --> AllInt
 
   AllInt -->|Yes| Overflow
 
   AllInt -->|No| FloatResult
    
 
   TotalFunc --> NoException

Integer Overflow Handling

The test suite includes specific validation for integer overflow behavior when using large values.

Overflow Test Data:

• Inserts 1<<63 (9223372036854775808) twice
• Inserts -1 once
• Expected overflow when summing

Test Results:

Evidence References:

• R-08904-24719 (sum() throws integer overflow)
• R-19553-64528 (total() never throws overflow)

Sources: test/evidence/slt_lang_aggfunc.test:466-499

Test Execution Directives

The aggregate function test file uses specific directives to control test execution and result comparison.

Key Directives Used:

Platform Restriction:

The file begins with a platform check that halts execution on non-SQLite databases:

skipif sqlite
halt

This inverse logic (skip if SQLite, then halt) means: "If this is NOT SQLite, halt execution."

Sources: test/evidence/slt_lang_aggfunc.test:1-500

Evidence-Based Testing

The test file validates specific SQLite documentation requirements. Each test section includes comments with "EVIDENCE-OF" markers.

Evidence Reference Format:

Each comment references a specific requirement from SQLite's documentation:

• Format: # EVIDENCE-OF: R-XXXXX-XXXXX <description>
• The R-number is a unique identifier for that requirement

Evidence Coverage Map:

Sources: test/evidence/slt_lang_aggfunc.test:1-500

Index Maintenance Tests

Relevant source files

• test/evidence/slt_lang_reindex.test

Purpose and Scope

This document covers the test suite for index maintenance operations in SQLite, specifically focusing on the REINDEX command. These tests validate the functionality of rebuilding database indices from scratch, including error handling and cross-database compatibility considerations.

For general query operator tests, see Query Operator Tests. For comprehensive index optimization tests including BETWEEN operators, see Index and Optimization Tests.

Overview

The index maintenance test suite validates the REINDEX SQL command, which is a SQLite-specific extension not included in the SQL-92 standard. The primary test file test/evidence/slt_lang_reindex.test:1-59 contains evidence points that verify the command's specification and behavior.

The REINDEX command serves a single purpose: to delete and recreate indices from scratch. This functionality is critical for:

• Repairing potentially corrupted indices
• Updating indices after collation sequence changes
• Optimizing index structure after bulk data operations

Sources: test/evidence/slt_lang_reindex.test:1-59

Test Environment Setup

The test file establishes a minimal testing environment to validate REINDEX behavior:

Test Setup Sequence:

graph TB
    subgraph "Test Database Schema"
        T1["Table: t1\nColumns: x INTEGER, y VARCHAR(8)"]
I1["Index: t1i1\nON t1(x)"]
end
    
    subgraph "Test Data"
        R1["Row 1: (1, 'true')"]
R2["Row 2: (0, 'false')"]
R3["Row 3: (NULL, 'NULL')"]
end
    
 
   T1 -->|indexed by| I1
 
   T1 -->|contains| R1
 
   T1 -->|contains| R2
 
   T1 -->|contains| R3

Sources: test/evidence/slt_lang_reindex.test:3-16

Cross-Database Compatibility

Platform Support Matrix

The REINDEX command is not universally supported across database platforms. The test file uses conditional execution directives to handle platform-specific behavior:

Conditional Execution Flow

Halt Directives:

• test/evidence/slt_lang_reindex.test:22-24 - Halts execution for MSSQL
• test/evidence/slt_lang_reindex.test:26-28 - Halts execution for Oracle
• test/evidence/slt_lang_reindex.test:30-32 - Halts execution for MySQL

Sources: test/evidence/slt_lang_reindex.test:18-32

graph LR
    CMD["REINDEX t1i1"]
IDX["Index: t1i1"]
DELETE["Delete Index"]
RECREATE["Recreate Index"]
RESULT["statement ok"]
CMD --> IDX
 
   IDX --> DELETE
 
   DELETE --> RECREATE
 
   RECREATE --> RESULT

Command Syntax Validation

The test suite validates both successful and error cases for the REINDEX command:

Valid REINDEX Command

The test at test/evidence/slt_lang_reindex.test:37-38 executes REINDEX t1i1 and expects successful completion (statement ok). This validates that an existing index can be rebuilt.

Error Handling

The test at test/evidence/slt_lang_reindex.test:40-41 validates error handling by attempting to reindex a non-existent index tXiX. The expected behavior is statement error, confirming that the database properly rejects invalid index names.

Sources: test/evidence/slt_lang_reindex.test:37-41

Evidence Points Coverage

The test file documents specific evidence points from SQLite's specification, though some remain unimplemented (marked as TBD):

graph TB
    EV1["R-52173-44778:\nREINDEX deletes and\nrecreates indices"]
T1["Test: REINDEX t1i1\n(line 38)"]
T2["Test: REINDEX tXiX\n(line 40-41)"]
EV1 --> T1
 
   EV1 --> T2
    
 
   T1 --> V1["Validates successful\nindex rebuild"]
T2 --> V2["Validates error handling\nfor invalid index"]

Implemented Evidence

Pending Test Coverage

Four evidence points are marked as "TBD" (To Be Determined), indicating planned but not yet implemented test cases:

1. Collation-based reindexing - Testing REINDEX with a collation sequence name to rebuild all indices using that collation
2. Table-based reindexing - Testing REINDEX with a table name to rebuild all indices on that table
3. Specific index reindexing - Already partially covered by existing tests, but may need additional validation
4. Name resolution - Testing disambiguation when a name matches both a collation sequence and a table/index

Sources: test/evidence/slt_lang_reindex.test:34-57

Test File Metadata

The test file begins with a hash-threshold configuration:

hash-threshold 8

This directive at test/evidence/slt_lang_reindex.test1 sets the hash threshold parameter for the test execution environment. This controls when the query engine switches between different algorithms for query processing, though the specific implications are runtime-dependent.

Sources: test/evidence/slt_lang_reindex.test1

graph TB
    subgraph "SQL Language Evidence Tests (3.1)"
        DDL["DDL Tests (3.1.1)"]
DML["DML Tests (3.1.4)"]
QUERY["Query Tests (3.1.5)"]
REINDEX["Index Maintenance (3.1.8)"]
end
    
    subgraph "REINDEX Test Components"
        SETUP["Table and Index Creation\n(DDL operations)"]
DATA["Data Insertion\n(DML operations)"]
MAINT["Index Rebuilding\n(REINDEX operation)"]
end
    
 
   DDL -.->|uses| SETUP
 
   DML -.->|uses| DATA
 
   REINDEX --> SETUP
 
   REINDEX --> DATA
 
   REINDEX --> MAINT

Integration with Test Corpus

This test file is part of the broader evidence test suite that validates SQL language compliance:

The REINDEX tests leverage DDL operations (table and index creation) and DML operations (data insertion) to establish a testable environment, then validate the index maintenance functionality.

Sources: test/evidence/slt_lang_reindex.test:1-59

Summary

The index maintenance test suite provides focused validation of the REINDEX command with the following characteristics:

The test suite demonstrates the corpus's approach to handling non-standard SQL extensions: documenting platform compatibility constraints using conditional directives, validating core functionality where supported, and marking future test coverage areas for comprehensive specification validation.

For information on how conditional execution directives work across the entire corpus, see Conditional Execution Directives. For platform-specific behavior documentation, see Platform-Specific Behaviors.

Sources: test/evidence/slt_lang_reindex.test:1-59

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

BETWEEN Operator Test Suites

Relevant source files

• test/index/between/1/slt_good_0.test
• test/index/between/10/slt_good_0.test
• test/index/between/10/slt_good_1.test
• test/index/between/10/slt_good_2.test
• test/index/between/10/slt_good_3.test
• test/index/between/10/slt_good_4.test
• test/index/between/10/slt_good_5.test
• test/index/between/100/slt_good_0.test

Purpose and Scope

This section documents the comprehensive test suites for the SQL BETWEEN operator, focusing on query optimization behavior with various index configurations. These tests validate that the BETWEEN operator produces identical results to its equivalent >= AND <= expression across different data types, index strategies, and table sizes.

For general information about index and optimization tests, see Index and Optimization Tests. For SQL language evidence tests, see SQL Language Evidence Tests.

Test Suite Organization

The BETWEEN operator test suites are organized by data volume, with each category testing identical query patterns against varying dataset sizes:

Sources: test/index/between/1/slt_good_0.test:1-4 test/index/between/10/slt_good_0.test:1-4

Directory Structure

Sources: test/index/between/1/slt_good_0.test1 test/index/between/10/slt_good_0.test1

Table Schema and Index Configurations

Each test file creates five tables (tab0 through tab4) with identical schemas but different index configurations to validate query optimizer behavior across various index strategies.

Common Table Schema

All tables share the following structure:

Sources: test/index/between/1/slt_good_0.test:3-4 test/index/between/10/slt_good_0.test:3-4

Index Configuration Matrix

Sources: test/index/between/1/slt_good_0.test:13-23 test/index/between/10/slt_good_0.test:39-50

Index Configuration Examples

The following table shows representative index configurations from test/index/between/10/slt_good_0.test:

Sources: test/index/between/10/slt_good_0.test:39-98

Test Methodology

Equivalence Validation Pattern

Each test validates that BETWEEN and its equivalent >= AND <= expression produce identical results. This is critical for ensuring query optimizer correctness.

Sources: test/index/between/1/slt_good_0.test:69-84 test/index/between/10/slt_good_0.test:102-150

sequenceDiagram
    participant Test as Test Harness
    participant DB as Database Engine
    
    Note over Test: For each table (tab0-tab4)
    
    Test->>DB: Execute query with BETWEEN
    DB-->>Test: Result set A (with hash)
    
    Test->>DB: Execute query with >= AND <=
    DB-->>Test: Result set B (with hash)
    
    Test->>Test: Compare Result A == Result B
    
    alt Results match
        Test->>Test: Test passes
    else Results differ
        Test->>Test: Test fails
    end

Query Pair Structure

Every BETWEEN test consists of paired queries using labels for result comparison:

Key components:

• query I: Expects integer result type
• rowsort: Results are order-independent (sorted for comparison)
• label-0: Shared label ensures both queries produce identical results
• Both queries executed on all 5 tables for cross-index validation

Sources: test/index/between/1/slt_good_0.test:69-76

BETWEEN Operator Test Scenarios

1. Simple Range Queries

Basic BETWEEN operations on indexed and non-indexed columns:

Sources: test/index/between/10/slt_good_0.test:193-270

2. Inverted Range Edge Cases

Tests where the lower bound exceeds the upper bound (should return empty result set):

Sources: test/index/between/1/slt_good_0.test:209-247

3. BETWEEN in Complex Predicates

BETWEEN combined with multiple logical operators:

Sources: test/index/between/1/slt_good_0.test:69-107 test/index/between/1/slt_good_0.test:109-157

4. BETWEEN with NULL Handling

Tests involving NULL values and BETWEEN operations:

Sources: test/index/between/1/slt_good_0.test:159-207

5. Nested BETWEEN in Subqueries

BETWEEN within IN clauses and subquery predicates:

Sources: test/index/between/10/slt_good_0.test:152-190

6. Multi-Column BETWEEN Conditions

Multiple BETWEEN clauses on different columns:

Sources: test/index/between/10/slt_good_0.test:513-511

Data Type Coverage

Integer Columns (col0, col3)

Tests cover:

• Positive integers
• Range boundaries (inclusive)
• Single-value ranges (BETWEEN x AND x)
• Inverted ranges
• NULL comparisons

Sources: test/index/between/10/slt_good_0.test:193-270

Float Columns (col1, col4)

Tests cover:

• Decimal precision
• Scientific notation boundaries
• Fractional range queries
• Mixed precision comparisons

Sources: test/index/between/10/slt_good_0.test:272-350

Cross-Index Validation Strategy

Purpose: Validates that the query optimizer produces correct results regardless of available indexes, ensuring BETWEEN operations are handled consistently across different access paths.

Sources: test/index/between/1/slt_good_0.test:69-107

Test Label Organization

Tests use numeric labels to group equivalent queries:

Each label group contains:

1. Query with BETWEEN operator
2. Query with >= AND <= equivalent
3. Both executed on all 5 tables (10 total queries per label)

Sources: test/index/between/1/slt_good_0.test69 test/index/between/1/slt_good_0.test109

Hash Threshold Configuration

All test files begin with:

hash-threshold 8

This directive instructs the test harness to:

• Hash result sets with more than 8 rows for comparison
• Display individual row values for result sets ≤ 8 rows
• Enable efficient comparison of large result sets

Sources: test/index/between/1/slt_good_0.test1 test/index/between/10/slt_good_0.test1

Data Population Strategy

Single Row Tests (between/1/)

Each table contains exactly one row with identical data:

All tables (tab0 through tab4) populated via:

Purpose: Isolates BETWEEN operator logic from result set size variations, focusing on edge case behavior.

Sources: test/index/between/1/slt_good_0.test:6-7 test/index/between/1/slt_good_0.test25

Ten Row Tests (between/10/)

Each test file has unique data but follows the same pattern:

Tables tab1 through tab4 populated identically from tab0.

Purpose: Tests query optimization with realistic data volumes and distribution.

Sources: test/index/between/10/slt_good_0.test:6-34 test/index/between/10/slt_good_0.test52

Result Comparison Methodology

Order-Independent Comparison

All queries use rowsort to ensure deterministic comparison:

query I rowsort label-N

• Results are sorted before comparison
• Hash values computed for large result sets
• Label matching ensures BETWEEN and equivalent queries match

Expected Result Format

Both queries must produce:

• Identical row values
• Identical row count
• Identical hash (if > 8 rows)

Sources: test/index/between/1/slt_good_0.test:69-76

Test Coverage Summary

By Data Volume

By Index Type

By Operator Complexity

Sources: All test files in test/index/between/

Index and Optimization Tests

Relevant source files

• test/index/between/1/slt_good_0.test
• test/index/between/10/slt_good_0.test
• test/index/between/10/slt_good_1.test
• test/index/between/10/slt_good_2.test
• test/index/between/10/slt_good_3.test
• test/index/between/10/slt_good_4.test
• test/index/between/10/slt_good_5.test
• test/index/between/100/slt_good_0.test

Purpose and Scope

Index and Optimization Tests validate that SQLite's query optimizer correctly utilizes indexes when executing queries, particularly those involving the BETWEEN operator and complex WHERE clauses. These tests ensure that:

1. Queries produce identical results regardless of index configuration
2. The BETWEEN operator is semantically equivalent to its expanded form (col >= lower AND col <= upper)
3. Query optimization works correctly across varying data volumes and complexity levels

For SQL language specification compliance tests covering DDL, DML, and DQL commands, see SQL Language Evidence Tests. For detailed coverage of BETWEEN operator test variations, see BETWEEN Operator Test Suites.

Sources: test/index/between/1/slt_good_0.test:1-108 test/index/between/10/slt_good_0.test:1-150

Test Organization Structure

Index tests are organized hierarchically by data volume, enabling validation across different dataset sizes that may trigger different optimization strategies.

Test Volume Characteristics:

graph TD
    ROOT["test/index/"]
BETWEEN["between/"]
SET1["1/"]
SET10["10/"]
SET100["100/"]
FILE1_0["slt_good_0.test\n1 data row per table"]
FILE10_0["slt_good_0.test\n10 rows"]
FILE10_1["slt_good_1.test\n10 rows"]
FILE10_2["slt_good_2.test\n10 rows"]
FILE10_3["slt_good_3.test\n10 rows"]
FILE10_4["slt_good_4.test\n10 rows"]
FILE10_5["slt_good_5.test\n10 rows"]
FILE100_0["slt_good_0.test\n100+ rows"]
ROOT --> BETWEEN
 
   BETWEEN --> SET1
 
   BETWEEN --> SET10
 
   BETWEEN --> SET100
    
 
   SET1 --> FILE1_0
 
   SET10 --> FILE10_0
 
   SET10 --> FILE10_1
 
   SET10 --> FILE10_2
 
   SET10 --> FILE10_3
 
   SET10 --> FILE10_4
 
   SET10 --> FILE10_5
 
   SET100 --> FILE100_0

Sources: test/index/between/1/slt_good_0.test:1-10 test/index/between/10/slt_good_0.test:1-100

Table Schema and Index Configurations

Each test file creates five tables with identical schema but different index configurations. This design validates that query results remain consistent regardless of which indexes the optimizer chooses to use.

Standard Table Schema

All tables use this schema:

Index Configuration Matrix

Index Configuration Patterns:

Sources: test/index/between/1/slt_good_0.test:3-67 test/index/between/10/slt_good_0.test:4-100

Test Methodology

Query Equivalence Validation

Each test validates that BETWEEN produces identical results to its expanded form. The test pattern repeats for each table configuration:

sequenceDiagram
    participant TF as Test File
    participant DB as Database
    participant V as Validator
    
    Note over TF,V: For each table (tab0..tab4)
    
    TF->>DB: Execute query with BETWEEN
    DB->>V: Result Set A
    
    TF->>DB: Execute equivalent query with >= AND <=
    DB->>V: Result Set B
    
    V->>V: Compare Result Set A vs B
    
    alt Results Match
        V->>TF: PASS
    else Results Differ
        V->>TF: FAIL (optimizer bug)
    end

Label-Based Test Groups

Tests use labels to group related queries:

query I rowsort label-0
SELECT pk FROM tab0 WHERE col3 < 6 OR col3 < 1 AND col3 BETWEEN 2 AND 7
----

query I rowsort label-0
SELECT pk FROM tab0 WHERE col3 < 6 OR col3 < 1 AND (col3 >= 2 AND col3 <= 7)
----

Each label-N represents a specific query pattern tested across all five table configurations, with both BETWEEN and expanded syntax.

Sources: test/index/between/1/slt_good_0.test:69-107 test/index/between/10/slt_good_0.test:102-150

Query Pattern Categories

Simple Range Queries

Basic BETWEEN operations on indexed columns:

Sources: test/index/between/1/slt_good_0.test:69-84

Complex Compound Predicates

Multi-condition queries combining BETWEEN with other operators:

Sources: test/index/between/1/slt_good_0.test:109-157

Nested Subquery Integration

BETWEEN within subquery predicates:

Sources: test/index/between/10/slt_good_0.test:152-190

NULL Handling with BETWEEN

Tests validate BETWEEN behavior with NULL values:

Sources: test/index/between/1/slt_good_0.test:159-207

Query Complexity Spectrum

Tests progressively increase in complexity to stress-test the optimizer:

Complexity Distribution:

Sources: test/index/between/1/slt_good_0.test:69-247

Result Validation Approach

Hash-Based Comparison

Tests use hash-threshold 8 to enable result set hashing for efficient comparison:

hash-threshold 8

query I rowsort label-0
SELECT pk FROM tab0 WHERE col3 > 23 AND ((col0 <= 96)) OR (col3 >= 39)
----
10 values hashing to e20b902b49a98b1a05ed62804c757f94

The hash value ensures:

1. Result sets from different tables match exactly
2. BETWEEN and expanded syntax produce identical results
3. Order-independent comparison via rowsort

Sources: test/index/between/10/slt_good_0.test:1-110

Cross-Table Consistency Validation

Sources: test/index/between/1/slt_good_0.test:69-107

Data Type Coverage

Tests validate BETWEEN across all supported data types:

Inverted Range Testing:

Many tests intentionally use inverted ranges (e.g., BETWEEN 7 AND 4 where 7 > 4) to validate that:

1. Empty result sets are handled correctly
2. Optimizer doesn't incorrectly rewrite the predicate
3. Semantic equivalence is maintained

Sources: test/index/between/1/slt_good_0.test:109-157 test/index/between/10/slt_good_0.test:272-350

Optimizer Coverage Areas

Index Selection Validation

Tests verify the optimizer correctly chooses indexes for BETWEEN predicates:

Sources: test/index/between/10/slt_good_2.test:199-247

Descending Index Utilization

Tests validate BETWEEN works with DESC indexes:

Sources: test/index/between/10/slt_good_0.test:58-70

Composite Index Prefix Usage

Validates optimizer can use compound index prefixes for BETWEEN:

Sources: test/index/between/10/slt_good_0.test:58-67

Test Execution Flow

Test File Structure:

1. Setup Phase:

   • hash-threshold 8 directive
   • CREATE TABLE statements for tab0-tab4
   • INSERT INTO statements (1, 10, or 100+ rows)
   • CREATE INDEX statements per table

2. Test Phase:

   • Grouped by label-N identifiers
   • Two queries per table per label (BETWEEN vs expanded)
   • Expected results follow ---- separator

3. Validation:

   • Hash-based result comparison
   • Automatic failure on mismatch

Sources: test/index/between/1/slt_good_0.test:1-108 test/index/between/10/slt_good_0.test:1-150

Special Test Cases

Empty Result Sets

Tests validate correct handling of unsatisfiable conditions:

Sources: test/index/between/1/slt_good_0.test:159-167

Extreme Predicate Nesting

Label-50 and higher test deeply nested boolean expressions:

These tests ensure the optimizer handles complex expressions without incorrectly simplifying or rewriting predicates.

Sources: test/index/between/1/slt_good_0.test:289-337

Multiple BETWEEN Clauses

Tests validate queries with multiple BETWEEN predicates:

Sources: test/index/between/1/slt_good_0.test:289-296

Cross-Database Compatibility

Relevant source files

• test/evidence/in1.test
• test/evidence/in2.test
• test/evidence/slt_lang_dropindex.test
• test/evidence/slt_lang_reindex.test
• test/evidence/slt_lang_replace.test

Purpose and Scope

This document explains how the sqllogictest corpus supports multiple database platforms through conditional test execution. The test files use directives (skipif, onlyif, halt) to control whether specific tests run on different SQL engines, allowing a single test corpus to validate behavior across SQLite, MySQL, MSSQL, Oracle, and PostgreSQL while accommodating platform-specific SQL implementation differences.

For information about the test file format and directive syntax, see Test File Format Specification. For details on how to integrate the corpus with CI/CD systems for multi-database testing, see Integrating with CI/CD Systems.

Cross-Database Testing Model

The corpus employs a conditional execution strategy where test directives control execution flow based on the target database platform. This allows the same test file to validate SQL behavior across different engines while handling platform-specific limitations and non-standard features.

Supported Database Platforms

The following table summarizes the database platforms supported by the test corpus and their key characteristics:

Sources: test/evidence/in1.test:1-10 test/evidence/slt_lang_reindex.test:18-32 test/evidence/slt_lang_replace.test:1-7

Conditional Execution Architecture

Execution Control Flow

Diagram: Conditional Execution State Machine

The test runner processes directives line-by-line to determine whether subsequent SQL statements should execute. The onlyif directive allows execution only on matching platforms, while skipif prevents execution on matching platforms. The halt directive immediately terminates file processing for the current platform.

Sources: test/evidence/in1.test:4-9 test/evidence/slt_lang_reindex.test:22-32

Platform-Specific Test Isolation Patterns

Pattern 1: Complete File Exclusion

Tests that rely on platform-specific features can exclude entire files using the halt directive immediately after platform detection:

# Skip this entire file if MS SQL Server
onlyif mssql
halt

# Skip this entire file if Oracle
onlyif oracle
halt

This pattern appears in tests for SQLite-specific features like the REINDEX command and REPLACE syntax.

Sources: test/evidence/slt_lang_reindex.test:22-28 test/evidence/slt_lang_replace.test:1-7 test/evidence/in1.test:3-9

Pattern 2: Selective Statement Skipping

For tests that are mostly portable but have specific incompatibilities, directives precede individual statements:

# Only run on SQLite (empty RHS not standard SQL)
onlyif sqlite
query I nosort
SELECT 1 IN ()
----
0

This allows a single test file to run on multiple platforms while skipping non-portable statements.

Sources: test/evidence/in1.test:22-26 test/evidence/in2.test:78-84

Pattern 3: Platform-Specific Alternatives

Some tests provide alternative implementations for different platforms:

skipif mysql
statement ok
CREATE TABLE t7(a TEXT UNIQUE)

onlyif mysql
statement ok
CREATE TABLE t7(a TEXT, UNIQUE (a(1)))

MySQL requires a key length specification for TEXT columns in unique indexes, while other databases do not.

Sources: test/evidence/in1.test:398-405 test/evidence/in1.test:421-427

Platform-Specific Behaviors

SQLite-Specific Extensions

SQLite implements several non-standard SQL features that are explicitly excluded from other platforms:

Empty Right-Hand Side in IN Operator

SQLite allows empty parentheses in IN/NOT IN expressions, which is non-standard:

These tests are marked onlyif sqlite # empty RHS and skipped on MySQL, MSSQL, and Oracle.

Sources: test/evidence/in1.test:16-20 test/evidence/in1.test:22-64 test/evidence/in2.test:7-11 test/evidence/in2.test:78-144

Table-as-Operand Syntax

SQLite allows using a table name directly as the right operand of IN:

This SQLite-specific shorthand is marked onlyif sqlite throughout the test corpus.

Sources: test/evidence/in1.test:69-74 test/evidence/in1.test:80-90

REINDEX Command

SQLite and PostgreSQL support the REINDEX command for rebuilding indexes, while MySQL uses REPAIR TABLE and MSSQL/Oracle have no direct equivalent:

# skip this entire file if ms sql server
onlyif mssql
halt

# skip this entire file if oracle
onlyif oracle
halt

# skip this entire file if mysql
onlyif mysql
halt

Sources: test/evidence/slt_lang_reindex.test:18-32

REPLACE Statement

SQLite's REPLACE INTO syntax (alias for INSERT OR REPLACE) is not supported by MSSQL and Oracle:

# skip this file if ms sql server
onlyif mssql
halt

# skip this file if oracle
onlyif oracle
halt

Sources: test/evidence/slt_lang_replace.test:1-7

MySQL-Specific Limitations and Workarounds

NULL Handling in Subqueries

MySQL has known issues with NULL handling in certain IN subquery contexts:

# mysql is failing this one
skipif mysql
query I nosort label-55
SELECT null IN (SELECT * FROM t4)
----
NULL

These incompatibilities are documented inline and the tests are skipped for MySQL.

Sources: test/evidence/in1.test:951-956 test/evidence/in1.test:970-975

UNIQUE Index Key Length Requirement

MySQL requires explicit key length specifications for TEXT columns in UNIQUE indexes:

skipif mysql
statement ok
CREATE TABLE t7(a TEXT UNIQUE)

onlyif mysql
statement ok
CREATE TABLE t7(a TEXT, UNIQUE (a(1)))

Sources: test/evidence/in1.test:398-405

INSERT OR REPLACE Syntax

MySQL does not support SQLite's INSERT OR REPLACE syntax:

skipif mysql
statement ok
INSERT OR REPLACE INTO t1 VALUES(2, 'insert or replace')

Sources: test/evidence/slt_lang_replace.test:37-46

MSSQL-Specific Differences

DROP INDEX Syntax

MSSQL uses a different syntax for DROP INDEX that requires the table name:

skipif mssql
statement ok
DROP INDEX t1i1;

The entire DROP INDEX test file skips MSSQL-specific statements.

Sources: test/evidence/slt_lang_dropindex.test:22-34

Empty RHS in IN Operator

MSSQL does not support empty parentheses in IN expressions:

skipif mysql # empty RHS
skipif mssql # empty RHS
skipif oracle # empty RHS
query I nosort
SELECT 1 FROM t1 WHERE 1 IN ()

Sources: test/evidence/in2.test:78-84

Oracle-Specific Considerations

VARCHAR vs TEXT Data Type

Oracle uses VARCHAR instead of TEXT for character data:

skipif oracle
statement ok
CREATE TABLE t1( x INTEGER, y TEXT )

onlyif oracle
statement ok
CREATE TABLE t1( x INTEGER, y VARCHAR(8) )

Sources: test/evidence/in2.test:23-29

REINDEX and REPLACE Not Supported

Oracle lacks support for both REINDEX and REPLACE commands, resulting in complete file exclusion for these test suites.

Sources: test/evidence/slt_lang_reindex.test:26-28 test/evidence/slt_lang_replace.test:5-7

graph TB
    subgraph Tests["Test Categories"]
CoreSQL["Core SQL\n(SELECT, INSERT, UPDATE)"]
DDL["DDL Commands\n(CREATE, DROP)"]
INNULL["IN/NOT IN with NULL"]
EmptyRHS["Empty RHS in IN"]
TableAsOp["Table-as-operand"]
REINDEX_T["REINDEX Command"]
REPLACE_T["REPLACE Statement"]
end
    
    subgraph Platforms["Database Platforms"]
SQLite["SQLite"]
MySQL["MySQL"]
PostgreSQL["PostgreSQL"]
MSSQL["MSSQL"]
Oracle["Oracle"]
end
    
 
   CoreSQL -->|Full support| SQLite
 
   CoreSQL -->|Full support| MySQL
 
   CoreSQL -->|Full support| PostgreSQL
 
   CoreSQL -->|Full support| MSSQL
 
   CoreSQL -->|Full support| Oracle
    
 
   DDL -->|Full support| SQLite
 
   DDL -->|Full support| MySQL
 
   DDL -->|Full support| PostgreSQL
 
   DDL -->|Syntax differences| MSSQL
 
   DDL -->|Type differences| Oracle
    
 
   INNULL -->|Full support| SQLite
 
   INNULL -->|Partial subquery issues| MySQL
 
   INNULL -->|Full support| PostgreSQL
 
   INNULL -->|Full support| MSSQL
 
   INNULL -->|Full support| Oracle
    
 
   EmptyRHS -->|Supported non-standard| SQLite
 
   EmptyRHS -->|Not supported| MySQL
 
   EmptyRHS -->|Not supported| PostgreSQL
 
   EmptyRHS -->|Not supported| MSSQL
 
   EmptyRHS -->|Not supported| Oracle
    
 
   TableAsOp -->|Supported SQLite-specific| SQLite
 
   TableAsOp -->|Not supported| MySQL
 
   TableAsOp -->|Not supported| PostgreSQL
 
   TableAsOp -->|Not supported| MSSQL
 
   TableAsOp -->|Not supported| Oracle
    
 
   REINDEX_T -->|Full support| SQLite
 
   REINDEX_T -->|REPAIR TABLE instead| MySQL
 
   REINDEX_T -->|Full support| PostgreSQL
 
   REINDEX_T -->|Not supported| MSSQL
 
   REINDEX_T -->|Not supported| Oracle
    
 
   REPLACE_T -->|Full support| SQLite
 
   REPLACE_T -->|Limited support| MySQL
 
   REPLACE_T -->|Not supported| PostgreSQL
 
   REPLACE_T -->|Not supported| MSSQL
 
   REPLACE_T -->|Not supported| Oracle
    
    style SQLite fill:#f0f0f0
    style MySQL fill:#f0f0f0
    style PostgreSQL fill:#f0f0f0
    style MSSQL fill:#f0f0f0
    style Oracle fill:#f0f0f0

Multi-Platform Testing Strategy Matrix

The following diagram illustrates how different test categories map to platform support:

Diagram: Platform Support Matrix for Test Categories

Sources: test/evidence/in1.test:1-1156 test/evidence/slt_lang_reindex.test:18-32 test/evidence/slt_lang_replace.test:1-76

Directive Usage Patterns in Test Files

Common Directive Combinations

The test corpus uses specific directive patterns to handle different compatibility scenarios:

Sources: test/evidence/in1.test:1-1156 test/evidence/slt_lang_reindex.test:1-59 test/evidence/slt_lang_replace.test:1-76

Inline Documentation Comments

Test files include inline comments explaining platform-specific behaviors:

# EVIDENCE-OF: R-64309-54027 Note that SQLite allows the parenthesized
# list of scalar values on the right-hand side of an IN or NOT IN
# operator to be an empty list but most other SQL database engines and
# the SQL92 standard require the list to contain at least one element.

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

The # empty RHS suffix provides context for why the directive is needed.

Sources: test/evidence/in1.test:16-26 test/evidence/in2.test:7-11

graph LR
    subgraph TestFiles["Test Files"]
in1["in1.test\n(IN operator edge cases)"]
in2["in2.test\n(IN operator validation)"]
dropindex["slt_lang_dropindex.test\n(DROP INDEX)"]
reindex["slt_lang_reindex.test\n(REINDEX)"]
replace["slt_lang_replace.test\n(REPLACE)"]
end
    
    subgraph SQLiteExec["SQLite: Full Execution"]
S1["All tests run"]
S2["Including non-standard features"]
end
    
    subgraph MySQLExec["MySQL: Partial Execution"]
M1["Skip empty RHS tests"]
M2["Skip table-as-operand tests"]
M3["Skip REINDEX tests"]
M4["Skip INSERT OR REPLACE"]
M5["NULL subquery issues"]
end
    
    subgraph MSSQLExec["MSSQL: Partial Execution"]
MS1["File halted: in1.test"]
MS2["File halted: reindex.test"]
MS3["File halted: replace.test"]
MS4["Modified DROP INDEX syntax"]
end
    
    subgraph OracleExec["Oracle: Partial Execution"]
O1["File halted: in1.test"]
O2["File halted: reindex.test"]
O3["File halted: replace.test"]
O4["VARCHAR vs TEXT substitution"]
end
    
 
   in1 -->|Full| SQLiteExec
 
   in2 -->|Full| SQLiteExec
 
   dropindex -->|Full| SQLiteExec
 
   reindex -->|Full| SQLiteExec
 
   replace -->|Full| SQLiteExec
    
 
   in1 -->|Partial| MySQLExec
 
   in2 -->|Partial| MySQLExec
 
   reindex -.->|Halted| MySQLExec
 
   replace -->|Partial| MySQLExec
    
 
   in1 -.->|Halted| MSSQLExec
 
   dropindex -->|Skipped statements| MSSQLExec
 
   reindex -.->|Halted| MSSQLExec
 
   replace -.->|Halted| MSSQLExec
    
 
   in1 -.->|Halted| OracleExec
 
   in2 -->|With type changes| OracleExec
 
   reindex -.->|Halted| OracleExec
 
   replace -.->|Halted| OracleExec

Test Execution Compatibility Map

The following diagram maps specific test files to their platform compatibility:

Diagram: Test File Execution by Platform

Sources: test/evidence/in1.test:3-9 test/evidence/slt_lang_reindex.test:22-32 test/evidence/slt_lang_replace.test:1-7 test/evidence/slt_lang_dropindex.test:22-28

Best Practices for Cross-Platform Test Design

When adding or modifying tests in the corpus, follow these patterns:

1. Assume SQLite as the Primary Target : All tests should run cleanly on SQLite without directives unless testing non-standard features.

2. Document Non-Standard Features : Use inline comments to explain why platform-specific directives are necessary, citing relevant SQL standard documentation when applicable.

3. Preferskipif Over onlyif: When excluding a single platform, use skipif <platform> rather than listing all other platforms with onlyif. This makes tests more maintainable as new platforms are added.

4. Usehalt for Wholesale Incompatibility: If most of a test file relies on platform-specific features, use the halt pattern at the file beginning rather than marking individual statements.

5. Provide Alternative Implementations When Possible : For syntax differences (like MySQL's UNIQUE index requirements), provide platform-specific alternatives rather than skipping functionality entirely.

6. Label Related Test Groups : Use the label-N syntax to group related tests that have different implementations across platforms but validate the same behavior.

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

Summary

The sqllogictest corpus achieves cross-database compatibility through a directive-based conditional execution model. SQLite serves as the primary test target with full feature coverage, while MySQL, PostgreSQL, MSSQL, and Oracle have varying levels of support based on their SQL implementation conformance. The skipif, onlyif, and halt directives enable a single test corpus to validate behavior across platforms while gracefully handling platform-specific limitations and non-standard extensions.

For details on how these directives are parsed and executed, see Conditional Execution Directives. For a comprehensive catalog of platform-specific behaviors, see Platform-Specific Behaviors.

Conditional Execution Directives

Relevant source files

• test/evidence/in1.test
• test/evidence/slt_lang_dropindex.test
• test/evidence/slt_lang_reindex.test

Purpose and Scope

This document specifies the conditional execution directives used in SQL Logic Test files to control which statements and queries execute based on the target database platform. These directives enable a single test file to support multiple SQL implementations while accommodating platform-specific differences in SQL syntax, feature support, and semantic behavior.

For information about the specific platform differences that necessitate these directives, see Platform-Specific Behaviors. For details on the complete test file format specification, see Test File Format Specification.

Overview

Conditional execution directives are line-based control statements that determine whether subsequent test operations should execute, be skipped, or halt file processing entirely. These directives enable tests to adapt their behavior based on which database engine is executing them, allowing the corpus to validate both SQLite-specific extensions and standard SQL behavior across different implementations.

The directives operate on a simple boolean matching model: each directive specifies a target database platform and controls the execution of the immediately following statement or query. When multiple directives appear consecutively, they create conditional execution chains that can handle complex platform-specific scenarios.

Sources: test/evidence/in1.test:1-10 test/evidence/slt_lang_reindex.test:18-32

Directive Types

skipif Directive

The skipif directive prevents execution of the next statement or query when the current database platform matches the specified identifier.

Syntax:

skipif <database-identifier>

Behavior: If the test runner is executing against the specified database, the immediately following test directive (statement or query) is skipped and execution continues with the subsequent line. If the database does not match, the directive has no effect and normal execution proceeds.

Example Usage:

skipif mssql
statement ok
DROP INDEX t1i1;

In this example from test/evidence/slt_lang_dropindex.test:22-24 the DROP INDEX statement is skipped when running on Microsoft SQL Server because MSSQL uses different syntax for dropping indexes (DROP INDEX table.index instead of DROP INDEX index).

Common Patterns:

Sources: test/evidence/slt_lang_dropindex.test:22-34 test/evidence/in1.test:398-404 test/evidence/in1.test:421-427

onlyif Directive

The onlyif directive ensures that the next statement or query executes only when the current database platform matches the specified identifier.

Syntax:

onlyif <database-identifier>

Behavior: If the test runner is executing against the specified database, the immediately following test directive proceeds normally. If the database does not match, the following directive is skipped and execution continues.

Example Usage:

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

This example from test/evidence/in1.test:22-26 demonstrates a SQLite-specific feature where the IN operator can accept an empty set on the right-hand side. The comment # empty RHS provides context that this test validates non-standard behavior.

File-Level Halting Pattern:

onlyif mssql
halt

When combined with halt, the onlyif directive creates an early-exit mechanism for entire files. The pattern at test/evidence/slt_lang_reindex.test:23-24 causes the test runner to stop processing the file immediately when running on MSSQL, effectively skipping all tests in that file.

Common Patterns:

Sources: test/evidence/in1.test:4-9 test/evidence/in1.test:22-26 test/evidence/slt_lang_reindex.test:23-32

halt Directive

The halt directive immediately stops processing the current test file when encountered.

Syntax:

halt

Behavior: When executed, this directive causes the test runner to cease reading and processing any remaining lines in the file. Execution then proceeds to the next test file in the suite.

Typical Usage Pattern:

The halt directive is almost always used in combination with onlyif to create platform-specific file exclusions:

# skip this entire file if ms sql server
onlyif mssql
halt

# skip this entire file if oracle
onlyif oracle
halt

This pattern from test/evidence/slt_lang_reindex.test:22-28 demonstrates the idiomatic approach: conditional directives at the beginning of a file determine whether the entire file should be skipped based on database platform compatibility.

Sources: test/evidence/in1.test:4-9 test/evidence/slt_lang_reindex.test:22-32

Database Platform Identifiers

Conditional directives recognize the following database platform identifiers:

The identifier comparison is case-sensitive and must match exactly as shown above.

Sources: test/evidence/in1.test:4-9 test/evidence/slt_lang_reindex.test:23-32 test/evidence/in1.test:398-427

Directive Execution Model

Single-Statement Control Flow

Sources: test/evidence/in1.test:22-65 test/evidence/slt_lang_dropindex.test:22-34

File-Level Halt Pattern

The most common pattern for excluding entire files based on database platform uses consecutive onlyif + halt pairs:

Sources: test/evidence/in1.test:1-10 test/evidence/slt_lang_reindex.test:22-32

Label-Based Test Organization

Conditional directives work in conjunction with the label system to enable platform-specific variations of the same logical test:

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

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

In this pattern from test/evidence/in1.test:69-78 both queries share the same label-1 identifier. The first query uses SQLite's non-standard table-as-operand syntax (1 IN t1), which only executes on SQLite. The second query uses standard SQL subquery syntax and executes on all platforms. Both validate the same logical behavior but accommodate different SQL syntax support.

Label Pattern Analysis:

This pattern appears extensively throughout test/evidence/in1.test:69-549 with each label representing a distinct test case that has both SQLite-specific and portable implementations.

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

Directive Combination Patterns

Multiple Platform Exclusions

Files that are incompatible with multiple platforms use sequential onlyif + halt pairs:

# skip this entire file if ms sql server
onlyif mssql
halt

# skip this entire file if oracle
onlyif oracle
halt

# skip this entire file if mysql
onlyif mysql
halt

This pattern from test/evidence/slt_lang_reindex.test:22-32 ensures the REINDEX command tests only run on SQLite and PostgreSQL, as these are the only major SQL implementations that support the REINDEX statement.

Sources: test/evidence/slt_lang_reindex.test:18-32

Inline Conditional Execution

For individual statement variations, directives appear immediately before the affected test:

skipif mysql
statement ok
CREATE TABLE t7(a TEXT UNIQUE)

onlyif mysql
statement ok
CREATE TABLE t7(a TEXT, UNIQUE (a(1)))

This pattern from test/evidence/in1.test:398-404 handles MySQL's requirement for indexed prefix lengths on TEXT columns. The first statement creates a standard unique constraint for all databases except MySQL. The second statement uses MySQL's indexed prefix syntax UNIQUE (a(1)) only when running on MySQL.

Sources: test/evidence/in1.test:398-427

Platform-Specific Comment Annotations

Directives often include inline comments explaining why the conditional execution is necessary:

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

The comment # empty RHS from test/evidence/in1.test:22-26 documents that this test validates SQLite's non-standard support for empty right-hand side sets in IN expressions. These annotations serve as inline documentation for developers reading the test files.

Sources: test/evidence/in1.test:22-26 test/evidence/in1.test:38-42

Directive Scope and Limitations

Single-Statement Scope

Each skipif or onlyif directive affects exactly one subsequent statement or query directive. Multiple directives cannot be batched under a single conditional:

Valid Pattern:

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

onlyif sqlite
query I nosort
SELECT 1 NOT IN t1
----
1

Invalid Pattern (Not Supported):

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

query I nosort
SELECT 1 NOT IN t1  # This will execute unconditionally
----
1

Each conditional test requires its own directive prefix, as seen in test/evidence/in1.test:69-90 where consecutive onlyif sqlite directives each control a single query.

Sources: test/evidence/in1.test:69-90 test/evidence/in1.test:163-205

No Nested Conditionals

The directive system does not support logical combinations or nested conditions. Each directive evaluates independently:

• No AND logic: skipif mysql AND oracle (not supported)
• No OR logic: onlyif sqlite OR postgresql (not supported)
• No negation: skipif NOT mysql (not supported)

To exclude a test from multiple platforms, use separate directive-statement pairs or the file-level halt pattern.

Sources: test/evidence/slt_lang_reindex.test:22-32

Integration with Test Execution

Test Runner Requirements

To implement conditional execution support, test runners must:

1. Identify the target platform: Maintain a database identifier string matching one of the recognized platform identifiers
2. Parse directives: Recognize skipif, onlyif, and halt keywords at the beginning of lines
3. Evaluate conditions: Compare the directive's database identifier against the runner's platform identifier
4. Control execution flow: Skip or execute the next directive based on the evaluation result
5. Handle halt: Immediately terminate file processing when a halt directive executes

Sources: test/evidence/in1.test:1-10 test/evidence/slt_lang_dropindex.test:22-34

Execution State Machine

Sources: test/evidence/in1.test:1-65 test/evidence/slt_lang_reindex.test:22-38

Usage Statistics

Analysis of the provided test files reveals the frequency and distribution of conditional directives:

in1.test Directive Distribution

Analysis: The heavy use of onlyif sqlite in test/evidence/in1.test reflects this file's focus on validating SQLite's non-standard IN operator extensions, particularly the table-as-operand syntax and empty set support.

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

slt_lang_reindex.test Directive Distribution

Analysis: This file uses exclusively file-level halt directives, as the REINDEX command is fundamentally incompatible with most database systems except SQLite and PostgreSQL.

Sources: test/evidence/slt_lang_reindex.test:18-32

slt_lang_dropindex.test Directive Distribution

Analysis: The consistent use of skipif mssql before DROP INDEX statements reflects MSSQL's requirement to qualify the index name with its table name.

Sources: test/evidence/slt_lang_dropindex.test:22-34

This conditional execution system enables the SQL Logic Test corpus to serve as a comprehensive validation suite across multiple database platforms while maintaining a single, unified test file format. The simplicity of the directive syntax—limited to three keywords and platform-specific matching—ensures that test files remain readable and maintainable while supporting complex cross-platform testing scenarios.

Platform-Specific Behaviors

Relevant source files

• test/evidence/in1.test
• test/evidence/in2.test
• test/evidence/slt_lang_createview.test
• test/evidence/slt_lang_reindex.test

Purpose and Scope

This page catalogs the known SQL implementation differences between SQLite, MySQL, MSSQL, Oracle, and PostgreSQL as revealed by the test corpus. These differences determine when tests must use conditional execution directives (skipif, onlyif, halt) to adapt to platform-specific SQL semantics.

For information about the conditional execution directives themselves, see Conditional Execution Directives. For practical examples of integrating these tests into CI/CD pipelines, see Integrating with CI/CD Systems.

Platform Compatibility Overview

The test corpus supports five major database platforms, each with varying levels of SQL standard compliance and proprietary extensions. The following diagram illustrates the primary compatibility categories:

Diagram: Platform Support Matrix

graph TB
    subgraph "SQLite Features"
        SQLITE_EMPTY["Empty RHS in IN: IN ()"]
SQLITE_TABLE["Table-as-operand: SELECT 1 IN t1"]
SQLITE_REINDEX["REINDEX command"]
SQLITE_TEMP["TEMP VIEW support"]
SQLITE_READONLY["Strict read-only views"]
end
    
    subgraph "MySQL Compatibility"
        MYSQL_NO_EMPTY["Rejects empty IN ()"]
MYSQL_UNIQUE_LEN["UNIQUE requires length on TEXT"]
MYSQL_NO_TABLE["Requires subquery syntax"]
MYSQL_REPAIR["Uses REPAIR TABLE instead"]
end
    
    subgraph "MSSQL Compatibility"
        MSSQL_NO_EMPTY["Rejects empty IN ()"]
MSSQL_VIEW_DML["Allows DELETE/UPDATE on views"]
MSSQL_NO_REINDEX["No REINDEX support"]
end
    
    subgraph "Oracle Compatibility"
        ORACLE_NO_EMPTY["Rejects empty IN ()"]
ORACLE_VARCHAR["VARCHAR vs TEXT differences"]
ORACLE_NO_REINDEX["No REINDEX support"]
end
    
    subgraph "PostgreSQL Compatibility"
        POSTGRES_REINDEX["REINDEX supported"]
POSTGRES_STANDARD["High SQL standard compliance"]
end

Sources: test/evidence/in1.test:1-20 test/evidence/slt_lang_reindex.test:18-32 test/evidence/slt_lang_createview.test:65-86

Empty RHS in IN Operator

SQLite Extension : SQLite allows the right-hand side of IN and NOT IN operators to be an empty list, returning deterministic results regardless of the left operand.