rust/greptimedb
1
0
Fork 0
mirror of https://github.com/GreptimeTeam/greptimedb.git synced 2026-01-07 13:52:59 +00:00
Code Issues Packages Projects Releases Wiki Activity
Files
stream
greptimedb/tests/cases/standalone/common/select/range_select.result
WU Jingdi 15912afd96 fix: the inconsistent order of input/output in range select (#2229) 
* fix: the inconsistent order of input/output in range select

* chore: apply CR
2023-08-25 04:12:59 +00:00

350 lines
20 KiB
Plaintext
Raw Permalink Blame History

CREATE TABLE host (
ts timestamp(3) time index,
host STRING PRIMARY KEY,
val DOUBLE,
);
Affected Rows: 0
INSERT INTO TABLE host VALUES
(0, 'host1', 0.0),
(5000, 'host1', 1.0),
(10000, 'host1', 2.0),
(15000, 'host1', 3.0),
(20000, 'host1', 4.0),
(25000, 'host1', 5.0),
(30000, 'host1', 6.0),
(35000, 'host1', 7.0),
(40000, 'host1', 8.0),
(0, 'host2', 9.0),
(5000, 'host2', 10.0),
(10000, 'host2', 11.0),
(15000, 'host2', 12.0),
(20000, 'host2', 13.0),
(25000, 'host2', 14.0),
(30000, 'host2', 15.0),
(35000, 'host2', 16.0),
(40000, 'host2', 17.0);
Affected Rows: 18
SELECT ts, host, min(val) RANGE '10s', max(val) RANGE '10s' FROM host ALIGN '5s' ORDER BY host, ts;
+---------------------+-------+---------------+---------------+
| ts | host | MIN(host.val) | MAX(host.val) |
+---------------------+-------+---------------+---------------+
| 1970-01-01T00:00:00 | host1 | 0.0 | 0.0 |
| 1970-01-01T00:00:05 | host1 | 0.0 | 1.0 |
| 1970-01-01T00:00:10 | host1 | 1.0 | 2.0 |
| 1970-01-01T00:00:15 | host1 | 2.0 | 3.0 |
| 1970-01-01T00:00:20 | host1 | 3.0 | 4.0 |
| 1970-01-01T00:00:25 | host1 | 4.0 | 5.0 |
| 1970-01-01T00:00:30 | host1 | 5.0 | 6.0 |
| 1970-01-01T00:00:35 | host1 | 6.0 | 7.0 |
| 1970-01-01T00:00:40 | host1 | 7.0 | 8.0 |
| 1970-01-01T00:00:45 | host1 | 8.0 | 8.0 |
| 1970-01-01T00:00:00 | host2 | 9.0 | 9.0 |
| 1970-01-01T00:00:05 | host2 | 9.0 | 10.0 |
| 1970-01-01T00:00:10 | host2 | 10.0 | 11.0 |
| 1970-01-01T00:00:15 | host2 | 11.0 | 12.0 |
| 1970-01-01T00:00:20 | host2 | 12.0 | 13.0 |
| 1970-01-01T00:00:25 | host2 | 13.0 | 14.0 |
| 1970-01-01T00:00:30 | host2 | 14.0 | 15.0 |
| 1970-01-01T00:00:35 | host2 | 15.0 | 16.0 |
| 1970-01-01T00:00:40 | host2 | 16.0 | 17.0 |
| 1970-01-01T00:00:45 | host2 | 17.0 | 17.0 |
+---------------------+-------+---------------+---------------+
SELECT ts, host, min(val / 2.0)/2 RANGE '10s', max(val / 2.0)/2 RANGE '10s' FROM host ALIGN '5s' ORDER BY host, ts;
+---------------------+-------+---------------------------------------+---------------------------------------+
| ts | host | MIN(host.val / Float64(2)) / Int64(2) | MAX(host.val / Float64(2)) / Int64(2) |
+---------------------+-------+---------------------------------------+---------------------------------------+
| 1970-01-01T00:00:00 | host1 | 0.0 | 0.0 |
| 1970-01-01T00:00:05 | host1 | 0.0 | 0.25 |
| 1970-01-01T00:00:10 | host1 | 0.25 | 0.5 |
| 1970-01-01T00:00:15 | host1 | 0.5 | 0.75 |
| 1970-01-01T00:00:20 | host1 | 0.75 | 1.0 |
| 1970-01-01T00:00:25 | host1 | 1.0 | 1.25 |
| 1970-01-01T00:00:30 | host1 | 1.25 | 1.5 |
| 1970-01-01T00:00:35 | host1 | 1.5 | 1.75 |
| 1970-01-01T00:00:40 | host1 | 1.75 | 2.0 |
| 1970-01-01T00:00:45 | host1 | 2.0 | 2.0 |
| 1970-01-01T00:00:00 | host2 | 2.25 | 2.25 |
| 1970-01-01T00:00:05 | host2 | 2.25 | 2.5 |
| 1970-01-01T00:00:10 | host2 | 2.5 | 2.75 |
| 1970-01-01T00:00:15 | host2 | 2.75 | 3.0 |
| 1970-01-01T00:00:20 | host2 | 3.0 | 3.25 |
| 1970-01-01T00:00:25 | host2 | 3.25 | 3.5 |
| 1970-01-01T00:00:30 | host2 | 3.5 | 3.75 |
| 1970-01-01T00:00:35 | host2 | 3.75 | 4.0 |
| 1970-01-01T00:00:40 | host2 | 4.0 | 4.25 |
| 1970-01-01T00:00:45 | host2 | 4.25 | 4.25 |
+---------------------+-------+---------------------------------------+---------------------------------------+
SELECT ts, covar(val, val) RANGE '10s', host FROM host ALIGN '5s' ORDER BY host, ts;
+---------------------+-------------------------------+-------+
| ts | COVARIANCE(host.val,host.val) | host |
+---------------------+-------------------------------+-------+
| 1970-01-01T00:00:00 | | host1 |
| 1970-01-01T00:00:05 | 0.5 | host1 |
| 1970-01-01T00:00:10 | 0.5 | host1 |
| 1970-01-01T00:00:15 | 0.5 | host1 |
| 1970-01-01T00:00:20 | 0.5 | host1 |
| 1970-01-01T00:00:25 | 0.5 | host1 |
| 1970-01-01T00:00:30 | 0.5 | host1 |
| 1970-01-01T00:00:35 | 0.5 | host1 |
| 1970-01-01T00:00:40 | 0.5 | host1 |
| 1970-01-01T00:00:45 | | host1 |
| 1970-01-01T00:00:00 | | host2 |
| 1970-01-01T00:00:05 | 0.5 | host2 |
| 1970-01-01T00:00:10 | 0.5 | host2 |
| 1970-01-01T00:00:15 | 0.5 | host2 |
| 1970-01-01T00:00:20 | 0.5 | host2 |
| 1970-01-01T00:00:25 | 0.5 | host2 |
| 1970-01-01T00:00:30 | 0.5 | host2 |
| 1970-01-01T00:00:35 | 0.5 | host2 |
| 1970-01-01T00:00:40 | 0.5 | host2 |
| 1970-01-01T00:00:45 | | host2 |
+---------------------+-------------------------------+-------+
SELECT covar(ceil(val), floor(val)) RANGE '10s', ts, host FROM host ALIGN '5s' ORDER BY host, ts;
+--------------------------------------------+---------------------+-------+
| COVARIANCE(ceil(host.val),floor(host.val)) | ts | host |
+--------------------------------------------+---------------------+-------+
| | 1970-01-01T00:00:00 | host1 |
| 0.5 | 1970-01-01T00:00:05 | host1 |
| 0.5 | 1970-01-01T00:00:10 | host1 |
| 0.5 | 1970-01-01T00:00:15 | host1 |
| 0.5 | 1970-01-01T00:00:20 | host1 |
| 0.5 | 1970-01-01T00:00:25 | host1 |
| 0.5 | 1970-01-01T00:00:30 | host1 |
| 0.5 | 1970-01-01T00:00:35 | host1 |
| 0.5 | 1970-01-01T00:00:40 | host1 |
| | 1970-01-01T00:00:45 | host1 |
| | 1970-01-01T00:00:00 | host2 |
| 0.5 | 1970-01-01T00:00:05 | host2 |
| 0.5 | 1970-01-01T00:00:10 | host2 |
| 0.5 | 1970-01-01T00:00:15 | host2 |
| 0.5 | 1970-01-01T00:00:20 | host2 |
| 0.5 | 1970-01-01T00:00:25 | host2 |
| 0.5 | 1970-01-01T00:00:30 | host2 |
| 0.5 | 1970-01-01T00:00:35 | host2 |
| 0.5 | 1970-01-01T00:00:40 | host2 |
| | 1970-01-01T00:00:45 | host2 |
+--------------------------------------------+---------------------+-------+
SELECT ts, host, covar((sin(val) + cos(val))/2.0 + 1.0, 2.0) RANGE '10s' FROM host ALIGN '5s' ORDER BY host, ts;
+---------------------+-------+--------------------------------------------------------------------------------+
| ts | host | COVARIANCE(sin(host.val) + cos(host.val) / Float64(2) + Float64(1),Float64(2)) |
+---------------------+-------+--------------------------------------------------------------------------------+
| 1970-01-01T00:00:00 | host1 | |
| 1970-01-01T00:00:05 | host1 | 0.0 |
| 1970-01-01T00:00:10 | host1 | 0.0 |
| 1970-01-01T00:00:15 | host1 | 0.0 |
| 1970-01-01T00:00:20 | host1 | 0.0 |
| 1970-01-01T00:00:25 | host1 | 0.0 |
| 1970-01-01T00:00:30 | host1 | 0.0 |
| 1970-01-01T00:00:35 | host1 | 0.0 |
| 1970-01-01T00:00:40 | host1 | 0.0 |
| 1970-01-01T00:00:45 | host1 | |
| 1970-01-01T00:00:00 | host2 | |
| 1970-01-01T00:00:05 | host2 | 0.0 |
| 1970-01-01T00:00:10 | host2 | 0.0 |
| 1970-01-01T00:00:15 | host2 | 0.0 |
| 1970-01-01T00:00:20 | host2 | 0.0 |
| 1970-01-01T00:00:25 | host2 | 0.0 |
| 1970-01-01T00:00:30 | host2 | 0.0 |
| 1970-01-01T00:00:35 | host2 | 0.0 |
| 1970-01-01T00:00:40 | host2 | 0.0 |
| 1970-01-01T00:00:45 | host2 | |
+---------------------+-------+--------------------------------------------------------------------------------+
SELECT ts, min(val) RANGE '10s', host, max(val) RANGE '10s' FROM host ALIGN '1000s' ORDER BY host, ts;
+---------------------+---------------+-------+---------------+
| ts | MIN(host.val) | host | MAX(host.val) |
+---------------------+---------------+-------+---------------+
| 1970-01-01T00:00:00 | 0.0 | host1 | 0.0 |
| 1970-01-01T00:00:00 | 9.0 | host2 | 9.0 |
+---------------------+---------------+-------+---------------+
SELECT ts, host, min(val) RANGE '10s', max(val) RANGE '5s' FROM host ALIGN '5s' ORDER BY host, ts;
+---------------------+-------+---------------+---------------+
| ts | host | MIN(host.val) | MAX(host.val) |
+---------------------+-------+---------------+---------------+
| 1970-01-01T00:00:00 | host1 | 0.0 | 0.0 |
| 1970-01-01T00:00:05 | host1 | 0.0 | 1.0 |
| 1970-01-01T00:00:10 | host1 | 1.0 | 2.0 |
| 1970-01-01T00:00:15 | host1 | 2.0 | 3.0 |
| 1970-01-01T00:00:20 | host1 | 3.0 | 4.0 |
| 1970-01-01T00:00:25 | host1 | 4.0 | 5.0 |
| 1970-01-01T00:00:30 | host1 | 5.0 | 6.0 |
| 1970-01-01T00:00:35 | host1 | 6.0 | 7.0 |
| 1970-01-01T00:00:40 | host1 | 7.0 | 8.0 |
| 1970-01-01T00:00:45 | host1 | 8.0 | |
| 1970-01-01T00:00:00 | host2 | 9.0 | 9.0 |
| 1970-01-01T00:00:05 | host2 | 9.0 | 10.0 |
| 1970-01-01T00:00:10 | host2 | 10.0 | 11.0 |
| 1970-01-01T00:00:15 | host2 | 11.0 | 12.0 |
| 1970-01-01T00:00:20 | host2 | 12.0 | 13.0 |
| 1970-01-01T00:00:25 | host2 | 13.0 | 14.0 |
| 1970-01-01T00:00:30 | host2 | 14.0 | 15.0 |
| 1970-01-01T00:00:35 | host2 | 15.0 | 16.0 |
| 1970-01-01T00:00:40 | host2 | 16.0 | 17.0 |
| 1970-01-01T00:00:45 | host2 | 17.0 | |
+---------------------+-------+---------------+---------------+
SELECT ts, host, (min(val)+max(val))/4 RANGE '10s' FROM host ALIGN '5s' ORDER BY host, ts;
+---------------------+-------+------------------------------------------+
| ts | host | MIN(host.val) + MAX(host.val) / Int64(4) |
+---------------------+-------+------------------------------------------+
| 1970-01-01T00:00:00 | host1 | 0.0 |
| 1970-01-01T00:00:05 | host1 | 0.25 |
| 1970-01-01T00:00:10 | host1 | 0.75 |
| 1970-01-01T00:00:15 | host1 | 1.25 |
| 1970-01-01T00:00:20 | host1 | 1.75 |
| 1970-01-01T00:00:25 | host1 | 2.25 |
| 1970-01-01T00:00:30 | host1 | 2.75 |
| 1970-01-01T00:00:35 | host1 | 3.25 |
| 1970-01-01T00:00:40 | host1 | 3.75 |
| 1970-01-01T00:00:45 | host1 | 4.0 |
| 1970-01-01T00:00:00 | host2 | 4.5 |
| 1970-01-01T00:00:05 | host2 | 4.75 |
| 1970-01-01T00:00:10 | host2 | 5.25 |
| 1970-01-01T00:00:15 | host2 | 5.75 |
| 1970-01-01T00:00:20 | host2 | 6.25 |
| 1970-01-01T00:00:25 | host2 | 6.75 |
| 1970-01-01T00:00:30 | host2 | 7.25 |
| 1970-01-01T00:00:35 | host2 | 7.75 |
| 1970-01-01T00:00:40 | host2 | 8.25 |
| 1970-01-01T00:00:45 | host2 | 8.5 |
+---------------------+-------+------------------------------------------+
SELECT ts, host, foo FROM (SELECT ts, host, (min(val)+max(val))/4 RANGE '10s' AS foo FROM host ALIGN '5s' ORDER BY host, ts) WHERE foo > 5 ORDER BY host, ts;
+---------------------+-------+------+
| ts | host | foo |
+---------------------+-------+------+
| 1970-01-01T00:00:10 | host2 | 5.25 |
| 1970-01-01T00:00:15 | host2 | 5.75 |
| 1970-01-01T00:00:20 | host2 | 6.25 |
| 1970-01-01T00:00:25 | host2 | 6.75 |
| 1970-01-01T00:00:30 | host2 | 7.25 |
| 1970-01-01T00:00:35 | host2 | 7.75 |
| 1970-01-01T00:00:40 | host2 | 8.25 |
| 1970-01-01T00:00:45 | host2 | 8.5 |
+---------------------+-------+------+
SELECT ts, b, (min(c)+max(c))/4 RANGE '10s' FROM (SELECT ts, host AS b, val AS c FROM host WHERE val > 8.0) ALIGN '5s' BY (b) ORDER BY b, ts;
+---------------------+-------+----------------------------+
| ts | b | MIN(c) + MAX(c) / Int64(4) |
+---------------------+-------+----------------------------+
| 1970-01-01T00:00:00 | host2 | 4.5 |
| 1970-01-01T00:00:05 | host2 | 4.75 |
| 1970-01-01T00:00:10 | host2 | 5.25 |
| 1970-01-01T00:00:15 | host2 | 5.75 |
| 1970-01-01T00:00:20 | host2 | 6.25 |
| 1970-01-01T00:00:25 | host2 | 6.75 |
| 1970-01-01T00:00:30 | host2 | 7.25 |
| 1970-01-01T00:00:35 | host2 | 7.75 |
| 1970-01-01T00:00:40 | host2 | 8.25 |
| 1970-01-01T00:00:45 | host2 | 8.5 |
+---------------------+-------+----------------------------+
-- Test Invalid cases
-- 1. error timestamp
SELECT min(val) RANGE 'not_time' FROM host ALIGN '5s';
Error: 2000(InvalidSyntax), sql parser error: not a valid duration string: not_time
SELECT min(val) RANGE '5s' FROM host ALIGN 'not_time';
Error: 2000(InvalidSyntax), sql parser error: not a valid duration string: not_time
-- 2.1 no range param
SELECT min(val) FROM host ALIGN '5s';
Error: 2000(InvalidSyntax), sql parser error: RANGE argument not found in min(val)
SELECT min(val) RANGE '10s', max(val) FROM host ALIGN '5s';
Error: 2000(InvalidSyntax), sql parser error: RANGE argument not found in max(val)
-- 2.2 no align param
SELECT min(val) RANGE '5s' FROM host;
Error: 1003(Internal), Error during planning: Illegal argument in range select query
DROP TABLE host;
Affected Rows: 1
CREATE TABLE host_sec (
ts timestamp(0) time index,
host STRING PRIMARY KEY,
val DOUBLE,
);
Affected Rows: 0
INSERT INTO TABLE host_sec VALUES
(0, 'host1', 0.0),
(5, 'host1', 1.0),
(10, 'host1', 2.0),
(15, 'host1', 3.0),
(20, 'host1', 4.0),
(25, 'host1', 5.0),
(30, 'host1', 6.0),
(35, 'host1', 7.0),
(40, 'host1', 8.0),
(0, 'host2', 9.0),
(5, 'host2', 10.0),
(10, 'host2', 11.0),
(15, 'host2', 12.0),
(20, 'host2', 13.0),
(25, 'host2', 14.0),
(30, 'host2', 15.0),
(35, 'host2', 16.0),
(40, 'host2', 17.0);
Affected Rows: 18
SELECT ts, host, min(val) RANGE '10s', max(val) RANGE '10s' FROM host_sec ALIGN '5s' ORDER BY host, ts;
+---------------------+-------+-------------------+-------------------+
| ts | host | MIN(host_sec.val) | MAX(host_sec.val) |
+---------------------+-------+-------------------+-------------------+
| 1970-01-01T00:00:00 | host1 | 0.0 | 0.0 |
| 1970-01-01T00:00:05 | host1 | 0.0 | 1.0 |
| 1970-01-01T00:00:10 | host1 | 1.0 | 2.0 |
| 1970-01-01T00:00:15 | host1 | 2.0 | 3.0 |
| 1970-01-01T00:00:20 | host1 | 3.0 | 4.0 |
| 1970-01-01T00:00:25 | host1 | 4.0 | 5.0 |
| 1970-01-01T00:00:30 | host1 | 5.0 | 6.0 |
| 1970-01-01T00:00:35 | host1 | 6.0 | 7.0 |
| 1970-01-01T00:00:40 | host1 | 7.0 | 8.0 |
| 1970-01-01T00:00:45 | host1 | 8.0 | 8.0 |
| 1970-01-01T00:00:00 | host2 | 9.0 | 9.0 |
| 1970-01-01T00:00:05 | host2 | 9.0 | 10.0 |
| 1970-01-01T00:00:10 | host2 | 10.0 | 11.0 |
| 1970-01-01T00:00:15 | host2 | 11.0 | 12.0 |
| 1970-01-01T00:00:20 | host2 | 12.0 | 13.0 |
| 1970-01-01T00:00:25 | host2 | 13.0 | 14.0 |
| 1970-01-01T00:00:30 | host2 | 14.0 | 15.0 |
| 1970-01-01T00:00:35 | host2 | 15.0 | 16.0 |
| 1970-01-01T00:00:40 | host2 | 16.0 | 17.0 |
| 1970-01-01T00:00:45 | host2 | 17.0 | 17.0 |
+---------------------+-------+-------------------+-------------------+
DROP TABLE host_sec;
Affected Rows: 1
Reference in New Issue View Git Blame Copy Permalink