Support fills for SAMPLE BY FROM-TO with empty record sets

[nwoolmer]

Is your feature request related to a problem?

The SAMPLE BY FROM-TO feature added support for pre and post filling result sets, around existing records.

It also added support for fills with parallel sample by.

However, using the fill with no present records is not well supported.

Technically, it works with parallel sample by. Here's an example on demo:

select pickup_datetime, count() from trips
sample by 1M from '2030-01-01' to '2031-01-01' fill(null)

However, a version of this query runing with a sequential sample by will return nothing:

select pickup_datetime, count() from trips
sample by 1M from '2030-01-01' to '2031-01-01' fill(null)
align to calendar with offset '10:00'

This is because initTimestamps() and SampleByFillValueNotKeyedRecordCursor are short-circuit in the zero records case, and were not designed to return anything.

Describe the solution you'd like.

Ideally, we should support it for both the parallel and sequential cases, as a convenience syntax for generating timestamps.

Describe alternatives you've considered.

Using timestamp_sequence() i.e

select x, timestamp_floor('1d', timestamp_sequence(systimestamp(), 86400 * 1000000L))
from long_sequence(3)

This doesn't make it easy to handle calendar aligned cases.

Full Name:

Nick Woolmer

Affiliation:

QuestDB

Additional context

No response