Re: [sqlite] NaN in, 0.0 out?

[Dominique Pellé]

Francis J. Monari, Esquire <monarifj....@juno.com> wrote:

> All,
>
> How are +infinity and -infinity handled?
>
> Frank.

Don't forget signed zero, signaling and quiet NaN.

Here is a short c++ programs to show how +infinity, -infinity
zero, -zero, quiet NaN, signaling NaN are internally represented
in IEEE-754:

$ cat double.cpp
#include <limits>
#include <iostream>
#include <cstdint>

using namespace std;
int main()
{
  union
  {
    double   d;
    uint64_t i;
  } inf, ninf, zero, nzero, qnan, snan;

  if (numeric_limits<double>::has_infinity)
  {
    inf.d = numeric_limits<double>::infinity();
    ninf.d = -inf.d;
    cout << "+infinity:\t[" <<  inf.d << "]" << "\t0x" << hex <<
inf.i << dec << endl;
    cout << "-infinity:\t[" << ninf.d << "]" << "\t0x" << hex <<
ninf.i << dec << endl;

    zero.d  = 1/inf.d;
    nzero.d = 1/ninf.d;
    cout << "zero\t\t["  <<  zero.d << "]" << "\t0x" << hex <<  zero.i
<< dec << endl;
    cout << "-zero\t\t[" << nzero.d << "]" << "\t0x" << hex << nzero.i
<< dec << endl;
  }
  if (numeric_limits<double>::has_quiet_NaN)
  {
    qnan.d = numeric_limits<double>::quiet_NaN();
    cout << "quiet nan:\t[" << qnan.d << "]" << "\t0x" << hex <<
qnan.i << dec << endl;
  }
  if (numeric_limits<double>::has_signaling_NaN)
  {
    snan.d = numeric_limits<double>::signaling_NaN();
    cout << "signaling nan:\t[" << snan.d << "]" << "\t0x" << hex <<
snan.i << dec << endl;
  }
  return 0;
}

$ g++ -std=gnu++0x -Wall double.cpp
$ ./a.out
+infinity:      [inf]   0x7ff0000000000000
-infinity:      [-inf]  0xfff0000000000000
zero            [0]     0x0
-zero           [-0]    0x8000000000000000
quiet nan:      [nan]   0x7ff8000000000000
signaling nan:  [nan]   0x7ffc000000000000


In sqlite:

sqlite> CREATE TABLE t (pos REAL, neg REAL, nn REAL);
sqlite> INSERT into t VALUES (1, 2, 3);
sqlite> .nullvalue ThisIsANull
sqlite> insert into t values ((1e305 * 1e305), (-1e305 * 1e305), 1/0);
sqlite> insert into t values (1/(1e305 * 1e305), 1/(-1e305 * 1e305), 1/0);
spatialite> SELECT * FROM t;
1.0|2.0|3.0
Inf|-Inf|ThisIsANull
0.0|0.0|ThisIsANull

The signed zero seems lost in SQLite.

Regards
-- Dominique
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users