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 ALTER TABLE test_alt_table ADD COLUMN dt DATETIME; Affected Rows: 0 -- Should fail issue #5422 ALTER TABLE test_alt_table ADD COLUMN n interval; Error: 1001(Unsupported), Not supported: Add column with interval type -- Should fail issue #5422 ALTER TABLE test_alt_table MODIFY COLUMN m interval; Error: 1001(Unsupported), Not supported: Modify column type to interval type INSERT INTO test_alt_table (h, i, j, m, dt) VALUES (42, 42, 0, 11, 0); Affected Rows: 1 ALTER TABLE test_alt_table MODIFY COLUMN m Float64; Affected Rows: 0 SELECT * FROM test_alt_table; +----+----+-------------------------+---+------+---------------------+ | h | i | j | k | m | dt | +----+----+-------------------------+---+------+---------------------+ | 1 | 1 | 1970-01-01T00:00:00 | | | | | 2 | 2 | 1970-01-01T00:00:00.001 | | | | | 42 | 42 | 1970-01-01T00:00:00 | | 11.0 | 1970-01-01T00:00:00 | +----+----+-------------------------+---+------+---------------------+ ALTER TABLE test_alt_table MODIFY COLUMN m INTEGER; Affected Rows: 0 SELECT * FROM test_alt_table; +----+----+-------------------------+---+----+---------------------+ | h | i | j | k | m | dt | +----+----+-------------------------+---+----+---------------------+ | 1 | 1 | 1970-01-01T00:00:00 | | | | | 2 | 2 | 1970-01-01T00:00:00.001 | | | | | 42 | 42 | 1970-01-01T00:00:00 | | 11 | 1970-01-01T00:00:00 | +----+----+-------------------------+---+----+---------------------+ ALTER TABLE test_alt_table MODIFY COLUMN m BOOLEAN; Affected Rows: 0 SELECT * FROM test_alt_table; +----+----+-------------------------+---+------+---------------------+ | h | i | j | k | m | dt | +----+----+-------------------------+---+------+---------------------+ | 1 | 1 | 1970-01-01T00:00:00 | | | | | 2 | 2 | 1970-01-01T00:00:00.001 | | | | | 42 | 42 | 1970-01-01T00:00:00 | | true | 1970-01-01T00:00:00 | +----+----+-------------------------+---+------+---------------------+ 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 | Boolean | | YES | | FIELD | | dt | TimestampMicrosecond | | YES | | FIELD | +--------+----------------------+-----+------+---------+---------------+ DROP TABLE test_alt_table; Affected Rows: 0 -- test if column with default value can change type properly CREATE TABLE test_alt_table_default(h INTEGER, i Float64 DEFAULT 0.0, j TIMESTAMP TIME INDEX, PRIMARY KEY (h)); Affected Rows: 0 INSERT INTO test_alt_table_default (h, j) VALUES (0, 0); Affected Rows: 1 INSERT INTO test_alt_table_default (h, i, j) VALUES (1, 0.1, 0); Affected Rows: 1 SELECT * FROM test_alt_table_default ORDER BY h; +---+-----+---------------------+ | h | i | j | +---+-----+---------------------+ | 0 | 0.0 | 1970-01-01T00:00:00 | | 1 | 0.1 | 1970-01-01T00:00:00 | +---+-----+---------------------+ ALTER TABLE test_alt_table_default MODIFY COLUMN i BOOLEAN; Affected Rows: 0 INSERT INTO test_alt_table_default (h, j) VALUES (2, 0); Affected Rows: 1 INSERT INTO test_alt_table_default (h, i, j) VALUES (3, TRUE, 0); Affected Rows: 1 SELECT * FROM test_alt_table_default ORDER BY h; +---+-------+---------------------+ | h | i | j | +---+-------+---------------------+ | 0 | false | 1970-01-01T00:00:00 | | 1 | true | 1970-01-01T00:00:00 | | 2 | false | 1970-01-01T00:00:00 | | 3 | true | 1970-01-01T00:00:00 | +---+-------+---------------------+ ALTER TABLE test_alt_table_default MODIFY COLUMN i INTEGER; Affected Rows: 0 DESC TABLE test_alt_table_default; +--------+----------------------+-----+------+---------+---------------+ | Column | Type | Key | Null | Default | Semantic Type | +--------+----------------------+-----+------+---------+---------------+ | h | Int32 | PRI | YES | | TAG | | i | Int32 | | YES | 0 | FIELD | | j | TimestampMillisecond | PRI | NO | | TIMESTAMP | +--------+----------------------+-----+------+---------+---------------+ INSERT INTO test_alt_table_default (h, j) VALUES (4, 0); Affected Rows: 1 INSERT INTO test_alt_table_default (h, i, j) VALUES (5, 42, 0); Affected Rows: 1 SELECT * FROM test_alt_table_default ORDER BY h; +---+----+---------------------+ | h | i | j | +---+----+---------------------+ | 0 | 0 | 1970-01-01T00:00:00 | | 1 | 0 | 1970-01-01T00:00:00 | | 2 | 0 | 1970-01-01T00:00:00 | | 3 | 1 | 1970-01-01T00:00:00 | | 4 | 0 | 1970-01-01T00:00:00 | | 5 | 42 | 1970-01-01T00:00:00 | +---+----+---------------------+ ALTER TABLE test_alt_table_default MODIFY COLUMN i STRING; Affected Rows: 0 INSERT INTO test_alt_table_default (h, j) VALUES (6, 0); Affected Rows: 1 INSERT INTO test_alt_table_default (h, i, j) VALUES (7, "word" ,1); Affected Rows: 1 SELECT * FROM test_alt_table_default ORDER BY h; +---+-------+-------------------------+ | h | i | j | +---+-------+-------------------------+ | 0 | 0.0 | 1970-01-01T00:00:00 | | 1 | 0.1 | 1970-01-01T00:00:00 | | 2 | false | 1970-01-01T00:00:00 | | 3 | true | 1970-01-01T00:00:00 | | 4 | 0 | 1970-01-01T00:00:00 | | 5 | 42 | 1970-01-01T00:00:00 | | 6 | 0 | 1970-01-01T00:00:00 | | 7 | word | 1970-01-01T00:00:00.001 | +---+-------+-------------------------+ DROP TABLE test_alt_table_default; Affected Rows: 0 -- test with non-zero default value CREATE TABLE test_alt_table_default_nz(h INTEGER, i Float64 DEFAULT 0.1, j TIMESTAMP TIME INDEX, PRIMARY KEY (h)); Affected Rows: 0 INSERT INTO test_alt_table_default_nz (h, j) VALUES (0, 0); Affected Rows: 1 INSERT INTO test_alt_table_default_nz (h, i, j) VALUES (1, 0.0, 0); Affected Rows: 1 ADMIN FLUSH_TABLE('test_alt_table_default_nz'); +------------------------------------------------+ | ADMIN FLUSH_TABLE('test_alt_table_default_nz') | +------------------------------------------------+ | 0 | +------------------------------------------------+ SELECT * FROM test_alt_table_default_nz ORDER BY h; +---+-----+---------------------+ | h | i | j | +---+-----+---------------------+ | 0 | 0.1 | 1970-01-01T00:00:00 | | 1 | 0.0 | 1970-01-01T00:00:00 | +---+-----+---------------------+ ALTER TABLE test_alt_table_default_nz MODIFY COLUMN i BOOLEAN; Affected Rows: 0 INSERT INTO test_alt_table_default_nz (h, j) VALUES (2, 0); Affected Rows: 1 INSERT INTO test_alt_table_default_nz (h, i, j) VALUES (3, FALSE, 0); Affected Rows: 1 SELECT * FROM test_alt_table_default_nz ORDER BY h; +---+-------+---------------------+ | h | i | j | +---+-------+---------------------+ | 0 | true | 1970-01-01T00:00:00 | | 1 | false | 1970-01-01T00:00:00 | | 2 | true | 1970-01-01T00:00:00 | | 3 | false | 1970-01-01T00:00:00 | +---+-------+---------------------+ ALTER TABLE test_alt_table_default_nz MODIFY COLUMN i INTEGER; Affected Rows: 0 DESC TABLE test_alt_table_default_nz; +--------+----------------------+-----+------+---------+---------------+ | Column | Type | Key | Null | Default | Semantic Type | +--------+----------------------+-----+------+---------+---------------+ | h | Int32 | PRI | YES | | TAG | | i | Int32 | | YES | 1 | FIELD | | j | TimestampMillisecond | PRI | NO | | TIMESTAMP | +--------+----------------------+-----+------+---------+---------------+ INSERT INTO test_alt_table_default_nz (h, j) VALUES (4, 0); Affected Rows: 1 INSERT INTO test_alt_table_default_nz (h, i, j) VALUES (5, 42, 0); Affected Rows: 1 SELECT * FROM test_alt_table_default_nz ORDER BY h; +---+----+---------------------+ | h | i | j | +---+----+---------------------+ | 0 | 0 | 1970-01-01T00:00:00 | | 1 | 0 | 1970-01-01T00:00:00 | | 2 | 1 | 1970-01-01T00:00:00 | | 3 | 0 | 1970-01-01T00:00:00 | | 4 | 1 | 1970-01-01T00:00:00 | | 5 | 42 | 1970-01-01T00:00:00 | +---+----+---------------------+ ALTER TABLE test_alt_table_default_nz MODIFY COLUMN i STRING; Affected Rows: 0 INSERT INTO test_alt_table_default_nz (h, j) VALUES (6, 0); Affected Rows: 1 INSERT INTO test_alt_table_default_nz (h, i, j) VALUES (7, "word" ,1); Affected Rows: 1 SELECT * FROM test_alt_table_default_nz ORDER BY h; +---+-------+-------------------------+ | h | i | j | +---+-------+-------------------------+ | 0 | 0.1 | 1970-01-01T00:00:00 | | 1 | 0.0 | 1970-01-01T00:00:00 | | 2 | true | 1970-01-01T00:00:00 | | 3 | false | 1970-01-01T00:00:00 | | 4 | 1 | 1970-01-01T00:00:00 | | 5 | 42 | 1970-01-01T00:00:00 | | 6 | 1 | 1970-01-01T00:00:00 | | 7 | word | 1970-01-01T00:00:00.001 | +---+-------+-------------------------+ DROP TABLE test_alt_table_default_nz; Affected Rows: 0 -- test alter table type will cause wired behavior due to underlying column data is unchanged CREATE TABLE test_alt_table_col_ty(h INTEGER, i Float64 DEFAULT 0.1, j TIMESTAMP TIME INDEX, PRIMARY KEY (h)); Affected Rows: 0 INSERT INTO test_alt_table_col_ty (h, j) VALUES (0, 0); Affected Rows: 1 INSERT INTO test_alt_table_col_ty (h, i, j) VALUES (1, 0.2, 0); Affected Rows: 1 SELECT * FROM test_alt_table_col_ty ORDER BY h; +---+-----+---------------------+ | h | i | j | +---+-----+---------------------+ | 0 | 0.1 | 1970-01-01T00:00:00 | | 1 | 0.2 | 1970-01-01T00:00:00 | +---+-----+---------------------+ ALTER TABLE test_alt_table_col_ty MODIFY COLUMN i BOOLEAN; Affected Rows: 0 INSERT INTO test_alt_table_col_ty (h, j) VALUES (2, 0); Affected Rows: 1 INSERT INTO test_alt_table_col_ty (h, i, j) VALUES (3, TRUE, 0); Affected Rows: 1 SELECT * FROM test_alt_table_col_ty ORDER BY h; +---+------+---------------------+ | h | i | j | +---+------+---------------------+ | 0 | true | 1970-01-01T00:00:00 | | 1 | true | 1970-01-01T00:00:00 | | 2 | true | 1970-01-01T00:00:00 | | 3 | true | 1970-01-01T00:00:00 | +---+------+---------------------+ ALTER TABLE test_alt_table_col_ty MODIFY COLUMN i INTEGER; Affected Rows: 0 INSERT INTO test_alt_table_col_ty (h, j) VALUES (4, 0); Affected Rows: 1 INSERT INTO test_alt_table_col_ty (h, i, j) VALUES (5, 42, 0); Affected Rows: 1 SELECT * FROM test_alt_table_col_ty ORDER BY h; +---+----+---------------------+ | h | i | j | +---+----+---------------------+ | 0 | 0 | 1970-01-01T00:00:00 | | 1 | 0 | 1970-01-01T00:00:00 | | 2 | 1 | 1970-01-01T00:00:00 | | 3 | 1 | 1970-01-01T00:00:00 | | 4 | 1 | 1970-01-01T00:00:00 | | 5 | 42 | 1970-01-01T00:00:00 | +---+----+---------------------+ ALTER TABLE test_alt_table_col_ty MODIFY COLUMN i STRING; Affected Rows: 0 INSERT INTO test_alt_table_col_ty (h, j) VALUES (6, 0); Affected Rows: 1 INSERT INTO test_alt_table_col_ty (h, i, j) VALUES (7, "how many roads must a man walk down before they call him a man", 0); Affected Rows: 1 -- here see 0.1 is converted to "0.1" since underlying column data is unchanged SELECT * FROM test_alt_table_col_ty ORDER BY h; +---+----------------------------------------------------------------+---------------------+ | h | i | j | +---+----------------------------------------------------------------+---------------------+ | 0 | 0.1 | 1970-01-01T00:00:00 | | 1 | 0.2 | 1970-01-01T00:00:00 | | 2 | true | 1970-01-01T00:00:00 | | 3 | true | 1970-01-01T00:00:00 | | 4 | 1 | 1970-01-01T00:00:00 | | 5 | 42 | 1970-01-01T00:00:00 | | 6 | 1 | 1970-01-01T00:00:00 | | 7 | how many roads must a man walk down before they call him a man | 1970-01-01T00:00:00 | +---+----------------------------------------------------------------+---------------------+ DROP TABLE test_alt_table_col_ty; 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 CREATE TABLE grpc_latencies ( ts TIMESTAMP TIME INDEX, host STRING, method_name STRING, latency DOUBLE, PRIMARY KEY (host, method_name) ) with('append_mode'='true'); Affected Rows: 0 INSERT INTO grpc_latencies (ts, host, method_name, latency) VALUES ('2024-07-11 20:00:06', 'host1', 'GetUser', 103.0); Affected Rows: 1 SELECT * FROM grpc_latencies; +---------------------+-------+-------------+---------+ | ts | host | method_name | latency | +---------------------+-------+-------------+---------+ | 2024-07-11T20:00:06 | host1 | GetUser | 103.0 | +---------------------+-------+-------------+---------+ ALTER TABLE grpc_latencies SET ttl = '10000d'; Affected Rows: 0 ALTER TABLE grpc_latencies ADD COLUMN home INTEGER FIRST; Affected Rows: 0 SELECT * FROM grpc_latencies; +------+---------------------+-------+-------------+---------+ | home | ts | host | method_name | latency | +------+---------------------+-------+-------------+---------+ | | 2024-07-11T20:00:06 | host1 | GetUser | 103.0 | +------+---------------------+-------+-------------+---------+ DROP TABLE grpc_latencies; Affected Rows: 0