xuzifu666 commented on PR #5003:
URL: https://github.com/apache/calcite/pull/5003#issuecomment-4666293291
> Approval conditional on the assumption that the quidem test results were
validated
Very thanks for your review and suggestion!
**This is explanation of the test result verification:**
Test 1: STDDEV_POP(deptno) Result Analysis
| DEPTNO | SDP |
|--------|-----|
| 10 | 0 | ← All employees in deptno=10 group
| 20 | 0 | ← All employees in deptno=20 group
| 30 | 0 | ← All employees in deptno=30 group
| 50 | 0 | ← All employees in deptno=50 group
| 60 | 0 | ← All employees in deptno=60 group
| | | ← Employees with deptno=NULL
Why are all values 0?
- When all employees in deptno=10 group have deptno value 10 →
STDDEV_POP(10, 10, 10, ...) = 0
- When deptno=20 → STDDEV_POP(20) = 0 (single value has zero standard
deviation)
- When deptno=NULL → SQL aggregate functions skip NULL, result is NULL
(empty)
---
Test 2: STDDEV_SAMP(deptno) Result Analysis
| DEPTNO | SDS |
|--------|-----|
| 10 | 0 | ← 2 values: stddev_samp(10,10) = 0
| 20 | | ← 1 value: sample size < 2, returns NULL
| 30 | 0 | ← 2 values: stddev_samp(30,30) = 0
| 50 | 0 | ← 2 values: stddev_samp(50,50) = 0
| 60 | | ← 1 value: sample size < 2, returns NULL
| | | ← NULL values are skipped, result is NULL
Why some values are NULL?
- STDDEV_SAMP requires at least 2 samples
- deptno=20 has only 1 employee → NULL
- deptno=60 has only 1 employee → NULL
---
Test 6: MAX(deptno) Result Analysis
| DEPTNO | M |
|--------|----|
| 10 | 10 | ← All values in group are 10, MAX = 10
| 20 | 20 | ← All values in group are 20, MAX = 20
| 30 | 30 | ← All values in group are 30, MAX = 30
| 50 | 50 | ← All values in group are 50, MAX = 50
| 60 | 60 | ← All values in group are 60, MAX = 60
| | | ← NULL values are skipped, result is NULL
Why does MAX(deptno) equal deptno itself?
- Because all rows in each group have the same deptno value (equal to that
group's key)
- MAX(10, 10, ...) = 10
- MAX(20) = 20
---
Test 9: Mixed Functions Result
| DEPTNO | M | SDP |
|--------|----|----|
| 10 | 10 | 0 | ← MAX(deptno)=10, STDDEV_POP(deptno)=0
| 20 | 20 | 0 | ← MAX(deptno)=20, STDDEV_POP(deptno)=0
| 30 | 30 | 0 | ← MAX(deptno)=30, STDDEV_POP(deptno)=0
| 50 | 50 | 0 | ← MAX(deptno)=50, STDDEV_POP(deptno)=0
| 60 | 60 | 0 | ← MAX(deptno)=60, STDDEV_POP(deptno)=0
| | | | ← Both functions skip NULL, both return NULL
Two aggregate functions validate the rule's correctness simultaneously:
- MAX verifies the "return key value itself" logic
- STDDEV_POP verifies the "return constant value" logic
- NULL row verifies the "NULL semantics handling" logic
---
Core Validation Logic:
After GROUP BY deptno, all rows in each group have the same deptno value
(a constant), therefore:
- Aggregate functions operating on constants produce predictable results
- Each row in these 9 tests validates the optimization rule's correctness.
I'd like to know if you're satisfied with the current changes. Of course,
I'll fix any boundary condition issues I discover in the future. @mihaibudiu
--
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]
