Test File Format Specification

Relevant source files

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

Purpose and Scope

This document provides the technical specification for the SQL Logic Test (SLT) file format used throughout the test corpus. Test files in this repository follow a line-based domain-specific language (DSL) that defines SQL statements, expected results, and conditional execution directives. This format enables cross-database testing and precise validation of SQL behavior.

For information about the test corpus organization and categories, see Test Corpus Reference. For practical usage examples, see Usage Guide.

File Structure Overview

SQL Logic Test files are plain text files with a .test extension organized in a line-based format where each directive occupies one or more consecutive lines. The parser processes files sequentially, interpreting directives and executing SQL statements against the target database.

Sources: test/evidence/in1.test:1-1156 test/evidence/slt_lang_createtrigger.test:1-220 test/index/between/1/slt_good_0.test:1-768

graph TB
 
   FILE["Test File (.test)"] --> LINES["Line-by-Line Processing"]
LINES --> COMMENT["# Comment Lines"]
LINES --> CONFIG["Configuration Directives"]
LINES --> CONTROL["Control Flow Directives"]
LINES --> EXEC["Execution Directives"]
LINES --> BLANK["Blank Lines (ignored)"]
CONFIG --> HASH["hash-threshold N"]
CONTROL --> SKIPIF["skipif <database>"]
CONTROL --> ONLYIF["onlyif <database>"]
CONTROL --> HALT["halt"]
EXEC --> STATEMENT["statement ok/error"]
EXEC --> QUERY["query <type> <mode> [label-N]"]
STATEMENT --> SQL1["SQL Statement"]
QUERY --> SQL2["SQL Query"]
QUERY --> EXPECTED["---- separator"]
QUERY --> RESULTS["Expected Results"]

Comment Syntax

Lines beginning with # are treated as comments and ignored during execution. Comments provide documentation and reference SQLite evidence documentation identifiers.

# This is a comment
# EVIDENCE-OF: R-52275-55503 When the right operand is an empty set...

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

Configuration Directives

hash-threshold

The hash-threshold directive sets the number of result rows after which the test runner should use hash-based comparison instead of direct string matching. This optimizes performance for large result sets.

hash-threshold 8

Syntax:

• hash-threshold <N> where N is an integer

Sources: test/evidence/slt_lang_createtrigger.test1 test/index/between/1/slt_good_0.test1

Control Flow Directives

Control flow directives enable conditional test execution based on the target database platform, allowing a single test file to support multiple SQL engines.

flowchart TD
 
   START["Parse Directive"] --> CHECK_TYPE{"Directive Type?"}
CHECK_TYPE -->|skipif| SKIPIF["skipif <database>"]
CHECK_TYPE -->|onlyif| ONLYIF["onlyif <database>"]
CHECK_TYPE -->|halt| HALT["halt"]
SKIPIF --> MATCH_SKIP{"Current DB\nmatches?"}
ONLYIF --> MATCH_ONLY{"Current DB\nmatches?"}
HALT --> MATCH_HALT{"Current DB\nmatches?"}
MATCH_SKIP -->|Yes| SKIP["Skip following statements\nuntil next directive"]
MATCH_SKIP -->|No| CONTINUE1["Continue execution"]
MATCH_ONLY -->|No| SKIP
 
   MATCH_ONLY -->|Yes| CONTINUE2["Continue execution"]
MATCH_HALT -->|Yes| STOP["Stop file execution"]
MATCH_HALT -->|No| CONTINUE3["Continue execution"]
SKIP --> NEXT["Next directive"]
CONTINUE1 --> NEXT
 
   CONTINUE2 --> NEXT
 
   CONTINUE3 --> NEXT

skipif

The skipif directive causes the test runner to skip the immediately following SQL statement(s) if the current database matches the specified platform.

Syntax:

skipif <database>
<SQL statement or query>

Supported database identifiers:

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

Example:

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

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

onlyif

The onlyif directive causes the test runner to execute the following statement(s) only if the current database matches the specified platform. All other platforms skip the statement.

Syntax:

onlyif <database>
<SQL statement or query>

Example:

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

Sources: test/evidence/in1.test:22-26 test/evidence/slt_lang_createtrigger.test:18-19

halt

The halt directive, when preceded by an onlyif directive, causes test execution to stop entirely for the specified database. This is commonly used to exclude entire test files from certain platforms.

Syntax:

onlyif <database>
halt

Example:

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

Sources: test/evidence/in1.test:3-9 test/evidence/slt_lang_createtrigger.test:18-19

graph LR
 
   STMT["statement"] --> TYPE{"Expected\nOutcome?"}
TYPE -->|ok| OK["statement ok"]
TYPE -->|error| ERROR["statement error"]
OK --> SQL1["SQL Statement\n(must succeed)"]
ERROR --> SQL2["SQL Statement\n(must fail)"]
SQL1 --> EXEC1["Execute Statement"]
SQL2 --> EXEC2["Execute Statement"]
EXEC1 --> CHECK1{"Succeeded?"}
EXEC2 --> CHECK2{"Failed?"}
CHECK1 -->|Yes| PASS1["Test Passes"]
CHECK1 -->|No| FAIL1["Test Fails"]
CHECK2 -->|Yes| PASS2["Test Passes"]
CHECK2 -->|No| FAIL2["Test Fails"]

Statement Execution Directives

Statement directives execute SQL DDL, DML, or other non-query statements and validate their success or failure.

statement ok

The statement ok directive indicates that the following SQL statement must execute successfully without error.

Syntax:

statement ok
<SQL statement>

Example:

statement ok
CREATE TABLE t1(x INTEGER)

Sources: test/evidence/in1.test:66-67 test/evidence/slt_lang_createtrigger.test:3-16

statement error

The statement error directive indicates that the following SQL statement must fail with an error. This validates error handling and constraint enforcement.

Syntax:

statement error
<SQL statement>

Example:

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

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

graph TB
 
   QUERY["query Directive"] --> FORMAT["Query Type\nSpecification"]
FORMAT --> MODE["Comparison Mode"]
MODE --> LABEL["Optional Label"]
FORMAT --> TYPES["Type Codes"]
TYPES --> I["I = Integer"]
TYPES --> II["II = Two Integers"]
TYPES --> III["III = Three Integers"]
TYPES --> T["T = Text"]
TYPES --> R["R = Real (Float)"]
MODE --> NOSORT["nosort\n(order-dependent)"]
MODE --> ROWSORT["rowsort\n(order-independent)"]
LABEL --> MARKER["label-N\n(groups related tests)"]
QUERY --> SEPARATOR["----\n(separates query from results)"]
SEPARATOR --> RESULTS["Expected Results\n(one value per line)"]

Query Execution Directives

Query directives execute SELECT statements and validate their results against expected output.

Query Type Specification

The query type code immediately follows the query keyword and specifies the expected column types in the result set.

Syntax:

query <type-code> <comparison-mode> [label-<N>]
<SQL SELECT statement>
----
<expected results>

Type Codes:

Multiple type codes can be combined to specify multiple columns with different types.

Sources: test/evidence/in1.test:23-26 test/index/between/1/slt_good_0.test:69-107

Comparison Modes

nosort

The nosort mode performs order-dependent comparison. Result rows must match the expected output in exact sequence.

Example:

query I nosort
SELECT 1 IN (2)
----
0

Sources: test/evidence/in1.test:28-31

rowsort

The rowsort mode performs order-independent comparison. Both actual and expected results are sorted before comparison, allowing queries to return rows in any order.

Example:

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

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

Label Markers

Label markers group related test cases that verify the same SQL behavior with different table configurations or query formulations. Tests with the same label should produce identical results.

Syntax:

query <type> <mode> label-<N>

Where <N> is a numeric identifier.

Example - Grouped Tests:

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 label-1 and must return the same result (0), demonstrating that table syntax and (SELECT * FROM table) syntax are equivalent.

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

Expected Output Format

Result Separator

The ---- line separates the SQL query from its expected results. This delimiter is mandatory for all query directives.

Example:

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

Result Values

Expected values appear one per line after the ---- separator. For queries returning multiple columns, values appear in sequence:

Single column:

query I nosort
SELECT 1
----
1

Multiple rows:

query I nosort
SELECT * FROM (VALUES (1), (2), (3))
----
1
2
3

Multiple columns:

query II nosort
SELECT 1, 2
----
1
2

Empty Results

When a query returns no rows, the expected output section remains empty:

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

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

NULL Values

NULL values in result sets are represented as empty lines or the literal text NULL depending on the test configuration:

query I nosort
SELECT null IN ()
----
0

Sources: test/evidence/in1.test:54-64

Test File Processing Flow

Sources: test/evidence/in1.test:1-1156 test/index/between/1/slt_good_0.test:1-768

graph TB
 
   TEST["Test Case"] --> VARIANT{"Database-Specific\nSyntax?"}
VARIANT -->|No| COMMON["Common test\n(no directives)"]
VARIANT -->|Yes| PLATFORM["Platform-specific variants"]
PLATFORM --> SQLITE["onlyif sqlite\nquery I nosort\nSELECT 1 IN ()\n----\n0"]
PLATFORM --> MYSQL["skipif mysql\nstatement ok\nCREATE TABLE t7(a TEXT UNIQUE)"]
PLATFORM --> ALT_MYSQL["onlyif mysql\nstatement ok\nCREATE TABLE t7(a TEXT, UNIQUE (a(1)))"]
COMMON --> ALL["Executed on\nall databases"]
SQLITE --> ONLY_SQLITE["Executed only\non SQLite"]
MYSQL --> NOT_MYSQL["Skipped on MySQL"]
ALT_MYSQL --> ONLY_MYSQL["Executed only\non MySQL"]
style COMMON fill:#e1f5ff
    style SQLITE fill:#fff4e1
    style MYSQL fill:#ffe1e1
    style ALT_MYSQL fill:#ffe1e1

Cross-Database Compatibility Patterns

Test files use combinations of conditional directives to handle platform-specific SQL syntax differences:

Common patterns:

1. Feature availability:

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

2. Syntax variations:

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

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

3. Platform exclusion:

onlyif mssql
halt

Sources: test/evidence/in1.test:3-9 test/evidence/in1.test:22-26 test/evidence/in1.test:398-427

Directive Precedence and Scope

Scope rules:

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

Advanced Query Features

BETWEEN Operator Testing

Test files extensively use BETWEEN expressions and validate their equivalence to range comparisons:

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

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

Both queries share label-0 and must produce identical results, demonstrating BETWEEN semantics.

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

Subquery Equivalence

Tests validate that different query formulations produce identical results:

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

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

The label-1 marker groups these tests, asserting that SQLite's table-as-operand syntax (IN t1) is equivalent to the standard subquery syntax.

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

Complex WHERE Clauses

Test files include deeply nested WHERE clauses to validate parser correctness and operator precedence:

query I rowsort label-50
SELECT pk FROM tab0 WHERE ((((((col0 >= 3 OR ((col4 > 5.3) OR col0 = 1)) 
OR (((((((((((col0 IN (6,8,8) AND (((col3 IS NULL)) OR (col3 >= 7))...

These tests ensure databases correctly parse and execute complex boolean expressions.

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

File Naming Conventions

Test files follow consistent naming patterns:

Sources: test/evidence/slt_lang_createtrigger.test1 test/evidence/in1.test1 test/index/between/1/slt_good_0.test1