Framework 2.0 BUG with SqlParameter and "negative zero"

[pdxfilter-google]

Hello! We're seeing some very odd results from the below code. Under
certain conditions, ADO.NET will create commands that specify a
negative zero amount ("-0.00"). It's easy to reproduce.

This is a major issue in our application as we do lots of decimal math
and rely on SqlDataAdapter and SqlCommandBuilder to do our data
updates. As a result, we have intermittent failure. We compare the
supposedly "zero" value to 0 inside of a trigger. When the value
reaches SQL Server as a "negative zero", some actions are undefined.

This is a potentially serious framework bug. I'd like to see it
verified by someone at Microsoft. Ideas for a workaround are welcome.
If no workaround, a T-Shirt or something would be nice!

Thanks,
Jon

=========================

// Run in Visual Studio 2005 "Console Mode"
using System;
using System.Data;
using System.Data.SqlClient;

namespace BadZero {
class Program {
static bool IsDecimalZero(decimal input) {
SqlConnection Connection = new SqlConnection(
@"Data Source=localhost;Trusted_Connection=True");
Connection.Open();
// Send command to SQL to check the value against zero
SqlCommand Command = new SqlCommand(
"IF @p1 = 0 SELECT 'Equal' ELSE SELECT 'Not Equal'",
Connection);
SqlParameter Param = new SqlParameter("@p1",
SqlDbType.Decimal);
Param.Value = input;
Command.Parameters.Add(Param);
string Result = Command.ExecuteScalar() as string;
// When BadZero is passed in, using SQL Profiler, we see
// @p1 being set to:
// GoodZero: 0.00
// BadZero: -0.00 (yes, MINUS 0.00)
Connection.Close();
return Result == "Equal";
}

static void Main(string[] args) {
decimal GoodZero = 0.00m;
decimal BadZero = 0.00m - 0;
// Look the same, but different internally
Console.WriteLine("GoodZero:{0} BadZero:{1}",
decimal.GetBits(GoodZero)[3], decimal.GetBits(BadZero)[3]);

bool GoodResult = IsDecimalZero(GoodZero); // true
bool BadResult = IsDecimalZero(BadZero); // false

Console.WriteLine("GoodResult:{0} BadResult:{1}",
GoodResult.ToString(), BadResult.ToString());
}
}
}

 

[Guest]

Hi, recently we discovered the same bug. I do agree it's a serious issue and
in our case, an online leasing sales-system, it can even have a negative
economic impact on our customer's business. However we can make a relatively
cheap workaround in the ORM mapper used, I understand it's hard to prevent it
on the program level. It should IMHO be fixed within the framework.

Ondřej

 

[Frans Bouma [C# MVP]]

Hi, recently we discovered the same bug. I do agree it's a serious
issue and in our case, an online leasing sales-system, it can even
have a negative economic impact on our customer's business. However
we can make a relatively cheap workaround in the ORM mapper used, I
understand it's hard to prevent it on the program level. It should
IMHO be fixed within the framework.

I escalated it to MS. Haven't heard back about a solution.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Cor Ligthert [MVP]]

I escalated it to MS. Haven't heard back about a solution.And did you get a T-Shirt.

Cor

 

[Frans Bouma [C# MVP]]

Hello! We're seeing some very odd results from the below code. Under
certain conditions, ADO.NET will create commands that specify a
negative zero amount ("-0.00"). It's easy to reproduce.

This is a major issue in our application as we do lots of decimal math
and rely on SqlDataAdapter and SqlCommandBuilder to do our data
updates. As a result, we have intermittent failure. We compare the
supposedly "zero" value to 0 inside of a trigger. When the value
reaches SQL Server as a "negative zero", some actions are undefined.

This is a potentially serious framework bug. I'd like to see it
verified by someone at Microsoft. Ideas for a workaround are welcome.
If no workaround, a T-Shirt or something would be nice!

Ok, I asked MS what's the cause and this is what I got back:

"SqlClient is still passing the decimal value it receives to SQL
Server. The difference in behavior is because the representation for
the "negative zero" changed between .NET 1.1 and .NET 2.0.

SQL Server 2005 has some added validation for incoming input and
translates the "negative zero" to the more standard zero."

HTH,

Frans

Thanks,
Jon

=========================

// Run in Visual Studio 2005 "Console Mode"
using System;
using System.Data;
using System.Data.SqlClient;

namespace BadZero {
class Program {
static bool IsDecimalZero(decimal input) {
SqlConnection Connection = new SqlConnection(
@"Data Source=localhost;Trusted_Connection=True");
Connection.Open();
// Send command to SQL to check the value against zero
SqlCommand Command = new SqlCommand(
"IF @p1 = 0 SELECT 'Equal' ELSE SELECT 'Not Equal'",
Connection);
SqlParameter Param = new SqlParameter("@p1",
SqlDbType.Decimal);
Param.Value = input;
Command.Parameters.Add(Param);
string Result = Command.ExecuteScalar() as string;
// When BadZero is passed in, using SQL Profiler, we see
// @p1 being set to:
// GoodZero: 0.00
// BadZero: -0.00 (yes, MINUS 0.00)
Connection.Close();
return Result == "Equal";
}

static void Main(string[] args) {
decimal GoodZero = 0.00m;
decimal BadZero = 0.00m - 0;
// Look the same, but different internally
Console.WriteLine("GoodZero:{0} BadZero:{1}",
decimal.GetBits(GoodZero)[3],
decimal.GetBits(BadZero)[3]);

bool GoodResult = IsDecimalZero(GoodZero); // true
bool BadResult = IsDecimalZero(BadZero); // false

Console.WriteLine("GoodResult:{0} BadResult:{1}",
GoodResult.ToString(), BadResult.ToString());
}
}
}

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------