BETWEEN with a column bound fails three different ways, for every column type

[fupelaqu]

BETWEEN works with literal bounds and fails with a column bound. The failure differs by whether a schema is attached, so both paths are given separately below — an earlier draft showed the schema-less emission under text that read as though it were the general case.

All re-measured at 80f76b1a. Pre-existing; verified against a control clone of the merge-base.

This also corrects an earlier note describing it as "d BETWEEN ts AND ts fails — same column twice": the repetition is irrelevant, and it is not restricted to dates.

Schema-LESS — a client-side throw, before any query is built

Measured through the bridge, which is the production path when no schema is attached:

d BETWEEN ts AND ts2   ->  IllegalArgumentException: Unsupported out type for range query: ANY
n BETWEEN lo AND hi    ->  IllegalArgumentException: Unsupported out type for range query: ANY

d BETWEEN '2025-01-01' AND '2025-12-31'
  ->  {"range":{"d":{"gte":"2025-01-01","lte":"2025-12-31"}}}                       ✅

The numeric row is the point: this is not a temporal defect.

The mixed literal/column form, same path

A range query is emitted carrying a Painless expression — and a re-quoted literal — as string bounds:

{"range":{"d":{"gte":"\"2025-01-01\"","lte":"(doc['ts'].size() == 0 ? null : doc['ts'].value)"}}}

Executed against a real index:

400 parse_exception: failed to parse date field ["2025-01-01"] with format
    [strict_date_optional_time||epoch_millis]

Schema-CARRYING — a script, and a different failure

Here the same SQL reaches Painless instead. Column bounds emit relational operators:

d BETWEEN ts AND ts2   ->  param1 >= param2 && param1 <= param3
n BETWEEN lo AND hi    ->  param1 >= param2 && param1 <= param3

Correct for numerics (executed: true). On temporals the doc-values are ZonedDateTime objects:

400 class_cast_exception: Cannot apply [>] operation to types
    [java.time.ZonedDateTime] and [java.time.ZonedDateTime]

The mixed form takes a third shape again — compareTo against the literal, and against the column:

d BETWEEN '2025-01-01' AND ts
  ->  param1.compareTo("2025-01-01") >= 0 && param1.compareTo(param2) <= 0

ZonedDateTime.compareTo(String) does not exist.

Relationship to the column-vs-column fix

#373's item 4 fixed this family for binary comparison — a column compared with another column now runs as a script. BETWEEN never reaches that code: schema-less it throws while the range query is being built, and schema-carrying it takes the check path that emits raw >=/<= rather than the temporal isBefore/isAfter dispatch. The control diff confirms that fix moved none of these shapes.

Adjacent

A BETWEEN whose bounds reference an undeclared column was invisible to the computed-column reference walk until #376, because the bounds live in a FromTo, which is not a PainlessScript. That half is fixed; this one is not.

[fupelaqu]

Context for whoever picks this up: #384 merged as PR #388 (975aa87b) and deliberately did not touch BETWEEN — by decision, because this is a dispatch defect (it fails ZonedDateTime against ZonedDateTime too, and throws before Painless when no schema is attached) rather than the reconciliation defect #384 owned. The shape is characterised, not silently accepted, in MixedTemporalComparisonSpec.

What changed under this issue's feet, all on main:

1. The reconciliation #381 needs now exists. Expression gained renderedType / operandRenderedType / valueRenderedType (the JAVA type an operand renders, where chainType is the SQL one), comparisonTargetType (the type BOTH operands are reconciled to) and promotedRight. A comparison between a LocalDate operand and a ZonedDateTime one now promotes whichever side is date-only.

2. It provably cannot reach a BETWEEN bound today, and that is the concrete thing to fix. BetweenExpr overrides check (Where.scala:2249) and renders both bounds itself:

val from = painlessOperand(fromTo.from, context)
val to   = painlessOperand(fromTo.to, context)
s"(${super.check(context, param, from, GE)} && ${super.check(context, param, to, LE)})"

The promotion travels in the value argument that override discards, so neither promotedRight nor the new ingest-script parse (processorParsedRight) reaches a bound. BetweenExpr overrides check and bucketPipelineCheck only — not painless/leftOperand — so the LEFT operand does get the full treatment while the bounds get none. That asymmetry is this issue's schema-carrying half, stated in terms of the code that causes it.

3. There is a second out-vs-chainType lie still live here. BetweenExpr.validate (Where.scala:2233) reads validateTypesMatching(identifier.out, fromTo.out) — the COLUMN's type, not the chain's. #384 fixed exactly that for Expression.validate (now identifier.chainType / valueType); the BETWEEN copy was left alone because nothing downstream of it reconciles yet.

4. One allowance was deliberately withheld from BETWEEN. #384 added SQLTypeUtils.comparableTemporals (DATE/DATETIME/TIMESTAMP mutually comparable — measured: DuckDB and PostgreSQL 17 both accept DATE > TIMESTAMP; both reject the TIME pairs) and scoped it to the comparison only. Widening the shared SQLTypeUtils.matches was implemented, measured and reverted: it made CAST(d AS DATE) BETWEEN CAST('2025-01-01' AS DATE) AND CAST('…' AS TIMESTAMP) parse and then fail with all shards failed, because BETWEEN does not promote. That statement is pinned as still rejected. When this issue makes the bounds reconcile, that allowance should extend to BETWEEN — and not before.

So the suggested order is: make check dispatch per bound (which is what the >=/<=-instead-of-isBefore/isAfter half of this issue is), route the bounds through the existing reconciliation, then switch validate to the chain types and extend comparableTemporals. The schema-less half (Unsupported out type for range query: ANY, thrown while the range query is built) is a different layer and unaffected by any of the above.