xuzifu666 commented on code in PR #5040:
URL: https://github.com/apache/calcite/pull/5040#discussion_r3471484634
##########
core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java:
##########
@@ -2609,6 +2618,47 @@ private RexNode convertOver(Blackboard bb, SqlNode node)
{
}
}
+ /**
+ * Applies a FILTER clause to the arguments of an aggregate call by wrapping
+ * each argument in a CASE expression. For example,
+ * {@code SUM(sal) FILTER (WHERE comm IS NOT NULL)} becomes
+ * {@code SUM(CASE WHEN comm IS NOT NULL THEN sal END)}.
Review Comment:
Your concern is valid. The rewrite is only semantically correct for true
aggregate functions like SUM, COUNT, AVG, MIN, and MAX, because those functions
ignore NULL inputs.
However filter does not hold for window value functions such as FIRST_VALUE,
LAST_VALUE, NTH_VALUE, LEAD, and LAG. such as sql in postgresql would error out：
```
SELECT
ename,
job,
FIRST_VALUE(sal) FILTER (WHERE job = 'MANAGER') OVER (ORDER BY hiredate,
ename) AS first_mgr_sal
FROM emp
ORDER BY hiredate, ename;
```
```
psql:commands.sql:81: ERROR: FILTER is not implemented for non-aggregate
window functions
LINE 4: FIRST_VALUE(sal) FILTER (WHERE job = 'MANAGER') OVER (ORDE...
```
Therefore, based on the tests conducted so far, I believe there are no
issues.
--
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]
