trino icon indicating copy to clipboard operation
trino copied to clipboard

Improve DECIMAL divide precision/scale inference

Open devozerov opened this issue 10 months ago • 2 comments

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:

  1. At first we attempt to extend both scale and precision to fully contain the resulting value
  2. If resulting precision is greater than 38, we try to shrink the scale to 6.
  3. 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`)

devozerov avatar Apr 07 '24 18:04 devozerov

This pull request has gone a while without any activity. Tagging the Trino developer relations team: @bitsondatadev @colebow @mosabua

github-actions[bot] avatar May 10 '24 17:05 github-actions[bot]

@bitsondatadev @mosabua @colebow any update on this?, simple division is giving 0

mickyarun avatar May 20 '24 14:05 mickyarun

@martint will have to assess

mosabua avatar May 29 '24 21:05 mosabua