Consider regression=# select sum(1/ten) filter (where ten>0) from tenk1; ERROR: division by zero
This query works without error in versions before 10. It was evidently broken by commit 8ed3f11bb, which rearranged nodeAgg.c to evaluate all aggregate input expressions before considering any FILTERs. This is not an acceptable behavioral change. This sort of thing seems like perhaps the primary use-case for FILTER. It's stated to work by our own manual --- see the last sentence in https://www.postgresql.org/docs/devel/static/sql-expressions.html#syntax-express-eval And it's required by the SQL spec, which states clearly that the aggregate's input expression is only evaluated at rows for which the filter expression yields true. (In SQL:2011, see 10.9 <aggregate function> general rules 4 and 5a.) I think possibly the best answer is to revert 8ed3f11bb. We could think about some compromise solution like merging the projections only for aggregates without FILTER. But that would require two code paths through the relevant functions in nodeAgg.c, which would be a substantial maintenance burden; and the extra branches required means that this would be a net negative for performance in the simplest case with only one aggregate. In any case, since that patch went in before the v10 expression evaluation rewrite, I think any argument that it's worth keeping would need to be made afresh. The overhead that it was hoping to save should be much lower now. Thoughts? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers