CREATE TABLE system_metrics (
    host STRING,
    idc STRING,
    cpu_util DOUBLE,
    memory_util DOUBLE,
    disk_util DOUBLE,
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
    PRIMARY KEY(host, idc),
    TIME INDEX(ts)
);

Affected Rows: 0

INSERT INTO system_metrics
VALUES
    ("host1", "idc_a", 11.8, 10.3, 10.3, 1667446797450),
    ("host2", "idc_a", 80.0, 70.3, 90.0, 1667446797450),
    ("host1", "idc_b", 50.0, 66.7, 40.6, 1667446797450);

Affected Rows: 3

SELECT * FROM system_metrics;

+-------+-------+----------+-------------+-----------+-------------------------+
| host  | idc   | cpu_util | memory_util | disk_util | ts                      |
+-------+-------+----------+-------------+-----------+-------------------------+
| host1 | idc_a | 11.8     | 10.3        | 10.3      | 2022-11-03T03:39:57.450 |
| host1 | idc_b | 50.0     | 66.7        | 40.6      | 2022-11-03T03:39:57.450 |
| host2 | idc_a | 80.0     | 70.3        | 90.0      | 2022-11-03T03:39:57.450 |
+-------+-------+----------+-------------+-----------+-------------------------+

SELECT count(*) FROM system_metrics;

+----------+
| count(*) |
+----------+
| 3        |
+----------+

SELECT avg(cpu_util) FROM system_metrics;

+------------------------------+
| avg(system_metrics.cpu_util) |
+------------------------------+
| 47.26666666666667            |
+------------------------------+

SELECT idc, avg(memory_util) FROM system_metrics GROUP BY idc ORDER BY idc;

+-------+---------------------------------+
| idc   | avg(system_metrics.memory_util) |
+-------+---------------------------------+
| idc_a | 40.3                            |
| idc_b | 66.7                            |
+-------+---------------------------------+

create table foo (
    host string,
    ts timestamp DEFAULT '2023-04-29 00:00:00+00:00',
    cpu double default 0,
    TIME INDEX (ts),
    PRIMARY KEY(host)
) engine=mito;

Affected Rows: 0

insert into foo (host, cpu, ts) values ('host1', 1.1, '2000-01-01 00:00:00+00:00');

Affected Rows: 1

insert into foo (host, cpu) values ('host2', 2.2);

Affected Rows: 1

insert into foo (host) values ('host3');

Affected Rows: 1

select * from foo order by ts;

+-------+---------------------+-----+
| host  | ts                  | cpu |
+-------+---------------------+-----+
| host1 | 2000-01-01T00:00:00 | 1.1 |
| host2 | 2023-04-29T00:00:00 | 2.2 |
| host3 | 2023-04-29T00:00:00 | 0.0 |
+-------+---------------------+-----+

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 |
+-------+-------------------------+-----+

delete from t1;

Affected Rows: 2

-- do not support DELETE FROM physical table for now
delete from phy;

Affected Rows: 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;

++
++

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

Affected Rows: 2

-- SQLNESS ARG restart=true
SELECT * FROM system_metrics;

+-------+-------+----------+-------------+-----------+-------------------------+
| host  | idc   | cpu_util | memory_util | disk_util | ts                      |
+-------+-------+----------+-------------+-----------+-------------------------+
| host1 | idc_a | 11.8     | 10.3        | 10.3      | 2022-11-03T03:39:57.450 |
| host1 | idc_b | 50.0     | 66.7        | 40.6      | 2022-11-03T03:39:57.450 |
| host2 | idc_a | 80.0     | 70.3        | 90.0      | 2022-11-03T03:39:57.450 |
+-------+-------+----------+-------------+-----------+-------------------------+

select * from foo order by host asc;

+-------+---------------------+-----+
| host  | ts                  | cpu |
+-------+---------------------+-----+
| host1 | 2000-01-01T00:00:00 | 1.1 |
| host2 | 2023-04-29T00:00:00 | 2.2 |
| host3 | 2023-04-29T00:00:00 | 0.0 |
+-------+---------------------+-----+

SELECT * from t1 order by ts desc;

++
++

SELECT * from t2 order by ts desc;

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

DROP TABLE t1;

Affected Rows: 0

DROP TABLE t2;

Affected Rows: 0

DROP TABLE phy;

Affected Rows: 0

DROP TABLE system_metrics;

Affected Rows: 0

DROP TABLE foo;

Affected Rows: 0

-- SQLNESS PROTOCOL MYSQL
SET MAX_EXECUTION_TIME = 2000;

affected_rows: 0

-- SQLNESS PROTOCOL MYSQL
SHOW VARIABLES MAX_EXECUTION_TIME;

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
|               |       |
+---------------+-------+

-- SQLNESS PROTOCOL MYSQL
SET MAX_EXECUTION_TIME = 0;

affected_rows: 0