CREATE TABLE host (
  ts timestamp(3) time index,
  host STRING PRIMARY KEY,
  val BIGINT,
);

Affected Rows: 0

INSERT INTO TABLE host VALUES
    (0,     'host1', 0),
    (5000,  'host1', null),
    (10000, 'host1', 1),
    (15000, 'host1', null),
    (20000, 'host1', 2),
    (0,     'host2', 3),
    (5000,  'host2', null),
    (10000, 'host2', 4),
    (15000, 'host2', null),
    (20000, 'host2', 5);

Affected Rows: 10

-- Test range query in nest sql
SELECT ts, host, foo FROM (SELECT ts, host, min(val) RANGE '5s' AS foo FROM host ALIGN '5s') WHERE host = 'host1' ORDER BY host, ts;

+---------------------+-------+-----+
| ts                  | host  | foo |
+---------------------+-------+-----+
| 1970-01-01T00:00:00 | host1 | 0   |
| 1970-01-01T00:00:05 | host1 |     |
| 1970-01-01T00:00:10 | host1 | 1   |
| 1970-01-01T00:00:15 | host1 |     |
| 1970-01-01T00:00:20 | host1 | 2   |
+---------------------+-------+-----+

SELECT ts, b, min(c) RANGE '5s' FROM (SELECT ts, host AS b, val AS c FROM host WHERE host = 'host1') ALIGN '5s' BY (b) ORDER BY b, ts;

+---------------------+-------+---------------------------+
| ts                  | b     | MIN(c) RANGE 5s FILL NULL |
+---------------------+-------+---------------------------+
| 1970-01-01T00:00:00 | host1 | 0                         |
| 1970-01-01T00:00:05 | host1 |                           |
| 1970-01-01T00:00:10 | host1 | 1                         |
| 1970-01-01T00:00:15 | host1 |                           |
| 1970-01-01T00:00:20 | host1 | 2                         |
+---------------------+-------+---------------------------+

-- Test EXPLAIN and ANALYZE
-- SQLNESS REPLACE (-+) -
-- SQLNESS REPLACE (\s\s+) _
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE (metrics.*) REDACTED
EXPLAIN SELECT ts, host, min(val) RANGE '5s' FROM host ALIGN '5s';

+-+-+
| plan_type_| plan_|
+-+-+
| logical_plan_| RangeSelect: range_exprs=[MIN(host.val) RANGE 5s FILL NULL], align=5000ms, align_to=0ms, align_by=[host.host], time_index=ts |
|_|_MergeScan [is_placeholder=false]_|
| physical_plan | RangeSelectExec: range_expr=[MIN(host.val) RANGE 5s FILL NULL], align=5000ms, align_to=0ms, align_by=[host@1], time_index=ts |
|_|_MergeScanExec: REDACTED
|_|_|
+-+-+

-- SQLNESS REPLACE (-+) -
-- SQLNESS REPLACE (\s\s+) _
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE (metrics.*) REDACTED
EXPLAIN ANALYZE SELECT ts, host, min(val) RANGE '5s' FROM host ALIGN '5s';

+-+-+
| plan_type_| plan_|
+-+-+
| Plan with Metrics | RangeSelectExec: range_expr=[MIN(host.val) RANGE 5s FILL NULL], align=5000ms, align_to=0ms, align_by=[host@1], time_index=ts, REDACTED
|_|_MergeScanExec: REDACTED
|_|_|
+-+-+

DROP TABLE host;

Affected Rows: 0