Map DECIMAL(p, 0) to a type that holds all its values in the multi-stage engine - #19790

PR flow

Maps DECIMAL(p,0) to INT for p≤9, LONG for p≤18, BIG_DECIMAL otherwise in logical and physical plan converters.

flowchart TD
  N0["LogicalResolver#46;resolveDecimal #40;F1#41;"]:::stModified
  N1["Logical#95;INT#95;branch #40;p#8804;9#41; #40;F1#41;"]:::stModified
  N2["Logical#95;LONG#95;branch #40;p#8804;18#41; #40;F1#41;"]:::stModified
  N3["Logical#95;BIG#95;DECIMAL#95;branch #40;else#41; #40;F1#41;"]:::stModified
  N4["PhysicalResolver#46;resolveDecimal #40;F2#41;"]:::stModified
  N5["Physical#95;INT#95;branch #40;p#8804;9#41; #40;F2#41;"]:::stModified
  N6["Physical#95;LONG#95;branch #40;p#8804;18#41; #40;F2#41;"]:::stModified
  N7["Physical#95;BIG#95;DECIMAL#95;branch #40;else#41; #40;F2#41;"]:::stModified
  N0 -->|"if precision #60;#61; 9"| N1
  N0 -->|"else if precision #60;#61; 18"| N2
  N0 -->|"else"| N3
  N4 -->|"if precision #60;#61; 9"| N5
  N4 -->|"else if precision #60;#61; 18"| N6
  N4 -->|"else"| N7
  classDef stAdded fill:#dafbe1,stroke:#1a7f37,color:#1f2328,stroke-width:2px
  classDef stModified fill:#fff8c5,stroke:#9a6700,color:#1f2328,stroke-width:2px
  classDef stRemoved fill:#ffebe9,stroke:#cf222e,color:#1f2328,stroke-width:2px
  classDef stUnchanged fill:#f6f8fa,stroke:#656d76,color:#1f2328,stroke-width:1px

AI-generated · Green: added · Yellow: modified · Red: removed · Gray: existing

• Regenerate PR flow

Problem

The multi-stage engine maps DECIMAL(p, 0) to INT when p <= 10 and to LONG when p <= 38. INT cannot hold every 10-digit number, and LONG cannot hold every number with 19 to 38 digits. RexExpressionUtils.fromRexLiteralValue then calls intValue() / longValue(), which wrap the value without an error.

Calcite gives the type DECIMAL(p, 0) to integer literals beyond the BIGINT range and to CAST(... AS DECIMAL(p, 0)). Results on master, with both planners:

Fix

Map DECIMAL(p, 0) to INT only when p <= 9, to LONG only when p <= 18, and to BIG_DECIMAL otherwise. The change is in RelToPlanNodeConverter and in its copy for the physical optimizer, PRelToPlanNodeConverter.

Compatibility

• Result types change for some queries. DECIMAL(10, 0) is now LONG (was INT). DECIMAL(19..38, 0) is now BIG_DECIMAL (was LONG), so the broker returns it as a string, like other BIG_DECIMAL values. SUM, MIN, MAX and AVG over these values change the same way. A plain CAST(x AS DECIMAL) is DECIMAL(2000, 1000), so it does not change.
• Arrays of DECIMAL(19..38, 0) are now DOUBLE_ARRAY (was LONG_ARRAY). This is the same as arrays with p > 38 today (see the BIG_DECIMAL_ARRAY TODO).
• Mixed versions: the broker sets all plan types. Servers already accept BIG_DECIMAL literals in plans ([Multi-stage] Fix literal handling #13344), so no new type goes over the wire.

Not in this PR

• SUM, MAX and MOD compute in double, so values above 2^53 lose precision. This also happens on master for plain LONG columns.
• The single-stage engine has the same wrap in RequestUtils.getLiteral.

Tests

• NumericTypes.json: 16 queries compared with H2, in the leaf stage, in intermediate stages and during planning. 14 fail on master with both planners. The other 2 make sure that a BIGINT promoted to DECIMAL(19, 0) keeps its exact value.
• Unit tests for the new limits in RelToPlanNodeConverterTest and PRelToPlanNodeConverterTest.