Convince Aggregates to return NULL instead of NaN

Start by reading src/deparse.c, src/binary.cpp, and src/http.c alongside the ClickHouse aggregate_functions_null_for_empty behavior described in the issue. The attempted approaches show incompatibilities with groupArray(), array_agg(), AggregateFunction, and SimpleAggregateFunction columns, but the issue does not define a concrete implementation or completion criterion.

ClickHouse aggregates don't return NULL when they process no rows. Instead they return values like 0 for integers, NaN for floats and [] for arrays:

:) select sumIf(method_byte, false) FROM system.codecs;

   ┌─sumIf(method_byte, false)─┐
1. │                         0 │
   └───────────────────────────┘

:) select avgIf(method_byte, false) FROM system.codecs;

   ┌─avgIf(method_byte, false)─┐
1. │                       nan │
   └───────────────────────────┘

:) select groupArrayIf(name, false), from system.codecs;

   ┌─groupArrayIf(name, false)─┐
1. │ []                        │
   └───────────────────────────┘

This behavior conflicts with the SQL standard and Postgres, which do return NULL when no rows are processed.

try=# select sum(relpages) filter (where false) from pg_catalog.pg_class;
  sum   
--------
 [null]

Time: 4.614 ms
try=# select avg(relpages) filter (where false) from pg_catalog.pg_class;
  avg   
--------
 [null]

Time: 2.605 ms
try=# select array_agg(relpages) filter (where false) from pg_catalog.pg_class;
 array_agg 
-----------
 [null]

This can cause problems when one executes a query against a ClickHouse foreign table and expects to get NULLs for no input. An example is this HouseClick query

SELECT                                                     
 round(avg(price) FILTER (WHERE town='ILMINSTER' AND district='SOUTH SOMERSET' AND postcode1='TA19')) AS filter_avg,
 round(avg(price)) AS avg,                                                               
 EXTRACT(YEAR FROM date) AS year                               
 FROM public.uk_price_paid
 GROUP BY year               
 ORDER BY year ASC;

For the native Postgres table, the last two rows are:

     [null] | 376468 | 2024
     [null] | 365872 | 2025

But for the foreign tables, they're:

     NaN | 376468 | 2024
     NaN | 365872 | 2025

ClickHouse provides a setting, aggregate_functions_null_for_empty, intended to make aggregates with now inputs return NULL; it does so by appending OrNull to the functions:

:) SET aggregate_functions_null_for_empty = 1

Ok.

:) select sumIf(method_byte, false) FROM system.codecs;

   ┌─sumIf(method_byte, false)─┐
1. │                      ᴺᵁᴸᴸ │
   └───────────────────────────┘

:) select avgIf(method_byte, false) FROM system.codecs;

   ┌─avgIf(method_byte, false)─┐
1. │                      ᴺᵁᴸᴸ │
   └───────────────────────────┘

Unfortunately, this setting breaks groupArray, among other aggregate functions that work with nested values:

:) select groupArrayIf(name, false), from system.codecs;

Received exception from server (version 25.9.2):
Code: 43. DB::Exception: Received from localhost:9000. DB::Exception: Nested type Array(String) cannot be inside Nullable type. (ILLEGAL_TYPE_OF_ARGUMENT)

I thought this might be acceptable, so made a couple of attempts to enable this feature. Was was to set aggregate_functions_null_for_empty for every query:

diff --git a/src/binary.cpp b/src/binary.cpp
index 689ce57..1b585a6 100644
--- a/src/binary.cpp
+++ b/src/binary.cpp
@@ -181,9 +181,17 @@ ch_binary_response_t * ch_binary_simple_query(
 	{
 		resp = new ch_binary_response_t();
 		values = new std::vector<std::vector<clickhouse::ColumnRef>>();
-
-		client->SelectCancelable(
-			std::string(query), [&resp, &values, &check_cancel](const Block & block) {
+		client->Select(
+			clickhouse::Query(query).SetQuerySettings(QuerySettings{
+				/*
+				 * Enable SQL compatibility by having aggregate functions
+				 * return NULL instead of NaN when no values are aggregated.
+				 * Unfortunately this breaks array_agg()/groupArray() but
+				 * makes all other aggregates behave as expected in a Postgres
+				 * context.
+				 */
+				{"aggregate_functions_null_for_empty", QuerySettingsField{ "1", 1 }},
+			}).OnDataCancelable([&resp, &values, &check_cancel](const Block & block) {
 				if (check_cancel && check_cancel())
 				{
 					set_resp_error(resp, "query was canceled");
@@ -210,7 +218,8 @@ ch_binary_response_t * ch_binary_simple_query(
 
 				values->push_back(std::move(vec));
 				return true;
-			});
+			})
+		);
 
 		resp->values = (void *)values;
 	}
diff --git a/src/http.c b/src/http.c
index 44e69d0..73bdf19 100644
--- a/src/http.c
+++ b/src/http.c
@@ -147,9 +147,17 @@ ch_http_response_t *ch_http_simple_query(ch_http_connection_t *conn, const char
 
 	assert(conn && conn->curl);
 
-	/* construct url */
-	url = malloc(conn->base_url_len + 37 + 12 /* query_id + ?query_id= */);
-	sprintf(url, "%s?query_id=%s", conn->base_url, resp->query_id);
+	/*
+	 * Enable SQL compatibility by having aggregate functions return NULL
+	 * instead of NaN when no values are aggregated. Unfortunately this breaks
+	 * array_agg()/groupArray() but makes all other aggregates behave as
+	 * expected in a Postgres context.
+	 */
+	const char *params = "aggregate_functions_null_for_empty=1";
+
+	/* construct url: query_id + ?query_id= + params */
+	url = malloc(conn->base_url_len + 37 + 12 + strlen(params));
+	sprintf(url, "%s?query_id=%s&%s", conn->base_url, resp->query_id, params);
 
 	/* constant */
 	errbuffer[0] = '\0';

Unfortunately, in addition to breaking array_agg()/groupArray(), it also breaks AggregateFunction and SimpleAggregateFunction columns as documented in comments on ClickHouse/ClickHouse#38738.

I also tried to manually append OrNull:

diff --git a/src/deparse.c b/src/deparse.c
index d6ecc24..f5eeecc 100644
--- a/src/deparse.c
+++ b/src/deparse.c
@@ -3362,6 +3362,7 @@ deparseAggref(Aggref *node, deparse_expr_cxt *context)
 	uint8	brcount = 1;
 	bool	use_variadic;
 	int 	first_arg = 0;
+	char    *name = get_func_name(node->aggfnoid);
 
 	/* Only basic, non-split aggregation accepted. */
 	Assert(node->aggsplit == AGGSPLIT_SIMPLE);
@@ -3399,6 +3400,18 @@ deparseAggref(Aggref *node, deparse_expr_cxt *context)
 		appendStringInfoString(buf, "If");
 	}
 
+	/*
+	 * ClickHouse aggregates return NaN instead of NULL when no values were
+	 * input. Ideally we'd `SET aggregate_functions_null_for_empty` to make it
+	 * compatible by appending `OrNull` to every aggregate function.
+	 * Unfortunately that currenltly breaks array-returning aggregage functions
+	 * like groupArray()/array_agg(). So manually append `OrNull` for
+	 * aggregtes other than array_agg. Unfortunately, there is currently no
+	 * way to return a NULL instead of an empty array.
+	*/
+	if (strcmp("array_agg", name) != 0 && strcmp("count", name) != 0)
+		appendStringInfoString(buf, "OrNull");
+
 	appendStringInfoChar(buf, '(');
 
 	/* Explained below. */
@@ -3447,7 +3460,7 @@ deparseAggref(Aggref *node, deparse_expr_cxt *context)
 			* Client::GetServerInfo() to deparse_expr_cxt so we can allow * to be
 			* passed through for the fixed version.
 			*/
-			omit_star = node->aggfilter && node->aggdistinct == NIL && strcmp(buf->data, "count");
+			omit_star = node->aggfilter && node->aggdistinct == NIL && !strcmp(name, "count");
 			if (context->func && context->func->cf_type == CF_SIGN_COUNT)
 			{
 				Assert(fpinfo && fpinfo->ch_table_engine == CH_COLLAPSING_MERGE_TREE);

This allows array_agg() to work, but still causes compatibility problems with AggregateFunction and SimpleAggregateFunction columns.

So for now I think the thing to do is to ask clients to properly handle these default values. In some cases, like sum() returning 0 and array_agg() returning an empty array they might be preferable! But NaNs will require special treatment.