Adjust precision and scale in Numeric Multiplication and Division

[Yvinayak07]

1. Issues

In Babelfish's implementation of numeric operations (multiplication and division), logic for adjustment of precision and scale (if precision is greater then NUMERIC_MAX_PRECISION (38)) is incorrect as compared to TSQL.

-- Babelfish 
1> select cast(1.289473 as numeric(38, 6)) * 100
2> go
                                       
----------------------------------------
                                  128.95
 
(1 rows affected)
 
-- Expected behaviour (TSQL) 
1> select cast(1.289473 as numeric(38, 6)) * 100
2> go
                                       
----------------------------------------
                              128.947300
 
(1 rows affected)

-- bbf 
1> DECLARE @o NUMERIC(38,0) = 9999999999999999999999999999999999999; DECLARE @p NUMERIC(38,0)= 3; SELECT @o / @p AS result; 
3> go 
result 
Sqlcmd: Error: Internal error at ReadAndHandleColumnData (Reason: Error reading column data). SqlState HY000, Protocol error in TDS stream
 
-- TSQL 
1> DECLARE @o NUMERIC(38,0) = 9999999999999999999999999999999999999; DECLARE @p NUMERIC(38,0)= 3; SELECT @o / @p AS result; 
3> go 
Msg 8115, Level 16, State 2, Server EC2AMAZ-T4MF7PS, Line 1 Arithmetic overflow error converting expression to data type numeric.

2. Changes made to fix the issues

• The numeric operations (multiplication and division) logic for precision and scale adjustment needs to be corrected. To prevent TDS protocol violations, errors should be thrown in the execution stage (before setting metadata) instead of the TDS stage.
• tsql_numeric_get_typmod doesn’t handle edge cases wisely, fixed the API to give correct typmod for a numeric value.
• Could not calculate typmod from T_var node in resolve_numeric_typmod_from_exp unable to calculate typmod for fixed length datatypes and UDT's. added handling to calculate typmod from fixed length datatypes and UDT's from T_Var node.

-- BBF
1> create table testTbl(size int) insert into testTbl values (707072) 
2> go 
(1 rows affected) 
1> select size * 8.00 from testTbl 
2> go 
--------- 
Sqlcmd: Error: Internal error at ReadAndHandleColumnData (Reason: Error reading column data). SqlState HY000, Protocol error in TDS stream

-- TSQL
1> select size * 8.00 from testTbl
2> go
                
----------------
      5656576.00

(1 rows affected)

• Failed in parallel query mode as worker node couldn’t load pltsql_protocol_plugin_ptr , So moved resolve_numeric_typmod_from_exp API from TDS side to TSQL.

Issues Resolved

Task: BABEL-5689 Signed-off-by: yashneet vinayak [email protected]

Test Scenarios Covered

• Use case based -

• Boundary conditions -

• Arbitrary inputs -

• Negative test cases -

• Minor version upgrade tests -

• Major version upgrade tests -

• Performance tests -

• Tooling impact -

• Client tests -

Check List

• [x] Commits are signed per the DCO using --signoff

By submitting this pull request, I confirm that my contribution is under the terms of the Apache 2.0 and PostgreSQL licenses, and grant any person obtaining a copy of the contribution permission to relicense all or a portion of my contribution to the PostgreSQL License solely to contribute all or a portion of my contribution to the PostgreSQL open source project.

For more information on following Developer Certificate of Origin and signing off your commits, please check here.

[coveralls]

Pull Request Test Coverage Report for Build 14930235873

Warning: This coverage report may be inaccurate.

This pull request's base commit is no longer the HEAD commit of its target branch. This means it includes changes from outside the original pull request, including, potentially, unrelated coverage changes.

• For more information on this, see Tracking coverage changes with pull request builds.
• To avoid this issue with future PRs, see these Recommended CI Configurations.
• For a quick fix, rebase this PR at GitHub. Your next report should be accurate.

Details

• 379 of 410 (92.44%) changed or added relevant lines in 8 files are covered.
• 11 unchanged lines in 1 file lost coverage.
• Overall coverage increased (+0.03%) to 75.17%

---

Changes Missing Coverage	Covered Lines	Changed/Added Lines	%
contrib/babelfishpg_tsql/src/pltsql_coerce.c	348	379	91.82%
<!--	Total:	379	410

Files with Coverage Reduction	New Missed Lines	%
contrib/babelfishpg_tsql/src/fts_parser.y	11	93.29%
<!--	Total:	11

Totals
Change from base Build 14657622694:	0.03%
Covered Lines:	47994
Relevant Lines:	63847

---

💛 - Coveralls

[Yvinayak07]

As discussed offline, we also need to fix the overflow conditions for addition and subtraction. As per: https://github.com/MicrosoftDocs/sql-docs/blob/live/docs/t-sql/data-types/precision-scale-and-length-transact-sql.md

In addition and subtraction operations, we need max(p1 - s1, p2 - s2) places to store the integral part of the decimal number. If there isn't enough space to store them (that is, max(p1 - s1, p2 - s2) < min(38, precision) - scale), the scale is reduced to provide enough space for the integral part. The resulting scale is min(precision, 38) - max(p1 - s1, p2 - s2), so the fractional part might be rounded to fit into the resulting scale.

We already have handling for this the addition error is thrown from numeric_sum.

[Yvinayak07]

Please add comment why we have added changes in both upgrade scripts for clarity of reviewers

Added comments in description.