Feature Request: Decimal Truncation and Remainder Functions #16560

williamkaper · 2025-03-08T02:42:46Z

williamkaper
Mar 8, 2025

We do a lot of accounting calculations that require high precision. Numeric/decimal types wind up rounding when you try to cast to a specific precision instead of truncating. There aren't any ways to do high precision truncating/remaindering on decimals, so we created a set of Macros to do it for us, but it would be faster if this logic (or even more performant logic) was in C as a supported native function for DuckDB.

MACROS (the first is a helper to convert e-notation to its high precision normalized string equivalent in base 10 format)

CREATE OR REPLACE MACRO enotation_safe(arg1) AS (
  SELECT
    CASE
      WHEN arg_val IS NULL THEN NULL::text
      WHEN arg_val::text NOT ILIKE '%e%' THEN arg_val::text
      WHEN arg_val::text ILIKE '%e-%'
        THEN
          CASE WHEN arg_val < 0 THEN '-' ELSE '' END
          || '0.' ||
          LPAD('0', ((
            STRING_SPLIT(abs(arg_val)::text, 'e-')[2]
          ))::INT-1, '0')
          || REPLACE((STRING_SPLIT(abs(arg_val)::text, 'e-')[1]), '.', '')
      ELSE
        LEFT(REPLACE(STRING_SPLIT(arg_val::text, 'e')[1], '.', '') || LPAD('', ((STRING_SPLIT(arg_val::text, 'e')[2]))::INT, '0'), ((STRING_SPLIT(arg_val::text, 'e')[2]))::INT + 1)
        || '.' ||
        (REPLACE(STRING_SPLIT(arg_val::text, 'e')[1], '.', '') || LPAD('', ((STRING_SPLIT(arg_val::text, 'e')[2]))::INT, '0'))[((STRING_SPLIT(arg_val::text, 'e')[2]))::INT+2:100000]
        || '0'
    END
  FROM VALUES (arg1) AS res(arg_val)
);

CREATE OR REPLACE MACRO trunc_prec(arg1, arg2) AS (
  SELECT COALESCE((STRING_SPLIT(enotation_safe(arg_val), '.')[1] || '.' || RPAD(STRING_SPLIT(enotation_safe(arg_val), '.')[2], arg_prec::INT, '0'))::DOUBLE, arg_val::DOUBLE)
  FROM VALUES (arg1, arg2) AS res(arg_val, arg_prec)
);

CREATE OR REPLACE MACRO trunc_rem(arg1, arg2) AS (
  SELECT COALESCE((CASE WHEN arg_val < 0 THEN '-' ELSE '' END || '0.' || LPAD('', arg_prec::int, '0') || LEFT(STRING_SPLIT(enotation_safe(abs(arg_val)), '.')[2], 19)[arg_prec+1:-1] || '0')::NUMERIC(21, 20), 0.0::NUMERIC(21, 20))
  FROM VALUES (arg1, arg2) AS res(arg_val, arg_prec)
);

These are tests we wrote in DBT for these Macros:

/* Tests to validate the trunc_prec MACRO */
SELECT calc.result
FROM (
  SELECT
    (
      -- CASE 1
      trunc_prec(25.5544999, 4) = 25.5544
      AND trunc_prec(25.5544999999999999999, 4) = 25.5544
      AND trunc_prec(25.55449999999999999999999999999999999, 4) = 25.5544

      -- CASE 2
      AND trunc_prec(60.8544999, 4) = 60.8544
      AND trunc_prec(60.8544999999999999999, 4) = 60.8544
      AND trunc_prec(60.85449999999999999999999999999999999, 4) = 60.8544

      -- CASE 3
      AND trunc_prec(3025.5544999, 4) = 3025.5544
      AND trunc_prec(3025.5544999999999999999, 4) = 3025.5544
      AND trunc_prec(3025.554499999999999999999999999999999, 4) = 3025.5544

      -- CASE 4
      AND trunc_prec(3060.8544999, 4) = 3060.8544
      AND trunc_prec(3060.8544999999999999999, 4) = 3060.8544
      AND trunc_prec(3060.854499999999999999999999999999999, 4) = 3060.8544

      -- CASE 5
      AND trunc_prec(25.5544999, 8) = 25.55449990
      AND trunc_prec(25.5544999999999999999, 8) = 25.55449999
      AND trunc_prec(25.55449999999999999999999999999999999, 8) = 25.55449999

      -- CASE 6
      AND trunc_prec(3060.8544999, 8) = 3060.85449990
      AND trunc_prec(3060.8544999999999999999, 8) = 3060.85449999
      AND trunc_prec(3060.854499999999999999999999999999999, 8) = 3060.85449999

      -- CASE 7
      AND trunc_prec(-25.5544999, 4) = -25.5544
      AND trunc_prec(-25.5544999999999999999, 4) = -25.5544
      AND trunc_prec(-25.55449999999999999999999999999999999, 4) = -25.5544

      -- CASE 8
      AND trunc_prec(-3060.8544999, 8) = -3060.85449990
      AND trunc_prec(-3060.8544999999999999999, 8) = -3060.85449999
      AND trunc_prec(-3060.854499999999999999999999999999999, 8) = -3060.85449999

      -- CASE (No decimal)
      AND trunc_prec(10, 0) = 10
      AND trunc_prec(10, 4) = 10
      AND trunc_prec(10, 8) = 10

      -- CASE 10 (Zero)
      AND trunc_prec(0.0, 0) = 0
      AND trunc_prec(0.0, 4) = 0
      AND trunc_prec(0.0, 8) = 0

      -- CASE 11 (E Notation Positive)
      AND trunc_prec(3.14159547e3, 3) = 3141.595
      AND trunc_prec(3.14159547e3, 4) = 3141.5954
      AND trunc_prec(3.14159547e10, 4) = 31415954700
      AND trunc_prec(3.14159547e20, 4) = 314159547000000000000

      -- CASE 13 (Negative E Notation Positive)
      AND trunc_prec(-3.14159547e3, 3) = -3141.595
      AND trunc_prec(-3.14159547e3, 4) = -3141.5954

      -- CASE 14 (E Notation negative)
      AND trunc_prec(3.14159547e-3, 3) = 0.003
      AND trunc_prec(3.14159547e-10, 12) = 0.000000000314
      AND trunc_prec(3.14159547e-30, 12) = 0

      -- CASE 15 (Negative E Notation negative)
      AND trunc_prec(-3.14159547e-3, 3) = -0.003
      AND trunc_prec(-3.14159547e-10, 12) = -0.000000000314
      AND trunc_prec(-3.14159547e-30, 12) = -0

      -- CASE 16 (E Notation positive)
      AND trunc_prec(3.14159547e10, 12) = 31415954700

      -- CASE 17 (Gamma failure during dev)
      AND trunc_prec((-0.000086968036)::double, 7) = -0.0000869

    ) AS result
) AS calc
WHERE NOT calc.result

/* Tests to validate the trunc_rem MACRO */
SELECT calc.result
FROM (
  SELECT
    (
      -- CASE 1
      trunc_rem(25.5544999, 4) = 0.00009990
      AND trunc_rem(25.5544999999999999999, 4) = 0.00009999999999999990
      AND trunc_rem(25.5544999999999999999999999999999999, 4) = 0.00009999999999999990

      -- CASE 2
      AND trunc_rem(3060.8544999, 8) = 0.00000000
      AND trunc_rem(3060.8544999999999999999, 8) = 0.00000000999999999990
      AND trunc_rem(3060.85449999999999999999999999999999, 8) = 0.00000000999999999990

      -- CASE 3
      AND trunc_rem(-25.5544999, 4) = -0.00009990
      AND trunc_rem(-25.5544999999999999999, 4) = -0.00009999999999999990
      AND trunc_rem(-25.5544999999999999999999999999999999, 4) = -0.00009999999999999990

      -- CASE 4
      AND trunc_rem(10, 0) = 0
      AND trunc_rem(10, 4) = 0
      AND trunc_rem(10, 8) = 0

      -- CASE 5
      AND trunc_rem(-10, 0) = 0
      AND trunc_rem(-10, 4) = 0
      AND trunc_rem(-10, 8) = 0

      -- CASE 6
      AND trunc_rem(0, 0) = 0
      AND trunc_rem(0, 4) = 0
      AND trunc_rem(0, 8) = 0

     -- CASE 7 (Gamma test Failure)
      AND trunc_rem((-0.000086968036)::double, 7) = -0.00000006803600

    ) AS result
) AS calc
WHERE NOT calc.result

soerenwolfers · 2025-03-08T17:37:59Z

soerenwolfers
Mar 8, 2025

Since DuckDB already has trunc(arg) this could be more succinctly phrased as: Please support trunc(arg, precision) to match PostgreSQL.

2 replies

williamkaper Mar 8, 2025
Author

Yes as long as it handles numeric arguments explicitly and doesn't try to convert it into and out of double to then truncate it.

If you try to use the existing trunc function today to do the trunc'ing by taking the decimal, multiplying it by 10^x, truncing that, and then dividing by 10^x , it won't yield clean results because the moving into and out of floating point causes issues.

You can see that in this example:

CREATE OR REPLACE MACRO trunc_prec_test(arg1, arg2) AS (
  SELECT trunc((arg1 * (10.0^arg2))) / (10.0^arg2)::DECIMAL(20, 1)
  FROM VALUES (arg1, arg2) AS res(arg_val, arg_prec)
);
SELECT trunc_prec_test(25.5544999999999999999, 4) = 25.5544;

soerenwolfers Mar 8, 2025

In fact, the two argument trunc only makes sense for decimal numbers to begin with, so unlike the one argument version, it should only be offered with a decimal first argument.
And yes, division always converts to double because division almost never preserves decimalness.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Feature Request: Decimal Truncation and Remainder Functions #16560

Uh oh!

{{title}}

Uh oh!

Replies: 1 comment 2 replies

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Select a reply

Uh oh!

Feature Request: Decimal Truncation and Remainder Functions #16560

Uh oh!

williamkaper Mar 8, 2025

Replies: 1 comment · 2 replies

Uh oh!

soerenwolfers Mar 8, 2025

Uh oh!

williamkaper Mar 8, 2025 Author

Uh oh!

soerenwolfers Mar 8, 2025

williamkaper
Mar 8, 2025

Replies: 1 comment 2 replies

soerenwolfers
Mar 8, 2025

williamkaper Mar 8, 2025
Author