mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-05-15 20:40:39 +00:00
42ad842434
* feat: support alter append_mode to true Signed-off-by: evenyag <realevenyag@gmail.com> * test: add sqlness test Signed-off-by: evenyag <realevenyag@gmail.com> * chore: remove comment Signed-off-by: evenyag <realevenyag@gmail.com> * chore: fix compiler errors Signed-off-by: evenyag <realevenyag@gmail.com> * fix: clear merge mode in mito when setting append mode Signed-off-by: evenyag <realevenyag@gmail.com> * fix: sanitize open request and options with both append/merge mode Signed-off-by: evenyag <realevenyag@gmail.com> * feat: clear merge mode when append mode is true Signed-off-by: evenyag <realevenyag@gmail.com> --------- Signed-off-by: evenyag <realevenyag@gmail.com>
193 lines
8.5 KiB
Plaintext
193 lines
8.5 KiB
Plaintext
-- Test altering append_mode from false to true
|
|
-- Create a table with append_mode=false (default)
|
|
CREATE TABLE test_alter_append_mode(
|
|
host STRING,
|
|
ts TIMESTAMP TIME INDEX,
|
|
cpu DOUBLE,
|
|
PRIMARY KEY(host)
|
|
) ENGINE=mito;
|
|
|
|
Affected Rows: 0
|
|
|
|
-- Insert some data
|
|
INSERT INTO test_alter_append_mode VALUES ('host1', 0, 1.0), ('host2', 1, 2.0);
|
|
|
|
Affected Rows: 2
|
|
|
|
-- Insert duplicate data (should be deduplicated since append_mode=false)
|
|
INSERT INTO test_alter_append_mode VALUES ('host1', 0, 10.0), ('host2', 1, 20.0);
|
|
|
|
Affected Rows: 2
|
|
|
|
-- Query should show deduplicated data (latest values)
|
|
SELECT * FROM test_alter_append_mode ORDER BY host, ts;
|
|
|
|
+-------+-------------------------+------+
|
|
| host | ts | cpu |
|
|
+-------+-------------------------+------+
|
|
| host1 | 1970-01-01T00:00:00 | 10.0 |
|
|
| host2 | 1970-01-01T00:00:00.001 | 20.0 |
|
|
+-------+-------------------------+------+
|
|
|
|
-- Alter append_mode from false to true
|
|
ALTER TABLE test_alter_append_mode SET 'append_mode' = 'true';
|
|
|
|
Affected Rows: 0
|
|
|
|
-- Verify append_mode is set via SHOW CREATE TABLE
|
|
SHOW CREATE TABLE test_alter_append_mode;
|
|
|
|
+------------------------+-------------------------------------------------------+
|
|
| Table | Create Table |
|
|
+------------------------+-------------------------------------------------------+
|
|
| test_alter_append_mode | CREATE TABLE IF NOT EXISTS "test_alter_append_mode" ( |
|
|
| | "host" STRING NULL, |
|
|
| | "ts" TIMESTAMP(3) NOT NULL, |
|
|
| | "cpu" DOUBLE NULL, |
|
|
| | TIME INDEX ("ts"), |
|
|
| | PRIMARY KEY ("host") |
|
|
| | ) |
|
|
| | |
|
|
| | ENGINE=mito |
|
|
| | WITH( |
|
|
| | append_mode = 'true' |
|
|
| | ) |
|
|
+------------------------+-------------------------------------------------------+
|
|
|
|
-- Insert duplicate data again (should be preserved since append_mode=true now)
|
|
INSERT INTO test_alter_append_mode VALUES ('host1', 0, 100.0), ('host2', 1, 200.0);
|
|
|
|
Affected Rows: 2
|
|
|
|
-- Query should show the new duplicates preserved
|
|
SELECT * FROM test_alter_append_mode ORDER BY host, ts, cpu;
|
|
|
|
+-------+-------------------------+-------+
|
|
| host | ts | cpu |
|
|
+-------+-------------------------+-------+
|
|
| host1 | 1970-01-01T00:00:00 | 10.0 |
|
|
| host1 | 1970-01-01T00:00:00 | 100.0 |
|
|
| host2 | 1970-01-01T00:00:00.001 | 20.0 |
|
|
| host2 | 1970-01-01T00:00:00.001 | 200.0 |
|
|
+-------+-------------------------+-------+
|
|
|
|
-- Try to alter append_mode from true to false (should fail)
|
|
-- SQLNESS REPLACE \d+\(\d+,\s+\d+\) REDACTED
|
|
ALTER TABLE test_alter_append_mode SET 'append_mode' = 'false';
|
|
|
|
Error: 1004(InvalidArguments), Invalid region request, region_id: REDACTED, err: Only allow changing append_mode from false to true
|
|
|
|
-- Clean up
|
|
DROP TABLE test_alter_append_mode;
|
|
|
|
Affected Rows: 0
|
|
|
|
-- Test creating with append_mode=true and trying to alter to false
|
|
CREATE TABLE test_append_mode_true(
|
|
host STRING,
|
|
ts TIMESTAMP TIME INDEX,
|
|
cpu DOUBLE,
|
|
PRIMARY KEY(host)
|
|
) ENGINE=mito WITH('append_mode'='true');
|
|
|
|
Affected Rows: 0
|
|
|
|
-- Try to alter append_mode from true to false (should fail)
|
|
-- SQLNESS REPLACE \d+\(\d+,\s+\d+\) REDACTED
|
|
ALTER TABLE test_append_mode_true SET 'append_mode' = 'false';
|
|
|
|
Error: 1004(InvalidArguments), Invalid region request, region_id: REDACTED, err: Only allow changing append_mode from false to true
|
|
|
|
-- Altering to the same value should succeed
|
|
ALTER TABLE test_append_mode_true SET 'append_mode' = 'true';
|
|
|
|
Affected Rows: 0
|
|
|
|
-- Clean up
|
|
DROP TABLE test_append_mode_true;
|
|
|
|
Affected Rows: 0
|
|
|
|
-- Test altering append_mode on a table with merge_mode set
|
|
CREATE TABLE test_alter_append_with_merge(
|
|
host STRING,
|
|
ts TIMESTAMP TIME INDEX,
|
|
cpu DOUBLE,
|
|
PRIMARY KEY(host)
|
|
) ENGINE=mito WITH('merge_mode'='last_non_null');
|
|
|
|
Affected Rows: 0
|
|
|
|
-- Verify merge_mode is set
|
|
SHOW CREATE TABLE test_alter_append_with_merge;
|
|
|
|
+------------------------------+-------------------------------------------------------------+
|
|
| Table | Create Table |
|
|
+------------------------------+-------------------------------------------------------------+
|
|
| test_alter_append_with_merge | CREATE TABLE IF NOT EXISTS "test_alter_append_with_merge" ( |
|
|
| | "host" STRING NULL, |
|
|
| | "ts" TIMESTAMP(3) NOT NULL, |
|
|
| | "cpu" DOUBLE NULL, |
|
|
| | TIME INDEX ("ts"), |
|
|
| | PRIMARY KEY ("host") |
|
|
| | ) |
|
|
| | |
|
|
| | ENGINE=mito |
|
|
| | WITH( |
|
|
| | merge_mode = 'last_non_null' |
|
|
| | ) |
|
|
+------------------------------+-------------------------------------------------------------+
|
|
|
|
-- Insert some data
|
|
INSERT INTO test_alter_append_with_merge VALUES ('host1', 0, 1.0), ('host2', 1, 2.0);
|
|
|
|
Affected Rows: 2
|
|
|
|
-- Alter append_mode to true (should succeed and clear merge_mode)
|
|
ALTER TABLE test_alter_append_with_merge SET 'append_mode' = 'true';
|
|
|
|
Affected Rows: 0
|
|
|
|
-- Verify merge_mode is cleared and append_mode is set
|
|
SHOW CREATE TABLE test_alter_append_with_merge;
|
|
|
|
+------------------------------+-------------------------------------------------------------+
|
|
| Table | Create Table |
|
|
+------------------------------+-------------------------------------------------------------+
|
|
| test_alter_append_with_merge | CREATE TABLE IF NOT EXISTS "test_alter_append_with_merge" ( |
|
|
| | "host" STRING NULL, |
|
|
| | "ts" TIMESTAMP(3) NOT NULL, |
|
|
| | "cpu" DOUBLE NULL, |
|
|
| | TIME INDEX ("ts"), |
|
|
| | PRIMARY KEY ("host") |
|
|
| | ) |
|
|
| | |
|
|
| | ENGINE=mito |
|
|
| | WITH( |
|
|
| | append_mode = 'true' |
|
|
| | ) |
|
|
+------------------------------+-------------------------------------------------------------+
|
|
|
|
-- Insert duplicate data (should be preserved since append_mode=true now)
|
|
INSERT INTO test_alter_append_with_merge VALUES ('host1', 0, 100.0), ('host2', 1, 200.0);
|
|
|
|
Affected Rows: 2
|
|
|
|
-- Query should show the new duplicates preserved
|
|
SELECT * FROM test_alter_append_with_merge ORDER BY host, ts, cpu;
|
|
|
|
+-------+-------------------------+-------+
|
|
| host | ts | cpu |
|
|
+-------+-------------------------+-------+
|
|
| host1 | 1970-01-01T00:00:00 | 1.0 |
|
|
| host1 | 1970-01-01T00:00:00 | 100.0 |
|
|
| host2 | 1970-01-01T00:00:00.001 | 2.0 |
|
|
| host2 | 1970-01-01T00:00:00.001 | 200.0 |
|
|
+-------+-------------------------+-------+
|
|
|
|
-- Clean up
|
|
DROP TABLE test_alter_append_with_merge;
|
|
|
|
Affected Rows: 0
|
|
|