Wrong precision for BigDecimal < 0 (since #2051)

[thomasheiser85]

Driver version

12.2.0

SQL Server version

Microsoft SQL Server 2019 (RTM-GDR) (KB5021125) - 15.0.2101.7 (X64) Jan 23 2023 13:08:05 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise LTSC 2019 10.0 (Build 17763: ) (Hypervisor)

Client Operating System

Windows 10

JAVA/JVM version

1.8.0

Table schema

n/a

Problem description

The precision for BigDecimals < 0 is always 1 higher than it should.

For example a BigDecimal less than 0 with a precision of 38: 0.98432319763138435186412316842316874322
results: decimal(39,38)

This caused an error:

SQL Error: 2717, SQLState: S0001
Die Größe (39) von "decimal" (Typ) überschreitet den zulässigen maximalen Wert (38)

But the value fits to a decimal(38,38). Tested with the following sql:

DECLARE @numberVal decimal(38,38);
SET @numberVal = 0.98432319763138435186412316842316874322;

On the other side a BigDecimal biger than 1 and a precision of 38: 1.9843231976313843518641231684231687432
results: decimal(38,37)

So the problem only occurs for BigDecimals < 0.

#####################

Quick test class:

import java.math.BigDecimal;
import java.math.MathContext;
import java.math.RoundingMode;

class Scratch
{

  public static BigDecimal roundIfNeeded( final BigDecimal toRound )
  {
    BigDecimal rounded = toRound;

    if ( rounded != null )
    {
      if ( rounded.precision()  38 )
      {
        rounded = rounded.round( new MathContext( 38, RoundingMode.HALF_UP ) );
      }

      if ( rounded.scale()  38 )
      {
        rounded = rounded.setScale( 38, RoundingMode.HALF_UP );
      }
    }

    return rounded;
  }


  public static void main( String[] args )
  {

    BigDecimal number = new BigDecimal( 0.984323197631384351864123168423168743216874321684132168451946546357451684134654654561465 );
    System.out.println(number.toPlainString() +  scale + number.scale() + prec+ number.precision());
    number=roundIfNeeded( number );
    System.out.println(number.toPlainString() +  scale + number.scale() + prec+ number.precision());


     [START] Copied snipped from httpsgithub.commicrosoftmssql-jdbcpull2051files
    String[] plainValueArray = number.abs().toPlainString().split(.);
    System.out.println(number.abs().toPlainString());
    String sqlTypeDef = decimal + ( +
         Precision
        (plainValueArray.length == 2  plainValueArray[0].length()
            + plainValueArray[1].length()  plainValueArray[0].length())
        + , +
         Scale
        (plainValueArray.length == 2  plainValueArray[1].length()  0) + );
     [END] Copied snipped from httpsgithub.commicrosoftmssql-jdbcpull2051files
    System.out.println(sqlTypeDef);

    System.out.println(++++++++++++++++++++++++++++);


    number = new BigDecimal( 1.984323197631384351864123168423168743216874321684132168451946546357451684134654654561465 );
    System.out.println(number.toPlainString() +  scale + number.scale() + prec+ number.precision());
    number=roundIfNeeded( number );
    System.out.println(number.toPlainString() +  scale + number.scale() + prec+ number.precision());

     [START] Copied snipped from httpsgithub.commicrosoftmssql-jdbcpull2051files
    plainValueArray = number.abs().toPlainString().split(.);
    System.out.println(number.abs().toPlainString());
    sqlTypeDef = decimal + ( +
         Precision
        (plainValueArray.length == 2  plainValueArray[0].length()
            + plainValueArray[1].length()  plainValueArray[0].length())
        + , +
         Scale
        (plainValueArray.length == 2  plainValueArray[1].length()  0) + );
     [END] Copied snipped from httpsgithub.commicrosoftmssql-jdbcpull2051files

    System.out.println(sqlTypeDef);

  }
}

Output:

0.984323197631384351864123168423168743216874321684132168451946546357451684134654654561465| scale:87| prec:87
0.98432319763138435186412316842316874322| scale:38| prec:38
0.98432319763138435186412316842316874322
decimal(39,38)
++++++++++++++++++++++++++++
1.984323197631384351864123168423168743216874321684132168451946546357451684134654654561465| scale:87| prec:88
1.9843231976313843518641231684231687432| scale:37| prec:38
1.9843231976313843518641231684231687432
decimal(38,37)

[Jeffery-Wasty]

Hi @thomasheiser85,

Looks like you're right. When converting between bigDecimal, array, and string, it looks like we had an assumption for different behavior for digits between 0 and 1, and those greater than 1. Thank you, this will be fixed for our next release.

[Jeffery-Wasty]

This was reverted in 12.4 due to performance impacts and reintroduced in the 12.5 preview release as a connection string option, calcBigDecimalScale (default false). The first stable release to include this fix will be 12.6, planned for January 31, 2024.