xuzifu666 commented on code in PR #5095: URL: https://github.com/apache/calcite/pull/5095#discussion_r3584008948
########## core/src/test/resources/sql/agg.iq: ########## @@ -3844,6 +3844,52 @@ select distinct sum(deptno + '1') as deptsum from dept order by 1; !ok +# [CALCITE-6767] PERCENTILE_CONT/PERCENTILE_DISC syntax not supported. +# These sql programs were validated in PostgreSQL +select + percentile_cont(0.5) within group (order by empno) as c, + percentile_disc(0.5) within group (order by empno) as d +from emp; ++------+------+ +| C | D | ++------+------+ +| 7785 | 7782 | ++------+------+ +(1 row) + +!ok + +# PERCENTILE_CONT / PERCENTILE_DISC with GROUP BY. Review Comment: I had added related tests and validate in https://onecompiler.com/oracle/44v8xy4ft Judging by the results: -PostgreSQL, `percentile_cont` returns only `double precision` (53-bit mantissa, approximately 15–16 decimal digits); converting a 19-digit `DECIMAL` value to this type results in a loss of precision—for instance, `9999999999999999991` becomes `1e+19`. -Calcite (after modification): Uses `BigDecimal` throughout; the return type matches the `ORDER BY` column (i.e., `DECIMAL(19,0)`), fully preserving the 19-digit precision to yield the exact value `9999999999999999991.0`. The SQL standard may allow `percentile_cont` to return a `double` for exact numeric types, and PostgreSQL is one implementation that follows this standard (I also tested Oracle as well, and it behaves the same way). Of course, having Calcite preserve the input type's precision would be more user-friendly for those using `DECIMAL`—which is the approach currently adopted. Few databases support the UNSIGNED type, so I chose DuckDB for testing; the results returned matched the expected test outcomes.（can be validated in https://shell.duckdb.org/）： using: ``` select percentile_cont(0.5) within group (order by v) as c, percentile_disc(0.5) within group (order by v) as d from (values (cast(10 as uinteger)), (cast(20 as uinteger))) as t(v); ``` result is : ``` ┌────────┬────────┐ │ c │ d │ │ double │ uint32 │ ├────────┼────────┤ │ 15.0 │ 10 │ └────────┴────────┘ ``` -- 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]
