search

microsoft / mssql-django

The Microsoft Django backend for SQL Server provides a connectivity layer for Django on SQL Server or Azure SQL DB.
Other
338 stars 112 forks source link

Unexpected Arithmetic overflow error #310

Open niekheinen opened 10 months ago

niekheinen commented 10 months ago

Software versions

Table schema and Model

PRICE_MAX_DIGITS = 10
PRICE_DECIMAL_PLACES = 4

class PriceList(BaseModel):
    item = models.ForeignKey(
        settings.ITEM_MODEL, db_constraint=False, on_delete=models.CASCADE, related_name="price_lists"
    )
    default_price = models.DecimalField(max_digits=PRICE_MAX_DIGITS, decimal_places=PRICE_DECIMAL_PLACES)
    medium_price = models.DecimalField(
        max_digits=PRICE_MAX_DIGITS, decimal_places=PRICE_DECIMAL_PLACES, null=True, blank=True
    )
    best_price = models.DecimalField(
        max_digits=PRICE_MAX_DIGITS, decimal_places=PRICE_DECIMAL_PLACES, null=True, blank=True
    )
    default_price_m2 = models.DecimalField(
        max_digits=PRICE_MAX_DIGITS, decimal_places=PRICE_DECIMAL_PLACES, null=True, blank=True
    )
    medium_price_m2 = models.DecimalField(
        max_digits=PRICE_MAX_DIGITS, decimal_places=PRICE_DECIMAL_PLACES, null=True, blank=True
    )
    best_price_m2 = models.DecimalField(
        max_digits=PRICE_MAX_DIGITS, decimal_places=PRICE_DECIMAL_PLACES, null=True, blank=True
    )
    valid_from = models.DateField()
    valid_to = models.DateField()

Database Connection Settings

DATABASES = {
    "default": {
        "ENGINE": "mssql",
        "NAME": "HIT",
        "USER": "django",
        "PASSWORD": os.environ.get("DB_PASSWORD"),
        "HOST": os.environ.get("DB_HOST"),
        "PORT": 1433,
        "AUTOCOMMIT": True,
        "CONN_MAX_AGE": None,
        "OPTIONS": {
            "driver": "ODBC Driver 18 for SQL Server",
            "extra_params": "MARS_Connection=yes;MARS_Connection=yes;Encrypt=no",
            "return_rows_bulk_insert": True,
        },
    }
}

Problem description and steps to reproduce When using bulk_create to create multiple price lists at once, we sometime receive the following error message from the db driver: ('22003', '[22003] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Arithmetic overflow error converting nvarchar to data type numeric. (8115) (SQLExecDirectW)').

At first, it seems we might just provide wrong values to the bulk_create, but after some further investigation I'm afraid the issue lies with the driver.

Business context: Not all items have a medium or best price, so these fields are nullable. The m2 prices are not provided as parameters in the mutation, instead they're automatically calculated by a model manager based on the item's width.

While debugging we noticed something strange. If we provide bulk_create with multiple objects where all medium or best prices are null, everything works as expected. If we provide bulk_create with multiple objects where all medium or best prices are not null, everything also works as expected. However, as soon as we have one object where medium or best price is null, and another object where medium or best is not null we run into the above mentioned error.

I.e. the following input will result in an Arithmetic overflow error:

{
    "item":"976976223520",
    "default_price":"35.0246",
    "medium_price":"12.0000",
    "best_price":"11.0000",
    "valid_from":"2024-01-01",
    "valid_to":"2024-06-30"
},
{
    "item":"978984013500",
    "default_price":"12.8500",
    "medium_price":None,
    "best_price":None,
    "valid_from":"2024-01-01",
    "valid_to":"2024-06-30"
}

Expected behavior and actual behavior We expect bulk_create to also work on the above mentioned input. In development, our tests pass for this input when using a postgres database, howver, the test fails on our CI where we use mssql.

Error message/stack trace image

Variables at moment of error image

Any other details that can be helpful I've tried to see the actual SQL that was created by the driver by starting a trace with the SQL server profiler. I've made two mutations 1 with a mixed nullable input (on the left in image below), and one where medium/best were null for both objects (on the right in image below). The SQL on the left resulted in an Arithmetic overflow error, the SQL on the right doesn't. image

The text found in the image can be seen below for reference:

-- Left text
exec sp_describe_undeclared_parameters N'INSERT INTO [prices_pricelist] ([sysmodified], [syscreated], [sysmodifier_id], [syscreator_id], [item_id], [default_price], [medium_price], [best_price], [valid_from], [valid_to], [default_price_m2], [medium_price_m2], [best_price_m2]) OUTPUT INSERTED.[id] VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12, @P13), (@P14, @P15, @P16, @P17, @P18, @P19, @P20, @P21, @P22, @P23, @P24, @P25, @P26)'

declare @p1 int  set @p1=113  
exec sp_prepexec @p1 output,

N'
    @P1 datetime2,
    @P2 datetime2,
    @P3 int,
    @P4 int,
    @P5 nvarchar(24),
    @P6 nvarchar(14),   --default_price    = N'35.0150' 
    @P7 nvarchar(14),   --medium_price     = N'11.9850'
    @P8 nvarchar(14),   --best_price       = N'10.9980'
    @P9 nvarchar(20),
    @P10 nvarchar(20),
    @P11 nvarchar(14),  --default_price_m2 = N'14.9000'
    @P12 nvarchar(12),  --medium_price_m2  = N'5.1000'
    @P13 nvarchar(12),  --best_price_m2    = N'4.6800'

    @P14 datetime2,
    @P15 datetime2,
    @P16 int,
    @P17 int,
    @P18 nvarchar(24),
    @P19 nvarchar(14),  --default_price    = N'12.8500'
    @P20 numeric(1,0),  --medium_price     = NULL
    @P21 numeric(1,0),  --best_price       = NULL
    @P22 nvarchar(20),
    @P23 nvarchar(20),
    @P24 nvarchar(12),  --default_price_m2 = N'9.5200'
    @P25 numeric(1,0),  --medium_price_m2  = NULL
    @P26 numeric(1,0)   --best_price_m2    = NULL

',N'INSERT INTO [prices_pricelist] ([sysmodified], [syscreated], [sysmodifier_id], [syscreator_id], [item_id], [default_price], [medium_price], [best_price], [valid_from], [valid_to], [default_price_m2], [medium_price_m2], [best_price_m2]) OUTPUT INSERTED.[id] 

VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12, @P13), (@P14, @P15, @P16, @P17, @P18, @P19, @P20, @P21, @P22, @P23, @P24, @P25, @P26)','2023-10-30 15:05:01.8261460','2023-10-30 15:05:01.8261790',5,5,N'976976223520',N'35.0150',N'11.9850',N'10.9980', N'2024-01-01',N'2024-06-30',N'14.9000',N'5.1000',N'4.6800','2023-10-30 15:05:01.8263250','2023-10-30 15:05:01.8263400',5,5,N'978984013500',N'12.8500',NULL,NULL,N'2024-01-01',N'2024-06-30',N'9.5200',NULL,NULL  select @p1

-- Right text
exec sp_describe_undeclared_parameters N'INSERT INTO [prices_pricelist] ([sysmodified], [syscreated], [sysmodifier_id], [syscreator_id], [item_id], [default_price], [medium_price], [best_price], [valid_from], [valid_to], [default_price_m2], [medium_price_m2], [best_price_m2]) OUTPUT INSERTED.[id] VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12, @P13), (@P14, @P15, @P16, @P17, @P18, @P19, @P20, @P21, @P22, @P23, @P24, @P25, @P26)'

declare @p1 int  set @p1=120  
exec sp_prepexec @p1 output,

N'
    @P1 datetime2,
    @P2 datetime2,
    @P3 int,
    @P4 int,
    @P5 nvarchar(24), 
    @P6 nvarchar(14),   --default_price    = N'35.0150'
    @P7 numeric(1,0),   --medium_price     = NULL
    @P8 numeric(1,0),   --best_price       = NULL
    @P9 nvarchar(20),
    @P10 nvarchar(20),
    @P11 nvarchar(14),  --default_price_m2 = N'14.9000'
    @P12 numeric(1,0),  --medium_price_m2  = NULL
    @P13 numeric(1,0),  --best_price_m2    = NULL

    @P14 datetime2,
    @P15 datetime2,
    @P16 int,
    @P17 int,
    @P18 nvarchar(24),
    @P19 nvarchar(14),  --default_price    = N'12.8500'
    @P20 numeric(1,0),  --medium_price     = NULL
    @P21 numeric(1,0),  --best_price       = NULL
    @P22 nvarchar(20),
    @P23 nvarchar(20),
    @P24 nvarchar(12),  --default_price_m2 = N'9.5200'
    @P25 numeric(1,0),  --medium_price_m2  = NULL
    @P26 numeric(1,0)   --best_price_m2    = NULL

',N'INSERT INTO [prices_pricelist] ([sysmodified], [syscreated], [sysmodifier_id], [syscreator_id], [item_id], [default_price], [medium_price], [best_price], [valid_from], [valid_to], [default_price_m2], [medium_price_m2], [best_price_m2]) OUTPUT INSERTED.[id] 

VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12, @P13), (@P14, @P15, @P16, @P17, @P18, @P19, @P20, @P21, @P22, @P23, @P24, @P25, @P26)','2023-10-30 15:05:10.6467840','2023-10-30 15:05:10.6468110',5,5,N'976976223520',N'35.0150',NULL,NULL,N'2024-01-01',N'2024-06-30',N'14.9000',NULL,NULL,'2023-10-30 15:05:10.6469010','2023-10-30 15:05:10.6469110',5,5,N'978984013500',N'12.8500',NULL,NULL,N'2024-01-01',N'2024-06-30',N'9.5200',NULL,NULL  select @p1

Screenshot of the the table in question viewed via ssms: image.

dauinsight commented 10 months ago

Hi @niekheinen, the server behavior is actually by design as we're expecting the same type for the field.

For more details and workaround: https://github.com/mkleehammer/pyodbc/issues/419

niekheinen commented 10 months ago

Hi @dauinsight, thanks for the quick reply. I've implemented a similar workaround myself.

Would it make sense to implement such a workaround in mssql-django? This way the MSSQL driver behaves in a more similar way to Django's officially supported DB drivers.

dauinsight commented 10 months ago

Thank you for the suggestion! We generally don't do any type conversions on our end but might consider this in the future.

amy-mac commented 5 months ago

We have this same problem. We were using a postgresql database for years with no issue, but now that we're using mssql we're running into this error. Changing our bulk_create to be one at a time when certain fields are allowed to be NULL is not a great work-around.