[
https://issues.apache.org/jira/browse/CALCITE-4467?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17264863#comment-17264863
]
Stamatis Zampetakis commented on CALCITE-4467:
----------------------------------------------
>From what I recall the SQL standard does not cover operations with NaN. From
>the standard perspective I think that operations that would otherwise return
>NaN raise exceptions.
Below some extracts from the SQL standard which raise exceptions:
{noformat}
The dyadic arithmetic operators <plus sign>, <minus sign>, <asterisk>, and
<solidus> (+, –, *, and /,
respectively) specify addition, subtraction, multiplication, and division,
respectively. If the value of a
divisor is zero, then an exception condition is raised: data exception —
division by zero.
10) If <natural logarithm> is specified, then let V be the value of the simply
contained <numeric value expression>.
If V is 0 (zero) or negative, then an exception condition is raised: data
exception — invalid argument for natural logarithm.
12) If <power function> is specified, then let NVEB be the <numeric value
expression base>, then let VB be the value of NVEB, let NVEE be the <numeric
value expression exponent>, and let VE be the value of NVEE.
b) If VB is 0 (zero) and VE is negative, then an exception condition is raised:
data exception — invalid argument for power function.
e) If VB is negative and VE is not equal to an exact numeric value with scale 0
(zero), then an exception condition is raised: data exception — invalid
argument for power function.
{noformat}
If everybody was following the SQL standard then I guess we wouldn't ever have
NaN values to compare. In reality though, DB implementations do contain NaN
values. For the record,
[Oracle|https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#SQLRF50979]
also considers 'Nan'='Nan' violating the IEEE 754 standard.
We could introduce a configuration property; people who strictly follow the
standard can benefit from the simplications and those who don't can still run
the simplifier.
If we don't want to introduce a property then it sounds reasonable to be more
conservative and don't simplify things that might lead to wrong results for
some DBMS.
> Incorrect simplification for 'NaN' value
> ----------------------------------------
>
> Key: CALCITE-4467
> URL: https://issues.apache.org/jira/browse/CALCITE-4467
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Jesus Camacho Rodriguez
> Assignee: Jesus Camacho Rodriguez
> Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> {{RexSimplify}} simplifies {{x = x}} to {{null or x is not null}} (similarly
> <= and >=), and {{x != x}} to {{null and x is null}} (similarly < and >).
> https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L363
> This may not be applicable in some cases. For instance, if the type of x is
> floating-point, x could be 'NaN'. While some RDBMS consider 'NaN' = 'NaN'
> (e.g., Postgres), some others consider 'NaN' != 'NaN' following the IEEE 754
> standard. For the latest, the rewriting above will result in incorrect
> results.
> I think we should simply ignore this simplification for floating-point type.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
