greptimedb/tests/cases/standalone/common/select/prune.result

create table demo(ts timestamp time index, `value` double, host string,idc string, collector string, primary key(host, idc, collector));

Affected Rows: 0

insert into demo values(1,2,'test1', 'idc1', 'disk') ,(2,3,'test2', 'idc1', 'disk'), (3,4,'test3', 'idc2','memory');

Affected Rows: 3

select * from demo where host='test1';

+-------------------------+-------+-------+------+-----------+
| ts                      | value | host  | idc  | collector |
+-------------------------+-------+-------+------+-----------+
| 1970-01-01T00:00:00.001 | 2.0   | test1 | idc1 | disk      |
+-------------------------+-------+-------+------+-----------+

select * from demo where host='test2';

+-------------------------+-------+-------+------+-----------+
| ts                      | value | host  | idc  | collector |
+-------------------------+-------+-------+------+-----------+
| 1970-01-01T00:00:00.002 | 3.0   | test2 | idc1 | disk      |
+-------------------------+-------+-------+------+-----------+

select * from demo where host='test3';

+-------------------------+-------+-------+------+-----------+
| ts                      | value | host  | idc  | collector |
+-------------------------+-------+-------+------+-----------+
| 1970-01-01T00:00:00.003 | 4.0   | test3 | idc2 | memory    |
+-------------------------+-------+-------+------+-----------+

select * from demo where host='test2' and idc='idc1';

+-------------------------+-------+-------+------+-----------+
| ts                      | value | host  | idc  | collector |
+-------------------------+-------+-------+------+-----------+
| 1970-01-01T00:00:00.002 | 3.0   | test2 | idc1 | disk      |
+-------------------------+-------+-------+------+-----------+

select * from demo where host='test2' and idc='idc1' and collector='disk';

+-------------------------+-------+-------+------+-----------+
| ts                      | value | host  | idc  | collector |
+-------------------------+-------+-------+------+-----------+
| 1970-01-01T00:00:00.002 | 3.0   | test2 | idc1 | disk      |
+-------------------------+-------+-------+------+-----------+

select * from demo where host='test2' and idc='idc2';

+----+-------+------+-----+-----------+
| ts | value | host | idc | collector |
+----+-------+------+-----+-----------+
+----+-------+------+-----+-----------+

select * from demo where host='test3' and idc>'idc1';

+-------------------------+-------+-------+------+-----------+
| ts                      | value | host  | idc  | collector |
+-------------------------+-------+-------+------+-----------+
| 1970-01-01T00:00:00.003 | 4.0   | test3 | idc2 | memory    |
+-------------------------+-------+-------+------+-----------+

select * from demo where idc='idc1' order by ts;

+-------------------------+-------+-------+------+-----------+
| ts                      | value | host  | idc  | collector |
+-------------------------+-------+-------+------+-----------+
| 1970-01-01T00:00:00.001 | 2.0   | test1 | idc1 | disk      |
| 1970-01-01T00:00:00.002 | 3.0   | test2 | idc1 | disk      |
+-------------------------+-------+-------+------+-----------+

select * from demo where collector='disk' order by ts;

+-------------------------+-------+-------+------+-----------+
| ts                      | value | host  | idc  | collector |
+-------------------------+-------+-------+------+-----------+
| 1970-01-01T00:00:00.001 | 2.0   | test1 | idc1 | disk      |
| 1970-01-01T00:00:00.002 | 3.0   | test2 | idc1 | disk      |
+-------------------------+-------+-------+------+-----------+

-- 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 * from demo where idc='idc1';

+-+-+-+
| stage | node | plan_|
+-+-+-+
| 0_| 0_|_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: 2_|
+-+-+-+

SELECT * FROM demo where host in ('test1');

+-------------------------+-------+-------+------+-----------+
| ts                      | value | host  | idc  | collector |
+-------------------------+-------+-------+------+-----------+
| 1970-01-01T00:00:00.001 | 2.0   | test1 | idc1 | disk      |
+-------------------------+-------+-------+------+-----------+

-- SQLNESS REPLACE (metrics.*) REDACTED
-- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED
-- SQLNESS REPLACE (-+) -
-- SQLNESS REPLACE (\s\s+) _
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
explain analyze SELECT * FROM demo where host in ('test1');

+-+-+-+
| stage | node | plan_|
+-+-+-+
| 0_| 0_|_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: 1_|
+-+-+-+

drop table demo;

Affected Rows: 0

CREATE TABLE test_time_filter(
    host STRING,
    greptime_timestamp TIMESTAMP,
    TIME INDEX(greptime_timestamp)
)
WITH
(
    'append_mode' = 'true',
    'sst_format' = 'flat'
);

Affected Rows: 0

INSERT INTO test_time_filter(host, greptime_timestamp) VALUES ('hello', '2026-01-07T00:00:00'), ('world', '2026-01-07T00:00:01'), ('test', '2026-01-07T00:00:00'), ('go', '2026-01-07T00:00:01');

Affected Rows: 4

SELECT host FROM test_time_filter WHERE greptime_timestamp > '2026-01-07 00:00:00';

+-------+
| host  |
+-------+
| go    |
| world |
+-------+

SELECT host, greptime_timestamp FROM test_time_filter WHERE greptime_timestamp > '2026-01-07 00:00:00';

+-------+---------------------+
| host  | greptime_timestamp  |
+-------+---------------------+
| go    | 2026-01-07T00:00:01 |
| world | 2026-01-07T00:00:01 |
+-------+---------------------+

ADMIN flush_table('test_time_filter');

+---------------------------------------+
| ADMIN flush_table('test_time_filter') |
+---------------------------------------+
| 0                                     |
+---------------------------------------+

SELECT host FROM test_time_filter WHERE greptime_timestamp > '2026-01-07 00:00:00';

+-------+
| host  |
+-------+
| go    |
| world |
+-------+

SELECT host, greptime_timestamp FROM test_time_filter WHERE greptime_timestamp > '2026-01-07 00:00:00';

+-------+---------------------+
| host  | greptime_timestamp  |
+-------+---------------------+
| go    | 2026-01-07T00:00:01 |
| world | 2026-01-07T00:00:01 |
+-------+---------------------+

DROP TABLE test_time_filter;

Affected Rows: 0