[CH] Aligning the NULL and NaN compare semantics of Spark and CH

[exmy]

Spark and CH have different compare semantics for NULL and NaN value:

• In Spark, NULL is least and Nan is greatest
• IN CH, NaN and NULLs are alway last in the array. nan_direction_hint can control its least or greatest.
  • if nan_direction_hint == -1, NaN and NULLs are considered as least than everything other;
  • if nan_direction_hint == 1, NaN and NULLs are considered as greatest than everything other.

example:
sort array [null,0.5,1.0,2.1,NaN] in ascending, in descending

	ascending	descending
Spark	[null,0.5,1.0,2.1,NaN]	[NaN,2.1,1.0,0.5,null]
CH	[0.5,1.0,2.1,nan,NULL]	[2.1,1.0,0.5,nan,NULL]

The array_max/array_min functions have the same issue.

It seems there is no other way to align them except by modifying the CH kernel code, and the modify is unlikely to be accepted by CH community.

The initial change:

diff --git a/src/Columns/ColumnVector.h b/src/Columns/ColumnVector.h
index 7b40d01c..a06c985d 100644
--- a/src/Columns/ColumnVector.h
+++ b/src/Columns/ColumnVector.h
@@ -99,8 +99,8 @@ struct FloatCompareHelper
                 return 0;
 
             return isnan_a
-                ? nan_direction_hint
-                : -nan_direction_hint;
+                ? -nan_direction_hint
+                : nan_direction_hint;
         }
 
         return (T(0) < (a - b)) - ((a - b) < T(0));
diff --git a/src/Functions/array/arraySort.cpp b/src/Functions/array/arraySort.cpp
index a853289e..86457fed 100644
--- a/src/Functions/array/arraySort.cpp
+++ b/src/Functions/array/arraySort.cpp
@@ -22,7 +22,7 @@ struct Less
     bool operator()(size_t lhs, size_t rhs) const
     {
         if constexpr (positive)
-            return column.compareAt(lhs, rhs, column, 1) < 0;
+            return column.compareAt(lhs, rhs, column, -1) < 0;
         else
             return column.compareAt(lhs, rhs, column, -1) > 0;
     }