To reproduce

This query fails on 9.0.0 when issued via PG Wire protocol, while it was working on 8.3.3.

Here follows the log:

2025-07-14T15:02:39.552971Z I pg-server connected [ip=192.168.77.153, fd=120259104060]
2025-07-14T15:02:39.557383Z I i.q.g.e.QueryProgress fin [id=-1, sql=BEGIN, principal=admin, cache=false, jit=true, time=22930]
2025-07-14T15:02:39.557993Z I i.q.g.e.QueryProgress fin [id=-1, sql=set time zone 'Brazil/East', principal=admin, cache=false, jit=true, time=3608]
2025-07-14T15:02:39.558833Z I i.q.g.e.QueryProgress exe [id=572, sql=select distinct sensor_id, apptype, feature from sensors where uuid = $1 limit 1;, principal=admin, cache=true, jit=true]
2025-07-14T15:02:39.560439Z I i.q.g.e.QueryProgress fin [id=572, sql=select distinct sensor_id, apptype, feature from sensors where uuid = $1 limit 1;, principal=admin, cache=true, jit=true, time=1608453]
2025-07-14T15:02:39.561188Z I i.q.g.e.QueryProgress fin [id=-1, sql=COMMIT, principal=admin, cache=false, jit=true, time=5720]
2025-07-14T15:02:39.561492Z I pg-server disconnected [ip=192.168.77.153, fd=120259104060, src=queue]
2025-07-14T15:02:39.562461Z I pg-server connected [ip=192.168.77.153, fd=120259104061]
2025-07-14T15:02:39.564086Z I i.q.g.e.QueryProgress fin [id=-1, sql=BEGIN, principal=admin, cache=false, jit=true, time=2390]
2025-07-14T15:02:39.564660Z I i.q.g.e.QueryProgress fin [id=-1, sql=set time zone 'Brazil/East', principal=admin, cache=false, jit=true, time=2235]
2025-07-14T15:02:39.567249Z E i.q.g.e.QueryProgress err [id=-1, sql=**with aggtable as (select distinct sa.ts,min(sample) as min_k,avg(sample) as avg_k,max(sample) as max_k from samples sa where sensor_id = $1 and sa.ts between $2 and $3 sample by 24h align to calendar time zone $4 order by 1 limit $5) select distinct to_timezone(ts,$4) as "Timestamp",case when $6 = 'C' and '3' = 1 and 'Rectifiers usage' = 'Temperature' then (min_k - 273.15) when $6 = 'F' and '3' = 1 and 'Rectifiers usage' = 'Temperature' then ((min_k - 273.15) * 9 / 5 + 32) else (min_k) end as "Min",case when $6 = 'C' and '3' = 1 and 'Rectifiers usage' = 'Temperature' then (avg_k - 273.15) when $6 = 'F' and '3' = 1 and 'Rectifiers usage' = 'Temperature' then ((avg_k - 273.15) * 9 / 5 + 32) else (avg_k) end as "Avg",case when $6 = 'C' and '3' = 1 and 'Rectifiers usage' = 'Temperature' then (max_k - 273.15) when $6 = 'F' and '3' = 1 and 'Rectifiers usage' = 'Temperature' then ((max_k - 273.15) * 9 / 5 + 32) else (max_k) end as "Max" from aggtable ;**, principal=admin, cache=false, jit=true, time=1861034, msg=Invalid table name or alias, errno=0, pos=0]
2025-07-14T15:02:39.567347Z E i.q.c.p.m.PGConnectionContextModern failed to parse message [err: Invalid table name or alias]
2025-07-14T15:02:39.567994Z I i.q.g.e.QueryProgress fin [id=-1, sql=ROLLBACK, principal=admin, cache=false, jit=true, time=3725]
2025-07-14T15:02:39.568225Z I pg-server disconnected [ip=192.168.77.153, fd=120259104061, src=queue]
2025-07-14T15:04:22.779107Z I http-server disconnected [ip=192.168.77.153, fd=734439427290, src=idle]
2025-07-14T15:04:24.088370Z I http-server disconnected [ip=192.168.77.153, fd=738734394587, src=idle]
2025-07-14T15:04:25.149405Z I http-server disconnected [ip=192.168.77.153, fd=416611847383, src=idle]
2025-07-14T15:04:43.450280Z I i.q.c.p.ReaderPool closed [table=samples21, at=0:0, reason=IDLE]
2025-07-14T15:04:43.455651Z I i.q.c.p.ReaderPool closed [table=TableToken{tableName=sensors, dirName=sensors_new72}, at=0:0, reason=IDLE]
2025-07-14T15:04:43.455792Z I i.q.c.p.ReaderPool closed [table=status, at=0:0, reason=IDLE]

QuestDB version:

9.0.0

OS, in case of Docker specify Docker and the Host OS:

Red Hat Enterprise Linux release 9.5 (Plow)

File System, in case of Docker specify Host File System:

xfs

Full Name:

Andrea Moretto

Affiliation:

HyperMatrix

Have you followed Linux, MacOs kernel configuration steps to increase Maximum open files and Maximum virtual memory areas limit?

• Yes, I have

Additional context

Same query does not raise any error when issued from psql CLI:

admin=> with aggtable as
(
select distinct sa.ts,min(sample) as min_k,avg(sample) as avg_k,max(sample) as max_k
from samples sa
where sensor_id = '1720376755:3_4_None' and sa.ts between 0 and 1
sample by 24h align to calendar time zone 'UTC'
order by 1
limit 5
)
select distinct to_timezone(ts,'UTC') as "Timestamp",
case when 'C' = 'C' and '3' = 1 and 'Rectifiers usage' = 'Temperature' then (min_k - 273.15) when 'C' = 'F' and '3' = 1 and 'Rectifiers usage' = 'Temperature' then ((min_k - 273.15) * 9 / 5 + 32) else (min_k)
end as "Min",
case when 'C' = 'C' and '3' = 1 and 'Rectifiers usage' = 'Temperature' then (avg_k - 273.15) when 'C' = 'F' and '3' = 1 and 'Rectifiers usage' = 'Temperature' then ((avg_k - 273.15) * 9 / 5 + 32) else (avg_k)
end as "Avg",
case when 'C' = 'C' and '3' = 1 and 'Rectifiers usage' = 'Temperature' then (max_k - 273.15) when 'C' = 'F' and '3' = 1 and 'Rectifiers usage' = 'Temperature' then ((max_k - 273.15) * 9 / 5 + 32) else (max_k)
end as "Max"
from aggtable;
Timestamp | Min | Avg | Max
-----------+-----+-----+-----
(0 rows)