time_bucket_gapfill cannot infer start and finish from subquery

[scimad]

What type of bug is this?

Unexpected error

What subsystems and features are affected?

Gapfill

What happened?

time_bucket_gapfill cannot infer start and finish from subquery.
This doesn't work:

select
	time_bucket_gapfill('5 minutes',
	created_at) bucket,
	max(value_float8)
from
	test t
where 
	t.created_at < (select max(created_at) from test t2 )
	and t.created_at > (select min(created_at) from test t2 )
group by
	bucket;

Following queries (for time_bucket and time_bucket_gapfill) works though:
TIME_BUCKET:

select
	time_bucket('5 minutes',
	created_at) bucket,
	max(value_float8)
from
	test t
where 
	t.created_at < (select max(created_at) from test t2 )
	and t.created_at > (select min(created_at) from	test t2 )
group by
	bucket;

TIME_BUCKET_GAPFILL:

select
	time_bucket_gapfill('1 hour', created_at) bucket, max(value_float8)
from
	test t
where
	t.created_at < '2025-01-18 19:08:30.628 +0000'
	and t.created_at > '2025-01-18 01:07:04.205 +0000'
group by
	bucket;

TimescaleDB version affected

2.16.1

PostgreSQL version used

15

What operating system did you use?

Mac OS X

What installation method did you use?

Homebrew

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

SQL Error [22023]: ERROR: missing time_bucket_gapfill argument: could not infer start from WHERE clause
  Hint: Specify start and finish as arguments or in the WHERE clause.


org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [22023]: ERROR: missing time_bucket_gapfill argument: could not infer start from WHERE clause
  Hint: Specify start and finish as arguments or in the WHERE clause.
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:614)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$2(SQLQueryJob.java:505)
	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:194)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:524)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:976)
	at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:4155)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123)
	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:194)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5152)
	at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:115)
	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: org.postgresql.util.PSQLException: ERROR: missing time_bucket_gapfill argument: could not infer start from WHERE clause
  Hint: Specify start and finish as arguments or in the WHERE clause.
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:502)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:419)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:341)
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:326)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:302)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:297)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:330)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
	... 12 more

How can we reproduce the bug?

CREATE TABLE test (
	created_at timestamptz DEFAULT CURRENT_TIMESTAMP NULL,
	value_float8 float8 NULL,
);



select
	time_bucket_gapfill('5 minutes',
	created_at) bucket,
	max(value_float8)
from
	test t
where 
	t.created_at < (select max(created_at) from test t2 )
	and t.created_at > (select min(created_at) from test t2 )
group by
	bucket;

[mkindahl]

@scimad Thank you for the bug report! Trivial to reproduce using your example.