SCM

[#1010992] System.Double to DOUBLE PRECISION conversion bug

View Trackers | Bugs | Download .csv | Monitor

Date:
2011-02-11 20:28
Priority:
3
State:
Open
Submitted by:
Udo Liess (uli)
Assigned to:
Nobody (None)
Npgsql Version:
2.0.11
Category:
Group:
Resolution:
None
Summary:
System.Double to DOUBLE PRECISION conversion bug

Detailed description
I found a bug while inserting a System.Double value into a column of type double precision. After reading back some values are not exactly the same. I think the wrong ToString method is used - roundtrip formatting is necessary.
I found in the source code method BasicBackendToNativeTypeConverter.ToBasicType<T>. I propose to change the code for System.Double and System.Single to something like the following:
DoubleValue.ToString("R", CultureInfo.InvariantCulture)
SingleValue.ToString("R", CultureInfo.InvariantCulture)
Probably a new method for floating point variables is necessary.

While comparing the code with my own lines in another project I found also another (better?) way for DateTime:
DateTimeValue.ToString("o", CultureInfo.InvariantCulture)

Udo

Followup

Message
Date: 2011-11-19 03:04
Sender: Francisco Figueiredo jr.

Fixed in cvs. Please, give it a try and let me know if it works for you.
Thanks in advance.
Date: 2011-11-12 22:21
Sender: Francisco Figueiredo jr.


Hi!

Sorry for taking so much time to answer...

I already patched the source in my dev machine to include your fixes for double precision and the tests work ok!

I'll be soon committing the changes to cvs and closing this bug.

Thanks for your feedback and patches.
Date: 2011-02-22 22:04
Sender: Udo Liess

Hi,

(sorry for sending last comment twice. My browser reloaded
the send page after my PC woke up this morning.)

I'm still wondering about the fact that all data are
transferred as string. If so it is a real performance issue!
So I looked around and found
<http://www.postgresql.org/docs/9.0/static/libpq-exec.html>.
In function PQexecParams you can decide which parameter
format to use: text or binary. Of course you have to know
the internal binary structures but this is easy for IEEE754
floating point values and other types. So why not use binary
format for well known types and boost the performance?

Udo
Date: 2011-02-22 07:05
Sender: Udo Liess

Hi Francisco,

nice to here that the double behavior will be improved. Thanks.

On
<http://msdn.microsoft.com/en-us/library/system.globalization.datetimeformatinfo%28v=VS.85%29.aspx>
you can find the specification of "o" format pattern.
Round-trip pattern means: no single bit will be changed
during value-->string-->value conversion. This is in my
opinion the optimum for transferring data into and from
database.
But "o" is not documented for .NET 2.0! In my test I
compiled with VS2010 for .NET 2.0 and it worked - but I have
.NET 4.0 installed. It could fail on a pure .NET 2.0 system.
Also interesting:
<http://stackoverflow.com/questions/1781232/override-datetime-tostring-to-use-o-instead-of-g>

Is there an easy test scenario for the problem?

Udo
Date: 2011-02-21 16:44
Sender: Udo Liess

Hi Francisco,

nice to here that the double behavior will be improved. Thanks.

On
<http://msdn.microsoft.com/en-us/library/system.globalization.datetimeformatinfo%28v=VS.85%29.aspx>
you can find the specification of "o" format pattern.
Round-trip pattern means: no single bit will be changed
during value-->string-->value conversion. This is in my
opinion the optimum for transferring data into and from
database.
But "o" is not documented for .NET 2.0! In my test I
compiled with VS2010 for .NET 2.0 and it worked - but I have
.NET 4.0 installed. It could fail on a pure .NET 2.0 system.
Also interesting:
<http://stackoverflow.com/questions/1781232/override-datetime-tostring-to-use-o-instead-of-g>

Is there an easy test scenario for the problem?

Udo
Date: 2011-02-21 13:36
Sender: Francisco Figueiredo jr.


Hi, Udo.

Got your example here.... Although the terminal says the precision is 15 digits, internally it isn't :) my fault.

Thanks for your feedback. I'll fix it.

About the "o" representation, I'm asking how you think we could better use the "o" specifier. We currently have some problems with datetime and timezone values. Mostly because .net 2.0 kind of lack support for that. Do you have suggestions where we can improve on this area?

Thanks in advance.
Date: 2011-02-21 08:21
Sender: Udo Liess

Hi Francisco,

here comes a small sample program. It can be best evaluate
with help of debugger.
------------------------------------------------
using System;
using System.Globalization;
using Npgsql;

namespace NpgsqlDoubleTests
{
class Program
{
static void Main(string[] args)
{
var csb = new NpgsqlConnectionStringBuilder();
csb.Host = "127.0.0.1";
csb.Database = "postgres";
csb.UserName = "postgres";
csb.Password = "test";
using (var con = new NpgsqlConnection(csb.ConnectionString))
{
con.Open();

double x = 1d / 7d;

// Test 1: Sending a double value into db and reading it
back will result in a different value!
using (var cmd1 = con.CreateCommand())
{
double x1 = x;
cmd1.Parameters.AddWithValue("0", x1);
cmd1.CommandText = "SELECT @0::DOUBLE PRECISION;";
object o1 = cmd1.ExecuteScalar();
double y1 = (double)o1;
if (x != y1)
Console.WriteLine("Test 1: value has been changed");
}

// Test 2: But sending of double can be corrected by
using "R" formatting of ToString().
// We let PostgreSQL calculate 1/7 and select the
difference to our value.
using (var cmd2 = con.CreateCommand())
{
string x2 = x.ToString("R", CultureInfo.InvariantCulture);
cmd2.Parameters.AddWithValue("0", x2);
cmd2.CommandText = "SELECT 1::DOUBLE PRECISION /
7::DOUBLE PRECISION - @0;";
object o2 = cmd2.ExecuteScalar();
double y2 = (double)o2;
if (y2 == 0)
Console.WriteLine("Test 2: double value was send
correctly");
}

// Test 3: Get DOUBLE PRECISION value correctly out of
db by using extra_float_digits.
// First set to maximum value (2) for PostgreSQL 8. Then
try to set to maximum value (3) for PostgreSQL 9. This way
the code will run on all plattforms.
using (var cmd3a = con.CreateCommand())
{
cmd3a.CommandText = "SET extra_float_digits TO 2; SET
extra_float_digits TO 3;";
cmd3a.ExecuteNonQuery();
}
using (var cmd3b = con.CreateCommand())
{
cmd3b.CommandText = "SHOW extra_float_digits;";
Console.WriteLine("extra_float_digits = " +
cmd3b.ExecuteScalar());
}
using (var cmd3c = con.CreateCommand())
{
string x3 = x.ToString("R", CultureInfo.InvariantCulture);
cmd3c.Parameters.AddWithValue("0", x3);
cmd3c.CommandText = "SELECT @0::DOUBLE PRECISION;";
object o3 = cmd3c.ExecuteScalar();
double y3 = (double)o3;
if (x == y3)
Console.WriteLine("Test 3: now the value is the same!");
}

Console.ReadKey();
}
}
}
}
------------------------------------------------

Conclusion:
NPGSQL provides a binary interface to PostgreSQL: it
translates objects like System.Double into database specific
data in order to transport them. Therefore it should handle
all data in a binary correct way - not a single bit should
be changed while data is transfered from or to database.
--> NPGSQL must use "R" formatting for System.Double and for
System.Single.
--> NPGSQL should execute "SET extra_float_digits TO 2; SET
extra_float_digits TO 3;" at beginning of a connection.


(About "o" specifier: this was only an idea.)


Udo
Date: 2011-02-17 13:31
Sender: Francisco Figueiredo jr.


Hi, Udo!

Can you post a sample case?

Posgresql only supports 15 digit precision as specified in the documentation:
http://developer.postgresql.org/pgdocs/postgres/datatype-numeric.html#DATATYPE-FLOAT

So, if you put a value with a higher precision, before getting in the postgresql server, Npgsql will send a string with a 15 numeric precision which according do ms documentation is the default precision of toString.

I think Mono doesn't behave like that because on my tests with Mono on osx, it rounds up the value to 15 digits and send to server.

So, even if we change Npgsql to send the 17 digits with "R" as you suggest, when returning from server the value will be roundup to 15 digits anyway.

Can you elaborate more why you think "o" specifier would be better?

Thanks in advance.

Attached Files:

Changes:

No Changes Have Been Made to This Item

Powered By FusionForge