Incorrect results for not (NaN between A and B)

[bhhari]

Consider a table with a double column with some NaN values.The following queries return inconsistent results. not (val between 1 and 3) when used as a filter excludes NaN rows, but when used as a projection returns true.

presto:tpch> select * from nan;
 val
-----
 NaN
(1 row)

presto:tpch> select * from nan where not (val between 1 and 3);
 val
-----
(0 rows)

presto:tpch> select not (val between 1 and 3) from nan;
 _col0
-------
 true
(1 row)

[bhhari]

cc : @mbasmanova @rongrong @kaikalur

[mbasmanova]

CC: @highker @wenleix @arhimondr

[kaikalur]

OK. To simplify, I just made it a < and I see a somewhat related issue. Evaluating using NaN seems to be messed up:

 select * from test_sreeni_temp;
  x                   
-----
 NaN 


select not(x < 2), x < 2, not(x > 2), x > 2, not (x between 1 and 2), x between 1 and 2 from test_sreeni_temp;
 _col0 | _col1 | _col2 | _col3 | _col4 | _col5 
-------+-------+-------+-------+-------+-------
 false | false | false | false | true  | false 

so both x </> 2 and not(x </> 2) return false - not confusing at all! And to make more fun between has a completely different behavior.

[mbasmanova]

@kaikalur Sreeni, do you happen to know what would be the right behavior?

[kaikalur]

Quick check in the spec doesn't find NaN at all. That's why I thought it would be just like NULL. I will read the spec more carefully and update it. We can look at Java semantics I suppose.