apache/druid · error · IllegalStateException

Must use natural comparator for column

Error message

Must use natural comparator for column [%s] of type [%s]

What it means

GroupByQueryKit.validateQuery enforces that any OrderByColumnSpec in an MSQ group-by uses the natural (default) comparator consistent with the column's result type. DimensionComparisonUtils.isNaturalComparator fails for other comparators (e.g. lexicographic/alphanumeric/numeric overrides or custom strategies), so an IllegalStateException is thrown because sort-merge group-by cannot honor them.

Solutions

1. Remove the explicit comparator from the order-by column so the natural comparator is used.
2. Ensure the column exists in the result signature so its type can be resolved (fix typos / include the column in dimensions).
3. If non-natural ordering is required, sort results outside MSQ (outer query or client-side) instead of in the group-by limitSpec.
4. Rewrite the ordering on an expression column that Druid can compare naturally.

Example fix

// before
"orderBySpec": [{"dimension": "d", "orderBySpec": {"comparator": "numeric"}}]
// after
"orderBySpec": [{"dimension": "d"}]  // natural comparator

// Ensure order-by columns use the natural comparator and are present in the result signature
for (const col of q.orderBy || []) {
  if (col.comparator && col.comparator !== 'natural') {
    throw new Error('MSQ group-by requires natural comparator for column ' + col.dimension);
  }
  if (!resultSignature.some(c => c.name === col.dimension)) {
    throw new Error('Order-by column not in result signature: ' + col.dimension);
  }
}

const usesNaturalComparator = (col, sig) => !col.dimensionComparator || sig.find(c => c.name === col.dimension)?.type !== undefined;

When it happens

Trigger: Submitting a group-by query via MSQ whose ordering (orderBySpec / limitSpec columns) specifies a non-natural dimension comparator — e.g. comparator "numeric" or "strlen" — or an ordering column whose result type is unknown.

Common situations: Native group-by queries ported from the classic engine with explicit comparators; SQL tooling generating orderBy columns with strategies; ordering on expression columns without a resolvable type.

Understand the failure class

Background: "Invalid value" and "allowed values are" config errors: what your library rejected and how to fix it — this error's family across 41 libraries.

Related errors

• Must use 'filter' or 'always' havingSpec
• Row compares higher than mark; out-of-order input?
• Adjacent intervals are not sorted
• Ambiguous build, limit
• Ambiguous build, limitSpec

AI-assisted analysis of apache/druid@9b90983fd2 (2026-09-07). Data as JSON: /api/errors/99d755d24dae474e. Report an issue: GitHub.

Appendix: source

Thrown at multi-stage-query/src/main/java/org/apache/druid/msq/querykit/groupby/GroupByQueryKit.java:393

    Preconditions.checkState(
        query.getLimitSpec() instanceof NoopLimitSpec || query.getLimitSpec() instanceof DefaultLimitSpec,
        "Must have noop or default limitSpec"
    );

    final RowSignature resultSignature = computeResultSignature(query);
    QueryKitUtils.verifyRowSignature(resultSignature);

    if (query.getLimitSpec() instanceof DefaultLimitSpec) {
      final DefaultLimitSpec defaultLimitSpec = (DefaultLimitSpec) query.getLimitSpec();

      for (final OrderByColumnSpec column : defaultLimitSpec.getColumns()) {
        final Optional<ColumnType> type = resultSignature.getColumnType(column.getDimension());

        if (!type.isPresent() || !DimensionComparisonUtils.isNaturalComparator(
            type.get().getType(),
            column.getDimensionComparator()
        )) {
          throw new ISE(
              "Must use natural comparator for column [%s] of type [%s]",
              column.getDimension(),
              type.orElse(null)
          );
        }
      }
    }
  }

}

View on GitHub (pinned to 9b90983fd2)