Result Comparison Modes

Relevant source files

• test/evidence/slt_lang_aggfunc.test
• test/index/between/10/slt_good_1.test
• test/index/between/10/slt_good_5.test

Purpose and Scope

This document describes the result comparison modes used in SQL Logic Test files to validate query outputs. These modes determine how the test runner compares actual query results against expected values, enabling flexible and robust testing of SQL behavior across different scenarios.

For information about test directives and query type specifications, see Test Directives Reference. For the overall test file format, see Test File Format Specification.

Overview

Result comparison modes control how query results are validated in SQL Logic Test files. The mode is specified as part of the query directive and determines whether results must match exactly in order, can be compared order-independently, or are hashed for large result sets.

Comparison Mode Syntax

Query directives follow this pattern:

query <type> <mode> [label-<name>]

Where:

• <type> specifies the column data types (I, T, R, II, III, etc.)
• <mode> specifies the comparison mode (nosort, rowsort, or omitted for hash)
• label-<name> is an optional marker for grouping related tests

Sources: test/evidence/slt_lang_aggfunc.test:28-29 test/index/between/10/slt_good_1.test:90-91

Comparison Modes

Mode: nosort

The nosort mode requires that query results match the expected output in exact order. The database must return rows in the precise sequence specified in the test file.

Example Usage

query I nosort
SELECT count(DISTINCT x) FROM t1
----
2

flowchart TD
 
   Query["query I nosort"] --> Execute["Execute SQL Query"]
Execute --> ActualResults["Actual Results:\nRow 1, Row 2, Row 3"]
ExpectedResults["Expected Results:\nRow 1, Row 2, Row 3"] --> Compare
 
   ActualResults --> Compare["Compare Results\nOrder-Sensitive"]
Compare --> Match{"Exact\nMatch?"}
Match -->|Yes| Pass["Test Pass"]
Match -->|No| Fail["Test Fail"]

In this example, the query must return the single value 2. Since it's a single-value result, order is trivial, but for multi-row results, the order must match exactly.

Diagram: nosort Comparison Flow

Use Cases:

• Testing ORDER BY clauses where sequence matters
• Validating aggregation functions that return specific ordered results
• Ensuring deterministic query execution order

Sources: test/evidence/slt_lang_aggfunc.test:28-31 test/evidence/slt_lang_aggfunc.test:173-177

Mode: rowsort

The rowsort mode performs order-independent comparison. Both actual and expected results are sorted before comparison, allowing tests to validate result sets regardless of the order in which rows are returned.

Example Usage

query I rowsort label-0
SELECT pk FROM tab0 WHERE col4 BETWEEN 57.93 AND 43.23 OR ((col3 > 27) AND (col3 >= 59))
----
0
3
5
7
9

In this example, the database can return the primary keys 0, 3, 5, 7, 9 in any order, and the test will pass as long as the set of values matches.

Diagram: rowsort Comparison Flow

flowchart TD
 
   Query["query I rowsort"] --> Execute["Execute SQL Query"]
Execute --> ActualResults["Actual Results:\n3, 0, 9, 5, 7"]
ExpectedResults["Expected Results:\n0, 3, 5, 7, 9"] --> SortExpected["Sort Expected"]
ActualResults --> SortActual["Sort Actual"]
SortActual --> SortedActual["Sorted Actual:\n0, 3, 5, 7, 9"]
SortExpected --> SortedExpected["Sorted Expected:\n0, 3, 5, 7, 9"]
SortedActual --> Compare["Compare Sorted Results"]
SortedExpected --> Compare
 
   Compare --> Match{"Match?"}
Match -->|Yes| Pass["Test Pass"]
Match -->|No| Fail["Test Fail"]

Use Cases:

• Testing queries without ORDER BY where row order is undefined
• Validating set operations (UNION, INTERSECT, etc.)
• Comparing results from indexed vs. non-indexed queries
• Testing BETWEEN operator behavior across different table structures

Sources: test/index/between/10/slt_good_1.test:90-97 test/index/between/10/slt_good_1.test:230-239

Hash-Based Comparison

For large result sets, tests use hash-based comparison to avoid storing thousands of expected rows. The expected output specifies a count and hash value instead of individual rows.

Example Usage

query I rowsort label-10
SELECT pk FROM tab0 WHERE col0 < 72 AND ((col3 >= 83 ...
----
9 values hashing to 0b2f3ce47428ebec5f2931eddc864093

flowchart LR
 
   Query["query I rowsort label-10"] --> Execute["Execute SQL Query"]
Execute --> Count["Count Rows"]
Execute --> Hash["Hash Row Values"]
Count --> CountResult["Result: 9 rows"]
Hash --> HashResult["Result: 0b2f3ce..."]
Expected["Expected:\n9 values hashing to\n0b2f3ce..."] --> CompareCount
 
   CountResult --> CompareCount["Compare Count"]
HashResult --> CompareHash["Compare Hash"]
Expected --> CompareHash
 
   CompareCount --> Match{"Both\nMatch?"}
CompareHash --> Match
 
   Match -->|Yes| Pass["Test Pass"]
Match -->|No| Fail["Test Fail"]

The test validates that:

1. The query returns exactly 9 rows
2. The rows hash to the specified value 0b2f3ce47428ebec5f2931eddc864093

Diagram: Hash-Based Comparison Flow

Use Cases:

• Complex queries with hundreds or thousands of result rows
• Index optimization tests where the full result set is large
• Queries with deeply nested conditions

Hash Threshold: Tests may specify a hash threshold at the file level:

hash-threshold 8

This indicates that result sets with more than 8 values will use hash-based comparison.

Sources: test/index/between/10/slt_good_1.test1 test/index/between/10/slt_good_1.test:181-183 test/evidence/slt_lang_aggfunc.test1

Label Markers

Label markers provide semantic grouping for related tests and serve as documentation within test files. They follow the pattern label-<identifier>.

Common Label Patterns

Example Usage

query I nosort label-NULL
SELECT avg(x) FROM t1 WHERE y='null'
----
NULL

query I nosort label-sum
SELECT sum(x) FROM t1
----
5

query I nosort label-zero
SELECT total(x) FROM t1 WHERE y='null'
----
0

Benefits:

• Enables selective test execution by label
• Documents the intent of test groups
• Facilitates test maintenance and debugging
• Allows cross-referencing related tests

Sources: test/evidence/slt_lang_aggfunc.test:187-195 test/evidence/slt_lang_aggfunc.test:375-383 test/evidence/slt_lang_aggfunc.test:409-417

Expected Output Format

Single-Value Results

For single values, the expected output follows immediately after the ---- separator:

query I nosort
SELECT count(*) FROM t1
----
5

Multi-Row Results

For multiple rows, each row is on a separate line:

query I rowsort label-20
SELECT pk FROM tab0 WHERE (col0 BETWEEN 14 AND 89)
----
2
3
6
7
8
9

Multi-Column Results

Multi-column results are specified with each row on its own line, columns separated by the column count in the query type:

query II nosort
SELECT x, y FROM t1 ORDER BY x
----
0 false
1 true
2 true

NULL Values

NULL values in results may be represented differently depending on context:

• In numeric columns: The literal NULL or the label marker label-NULL

• In text columns: The string "NULL" (as actual data)

  query I nosort label-NULL SELECT min(x) FROM t1 WHERE y='null'

  NULL

Sources: test/evidence/slt_lang_aggfunc.test:361-369 test/index/between/10/slt_good_1.test:230-239

Practical Examples

Example 1: Testing Aggregate Functions with nosort

This example validates the avg() function behavior with floating-point results:

query R nosort
SELECT avg(x) FROM t1
----
1.250

The R type indicates a real (floating-point) result, and nosort ensures exact value matching.

Sources: test/evidence/slt_lang_aggfunc.test:173-177

Example 2: Testing BETWEEN Operator with rowsort

This example validates that BETWEEN syntax and explicit comparison are equivalent:

query I rowsort label-20
SELECT pk FROM tab0 WHERE (col0 BETWEEN 14 AND 89)
----
2
3
6
7
8
9

query I rowsort label-20
SELECT pk FROM tab0 WHERE (( col0 >= 14 AND col0 <= 89))
----
2
3
6
7
8
9

Both queries share the same label and expected results, demonstrating semantic equivalence.

Sources: test/index/between/10/slt_good_1.test:230-249

Example 3: Complex Query with Hash Comparison

query I rowsort label-10
SELECT pk FROM tab0 WHERE col0 < 72 AND ((col3 >= 83 AND ...
----
9 values hashing to 0b2f3ce47428ebec5f2931eddc864093

The complex WHERE clause generates 9 results, which are validated via hash rather than listing all values.

Sources: test/index/between/10/slt_good_1.test:181-183

Result Comparison Mode Decision Tree

Diagram: Choosing the Appropriate Comparison Mode

Sources: test/evidence/slt_lang_aggfunc.test1 test/evidence/slt_lang_aggfunc.test:28-31 test/index/between/10/slt_good_1.test:90-97

Comparison Mode Summary

Implementation Notes

1. nosort comparison : Results are compared line-by-line in the order returned
2. rowsort comparison : Both actual and expected results are sorted lexicographically before comparison
3. Hash comparison : Results are counted and hashed; only count and hash value are compared
4. Label markers : Do not affect comparison logic; used only for organization and filtering

Sources: test/evidence/slt_lang_aggfunc.test:28-500 test/index/between/10/slt_good_1.test:90-809

Related Directives

Result comparison modes work in conjunction with:

• Query type specifiers (I, T, R, II, etc.) - define expected column types
• Statement directives (statement ok, statement error) - do not use comparison modes
• Conditional directives (skipif, onlyif) - control test execution but not comparison

For complete directive documentation, see Test Directives Reference.

Sources: test/evidence/slt_lang_aggfunc.test:3-16 test/index/between/10/slt_good_1.test:3-53