Support for passing aggregate functions as arguments to other functions

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

Start by reproducing the datediff query with MIN and MAX in the SQL parser and optimizer, then inspect the SqlOptimiser and SqlParser paths mentioned in the issue. The work is complete when the aggregate-argument form is accepted and produces the equivalent rewritten query behavior without regressing existing aggregate or function-call handling.