time_weight OrderError

[Wintermute79]

Relevant system information:

• OS: Ubuntu 14.11-1.pgdg22.04+1
• PostgreSQL version: PostgreSQL 14.11 (Ubuntu 14.11-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
• TimescaleDB Toolkit version: 1.18.0
• Installation method: docker

Describe the bug
time_weight fails with error in console and in C# with Npgsql but actually works fine in DBeaver 24.0.0.

To Reproduce

Query:
SELECT average(time_weight('LOCF', time, e5)) as e5 FROM prices WHERE time >= CURRENT_DATE - 1 AND time < CURRENT_DATE;

DDL:

CREATE TABLE IF NOT EXISTS prices
(
    id integer NOT NULL,
    "time" timestamp with time zone NOT NULL,
    e5 numeric(4,3)
);

-- hypertable
SELECT create_hypertable('prices', 'time');

-- default index created by timescaledb
CREATE INDEX prices_time_idx ON prices USING btree ("time" DESC);

-- additional index for faster queries by id and time
CREATE INDEX prices_id_time_idx ON prices USING btree (id ASC, "time" DESC);

Expected behavior
A floating point number like 1.836 (like in DBeaver)

Actual behavior

ERROR:  called `Result::unwrap()` on an `Err` value: OrderError

[wiboticalex]

I'm running into what seems to be the same issue and get

ERROR:  called `Result::unwrap()` on an `Err` value: OrderError

as a response to my query.

OS: Debian 12.2
PostgreSQL: PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
TimescaleDB Toolkit version: 1.18.0 (also tried the latest development version 1.19.0-dev)
Installation method: compiled from source

Query:

SELECT average(time_weight('Linear', "time", "voltage"))
FROM chargelogdata
WHERE uuid = 'a8fa74bb-d000-4751-a26e-ec81b90664cf'::uuid

Interestingly, making a simple change by adding

GROUP BY time_bucket('9999 years'::interval, time)

to the end of the query allows it to run and give the correct answer.

Also, I am able to run the query against shorter sequences of data and do not need the additional GROUP BY. The query that has problems looks at 201939 rows.

Update:
I used explain on the query and it seemed like the common pattern for queries that work was the lack of a "Gather" step before the final Aggregate. It turns out that I can make all queries work by executing

SET max_parallel_workers_per_gather = 0;

first. Setting this variable also causes the execution plan to change as well, dropping the "Gather" step.

[owl-with-beard]

Same issue here.

• host OS: Ubuntu 24.04.1 LTS
• postgreSQL version: PostgreSQL 14.13 (Ubuntu 14.13-1.pgdg22.04+1) on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
• TimescaleDB version: 2.16.1
• TimescaleDB Toolkit version: 1.18.0
• Installation method: docker

Query:

SELECT 
   integral(time_weight('linear', time, case WHEN p_sum < 0 THEN 0 ELSE p_sum end)) / 3600 as energy_total_discharge,
   integral(time_weight('linear', time, case WHEN p_sum > 0 THEN 0 ELSE p_sum end)) / 3600 as energy_total_charge
FROM battery_table
WHERE time >= '2025-01-01' and time < '2025-01-09' AND sensor_id = 'abcd1234'

ERROR: called Result::unwrap() on an Err value: OrderError
SQL state: XX000

The suggestion of @wiboticalex really did work, but it's not really a viable fix for production workflows.
I don't know Rust or the inner workings of the toolkit, can someone more knowledgeable guess what's going on here ?

Best regards