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:

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
├── statistics_trace/ # StatisticsTrace replay fixtures
│   ├── sql/          # SQL statements replayed from trace JSON
│   └── traces/       # StatisticsTrace JSON payloads
└── 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.

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

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

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

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

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

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

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

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

statistics_trace/sql/ and statistics_trace/traces/ contain paired SQL and JSON inputs for direct StatisticsTrace replay. The service-side trace test generates a fresh JSON payload from CollectStatisticsOptimizer and compares it with the JSON fixture. The SQL-side golden test consumes the same SQL and JSON fixtures through the lightweight replay harness.

To add a new replay test case:

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

If the expected output of a replay test changes:

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