IgnatiusPang commented on PR #25747:
URL: https://github.com/apache/datafusion/pull/25747#issuecomment-5835222779
```markdown
### 1. SQL Reproducer (Asymmetric NaN Handling)
```sql
-- Case 1: One column contains NaN -> unexpectedly returns NULL (empty)
SELECT corr(x, y) AS r
FROM (
VALUES
(1.0, 'NaN'::double),
(2.0, 3.0),
(3.0, 4.0)
) AS t(x, y);
-- Case 2: Both columns contain NaN -> returns NaN
SELECT corr(x, y) AS r
FROM (
VALUES
('NaN'::double, 'NaN'::double),
(2.0, 3.0),
(3.0, 4.0)
) AS t(x, y);
```
**Current Result (unpatched DataFusion):**
- Case 1 output: `NULL` (empty)
- Case 2 output: `NaN`
**Expected Result:**
Both queries should return `NaN`. In IEEE-754 and relational algebra, an
invalid floating-point computation (`NaN`) must not silently turn into missing
data (`NULL`).
---
### 2. Grouped Aggregation Inconsistency
```sql
SELECT grp, corr(x, y) AS r
FROM (
VALUES
('g1', 1.0, 'NaN'::double),
('g1', 2.0, 3.0),
('g1', 3.0, 4.0),
('g2', 1.0, 2.0),
('g2', 2.0, 4.0),
('g2', 3.0, 6.0)
) AS t(grp, x, y)
GROUP BY grp
ORDER BY grp;
```
**Current Output:**
```text
+-----+-----+
| grp | r |
+-----+-----+
| g1 | | <-- SQL NULL instead of NaN
| g2 | 1.0 |
+-----+-----+
```
---
### 3. Root Cause
In `datafusion/functions-aggregate/src/correlation.rs`:
```rust
// CorrelationAccumulator::evaluate:
if mean1.is_nan() && mean2.is_nan() {
return Ok(ScalarValue::Float64(Some(f64::NAN)));
}
let n = self.covar.get_count();
if mean1.is_nan() || mean2.is_nan() || n < 2 {
return Ok(ScalarValue::Float64(None)); // <-- returns SQL NULL
}
```
When only one column mean is `NaN`, `mean1.is_nan() && mean2.is_nan()` is
false. The code falls through to `mean1.is_nan() || mean2.is_nan() || n < 2`
and returns `None` (SQL `NULL`).
### 4. Proposed Fix
Check `if mean1.is_nan() || mean2.is_nan()` to return `Some(f64::NAN)`,
reserving `None` solely for insufficient rows (`n < 2`) without `NaN`.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
