r33s3n6 opened a new issue, #68717: URL: https://github.com/apache/doris/issues/68717
### Search before asking - [x] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) 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. ```sql 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](https://github.com/apache/doris/blob/4.1.4/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/NumericArithmetic.java#L377) — `round(DoubleLiteral, IntegerLiteral)` via `new BigDecimal(Double.toString(x))`; `ceil` at L414 and `floor` at L451 do the same - BE: [be/src/exprs/function/round.h#L294](https://github.com/apache/doris/blob/4.1.4/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 - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
