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

create table if not exists last_non_null_table(
    host string,
    ts timestamp,
    cpu double,
    memory double,
    TIME INDEX (ts),
    PRIMARY KEY(host)
)
engine=mito
with('merge_mode'='last_non_null');

Affected Rows: 0

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

Affected Rows: 2

INSERT INTO last_non_null_table VALUES ('host1', 0, NULL, 10), ('host2', 1, 11, NULL);

Affected Rows: 2

SELECT * from last_non_null_table ORDER BY host, ts;

+-------+-------------------------+------+--------+
| host  | ts                      | cpu  | memory |
+-------+-------------------------+------+--------+
| host1 | 1970-01-01T00:00:00     | 0.0  | 10.0   |
| host2 | 1970-01-01T00:00:00.001 | 11.0 | 1.0    |
+-------+-------------------------+------+--------+

INSERT INTO last_non_null_table VALUES ('host1', 0, 20, NULL);

Affected Rows: 1

SELECT * from last_non_null_table ORDER BY host, ts;

+-------+-------------------------+------+--------+
| host  | ts                      | cpu  | memory |
+-------+-------------------------+------+--------+
| host1 | 1970-01-01T00:00:00     | 20.0 | 10.0   |
| host2 | 1970-01-01T00:00:00.001 | 11.0 | 1.0    |
+-------+-------------------------+------+--------+

INSERT INTO last_non_null_table VALUES ('host1', 0, NULL, NULL);

Affected Rows: 1

SELECT * from last_non_null_table ORDER BY host, ts;

+-------+-------------------------+------+--------+
| host  | ts                      | cpu  | memory |
+-------+-------------------------+------+--------+
| host1 | 1970-01-01T00:00:00     | 20.0 | 10.0   |
| host2 | 1970-01-01T00:00:00.001 | 11.0 | 1.0    |
+-------+-------------------------+------+--------+

DROP TABLE last_non_null_table;

Affected Rows: 0

create table if not exists last_row_table(
    host string,
    ts timestamp,
    cpu double,
    memory double,
    TIME INDEX (ts),
    PRIMARY KEY(host)
)
engine=mito
with('merge_mode'='last_row');

Affected Rows: 0

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

Affected Rows: 2

INSERT INTO last_row_table VALUES ('host1', 0, NULL, 10), ('host2', 1, 11, NULL);

Affected Rows: 2

SELECT * from last_row_table ORDER BY host, ts;

+-------+-------------------------+------+--------+
| host  | ts                      | cpu  | memory |
+-------+-------------------------+------+--------+
| host1 | 1970-01-01T00:00:00     |      | 10.0   |
| host2 | 1970-01-01T00:00:00.001 | 11.0 |        |
+-------+-------------------------+------+--------+

DROP TABLE last_row_table;

Affected Rows: 0

CREATE TABLE IF NOT EXISTS `delete_between` (
  `time` TIMESTAMP(0) NOT NULL,
  `code` STRING NULL,
  `name` STRING NULL,
  `status` TINYINT NULL,
  TIME INDEX (`time`),
  PRIMARY KEY (`code`)
) ENGINE=mito WITH(
  merge_mode = 'last_non_null'
);

Affected Rows: 0

INSERT INTO `delete_between` (`time`, `code`, `name`, `status`) VALUES ('2024-11-26 10:00:00', 'achn', '1.png', 0);

Affected Rows: 1

INSERT INTO `delete_between` (`time`, `code`, `name`, `status`) VALUES ('2024-11-26 10:01:00', 'achn', '2.png', 0);

Affected Rows: 1

INSERT INTO `delete_between` (`time`, `code`, `name`, `status`) VALUES ('2024-11-26 10:02:00', 'achn', '3.png', 1);

Affected Rows: 1

SELECT * FROM `delete_between`;

+---------------------+------+-------+--------+
| time                | code | name  | status |
+---------------------+------+-------+--------+
| 2024-11-26T10:00:00 | achn | 1.png | 0      |
| 2024-11-26T10:01:00 | achn | 2.png | 0      |
| 2024-11-26T10:02:00 | achn | 3.png | 1      |
+---------------------+------+-------+--------+

DELETE FROM `delete_between`;

Affected Rows: 3

INSERT INTO `delete_between` (`time`, `code`, `name`) VALUES ('2024-11-26 10:00:00', 'achn', '1.png');

Affected Rows: 1

INSERT INTO `delete_between` (`time`, `code`, `name`) VALUES ('2024-11-26 10:01:00', 'achn', '2.png');

Affected Rows: 1

INSERT INTO `delete_between` (`time`, `code`, `name`) VALUES ('2024-11-26 10:02:00', 'achn', '3.png');

Affected Rows: 1

SELECT * FROM `delete_between`;

+---------------------+------+-------+--------+
| time                | code | name  | status |
+---------------------+------+-------+--------+
| 2024-11-26T10:00:00 | achn | 1.png |        |
| 2024-11-26T10:01:00 | achn | 2.png |        |
| 2024-11-26T10:02:00 | achn | 3.png |        |
+---------------------+------+-------+--------+

DROP TABLE `delete_between`;

Affected Rows: 0

-- SQLNESS REPLACE (\sat\sline\s\d+\scolumn\s\d+)
create table if not exists invalid_merge_mode(
    host string,
    ts timestamp,
    cpu double,
    memory double,
    TIME INDEX (ts),
    PRIMARY KEY(host)
)
engine=mito
with('merge_mode'='first_row');

Error: 1004(InvalidArguments), Invalid options: Matching variant not found

create table if not exists invalid_merge_mode(
    host string,
    ts timestamp,
    cpu double,
    memory double,
    TIME INDEX (ts),
    PRIMARY KEY(host)
)
engine=mito
with('merge_mode'='last_non_null', 'append_mode'='true');

Error: 1004(InvalidArguments), Invalid region options, only last_row merge_mode is allowed when append_mode is enabled