greptimedb/tests/cases/standalone/common/alter/alter_table.result

CREATE TABLE test_alt_table(h INTEGER, i INTEGER, j TIMESTAMP TIME INDEX, PRIMARY KEY (h, i));

Affected Rows: 0

DESC TABLE test_alt_table;

+--------+----------------------+-----+------+---------+---------------+
| Column | Type                 | Key | Null | Default | Semantic Type |
+--------+----------------------+-----+------+---------+---------------+
| h      | Int32                | PRI | YES  |         | TAG           |
| i      | Int32                | PRI | YES  |         | TAG           |
| j      | TimestampMillisecond | PRI | NO   |         | TIMESTAMP     |
+--------+----------------------+-----+------+---------+---------------+

INSERT INTO test_alt_table VALUES (1, 1, 0), (2, 2, 1);

Affected Rows: 2

-- TODO: It may result in an error if `k` is with type INTEGER.
-- Error: 3001(EngineExecuteQuery), Invalid argument error: column types must match schema types, expected Int32 but found Utf8 at column index 3
ALTER TABLE test_alt_table ADD COLUMN k STRING PRIMARY KEY;

Affected Rows: 0

DESC TABLE test_alt_table;

+--------+----------------------+-----+------+---------+---------------+
| Column | Type                 | Key | Null | Default | Semantic Type |
+--------+----------------------+-----+------+---------+---------------+
| h      | Int32                | PRI | YES  |         | TAG           |
| i      | Int32                | PRI | YES  |         | TAG           |
| j      | TimestampMillisecond | PRI | NO   |         | TIMESTAMP     |
| k      | String               | PRI | YES  |         | TAG           |
+--------+----------------------+-----+------+---------+---------------+

SELECT * FROM test_alt_table;

+---+---+-------------------------+---+
| h | i | j                       | k |
+---+---+-------------------------+---+
| 1 | 1 | 1970-01-01T00:00:00     |   |
| 2 | 2 | 1970-01-01T00:00:00.001 |   |
+---+---+-------------------------+---+

SELECT * FROM test_alt_table WHERE i = 1;

+---+---+---------------------+---+
| h | i | j                   | k |
+---+---+---------------------+---+
| 1 | 1 | 1970-01-01T00:00:00 |   |
+---+---+---------------------+---+

-- SQLNESS ARG restart=true
ALTER TABLE test_alt_table ADD COLUMN m INTEGER;

Affected Rows: 0

DESC TABLE test_alt_table;

+--------+----------------------+-----+------+---------+---------------+
| Column | Type                 | Key | Null | Default | Semantic Type |
+--------+----------------------+-----+------+---------+---------------+
| h      | Int32                | PRI | YES  |         | TAG           |
| i      | Int32                | PRI | YES  |         | TAG           |
| j      | TimestampMillisecond | PRI | NO   |         | TIMESTAMP     |
| k      | String               | PRI | YES  |         | TAG           |
| m      | Int32                |     | YES  |         | FIELD         |
+--------+----------------------+-----+------+---------+---------------+

DROP TABLE test_alt_table;

Affected Rows: 0

-- to test if same name column can be added
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, 1),
    ('host2', 1, 0,);

Affected Rows: 2

SELECT
    *
FROM
    t1;

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

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

Affected Rows: 0

ALTER TABLE
    t1
ADD
    COLUMN `at` STRING;

Affected Rows: 0

ALTER TABLE
    t2
ADD
    COLUMN at3 STRING;

Affected Rows: 0

ALTER TABLE
    t2
ADD
    COLUMN `at` STRING;

Affected Rows: 0

ALTER TABLE
    t2
ADD
    COLUMN at2 STRING;

Affected Rows: 0

ALTER TABLE 
    t2 
ADD 
    COLUMN at4 UINT16;

Affected Rows: 0

INSERT INTO
    t2
VALUES
    ("loc_1", "loc_2", "loc_3", 2, 'job1', 0, 1);

Affected Rows: 1

SELECT
    *
FROM
    t2;

+-------+-------+-------+-----+------+---------------------+-----+
| at    | at2   | at3   | at4 | job  | ts                  | val |
+-------+-------+-------+-----+------+---------------------+-----+
| loc_1 | loc_2 | loc_3 | 2   | job1 | 1970-01-01T00:00:00 | 1.0 |
+-------+-------+-------+-----+------+---------------------+-----+

DROP TABLE t1;

Affected Rows: 0

DROP TABLE t2;

Affected Rows: 0

DROP TABLE phy;

Affected Rows: 0