Repository: snowflakedb/gosnowflake
Pull request #1079 (2024-03-22 21:11:23)

ArrowBatch high precision fails when using compute divide from int64 to bigDecimal
### Description

SNOW-XXX Please explain the changes you made here.
Currently, if you run the query `select '0.1':: DECIMAL(38, 19) as c` with arrow batching turned on, the result will error with `invalid: integer value 1000000000000000000 not in range: -9007199254740992 to 9007199254740992`. The error is thrown by compute.Divide(), when receiving int64 value for a BigDecimal column.
The fix is to convert int64 array to arrow.Decimal array, and follow a different code path (line 943 - line 955) where compute.Divide() is not involved.

The error originates from arrow library: https://github.com/apache/arrow/issues/36998


### Checklist
- [ ] Created tests which fail without the change (if possible)
- [ ] Extended the README / documentation, if necessary


github-actions[bot]: **CLA Assistant Lite bot:** <br/>Thank you for your submission, we really appreciate it. Like many open-source projects, we ask that you all sign our [Contributor License Agreement](https://github.com/snowflakedb/CLA/blob/main/README.md) before we can accept your contribution. You can sign the CLA by just posting a Pull Request Comment same as the below format.<br/>
   - - -
   I have read the CLA Document and I hereby sign the CLA
   - - -
   **1** out of **2** committers have signed the CLA.<br/>:white_check_mark: (madisonchamberlain)[https://github.com/madisonchamberlain]<br/>:x: @Madison Chamberlain<br/>**Madison Chamberlain** seems not to be a GitHub user. You need a GitHub account to be able to sign the CLA. If you have already a GitHub account, please [add the email address used for this commit to your account](https://help.github.com/articles/why-are-my-commits-linked-to-the-wrong-user/#commits-are-not-linked-to-any-user).<br/><sub>You can retrigger this bot by commenting **recheck** in this Pull Request</sub>
madisonchamberlain: I have read the CLA Document and I hereby sign the CLA
madisonchamberlain: recheck
madisonchamberlain: recheck
Yifeng-Sigma: https://github.com/snowflakedb/gosnowflake/issues/878

Patch:
diff --git a/converter.go b/converter.go
index bc823bd..3042e29 100644
--- a/converter.go
+++ b/converter.go
@@ -1039,7 +1039,7 @@ func arrowToRecord(ctx context.Context, record arrow.Record, pool memory.Allocat
 					return nil, err
 				}
 				defer newCol.Release()
-			} else if srcColumnMeta.Scale != 0 {
+			} else if srcColumnMeta.Scale != 0 && col.DataType().ID() != arrow.INT64 {
 				result, err := compute.Divide(ctxAlloc, compute.ArithmeticOptions{NoCheckOverflow: true},
 					&compute.ArrayDatum{Value: newCol.Data()},
 					compute.NewDatum(math.Pow10(int(srcColumnMeta.Scale))))
@@ -1049,6 +1049,20 @@ func arrowToRecord(ctx context.Context, record arrow.Record, pool memory.Allocat
 				defer result.Release()
 				newCol = result.(*compute.ArrayDatum).MakeArray()
 				defer newCol.Release()
+			} else if srcColumnMeta.Scale != 0 && col.DataType().ID() == arrow.INT64 {
+				// gosnowflake driver uses compute.Divide() which could bring `integer value not in range: -9007199254740992 to 9007199254740992` error
+				// if we convert int64 to BigDecimal and then use compute.CastArray to convert BigDecimal to float64, we won't have enough precision.
+				// e.g 0.1 as (38,19) will result 0.09999999999999999
+				values := col.(*array.Int64).Int64Values()
+				floatValues := make([]float64, len(values))
+				for i, val := range values {
+					floatValues[i], _ = intToBigFloat(val, srcColumnMeta.Scale).Float64()
+				}
+				builder := array.NewFloat64Builder(memory.NewCheckedAllocator(memory.NewGoAllocator()))
+				builder.AppendValues(floatValues, nil)
+				newCol = builder.NewArray()
+				builder.Release()
+				defer newCol.Release()
 			}
 		case timeType:
 			newCol, err = compute.CastArray(ctxAlloc, col, compute.SafeCastOptions(arrow.FixedWidthTypes.Time64ns))


Test patch:
diff --git a/arrow_test.go b/arrow_test.go
index e4d103c..1cab04a 100644
--- a/arrow_test.go
+++ b/arrow_test.go
@@ -11,6 +11,8 @@ import (
 	"strings"
 	"testing"
 	"time"
+
+	"database/sql/driver"
 )
 
 // A test just to show Snowflake version
@@ -34,6 +36,45 @@ func TestCheckVersion(t *testing.T) {
 	println(s)
 }
 
+func TestArrowBatchHighPrecision(t *testing.T) {
+	runDBTest(t, func(dbt *DBTest) {
+		ctx := WithArrowBatches(context.Background())
+		query := "select '0.1':: DECIMAL(38, 19) as c"
+
+		var rows driver.Rows
+		var err error
+
+		// must use conn.Raw so we can get back driver rows (an interface)
+		// which can be cast to snowflakeRows which exposes GetArrowBatch
+		err = dbt.conn.Raw(func(x interface{}) error {
+			queryer, implementsQueryContext := x.(driver.QueryerContext)
+			assertTrueF(t, implementsQueryContext, "snowflake connection driver does not implement queryerContext")
+
+			rows, err = queryer.QueryContext(WithArrowBatches(ctx), query, nil)
+			return err
+		})
+
+		assertNilF(t, err, "error running select query")
+
+		sfRows, isSfRows := rows.(SnowflakeRows)
+		assertTrueF(t, isSfRows, "rows should be snowflakeRows")
+
+		arrowBatches, err := sfRows.GetArrowBatches()
+		assertNilF(t, err, "error getting arrow batches")
+		assertNotEqualF(t, len(arrowBatches), 0, "should have at least one batch")
+
+		c, err := arrowBatches[0].Fetch()
+		assertNilF(t, err, "error fetching first batch")
+
+		chunk := *c
+		assertNotEqualF(t, len(chunk), 0, "should have at least one chunk")
+
+		strVal := chunk[0].Column(0).ValueStr(0)
+		expected := "0.1"
+		assertEqualF(t, strVal, expected, fmt.Sprintf("should have returned 0.1, but got: %s", strVal))
+	})
+}
+
 func TestArrowBigInt(t *testing.T) {
 	conn := openConn(t)
 	defer conn.Close()
diff --git a/assert_test.go b/assert_test.go
index d454965..6bf28c4 100644
--- a/assert_test.go
+++ b/assert_test.go
@@ -35,6 +35,10 @@ func assertEqualF(t *testing.T, actual any, expected any, descriptions ...string
 	fatalOnNonEmpty(t, validateEqual(actual, expected, descriptions...))
 }
 
+func assertNotEqualF(t *testing.T, actual any, expected any, descriptions ...string) {
+	fatalOnNonEmpty(t, validateNotEqual(actual, expected, descriptions...))
+}
+
 func assertBytesEqualE(t *testing.T, actual []byte, expected []byte, descriptions ...string) {
 	errorOnNonEmpty(t, validateBytesEqual(actual, expected, descriptions...))
 }
@@ -111,6 +115,14 @@ func validateEqual(actual any, expected any, descriptions ...string) string {
 	return fmt.Sprintf("expected \"%s\" to be equal to \"%s\" but was not. %s", actual, expected, desc)
 }
 
+func validateNotEqual(actual any, expected any, descriptions ...string) string {
+	if expected != actual {
+		return ""
+	}
+	desc := joinDescriptions(descriptions...)
+	return fmt.Sprintf("expected \"%s\" not to be equal to \"%s\" but they were the same. %s", actual, expected, desc)
+}
+
 func validateBytesEqual(actual []byte, expected []byte, descriptions ...string) string {
 	if bytes.Equal(actual, expected) {
 		return ""