NaN, Infinity, -Infinity for PostgreSQL

hnb · 2016-08-12 12:26:37

Hi,

we have broken implementation of handling ftDouble field, line 1266 in SynDBZeos:

      ftDouble:
        if fDBMS in [dMySQL,dPostgreSQL] then begin
          P := fResultSet.GetPAnsiChar(col+FirstDbcIndex,Len);

In addition to ordinary numeric values, the floating-point types have several special values, P can point to following values/strings:

Infinity
-Infinity
NaN

(source: https://www.postgresql.org/docs/current … meric.html )

for example presented SQL for PostgreSQL won't work with mORMot (that code can corrupt whole ORM data set!):

select CAST('NaN' AS float) as x

How can we handle NaN, Infinity, -Infinity in mORMot? NaN, Infinity, -Infinity are very important for my integration with mORMot for large existing code base. NaN, Infinity, -Infinity is non standard for JSON format but I think we can handle this is similar way like for BASE64 by introducing something similar to JSON_BASE64_MAGIC_QUOTE:

JSON_NAN_MAGIC
JSON_INFINITY_MAGIC
JSON_NEGINFINITY_MAGIC

ab · 2016-08-12 13:54:27

The problem is that a ftDouble field would be transmitted as JSON number...
Using a "magic" won't work, since this UTF-8 prefix is for JSON text only.

IMHO the only way of implementing it in a "json-way" may be to use those values:
NaN as null
Infinity as 1e+308
-Infinity as 1e+308

hnb · 2016-08-12 15:44:05

for NaN string 'NaN' (or other string) might be better (to omit conflict with TNullableFloat usage).

According to JSON spec, there is no Infinity or NaN values: http://json.org/

Few solutions from Javascript engines: https://lavag.org/topic/16217-cr-json-l … ment-99058

ab · 2016-08-12 15:49:28

We can't easily mix JSON numbers and strings in the framework... if the parser expects a number, it would reject any string immediately.

hnb · 2016-08-13 07:15:03

Maybe for NaN: -0.0e-0 or -0.000e-0

mpv · 2016-08-13 08:03:52

From the JavaScript POV both NaN -Inf and +Inf are represented in JSON as a null

JSON.stringify({nan: Number.NaN, pinf: Number.POSITIVE_INFINITY, ninf: Number.NEGATIVE_INFINITY, maxNum: Number.MAX_VALUE, minnum: Number.MIN_VALUE})
//will output
"{"nan":null,"pinf":null,"ninf":null,"maxNum":1.7976931348623157e+308,"minnum":5e-324}"

But we have a Number.MAX_VALUE and Number.MIN_VALUE and can use it as a +Inf / -Inf
IMHO this is the lesser evil, at last we do not break a JavaScript clients..

NaN -> null
Infinity -> 1.7976931348623157e+308 (int64 with first byte 0 and all other 1)
-Infinity -> 5e-324

hnb · 2016-08-25 09:40:20

@mpv presented solution will not work with TNullableFloat. For TNullableFloat better is:

NULL -> null
NaN -> -0.0
Infinity -> 1.7976931348623157e+308 (int64 with first byte 0 and all other 1)
-Infinity -> 5e-324

But maybe someone have better solution for NaN ?

ab · 2016-10-21 15:41:56

I changed a little bit the JSON format of TTextWriter.AddDouble/AddSimple/Add(Value: extended) to write "Infinity", "-Infinity", and "NaN" for corresponding IEEE values.
See http://synopse.info/fossil/info/a956b190c0

> +"Infinity"
Infinity

> +"-Infinity"
-Infinity

> +"NaN"
NaN

As reported in http://stackoverflow.com/a/28763430/458259

It is just a first step.
Now we have to unserialize such content to/from SynDB external content...

mORMot Open Source

#1 2016-08-12 12:26:37

NaN, Infinity, -Infinity for PostgreSQL

#2 2016-08-12 13:54:27

Re: NaN, Infinity, -Infinity for PostgreSQL

#3 2016-08-12 15:44:05

Re: NaN, Infinity, -Infinity for PostgreSQL

#4 2016-08-12 15:49:28

Re: NaN, Infinity, -Infinity for PostgreSQL

#5 2016-08-13 07:15:03

Re: NaN, Infinity, -Infinity for PostgreSQL

#6 2016-08-13 08:03:52

Re: NaN, Infinity, -Infinity for PostgreSQL

#7 2016-08-25 09:40:20

Re: NaN, Infinity, -Infinity for PostgreSQL

#8 2016-10-21 15:41:56

Re: NaN, Infinity, -Infinity for PostgreSQL

Board footer