Test Directives Reference

Relevant source files

• test/evidence/in1.test
• test/evidence/slt_lang_replace.test
• test/index/between/10/slt_good_1.test

Purpose and Scope

This page provides a complete reference for all test directives used in SQL Logic Test files within the sqlite-sqllogictest-corpus. Test directives are line-based commands that control test execution, define SQL statements and queries, specify expected results, and enable conditional execution across different database platforms.

For information about result comparison modes (nosort, rowsort, valuesort) and expected output format, see Result Comparison Modes. For the broader context of how test files are organized and executed, see Test File Format Specification.

Directive Categories

SQL Logic Test files use a domain-specific language (DSL) with several categories of directives:

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

Conditional Execution Directives

Overview

Conditional execution directives enable a single test file to support multiple database platforms by controlling which statements execute based on the current database engine. These directives are essential for cross-platform compatibility.

Diagram: Conditional Directive Execution Flow

stateDiagram-v2
    [*] --> ParseDirective : Read line
    
    ParseDirective --> SkipIf : skipif <db>
    ParseDirective --> OnlyIf : onlyif <db>
    ParseDirective --> Halt : halt
    ParseDirective --> Execute : Other directive
    
    SkipIf --> CheckDB1 : Check if current DB matches
    OnlyIf --> CheckDB2 : Check if current DB matches
    Halt --> CheckDB3 : Check if previous onlyif matched
    
    CheckDB1 --> SkipNext : Match - Skip next statement(s)
    CheckDB1 --> Execute : No match - Continue
    
    CheckDB2 --> Execute : Match - Continue
    CheckDB2 --> SkipNext : No match - Skip next statement(s)
    
    CheckDB3 --> [*] : Previous onlyif matched - Stop file
    CheckDB3 --> Execute : Previous onlyif didn't match - Continue
    
    SkipNext --> ParseDirective
    Execute --> ParseDirective

Sources: test/evidence/in1.test:4-9 test/evidence/slt_lang_replace.test:2-7

skipif Directive

Syntax: skipif <database>

Purpose: Skip the next statement or query if the current database platform matches the specified database.

Supported Database Identifiers:

• sqlite - SQLite database engine
• mysql - MySQL/MariaDB
• mssql - Microsoft SQL Server
• oracle - Oracle Database
• postgresql - PostgreSQL

Example:

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

In this example, the CREATE TABLE statement is skipped when running on MySQL because MySQL requires a key length specification for TEXT columns in UNIQUE constraints.

Sources: test/evidence/in1.test:398-400 test/evidence/slt_lang_replace.test:37-39

onlyif Directive

Syntax: onlyif <database>

Purpose: Execute the next statement or query only if the current database platform matches the specified database. All other platforms skip the statement.

Example:

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

This query tests SQLite-specific behavior where empty lists are allowed on the right-hand side of IN operators, which is not standard SQL and not supported by most other databases.

Usage Pattern for File-Level Exclusion:

onlyif mssql
halt

onlyif oracle
halt

This pattern at the beginning of a file causes the entire test file to halt execution when running on MSSQL or Oracle, effectively excluding these platforms from the test.

Sources: test/evidence/in1.test:4-9 test/evidence/in1.test:22-26 test/evidence/slt_lang_replace.test:2-7

halt Directive

Syntax: halt

Purpose: Immediately stop execution of the current test file. Typically used in combination with onlyif to exclude entire files from specific database platforms.

Common Pattern:

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

# skip this entire file if oracle
onlyif oracle
halt

This pattern appears at the beginning of test files that contain database-specific features not supported by certain platforms.

Sources: test/evidence/in1.test:4-9 test/evidence/slt_lang_replace.test:1-7

Statement Execution Directives

statement ok

Syntax: statement ok

Purpose: Execute the following SQL statement and expect it to complete successfully without errors. The statement should not return results (typically DDL or DML statements).

Example:

statement ok
CREATE TABLE t1(x INTEGER)

statement ok
INSERT INTO t4 VALUES(2)

statement ok
INSERT INTO t5 SELECT * FROM t4

The directive is followed by one or more lines containing the SQL statement to execute. The statement may span multiple lines until the next directive is encountered.

Sources: test/evidence/in1.test:66-67 test/evidence/in1.test:369-381 test/evidence/slt_lang_replace.test:11-19

statement error

Syntax: statement error [optional-error-pattern]

Purpose: Execute the following SQL statement and expect it to fail with an error. Optionally, verify that the error message matches a specific pattern.

Note: While statement error is part of the SQL Logic Test specification, it does not appear in the provided test file examples. The corpus primarily uses statement ok for validation.

Query Execution Directives

Query Directive Syntax

General Syntax: query <type-spec> <sort-mode> [label-<id>]

Components:

Type Specifications

The type specification defines the number and types of columns expected in the query result:

Example:

query I nosort
SELECT 1 IN (2,3,4)
----
0

query IT rowsort
SELECT x, y FROM t1 WHERE x=2
----
2
insert

Sources: test/evidence/in1.test:23-26 test/evidence/slt_lang_replace.test:24-35

Sort Mode Specifications

Sort modes control how actual results are compared with expected results:

For detailed information about sort modes and result comparison, see Result Comparison Modes.

Sources: test/evidence/in1.test:23-26 test/evidence/slt_lang_replace.test:24-26

Label Markers

Syntax: query <type> <sort-mode> label-<identifier>

Purpose: Group related queries that test the same logical condition across different implementation approaches. Queries with the same label should produce identical results.

Example:

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

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

Both queries are marked with label-1, indicating they test the same logical condition: checking if 1 is in an empty table. The first uses SQLite's table-as-operand syntax, while the second uses standard subquery syntax. Both must produce result 0.

Sources: test/evidence/in1.test:70-78

Test Configuration Directives

hash-threshold

Syntax: hash-threshold <number>

Purpose: Configure the hash threshold for result validation. When the number of result rows exceeds this threshold, the test runner may use hash-based comparison instead of line-by-line comparison for performance.

Example:

hash-threshold 8

This sets the hash threshold to 8 rows. Results with more than 8 rows may be validated using MD5 or other hash comparison.

Sources: test/evidence/slt_lang_replace.test9

Comments

Syntax: # comment text

Purpose: Provide human-readable documentation within test files. Comments are ignored during execution.

Common Uses:

1. Document test purpose or evidence citations
2. Explain platform-specific behavior
3. Provide context for complex test scenarios

Example:

# 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.

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

Sources: test/evidence/in1.test:1-19 test/evidence/slt_lang_replace.test:1-3

flowchart TD
 
   START["Test file line"] --> PARSE["Parse directive type"]
PARSE --> COMMENT["# comment"]
PARSE --> SKIPIF["skipif <db>"]
PARSE --> ONLYIF["onlyif <db>"]
PARSE --> HALT["halt"]
PARSE --> STMT_OK["statement ok"]
PARSE --> QUERY["query <type> <mode>"]
PARSE --> HASH["hash-threshold <n>"]
COMMENT --> NEXT1["Continue to next line"]
HASH --> NEXT2["Set threshold, continue"]
SKIPIF --> CHECK_SKIP{"Current DB\nmatches?"}
ONLYIF --> CHECK_ONLY{"Current DB\nmatches?"}
HALT --> STOP["Stop file execution"]
CHECK_SKIP -->|Yes| SKIP_BLOCK["Skip next SQL block"]
CHECK_SKIP -->|No| NEXT3["Continue to next line"]
CHECK_ONLY -->|Yes| NEXT4["Continue to next line"]
CHECK_ONLY -->|No| SKIP_BLOCK
    
 
   SKIP_BLOCK --> NEXT5["Continue to next line"]
STMT_OK --> READ_SQL1["Read SQL statement"]
QUERY --> READ_SQL2["Read SQL query"]
READ_SQL1 --> EXEC_STMT["Execute statement"]
READ_SQL2 --> EXEC_QUERY["Execute query"]
EXEC_STMT --> VERIFY_OK{"Execution\nsucceeded?"}
EXEC_QUERY --> READ_EXPECTED["Read expected results"]
VERIFY_OK -->|Yes| NEXT6["Continue to next line"]
VERIFY_OK -->|No| FAIL1["Test failure"]
READ_EXPECTED --> COMPARE["Compare actual vs expected"]
COMPARE --> MATCH{"Results\nmatch?"}
MATCH -->|Yes| NEXT7["Continue to next line"]
MATCH -->|No| FAIL2["Test failure"]
NEXT1 --> END["Next directive"]
NEXT2 --> END
 
   NEXT3 --> END
 
   NEXT4 --> END
 
   NEXT5 --> END
 
   NEXT6 --> END
 
   NEXT7 --> END

Directive Execution Model

The following diagram maps test directives to the actual execution behavior in test runners:

Diagram: Test Directive Execution State Machine

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

Platform Compatibility Matrix

The following table summarizes how conditional directives are used across different database platforms:

Sources: test/evidence/in1.test:4-9 test/evidence/in1.test:22-26 test/evidence/in1.test:69-73 test/evidence/slt_lang_replace.test:1-7 test/evidence/slt_lang_replace.test:37-46

Complete Directive Reference Table

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

graph TB
    subgraph "Directive Categories"
        COND["Conditional Directives\nskipif/onlyif/halt"]
STMT["Statement Directives\nstatement ok"]
QUERY["Query Directives\nquery I/IT/etc"]
CONFIG["Configuration\nhash-threshold"]
end
    
    subgraph "Test File Examples"
        IN1["test/evidence/in1.test\nLines 4-9, 22-26, 66-78"]
REPLACE["test/evidence/slt_lang_replace.test\nLines 1-9, 24-55"]
end
    
    subgraph "Platform-Specific Behaviors"
        SQLITE_ONLY["SQLite-only features\nEmpty IN lists\nTable-as-operand"]
MYSQL_SKIP["MySQL exclusions\nTEXT UNIQUE length\nINSERT OR REPLACE"]
MSSQL_HALT["MSSQL/Oracle exclusions\nFile-level halt"]
end
    
 
   COND --> IN1
 
   COND --> REPLACE
 
   STMT --> IN1
 
   STMT --> REPLACE
 
   QUERY --> IN1
 
   QUERY --> REPLACE
 
   CONFIG --> REPLACE
    
 
   IN1 --> SQLITE_ONLY
 
   IN1 --> MYSQL_SKIP
 
   IN1 --> MSSQL_HALT
 
   REPLACE --> MYSQL_SKIP
 
   REPLACE --> MSSQL_HALT

Cross-Reference: Code to Test Directives

The following diagram shows how test directives in the corpus map to actual test files and validation scenarios:

Diagram: Test Directive Usage in Corpus Files

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