FILL does not work correctly with CTE and COALESCE #6023
Description
To reproduce
Having candlestick data I want to fill gaps with last close price.
Gap candle open,close,high,low = close of the last candle
Table Structure
CREATE TABLE IF NOT EXISTS candles_market_spot_4_1m (
candle_start_time timestamp,
candle_open_price double,
candle_close_price double,
candle_low_price double,
candle_high_price double,
candle_vwap double,
candle_volume double,
candle_trades_count int,
candle_usd_volume double,
computed_by varchar,
created_at timestamp,
candle_symbol symbol),
INDEX(candle_symbol) TIMESTAMP(candle_start_time) PARTITION BY WEEK
DEDUP UPSERT KEYS(candle_start_time, candle_symbol);
The query that works correctly. It fill the gap on 2025-07-30 21:00:00
select
candle_start_time,
candle_symbol,
first(candle_open_price) as open,
last(candle_close_price) as close,
min(candle_low_price) as low,
max(candle_high_price) as high,
sum(candle_volume) as candle_volume,
sum(candle_usd_volume) as candle_usd_volume,
sum(candle_trades_count) as cnt
from candles_market_spot_4_1m where candle_start_time >= '2025-07-30 20:00:00' and candle_start_time <= '2025-07-30 23:00:00' and candle_symbol = 'LSKBTC'
sample by 1h fill(PREV, PREV, PREV, PREV, 0, 0, 0) order by candle_start_time desc;
candle_start_time | candle_symbol | open | close | low | high | candle_volume | candle_usd_volume | cnt
----------------------------+---------------+---------+---------+---------+---------+---------------+-------------------+-----
2025-07-30 22:00:00.000000 | LSKBTC | 3.74E-6 | 3.74E-6 | 3.74E-6 | 3.74E-6 | 798.3 | 350.53257251232 | 18
2025-07-30 21:00:00.000000 | LSKBTC | 3.8E-6 | 3.78E-6 | 3.78E-6 | 3.8E-6 | 0.0 | 0.0 | 0
2025-07-30 20:00:00.000000 | LSKBTC | 3.8E-6 | 3.78E-6 | 3.78E-6 | 3.8E-6 | 3109.9 | 1376.75835070536 | 15
(3 rows)
The query that does not work as expected
with sq as (select
candle_start_time,
candle_symbol,
first(candle_open_price) as open,
last(candle_close_price) as close,
min(candle_low_price) as low,
max(candle_high_price) as high,
sum(candle_volume) as candle_volume,
sum(candle_usd_volume) as candle_usd_volume,
sum(candle_trades_count) as cnt
from candles_market_spot_4_1m where candle_start_time >= '2025-07-30 20:00:00' and candle_start_time <= '2025-07-30 23:00:00' and candle_symbol = 'LSKBTC'
sample by 1h fill(PREV, PREV, PREV, PREV, 0, 0, 0) order by candle_start_time desc)
select
candle_start_time,
candle_symbol,
open,
close,
low,
case when cnt = 0 then close else high end as high,
candle_volume,
candle_usd_volume,
cnt
from sq;
candle_start_time | candle_symbol | open | close | low | high | candle_volume | candle_usd_volume | cnt
----------------------------+---------------+---------+---------+---------+---------+---------------+-------------------+-----
2025-07-30 22:00:00.000000 | LSKBTC | 3.74E-6 | 3.74E-6 | 3.74E-6 | 3.74E-6 | 798.3 | 350.53257251232 | 18
2025-07-30 21:00:00.000000 | LSKBTC | 3.8E-6 | 3.78E-6 | 3.78E-6 | 0.0 | 0.0 | 0.0 | 15
2025-07-30 20:00:00.000000 | LSKBTC | 3.8E-6 | 3.78E-6 | 3.78E-6 | 3.8E-6 | 3109.9 | 1376.75835070536 | 15
(3 rows)
The cnt = 15 expected 0
high = 0 expected 3.78E-6
QuestDB version:
9.0.1
OS, in case of Docker specify Docker and the Host OS:
Ubuntu 24.04
File System, in case of Docker specify Host File System:
zfs
Full Name:
Maksim
Affiliation:
newstandards
Have you followed Linux, MacOs kernel configuration steps to increase Maximum open files and Maximum virtual memory areas limit?
- Yes, I have
Additional context
The useful feature - fill the gap using the arbitrary column.
Like
FILL(candle_close_price)