greptimedb/tests/cases/standalone/common/insert/logical_metric_table.result

CREATE TABLE phy (ts timestamp time index, val double) engine=metric with ("physical_metric_table" = "");

Affected Rows: 0

CREATE TABLE t1 (ts timestamp time index, val double, host string primary key) engine = metric with ("on_physical_table" = "phy");

Affected Rows: 0

INSERT INTO t1 VALUES ('host1',0, 0), ('host2', 1, 1,);

Affected Rows: 2

SELECT * from t1;

+-------+-------------------------+-----+
| host  | ts                      | val |
+-------+-------------------------+-----+
| host2 | 1970-01-01T00:00:00.001 | 1.0 |
| host1 | 1970-01-01T00:00:00     | 0.0 |
+-------+-------------------------+-----+

CREATE TABLE t2 (ts timestamp time index, job string primary key, val double) engine = metric with ("on_physical_table" = "phy");

Affected Rows: 0

SELECT * from t2;

+-----+----+-----+
| job | ts | val |
+-----+----+-----+
+-----+----+-----+

INSERT INTO t2 VALUES ('job1', 0, 0), ('job2', 1, 1);

Affected Rows: 2

SELECT * from t2;

+------+-------------------------+-----+
| job  | ts                      | val |
+------+-------------------------+-----+
| job1 | 1970-01-01T00:00:00     | 0.0 |
| job2 | 1970-01-01T00:00:00.001 | 1.0 |
+------+-------------------------+-----+

DROP TABLE t1;

Affected Rows: 0

DROP TABLE t2;

Affected Rows: 0

DESC TABLE phy;

+------------+----------------------+-----+------+---------+---------------+
| Column     | Type                 | Key | Null | Default | Semantic Type |
+------------+----------------------+-----+------+---------+---------------+
| ts         | TimestampMillisecond | PRI | NO   |         | TIMESTAMP     |
| val        | Float64              |     | YES  |         | FIELD         |
| __table_id | UInt32               | PRI | NO   |         | TAG           |
| __tsid     | UInt64               | PRI | NO   |         | TAG           |
| host       | String               | PRI | YES  |         | TAG           |
| job        | String               | PRI | YES  |         | TAG           |
+------------+----------------------+-----+------+---------+---------------+

SELECT ts, val, __tsid, host, job FROM phy;

+-------------------------+-----+----------------------+-------+------+
| ts                      | val | __tsid               | host  | job  |
+-------------------------+-----+----------------------+-------+------+
| 1970-01-01T00:00:00.001 | 1.0 | 7947983149541006936  | host2 |      |
| 1970-01-01T00:00:00     | 0.0 | 13882403126406556045 | host1 |      |
| 1970-01-01T00:00:00     | 0.0 | 6248409809737953425  |       | job1 |
| 1970-01-01T00:00:00.001 | 1.0 | 12867770218286207316 |       | job2 |
+-------------------------+-----+----------------------+-------+------+

DROP TABLE phy;

Affected Rows: 0

CREATE TABLE phy_default (ts timestamp time index, val double default 42) engine=metric with ("physical_metric_table" = "");

Affected Rows: 0

CREATE TABLE t_default (ts timestamp time index, val double default 42, host string primary key) engine = metric with ("on_physical_table" = "phy_default");

Affected Rows: 0

INSERT INTO t_default (host, ts) VALUES ('host1', 0), ('host2', 1);

Affected Rows: 2

SELECT host, ts, val FROM t_default ORDER BY host;

+-------+-------------------------+------+
| host  | ts                      | val  |
+-------+-------------------------+------+
| host1 | 1970-01-01T00:00:00     | 42.0 |
| host2 | 1970-01-01T00:00:00.001 | 42.0 |
+-------+-------------------------+------+

-- SQLNESS REPLACE (region\s\d+\(\d+\,\s\d+\)) region
INSERT INTO t_default (host, val) VALUES ('host3', 3);

Error: 1004(InvalidArguments), Invalid request for region, reason: missing required time index column ts

DROP TABLE t_default;

Affected Rows: 0

DROP TABLE phy_default;

Affected Rows: 0

CREATE TABLE phy (
    ts timestamp time index,
    val double
) engine = metric with (
    "physical_metric_table" = "",
    "memtable.type" = "bulk",
    "primary_key_encoding" = "sparse"
);

Affected Rows: 0

CREATE TABLE t1 (ts timestamp time index, val double, host string primary key) engine = metric with ("on_physical_table" = "phy");

Affected Rows: 0

INSERT INTO t1 VALUES ('host1',0, 0), ('host2', 1, 1,);

Affected Rows: 2

SELECT * from t1;

+-------+-------------------------+-----+
| host  | ts                      | val |
+-------+-------------------------+-----+
| host2 | 1970-01-01T00:00:00.001 | 1.0 |
| host1 | 1970-01-01T00:00:00     | 0.0 |
+-------+-------------------------+-----+

CREATE TABLE t2 (ts timestamp time index, job string primary key, val double) engine = metric with ("on_physical_table" = "phy");

Affected Rows: 0

SELECT * from t2;

+-----+----+-----+
| job | ts | val |
+-----+----+-----+
+-----+----+-----+

INSERT INTO t2 VALUES ('job1', 0, 0), ('job2', 1, 1);

Affected Rows: 2

SELECT * from t2;

+------+-------------------------+-----+
| job  | ts                      | val |
+------+-------------------------+-----+
| job1 | 1970-01-01T00:00:00     | 0.0 |
| job2 | 1970-01-01T00:00:00.001 | 1.0 |
+------+-------------------------+-----+

ADMIN flush_table('phy');

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

-- SQLNESS ARG restart=true
INSERT INTO t2 VALUES ('job3', 0, 0), ('job4', 1, 1);

Affected Rows: 2

SELECT * from t1;

+-------+-------------------------+-----+
| host  | ts                      | val |
+-------+-------------------------+-----+
| host2 | 1970-01-01T00:00:00.001 | 1.0 |
| host1 | 1970-01-01T00:00:00     | 0.0 |
+-------+-------------------------+-----+

SELECT * from t2;

+------+-------------------------+-----+
| job  | ts                      | val |
+------+-------------------------+-----+
| job3 | 1970-01-01T00:00:00     | 0.0 |
| job1 | 1970-01-01T00:00:00     | 0.0 |
| job4 | 1970-01-01T00:00:00.001 | 1.0 |
| job2 | 1970-01-01T00:00:00.001 | 1.0 |
+------+-------------------------+-----+

DROP TABLE t1;

Affected Rows: 0

DROP TABLE t2;

Affected Rows: 0

DESC TABLE phy;

+------------+----------------------+-----+------+---------+---------------+
| Column     | Type                 | Key | Null | Default | Semantic Type |
+------------+----------------------+-----+------+---------+---------------+
| ts         | TimestampMillisecond | PRI | NO   |         | TIMESTAMP     |
| val        | Float64              |     | YES  |         | FIELD         |
| __table_id | UInt32               | PRI | NO   |         | TAG           |
| __tsid     | UInt64               | PRI | NO   |         | TAG           |
| host       | String               | PRI | YES  |         | TAG           |
| job        | String               | PRI | YES  |         | TAG           |
+------------+----------------------+-----+------+---------+---------------+

DROP TABLE phy;

Affected Rows: 0