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
`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 ef42dce44d08..0dcb87db9b0c 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) {
-			finalize_data.ReturnNull();
+			target = NAN;
 			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 a3a117456ad3..7f741d22c8e7 100644
--- a/src/core_functions/aggregate/regression/regr_intercept.cpp
+++ b/src/core_functions/aggregate/regression/regr_intercept.cpp
@@ -45,6 +45,10 @@ 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 0d595b11aa7a..63cd80fabf2e 100644
--- a/src/include/duckdb/core_functions/aggregate/algebraic/corr.hpp
+++ b/src/include/duckdb/core_functions/aggregate/algebraic/corr.hpp
@@ -58,11 +58,7 @@ struct CorrOperation {
 			if (!Value::DoubleIsFinite(std_y)) {
 				throw OutOfRangeException("STDDEV_POP for Y is out of range!");
 			}
-			if (std_x * std_y == 0) {
-				finalize_data.ReturnNull();
-				return;
-			}
-			target = cov / (std_x * std_y);
+			target = std_x * std_y != 0 ? cov / (std_x * std_y) : NAN;
 		}
 	}
 
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 7010504190a6..be397d417605 100644
--- a/src/include/duckdb/core_functions/aggregate/regression/regr_slope.hpp
+++ b/src/include/duckdb/core_functions/aggregate/regression/regr_slope.hpp
@@ -46,11 +46,7 @@ struct RegrSlopeOperation {
 			if (!Value::DoubleIsFinite(var_pop)) {
 				throw OutOfRangeException("VARPOP is out of range!");
 			}
-			if (var_pop == 0) {
-				finalize_data.ReturnNull();
-				return;
-			}
-			target = cov / var_pop;
+			target = var_pop != 0 ? cov / var_pop : NAN;
 		}
 	}
 


Test patch:
diff --git a/test/sql/aggregate/aggregates/test_corr.test b/test/sql/aggregate/aggregates/test_corr.test
index 8b391312e384..4504f2c570b8 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)
 ----
-NULL
+NAN
 
 statement error
 select corr(*)
diff --git a/test/sql/aggregate/aggregates/test_regression.test b/test/sql/aggregate/aggregates/test_regression.test
index 196526459df6..4a807c063446 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)
 ----
-NULL
+NAN
 
 statement error
 select regr_slope(*)
diff --git a/test/sql/aggregate/aggregates/test_skewness.test b/test/sql/aggregate/aggregates/test_skewness.test
index ad3cf4c8a352..3a21a4673269 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)
 ----
-NULL
+NAN
 
 #Empty Table
 query I
diff --git a/test/sql/function/list/aggregates/skewness.test b/test/sql/function/list/aggregates/skewness.test
index 70d75e65ce26..5be729491cc1 100644
--- a/test/sql/function/list/aggregates/skewness.test
+++ b/test/sql/function/list/aggregates/skewness.test
@@ -19,6 +19,11 @@ 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