trino
trino copied to clipboard
Improve DECIMAL divide precision/scale inference
Description
Currently, Trino doesn't expand scale when dividing decimals. This could lead to unexpected results, e.g. 0.1 / 100.0 => 0
.
This PR proposes decimal division type inference which is similar to MS SQL Server, see https://learn.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql?view=sql-server-ver16#remarks:
- At first we attempt to extend both scale and precision to fully contain the resulting value
- If resulting precision is greater than 38, we try to shrink the scale to 6.
- If the resulting precision is still greater than 38, we then shrink the integral part.
Since p.2 and p.3 require some logic, I had to expand the type parser to support several additional helper functions.
The effect of the new approach is clearly visible in TPC-H/TPC-DS product tests: they are definitively better than before.
Release notes
( ) This is not user-visible or is docs only, and no release notes are required. ( ) Release notes are required. Please propose a release note for me. (x) Release notes are required, with the following suggested text:
# Section
* Improve DECIMAL type division scale inference ({issue}`issuenumber`)
This pull request has gone a while without any activity. Tagging the Trino developer relations team: @bitsondatadev @colebow @mosabua
@bitsondatadev @mosabua @colebow any update on this?, simple division is giving 0
@martint will have to assess