[Bug]: error when joining two hypertables with aggregate query with CURSOR #4774

cocowalla · 2022-09-29T14:52:13Z

What type of bug is this?

Unexpected error

What subsystems and features are affected?

Query executor

What happened?

When running aggregate queries that join two hypertables, an error occurs: ORDER/GROUP BY expression not found in targetlist

TimescaleDB version affected

2.8.0 and 2.9.0-dev

PostgreSQL version used

14.5

What operating system did you use?

Debian Bullseye

What installation method did you use?

Source

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

ORDER/GROUP BY expression not found in targetlist

How can we reproduce the bug?

Before starting, ensure you are using TSDB 2.9.0-dev (I built it from the main branch). This bug was somewhat apparent in 2.8.0 as well, but overlapped a lot with #4418.

Repro 1

First, create two hypertables:

DO LANGUAGE plpgsql $tran$
    DECLARE _ret text;
BEGIN

    CREATE TABLE table_a (
        id                uuid                        NOT NULL,
        timestamp         timestamp with time zone    NOT NULL,
        message_type      integer                     NOT NULL
    );

    CREATE TABLE table_b (
        id                uuid                        NOT NULL,
        timestamp         timestamp with time zone    NOT NULL,
        is_authorized     boolean                     NOT NULL
    );

    SELECT * INTO _ret FROM create_hypertable('table_a', 'timestamp', create_default_indexes => true, chunk_time_interval => interval '1 week');
    RAISE NOTICE 'Table A Hypertable created %', _ret;

    SELECT * INTO _ret FROM create_hypertable('table_b', 'timestamp', create_default_indexes => true, chunk_time_interval => interval '1 week');
    RAISE NOTICE 'Table B Hypertable created %', _ret;

    INSERT INTO table_a (id, timestamp, message_type) VALUES ('d0295c43-752c-42e7-b2c3-f03ad2e9f4f3', current_timestamp, 1);
    INSERT INTO table_b (id, timestamp, is_authorized) VALUES ('d0295c43-752c-42e7-b2c3-f03ad2e9f4f3', current_timestamp, false);

END;
$tran$;

Now execute an aggregate query that uses both tables:

SELECT
    time_bucket_gapfill('1 hour', a.timestamp) AS bucket,
    COALESCE(COUNT(*), 0) AS ct
FROM
    table_a AS a
LEFT JOIN
    table_b AS b ON
        b.id = a.id AND
        b.timestamp = a.timestamp
WHERE
    a.timestamp > NOW() - INTERVAL '1 week' AND a.timestamp < NOW()
GROUP BY
    bucket
ORDER BY
    1 ASC

Whether an INNER or LEFT join, this will result in the error:

ORDER/GROUP BY expression not found in targetlist

Repro 2

If we modify things slightly, using our own indices that include both the timestamp and id columns, then the issue only appears with INNER JOIN - not with LEFT JOIN.

First, create two hypertables:

DO LANGUAGE plpgsql $tran$
    DECLARE _ret text;
BEGIN

    CREATE TABLE table_a (
        id                uuid                        NOT NULL,
        timestamp         timestamp with time zone    NOT NULL,
        message_type      integer                     NOT NULL
    );
    CREATE UNIQUE INDEX table_a_idx_timestamp ON table_a ("message_type", "timestamp" DESC, "id");

    CREATE TABLE table_b (
        id                uuid                        NOT NULL,
        timestamp         timestamp with time zone    NOT NULL,
        is_authorized     boolean                     NOT NULL
    );
    CREATE UNIQUE INDEX table_b_idx_timestamp ON table_b ("timestamp" DESC, "id");

    SELECT * INTO _ret FROM create_hypertable('table_a', 'timestamp', create_default_indexes => false, chunk_time_interval => interval '1 week');
    RAISE NOTICE 'Table A Hypertable created %', _ret;

    SELECT * INTO _ret FROM create_hypertable('table_b', 'timestamp', create_default_indexes => false, chunk_time_interval => interval '1 week');
    RAISE NOTICE 'Table B Hypertable created %', _ret;

    INSERT INTO table_a (id, timestamp, message_type) VALUES ('d0295c43-752c-42e7-b2c3-f03ad2e9f4f3', current_timestamp, 1);
    INSERT INTO table_b (id, timestamp, is_authorized) VALUES ('d0295c43-752c-42e7-b2c3-f03ad2e9f4f3', current_timestamp, false);

END;
$tran$;

Now execute an aggregate query that uses both tables:

SELECT
    time_bucket_gapfill('1 hour', a.timestamp) AS bucket,
    COALESCE(COUNT(*), 0) AS ct
FROM
    table_a AS a
INNER JOIN
    table_b AS b ON
        b.id = a.id AND
        b.timestamp = a.timestamp
WHERE
    a.timestamp > NOW() - INTERVAL '1 week' AND a.timestamp < NOW()
GROUP BY
    bucket
ORDER BY
    1 ASC

This will result in the error:

ORDER/GROUP BY expression not found in targetlist

Note that if you include any of the joined table columns in the WHERE clause, it fails with LEFT JOIN too, e.g.:

SELECT
    time_bucket_gapfill('1 hour', a.timestamp) AS bucket,
    COALESCE(COUNT(*), 0) AS ct
FROM
    table_a AS a
LEFT JOIN
    table_b AS b ON
        b.id = a.id AND
        b.timestamp = a.timestamp
WHERE
    a.timestamp > NOW() - INTERVAL '1 week' AND a.timestamp < NOW() AND
    b.is_authorized = true
GROUP BY
    bucket
ORDER BY
    1 ASC

The text was updated successfully, but these errors were encountered:

cocowalla · 2022-09-29T14:53:11Z

Related to #4418, #4760

svenklemm · 2022-09-30T07:51:08Z

All of the queries you posted work for me on main-branch. Are you sure you tested latest version?

sven@i4774_2[3476275]=# SELECT commit_hash,commit_time FROM _timescaledb_internal.get_git_commit();
               commit_hash                |      commit_time       
------------------------------------------+------------------------
 1d4b9d6977098e74ad10888c2b44e8faf187458b | 2022-09-29 10:36:02+02
(1 row)

cocowalla · 2022-09-30T08:30:35Z

Are you sure you tested latest version?

Yes (I'm on a different time zone, but the commit time and hash are the same):

# SELECT commit_hash,commit_time FROM _timescaledb_internal.get_git_commit();
commit_hash	commit_time
1d4b9d6977098e74ad10888c2b44e8faf187458b	2022-09-29T08:36:02+00:00

I just tried it again, and still get the same error with repros above.

I also rebuilt the Docker image and went again, same problem. I'll create a Docker image that will surely allow you to replicate it.

Also, are you sure you ran the exact script I pasted? I noticed that the error doesn't occur of the tables are empty, hence why I included a couple of INSERT statements.

cocowalla · 2022-09-30T10:56:44Z

@svenklemm

OK, so I have a repro available as a Docker image now!

git clone https://github.com/cocowalla/tsdb-repro-4774.git
cd tsdb-repro-4774
make build
make run

At this point you'll have a Postgres server running with TSDB 2.9.0-dev (commit 1d4b9d6977098e74ad10888c2b44e8faf187458b). Next step is to connect to the app database as postgres (no password required) and create hypertables as before:

DO LANGUAGE plpgsql $tran$
    DECLARE _ret text;
BEGIN

    CREATE TABLE table_a (
        id                uuid                        NOT NULL,
        timestamp         timestamp with time zone    NOT NULL,
        message_type      integer                     NOT NULL
    );

    CREATE TABLE table_b (
        id                uuid                        NOT NULL,
        timestamp         timestamp with time zone    NOT NULL,
        is_authorized     boolean                     NOT NULL
    );

    SELECT * INTO _ret FROM create_hypertable('table_a', 'timestamp', create_default_indexes => true, chunk_time_interval => interval '1 week');
    RAISE NOTICE 'Table A Hypertable created %', _ret;

    SELECT * INTO _ret FROM create_hypertable('table_b', 'timestamp', create_default_indexes => true, chunk_time_interval => interval '1 week');
    RAISE NOTICE 'Table B Hypertable created %', _ret;

    INSERT INTO table_a (id, timestamp, message_type) VALUES ('d0295c43-752c-42e7-b2c3-f03ad2e9f4f3', current_timestamp, 1);
    INSERT INTO table_b (id, timestamp, is_authorized) VALUES ('d0295c43-752c-42e7-b2c3-f03ad2e9f4f3', current_timestamp, false);

END;
$tran$;

Finally, run the query, which will fail:

SELECT
    time_bucket_gapfill('1 hour', a.timestamp) AS bucket,
    COALESCE(COUNT(*), 0) AS ct
FROM
    table_a AS a
LEFT JOIN
    table_b AS b ON
        b.id = a.id AND
        b.timestamp = a.timestamp
WHERE
    a.timestamp > NOW() - INTERVAL '1 week' AND a.timestamp < NOW()
GROUP BY
    bucket
ORDER BY
    1 ASC

svenklemm · 2022-09-30T14:18:41Z

I still cannot reproduce:

app=# SELECT
app-#     time_bucket_gapfill('1 hour', a.timestamp) AS bucket,
app-#     COALESCE(COUNT(*), 0) AS ct
app-# FROM
app-#     table_a AS a
app-# LEFT JOIN
app-#     table_b AS b ON
app-#         b.id = a.id AND
app-#         b.timestamp = a.timestamp
app-# WHERE
app-#     a.timestamp > NOW() - INTERVAL '1 week' AND a.timestamp < NOW()
app-# GROUP BY
app-#     bucket
app-# ORDER BY
app-#     1 ASC;
         bucket         | ct
------------------------+----
 2022-09-23 14:00:00+00 |  0
 2022-09-23 15:00:00+00 |  0
 2022-09-23 16:00:00+00 |  0
 2022-09-23 17:00:00+00 |  0
 2022-09-23 18:00:00+00 |  0
 2022-09-23 19:00:00+00 |  0
 2022-09-23 20:00:00+00 |  0
 2022-09-23 21:00:00+00 |  0
 2022-09-23 22:00:00+00 |  0
 2022-09-23 23:00:00+00 |  0
 2022-09-24 00:00:00+00 |  0
 2022-09-24 01:00:00+00 |  0
 2022-09-24 02:00:00+00 |  0
 2022-09-24 03:00:00+00 |  0
 2022-09-24 04:00:00+00 |  0
 2022-09-24 05:00:00+00 |  0
 2022-09-24 06:00:00+00 |  0
 2022-09-24 07:00:00+00 |  0
 2022-09-24 08:00:00+00 |  0
 2022-09-24 09:00:00+00 |  0
 2022-09-24 10:00:00+00 |  0
 2022-09-24 11:00:00+00 |  0
 2022-09-24 12:00:00+00 |  0
 2022-09-24 13:00:00+00 |  0
 2022-09-24 14:00:00+00 |  0
 2022-09-24 15:00:00+00 |  0
 2022-09-24 16:00:00+00 |  0
 2022-09-24 17:00:00+00 |  0
 2022-09-24 18:00:00+00 |  0
 2022-09-24 19:00:00+00 |  0
 2022-09-24 20:00:00+00 |  0
 2022-09-24 21:00:00+00 |  0
 2022-09-24 22:00:00+00 |  0
 2022-09-24 23:00:00+00 |  0
 2022-09-25 00:00:00+00 |  0
 2022-09-25 01:00:00+00 |  0
 2022-09-25 02:00:00+00 |  0
 2022-09-25 03:00:00+00 |  0
 2022-09-25 04:00:00+00 |  0
 2022-09-25 05:00:00+00 |  0
 2022-09-25 06:00:00+00 |  0
 2022-09-25 07:00:00+00 |  0
 2022-09-25 08:00:00+00 |  0
 2022-09-25 09:00:00+00 |  0
 2022-09-25 10:00:00+00 |  0
 2022-09-25 11:00:00+00 |  0
 2022-09-25 12:00:00+00 |  0
 2022-09-25 13:00:00+00 |  0
 2022-09-25 14:00:00+00 |  0
 2022-09-25 15:00:00+00 |  0
 2022-09-25 16:00:00+00 |  0
 2022-09-25 17:00:00+00 |  0
 2022-09-25 18:00:00+00 |  0
 2022-09-25 19:00:00+00 |  0
 2022-09-25 20:00:00+00 |  0
 2022-09-25 21:00:00+00 |  0
 2022-09-25 22:00:00+00 |  0
 2022-09-25 23:00:00+00 |  0
 2022-09-26 00:00:00+00 |  0
 2022-09-26 01:00:00+00 |  0
 2022-09-26 02:00:00+00 |  0
 2022-09-26 03:00:00+00 |  0
 2022-09-26 04:00:00+00 |  0
 2022-09-26 05:00:00+00 |  0
 2022-09-26 06:00:00+00 |  0
 2022-09-26 07:00:00+00 |  0
 2022-09-26 08:00:00+00 |  0
 2022-09-26 09:00:00+00 |  0
 2022-09-26 10:00:00+00 |  0
 2022-09-26 11:00:00+00 |  0
 2022-09-26 12:00:00+00 |  0
 2022-09-26 13:00:00+00 |  0
 2022-09-26 14:00:00+00 |  0
 2022-09-26 15:00:00+00 |  0
app=#

svenklemm · 2022-09-30T14:20:45Z

How are you running the queries? table_a and table_b only have 1 row each is your data different?

cocowalla · 2022-09-30T15:28:26Z

The only data in these tables is this, simply because the error does not appear when the tables are empty:

    INSERT INTO table_a (id, timestamp, message_type) VALUES ('d0295c43-752c-42e7-b2c3-f03ad2e9f4f3', current_timestamp, 1);
    INSERT INTO table_b (id, timestamp, is_authorized) VALUES ('d0295c43-752c-42e7-b2c3-f03ad2e9f4f3', current_timestamp, false);

But, given this is a Docker image, you should really be seeing the same results as me - which got me thinking; the only thing that can differ will be how we query. I was using Azure Data Studio, and I guess you were using psql. So... I tried it wih psql, and it works just fine?! 😕

I checked the Postgres logs, and there is one difference - Azure Data Studio silently opens a cursor prior to executing the query, e.g.

DECLARE "e402c1c5-e6ec-436c-a9e8-bebf6df7ac4c" CURSOR WITH HOLD FOR SELECT
    time_bucket_gapfill('1 hour', a.timestamp) AS bucket,
    COALESCE(COUNT(*), 0) AS ct
FROM
    table_a AS a
LEFT JOIN
    table_b AS b ON
        b.id = a.id AND
        b.timestamp = a.timestamp
WHERE
    a.timestamp > NOW() - INTERVAL '1 week' AND a.timestamp < NOW()
GROUP BY
    bucket
ORDER BY
    1 ASC

So, it seems #4418 is actually resolved afterall!

How do you want to proceed from here? Should I close this issue, or do you folks want to fix the problem with cursors?

svenklemm · 2022-10-01T09:56:50Z

I've adjusted the title, it's still a bug but severity is lower since it only occurs with cursors.

cocowalla · 2022-10-04T09:46:02Z

@svenklemm sorry, me again! I've been doing some more testing, and this bug has reared it's head again - without cursors.

To reproduce, create the same 2 hypertables as before:

DO LANGUAGE plpgsql $tran$
    DECLARE _ret text;
BEGIN

    CREATE TABLE table_a (
        id                uuid                        NOT NULL,
        timestamp         timestamp with time zone    NOT NULL,
        message_type      integer                     NOT NULL
    );

    CREATE TABLE table_b (
        id                uuid                        NOT NULL,
        timestamp         timestamp with time zone    NOT NULL,
        is_authorized     boolean                     NOT NULL
    );

    SELECT * INTO _ret FROM create_hypertable('table_a', 'timestamp', create_default_indexes => true, chunk_time_interval => interval '1 week');
    RAISE NOTICE 'Table A Hypertable created %', _ret;

    SELECT * INTO _ret FROM create_hypertable('table_b', 'timestamp', create_default_indexes => true, chunk_time_interval => interval '1 week');
    RAISE NOTICE 'Table B Hypertable created %', _ret;

    INSERT INTO table_a (id, timestamp, message_type) VALUES ('d0295c43-752c-42e7-b2c3-f03ad2e9f4f3', current_timestamp, 1);
    INSERT INTO table_b (id, timestamp, is_authorized) VALUES ('d0295c43-752c-42e7-b2c3-f03ad2e9f4f3', current_timestamp, false);

END;
$tran$;

Then execute:

SELECT
    time_bucket_gapfill('1 day', a.timestamp) AS bucket,
    COALESCE(COUNT(*), 0) AS ct
FROM
    table_a2 AS a
LEFT JOIN
    table_b2 AS b ON
        b.id = a.id AND
        b.timestamp = a.timestamp
WHERE
    a.timestamp > NOW() - INTERVAL '1 week' AND a.timestamp < NOW() - INTERVAL '1 day'
GROUP BY
    bucket
ORDER BY
    1 ASC

It will fail with: [XX000] ERROR: ORDER/GROUP BY expression not found in targetlist.

The problem seems to arise when using time_bucket_gapfill in a query that joins multiple hypertables, where there are zero results for every time_bucket_gapfill bucket. Note that some data does need to be in the table.

Changing the join to an INNER JOIN works around the issue for this particular query, but I have INNER JOIN queries in production code that are failing the same way.

svenklemm · 2022-10-23T12:29:49Z

Doesnt fail for me:

sven@i4774[1238679]=# SELECT
    time_bucket_gapfill('1 day', a.timestamp) AS bucket,
    COALESCE(COUNT(*), 0) AS ct
FROM
    table_a AS a
LEFT JOIN
    table_b AS b ON
        b.id = a.id AND
        b.timestamp = a.timestamp
WHERE
    a.timestamp > NOW() - INTERVAL '1 week' AND a.timestamp < NOW() - INTERVAL '1 day'
GROUP BY
    bucket
ORDER BY
    1 ASC;
         bucket         | ct
------------------------+----
 2022-10-16 02:00:00+02 |  0
 2022-10-17 02:00:00+02 |  0
 2022-10-18 02:00:00+02 |  0
 2022-10-19 02:00:00+02 |  0
 2022-10-20 02:00:00+02 |  0
 2022-10-21 02:00:00+02 |  0
 2022-10-22 02:00:00+02 |  0
(7 rows)

cocowalla · 2022-10-24T19:37:01Z

@svenklemm are you running the same build as before, or a later commit? (I tested with commit 1d4b9d6)

JackMyers001 · 2022-11-30T09:03:55Z

I believe I have also run into this bug, running the latest build of the TimescaleDB Docker Container on a Linux x64 host (tag latest-pg14, container hash b608d69362d4, Timescale internal git commit 65078486025a5cfdd00663f524831619066c5946 | 2022-10-06 06:59:00+00.)

I have three tables, created as follows:

CREATE TABLE sensors (
    sensor_id SERIAL PRIMARY KEY,
    sensor_name VARCHAR(64) UNIQUE NOT NULL
);

CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER REFERENCES sensors,
    value real
);

CREATE TABLE sensor_override (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER REFERENCES sensors,
    value real
);

SELECT create_hypertable('sensor_data', 'time');
SELECT create_hypertable('sensor_override', 'time');

I need the ability in my application to override a sensor for a period of time (e.g. if the user knows that a faulty sensor has been giving incorrect data). I have data points for my sensors every 5 seconds.

I have a query that basically says "give me all data for a given sensor in a given time period where there's no override" (i.e. the resulting graph will be continuous unless an override exists, in which case there will be gaps in the graph). The query for this is as follows:

SELECT
  time_bucket_gapfill('5s', s.time, '2022-11-20T00:00:00Z', '2022-11-22T11:50:11Z') as stime,
  AVG(s.value) as value
FROM
  sensor_data s
WHERE NOT EXISTS (
  SELECT
    NULL
  FROM
    sensor_override ov
  WHERE
    ov.time = s.time AND
    ov.sensor_id = 123 AND
    ov.time BETWEEN '2022-11-20T00:00:00Z' AND '2022-11-22T11:50:11Z'
) AND
  s.time BETWEEN '2022-11-20T00:00:00Z' AND '2022-11-22T11:50:11Z' AND
  s.sensor_id = 123
GROUP BY stime
ORDER BY 1;

This query works. However, if I change the end time to 11:50:10, Postgres throws an error:

ERROR:  ORDER/GROUP BY expression not found in targetlist
SQL state: XX000

Decrease the end time by a second, a minute, an hour, a day? Still errors. Bring forward the start time? Error. Make the query range bigger (i.e. 3 days)? Absolutely fine

For some reason, any query that is less than about 215,411 seconds (or 43083 points at 1 point every 5 sec) fails.

akuzm · 2023-01-16T14:18:04Z

However, if I change the end time to 11:50:10, Postgres throws an error:

Still works fine for me on PG 14 and TimescaleDB 2.9.1. Maybe it's dependent on the data? I also tried eight different query plans: all chunks excluded for both tables, one of the tables, or none of the tables, in runtime or in planning time. Still no error. We need some way to reproduce this.

noudreturn · 2023-06-25T19:52:49Z

I seem to have the same bug in Timescale Cloud. When I increase the query time range, the error disappears.

RafiaSabih · 2023-07-18T12:05:58Z

@noudreturn Would you please give the complete set of steps for reproducing.

github-actions · 2023-11-12T01:47:17Z

Dear Author,

This issue has been automatically marked as stale due to lack of activity. With only the issue description that is currently provided, we do not have enough information to take action. If you have or find the answers we would need, please reach out. Otherwise, this issue will be closed in 30 days.

Thank you!

feikesteenbergen · 2023-12-04T11:13:57Z

I'm getting this error once upgrading to PostgreSQL 16, with TimescaleDB 2.13:

create table dummy(
    measured timestamptz not null,
    device_id int not null,
    value float not null
);
select public.create_hypertable('dummy', 'measured');
insert into dummy
select
    now() - random() * interval '3 months',
    (random()*1000)::int,
    random()
from
    generate_series(1,1000);

SELECT
    1
FROM
    (VALUES (1)) AS _(device_id)
LEFT JOIN (
    SELECT
        device_id,
        last(value, measured) AS last_value
    FROM
        dummy
    GROUP BY
        device_id
) AS biggest USING (device_id);

PostgreSQL 15, TimescaleDB 2.13.0:

 ?column? 
----------
        1
(1 row)

PostgreSQL 16, TimescaleDB 2.13.0:

ERROR:  XX000: ORDER/GROUP BY expression not found in targetlist
LOCATION:  get_sortgroupref_tle, tlist.c:357

antekresic · 2023-12-06T08:52:41Z

Issue has been fixed by #6377

Thanks for reporting this!

kyrias · 2023-12-14T11:15:09Z

I seem to be able to still reproduce this with the latest timescaledev/timescaledb:nightly-pg16 container image using the reduced example below.

CREATE TABLE u (id uuid primary key);
INSERT INTO u (id) values ('ad46125f-7e74-4f28-97c7-f8686cd2d012');

CREATE TABLE p (
    duration interval,
    start_time timestamptz not null,
    user_id uuid references u(id)
);
SELECT create_hypertable('p', 'start_time');
INSERT INTO p (duration, start_time, user_id) values ('02:00:00', '2023-12-14 09:01:03.91968+00', 'ad46125f-7e74-4f28-97c7-f8686cd2d012');

SELECT u.id
FROM users u
LEFT JOIN (
   SELECT FLOOR(EXTRACT (EPOCH FROM SUM(ap.duration)))::INT8 AS "total_login_seconds"
   FROM p ap
   WHERE ap.user_id = 'ad46125f-7e74-4f28-97c7-f8686cd2d012'
   GROUP BY ap.user_id
) ap ON TRUE
WHERE u.id = 'ad46125f-7e74-4f28-97c7-f8686cd2d012'
;

antekresic · 2023-12-14T13:53:57Z

Hm, I just tried it and it works fine.

Can you double-check that you pulled the latest Docker image?

Thanks,

kyrias · 2023-12-18T08:31:55Z

Strange, I've never pulled that image before so not sure what happened, but it does indeed seem to work after doing a podman system reset.

kyrias · 2023-12-19T12:34:56Z

Are there any estimates for when a 2.13.1 release might be cut? This is the last thing currently blocking us from being able to upgrade to PostgreSQL 16.

jnidzwetzki · 2023-12-19T14:09:50Z

Hello @kyrias,

We don't have an exact release date yet. But we target the next release at the end of January or early February.

svenstaro · 2023-12-20T02:30:32Z

I think this is a fairly important fix that should be backported and released in a patch release. After all, PostgreSQL 16 support was advertised and if bugs crop up, we shouldn't have to wait until the next minor release. Please consider this.

jnidzwetzki · 2023-12-20T12:36:25Z

Hi @svenstaro,

Thanks for bringing this up. I discussed this internally. We will build a patch release (2.13.1) at the beginning of January.

xMlex · 2024-04-19T11:04:15Z

Hello!

A have error with query:

select time_bucket('10 minutes', event_at),
       COUNT(*)       as value,
       host

from requests
WHERE event_at > now() - interval '10 minutes'
AND host in ('VM-FAST-WEB')
GROUP BY 1, host
ORDER BY 1;

result(error): [XX000] ERROR: ORDER/GROUP BY expression not found in targetlist

if exclude AND host in ('VM-FAST-WEB') - no problem, query executed.

version:(docker) timescale/timescaledb-ha:pg16.2-ts2.14.2-all

Help please)

xMlex · 2024-04-19T11:41:37Z

on pg 15 no problem

antekresic · 2024-04-22T10:35:43Z

Hey! I've tried replicating your problem on the specified docker image with pg16.2 and TS 2.14.2 but with no luck.

Could you double-confirm that you are in fact running 2.14.2 version of Timescale where you can replicate this issue?

This issue has been fixed before for PG16 and I'm unable to trigger it either on 2.14.2 version or the main development branch.

For reference, here are the steps I tried to reproduce, based on your report:

CREATE TABLE requests (event_at timestamptz, host text , value float);
SELECT create_hypertable('requests', 'event_at', chunk_time_interval=>'7 days'::interval);
insert into requests values (now(), 'VM-FAST-WEB', 1.0);
select time_bucket('10 minutes', event_at),
       COUNT(*)       as value,
       host

from requests
WHERE event_at > now() - interval '10 minutes'
AND host in ('VM-FAST-WEB')
GROUP BY 1, host
ORDER BY 1;
      time_bucket       | value |    host     
------------------------+-------+-------------
 2024-04-22 10:30:00+00 |     1 | VM-FAST-WEB
(1 row)

As you can see, I'm unable to trigger it this way. If you have a better/more consistent way of reproducing this, let me know.

Thanks!

cocowalla added the bug label Sep 29, 2022

cocowalla mentioned this issue Sep 29, 2022

[Bug]: Segmentation fault when INNER JOINing two hypertables #4418

Closed

svenklemm added the waiting-for-author label Sep 30, 2022

github-actions bot removed the waiting-for-author label Sep 30, 2022

svenklemm added the waiting-for-author label Sep 30, 2022

github-actions bot removed the waiting-for-author label Sep 30, 2022

svenklemm added the waiting-for-author label Sep 30, 2022

github-actions bot removed the waiting-for-author label Sep 30, 2022

svenklemm changed the title ~~[Bug]: error when joining two hypertables with aggregate query~~ [Bug]: error when joining two hypertables with aggregate query with CURSOR Sep 30, 2022

jnidzwetzki added the waiting-for-author label Sep 11, 2023

github-actions bot added the no-activity label Nov 12, 2023

timescale-automation removed waiting-for-author no-activity labels Dec 4, 2023

jnidzwetzki added the waiting-for-author label Dec 19, 2023

timescale-automation removed the waiting-for-author label Dec 20, 2023

jnidzwetzki added the waiting-for-author label Dec 20, 2023

timescale-automation removed the waiting-for-author label Apr 19, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

[Bug]: error when joining two hypertables with aggregate query with CURSOR #4774

[Bug]: error when joining two hypertables with aggregate query with CURSOR #4774

cocowalla commented Sep 29, 2022 •

edited

Loading

cocowalla commented Sep 29, 2022

svenklemm commented Sep 30, 2022

cocowalla commented Sep 30, 2022 •

edited

Loading

cocowalla commented Sep 30, 2022 •

edited

Loading

svenklemm commented Sep 30, 2022

svenklemm commented Sep 30, 2022

cocowalla commented Sep 30, 2022

svenklemm commented Oct 1, 2022

cocowalla commented Oct 4, 2022

svenklemm commented Oct 23, 2022

cocowalla commented Oct 24, 2022

JackMyers001 commented Nov 30, 2022

akuzm commented Jan 16, 2023 •

edited

Loading

noudreturn commented Jun 25, 2023

RafiaSabih commented Jul 18, 2023

github-actions bot commented Nov 12, 2023

feikesteenbergen commented Dec 4, 2023 •

edited

Loading

antekresic commented Dec 6, 2023

kyrias commented Dec 14, 2023

antekresic commented Dec 14, 2023

kyrias commented Dec 18, 2023

kyrias commented Dec 19, 2023

jnidzwetzki commented Dec 19, 2023

svenstaro commented Dec 20, 2023

jnidzwetzki commented Dec 20, 2023

xMlex commented Apr 19, 2024

xMlex commented Apr 19, 2024

antekresic commented Apr 22, 2024

[Bug]: error when joining two hypertables with aggregate query with CURSOR #4774

[Bug]: error when joining two hypertables with aggregate query with CURSOR #4774

Comments

cocowalla commented Sep 29, 2022 • edited Loading

What type of bug is this?

What subsystems and features are affected?

What happened?

TimescaleDB version affected

PostgreSQL version used

What operating system did you use?

What installation method did you use?

What platform did you run on?

Relevant log output and stack trace

How can we reproduce the bug?

Repro 1

Repro 2

cocowalla commented Sep 29, 2022

svenklemm commented Sep 30, 2022

cocowalla commented Sep 30, 2022 • edited Loading

cocowalla commented Sep 30, 2022 • edited Loading

svenklemm commented Sep 30, 2022

svenklemm commented Sep 30, 2022

cocowalla commented Sep 30, 2022

svenklemm commented Oct 1, 2022

cocowalla commented Oct 4, 2022

svenklemm commented Oct 23, 2022

cocowalla commented Oct 24, 2022

JackMyers001 commented Nov 30, 2022

akuzm commented Jan 16, 2023 • edited Loading

noudreturn commented Jun 25, 2023

RafiaSabih commented Jul 18, 2023

github-actions bot commented Nov 12, 2023

feikesteenbergen commented Dec 4, 2023 • edited Loading

antekresic commented Dec 6, 2023

kyrias commented Dec 14, 2023

antekresic commented Dec 14, 2023

kyrias commented Dec 18, 2023

kyrias commented Dec 19, 2023

jnidzwetzki commented Dec 19, 2023

svenstaro commented Dec 20, 2023

jnidzwetzki commented Dec 20, 2023

xMlex commented Apr 19, 2024

xMlex commented Apr 19, 2024

antekresic commented Apr 22, 2024

cocowalla commented Sep 29, 2022 •

edited

Loading

cocowalla commented Sep 30, 2022 •

edited

Loading

cocowalla commented Sep 30, 2022 •

edited

Loading

akuzm commented Jan 16, 2023 •

edited

Loading

feikesteenbergen commented Dec 4, 2023 •

edited

Loading