go-mssqldb
go-mssqldb copied to clipboard
denisenkom
VARBINARY Column parameter sent as NVARCHAR when value is nil
VARBINARY Column parameter sent as NVARCHAR
When inserting a Type of Valuer into a VARBINARY(MAX) column if the Value() returns nil, the parameter type sent to sql server is NVARCHAR
Profiler Output
When value provided for the custom type:
exec sp_executesql
N'INSERT INTO [dbo].[event_store] (payload, options, headers) VALUES (@p1, @p2, @p3)',
N'@p1 varbinary(12),
@p2 varbinary(113), <========
@p3 nvarchar(1)',
@p1=0x74657374207061796C6F6164,
@p2=0x7B22616D71704F75742E45786368616E67654E616D654F7074696F6E223A2274657374222C22616D71704F75742E45786368616E6765547970654F7074696F6E223A22746F706963222C22616D71704F75742E526F7574696E674B65794F7074696F6E223A22746573742E73656E64227D,
@p3=NULL
When no value provided for the custom type:
exec sp_executesql
N'INSERT INTO [dbo].[event_store] (payload, options, headers) VALUES (@p1, @p2, @p3)',
N'@p1 varbinary(12),
@p2 nvarchar(1), <========
@p3 nvarchar(1)',
@p1=0x74657374207061796C6F6164,
@p2=NULL, <========
@p3=NULL
Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.
The value sent to method
&outboxer.OutboxMessage{
Payload: []byte("test payload"),
Options: map[string]interface{}{
"amqpOut.ExchangeNameOption": "test",
"amqpOut.ExchangeTypeOption": "topic",
"amqpOut.RoutingKeyOption": "test.send",
}
Snippet from method that is inserting code:
query := fmt.Sprintf(`INSERT INTO [%s].[%s] (payload, options, headers) VALUES (@p1, @p2, @p3)`, s.SchemaName, s.EventStoreTable)
if _, err := s.conn.ExecContext(ctx, query, evt.Payload, evt.Options, evt.Headers); err != nil {
return fmt.Errorf("failed to insert message into the data store: %w", err)
}
Here Options and Headers are custom type that have Valuer implemented part of a struct OutboxMessage.
type OutboxMessage struct {
ID int64
Dispatched bool
DispatchedAt sql.NullTime
Payload []byte
Options DynamicValues
Headers DynamicValues
}
// DynamicValues is a map that can be serialized
type DynamicValues map[string]interface{}
// Value return a driver.Value representation of the order items
func (p DynamicValues) Value() (driver.Value, error) {
if len(p) == 0 {
return nil, nil
}
return json.Marshal(p)
}
Expected behavior The type sent to SQL Server must be VARBINARY.
Further technical details
SQL Server version: (DOCKER) Microsoft SQL Server 2017 (RTM-CU11) (KB4462262) - 14.0.3038.14 (X64) Sep 14 2018 13:53:44 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 16.04.5 LTS)
Operating system: Mac 10.14.5
Go version: go1.13.1 darwin/amd64
Table schema
CREATE TABLE dbo.event_store (
id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
dispatched BIT NOT NULL default 0,
dispatched_at DATETIME,
payload VARBINARY(MAX) not null,
options VARBINARY(MAX),
headers VARBINARY(MAX)
);
@m4ver1k I was able to reproduce the issue. I delve a bit deeper into the driver, looks like the culprit comes from https://github.com/denisenkom/go-mssqldb/blob/master/types.go#L1121 , when the parameter is nil, the parameter type becomes nvarchar(1) (probably because this is a more flexible type and SQL Server allows implicit conversion of nvarchar to many other SQL datatypes (except for varbinary in this case of course)). Anyhow, the case typeNull essentially comes from https://github.com/denisenkom/go-mssqldb/blob/master/mssql.go#L800 .
The problem is, when nil is passed as a parameter, the driver needs to guess what data type it should be. For some types (e.g., float64, bool), the sql package provides a null version (e.g., NullFloat64 and NullBool) so the user can use these types. For varbinary however there's no such type.
The only solution I can think of is the introduce another driver specific type (similar to the mssql.VarChar type we have currently) so user can use this new type and let the driver know this value is a varbinary. @denisenkom what do you think?
I ran in the issue today again which i had in 2018. Tried some possible setups to make a reproduction case but didn't manage to create one. I can still reproduce it in my environment, but i can't report it. So far from what i have found:
Using pointer causes the issue for me.
The struct:
type CreateOrderAddressRequest struct {
OrderID int `json:"OrderId" validate:"required"`
AddressID int `json:"AddressId"`
AddressIsActive bool
AddressTypeID *int `json:"AddressTypeId"`
AddressDescription *string `validate:"omitempty,max=50"`
CustomerStreet *string `validate:"omitempty,max=50"`
CustomerTown *string `validate:"omitempty,max=50"`
CustomerName1 *string `validate:"omitempty,max=50"`
CustomerName2 *string `validate:"omitempty,max=50"`
CustomerZipCodeNo *string `validate:"omitempty,max=25"`
CustomerCountryID *int `json:"CustomerCountryId"`
CustomerVatNo *string `validate:"omitempty,max=25"`
ContactID *int `json:"ContactId"`
ContactFormOfAddressAbbreviation *string `json:"ContactFormOfAddressAbbreviation"`
ContactFormOfAddressID *int `json:"ContactFormOfAddressId"`
ContactGivenname *string `validate:"omitempty,max=50"`
ContactSurname *string `validate:"omitempty,max=50"`
ContactTitle *string `validate:"omitempty,max=50"`
}
Part of the code of how i call it
var request CreateOrderAddressRequest
err = n.tx.GetContext(
ctx,
&address,
`
.....
FROM
sao.orders_p o WITH(READUNCOMMITTED)
LEFT OUTER JOIN sao.address_m a WITH(READUNCOMMITTED) ON a.i_address_m = @AddressID
LEFT OUTER JOIN sao.country_m c WITH(READUNCOMMITTED) ON c.i_country_m = ISNULL(@CustomerCountryID, a.i_country_m)
LEFT OUTER JOIN sao.contact_m co WITH(READUNCOMMITTED) ON co.i_contact_m = ISNULL(@ContactID, o.i_contact_m)
......
`,
sql.Named("OperatorName", n.Operator.UserName),
sql.Named("OrderID", request.OrderID),
sql.Named("AddressID", request.AddressID),
sql.Named("AddressIsActive", request.AddressIsActive),
sql.Named("AddressDescription", request.AddressDescription),
sql.Named("AddressTypeID", request.AddressTypeID),
sql.Named("CustomerCountryID", request.CustomerCountryID),
sql.Named("CustomerStreet", request.CustomerStreet),
sql.Named("CustomerTown", request.CustomerTown),
sql.Named("CustomerZipCodeNo", request.CustomerZipCodeNo),
sql.Named("CustomerName1", request.CustomerName1),
sql.Named("CustomerName2", request.CustomerName2),
sql.Named("CustomerVatNo", request.CustomerVatNo),
sql.Named("ContactID", request.ContactID),
sql.Named("ContactGivenname", request.ContactGivenname),
sql.Named("ContactSurname", request.ContactSurname),
sql.Named("ContactTitle", request.ContactTitle),
sql.Named("ContactFormOfAddressAbbreviation", request.ContactFormOfAddressAbbreviation),
sql.Named("ContactFormOfAddressID", request.ContactFormOfAddressID),
)
if err != nil {
return err
}
trace log:
......
FROM
sao.orders_p o WITH(READUNCOMMITTED)
LEFT OUTER JOIN sao.address_m a WITH(READUNCOMMITTED) ON a.i_address_m = @AddressID
LEFT OUTER JOIN sao.country_m c WITH(READUNCOMMITTED) ON c.i_country_m = ISNULL(@CustomerCountryID, a.i_country_m)
LEFT OUTER JOIN sao.contact_m co WITH(READUNCOMMITTED) ON co.i_contact_m = ISNULL(@ContactID, o.i_contact_m)
......
2021/05/20 17:25:44 @OperatorName System
2021/05/20 17:25:44 @OrderID 264560
2021/05/20 17:25:44 @AddressID 0
2021/05/20 17:25:44 @AddressIsActive true
2021/05/20 17:25:44 @AddressDescription Lieferadresse
2021/05/20 17:25:44 @AddressTypeID 4
2021/05/20 17:25:44 @CustomerCountryID <nil>
2021/05/20 17:25:44 @CustomerStreet <nil>
2021/05/20 17:25:44 @CustomerTown ASDASD
2021/05/20 17:25:44 @CustomerZipCodeNo 12345
2021/05/20 17:25:44 @CustomerName1 ASDASD
2021/05/20 17:25:44 @CustomerName2 ASDASD
2021/05/20 17:25:44 @CustomerVatNo <nil>
2021/05/20 17:25:44 @ContactID <nil>
2021/05/20 17:25:44 @ContactGivenname
2021/05/20 17:25:44 @ContactSurname
2021/05/20 17:25:44 @ContactTitle
2021/05/20 17:25:44 @ContactFormOfAddressAbbreviation
2021/05/20 17:25:44 @ContactFormOfAddressID <nil>
2021/05/20 17:25:44 got token tokenColMetadata
2021/05/20 17:25:44 got token tokenNbcRow
2021/05/20 17:25:44 got token tokenDoneInProc
2021/05/20 17:25:44 (1 row(s) affected)
2021/05/20 17:25:44 got token tokenError
2021/05/20 17:25:44 got ERROR 8115 Arithmetic overflow error converting expression to data type nvarchar.
2021/05/20 17:25:44 Arithmetic overflow error converting expression to data type nvarchar.
2021/05/20 17:25:44 got token tokenInfo
2021/05/20 17:25:44 got INFO 3621 The statement has been terminated.
2021/05/20 17:25:44 The statement has been terminated.
The issue why the error appears is because @CustomerCountryID and @ContactID are defined as *int in the struct and are compared against DECIMAL(28,0) columns in the table.
Since the fields are nil they will use the typeNull which converts it the datatype nvarchar(1) -> https://github.com/denisenkom/go-mssqldb/blob/master/types.go#L1126
If i fill the struct fields with values (not nil) its fine or when i cast the variables as the type i need it works as well like:
FROM
sao.orders_p o WITH(READUNCOMMITTED)
LEFT OUTER JOIN sao.address_m a WITH(READUNCOMMITTED) ON a.i_address_m = @AddressID
LEFT OUTER JOIN sao.country_m c WITH(READUNCOMMITTED) ON c.i_country_m = ISNULL(CAST(@CustomerCountryID AS INT), a.i_country_m)
LEFT OUTER JOIN sao.contact_m co WITH(READUNCOMMITTED) ON co.i_contact_m = ISNULL(CAST(@ContactID AS INT), o.i_contact_m)
The possible solution which could fix this issue, is to check if its a pointer and if yes, try to check the datatype behind and use its type.
Like if its a *bool, it should use the datatype of bool which would equal bit
Currently experiencing this bug as well. I'm just trying to NULL out a field that is a VARBINARY and it's giving me this message.
