You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Volodymyr Tkach <vo...@gmail.com> on 2017/11/23 12:53:48 UTC
Calcite does not handle NaN, Infinity float values
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?
Re: Calcite does not handle NaN, Infinity float values
Posted by Julian Hyde <jh...@apache.org>.
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 <vo...@gmail.com> 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 <jh...@apache.org>:
>
>> 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 <vo...@gmail.com>
>> 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?
>>
>>
Re: Calcite does not handle NaN, Infinity float values
Posted by Volodymyr Tkach <vo...@gmail.com>.
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 <jh...@apache.org>:
> 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 <vo...@gmail.com>
> 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?
>
>
Re: Calcite does not handle NaN, Infinity float values
Posted by Julian Hyde <jh...@apache.org>.
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 <vo...@gmail.com> 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?