Large numeric IN predicates cause slow SQL planning on Druid 27

Affected version

Druid 27 with Calcite 1.21.0.

Problem

A numeric IN predicate containing 11,481 literals causes a severe SQL planning slowdown:

maxLongUniform IN (11481 literal values)

A deterministic, planning-only JMH benchmark measured approximately 7,160 ms per plan on Druid 27. The delay occurs during SQL planning, before native query execution.

Root cause

For a literal list below inSubQueryThreshold, Calcite converts SQL IN into one equality per literal joined by OR:

SQL IN
  -> 11,481 equality OR terms
  -> RexSimplify.simplifyOrTerms
  -> native IN filter optimization

Druid 27's legacy null-replacement mode exposes numeric columns as non-nullable to Calcite. This makes simplifyOrTerms accumulate prior equality terms as predicates while simplifying later terms, which scales poorly for thousands of values. Druid eventually combines the filters into a native InDimFilter, but only after Calcite has paid the simplification cost.

The SQL-to-OR rewrite is intentional. The defect is the scaling of cross-term predicate simplification for a very large equality disjunction. This is related to #7904 and CALCITE-3178.

Version comparison

The exact Druid 32.0.0 result was verified using the official Calcite 1.37.0 artifact and the same maxLongUniform IN (11,481 literals) query shape with inSubQueryThreshold = Integer.MAX_VALUE.

Why Druid 32 and later are unaffected

Druid 32 removed legacy SQL-incompatible null handling in #17609. Ordinary numeric datasource columns are now exposed to Calcite as nullable. Calcite therefore skips the expensive predicate accumulation and can later combine the equality terms into SEARCH/Sarg.

The underlying Calcite weakness can still affect genuinely non-nullable schemas or plans where non-nullability is already established, but the original query against an ordinary Druid datasource does not reproduce the slowdown from Druid 32 onward.

Suggested actions

• On Druid 27, set inSubQueryThreshold below the literal count so Calcite uses an inline VALUES relation instead of generating the large OR expression; validate the resulting execution plan.
• Upgrade to Druid 32 or later for the long-term resolution of this ordinary-datasource case.
• Retain a large-IN planning benchmark as a regression guard.

PR #20314 adds a separate planning-timeout guardrail that limits the Broker impact of pathological planning cases; it does not change the root cause described here.