Repository navigation
Cannot write decimal.MaxValue if precision/scale are set on SqlParameter #1655
Description
It seems like it's possible to write decimal.Max into a decimal(38, 4) column, as long as Precision and Scale aren't set on the parameter. The moment they are set, an exception is thrown. In EF Core, we always set the Precision/Scale because it's necessary for Always Encrypted to function correctly.
Exception:
Unhandled exception. System.OverflowException: Conversion overflows.
at System.Data.SqlTypes.SqlDecimal.ToDecimal()
at System.Data.SqlTypes.SqlDecimal.get_Value()
at Microsoft.Data.SqlClient.TdsParser.AdjustDecimalScale(Decimal value, Int32 newScale)
at Microsoft.Data.SqlClient.TdsParser.TDSExecuteRPCAddParameter(TdsParserStateObject stateObj, SqlParameter param, MetaType mt, Byte options, SqlCommand command)
at Microsoft.Data.SqlClient.TdsParser.TdsExecuteRPC(SqlCommand cmd, _SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource`1 completion, Int32 startRpc, Int32 startParam)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Program.<Main>$(String[] args) in /home/roji/projects/test/Program.cs:line 24
at Program.<Main>$(String[] args) in /home/roji/projects/test/Program.cs:line 24
at Program.<Main>$(String[] args) in /home/roji/projects/test/Program.cs:line 24
at Program.<Main>(String[] args)
Full repro:
await using var conn = new SqlConnection("Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false");
await conn.OpenAsync();
await using var command = new SqlCommand(@"
DROP TABLE IF EXISTS data;
CREATE TABLE data (foo decimal(38,4))", conn);
await command.ExecuteNonQueryAsync();
// Works
using var cmd1 = new SqlCommand("INSERT INTO data (foo) VALUES (@p)", conn);
cmd1.Parameters.Add(new SqlParameter("p", decimal.MaxValue));
cmd1.ExecuteNonQuery();
// Throws
using var cmd2 = new SqlCommand("INSERT INTO data (foo) VALUES (@p)", conn);
cmd2.Parameters.Add(new SqlParameter("p", decimal.MaxValue) { Precision = 38, Scale = 4 });
cmd2.ExecuteNonQuery();Originally reported in dotnet/efcore#28240
Activity
DavoudEshtehari commentedon Jun 21, 2022
Thanks @roji, We'll check and will get back to you.
artur-yakupov commentedon Sep 26, 2023
It seems that some logic is incorrect in sqlbuffer or somewhere else. There were some issues years ago about precision and scale and some fixes have been made.
Things that i have read about: decimal(38,20) to .net decimal , decimal(38,18) to net and one more wich i cant find now.
Seems some fixes helped, since i can save 2342342,35467895678945678956 now into (38,20), but i accidentally got new bug.
i am using sql server and ef core 7.0.11. My database decimal type is decimal(38,20) and i am getting overflow exception when trying to save 9999999999999 (13 nines, notice that 12 nines is ok) for example.
i have quick look at sqlbuffer but could not find bug yet
DavoudEshtehari commentedon Sep 28, 2023
For the record, when the scale is going to change
SqlDecimal.AdjustScaleis called. The return value is aSqlDecimaland an attempt to retrieve data from theValueproperty throws the overflow exception. This exception comes fromSystem.Data.SqlTypes.SqlDecimaland I think the fix should be proposed from the related library.IT-CASADO commentedon Feb 22, 2024
I also run into this issue.
dotnet/efcore#33109
Why is this only Low Priority. How can we workaround this?
A possible mentioned workaround here is not a viable solution. See the comment by @roji here.
shidcordero commentedon Aug 5, 2024
It's been a while and haven't seen much update on this. We are also having issue storing decimal value with 28 precision on a decimal(38,10). Our code was heavy on EF and it will hard adding non-EF workaround. Any updates on this?
DavoudEshtehari commentedon Aug 6, 2024
@shidcordero, @IT-CASADO this issue is related to underlying APIs and it would be better addressed in the related API, as I mentioned here.
@shidcordero If you have another issue, feel free to open a new issue and file your repro for further investigation.
DennisTycho commentedon Oct 24, 2024
What exactly do you mean by "related to underlying APIs" ? Are you saying that it cannot be fixed in SqlClient? and needs to be fixed elsewhere? what needs to be done to inform the relevant stakeholders so this can be fixed. I see this as a rather big issue.
ekhtiari commentedon Feb 28, 2025
we still have this issue
HUkiah commentedon Mar 13, 2025
结合 ChatGPT 和 DeepSeek，大概明白了这个问题。核心点是 SqlClient 尝试调整小数位导致中间结果溢出（Conversion overflow）。.NET 的 decimal 类型的上限大约是 7.9228 × 10^28，AdjustDecimalScale 处理 Scale 时，value 会乘以 10^scale ，大于 .NET 的 decimal 类型的上限。如果调整 scale = 0 上述抛异常的代码可以会正常工作。我没有看具体的源码，测试了几个边界情况符合上面的描述，另外不改变 scale 的情况下，可能需要自定义ValueConverter去处理
Bump Microsoft.Data.SqlClient from 6.1.6 to 7.1.0 (#66)
Bump the microsoft group with 11 updates (#1370)
Bump the nuget-ecosystem group with 6 updates (#16)