Cast properties are derived from `canCoerce`, which silently breaks for complex types

Summary

TypeCoercion.canCoerce answers one question: is this pair related by an implicit coercion? Several optimizer sites use it as a proxy for a semantic property of the cast function instead — injectivity, order preservation, or fallibility. Being an implicit coercion implies none of those:

• bigint -> double is an implicit coercion and is not injective above 2^53.
• char(n) -> varchar(n) is an implicit coercion and is not order-preserving, because CHAR comparison is PAD SPACE and VARCHAR comparison is NO PAD.
• date -> timestamp with time zone is an implicit coercion and is not monotone across a forward DST transition.

Each site knows this and guards the counter-examples — but every guard is written as a top-level instanceof / equals check, and then falls through to canCoerce. canCoerce recurses through array, map and row (typeCompatibilityForCovariantParametrizedType, and field-wise for row), while the guards do not.

So for every type pair that is correctly rejected at the top level, the same pair wrapped in a container is silently accepted, and the optimizer rewrites on a false premise. This is not a bug about one type pair — it is the same structural gap repeated at every site.

All line references are against master at adbbc5bd2212.

The sites

Two of these are latent only by accident. DomainTranslator and RemoveUnsupportedDynamicFilters both need a SATURATED_FLOOR_CAST, which is not registered for array/map/row, so they bail before the bad premise is used — RemoveUnsupportedDynamicFilters checks explicitly in doesSaturatedFloorCastOperatorExist. They are protected by the absence of an operator, not by a stated invariant; registering a container saturated floor cast makes them live.

Reproductions

All of these have a control written so the rule cannot fire (transform, or an explicit per-element cast), which returns the correct answer.

1. array(bigint) -> array(double) — equality drops rows. 9007199254740993 (2^53+1) and 9007199254740992 (2^53) cast to the same double.

SELECT * FROM (VALUES ARRAY[BIGINT '9007199254740993']) t(a)
WHERE CAST(a AS array(double)) = ARRAY[DOUBLE '9007199254740992'];
-- expected: 1 row; actual: 0 rows

SELECT * FROM (VALUES ARRAY[BIGINT '9007199254740993']) t(a)
WHERE transform(a, x -> CAST(x AS double)) = ARRAY[DOUBLE '9007199254740992'];
-- 1 row

The rule rewrites the predicate to a = ARRAY[BIGINT '9007199254740992']. At the top level the identical cast is handled correctly: isInjectiveOrderPreservingCastAtValue requires the value to be strictly inside (-2^53, 2^53), and 2^53 is not, so CAST(x AS double) = DOUBLE '9007199254740992' for scalar x is left alone. Same for row(bigint) -> row(double).

2. array(char) -> array(varchar) — ordering comparison flips.

SELECT * FROM (VALUES ARRAY[CAST('a' || chr(0) AS char(3))]) t(a)
WHERE CAST(a AS array(varchar(3))) < ARRAY[CAST('a' AS varchar(3))];
-- expected: 0 rows; actual: 1 row

Chars.compareChars pads the shorter side with 0x20, so CHAR 'a\0' < CHAR 'a', while VARCHAR NO PAD gives 'a\0' > 'a'. The rewritten plan compares as char(3). At the top level this pair is routed to unwrapCharToVarcharCast, which unwraps only the equality family; the nested pair never reaches that dispatch.

3. map(varchar, bigint) -> map(varchar, double) — rewritten with no round-trip check.

SELECT * FROM (VALUES MAP(ARRAY['k'], ARRAY[BIGINT '9007199254740993'])) t(m)
WHERE CAST(m AS map(varchar(1), double)) = MAP(ARRAY['k'], ARRAY[DOUBLE '9007199254740992']);
-- expected: 1 row; actual: 0 rows

Maps are worse than arrays here: MapType does not override isOrderable(), so the targetType.isOrderable() branch that performs the literal round-trip is skipped entirely and control falls to the unconditional rewrite at the end of tryUnwrapCast.

4. PlanNodeDecorrelator loses the at-most-one-row guarantee. isSimpleInjectiveCast accepts any Cast(Reference) whose types satisfy isInjectiveCoercion, with no restriction on type shape. With u.c of array(bigint) holding both ARRAY[9007199254740992] and ARRAY[9007199254740993], a correlated scalar subquery keyed on CAST(u.c AS array(double)) equal to the correlation value adds u.c to constantSymbols, so the decorrelated plan assumes at most one matching row per correlation value and returns a row instead of raising TOO_MANY_ROWS.

isTypeOnlyCoercion already shows the shape of the fix

TypeCoercion.isTypeOnlyCoercion (TypeCoercion.java:73-138) derives a cast property from types and gets the nesting right: it gates on canCoerce, then recurses — an explicit RowType branch over fields (line 103) and a covariant-parametrized branch over type parameters (line 119). Its immediate neighbour isInjectiveCoercion (line 129) does not recurse at all. Whatever form the fix takes, the correct pattern is already in the same class.

Suggested direction

1. Make the property predicates structural. For a covariant container, require the property at every element / key / value / field position, mirroring isTypeOnlyCoercion. Injectivity and order preservation both compose through element-wise and lexicographic comparison, so the recursion is sound — the char/varchar failure is precisely an element-level order-preservation failure, and recursing catches it.
2. The hard part is that isInjectiveOrderPreservingCastAtValue is value-dependent (the bigint -> double and DST checks need the value, not just the type). Recursing means descending into the constant's block elements alongside the types. A conservative stopgap that fixes every case above: bail out whenever the source or target is a parametrized container, which loses only rewrites that are currently unsound.
3. Consolidate the duplicates. UnwrapCastInComparison.isInjectiveOrderPreservingCastAtValue and TypeCoercion.isInjectiveCoercion are two copies of one predicate that have already drifted — the latter's comment points at UnwrapCastInComparison.Visitor.hasInjectiveImplicitCoercion(), which no longer exists, and it never grew the char/varchar case. DomainTranslator.isOrderPreserving carries a TODO pointing at UnwrapCastInComparison for the same reason.

Not affected

Uses of canCoerce for what it actually means — analysis-time assignability — are correct and out of scope: ExpressionAnalyzer, StatementAnalyzer, SignatureBinder, SqlRoutineAnalyzer, ConstantEvaluator, and the gate inside isTypeOnlyCoercion.