Cross-engine SQL semantic differences

Last updated: 6 Oct 20265 min read

tutorial

A translated query can compile and still change a business answer because data types, null rules and time boundaries are part of the computation.

Define the result contract first

Name the business grain, column types, null meaning, currency precision, time zone and ordering requirements before translating SQL. A generated query is only a candidate implementation of that contract. Compare outputs from a pinned input generation; otherwise a late event can masquerade as an engine difference. Metric contracts provide the shared definition.

Probe null and anti-join behavior

A NOT IN predicate can produce surprising results when its right side contains NULL. Other constructs differ in how casts, division by zero or invalid dates fail or return NULL. Use a tiny fixture with null keys, duplicate keys and malformed values to expose semantics before full-table reconciliation. Prefer explicit null policy and a tested anti-join formulation to a dialect-dependent shortcut.

Protect numeric meaning

Decimal precision, scale and rounding mode matter for payment totals. Casting to binary floating point to make two schemas appear compatible can introduce small differences on every row that grow after aggregation. Specify whether tolerance is zero, an absolute amount or a relative bound, and explain why. For money, compare integer minor units or exact decimals when both engines support them.

Pin time boundaries

A timestamp with an offset and a local wall-clock value are not interchangeable. A daily grouping near midnight can shift between regions if one engine applies a session time zone and the other uses UTC. Set the zone in the query or connection, provide daylight-saving boundary fixtures and compare interval inclusion rules. Event-time handling also determines which late records belong to the result.

Compare canonical values

Normalize only representation differences allowed by the contract, such as a decimal string format or stable column alias. Preserve meaningful differences, including NULL versus zero and a missing row versus an empty string. Sort by a complete key before comparing sets, and do not compare unordered result positions. Keep the original values alongside canonical values so a discrepancy remains explainable.

Implementation

from decimal import Decimal
from datetime import datetime, timezone

source = [("region-west", "2026-09-27T23:40:00+00:00", "47.25"),
          ("region-east", "2026-09-28T00:20:00+00:00", None)]
target = [("region-east", "2026-09-28T00:20:00Z", None),
          ("region-west", "2026-09-27T23:40:00Z", "47.25")]

def canonical(rows):
    return sorted((region,
                   datetime.fromisoformat(timestamp.replace("Z", "+00:00")).astimezone(timezone.utc),
                   None if amount is None else Decimal(amount))
                  for region, timestamp, amount in rows)

assert canonical(source) == canonical(target)

Performance and operating cost

Canonicalizing N rows requires O(N log N) time for sorting and O(N) output space. Large comparisons should partition by stable key and use staged hashes plus row-level follow-up. Exact decimal arithmetic costs more than floating point but avoids an unjustified numeric tolerance. Query scans in both engines often dominate the comparison job.

Common Mistakes

Do not assume successful SQL translation proves equivalent results.
Do not convert NULL to zero during canonicalization.
Do not compare unordered row positions or unpinned input generations.