[Bug]: ORDER/GROUP BY expression not found in targetlist

[jledentu]

What type of bug is this?

Unexpected error

What subsystems and features are affected?

Query executor

What happened?

Some SELECT requests on an hypertable return the error below:

ERROR:  ORDER/GROUP BY expression not found in targetlist 

SQL state: XX000

TimescaleDB version affected

2.14.2

PostgreSQL version used

15.6

What operating system did you use?

Ubuntu 22.04

What installation method did you use?

Docker

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

No response

How can we reproduce the bug?

1. Run docker run -p 5432:5432 -e POSTGRES_PASSWORD=password timescale/timescaledb-ha:pg15.6-ts2.14.2
2. Connect to the database
3. Execute the queries below:

CREATE TABLE test (
    date timestamp with time zone NOT NULL,
    quantity double precision,
    "isText" boolean
);

CREATE INDEX IF NOT EXISTS test_idx ON test (date) WHERE "isText" IS NULL;
SELECT create_hypertable('test', 'date', chunk_time_interval => interval 'P7D', create_default_indexes => false);

INSERT INTO test(date, quantity)
SELECT date, round((random() * (100-3) + 3)::NUMERIC) AS quantity
FROM generate_series('2023-01-01T00:00:00+01:00', '2023-05-01T00:00:00+01:00', interval 'PT10M') AS t(date);

SELECT time_bucket_gapfill('PT1H', date - (- interval 'P1Y'), start => '2024-01-01T00:00:00+01:00', finish => '2024-04-26T00:00:00+02:00') AS date, first(quantity, date) AS quantity FROM test WHERE date >= '2024-01-01T00:00:00+01:00'::timestamp with time zone + (- interval 'P1Y') AND date < '2024-04-26T00:00:00+02:00'::timestamp with time zone + (- interval 'P1Y') AND "isText" IS NULL AND quantity != 0 AND date = date - (- interval 'P1Y') + (- interval 'P1Y') GROUP BY 1;

It seems that the partial index is not well handled. If I remove the condition WHERE "isText" IS NULL; on the index, the error disappears.

[gayyappan]

I am able to repro this:

test=# SELECT time_bucket_gapfill('PT1H', date - (- interval 'P1Y'), start => '2024-01-01T00:00:00+01:00', finish => '2024-04-26T00:00:00+02:00') AS date, first(quantity, date) AS quantity FROM test WHERE date >= '2024-01-01T00:00:00+01:00'::timestamp with time zone + (- interval 'P1Y') AND date < '2024-04-26T00:00:00+02:00'::timestamp with time zone + (- interval 'P1Y') AND "isText" IS NULL AND quantity != 0 AND date = date - (- interval 'P1Y') + (- interval 'P1Y') GROUP BY 1;
ERROR:  ORDER/GROUP BY expression not found in targetlist

[jledentu]

I also met this bug on tables using full indexes (without condition), even if I can't reproduce the error on a minimal case. It may depend on the amount of data, I guess. As workaround, I dropped indexes on concerned tables, resulting in degraded performances. Another solution is to not use time_bucket_gapfill, but it doesn't avoid this other bug: https://github.com/timescale/timescaledb/issues/7097

[RobAtticus]

I think this was fixed with https://github.com/timescale/timescaledb/pull/7091 which went out in 2.15.3

[jledentu]

@RobAtticus Hi, no I reproduce the bug in 2.15.3

docker run -p 5433:5432 -e POSTGRES_PASSWORD=password -e TS_TUNE_MEMORY="1GB" timescale/timescaledb-ha:pg15.7-ts2.15.3

I use PG15, and the fix you mention targets PG16.

Note that the bug doesn't occur if I disable optimizations:

SET LOCAL timescaledb.enable_optimizations TO true;

[RobAtticus]

cc @fabriziomello since you did the original fix

[fabriziomello]

cc @fabriziomello since you did the original fix

Looks like it is another problem. I had a look on it some days ago but didn't found the root cause. Will dig more on it next week.

[fabriziomello]

The PR #7161 will fix it.

[jledentu]

Thanks, I confirm that the 2.16.0 version fixes my error.