Optimizer Replay Data

This directory contains the shared replay data set for optimizer tests. It is the canonical source for the replay-style runners below:

src/query/sql/tests/it/planner.rs runs the lightweight SQL-side replay.
src/query/service/tests/it/sql/planner/optimizer/optimizer_test.rs runs the service-side replay, including physical plans.

This directory does not store every SQL-side golden file. Module-local golden files for lightweight semantic or optimizer tests live next to their tests under src/query/sql/tests/it/<layer>/, for example:

src/query/sql/tests/it/semantic/binder.txt
src/query/sql/tests/it/optimizer/eager_aggregation.txt

The shared replay data is structured as follows:

Directory Structure

data
├── tables/          # SQL table definitions
│   ├── basic/       # Basic table definitions
│   ├── tpcds/       # TPC-DS table definitions
│   └── obfuscated/  # Obfuscated table definitions
├── statistics/      # Statistics files
│   ├── basic/       # Basic statistics
│   ├── tpcds/       # TPC-DS statistics
│   └── obfuscated/  # Obfuscated statistics
├── cases/           # YAML test case definitions
│   ├── basic/       # Basic test cases
│   ├── tpcds/       # TPC-DS test cases
│   └── obfuscated/  # Obfuscated test cases
└── results/         # Test result files (generated)
    ├── basic/       # Results for basic test cases
    ├── tpcds/       # Results for TPC-DS test cases
    └── obfuscated/  # Results for obfuscated test cases

The test framework supports hierarchical subdirectory structures for better organization of test cases, tables, and results.

YAML Test Case Format

Each test case is defined in a YAML file with the following structure:

yaml

name: "Q3"                      # Test case name
description: "Test description" # Optional description

sql: |                          # SQL query to test
  SELECT ...

auto_statistics: false          # Whether to use CollectStatisticsOptimizer (default: false)

statistics_file: "tpcds_100g"   # Optional: reference external statistics file
                                # (from statistics/ directory, extension optional)

table_statistics:               # Inline table statistics (can be combined with statistics_file)
  table_name:
    num_rows: 1000
    data_size: 102400
    data_size_compressed: 51200
    index_size: 20480
    number_of_blocks: 10
    number_of_segments: 2

column_statistics:              # Inline column statistics (can be combined with statistics_file)
  table_name.column_name:
    min: 1990                   # Min value (can be number or string)
    max: 2000                   # Max value (can be number or string)
    ndv: 10                     # Number of distinct values
    null_count: 0               # Number of null values

histogram_statistics:           # Optional inline histograms keyed by table.column
  table_name.column_name:
    accuracy: true              # Whether bucket distinct counts are exact ANALYZE facts
    buckets:
      - lower_bound: !Int 1990  # Min value for the bucket as an explicit Datum
        upper_bound: !Int 2000  # Max value for the bucket as an explicit Datum
        num_values: 1000        # Row count represented by the bucket
        num_distinct: 10        # Distinct value count represented by the bucket

good_plan: |                    # Optional expected good plan
  ...

External Statistics Files

Statistics can be defined in separate YAML files in the statistics/ directory:

yaml

# statistics/tpcds/tpcds_100g.yaml
table_statistics:
  catalog_sales:
    num_rows: 143997065
    data_size: 12959733850
    # ... other stats
    
column_statistics:
  catalog_sales.cs_sold_date_sk:
    min: 2450815
    max: 2452921
    ndv: 1823
    null_count: 0
  # ... other columns

histogram_statistics:
  catalog_sales.cs_sold_date_sk:
    accuracy: true
    buckets:
      - lower_bound: !Int 2450815
        upper_bound: !Int 2452921
        num_values: 143997065
        num_distinct: 1823

Test cases can reference these files using the statistics_file field. The framework will automatically search for matching files (with or without numeric prefixes like 01_tpcds_100g.yaml).

Table Definitions

Table definitions are stored in SQL files in the tables/ directory. Each file contains CREATE TABLE statements.

The lightweight runner parses and registers these SQL statements into its in-memory fixture.
The service runner executes the same SQL statements to set up the test environment. If a table already exists, the error will be ignored.

Running Tests

Run All Service-side Tests

bash

cargo test --package databend-query --test it -- sql::planner::optimizer::optimizer_test::test_optimizer --exact --nocapture

Run All Lightweight Shared Tests

bash

cargo test --package databend-common-sql --test it -- planner::test_lite_replay_service_optimizer_cases --exact --nocapture

Run Tests from Specific Subdirectory

bash

TEST_SUBDIR=tpcds cargo test --package databend-query --test it -- sql::planner::optimizer::optimizer_test::test_optimizer --exact --nocapture

Generated Replay Result Files

Each replay test case generates up to three result files in the corresponding subdirectory under results/:

{test_name}_raw.txt - The raw plan before optimization
{test_name}_optimized.txt - The optimized logical plan
{test_name}_physical.txt - The physical execution plan when the runner supports physical planning

Adding New Replay Tests

To add a new replay test case:

Create a new YAML file in the appropriate subdirectory under cases/ (e.g., basic/, tpcds/, or obfuscated/).
If the test uses new tables, add the table definitions to the corresponding subdirectory under tables/.
If needed, add statistics files to the corresponding subdirectory under statistics/.
The test runner will automatically discover and run all test cases recursively in all subdirectories.
Test results will be saved in a matching subdirectory structure under the main results/ directory.

Updating Existing Replay Tests

If the expected output of a replay test changes:

Run the test with UPDATE_GOLDENFILES environment variable to generate new result files.
The new result files will be automatically saved in the matching subdirectory under results/.
Review the changes to ensure they are as expected.