CockroachDB 2.1.6 preserves ordering through aggregation plans

A column can be unnecessary to the final result while remaining necessary to execute the query correctly. CockroachDB 2.1.6 fixes a distributed aggregation bug caused by losing that distinction. A projection that removed unused columns also needed to preserve columns and ordering information used to combine distributed inputs.

Jordan Lewis's aggregation repair, included in this maintenance release, describes incorrect results from multi-node aggregations with implicit, partial input orderings. The fix uses the input's ordering requirements and retains columns required to express that ordering.

I value CockroachDB's consistency model because it gives applications a strong transaction contract. Query execution has its own correctness obligations within that contract. Replicating data consistently does not by itself ensure that a planner transformation preserves the meaning of an aggregation.

Projection has physical requirements too

The affected code builds the input to a group-by operation. It identifies grouping columns and columns consumed by aggregate functions, then removes other columns when appropriate. The commit explains that normalization usually performs this simplification earlier, so the problematic builder path was uncommon.

A purely value-oriented analysis can make the removed columns look redundant. If a column is absent from the grouping keys and aggregate arguments, it seems irrelevant to the final row. That conclusion misses the physical property used to deliver input in the required order.

The patch adds ordering columns to the set of needed columns. Its comment gives an ordered concatenation example in which the aggregate consumes a string but depends on another column's ordering. Removing the ordering column too early prevents distributed execution from maintaining that property.

The implementation also changes which ordering it passes into the projection. It uses the group-by input's physical ordering, not the group-by expression's ordering. These are not necessarily the same because the aggregation can change the rows and properties visible above it.

A further adjustment updates the output-column mapping before deriving the required ordering. This keeps the ordering description aligned with the projection's actual columns. Ordering metadata referring to the wrong column positions cannot provide a valid contract for subsequent execution.

The bug therefore combines two concerns that are easy to discuss separately. Projection chooses which values remain available. Physical planning chooses properties of the streams carrying those values. A transformation must preserve both when the downstream operator relies on them.

The regression uses a distributed setting

The commit adds a regression case with a composite primary key and an additional index. Its comment explains that the extra index helps the optimizer recognize an available ordering even though that index is not the access path used by the query.

The test also splits and relocates data, then inspects the resulting plans. This is more specific than checking an aggregation against one local table. The failure concerns the ordering required when distributed streams come together.

Its query groups by one primary-key column while restricting another to a fixed value. That constraint makes an ordering available which is not simply the table's unrestricted key order. The expected plan retains the ordering through the projection beneath the group operation.

This is why a multi-node plan matters to the fix. An individual input can have an order that must be respected when combined with other inputs. A plan that silently loses the requirement can feed an aggregation incorrectly even when the underlying scans each behave as expected.

My preference is to assess a correctness repair through the invariant it restores. Here, the invariant is concrete. Columns required for input ordering must remain available, and the projection must carry the input's ordering description into distributed planning.

UNION ALL has a separate post-processing fix

The release also includes Lewis's UNION ALL planning repair. Before it, two inputs could appear to have identical post-processing even when their render expressions differed.

Further processing above the union could then invalidate the plan and cause errors or crashes. The commit names projections, filters and renders above UNION ALL as affected forms in some cases. This is separate from the aggregation bug's incorrect results.

The patch strengthens the comparison of last-stage post-processing. It checks the number of render expressions and then compares their contents, alongside existing filter and projection checks. Two branches cannot share the same treatment merely because their output-column counts match.

This is another case where an apparently redundant detail carries meaning. A render expression determines how an output value is produced. Comparing only surrounding structural properties leaves out the computation that distinguishes one branch from another.

The changes between 2.1.5 and 2.1.6 include both repairs and a fix for an update using a subquery that returns no rows. The aggregation change deserves particular attention because its documented symptom is an incorrect answer, rather than a rejected query. Version 2.1.6 restores the physical-ordering requirements needed by that distributed execution path while separately tightening the union planner's comparison of branch computations.