greptimedb/tests/cases/standalone/common/range/nest.result

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 |
+---------------------+-------+-----------------+
| 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               |
+---------------------+-------+-----------------+

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

Affected Rows: 0

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

Affected Rows: 0

INSERT INTO TABLE host_union_0 VALUES
    (0,     'host1', 3, 0),
    (5000,  'host1', 2, 5000),
    (10000, 'host1', 1, 10000);

Affected Rows: 3

INSERT INTO TABLE host_union_1 VALUES
    (0,     'host1', 6, 0),
    (5000,  'host1', 5, 5000),
    (10000, 'host1', 4, 10000);

Affected Rows: 3

SELECT ts, host, min(val ORDER BY ts ASC) RANGE '5s'
FROM (
  SELECT ts, host, val, ts2 FROM host_union_0
  UNION ALL
  SELECT ts, host, val, ts2 FROM host_union_1
)
WHERE ts >= '1970-01-01 00:00:00'
ALIGN '5s' BY (host)
ORDER BY host, ts;

+---------------------+-------+------------------------------------------------+
| ts                  | host  | min(val) ORDER BY [ts ASC NULLS LAST] RANGE 5s |
+---------------------+-------+------------------------------------------------+
| 1970-01-01T00:00:00 | host1 | 3                                              |
| 1970-01-01T00:00:05 | host1 | 2                                              |
| 1970-01-01T00:00:10 | host1 | 1                                              |
+---------------------+-------+------------------------------------------------+

SELECT tmp.ts, tmp.host, min(tmp.val ORDER BY tmp.ts ASC) RANGE '5s'
FROM (
  SELECT ts, host, val, ts2 FROM host_union_0
  UNION ALL
  SELECT ts, host, val, ts2 FROM host_union_1
) AS tmp
WHERE tmp.ts >= '1970-01-01 00:00:00'
ALIGN '5s' BY (tmp.host)
ORDER BY tmp.host, tmp.ts;

+---------------------+-------+--------------------------------------------------------+
| ts                  | host  | min(tmp.val) ORDER BY [tmp.ts ASC NULLS LAST] RANGE 5s |
+---------------------+-------+--------------------------------------------------------+
| 1970-01-01T00:00:00 | host1 | 3                                                      |
| 1970-01-01T00:00:05 | host1 | 2                                                      |
| 1970-01-01T00:00:10 | host1 | 1                                                      |
+---------------------+-------+--------------------------------------------------------+

DROP TABLE host_union_0;

Affected Rows: 0

DROP TABLE host_union_1;

Affected Rows: 0

-- 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], align=5000ms, align_to=0ms, align_by=[host.host], time_index=ts |
|_|_MergeScan [is_placeholder=false, remote_input=[_|
|_| Projection: host.ts, host.host, host.val_|
|_|_TableScan: host_|
|_| ]]_|
| physical_plan | RangeSelectExec: range_expr=[min(host.val) RANGE 5s], align=5000ms, align_to=0ms, align_by=[host@1], time_index=ts |
|_|_CooperativeExec_|
|_|_MergeScanExec: REDACTED
|_|_|
+-+-+

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

+-+-+-+
| stage | node | plan_|
+-+-+-+
| 0_| 0_|_RangeSelectExec: range_expr=[min(host.val) RANGE 5s], align=5000ms, align_to=0ms, align_by=[host@1], time_index=ts REDACTED
|_|_|_CooperativeExec REDACTED
|_|_|_MergeScanExec: REDACTED
|_|_|_|
| 1_| 0_|_CooperativeExec REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":1, "mem_ranges":1, "files":0, "file_ranges":0} REDACTED
|_|_|_|
|_|_| Total rows: 10_|
+-+-+-+

DROP TABLE host;

Affected Rows: 0