Repository: duckdb/duckdb (language: python)

Issue:
`corr(x, y)` with constant columns should return `NaN`, not `NULL`
### What happens?

The `corr` aggregate should return `NaN` when a column is constant, not `NULL`, same as `0 / 0` returns `NaN` not `NULL` anymore since v1.1.

EDIT: can't check right now but it should be verified if the same issue exists for `regr_slope`



### To Reproduce

```sql
SELECT 
    corr(x, x)
FROM
    (SELECT 1 AS x UNION ALL SELECT 1 AS x);
````
```
┌────────────┐
│ corr(x, x) │
│   double   │
├────────────┤
│       NULL │
└────────────┘
```

### OS:

Linux

### DuckDB Version:

1.1.0

### DuckDB Client:

Python

### Hardware:

x64

### Full Name:

Soeren Wolfers

### Affiliation:

G-Research

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a nightly build

### Did you include all relevant data sets for reproducing the issue?

Not applicable - the reproduction does not require a data set

### Did you include all code required to reproduce the issue?

- [X] Yes, I have

### Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

- [X] Yes, I have


Discussion / hints:

Fix patch:
diff --git a/src/core_functions/aggregate/distributive/skew.cpp b/src/core_functions/aggregate/distributive/skew.cpp
index 0dcb87db9b0c..ef42dce44d08 100644
--- a/src/core_functions/aggregate/distributive/skew.cpp
+++ b/src/core_functions/aggregate/distributive/skew.cpp
@@ -62,7 +62,7 @@ struct SkewnessOperation {
 		}
 		double div = std::sqrt(p);
 		if (div == 0) {
-			target = NAN;
+			finalize_data.ReturnNull();
 			return;
 		}
 		double temp1 = std::sqrt(n * (n - 1)) / (n - 2);
diff --git a/src/core_functions/aggregate/regression/regr_intercept.cpp b/src/core_functions/aggregate/regression/regr_intercept.cpp
index 7f741d22c8e7..a3a117456ad3 100644
--- a/src/core_functions/aggregate/regression/regr_intercept.cpp
+++ b/src/core_functions/aggregate/regression/regr_intercept.cpp
@@ -45,10 +45,6 @@ struct RegrInterceptOperation {
 			return;
 		}
 		RegrSlopeOperation::Finalize<T, RegrSlopeState>(state.slope, target, finalize_data);
-		if (Value::IsNan(target)) {
-			finalize_data.ReturnNull();
-			return;
-		}
 		auto x_avg = state.sum_x / state.count;
 		auto y_avg = state.sum_y / state.count;
 		target = y_avg - target * x_avg;
diff --git a/src/include/duckdb/core_functions/aggregate/algebraic/corr.hpp b/src/include/duckdb/core_functions/aggregate/algebraic/corr.hpp
index 63cd80fabf2e..0d595b11aa7a 100644
--- a/src/include/duckdb/core_functions/aggregate/algebraic/corr.hpp
+++ b/src/include/duckdb/core_functions/aggregate/algebraic/corr.hpp
@@ -58,7 +58,11 @@ struct CorrOperation {
 			if (!Value::DoubleIsFinite(std_y)) {
 				throw OutOfRangeException("STDDEV_POP for Y is out of range!");
 			}
-			target = std_x * std_y != 0 ? cov / (std_x * std_y) : NAN;
+			if (std_x * std_y == 0) {
+				finalize_data.ReturnNull();
+				return;
+			}
+			target = cov / (std_x * std_y);
 		}
 	}
 
diff --git a/src/include/duckdb/core_functions/aggregate/regression/regr_slope.hpp b/src/include/duckdb/core_functions/aggregate/regression/regr_slope.hpp
index be397d417605..7010504190a6 100644
--- a/src/include/duckdb/core_functions/aggregate/regression/regr_slope.hpp
+++ b/src/include/duckdb/core_functions/aggregate/regression/regr_slope.hpp
@@ -46,7 +46,11 @@ struct RegrSlopeOperation {
 			if (!Value::DoubleIsFinite(var_pop)) {
 				throw OutOfRangeException("VARPOP is out of range!");
 			}
-			target = var_pop != 0 ? cov / var_pop : NAN;
+			if (var_pop == 0) {
+				finalize_data.ReturnNull();
+				return;
+			}
+			target = cov / var_pop;
 		}
 	}
 


Test patch:
diff --git a/test/sql/aggregate/aggregates/test_corr.test b/test/sql/aggregate/aggregates/test_corr.test
index 4504f2c570b8..8b391312e384 100644
--- a/test/sql/aggregate/aggregates/test_corr.test
+++ b/test/sql/aggregate/aggregates/test_corr.test
@@ -18,7 +18,7 @@ NULL
 query I
 select corr(1,1)
 ----
-NAN
+NULL
 
 statement error
 select corr(*)
diff --git a/test/sql/aggregate/aggregates/test_regression.test b/test/sql/aggregate/aggregates/test_regression.test
index 4a807c063446..196526459df6 100644
--- a/test/sql/aggregate/aggregates/test_regression.test
+++ b/test/sql/aggregate/aggregates/test_regression.test
@@ -72,7 +72,7 @@ NULL
 query I
 select regr_slope(1,1)
 ----
-NAN
+NULL
 
 statement error
 select regr_slope(*)
diff --git a/test/sql/aggregate/aggregates/test_skewness.test b/test/sql/aggregate/aggregates/test_skewness.test
index 3a21a4673269..ad3cf4c8a352 100644
--- a/test/sql/aggregate/aggregates/test_skewness.test
+++ b/test/sql/aggregate/aggregates/test_skewness.test
@@ -28,7 +28,7 @@ select skewness(*)
 query I
 select skewness (10) from range (5)
 ----
-NAN
+NULL
 
 #Empty Table
 query I
diff --git a/test/sql/function/list/aggregates/skewness.test b/test/sql/function/list/aggregates/skewness.test
index 5be729491cc1..70d75e65ce26 100644
--- a/test/sql/function/list/aggregates/skewness.test
+++ b/test/sql/function/list/aggregates/skewness.test
@@ -19,11 +19,6 @@ CREATE TABLE skew AS SELECT LIST(10) AS i FROM range(5) t1(i)
 query I
 select list_skewness (i) from skew
 ----
-NAN
-
-query I
-select list_skewness ([1,2])
-----
 NULL
 
 # out of range