This documentation is part of the "Projects with Books" initiative at zenOSmosis.

The source code for this project is available on GitHub.

BETWEEN Operator Test Suites

Relevant source files

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:

Directory	Dataset Size	Test Files	Purpose
`test/index/between/1/`	1 row per table	`slt_good_0.test`	Minimal data edge cases
`test/index/between/10/`	10 rows per table	`slt_good_0.test` through `slt_good_5.test`	Standard query optimization scenarios
`test/index/between/100/`	100 rows per table	`slt_good_0.test`	Large dataset performance validation

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:

Table	Index Name	Columns	Attributes
`tab0`	(none)	-	Baseline for comparison
`tab1`	`idx_tab1_0`	`col0`	Single column
`tab1`	`idx_tab1_1`	`col1`	Single column
`tab1`	`idx_tab1_3`	`col3`	Single column
`tab1`	`idx_tab1_4`	`col4`	Single column
`tab2`	`idx_tab2_0`	`col1, col0`	Composite
`tab2`	`idx_tab2_1`	`col4`	UNIQUE
`tab2`	`idx_tab2_4`	`col1 DESC, col3 DESC`	Composite, descending
`tab3`	`idx_tab3_0`	`col3`	UNIQUE
`tab3`	`idx_tab3_1`	`col0 DESC`	UNIQUE, descending
`tab3`	`idx_tab3_3`	`col4`	Single column
`tab4`	`idx_tab4_1`	`col3, col1`	UNIQUE, composite
`tab4`	`idx_tab4_2`	`col4`	Single column
`tab4`	`idx_tab4_4`	`col1 DESC, col3`	UNIQUE, composite
`tab4`	`idx_tab4_5`	`col0`	UNIQUE

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:

Label Pattern	Query Count	Purpose
`label-0`	2 per table × 5 tables	First test case in file
`label-10`	2 per table × 5 tables	Second test case in file
`label-20`	2 per table × 5 tables	Third test case in file
...	...	Continues in increments of 10

Each label group contains:

Query with BETWEEN operator
Query with >= AND <= equivalent
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

Dataset Size	Primary Focus	Test Files
1 row	Edge cases, NULL handling, empty results	1 file
10 rows	Query optimization, index selection	6 files
100 rows	Performance, large result sets	1 file

By Index Type

Index Type	Tables	Validation Focus
No indexes	`tab0`	Baseline correctness
Single column	`tab1`	Basic index utilization
Composite	`tab2`, `tab3`	Multi-column access paths
UNIQUE	`tab2`, `tab3`, `tab4`	Constraint interaction
DESC ordering	`tab2`, `tab3`, `tab4`	Sort order optimization

By Operator Complexity

Scenario	Example	Test Count
Simple BETWEEN	`col BETWEEN a AND b`	High
Inverted ranges	`col BETWEEN b AND a` (b > a)	Medium
Complex predicates	`BETWEEN` with AND/OR/NOT	High
Nested expressions	BETWEEN in subqueries	Medium
NULL handling	BETWEEN with IS NULL	Low

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

Keyboard shortcuts

sqlite-sqllogictest-corpus Documentation