It leads to 6x performance drop even if the argument is not nullable:

milovidov-desktop :) SELECT sum(number) FROM system.numbers

SELECT sum(number)
FROM system.numbers

Query id: 2f21a1b4-c469-4a0b-a8ed-e86efe2edd43

Cancelling query.
Ok.
Query was cancelled.

0 rows in set. Elapsed: 2.906 sec. Processed 8.84 billion rows, 70.74 GB (3.04 billion rows/s., 24.34 GB/s.) 

milovidov-desktop :) SELECT sumOrNull(number) FROM system.numbers

SELECT sumOrNull(number)
FROM system.numbers

Query id: 06dda4aa-9168-41fe-8d2d-63f7857e44fb

Cancelling query.
Ok.
Query was cancelled.

0 rows in set. Elapsed: 4.409 sec. Processed 2.20 billion rows, 17.59 GB (498.66 million rows/s., 3.99 GB/s.)

Please cherry-pick this commit: c7618ea
I reordered the settings and removed some of the obsolete settings.

It's tricky that the function count and count(DISTINCT ...) and some other functions have to continue to return zero value when aggregate_functions_null_for_empty is enabled (if we aim for SQL compatibility), in contrast to countOrNull, uniqExactOrNull, etc...

It is controlled by AggregateFunctionProperties.

It's tricky that the function count and count(DISTINCT ...) and some other functions have to continue to return zero value when aggregate_functions_null_for_empty is enabled (if we aim for SQL compatibility), in contrast to countOrNull, uniqExactOrNull, etc...

So, what is the best way to resolve this?

Look at the function properties (from AggregateFunctionFactory) and don't rewrite functions that should not return NULL.

@ucasfl We forgot about one little thing...

For -State and -Merge functions we need to add -OrNull before, not after...
Need to check how it will work.

@ucasfl We forgot about one little thing...

For -State and -Merge functions we need to add -OrNull before, not after...
Need to check how it will work.

Maybe not only -State and -Merge, but also -If, -MergeState?

Yes, -MergeState definitely.

About -If - let's check if the behaviour of -IfOrNull different with -OrNullIf.

SELECT sumIfOrNull(1, 0)

Query id: cfeae055-758d-4482-9cda-0ebac2bcca5f

┌─sumIfOrNull(1, 0)─┐
│                 0 │
└───────────────────┘
SELECT sumOrNullIf(1, 0)

Query id: eb9b740c-155e-46e4-9d44-5d67e6d2fb25

┌─sumOrNullIf(1, 0)─┐
│              ᴺᵁᴸᴸ │
└───────────────────┘

It's different.

And there may be multiple combinators in an aggregate function, such as avgIfState, sumStateForEach. I think for aggregate functions with combinators, we shoule move -OrNull to the first when rewrite?