greptimedb/tests/cases/standalone/common/range/error.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 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: Illegal Range select, no RANGE keyword found in any SelectItem

SELECT 1 FROM host ALIGN '5s';

Error: 2000(InvalidSyntax), sql parser error: Illegal Range select, no RANGE keyword found in any SelectItem

SELECT min(val) RANGE '10s', max(val) FROM host ALIGN '5s';

Error: 3001(EngineExecuteQuery), DataFusion error: No field named "MAX(host.val)". Valid fields are "MIN(host.val) RANGE 10s", host.ts, host.host.

SELECT min(val) * 2 RANGE '10s' FROM host ALIGN '5s';

Error: 2000(InvalidSyntax), sql parser error: Can't use the RANGE keyword in Expr 2 without function

SELECT 1 RANGE '10s' FILL NULL FROM host ALIGN '1h' FILL NULL;

Error: 2000(InvalidSyntax), sql parser error: Can't use the RANGE keyword in Expr 1 without function

-- 2.2 no align param
SELECT min(val) RANGE '5s' FROM host;

Error: 3000(PlanQuery), DataFusion error: Error during planning: Missing argument in range select query

-- 2.3 type mismatch
SELECT covar(ceil(val), floor(val)) RANGE '20s' FROM host ALIGN '10s';

+-------------------------------------------------+
| COVAR(ceil(host.val),floor(host.val)) RANGE 20s |
+-------------------------------------------------+
|                                                 |
| 0.5                                             |
| 0.5                                             |
|                                                 |
|                                                 |
| 0.5                                             |
| 0.5                                             |
|                                                 |
+-------------------------------------------------+

-- 2.4 nest query
SELECT min(max(val) RANGE '20s') RANGE '20s' FROM host ALIGN '10s';

Error: 2000(InvalidSyntax), Range Query: Nest Range Query is not allowed

-- 2.5 wrong Aggregate
SELECT rank() OVER (PARTITION BY host ORDER BY ts DESC) RANGE '10s' FROM host ALIGN '5s';

Error: 2000(InvalidSyntax), Range Query: Window functions is not allowed in Range Query

-- 2.6 invalid fill
SELECT min(val) RANGE '5s' FROM host ALIGN '5s' FILL 3.0;

Error: 3000(PlanQuery), DataFusion error: Error during planning: 3.0 is not a valid fill option, fail to convert to a const value. { Arrow error: Cast error: Cannot cast string '3.0' to value of Int64 type }

-- 2.7 zero align/range
SELECT min(val) RANGE '5s' FROM host ALIGN '0s';

Error: 3000(PlanQuery), DataFusion error: Error during planning: duration must be greater than 0

SELECT min(val) RANGE '0s' FROM host ALIGN '5s';

Error: 3000(PlanQuery), DataFusion error: Error during planning: duration must be greater than 0

SELECT min(val) RANGE '5s' FROM host ALIGN (INTERVAL '0' day);

Error: 2000(InvalidSyntax), Range Query: Can't use 0 as align in Range Query

SELECT min(val) RANGE (INTERVAL '0' day) FROM host ALIGN '5s';

Error: 2000(InvalidSyntax), Range Query: Invalid Range expr `MIN(host.val) RANGE IntervalMonthDayNano("0")`, Can't use 0 as range in Range Query

DROP TABLE host;

Affected Rows: 0