Repository navigation
[Bug] floor/ceil/round(DOUBLE, d) return different results for constant and column arguments #68717
Description
Search before asking
- I had searched in the issues and found no similar issues.
Version
4.1.4 (doris-4.1.4-rc04-ad35a140c7f), single FE + single BE
What's Wrong?
floor, ceil and round with a DOUBLE argument and a scale give different results when the argument is a constant (folded by FE) and when it comes from a column (computed by BE).
FE folding gives 0.29 / -0.29 / 1.01; BE gives 0.28 / -0.28 / 1. With debug_skip_fold_constant = true the constants also give the BE results.
SET enable_sql_cache = false
SELECT floor(CAST(0.29 AS DOUBLE), 2) AS fe_floor, floor(a, 2) AS be_floor,
ceil(CAST(-0.29 AS DOUBLE), 2) AS fe_ceil, ceil(-a, 2) AS be_ceil,
round(CAST(1.005 AS DOUBLE), 2) AS fe_round, round(b, 2) AS be_round FROM t
+----------+----------+---------+---------+----------+----------+
| fe_floor | be_floor | fe_ceil | be_ceil | fe_round | be_round |
+----------+----------+---------+---------+----------+----------+
| 0.29 | 0.28 | -0.29 | -0.28 | 1.01 | 1 |
+----------+----------+---------+---------+----------+----------+
SET debug_skip_fold_constant = true
SELECT floor(CAST(0.29 AS DOUBLE), 2), ceil(CAST(-0.29 AS DOUBLE), 2), round(CAST(1.005 AS DOUBLE), 2)
+--------------------------------+--------------------------------+---------------------------------+
| floor(CAST(0.29 AS DOUBLE), 2) | ceil(CAST(-0.29 AS DOUBLE), 2) | round(CAST(1.005 AS DOUBLE), 2) |
+--------------------------------+--------------------------------+---------------------------------+
| 0.28 | -0.28 | 1 |
+--------------------------------+--------------------------------+---------------------------------+
- FE constant folding: all arguments are literals, so Nereids folds the call during planning. Check: EXPLAIN shows the literal in place of the call:
final projections: 0.29, floor(a[#1], 2) - BE execution: the argument is a column (or
SET debug_skip_fold_constant = true), so BE computes it. Check: EXPLAIN keeps the call, e.g.floor(a[#1], 2).
What You Expected?
A constant argument and a column holding the same value give the same result.
How to Reproduce?
Deployment: single FE + single BE, default session variables.
CREATE TABLE t(id INT, a DOUBLE, b DOUBLE)
DISTRIBUTED BY HASH(id) BUCKETS 1 PROPERTIES('replication_num' = '1');
INSERT INTO t VALUES (1, 0.29, 1.005);
SET enable_sql_cache = false;
SELECT floor(CAST(0.29 AS DOUBLE), 2) AS fe_floor, floor(a, 2) AS be_floor,
ceil(CAST(-0.29 AS DOUBLE), 2) AS fe_ceil, ceil(-a, 2) AS be_ceil,
round(CAST(1.005 AS DOUBLE), 2) AS fe_round, round(b, 2) AS be_round FROM t;
SET debug_skip_fold_constant = true;
SELECT floor(CAST(0.29 AS DOUBLE), 2), ceil(CAST(-0.29 AS DOUBLE), 2), round(CAST(1.005 AS DOUBLE), 2);Anything Else?
FE converts the double to its shortest decimal string and rounds in decimal; BE multiplies by 10^d, rounds the binary value and divides back (0.29 * 100 = 28.999999999999996, 1.005 * 100 = 100.49999999999999).
- FE: fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/NumericArithmetic.java#L377 —
round(DoubleLiteral, IntegerLiteral)vianew BigDecimal(Double.toString(x));ceilat L414 andfloorat L451 do the same - BE: be/src/exprs/function/round.h#L294 —
FloatRoundingComputation::compute: multiply, round, divide on the binary value
Found with AI assistance.
Are you willing to submit PR?
- Yes I am willing to submit a PR!
Code of Conduct
- I agree to follow this project's Code of Conduct
Activity
Doris-Breakwater commentedon Oct 4, 2026
Breakwater-GitHub-Analysis-Slot: slot_4a272fe48044
Initial assessment: This is a reproducible constant-folding/execution parity issue for the
DOUBLEoverloads offloor,ceil, androundwith a scale. The supplied SQL, build hash, anddebug_skip_fold_constantcomparison are sufficient for a focused investigation; no profile or additional logs are needed for this specific result mismatch. The issue had no labels at intake.Verified from the
4.1.4source (tagad35a140c7f): Nereids' FE executable methods convert aDoubleLiteralviaDouble.toString()intoBigDecimal, then apply decimalsetScalethrough DecimalV3Literal (FLOOR,CEILING, orHALF_UP). The BE dispatcher sendsDOUBLEwith positive scale toFloatRoundingComputation, which multiplies by10^d, applies the floating-point rounding function, then divides.roundis registered with the ordinary, non-bankers tie mode.That difference explains all three reported examples: binary
0.29 * 100is28.999999999999996, so BEflooryields0.28; binary-0.29 * 100is-28.999999999999996, so BEceilyields-0.28; binary1.005 * 100is100.49999999999999, so BEroundyields1.0. FE instead rounds the shortest decimal strings"0.29","-0.29", and"1.005", yielding0.29,-0.29, and1.01. These arithmetic values were checked independently; the Doris query results themselves are as reported in the issue, not from a local running cluster.Maintainer next steps: Treat constant-versus-column equality as the regression criterion. Decide which
DOUBLErounding semantics are intended, then make FE folding and BE execution agree for those overloads. A narrow temporary option is to prevent FE folding of the affectedDOUBLEoverloads until equivalent semantics are implemented; the globaldebug_skip_fold_constantsetting is useful for diagnosis but is broad. Add regression cases comparing literal and column inputs for all three functions, including the supplied values, negative inputs, positive/zero/negative scales, and boundary/tie values. Check other floating-point overloads if the implementation change is shared. For applications requiring exact decimal rounding, useDECIMALvalues from ingestion onward rather than relying onDOUBLE.Open decision, not missing incident evidence: Which result should be the public contract for
DOUBLEinputs? The source establishes why the paths differ, but does not establish whether decimal-string or binary floating-point behavior is intended. The exact 4.1.4 runtime outputs and plans in the report are sufficient to start a fix; an attachedEXPLAINtext would only be useful if a maintainer cannot reproduce the stated folding behavior.