Automatic validation of query results against expected row counts
Row count validation is a feature that automatically validates benchmark query execution by comparing actual row counts against expected results from official TPC answer files. This helps ensure:
- Correctness: Queries return the expected number of rows
- Platform Compliance: Database platforms implement TPC specifications correctly
- Regression Detection: Changes to data or queries don't break correctness
- Confidence: Results are trustworthy before performance comparisons
Row count validation is automatically enabled for supported benchmarks (TPC-H, TPC-DS) when using the standard adapters:
from benchbox.platforms.duckdb import DuckDBAdapter
from benchbox import TPCH
adapter = DuckDBAdapter()
benchmark = TPCH(scale_factor=1.0)
results = adapter.run_benchmark(benchmark)
for query_result in results['queries']:
print(f"Query {query_result['query_id']}: {query_result.get('row_count_validation_status', 'N/A')}")Validation is enabled by default, and it runs automatically when the benchmark runs. The loop checks the validation status of each query result.
Each query result includes validation metadata when validation is performed:
{
"query_id": "1",
"status": "SUCCESS",
"execution_time": 0.123,
"rows_returned": 4,
"expected_row_count": 4,
"row_count_validation_status": "PASSED",
}expected_row_count is the expected count from the answer files, and row_count_validation_status is the validation result. A real result contains other fields as well.
Query returned the exact expected number of rows (or within acceptable range for non-deterministic queries).
✅ Query 1: 4 rows (expected: 4) - Validation PASSED
Query returned a different number of rows than expected. This indicates a potential correctness issue.
❌ Query 2: 250 rows (expected: 460) - Validation FAILED
Difference: -210 rows (-45.7%)
Possible causes:
- Incorrect SQL query implementation
- Data loading errors
- Platform-specific behavior differences
- Scale factor mismatch
Validation was skipped because no expected result is available for this query/scale factor combination.
⚠️ Query 3: 150 rows - Validation SKIPPED
Common reasons:
- Scale factor other than 1.0 (expected results only available for SF=1.0 currently)
- Query variant not in answer files
- Answer files are unavailable and the provider reports
FileNotFoundError, including a disabled download or an on-demand download that returned no files- run
benchbox download-answersto pre-populate the cache
- run
- Non-standard benchmark
SKIPPED means no row-count comparison was certified. Query execution can
remain SUCCESS while its row_count_validation block is SKIPPED and carries
a warning. An explicit SKIP expectation, an absent answer set or query lookup,
and a nonzero TPC reference stream use this unevaluated result.
A registered EXACT expectation whose count lookup returns None also becomes
SKIP with an EXACT validation mode but no expected count available warning.
This is the current defensive lookup behavior; it does not establish that the
returned rows are correct. Normal ExpectedQueryResult construction requires
an exact count or a formula for EXACT mode, so a constructor rejection is a
separate error, not this downgrade.
Expected-results failures are classified. A provider FileNotFoundError is
cached as absent answer data. The on-demand downloader currently converts its
failures to no files, which the loader reports as FileNotFoundError; those
failures therefore also become unevaluated skips. Other provider exceptions
produce a failed validation result and may retry on a later request. A waiter
timeout also fails validation while the background load continues. Neither an
invalid SKIP-mode result nor a provider timeout is published as normal SKIPPED
validation: the adapter records a failed query and validation error.
Canonical query-result serialization preserves expected/actual counts and warning/error text. It downgrades PASSED evidence without an expected count to SKIPPED. Consequently, a successfully evaluated RANGE check, whose result has no single expected count, also appears as SKIPPED after this normalization. Inspect the available evidence; execution success alone is not correctness certification.
TPC-DS DataFrame runs use a separate validation boundary because their expected row counts are not seed-aligned. Successful queries receive SKIPPED row-count evidence and an UNCERTAIN validation summary; execution failures remain visible as PARTIAL. SQL reference-stream validation does not certify those DataFrame streams.
- Scale Factors: SF=1.0 (with fallback for scale-independent queries at other SFs)
- Queries: All 22 official TPC-H queries (Q1-Q22)
- Query Variants: Q15 variants supported (15a, 15b, etc.)
- Scale-Independent Queries: Q1 (same row count at any SF)
Expected results sourced from official TPC-H answer files distributed with dbgen.
- Scale Factors: SF=1.0 only
- Queries: All 99 official TPC-DS queries
- Query Variants: Base queries without substitution variants
- Scale-Independent Queries: None (all TPC-DS queries are scale-dependent)
Expected results sourced from official TPC-DS answer sets for SF=1.0.
The validation system handles various query ID formats automatically:
validator.validate_query_result("tpch", 1, actual_row_count=4)
validator.validate_query_result("tpch", "1", actual_row_count=4)
validator.validate_query_result("tpch", "Q1", actual_row_count=4)
validator.validate_query_result("tpch", "query1", actual_row_count=4)
validator.validate_query_result("tpch", "15a", actual_row_count=1)
validator.validate_query_result("tpch", "Q15b", actual_row_count=1)The first four calls all map to Query 1: an integer, a string, a Q prefix and a query prefix. The last two are query variants, which use the base query number and map to Q15.
You can validate query results manually using the QueryValidator:
from benchbox.core.validation.query_validation import QueryValidator
validator = QueryValidator()
result = validator.validate_query_result(
benchmark_type="tpch",
query_id="1",
actual_row_count=4,
scale_factor=1.0
)
if result.is_valid:
print(f"✅ Validation passed: {result.expected_row_count} rows")
else:
print(f"❌ Validation failed: {result.error_message}")Full validation support with exact expected row counts:
results = adapter.run_benchmark(
TPCH(scale_factor=1.0),
validate_row_counts=True
)At SF=1.0 all queries are validated. validate_row_counts=True is the default.
Scale-independent queries (e.g., TPC-H Q1) use SF=1.0 expectations:
validator.validate_query_result("tpch", "1", actual_row_count=4, scale_factor=10.0)
validator.validate_query_result("tpch", "2", actual_row_count=1000, scale_factor=10.0)At SF=10, Q1 still validates because it is scale-independent. It uses the SF=1.0 expectation (4 rows) and the result is PASSED. Q2 is scale-dependent and there are no SF=10.0 expectations, so its validation is SKIPPED.
If you need to disable validation:
results = adapter.run_benchmark(benchmark)There are two options. Option 1, disabling validation at the adapter level, is not yet implemented, because validation is always on. It may be added in a future version if needed. Option 2 is to ignore validation results by not checking the row_count_validation_status fields.
┌─────────────────┐
│ Platform Adapter│
│ (run_benchmark)│
└────────┬────────┘
│
▼
┌─────────────────────┐ ┌──────────────────┐
│ Execute Query │ │ Expected Results │
│ (returns row count) │────▶│ Registry │
└─────────────────────┘ └────────┬─────────┘
│
▼
┌─────────────────┐
│ QueryValidator │
│ • Normalize ID │
│ • Lookup expect │
│ • Compare count │
└────────┬────────┘
│
▼
┌─────────────────┐
│ValidationResult │
│ • is_valid │
│ • status │
│ • difference │
└─────────────────┘
-
QueryValidator: Main validation engine
- Normalizes query IDs (handles various formats)
- Retrieves expected results from registry
- Compares actual vs expected counts
- Returns structured ValidationResult
-
ExpectedResultsRegistry: Centralized result storage
- Lazy-loads expected results per benchmark/scale factor
- Thread-safe caching for performance
- Supports scale-independent query fallback
- Provider pattern for extensibility
-
Providers: Benchmark-specific result loaders
tpch_results.py: Loads TPC-H expected resultstpcds_results.py: Loads TPC-DS expected results- Auto-registered on first validation
The validation system is thread-safe for concurrent query execution:
results = adapter.run_throughput_test(
benchmark=benchmark,
connection=connection,
num_streams=4,
)Throughput tests with concurrent queries are safe. The streams share one connection, and each stream gets its own session. num_streams=4 runs 4 concurrent query streams, and each stream can validate concurrently without conflicts.
Implementation:
- Registry uses
threading.Lockto protect cache and provider registry - Double-check locking pattern for efficient concurrent access
- Slow provider loads happen outside locks
TPC-H and TPC-DS answer files (~4.2 MB total) are distributed separately from the BenchBox package:
| Installation method | Answer file availability |
|---|---|
git clone / uv sync (dev) |
✅ Included in _sources/ |
pip install benchbox (wheel) |
⬇️ Downloaded on-demand |
BENCHBOX_NO_DOWNLOAD=1 set |
For wheel installs, BenchBox automatically downloads a cached copy of the answer files the first time validation is needed. Subsequent runs use the local cache with no network overhead.
Downloaded answer files are stored in:
$XDG_CACHE_HOME/benchbox/answers/ # if XDG_CACHE_HOME is set
~/.cache/benchbox/answers/ # otherwise (default)
Subdirectories:
~/.cache/benchbox/answers/
├── tpch/ # q1.out … q22.out
└── tpcds/ # 1.ans … 99.ans + NULLS variants
To populate the cache before your first benchmark run (e.g., in a CI environment without internet access during the run itself):
benchbox download-answers
benchbox download-answers --benchmark tpch
benchbox download-answers --benchmark tpcds
benchbox download-answers --force
benchbox download-answers --show-cache-dirThe commands download both TPC-H and TPC-DS answers, TPC-H only, TPC-DS only, re-download even if cached, and print the cache location and exit.
To opt out of all automatic downloads (air-gapped environments, CI pipelines where networking is blocked):
export BENCHBOX_NO_DOWNLOAD=1With this set, BenchBox skips the download attempt and logs an INFO message instead of attempting a network connection. Validation is gracefully skipped for queries whose answer files are not available locally; the benchmark still runs, only correctness validation is omitted.
To serve answer files from a private CDN or internal mirror:
export BENCHBOX_ANSWERS_URL=https://your-cdn.example.com/benchbox/answers-v1The downloader will fetch tpch-answers.tar.gz, tpcds-answers.tar.gz, and
checksums.sha256 from this URL instead of the default GitHub releases URL.
See .github/workflows/upload-answers.yml for the expected archive layout.
Symptom: Queries fail validation at scale factor 1.0
Possible causes:
-
Data loading error
# Verify data was loaded correctly SELECT COUNT(*) FROM lineitem; -- Should match scale factor -
SQL translation issue
sql = benchmark.get_query(query_id=1, dialect="duckdb") print(sql)
-
Platform-specific behavior
# Some platforms may handle edge cases differently # Check if difference is consistent across queries
Symptom: All validations skipped at SF=10, SF=100, etc.
This is expected behavior:
- Expected results are only available for SF=1.0
- Scale-dependent queries cannot be validated at other SFs
- Scale-independent queries (e.g., TPC-H Q1) still validate via fallback
Solution: If you need validation at higher SFs, use SF=1.0 for correctness validation, then higher SFs for performance testing.
Symptom: Warning message about missing provider
Cause: Provider auto-registration failed (rare)
Solution:
from benchbox.core.expected_results import register_all_providers
register_all_providers()This triggers provider registration manually.
Symptom: Validation skipped with "No expected row count defined"
Causes:
- Query ID format not recognized
- Use standard formats: 1, "1", "Q1", "query1"
- Query variant not in answer files
- Some TPC-DS query variants may not have answer sets
- Non-standard query
- Custom queries won't have expected results
@dataclass
class ExpectedQueryResult:
query_id: str
scale_factor: float | None = None
expected_row_count: int | None = None
expected_row_count_min: int | None = None
expected_row_count_max: int | None = None
row_count_formula: str | None = None
validation_mode: ValidationMode = ValidationMode.EXACT
scale_independent: bool = False
notes: str | None = Noneexpected_row_count_min and expected_row_count_max are for non-deterministic queries. row_count_formula holds an expression such as "SF * 100".
-
EXACT: Row count must match exactly
expected_row_count = 4 actual_row_count = 4 actual_row_count = 5
An actual count of 4 passes. An actual count of 5 fails.
-
RANGE: Row count must be within min/max range (for non-deterministic queries)
expected_row_count_min = 100 expected_row_count_max = 150 actual_row_count = 125 actual_row_count = 200
An actual count of 125 passes. An actual count of 200 fails.
-
SKIP: Validation is skipped. It is used when no expected result is available.
For scale-dependent queries, formulas can express expected count:
ExpectedQueryResult(
query_id="example",
row_count_formula="SF * 1000",
scale_independent=False
)The formula scales with the scale factor.
Currently, only exact row counts are used. Formulas are evaluated using safe AST parsing (no eval()).
correctness_results = adapter.run_benchmark(
TPCH(scale_factor=1.0),
validate_row_counts=True
)
assert all(q['row_count_validation_status'] == 'PASSED' for q in correctness_results['queries'])
performance_results = adapter.run_benchmark(
TPCH(scale_factor=100),
validate_row_counts=True
)The first run establishes correctness at SF=1.0, and all queries should PASS. Then scale up for performance testing. Scale-independent queries still validate at the larger scale.
results = adapter.run_benchmark(benchmark)
failed_validations = [
q for q in results['queries']
if q.get('row_count_validation_status') == 'FAILED'
]
if failed_validations:
for q in failed_validations:
print(f"❌ Query {q['query_id']}: {q.get('row_count_validation_error')}")
raise AssertionError(f"{len(failed_validations)} queries failed validation")Use this pattern in automated tests.
print("Running at SF=10.0:")
print("- Scale-independent queries: VALIDATED")
print("- Scale-dependent queries: SKIPPED (no SF=10.0 expectations)")If you run at a scale factor other than 1.0, document that validation is limited.
if query_result['row_count_validation_status'] != 'PASSED':
print(f"⚠️ Query may be incorrect - investigate before performance tuning")
print(f" Expected: {query_result['expected_row_count']} rows")
print(f" Actual: {query_result['rows_returned']} rows")When you investigate performance issues, check correctness first.
Planned improvements to row count validation:
- Additional Scale Factors: Expected results for SF=10, SF=100
- Custom Expectations: Allow users to define expected results for custom benchmarks
- Result Content Validation: Validate actual result values, not just row counts
- Checksum Validation: MD5/SHA checksums of result sets
- Differential Validation: Compare results across platforms
- Configuration Options: Toggle validation on/off per query or benchmark
Note: Wheel installs can download answer files on demand. See the Installation and Answer File Availability section above.
- TPC-H Specification: https://www.tpc.org/tpch/
- TPC-DS Specification: https://www.tpc.org/tpcds/
- BenchBox Architecture: docs/design/architecture.md
- Issue Tracking: Report validation bugs on GitHub