unlock unlimited code runs for $79, limited time →
NOSSLETTER.techLIVE
back to OSSpedia
duckdb/duckdb
mid8/10
data-structureserror-handlingtesting-strategy

Fix ASOF join over EXCEPT ALL / INTERSECT ALL children

Project: duckdb/duckdb | Language: C++ | Concepts: data-structures, error-handling, testing-strategy | Difficulty: mid
Link: https://github.com/duckdb/duckdb/pull/24760

The problem

When performing an ASOF JOIN in DuckDB where either side is an EXCEPT ALL or INTERSECT ALL set operation, the query failed with an internal error. This was due to a mismatch between the expected and actual column types and counts, caused by an internal ROW_NUMBER column added during physical planning for bag semantics. This ROW_NUMBER column was not properly hidden from later planning stages, causing downstream errors and assertion failures.

Why it's non-trivial

A naive fix might try to patch over the type or column count mismatch at the join or projection level. However, the root cause is subtle: physical planning for set operations temporarily mutates the logical operator's types, leaking an implementation detail (the ROW_NUMBER column) into later planning steps. Fixing this requires careful handling of operator types, ensuring that logical and physical types remain consistent and that implementation details do not leak into the logical plan.

The approach

Use the types of the planned children, not the logical operator

The main fix is to always use the types from the planned (physical) children, not from the logical operator, since planning may have changed the types. For example:

-	join_op.types = op.children[1]->types;
-	const auto &probe_types = op.children[0]->types;
+	// Use the types of the planned children: planning a child can change the types of the logical operator
+	// (EXCEPT ALL/INTERSECT ALL append a ROW_NUMBER column to it)
+	const auto &probe_types = probe.GetTypes();
+	const auto &build_types = build.GetTypes();
+
+	join_op.types = build_types;
 	join_op.types.insert(join_op.types.end(), probe_types.begin(), probe_types.end());

Annotation:
Instead of reading types from the logical children (op.children[i]->types), the code now reads from the actual planned children (probe.GetTypes(), build.GetTypes()). This ensures that any temporary or implementation-specific columns (like ROW_NUMBER) do not leak into the join's output schema.

Update projection maps and predicate remapping to use correct types

All code that previously used the logical children’s types is updated to use the planned children’s types:

-	for (idx_t i = 0; i < op.children[1]->types.size(); ++i) {
+	for (idx_t i = 0; i < build_types.size(); ++i) {
 		join_op.left_projection_map.emplace_back(i);
 	}

Annotation:
Projection maps are now sized according to the actual output columns of the planned children, not the logical children.

-	for (idx_t i = 0; i < op.children[0]->types.size(); ++i) {
+	for (idx_t i = 0; i < probe_types.size(); ++i) {
 		join_op.right_projection_map.emplace_back(i);
 	}

Annotation:
Same as above, but for the right/probe side.

-	const auto lhs_width = op.children[0]->types.size();
-	const auto rhs_width = op.children[1]->types.size();
+	const auto lhs_width = probe_types.size();
+	const auto rhs_width = build_types.size();

Annotation:
Predicate remapping also uses the correct, planned type widths.

Ensure logical operator types are restored after physical planning

When planning set operations, the logical operator’s types are restored after the ROW_NUMBER column is appended and used:

+			// Restore the logical operator's types: the ROW_NUMBER column is an implementation detail of the physical
+			// plan.
+			op.types.pop_back();

Annotation:
After the physical plan has made use of the extra column, it is removed from the logical operator to prevent further leakage.

Test coverage

A new test file is added to verify ASOF JOINs work correctly with EXCEPT ALL and INTERSECT ALL on either or both sides, for both the normal and NLJ optimization paths:

+query II
+SELECT * FROM (SELECT * FROM a EXCEPT ALL SELECT * FROM b) t0
+ASOF LEFT JOIN r t1 ON t0.d >= t1.d
+ORDER BY ALL;
+----
+1	1
+2	2

Annotation:
This and similar queries ensure the bug is fixed and the output is as expected.

From the review thread

No notable review discussion on this PR.

Takeaways

  • Always use the output types of planned (physical) operators, not logical operators, after planning steps that may mutate types for implementation reasons.
  • Implementation details (such as auxiliary columns for bag semantics) should be strictly contained within physical planning and must not leak into the logical plan or operator interfaces.
  • Comprehensive regression tests are essential after fixing subtle bugs involving query planning and schema consistency, especially for edge cases involving set operations or joins.