I’d be open to adding support for NaN and Infinity to Calcite’s SQL dialect (possibly enabled using a flag). But it would be more work than just letting in Double.NaN in RexBuilder and RexLiteral.
> On Nov 27, 2017, at 1:22 PM, Volodymyr Tkach <[email protected]> wrote: > > 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? >> >>
