greptimedb/tests/cases/standalone/common/partition.result

CREATE TABLE my_table (
  a INT PRIMARY KEY,
  b STRING,
  ts TIMESTAMP TIME INDEX,
)
PARTITION ON COLUMNS (a) (
  a < 1000,
  a >= 1000 AND a < 2000,
  a >= 2000
);

Affected Rows: 0

-- SQLNESS REPLACE (\d{13}) ID
SELECT table_catalog, table_schema, table_name, partition_name, partition_expression, partition_description, greptime_partition_id from information_schema.partitions WHERE table_name = 'my_table' ORDER BY table_catalog, table_schema, table_name, partition_name;

+---------------+--------------+------------+----------------+----------------------+------------------------+-----------------------+
| table_catalog | table_schema | table_name | partition_name | partition_expression | partition_description  | greptime_partition_id |
+---------------+--------------+------------+----------------+----------------------+------------------------+-----------------------+
| greptime      | public       | my_table   | p0             | a                    | a < 1000               | ID         |
| greptime      | public       | my_table   | p1             | a                    | a >= 1000 AND a < 2000 | ID         |
| greptime      | public       | my_table   | p2             | a                    | a >= 2000              | ID         |
+---------------+--------------+------------+----------------+----------------------+------------------------+-----------------------+

-- SQLNESS REPLACE (\d{13}) REGION_ID
-- SQLNESS REPLACE (\d{1}) PEER_ID
SELECT region_id, peer_id, is_leader, status FROM information_schema.region_peers ORDER BY peer_id;

+---------------+---------+-----------+--------+
| region_id     | peer_id | is_leader | status |
+---------------+---------+-----------+--------+
| REGION_ID | PEER_ID       | Yes       | ALIVE  |
| REGION_ID | PEER_ID       | Yes       | ALIVE  |
| REGION_ID | PEER_ID       | Yes       | ALIVE  |
+---------------+---------+-----------+--------+

INSERT INTO my_table VALUES
    (100, 'a', 1),
    (200, 'b', 2),
    (1100, 'c', 3),
    (1200, 'd', 4),
    (2000, 'e', 5),
    (2100, 'f', 6),
    (2200, 'g', 7),
    (2400, 'h', 8);

Affected Rows: 8

-- SQLNESS SORT_RESULT 3 1
SELECT * FROM my_table;

+------+---+-------------------------+
| a    | b | ts                      |
+------+---+-------------------------+
| 100  | a | 1970-01-01T00:00:00.001 |
| 1100 | c | 1970-01-01T00:00:00.003 |
| 1200 | d | 1970-01-01T00:00:00.004 |
| 200  | b | 1970-01-01T00:00:00.002 |
| 2000 | e | 1970-01-01T00:00:00.005 |
| 2100 | f | 1970-01-01T00:00:00.006 |
| 2200 | g | 1970-01-01T00:00:00.007 |
| 2400 | h | 1970-01-01T00:00:00.008 |
+------+---+-------------------------+

DELETE FROM my_table WHERE a < 150;

Affected Rows: 1

-- SQLNESS SORT_RESULT 3 1
SELECT * FROM my_table;

+------+---+-------------------------+
| a    | b | ts                      |
+------+---+-------------------------+
| 1100 | c | 1970-01-01T00:00:00.003 |
| 1200 | d | 1970-01-01T00:00:00.004 |
| 200  | b | 1970-01-01T00:00:00.002 |
| 2000 | e | 1970-01-01T00:00:00.005 |
| 2100 | f | 1970-01-01T00:00:00.006 |
| 2200 | g | 1970-01-01T00:00:00.007 |
| 2400 | h | 1970-01-01T00:00:00.008 |
+------+---+-------------------------+

DELETE FROM my_table WHERE a < 2200 AND a > 1500;

Affected Rows: 2

-- SQLNESS SORT_RESULT 3 1
SELECT * FROM my_table;

+------+---+-------------------------+
| a    | b | ts                      |
+------+---+-------------------------+
| 1100 | c | 1970-01-01T00:00:00.003 |
| 1200 | d | 1970-01-01T00:00:00.004 |
| 200  | b | 1970-01-01T00:00:00.002 |
| 2200 | g | 1970-01-01T00:00:00.007 |
| 2400 | h | 1970-01-01T00:00:00.008 |
+------+---+-------------------------+

DELETE FROM my_table WHERE a < 2500;

Affected Rows: 5

SELECT * FROM my_table;

+---+---+----+
| a | b | ts |
+---+---+----+
+---+---+----+

DROP TABLE my_table;

Affected Rows: 0

CREATE TABLE my_table (
  a INT PRIMARY KEY,
  b STRING,
  ts TIMESTAMP TIME INDEX,
)
PARTITION ON COLUMNS (a) ();

Affected Rows: 0

-- SQLNESS REPLACE (\d{13}) ID
SELECT table_catalog, table_schema, table_name, partition_name, partition_expression, greptime_partition_id from information_schema.partitions WHERE table_name = 'my_table' ORDER BY table_catalog, table_schema, table_name, partition_name;

+---------------+--------------+------------+----------------+----------------------+-----------------------+
| table_catalog | table_schema | table_name | partition_name | partition_expression | greptime_partition_id |
+---------------+--------------+------------+----------------+----------------------+-----------------------+
| greptime      | public       | my_table   | p0             | a                    | ID         |
+---------------+--------------+------------+----------------+----------------------+-----------------------+

-- SQLNESS REPLACE (\d{13}) REGION_ID
-- SQLNESS REPLACE (\d{1}) PEER_ID
SELECT region_id, peer_id, is_leader, status FROM information_schema.region_peers ORDER BY peer_id;

+---------------+---------+-----------+--------+
| region_id     | peer_id | is_leader | status |
+---------------+---------+-----------+--------+
| REGION_ID | PEER_ID       | Yes       | ALIVE  |
+---------------+---------+-----------+--------+

INSERT INTO my_table VALUES
    (100, 'a', 1),
    (200, 'b', 2),
    (1100, 'c', 3),
    (1200, 'd', 4),
    (2000, 'e', 5),
    (2100, 'f', 6),
    (2200, 'g', 7),
    (2400, 'h', 8);

Affected Rows: 8

-- SQLNESS SORT_RESULT 3 1
SELECT * FROM my_table;

+------+---+-------------------------+
| a    | b | ts                      |
+------+---+-------------------------+
| 100  | a | 1970-01-01T00:00:00.001 |
| 1100 | c | 1970-01-01T00:00:00.003 |
| 1200 | d | 1970-01-01T00:00:00.004 |
| 200  | b | 1970-01-01T00:00:00.002 |
| 2000 | e | 1970-01-01T00:00:00.005 |
| 2100 | f | 1970-01-01T00:00:00.006 |
| 2200 | g | 1970-01-01T00:00:00.007 |
| 2400 | h | 1970-01-01T00:00:00.008 |
+------+---+-------------------------+

DROP TABLE my_table;

Affected Rows: 0

-- incorrect partition rules
CREATE TABLE invalid_rule (
  a INT PRIMARY KEY,
  b STRING,
  ts TIMESTAMP TIME INDEX,
)
PARTITION ON COLUMNS (a) (
  a < 10,
  a > 10 AND a < 20,
  a >= 20
);

Error: 1004(InvalidArguments), Checkpoint `a=10` is not covered

CREATE TABLE invalid_rule2 (
  a INT,
  b STRING PRIMARY KEY,
  ts TIMESTAMP TIME INDEX,
)
PARTITION ON COLUMNS (b) (
  b < 'abc',
  b >= 'abca' AND b < 'o',
  b >= 'o',
);

Error: 1004(InvalidArguments), Checkpoint `b=abc` is not covered

CREATE TABLE invalid_rule3 (
  a INT,
  b STRING PRIMARY KEY,
  ts TIMESTAMP TIME INDEX,
)
PARTITION ON COLUMNS (b) (
  b >= 'a',
  b <= 'o',
);

Error: 1004(InvalidArguments), Checkpoint `b=a` is overlapped

CREATE TABLE valid_rule (
  a INT,
  b STRING,
  ts TIMESTAMP TIME INDEX,
  PRIMARY KEY (a, b)
)
PARTITION ON COLUMNS (a, b) (
  a < 10,
  a = 10 AND b < 'a',
  a = 10 AND b >= 'a' AND b < 'o',
  a = 10 AND b >= 'o',
  a > 10,
);

Affected Rows: 0

-- SQLNESS REPLACE (metrics.*) REDACTED
-- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED
-- SQLNESS REPLACE (Hash.*) REDACTED
-- SQLNESS REPLACE (-+) -
-- SQLNESS REPLACE (\s\s+) _
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
EXPLAIN ANALYZE SELECT * FROM valid_rule;

+-+-+-+
| stage | node | plan_|
+-+-+-+
| 0_| 0_|_CooperativeExec REDACTED
|_|_|_MergeScanExec: REDACTED
|_|_|_|
| 1_| 0_|_CooperativeExec REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":0, "mem_ranges":0, "files":0, "file_ranges":0} REDACTED
|_|_|_|
| 1_| 1_|_CooperativeExec REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":0, "mem_ranges":0, "files":0, "file_ranges":0} REDACTED
|_|_|_|
| 1_| 2_|_CooperativeExec REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":0, "mem_ranges":0, "files":0, "file_ranges":0} REDACTED
|_|_|_|
| 1_| 3_|_CooperativeExec REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":0, "mem_ranges":0, "files":0, "file_ranges":0} REDACTED
|_|_|_|
| 1_| 4_|_CooperativeExec REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":0, "mem_ranges":0, "files":0, "file_ranges":0} REDACTED
|_|_|_|
|_|_| Total rows: 0_|
+-+-+-+

-- SQLNESS REPLACE (metrics.*) REDACTED
-- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED
-- SQLNESS REPLACE (Hash.*) REDACTED
-- SQLNESS REPLACE (-+) -
-- SQLNESS REPLACE (\s\s+) _
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
EXPLAIN ANALYZE SELECT * FROM valid_rule
WHERE a < 10;

+-+-+-+
| stage | node | plan_|
+-+-+-+
| 0_| 0_|_CooperativeExec REDACTED
|_|_|_MergeScanExec: REDACTED
|_|_|_|
| 1_| 0_|_CooperativeExec REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":0, "mem_ranges":0, "files":0, "file_ranges":0} REDACTED
|_|_|_|
|_|_| Total rows: 0_|
+-+-+-+

-- SQLNESS REPLACE (metrics.*) REDACTED
-- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED
-- SQLNESS REPLACE (Hash.*) REDACTED
-- SQLNESS REPLACE (-+) -
-- SQLNESS REPLACE (\s\s+) _
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
EXPLAIN ANALYZE SELECT * FROM valid_rule
WHERE a = 10 AND b= 'z';

+-+-+-+
| stage | node | plan_|
+-+-+-+
| 0_| 0_|_CooperativeExec REDACTED
|_|_|_MergeScanExec: REDACTED
|_|_|_|
| 1_| 0_|_CooperativeExec REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":0, "mem_ranges":0, "files":0, "file_ranges":0} REDACTED
|_|_|_|
|_|_| Total rows: 0_|
+-+-+-+

-- SQLNESS REPLACE (metrics.*) REDACTED
-- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED
-- SQLNESS REPLACE (Hash.*) REDACTED
-- SQLNESS REPLACE (-+) -
-- SQLNESS REPLACE (\s\s+) _
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
EXPLAIN ANALYZE SELECT * FROM valid_rule
WHERE a = 10 OR b= 'z';

+-+-+-+
| stage | node | plan_|
+-+-+-+
| 0_| 0_|_CooperativeExec REDACTED
|_|_|_MergeScanExec: REDACTED
|_|_|_|
| 1_| 0_|_FilterExec: a@0 = 10 OR b@1 = z REDACTED
|_|_|_CooperativeExec REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":0, "mem_ranges":0, "files":0, "file_ranges":0} REDACTED
|_|_|_|
| 1_| 1_|_FilterExec: a@0 = 10 OR b@1 = z REDACTED
|_|_|_CooperativeExec REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":0, "mem_ranges":0, "files":0, "file_ranges":0} REDACTED
|_|_|_|
| 1_| 2_|_FilterExec: a@0 = 10 OR b@1 = z REDACTED
|_|_|_CooperativeExec REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":0, "mem_ranges":0, "files":0, "file_ranges":0} REDACTED
|_|_|_|
| 1_| 3_|_FilterExec: a@0 = 10 OR b@1 = z REDACTED
|_|_|_CooperativeExec REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":0, "mem_ranges":0, "files":0, "file_ranges":0} REDACTED
|_|_|_|
| 1_| 4_|_FilterExec: a@0 = 10 OR b@1 = z REDACTED
|_|_|_CooperativeExec REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":0, "mem_ranges":0, "files":0, "file_ranges":0} REDACTED
|_|_|_|
|_|_| Total rows: 0_|
+-+-+-+

-- SQLNESS REPLACE (metrics.*) REDACTED
-- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED
-- SQLNESS REPLACE (Hash.*) REDACTED
-- SQLNESS REPLACE (-+) -
-- SQLNESS REPLACE (\s\s+) _
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
EXPLAIN ANALYZE SELECT * FROM valid_rule
WHERE a = 10 OR ts > 1;

+-+-+-+
| stage | node | plan_|
+-+-+-+
| 0_| 0_|_CooperativeExec REDACTED
|_|_|_MergeScanExec: REDACTED
|_|_|_|
| 1_| 0_|_FilterExec: a@0 = 10 OR ts@2 > 1 REDACTED
|_|_|_CooperativeExec REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":0, "mem_ranges":0, "files":0, "file_ranges":0} REDACTED
|_|_|_|
| 1_| 1_|_FilterExec: a@0 = 10 OR ts@2 > 1 REDACTED
|_|_|_CooperativeExec REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":0, "mem_ranges":0, "files":0, "file_ranges":0} REDACTED
|_|_|_|
| 1_| 2_|_FilterExec: a@0 = 10 OR ts@2 > 1 REDACTED
|_|_|_CooperativeExec REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":0, "mem_ranges":0, "files":0, "file_ranges":0} REDACTED
|_|_|_|
| 1_| 3_|_FilterExec: a@0 = 10 OR ts@2 > 1 REDACTED
|_|_|_CooperativeExec REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":0, "mem_ranges":0, "files":0, "file_ranges":0} REDACTED
|_|_|_|
| 1_| 4_|_FilterExec: a@0 = 10 OR ts@2 > 1 REDACTED
|_|_|_CooperativeExec REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":0, "mem_ranges":0, "files":0, "file_ranges":0} REDACTED
|_|_|_|
|_|_| Total rows: 0_|
+-+-+-+

-- SQLNESS REPLACE (metrics.*) REDACTED
-- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED
-- SQLNESS REPLACE (Hash.*) REDACTED
-- SQLNESS REPLACE (-+) -
-- SQLNESS REPLACE (\s\s+) _
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
EXPLAIN ANALYZE SELECT * FROM valid_rule
WHERE a = 10 OR (ts > 1 AND b ='h');

+-+-+-+
| stage | node | plan_|
+-+-+-+
| 0_| 0_|_CooperativeExec REDACTED
|_|_|_MergeScanExec: REDACTED
|_|_|_|
| 1_| 0_|_FilterExec: a@0 = 10 OR ts@2 > 1 AND b@1 = h REDACTED
|_|_|_CooperativeExec REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":0, "mem_ranges":0, "files":0, "file_ranges":0} REDACTED
|_|_|_|
| 1_| 1_|_FilterExec: a@0 = 10 OR ts@2 > 1 AND b@1 = h REDACTED
|_|_|_CooperativeExec REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":0, "mem_ranges":0, "files":0, "file_ranges":0} REDACTED
|_|_|_|
| 1_| 2_|_FilterExec: a@0 = 10 OR ts@2 > 1 AND b@1 = h REDACTED
|_|_|_CooperativeExec REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":0, "mem_ranges":0, "files":0, "file_ranges":0} REDACTED
|_|_|_|
| 1_| 3_|_FilterExec: a@0 = 10 OR ts@2 > 1 AND b@1 = h REDACTED
|_|_|_CooperativeExec REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":0, "mem_ranges":0, "files":0, "file_ranges":0} REDACTED
|_|_|_|
| 1_| 4_|_FilterExec: a@0 = 10 OR ts@2 > 1 AND b@1 = h REDACTED
|_|_|_CooperativeExec REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":0, "mem_ranges":0, "files":0, "file_ranges":0} REDACTED
|_|_|_|
|_|_| Total rows: 0_|
+-+-+-+

DROP TABLE valid_rule;

Affected Rows: 0

-- Issue https://github.com/GreptimeTeam/greptimedb/issues/4247
-- Partition rule with unary operator
CREATE TABLE `molestiAe` (
  `sImiLiQUE` FLOAT NOT NULL,
  `amEt` TIMESTAMP(6) TIME INDEX,
  `EXpLICaBo` DOUBLE,
  PRIMARY KEY (`sImiLiQUE`)
) PARTITION ON COLUMNS (`sImiLiQUE`) (
  `sImiLiQUE` < -1,
  `sImiLiQUE` >= -1 AND `sImiLiQUE` < -0,
  `sImiLiQUE` >= 0
);

Affected Rows: 0

INSERT INTO `molestiAe` VALUES
  (-2, 0, 0),
  (-0.9, 0, 0),
  (1, 0, 0);

Affected Rows: 3

-- SQLNESS SORT_RESULT 3 1
SELECT * FROM `molestiAe`;

+-----------+---------------------+-----------+
| sImiLiQUE | amEt                | EXpLICaBo |
+-----------+---------------------+-----------+
| -0.9      | 1970-01-01T00:00:00 | 0.0       |
| -2.0      | 1970-01-01T00:00:00 | 0.0       |
| 1.0       | 1970-01-01T00:00:00 | 0.0       |
+-----------+---------------------+-----------+

DROP TABLE `molestiAe`;

Affected Rows: 0