Repository: snowflakedb/gosnowflake
Pull request #1179 (2024-07-12 07:06:25)

SNOW-1416000 Structure types array null handling
### Description

SNOW-1416000 Added new context option that makes values within arrays be parsed to sql.NullXYZ types, allowing for handling nulls in the data.

### Checklist
- [x] Created tests which fail without the change (if possible)
- [ ] Extended the README / documentation, if necessary


codecov[bot]: ## [Codecov](https://app.codecov.io/gh/snowflakedb/gosnowflake/pull/1179?dropdown=coverage&src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=snowflakedb) Report
Attention: Patch coverage is `52.23214%` with `107 lines` in your changes missing coverage. Please review.
> Project coverage is 82.53%. Comparing base [(`9730225`)](https://app.codecov.io/gh/snowflakedb/gosnowflake/commit/97302255dad3ea4c6b2f26b34bacb42b9f3ca84e?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=snowflakedb) to head [(`d0e6bad`)](https://app.codecov.io/gh/snowflakedb/gosnowflake/commit/d0e6badd2be1c6726e094c0bc9dc4c77b09a3d6f?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=snowflakedb).
> Report is 1 commits behind head on master.

| [Files](https://app.codecov.io/gh/snowflakedb/gosnowflake/pull/1179?dropdown=coverage&src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=snowflakedb) | Patch % | Lines |
|---|---|---|
| [converter.go](https://app.codecov.io/gh/snowflakedb/gosnowflake/pull/1179?src=pr&el=tree&filepath=converter.go&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=snowflakedb#diff-Y29udmVydGVyLmdv) | 52.68% | [97 Missing :warning: ](https://app.codecov.io/gh/snowflakedb/gosnowflake/pull/1179?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=snowflakedb) |
| [errors.go](https://app.codecov.io/gh/snowflakedb/gosnowflake/pull/1179?src=pr&el=tree&filepath=errors.go&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=snowflakedb#diff-ZXJyb3JzLmdv) | 0.00% | [10 Missing :warning: ](https://app.codecov.io/gh/snowflakedb/gosnowflake/pull/1179?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=snowflakedb) |

<details><summary>Additional details and impacted files</summary>


```diff
@@            Coverage Diff             @@
##           master    #1179      +/-   ##
==========================================
- Coverage   83.02%   82.53%   -0.49%     
==========================================
  Files          54       54              
  Lines       12653    12850     +197     
==========================================
+ Hits        10505    10606     +101     
- Misses       2148     2244      +96     
```

</details>

[:umbrella: View full report in Codecov by Sentry](https://app.codecov.io/gh/snowflakedb/gosnowflake/pull/1179?dropdown=coverage&src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=snowflakedb).   
:loudspeaker: Have feedback on the report? [Share it here](https://about.codecov.io/codecov-pr-comment-feedback/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=snowflakedb).

github-actions[bot]: <br/>Thank you for your submission, we really appreciate it. Like many open-source projects, we ask that you 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
   - - -
   <sub>You can retrigger this bot by commenting **recheck** in this Pull Request. Posted by the **CLA Assistant Lite bot**.</sub>
sfc-gh-astachowski: I have read the CLA Document and I hereby sign the CLA

Patch:
diff --git a/converter.go b/converter.go
index 50476c4..363d93f 100644
--- a/converter.go
+++ b/converter.go
@@ -1079,13 +1079,13 @@ func jsonToMapWithKeyType[K comparable](ctx context.Context, valueMetadata field
 			return v.(string), nil
 		}, func(v any) (sql.NullString, error) {
 			return sql.NullString{Valid: v != nil, String: ifNotNullOrDefault(v, "")}, nil
-		})
+		}, false)
 	case "boolean":
 		return buildMapValues[K, sql.NullBool, bool](mapValuesNullableEnabled, m, func(v any) (bool, error) {
 			return v.(bool), nil
 		}, func(v any) (sql.NullBool, error) {
 			return sql.NullBool{Valid: v != nil, Bool: ifNotNullOrDefault(v, false)}, nil
-		})
+		}, false)
 	case "fixed":
 		if valueMetadata.Scale == 0 {
 			return buildMapValues[K, sql.NullInt64, int64](mapValuesNullableEnabled, m, func(v any) (int64, error) {
@@ -1099,7 +1099,7 @@ func jsonToMapWithKeyType[K comparable](ctx context.Context, valueMetadata field
 					return sql.NullInt64{Valid: true, Int64: i64}, nil
 				}
 				return sql.NullInt64{Valid: false}, nil
-			})
+			}, false)
 		}
 		return buildMapValues[K, sql.NullFloat64, float64](mapValuesNullableEnabled, m, func(v any) (float64, error) {
 			return strconv.ParseFloat(string(v.(json.Number)), 64)
@@ -1112,7 +1112,7 @@ func jsonToMapWithKeyType[K comparable](ctx context.Context, valueMetadata field
 				return sql.NullFloat64{Valid: true, Float64: f64}, nil
 			}
 			return sql.NullFloat64{Valid: false}, nil
-		})
+		}, false)
 	case "real":
 		return buildMapValues[K, sql.NullFloat64, float64](mapValuesNullableEnabled, m, func(v any) (float64, error) {
 			return strconv.ParseFloat(string(v.(json.Number)), 64)
@@ -1125,16 +1125,19 @@ func jsonToMapWithKeyType[K comparable](ctx context.Context, valueMetadata field
 				return sql.NullFloat64{Valid: true, Float64: f64}, nil
 			}
 			return sql.NullFloat64{Valid: false}, nil
-		})
+		}, false)
 	case "binary":
 		return buildMapValues[K, []byte, []byte](mapValuesNullableEnabled, m, func(v any) ([]byte, error) {
+			if v == nil {
+				return nil, nil
+			}
 			return hex.DecodeString(v.(string))
 		}, func(v any) ([]byte, error) {
 			if v == nil {
 				return nil, nil
 			}
 			return hex.DecodeString(v.(string))
-		})
+		}, true)
 	case "date", "time", "timestamp_tz", "timestamp_ltz", "timestamp_ntz":
 		return buildMapValues[K, sql.NullTime, time.Time](mapValuesNullableEnabled, m, func(v any) (time.Time, error) {
 			sfFormat, err := dateTimeOutputFormatByType(valueMetadata.Type, params)
@@ -1163,7 +1166,7 @@ func jsonToMapWithKeyType[K comparable](ctx context.Context, valueMetadata field
 				return sql.NullTime{}, err
 			}
 			return sql.NullTime{Valid: true, Time: time}, nil
-		})
+		}, false)
 	case "array":
 		arrayMetadata := valueMetadata.Fields[0]
 		switch arrayMetadata.Type {
@@ -1218,7 +1221,7 @@ func ifNotNullOrDefault[T any](t any, def T) T {
 	return t.(T)
 }
 
-func buildMapValues[K comparable, Vnullable any, VnotNullable any](mapValuesNullableEnabled bool, m map[K]any, buildNotNullable func(v any) (VnotNullable, error), buildNullable func(v any) (Vnullable, error)) (snowflakeValue, error) {
+func buildMapValues[K comparable, Vnullable any, VnotNullable any](mapValuesNullableEnabled bool, m map[K]any, buildNotNullable func(v any) (VnotNullable, error), buildNullable func(v any) (Vnullable, error), nullableByDefault bool) (snowflakeValue, error) {
 	var err error
 	if mapValuesNullableEnabled {
 		result := make(map[K]Vnullable, len(m))
@@ -1231,6 +1234,9 @@ func buildMapValues[K comparable, Vnullable any, VnotNullable any](mapValuesNull
 	}
 	result := make(map[K]VnotNullable, len(m))
 	for k, v := range m {
+		if v == nil && !nullableByDefault {
+			return nil, errNullValueInMap()
+		}
 		if result[k], err = buildNotNullable(v); err != nil {
 			return nil, err
 		}
@@ -1634,96 +1640,306 @@ func buildListFromNativeArrow(ctx context.Context, rowIdx int, fieldMetadata fie
 		case *array.Decimal128:
 			if higherPrecision && fieldMetadata.Scale == 0 {
 				return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (*big.Int, error) {
-					return arrowDecimal128ToValue(typedValues, j, higherPrecision, fieldMetadata.Scale).(*big.Int), nil
+					bigInt := arrowDecimal128ToValue(typedValues, j, higherPrecision, fieldMetadata.Scale)
+					if bigInt == nil {
+						return nil, nil
+					}
+					return bigInt.(*big.Int), nil
+
 				})
 			} else if higherPrecision && fieldMetadata.Scale != 0 {
 				return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (*big.Float, error) {
-					return arrowDecimal128ToValue(typedValues, j, higherPrecision, fieldMetadata.Scale).(*big.Float), nil
+					bigFloat := arrowDecimal128ToValue(typedValues, j, higherPrecision, fieldMetadata.Scale)
+					if bigFloat == nil {
+						return nil, nil
+					}
+					return bigFloat.(*big.Float), nil
+
 				})
+
 			} else if !higherPrecision && fieldMetadata.Scale == 0 {
+				if arrayValuesNullableEnabled(ctx) {
+					return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (sql.NullInt64, error) {
+						v := arrowDecimal128ToValue(typedValues, j, higherPrecision, fieldMetadata.Scale)
+						if v == nil {
+							return sql.NullInt64{Valid: false}, nil
+						}
+						val, err := strconv.ParseInt(v.(string), 10, 64)
+						if err != nil {
+							return sql.NullInt64{Valid: false}, err
+						}
+						return sql.NullInt64{Valid: true, Int64: val}, nil
+
+					})
+				}
 				return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (int64, error) {
 					v := arrowDecimal128ToValue(typedValues, j, higherPrecision, fieldMetadata.Scale)
+					if v == nil {
+						return 0, errNullValueInArray()
+					}
 					return strconv.ParseInt(v.(string), 10, 64)
 				})
 			} else {
+				if arrayValuesNullableEnabled(ctx) {
+					return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (sql.NullFloat64, error) {
+						v := arrowDecimal128ToValue(typedValues, j, higherPrecision, fieldMetadata.Scale)
+						if v == nil {
+							return sql.NullFloat64{Valid: false}, nil
+						}
+						val, err := strconv.ParseFloat(v.(string), 64)
+						if err != nil {
+							return sql.NullFloat64{Valid: false}, err
+						}
+						return sql.NullFloat64{Valid: true, Float64: val}, nil
+
+					})
+				}
 				return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (float64, error) {
 					v := arrowDecimal128ToValue(typedValues, j, higherPrecision, fieldMetadata.Scale)
+					if v == nil {
+						return 0, errNullValueInArray()
+					}
 					return strconv.ParseFloat(v.(string), 64)
 				})
+
 			}
 		case *array.Int64:
+			if arrayValuesNullableEnabled(ctx) {
+				return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (sql.NullInt64, error) {
+					resInt := arrowInt64ToValue(typedValues, j, higherPrecision, fieldMetadata.Scale)
+					if resInt == nil {
+						return sql.NullInt64{Valid: false}, nil
+					}
+					return sql.NullInt64{Valid: true, Int64: resInt.(int64)}, nil
+				})
+			}
 			return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (int64, error) {
-				return arrowInt64ToValue(typedValues, j, higherPrecision, fieldMetadata.Scale).(int64), nil
+				resInt := arrowInt64ToValue(typedValues, j, higherPrecision, fieldMetadata.Scale)
+				if resInt == nil {
+					return 0, errNullValueInArray()
+				}
+				return resInt.(int64), nil
 			})
+
 		case *array.Int32:
+			if arrayValuesNullableEnabled(ctx) {
+				return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (sql.NullInt32, error) {
+					resInt := arrowInt32ToValue(typedValues, j, higherPrecision, fieldMetadata.Scale)
+					if resInt == nil {
+						return sql.NullInt32{Valid: false}, nil
+					}
+					return sql.NullInt32{Valid: true, Int32: resInt.(int32)}, nil
+
+				})
+			}
 			return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (int32, error) {
-				return arrowInt32ToValue(typedValues, j, higherPrecision, fieldMetadata.Scale).(int32), nil
+				resInt := arrowInt32ToValue(typedValues, j, higherPrecision, fieldMetadata.Scale)
+				if resInt == nil {
+					return 0, errNullValueInArray()
+				}
+				return resInt.(int32), nil
 			})
 		case *array.Int16:
+			if arrayValuesNullableEnabled(ctx) {
+				return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (sql.NullInt16, error) {
+					resInt := arrowInt16ToValue(typedValues, j, higherPrecision, fieldMetadata.Scale)
+					if resInt == nil {
+						return sql.NullInt16{Valid: false}, nil
+					}
+					return sql.NullInt16{Valid: true, Int16: resInt.(int16)}, nil
+
+				})
+			}
 			return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (int16, error) {
-				return arrowInt16ToValue(typedValues, j, higherPrecision, fieldMetadata.Scale).(int16), nil
+				resInt := arrowInt16ToValue(typedValues, j, higherPrecision, fieldMetadata.Scale)
+				if resInt == nil {
+					return 0, errNullValueInArray()
+				}
+				return resInt.(int16), nil
 			})
+
 		case *array.Int8:
+			if arrayValuesNullableEnabled(ctx) {
+				return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (sql.NullByte, error) {
+					resInt := arrowInt8ToValue(typedValues, j, higherPrecision, fieldMetadata.Scale)
+					if resInt == nil {
+						return sql.NullByte{Valid: false}, nil
+					}
+					return sql.NullByte{Valid: true, Byte: resInt.(byte)}, nil
+				})
+			}
 			return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (int8, error) {
-				return arrowInt8ToValue(typedValues, j, higherPrecision, fieldMetadata.Scale).(int8), nil
+				resInt := arrowInt8ToValue(typedValues, j, higherPrecision, fieldMetadata.Scale)
+				if resInt == nil {
+					return 0, errNullValueInArray()
+				}
+				return resInt.(int8), nil
 			})
 		}
 	case realType:
+		if arrayValuesNullableEnabled(ctx) {
+			return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (sql.NullFloat64, error) {
+				resFloat := arrowRealToValue(values.(*array.Float64), j)
+				if resFloat == nil {
+					return sql.NullFloat64{Valid: false}, nil
+				}
+				return sql.NullFloat64{Valid: true, Float64: resFloat.(float64)}, nil
+			})
+		}
 		return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (float64, error) {
-			return arrowRealToValue(values.(*array.Float64), j).(float64), nil
+			resFloat := arrowRealToValue(values.(*array.Float64), j)
+			if resFloat == nil {
+				return 0, errNullValueInArray()
+			}
+			return resFloat.(float64), nil
 		})
 	case textType:
+		if arrayValuesNullableEnabled(ctx) {
+			return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (sql.NullString, error) {
+				resString := arrowStringToValue(values.(*array.String), j)
+				if resString == nil {
+					return sql.NullString{Valid: false}, nil
+				}
+				return sql.NullString{Valid: true, String: resString.(string)}, nil
+			})
+		}
 		return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (string, error) {
-			return arrowStringToValue(values.(*array.String), j).(string), nil
+			resString := arrowStringToValue(values.(*array.String), j)
+			if resString == nil {
+				return "", errNullValueInArray()
+			}
+			return resString.(string), nil
 		})
 	case booleanType:
+		if arrayValuesNullableEnabled(ctx) {
+			return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (sql.NullBool, error) {
+				resBool := arrowBoolToValue(values.(*array.Boolean), j)
+				if resBool == nil {
+					return sql.NullBool{Valid: false}, nil
+				}
+				return sql.NullBool{Valid: true, Bool: resBool.(bool)}, nil
+			})
+		}
 		return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (bool, error) {
-			return arrowBoolToValue(values.(*array.Boolean), j).(bool), nil
+			resBool := arrowBoolToValue(values.(*array.Boolean), j)
+			if resBool == nil {
+				return false, errNullValueInArray()
+			}
+			return resBool.(bool), nil
+
 		})
+
 	case binaryType:
 		return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) ([]byte, error) {
-			return arrowBinaryToValue(values.(*array.Binary), j).([]byte), nil
+			res := arrowBinaryToValue(values.(*array.Binary), j)
+			if res == nil {
+				return nil, nil
+			}
+			return res.([]byte), nil
+
 		})
 	case dateType:
+		if arrayValuesNullableEnabled(ctx) {
+			return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (sql.NullTime, error) {
+				v := arrowDateToValue(values.(*array.Date32), j)
+				if v == nil {
+					return sql.NullTime{Valid: false}, nil
+				}
+				return sql.NullTime{Valid: true, Time: v.(time.Time)}, nil
+
+			})
+		}
 		return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (time.Time, error) {
-			return arrowDateToValue(values.(*array.Date32), int(j)).(time.Time), nil
+			v := arrowDateToValue(values.(*array.Date32), j)
+			if v == nil {
+				return time.Time{}, errNullValueInArray()
+			}
+			return v.(time.Time), nil
+
 		})
+
 	case timeType:
+		if arrayValuesNullableEnabled(ctx) {
+			return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (sql.NullTime, error) {
+				v := arrowTimeToValue(values, j, fieldMetadata.Scale)
+				if v == nil {
+					return sql.NullTime{Valid: false}, nil
+				}
+				return sql.NullTime{Valid: true, Time: v.(time.Time)}, nil
+
+			})
+		}
 		return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (time.Time, error) {
-			return arrowTimeToValue(values, int(j), fieldMetadata.Scale).(time.Time), nil
+			v := arrowTimeToValue(values, j, fieldMetadata.Scale)
+			if v == nil {
+				return time.Time{}, errNullValueInArray()
+			}
+			return v.(time.Time), nil
+
 		})
+
 	case timestampNtzType, timestampLtzType, timestampTzType:
+		if arrayValuesNullableEnabled(ctx) {
+			return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (sql.NullTime, error) {
+				ptr := arrowSnowflakeTimestampToTime(values, snowflakeType, fieldMetadata.Scale, j, loc)
+				if ptr != nil {
+					return sql.NullTime{Valid: true, Time: *ptr}, nil
+				}
+				return sql.NullTime{Valid: false}, nil
+			})
+		}
 		return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (time.Time, error) {
 			ptr := arrowSnowflakeTimestampToTime(values, snowflakeType, fieldMetadata.Scale, j, loc)
 			if ptr != nil {
 				return *ptr, nil
 			}
-			return time.Time{}, nil
+			return time.Time{}, errNullValueInArray()
 		})
 	case objectType:
 		return mapStructuredArrayNativeArrowRows(offsets, rowIdx, func(j int) (*structuredType, error) {
+			if values.IsNull(j) {
+				return nil, nil
+			}
 			m := make(map[string]any, len(fieldMetadata.Fields))
 			for fieldIdx, field := range fieldMetadata.Fields {
-				m[field.Name] = values.(*array.Struct).Field(fieldIdx).ValueStr(int(j))
+				m[field.Name] = values.(*array.Struct).Field(fieldIdx).ValueStr(j)
 			}
 			return buildStructuredTypeRecursive(ctx, m, fieldMetadata.Fields, params)
 		})
 	case arrayType:
 		switch fieldMetadata.Fields[0].Type {
 		case "text":
+			if arrayValuesNullableEnabled(ctx) {
+				return buildArrowListRecursive[sql.NullString](ctx, rowIdx, fieldMetadata, offsets, values, loc, higherPrecision, params)
+			}
 			return buildArrowListRecursive[string](ctx, rowIdx, fieldMetadata, offsets, values, loc, higherPrecision, params)
 		case "fixed":
 			if fieldMetadata.Fields[0].Scale == 0 {
+				if arrayValuesNullableEnabled(ctx) {
+					return buildArrowListRecursive[sql.NullInt64](ctx, rowIdx, fieldMetadata, offsets, values, loc, higherPrecision, params)
+				}
 				return buildArrowListRecursive[int64](ctx, rowIdx, fieldMetadata, offsets, values, loc, higherPrecision, params)
 			}
+			if arrayValuesNullableEnabled(ctx) {
+				return buildArrowListRecursive[sql.NullFloat64](ctx, rowIdx, fieldMetadata, offsets, values, loc, higherPrecision, params)
+			}
 			return buildArrowListRecursive[float64](ctx, rowIdx, fieldMetadata, offsets, values, loc, higherPrecision, params)
 		case "real":
+			if arrayValuesNullableEnabled(ctx) {
+				return buildArrowListRecursive[sql.NullFloat64](ctx, rowIdx, fieldMetadata, offsets, values, loc, higherPrecision, params)
+			}
 			return buildArrowListRecursive[float64](ctx, rowIdx, fieldMetadata, offsets, values, loc, higherPrecision, params)
 		case "boolean":
+			if arrayValuesNullableEnabled(ctx) {
+				return buildArrowListRecursive[sql.NullBool](ctx, rowIdx, fieldMetadata, offsets, values, loc, higherPrecision, params)
+			}
 			return buildArrowListRecursive[bool](ctx, rowIdx, fieldMetadata, offsets, values, loc, higherPrecision, params)
 		case "binary":
 			return buildArrowListRecursive[[]byte](ctx, rowIdx, fieldMetadata, offsets, values, loc, higherPrecision, params)
 		case "date", "time", "timestamp_ltz", "timestamp_ntz", "timestamp_tz":
+			if arrayValuesNullableEnabled(ctx) {
+				return buildArrowListRecursive[sql.NullTime](ctx, rowIdx, fieldMetadata, offsets, values, loc, higherPrecision, params)
+			}
 			return buildArrowListRecursive[time.Time](ctx, rowIdx, fieldMetadata, offsets, values, loc, higherPrecision, params)
 		}
 	}
@@ -1736,7 +1952,11 @@ func buildArrowListRecursive[T any](ctx context.Context, rowIdx int, fieldMetada
 		if err != nil {
 			return nil, err
 		}
+		if arrowList == nil {
+			return nil, nil
+		}
 		return arrowList.([]T), nil
+
 	})
 }
 
@@ -1781,6 +2001,9 @@ func buildStructuredMapFromArrow[K comparable](ctx context.Context, rowIdx int,
 			})
 		}
 		return mapStructuredMapNativeArrowRows(make(map[K]string), offsets, rowIdx, keyFunc, func(j int) (string, error) {
+			if items.IsNull(j) {
+				return "", errNullValueInMap()
+			}
 			return items.(*array.String).Value(j), nil
 		})
 	case "boolean":
@@ -1793,6 +2016,9 @@ func buildStructuredMapFromArrow[K comparable](ctx context.Context, rowIdx int,
 			})
 		}
 		return mapStructuredMapNativeArrowRows(make(map[K]bool), offsets, rowIdx, keyFunc, func(j int) (bool, error) {
+			if items.IsNull(j) {
+				return false, errNullValueInMap()
+			}
 			return items.(*array.Boolean).Value(j), nil
 		})
 	case "fixed":
@@ -1825,6 +2051,9 @@ func buildStructuredMapFromArrow[K comparable](ctx context.Context, rowIdx int,
 				})
 			}
 			return mapStructuredMapNativeArrowRows(make(map[K]int64), offsets, rowIdx, keyFunc, func(j int) (int64, error) {
+				if items.IsNull(j) {
+					return 0, errNullValueInMap()
+				}
 				s, err := mapStructuredMapNativeArrowFixedValue[string](valueMetadata, j, items, higherPrecision, "")
 				if err != nil {
 					return 0, err
@@ -1846,6 +2075,9 @@ func buildStructuredMapFromArrow[K comparable](ctx context.Context, rowIdx int,
 				})
 			}
 			return mapStructuredMapNativeArrowRows(make(map[K]float64), offsets, rowIdx, keyFunc, func(j int) (float64, error) {
+				if items.IsNull(j) {
+					return 0, errNullValueInMap()
+				}
 				s, err := mapStructuredMapNativeArrowFixedValue[string](valueMetadata, j, items, higherPrecision, "")
 				if err != nil {
 					return 0, err
@@ -1864,6 +2096,9 @@ func buildStructuredMapFromArrow[K comparable](ctx context.Context, rowIdx int,
 			})
 		}
 		return mapStructuredMapNativeArrowRows(make(map[K]float64), offsets, rowIdx, keyFunc, func(j int) (float64, error) {
+			if items.IsNull(j) {
+				return 0, errNullValueInMap()
+			}
 			return arrowRealToValue(items.(*array.Float64), j).(float64), nil
 		})
 	case "binary":
@@ -1947,6 +2182,9 @@ func buildTimeFromNativeArrowArray[K comparable](mapNullValuesEnabled bool, offs
 		})
 	}
 	return mapStructuredMapNativeArrowRows(make(map[K]time.Time), offsets, rowIdx, keyFunc, func(j int) (time.Time, error) {
+		if items.IsNull(j) {
+			return time.Time{}, errNullValueInMap()
+		}
 		return buildTime(j), nil
 	})
 }
diff --git a/doc.go b/doc.go
index c02ba6e..095004c 100644
--- a/doc.go
+++ b/doc.go
@@ -503,6 +503,15 @@ Additionally, `sf` tag can be added:
 See StructuredObject for all available operations including null support, embedding nested structs, etc.
 
 Retrieving array of simple types works exactly the same like normal values - using Scan function.
+
+You can use WithMapValuesNullable and WithArrayValuesNullable contexts to handle null values in, respectively, maps
+and arrays of simple types in the database. In that case, sql null types will be used:
+
+	ctx := WithArrayValuesNullable(WithStructuredTypesEnabled(context.Background))
+	...
+	var res []sql.NullBool
+	err := rows.Scan(&res)
+
 If you want to scan array of structs, you have to use a helper function ScanArrayOfScanners:
 
 	var res []*simpleObject
diff --git a/errors.go b/errors.go
index 4d068e3..250af2e 100644
--- a/errors.go
+++ b/errors.go
@@ -213,13 +213,17 @@ const (
 
 	// ErrInvalidTimestampTz is an error code for the case where a returned TIMESTAMP_TZ internal value is invalid
 	ErrInvalidTimestampTz = 268000
-	// ErrInvalidOffsetStr is an error code for the case where a offset string is invalid. The input string must
+	// ErrInvalidOffsetStr is an error code for the case where an offset string is invalid. The input string must
 	// consist of sHHMI where one sign character '+'/'-' followed by zero filled hours and minutes
 	ErrInvalidOffsetStr = 268001
 	// ErrInvalidBinaryHexForm is an error code for the case where a binary data in hex form is invalid.
 	ErrInvalidBinaryHexForm = 268002
 	// ErrTooHighTimestampPrecision is an error code for the case where cannot convert Snowflake timestamp to arrow.Timestamp
 	ErrTooHighTimestampPrecision = 268003
+	// ErrNullValueInArray is an error code for the case where there are null values in an array without arrayValuesNullable set to true
+	ErrNullValueInArray = 268004
+	// ErrNullValueInMap is an error code for the case where there are null values in a map without mapValuesNullable set to true
+	ErrNullValueInMap = 268005
 
 	/* OCSP */
 
@@ -293,6 +297,8 @@ const (
 	errMsgQueryStatus                        = "server ErrorCode=%s, ErrorMessage=%s"
 	errMsgInvalidPadding                     = "invalid padding on input"
 	errMsgClientConfigFailed                 = "client configuration failed: %v"
+	errMsgNullValueInArray                   = "for handling null values in arrays use WithArrayValuesNullable(ctx)"
+	errMsgNullValueInMap                     = "for handling null values in maps use WithMapValuesNullable(ctx)"
 )
 
 // Returned if a DNS doesn't include account parameter.
@@ -344,3 +350,17 @@ func errUnknownError() *SnowflakeError {
 		QueryID:  "-1",
 	}
 }
+
+func errNullValueInArray() *SnowflakeError {
+	return &SnowflakeError{
+		Number:  ErrNullValueInArray,
+		Message: errMsgNullValueInArray,
+	}
+}
+
+func errNullValueInMap() *SnowflakeError {
+	return &SnowflakeError{
+		Number:  ErrNullValueInMap,
+		Message: errMsgNullValueInMap,
+	}
+}
diff --git a/structured_type.go b/structured_type.go
index 381530d..17b7b91 100644
--- a/structured_type.go
+++ b/structured_type.go
@@ -1150,8 +1150,8 @@ func (st *structuredType) fieldMetadataByFieldName(fieldName string) (fieldMetad
 	return fieldMetadata{}, errors.New("no metadata for field " + fieldName)
 }
 
-func structuredTypesEnabled(ctx context.Context) bool {
-	v := ctx.Value(enableStructuredTypes)
+func optionEnabled(ctx context.Context, option contextKey) bool {
+	v := ctx.Value(option)
 	if v == nil {
 		return false
 	}
@@ -1159,13 +1159,16 @@ func structuredTypesEnabled(ctx context.Context) bool {
 	return ok && d
 }
 
+func structuredTypesEnabled(ctx context.Context) bool {
+	return optionEnabled(ctx, enableStructuredTypes)
+}
+
 func mapValuesNullableEnabled(ctx context.Context) bool {
-	v := ctx.Value(mapValuesNullable)
-	if v == nil {
-		return false
-	}
-	d, ok := v.(bool)
-	return ok && d
+	return optionEnabled(ctx, mapValuesNullable)
+}
+
+func arrayValuesNullableEnabled(ctx context.Context) bool {
+	return optionEnabled(ctx, arrayValuesNullable)
 }
 
 func getSfFieldName(field reflect.StructField) string {
diff --git a/util.go b/util.go
index 9352dc7..4bc8a5a 100644
--- a/util.go
+++ b/util.go
@@ -34,6 +34,7 @@ const (
 	queryTag                         contextKey = "QUERY_TAG"
 	enableStructuredTypes            contextKey = "ENABLE_STRUCTURED_TYPES"
 	mapValuesNullable                contextKey = "MAP_VALUES_NULLABLE"
+	arrayValuesNullable              contextKey = "ARRAY_VALUES_NULLABLE"
 )
 
 const (
@@ -160,6 +161,12 @@ func WithMapValuesNullable(ctx context.Context) context.Context {
 	return context.WithValue(ctx, mapValuesNullable, true)
 }
 
+// WithArrayValuesNullable changes how array values are returned.
+// Instead of simple values (like string) sql.NullXXX wrappers (like sql.NullString) are used.
+func WithArrayValuesNullable(ctx context.Context) context.Context {
+	return context.WithValue(ctx, arrayValuesNullable, true)
+}
+
 // Get the request ID from the context if specified, otherwise generate one
 func getOrGenerateRequestIDFromContext(ctx context.Context) UUID {
 	requestID, ok := ctx.Value(snowflakeRequestIDKey).(UUID)


Test patch:
diff --git a/structured_type_read_test.go b/structured_type_read_test.go
index 63b2033..906ff24 100644
--- a/structured_type_read_test.go
+++ b/structured_type_read_test.go
@@ -1649,6 +1649,155 @@ func TestMapWithNullValues(t *testing.T) {
 	})
 }
 
+func TestArraysWithNullValues(t *testing.T) {
+	warsawTz, err := time.LoadLocation("Europe/Warsaw")
+	assertNilF(t, err)
+	testcases := []struct {
+		name     string
+		query    string
+		actual   any
+		expected any
+	}{
+		{
+			name:     "string",
+			query:    "SELECT ARRAY_CONSTRUCT('x', null, 'yz', null)::ARRAY(STRING)",
+			actual:   []sql.NullString{},
+			expected: []sql.NullString{{Valid: true, String: "x"}, {Valid: false}, {Valid: true, String: "yz"}, {Valid: false}},
+		},
+		{
+			name:     "bool",
+			query:    "SELECT ARRAY_CONSTRUCT(true, null, false)::ARRAY(BOOLEAN)",
+			actual:   []sql.NullBool{},
+			expected: []sql.NullBool{{Valid: true, Bool: true}, {Valid: false}, {Valid: true, Bool: false}},
+		},
+		{
+			name:     "fixed - scale == 0",
+			query:    "SELECT ARRAY_CONSTRUCT(null, 2, 3)::ARRAY(BIGINT)",
+			actual:   []sql.NullInt64{},
+			expected: []sql.NullInt64{{Valid: false}, {Valid: true, Int64: 2}, {Valid: true, Int64: 3}},
+		},
+		{
+			name:     "fixed - scale == 0",
+			query:    "SELECT ARRAY_CONSTRUCT(1.3, 2.0, null, null)::ARRAY(NUMBER(38, 19))",
+			actual:   []sql.NullFloat64{},
+			expected: []sql.NullFloat64{{Valid: true, Float64: 1.3}, {Valid: true, Float64: 2.0}, {Valid: false}, {Valid: false}},
+		},
+		{
+			name:     "real",
+			query:    "SELECT ARRAY_CONSTRUCT(1.9, 0.2, null)::ARRAY(DOUBLE)",
+			actual:   []sql.NullFloat64{},
+			expected: []sql.NullFloat64{{Valid: true, Float64: 1.9}, {Valid: true, Float64: 0.2}, {Valid: false}},
+		},
+		{
+			name:     "binary",
+			query:    "SELECT ARRAY_CONSTRUCT(null, TO_BINARY('616263'))::ARRAY(BINARY)",
+			actual:   [][]byte{},
+			expected: [][]byte{nil, {'a', 'b', 'c'}},
+		},
+		{
+			name:     "date",
+			query:    "SELECT ARRAY_CONSTRUCT('2024-04-05'::DATE, null)::ARRAY(DATE)",
+			actual:   []sql.NullTime{},
+			expected: []sql.NullTime{{Valid: true, Time: time.Date(2024, time.April, 5, 0, 0, 0, 0, time.UTC)}, {Valid: false}},
+		},
+		{
+			name:     "time",
+			query:    "SELECT ARRAY_CONSTRUCT('13:14:15'::TIME, null)::ARRAY(TIME)",
+			actual:   []sql.NullTime{},
+			expected: []sql.NullTime{{Valid: true, Time: time.Date(1, 0, 0, 13, 14, 15, 0, time.UTC)}, {Valid: false}},
+		},
+		{
+			name:     "timestamp_tz",
+			query:    "SELECT ARRAY_CONSTRUCT('2022-08-31 13:43:22 +0200'::TIMESTAMP_TZ, null)::ARRAY(TIMESTAMP_TZ)",
+			actual:   []sql.NullTime{},
+			expected: []sql.NullTime{{Valid: true, Time: time.Date(2022, 8, 31, 13, 43, 22, 0, warsawTz)}, {Valid: false}},
+		},
+		{
+			name:     "timestamp_ntz",
+			query:    "SELECT ARRAY_CONSTRUCT('2022-08-31 13:43:22'::TIMESTAMP_NTZ, null)::ARRAY(TIMESTAMP_NTZ)",
+			actual:   []sql.NullTime{},
+			expected: []sql.NullTime{{Valid: true, Time: time.Date(2022, 8, 31, 13, 43, 22, 0, time.UTC)}, {Valid: false}},
+		},
+		{
+			name:     "timestamp_ltz",
+			query:    "SELECT ARRAY_CONSTRUCT('2022-08-31 13:43:22'::TIMESTAMP_LTZ, null)::ARRAY(TIMESTAMP_LTZ)",
+			actual:   []sql.NullTime{},
+			expected: []sql.NullTime{{Valid: true, Time: time.Date(2022, 8, 31, 13, 43, 22, 0, warsawTz)}, {Valid: false}},
+		},
+		{
+			name:     "array",
+			query:    "SELECT ARRAY_CONSTRUCT(ARRAY_CONSTRUCT(true, null), null, ARRAY_CONSTRUCT(null, false, true))::ARRAY(ARRAY(BOOLEAN))",
+			actual:   [][]sql.NullBool{},
+			expected: [][]sql.NullBool{{{Valid: true, Bool: true}, {Valid: false}}, nil, {{Valid: false}, {Valid: true, Bool: false}, {Valid: true, Bool: true}}},
+		},
+	}
+	runDBTest(t, func(dbt *DBTest) {
+		dbt.mustExec("ALTER SESSION SET TIMEZONE = 'Europe/Warsaw'")
+		dbt.forceNativeArrow()
+		dbt.enableStructuredTypes()
+		for _, tc := range testcases {
+			t.Run(tc.name, func(t *testing.T) {
+				rows := dbt.mustQueryContext(WithStructuredTypesEnabled(WithArrayValuesNullable(context.Background())), tc.query)
+				defer rows.Close()
+				rows.Next()
+				err := rows.Scan(&tc.actual)
+				assertNilF(t, err)
+				if tc.name == "time" {
+					for i, nt := range tc.actual.([]sql.NullTime) {
+						assertEqualE(t, nt.Valid, tc.expected.([]sql.NullTime)[i].Valid)
+						assertEqualE(t, nt.Time.Hour(), tc.expected.([]sql.NullTime)[i].Time.Hour())
+						assertEqualE(t, nt.Time.Minute(), tc.expected.([]sql.NullTime)[i].Time.Minute())
+						assertEqualE(t, nt.Time.Second(), tc.expected.([]sql.NullTime)[i].Time.Second())
+					}
+				} else if tc.name == "timestamp_tz" || tc.name == "timestamp_ltz" || tc.name == "timestamp_ntz" {
+					for i, nt := range tc.actual.([]sql.NullTime) {
+						assertEqualE(t, nt.Valid, tc.expected.([]sql.NullTime)[i].Valid)
+						assertTrueE(t, nt.Time.Equal(tc.expected.([]sql.NullTime)[i].Time))
+					}
+				} else {
+					assertDeepEqualE(t, tc.actual, tc.expected)
+				}
+				fmt.Printf("%v", reflect.TypeOf(tc.actual))
+			})
+		}
+	})
+
+}
+
+func TestArraysWithNullValuesHigherPrecision(t *testing.T) {
+	testcases := []struct {
+		name     string
+		query    string
+		actual   any
+		expected any
+	}{
+		{
+			name:   "fixed - scale == 0",
+			query:  "SELECT ARRAY_CONSTRUCT(null, 2)::ARRAY(BIGINT)",
+			actual: []*big.Int{},
+		},
+	}
+	runDBTest(t, func(dbt *DBTest) {
+		dbt.mustExec("ALTER SESSION SET TIMEZONE = 'Europe/Warsaw'")
+		dbt.forceNativeArrow()
+		dbt.enableStructuredTypes()
+		for _, tc := range testcases {
+			t.Run(tc.name, func(t *testing.T) {
+				ctx := WithHigherPrecision(WithStructuredTypesEnabled(WithArrayValuesNullable(context.Background())))
+				rows := dbt.mustQueryContext(ctx, tc.query)
+				defer rows.Close()
+				rows.Next()
+				err := rows.Scan(&tc.actual)
+				assertNilF(t, err)
+				assertNilF(t, tc.actual.([]*big.Int)[0])
+				bigInt, _ := new(big.Int).SetString("2", 10)
+				assertEqualE(t, tc.actual.([]*big.Int)[1].Cmp(bigInt), 0)
+			})
+		}
+	})
+
+}
+
 type HigherPrecisionStruct struct {
 	i *big.Int
 	f *big.Float