1) As for sql standard, I read specification document: there is nothing said about ieee 754 standard and nothing is written about NaN, Infinity literals. Among popular sql dbs only oracle and postgres allow storing and retrieving such values. These are short cuts from their documentation: *oracle:* *There are some special values for the BINARY_FLOAT/BINARY_DOUBLE datatypes.* * BINARY_FLOAT_NAN A value of type BINARY_FLOAT for Not a Number* * BINARY_DOUBLE_NAN A value of type BINARY_DOUBLE for Not a Number* * BINARY_FLOAT_INFINITY A value of type BINARY_FLOAT for positive infinity(+∞)* * BINARY_DOUBLE_INFINITY A value of type BINARY_DOUBLE for positive infinity(+∞)* *postgres:* *In addition to ordinary numeric values, the floating-point types have several special values:** Infinity, -Infinity, NaN* * These represent the IEEE 754 special values "infinity", "negative infinity", and "not-a-number", respectively. (On a machine whose floating-point arithmetic does not follow IEEE 754, these values will probably not work as expected.) When writing these values as constants in an SQL command, you must put quotes around them, for example UPDATE table SET x = 'Infinity'. On input, these strings are recognized in a case-insensitive manner.*
2) Julian, I created PR <https://github.com/apache/calcite/pull/572> with few tests and RexBuilder class modifications, can you please take a look, am I going in the right direction? 2017-11-25 4:56 GMT+00:00 Julian Hyde <[email protected]>: > I’m not sure whether we should do this. I can see the benefits of making > Calcite’s SQL FLOAT type behave more like Java's double type. (Not java > float; java float is 32 bit floating point, whereas SQL FLOAT is 64 bit > floating point.) But I don’t recall whether the SQL standard deals with > NaN, and I am cautious about departing from the standard. > > > Calcite uses BigDecimal for handling DOUBLE, FLOAT values > > Not strictly true. It uses BigDecimal for all numeric literals. But that’s > just while we’re preparing the query. At run time we translate to something > more efficient. What we translate to depends on which engine you are using. > > Julian > > > > > On Nov 23, 2017, at 4:53 AM, Volodymyr Tkach <[email protected]> > wrote: > > > > Currently we are working in Drill to allow support for Nan an Inf > > <https://issues.apache.org/jira/browse/DRILL-5919> values . > > When using such values with functions we see the NumberFormatException > > exception. > > Query example: *select sin(cast('NaN' as float)) from (values(1)) * > > > > It turns out that Calcite uses BigDecimal for handling DOUBLE, FLOAT > > values. > > Is it acceptable for Calcite to support nan, infinity values handling? > For > > example, adding new types NON_NUMERIC_DOUBLE, NON_NUMERIC_FLOAT? > >
