`sqlx::query!` macros do not work with TimescaleDB Hyperfunctions due to query parts being optimized away

[Erik1000]

PostgreSQL log:

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.
STATEMENT:  EXPLAIN (VERBOSE, FORMAT JSON) EXECUTE sqlx_s_4(NULL, NULL, NULL)

Description

I have used SQLx together with "normal" postgres for years. Now I am using TimescaleDB and thought that sqlx would work as well, because it is basically postgres with extras.

Unfortunately, queries using Hyperfunctions throw an error.

Reproduction steps

Consider this example setup:

TimescaleDB:

docker run -d --name timescaledb -e POSTGRES_DB=timescaledb -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=password -p 5432:5432 timescale/timescaledb-ha:pg17

CREATE TABLE test (time TIMESTAMPTZ, foo int);

This query would work in sqlx::query!:

SELECT time_bucket_gapfill('1m', time) AS one_min
FROM test
WHERE
    foo = 3 AND
    time < NOW() AND
    time > NOW() - INTERVAL '2m'
GROUP BY one_min;

but as soon as I introduce parameters, it does not:

let a = chrono::Utc::now();
let b = a + chrono::TimeDelta::days(1);
sqlx::query!(
        r#"
SELECT time_bucket_gapfill('1m', time) AS one_min
FROM test
WHERE
    foo = $1 AND
    time < $2 AND
    time > $3
GROUP BY one_min;
        "#,
        3,
        a,
        b
    )
    .fetch_one(&pool)
    .await?;

 error returned from database: missing time_bucket_gapfill argument: could not infer start from WHERE clause

The reason seems to lie in the way SQLx runs the queries against the database at compile time, setting them to null which leads to the statements (WHERE clause in this case) being optimized away, which breaks inference.
Basically, I think what the database receives is this:

SELECT time_bucket_gapfill('1m', time) AS one_min
FROM test
WHERE
    foo = null AND
    time < NOW() AND
    time > NOW() - INTERVAL '2m'
GROUP BY one_min;

which throws the same error.

@ppoum opened an issue at the timescaledb repo two weeks ago and the response suggested using PREPARE (see here) since it would be suffice, but I am not familar enough with SQLx and Postgres to evaluate this.

whole code:

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    // Create a connection pool
    //  for MySQL/MariaDB, use MySqlPoolOptions::new()
    //  for SQLite, use SqlitePoolOptions::new()
    //  etc.
    let pool = sqlx::postgres::PgPoolOptions::new()
        .max_connections(5)
        .connect("postgres://postgres:password@localhost/timescaledb")
        .await?;

    let a = chrono::Utc::now();
    let b = a + chrono::TimeDelta::days(1);

    sqlx::query!(
        r#"
SELECT time_bucket_gapfill('1m', time) AS one_min
FROM test
WHERE
    foo = 3 AND
    time < NOW() AND
    time > NOW() - INTERVAL '2m'
GROUP BY one_min;"#
    );

    sqlx::query!(
        r#"
SELECT time_bucket_gapfill('1m', time) AS one_min
FROM test
WHERE
    foo = $1 AND
    time < $2 AND
    time > $3
GROUP BY one_min;
        "#,
        3,
        a,
        b
    )
    .fetch_one(&pool)
    .await?;

    Ok(())
}

I think it would be really awesome if we were able to use all the great things from SQLX together with TimescaleDB for time series databases.

Also see @poum first post at the timescaledb forum: https://forum.tigerdata.com/forum/t/time-bucket-gapfill-could-not-infer-start-from-where-clause/3284
and their issue timescale/timescaledb#8525

SQLx version

0.8.6

Enabled SQLx features

"runtime-tokio","tls-rustls-ring-webpki","postgres","chrono","macros"

Database server and version

PostgreSQL 17.6 timescaledb.last_tuned_version = '0.18.1

Operating system

Docker

Rust version

rustc 1.91.0-nightly (7aef4bec4 2025-09-01)