Support for passing aggregate functions as arguments to other functions

[nwoolmer]

Is your feature request related to a problem?

A user encountered issues with the following query:

SELECT datediff('d', 
  (SELECT MIN(timestamp) FROM sensor_values), 
  (SELECT MAX(timestamp) FROM sensor_values)
) AS nb_days
FROM sensor_values;

which returns an error:

unexpected argument for function: datediff. expected args: (CHAR,TIMESTAMP,TIMESTAMP). actual args: (CHAR constant,CURSOR,CURSOR)

This is because SELECT does not support sub-queries in the column list.

The solution was to rewrite the query to:

SELECT datediff('d', min_ts, max_ts) AS nb_days
FROM (
  SELECT MIN(timestamp) min_ts, MAX(timestamp) max_ts
  FROM sensor_values
);

One might have thought that the min/max aggregates could have been used directly within the select i.e:

SELECT datediff('d', MIN(timestamp), MAX(timestamp)) AS nb_days
  FROM sensor_values

However, this is not supported, and returns an error:

Aggregate function cannot be passed as an argument

Describe the solution you'd like.

Where appropriate, queries containing aggregate functions as arguments should be re-written in SqlOptimiser to the example working query.

Alternatively, SqlParser could be updated to correctly parse aggregate functions as arguments, but this may have more side-effects.

Describe alternatives you've considered.

The workaround listed above.

Full Name:

Nick Woolmer

Affiliation:

QuestDB

Additional context

No response