When NULL is not enough – NaN in Firebird
Did you know you can store NaN value in Firebird? You didn’t? Me neither, until last week when Slavomír Skopalík showed me whole new world. It was in Delphi, so I was curious how it will turn out from .NET/FirebirdClient.
The NaN values is a special value of double type. It’s declared as 0D / 0D, which doesn’t make much sense. But when you look what’s under, you’ll see 00 00 00 00 00 00 F8 FF, which makes more sense. And given it’s a “normal” value it shouldn’t be a problem to save it to the Firebird from .NET. Let’s try it.
using (var conn = new FbConnection(ConnectionString))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "create table test (d double precision)";
cmd.ExecuteNonQuery();
}
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "insert into test values (@d)";
cmd.Parameters.Add("@d", double.NaN);
cmd.ExecuteNonQuery();
}
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "insert into test values (@d)";
cmd.Parameters.Add("@d", -double.NaN);
cmd.ExecuteNonQuery();
}
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "insert into test values (@d)";
cmd.Parameters.Add("@d", double.PositiveInfinity);
cmd.ExecuteNonQuery();
}
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "insert into test values (@d)";
cmd.Parameters.Add("@d", double.NegativeInfinity);
cmd.ExecuteNonQuery();
}
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from test";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(reader[0]);
}
}
}
}
As I was playing with it I tried saving also -double.NaN. And then also double.PositiveInfinity and double.NegativeInfinity. Because why not, right? 😃
The select then returns this. As expected.
NaN
NaN
?
-?
I suppose the -double.NaN is NaN anyway, that’s why the sign was “lost”.
And from isql, to make sure it’s really there and Firebird understands it (the missing sign for negative infinity in isql’s output is a known issue).
SQL> select * from test;
D
=======================
NaN
NaN
Infinity
Infinity
And there you have it. I hope I never encounter such value in the database myself, as I can imagine a lot of fun it provides.
Nonetheless, if NULL is not enough for you, this is an escape plan. Now only the undefined is missing (because UNKNOWN is already there), then the hell can really begin. 😃
