[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