Repository navigation
SQL regression tests
The suite runner was introduced in PR #101. PR #105 adds the TPC-H/TPC-DS suites, execution settings at suite/case scope, and duplicate output column support. PR #107 adds persistent JIT caching and diagnostics. Floating-point tolerance support is in PR #111; the settings below require that change.
test_reg uses one TQueryCase and one RegQuery check for SQL cases. A case
without table bindings checks its raw logical plan. A case with table bindings
checks the optimized logical plan and, when execution is enabled, the query
result through the native Parquet source and scheduler. Existing AST goldens remain separate parser
checks, including when their SQL files are migrated to suites. Each SQL file gets
one AST check regardless of its execution variants; new suites need not have AST
goldens.
The test-only CMake target regtest_lib owns case discovery, JSON parsing,
fixture materialization, schema/result printing, and comparison. test_reg.cpp
contains GoogleTest checks and runner setup. test_regtest_lib.cpp tests the
infrastructure contracts, including a dedicated CBO ordering test.
The first data suite is order_by. Its seven queries cover NULL ordering, multiple sort keys, expressions, LIMIT/OFFSET, duplicate rows, self joins, and an empty-table override. They run with fixed fixture statistics in the single-threaded and threaded schedulers. There is no join-order matrix applied to the whole suite.
The tpch and tpcds suites check optimized plans for 22 TPC-H queries and
103 TPC-DS statements. They set "execute": false, so each SQL case has one
plan check using real schemas and statistics. Each fixture contains at
most ten rows sampled from the local benchmark Parquet files, with the original
column names, integer widths, and nullability. DATE32 values use Qumir's i32
days-since-epoch representation, matching the production Parquet source's schema
boundary. Statistics are computed for these small inputs. The four TPC-DS cases
that previously contained two statements now have one SQL file per statement,
so both parts are checked. Fixture provenance is recorded in each suite's README.
TPC-H/TPC-DS result goldens are omitted while execution is disabled. A case can
enable execution and create its result expectation through --canonize.
Review those rows against the intended SQL semantics: #102
tracks empty-input aggregate behavior, and #103
tracks correlated COUNT(*) returning NULL instead of zero for unmatched rows.
Keep input JSON, SQL, and result/plan goldens in ordinary Git. A directory with
suite.json uses its declared fixture bindings; a directory without a manifest
produces plan-only query cases. Manifests apply to their own directory and do not
implicitly change child directories.
test/regtest/
cases/order_by/
suite.json
tables/numbers.json
tables/empty.json
nulls_first.sql
empty.sql
goldens/order_by/
nulls_first.plan
nulls_first.result.json
empty.plan
empty.result.json
{
"version": 1,
"fixtures": {
"numbers": {"json": "tables/numbers.json", "parquet": {"row_group_rows": 2}},
"empty": {"json": "tables/empty.json"}
},
"tables": {"t": "numbers"},
"variants": ["single", "threaded"],
"cases": {
"nulls_first.sql": {"order": "ordered"},
"empty.sql": {"tables": {"t": "empty"}, "order": "ordered"}
}
}SQL and fixture paths are relative to the manifest. Named fixtures are local to
its suite. Default SQL table bindings are merged with case overrides; multiple
table names can refer to one fixture. Aliases and CTEs follow SQL scope rules.
A case with no bindings runs once in the plan variant and does not execute SQL.
execute is a boolean available at both manifest and case scope. It defaults
to true; a case inherits the suite setting unless it supplies its own value.
For example, a suite can set "execute": false and enable one case with
"cases": {"query.sql": {"execute": true}}.
With table bindings, "execute": false still prepares fixtures, builds a typed
plan, runs the optimizer, and compares the optimized plan golden. It registers
one plan variant, stopping before scheduler lowering and LLVM compilation.
Result goldens are neither read nor written in this mode. An executing case
uses the suite's declared single/threaded variants.
The runner derives golden paths from SQL paths: cases/<path>.sql maps to
goldens/<path>.plan and, for executing cases, goldens/<path>.result.json.
The manifest cannot override these paths. All execution variants share both
files. SQL cases must be directly in the manifest directory and listed in
cases. Missing files, unknown fields, invalid versions, unknown fixtures or
variants, and unmapped tables in an execution case fail explicitly.
Input data and result goldens use a Qumir struct type string and row arrays.
Schema strings are parsed with NCore::ParseType and canonically printed with
NCore::PrintType. Nullable fields use <named Nullable [T]>, which maps to
Arrow field nullability on input and QDB's TNullable on output.
Input fixtures include "version": 1. Result goldens contain only schema and
rows; they have no version. Regenerate them with --canonize when the result
format changes. For example:
{
"schema": "<struct (id i64) (label <named Nullable [string]>)>",
"rows": [
["1", "one"],
["2", null]
]
}Input fixtures, result goldens, and diagnostics use one complete row per line,
with spaces between cells. JSON escaping keeps quotes and newlines inside string
values on that same line. Empty tables/results use "rows": [].
Supported JSON fixture and result types are signed/unsigned integers through
64 bits, f64, bool, and string, with optional nullability. All 64-bit
integers use decimal strings, parsed without conversion through double. Smaller
integers may use JSON numbers. Overflow, invalid row widths, duplicate input
schema fields, and NULL in a non-nullable input column are errors. Result schemas
allow duplicate column names, as SQL projections do; comparison follows column
position. Empty and all-NULL tables retain their schema.
Floats use JSON numbers or tags such as {"$float":"nan"}, {"$float":"+inf"},
{"$float":"-inf"}, and {"$float":"-0"}. Finite f64 result cells, including
nullable floats, compare using:
abs(actual - expected) <= abs_tol + rel_tol * max(abs(actual), abs(expected))
Both tolerances default to 1e-12. Set "tol": {"abs": 1e-12, "rel": 1e-12}
in the suite manifest or a case definition. Each case inherits the suite values;
overriding only one component retains the other. Both values must be finite,
nonnegative JSON numbers. Set both to zero for exact finite comparisons.
Schema, nonfloating cells, NULL, and float tags compare exactly. For example,
nan matches nan, infinities must have the same sign, and tagged negative zero
remains distinct from positive zero. Stored goldens retain full precision; the
tolerance applies during comparison, including agreement between canonized
execution variants. Failure output includes the applied absolute and relative
tolerances. Decimal/date JSON fixtures are future extensions.
JSON fixtures default to computing custom statistics at materialization time;
"stats": "compute" requests this explicitly. The generator computes exact NDV
for supported non-NULL values and sampled equi-depth histogram boundaries for
numeric columns, then writes the existing stats key-value blob into the Parquet
footer. Strings and booleans receive NDV without histograms. NaN columns omit
NDV; non-finite float boundaries and unsigned values outside the metadata
reader's signed-integer range omit histograms.
A fixture can instead provide an explicit stats object:
"stats": {
"columns": [
{"name": "id", "ndv": 6, "ndv_exact": true},
{"name": "a", "ndv": 3, "ndv_exact": true, "histogram": [1, 1, 2, 2, 3]}
]
}Explicit NDV/histogram values are written as supplied and are not supplemented
with computed values. This lets a test force the intended CBO join ordering by
controlling its input statistics. Set ndv_exact only when exactness is part of
the intended input. The dedicated SuppliedStatsControlCboJoinOrder test changes
NDV for one three-table query, checks that CBO chooses a different lower join,
and verifies the same result; ordinary suites do not enumerate join orders.
"stats": "footer" omits custom metadata. In all generated files, the Parquet
writer emits standard statistics; row count comes from the physical footer and
min/max/null count come from its column statistics. The custom blob supplies
NDV/histograms and can fill missing standard min/max/null-count values. It does
not override row count or existing standard column statistics.
A fixture can also use an existing file: {"parquet_file":"tables/input.parquet"}.
The runner opens it directly and uses its embedded standard/custom metadata
without regeneration or recomputation. Exactly one of json and parquet_file
is required. Existing-file fixtures do not accept generator options. This path
also accommodates particular encodings or malformed-input tests; LFS is optional.
main collects metadata once. A preparation environment materializes only
fixtures bound by selected query cases, once per suite/input/layout. Listing
tests does not generate files. Identical JSON paths and row-group sizes share a
file within a suite. The writer uses uncompressed data, no dictionaries, and
128 rows per row group unless row_group_rows overrides that size.
Each file has a shared TParquetFile, while every source occurrence gets a fresh
MakeSource() cursor. Self joins and repeated variants therefore have independent
scan state and projections. Generated files live in a suite-owned temporary
directory and are cleaned up when its registry is destroyed. Fixture files are
prepared afresh for each process. Generation uses the C++ Parquet writer.
For cases with tables, build a fresh plan, apply normal plan passes including
CBO, and compare PrintPlanTreeWithCtes output to .plan. This is the optimized
logical plan before scheduler lowering, with row/cost estimates and CTE trees.
When execution is enabled, execute and compare typed output to .result.json. Cases without tables
retain the existing raw s-expression plan format and schema-less mock sources.
Parser errors remain valid expected text in plan-only cases.
The single preset uses one worker and one scan task. The threaded preset uses
four workers and up to four row-group scan tasks. An ordered result compares the
row sequence; unordered compares a multiset, preserving duplicate counts.
For floating-point rows, unordered comparison finds a complete one-to-one
matching within the tolerance. It can reassign ambiguous pairs, avoiding false
differences from sorting or greedy pairing. Every column in a matched row must
agree, and every row occurrence is consumed once.
ORDER BY tests use explicit tie-breaking keys. All variants share plan and result
goldens; a disagreement is a failure. A non-executing case compares only its plan.
test_reg enables a persistent JIT dependency cache in test_reg_jit_cache
beside the executable. A nonempty QDB_JIT_CACHE_DIR overrides that location.
Executing cases use this cache; query-specific kernels are still compiled.
QDB_REGTEST_TRACE_FIXTURES=1 logs actual input reads, Parquet writes, and
preparation time. QDB_REGTEST_TIMING=1 reports parsing, planning, optimization,
golden comparison, lowering, JIT, and execution separately. Combine it with
QDB_JIT_CACHE_STATS=1 for detailed compiler/cache timings. Disabled execution
reports zero lowering, JIT, execution, and result-comparison times.
cmake --build build --target test_reg -j 4
ctest --test-dir build -R '^test_reg$' --output-on-failure
build/test/test_reg test/regtest --gtest_filter='RegQuery.order_by_*_single:RegQuery.order_by_*_threaded' --print
build/test/test_reg test/regtest --gtest_filter='RegQuery.order_by_*_single:RegQuery.order_by_*_threaded' --canonizeFor suite cases, --canonize collects plan and result goldens in memory and
checks agreement across selected variants. Both are written only after the
selected run passes. Missing expectations can be created in this mode. AST and
plan-only cases without manifests retain their existing immediate canonization.
Review plan changes and expected rows independently of the engine output.
For suite cases with "execute": false, canonization collects and commits only
plan goldens after the selected run passes.
The Roadmap describes the query execution goal, with fixture storage as a design choice. SQL input/result tests can migrate into suites; tests of ABI, ownership, scheduling, and internal estimates can stay in C++ where result and plan goldens cannot verify the contract.