Skip to content

isthmus: CEIL, FLOOR and ROUND over a decimal emit an output type their declaration does not derive #1349

Open

Description

@nielspardon

SqlToSubstrait maps Calcite's CEIL, FLOOR and ROUND over a decimal to ceil:dec, floor:dec and round:dec_i32 in functions_rounding_decimal.yaml, but writes the output type Calcite infers, not the one the declaration derives. For ROUND the emitted type is narrower than declared, so a value that rounds up to the next magnitude does not fit it: ROUND(99999.99, 0) is 100000.00, which needs decimal(8,2), but the plan says decimal(7,2).

CREATE TABLE t (d DECIMAL(7, 2) NOT NULL, z DECIMAL(7, 0) NOT NULL);
SELECT CEIL(d), FLOOR(z), ROUND(d, 1) FROM t;
SQL function emitted declared
CEIL(d), FLOOR(d) ceil:dec, floor:dec decimal<7,0> decimal<6,0>
CEIL(z), FLOOR(z) ceil:dec, floor:dec decimal<7,0> decimal<8,0>
ROUND(d, 1), ROUND(d, 0), ROUND(d, -1) round:dec_i32 decimal<7,2> decimal?<8,2>
ROUND(z, 0) round:dec_i32 decimal<7,0> decimal?<8,0>

"Declared" is resolveType over the arguments the plan actually emits. CEIL/FLOOR declare decimal<min(P - S + 1, 38), 0>, and round declares decimal?<min(P + 1, 38), S> with DECLARED_OUTPUT, so its result is nullable even for a required argument.

Measured on main at bac6b43 (substrait-packaging 0.102.0, Calcite 1.42.0).

Unlike the decimal arithmetic in #1347, SubstraitTypeSystem cannot fix this. Calcite fixes these return types on the operators themselves (SqlFloorFunction uses ReturnTypes.ARG0_OR_EXACT_NO_SCALE, SqlStdOperatorTable.ROUND uses ReturnTypes.ARG0_NULLABLE), and no RelDataTypeSystem hook is consulted. Fixing it means isthmus-local CEIL/FLOOR/ROUND operators that derive the declared types. round:dec_i32 already shows up in the TPC-H/TPC-DS sweep in #1117 (as "mostly nullability"); ceil/floor do not, because no TPC query uses them over a decimal.

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions