github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/snapshot/snapshot_read.result (about) 1 create database if not exists snapshot_read; 2 use snapshot_read; 3 create table test_snapshot_read (a int); 4 INSERT INTO test_snapshot_read (a) VALUES(1), (2), (3), (4), (5),(6), (7), (8), (9), (10), (11), (12),(13), (14), (15), (16), (17), (18), (19), (20),(21), (22), (23), (24), (25), (26), (27), (28), (29), (30),(31), (32), (33), (34), (35), (36), (37), (38), (39), (40),(41), (42), (43), (44), (45), (46), (47), (48), (49), (50),(51), (52), (53), (54), (55), (56), (57), (58), (59), (60),(61), (62), (63), (64), (65), (66), (67), (68), (69), (70),(71), (72), (73), (74), (75), (76), (77), (78), (79), (80), (81), (82), (83), (84), (85), (86), (87), (88), (89), (90),(91), (92), (93), (94), (95), (96), (97), (98), (99), (100); 5 select count(*) from test_snapshot_read; 6 count(*) 7 100 8 select sleep(1); 9 sleep(1) 10 0 11 create snapshot snapshot_01 for account sys; 12 delete from test_snapshot_read where a <= 50; 13 select count(*) from test_snapshot_read; 14 count(*) 15 50 16 select count(*) from test_snapshot_read {snapshot = 'snapshot_01'}; 17 count(*) 18 100 19 select sleep(1); 20 sleep(1) 21 0 22 create snapshot snapshot_02 for account sys; 23 INSERT INTO test_snapshot_read (a) VALUES(1), (2), (3), (4), (5),(6), (7), (8), (9), (10), (11), (12),(13), (14), (15), (16), (17), (18), (19), (20),(21), (22), (23), (24), (25), (26), (27), (28), (29), (30),(31), (32), (33), (34), (35), (36), (37), (38), (39), (40); 24 select count(*) from test_snapshot_read; 25 count(*) 26 90 27 select count(*) from test_snapshot_read{snapshot = 'snapshot_02'}; 28 count(*) 29 50 30 create table test_snapshot_read2(b int); 31 INSERT INTO test_snapshot_read2 select * from test_snapshot_read{snapshot = 'snapshot_01'} where a <= 30; 32 select count(*) test_snapshot_read2; 33 test_snapshot_read2 34 1 35 select count(*) from mo_catalog.mo_tables where reldatabase = 'snapshot_read'; 36 count(*) 37 2 38 select sleep(1); 39 sleep(1) 40 0 41 create snapshot snapshot_03 for account sys; 42 drop table if exists test_snapshot_read2; 43 select count(*) from mo_catalog.mo_tables where reldatabase = 'snapshot_read'; 44 count(*) 45 1 46 select count(*) from mo_catalog.mo_tables{snapshot = 'snapshot_03'} where reldatabase = 'snapshot_read'; 47 count(*) 48 2 49 drop table if exists test_snapshot_read; 50 drop database if exists snapshot_read; 51 drop snapshot snapshot_01; 52 drop snapshot snapshot_02; 53 drop snapshot snapshot_03; 54 create database if not exists snapshot_read; 55 use snapshot_read; 56 create table test_snapshot_read (a int); 57 INSERT INTO test_snapshot_read (a) VALUES(1), (2), (3), (4), (5),(6), (7), (8), (9), (10), (11), (12),(13), (14), (15), (16), (17), (18), (19), (20),(21), (22), (23), (24), (25), (26), (27), (28), (29), (30),(31), (32), (33), (34), (35), (36), (37), (38), (39), (40),(41), (42), (43), (44), (45), (46), (47), (48), (49), (50),(51), (52), (53), (54), (55), (56), (57), (58), (59), (60),(61), (62), (63), (64), (65), (66), (67), (68), (69), (70),(71), (72), (73), (74), (75), (76), (77), (78), (79), (80), (81), (82), (83), (84), (85), (86), (87), (88), (89), (90),(91), (92), (93), (94), (95), (96), (97), (98), (99), (100); 58 select count(*) from test_snapshot_read; 59 count(*) 60 100 61 create snapshot snapshot_01 for account sys; 62 delete from test_snapshot_read where a <= 50; 63 select count(*) from test_snapshot_read; 64 count(*) 65 50 66 select count(*) from test_snapshot_read {snapshot = 'snapshot_01'}; 67 count(*) 68 100 69 create snapshot snapshot_02 for account sys; 70 INSERT INTO test_snapshot_read (a) VALUES(1), (2), (3), (4), (5),(6), (7), (8), (9), (10), (11), (12),(13), (14), (15), (16), (17), (18), (19), (20),(21), (22), (23), (24), (25), (26), (27), (28), (29), (30),(31), (32), (33), (34), (35), (36), (37), (38), (39), (40); 71 select count(*) from test_snapshot_read; 72 count(*) 73 90 74 select count(*) from test_snapshot_read{snapshot = 'snapshot_02'}; 75 count(*) 76 50 77 create table test_snapshot_read2(b int); 78 INSERT INTO test_snapshot_read2 select * from test_snapshot_read{snapshot = 'snapshot_01'} where a <= 30; 79 select count(*) test_snapshot_read2; 80 test_snapshot_read2 81 1 82 select count(*) from mo_catalog.mo_tables where reldatabase = 'snapshot_read'; 83 count(*) 84 2 85 create snapshot snapshot_03 for account sys; 86 drop table if exists test_snapshot_read2; 87 select count(*) from mo_catalog.mo_tables where reldatabase = 'snapshot_read'; 88 count(*) 89 1 90 select count(*) from mo_catalog.mo_tables{snapshot = 'snapshot_03'} where reldatabase = 'snapshot_read'; 91 count(*) 92 2 93 drop table if exists test_snapshot_read; 94 drop database if exists snapshot_read; 95 drop snapshot snapshot_01; 96 drop snapshot snapshot_02; 97 drop snapshot snapshot_03; 98 drop account if exists test_account; 99 create account test_account admin_name = 'test_user' identified by '111'; 100 create database if not exists snapshot_read; 101 use snapshot_read; 102 create table test_snapshot_read (a int); 103 INSERT INTO test_snapshot_read (a) VALUES(1), (2), (3), (4), (5),(6), (7), (8), (9), (10), (11), (12),(13), (14), (15), (16), (17), (18), (19), (20),(21), (22), (23), (24), (25), (26), (27), (28), (29), (30),(31), (32), (33), (34), (35), (36), (37), (38), (39), (40),(41), (42), (43), (44), (45), (46), (47), (48), (49), (50),(51), (52), (53), (54), (55), (56), (57), (58), (59), (60),(61), (62), (63), (64), (65), (66), (67), (68), (69), (70),(71), (72), (73), (74), (75), (76), (77), (78), (79), (80), (81), (82), (83), (84), (85), (86), (87), (88), (89), (90),(91), (92), (93), (94), (95), (96), (97), (98), (99), (100); 104 select count(*) from test_snapshot_read; 105 count(*) 106 100 107 create snapshot snapshot_01 for account test_account; 108 delete from test_snapshot_read where a <= 50; 109 select count(*) from test_snapshot_read; 110 count(*) 111 50 112 select count(*) from test_snapshot_read {snapshot = 'snapshot_01'}; 113 count(*) 114 100 115 create snapshot snapshot_02 for account test_account; 116 INSERT INTO test_snapshot_read (a) VALUES(1), (2), (3), (4), (5),(6), (7), (8), (9), (10), (11), (12),(13), (14), (15), (16), (17), (18), (19), (20),(21), (22), (23), (24), (25), (26), (27), (28), (29), (30),(31), (32), (33), (34), (35), (36), (37), (38), (39), (40); 117 select count(*) from test_snapshot_read; 118 count(*) 119 90 120 select count(*) from test_snapshot_read{snapshot = 'snapshot_02'}; 121 count(*) 122 50 123 create table test_snapshot_read2(b int); 124 INSERT INTO test_snapshot_read2 select * from test_snapshot_read{snapshot = 'snapshot_01'} where a <= 30; 125 select count(*) test_snapshot_read2; 126 test_snapshot_read2 127 1 128 select count(*) from mo_catalog.mo_tables where reldatabase = 'snapshot_read'; 129 count(*) 130 2 131 create snapshot snapshot_03 for account test_account; 132 drop table if exists test_snapshot_read2; 133 select count(*) from mo_catalog.mo_tables where reldatabase = 'snapshot_read'; 134 count(*) 135 1 136 select count(*) from mo_catalog.mo_tables{snapshot = 'snapshot_03'} where reldatabase = 'snapshot_read'; 137 count(*) 138 2 139 select count(*) from test_snapshot_read{timestamp = '3020-01-01 00:00:00'}; 140 count(*) 141 90 142 drop table if exists test_snapshot_read; 143 drop database if exists snapshot_read; 144 drop snapshot snapshot_01; 145 drop snapshot snapshot_02; 146 drop snapshot snapshot_03; 147 drop account if exists test_account; 148 create database if not exists snapshot_read; 149 use snapshot_read; 150 CREATE TABLE users ( 151 id INT AUTO_INCREMENT PRIMARY KEY, 152 username VARCHAR(255) NOT NULL, 153 email VARCHAR(255) NOT NULL UNIQUE, 154 password VARCHAR(255) NOT NULL, 155 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 156 ); 157 INSERT INTO users (username, email, password) VALUES ('john_doe', 'john@example.com', 'securepassword123'); 158 INSERT INTO users (username, email, password) VALUES ('jane_smith', 'jane.smith@example.com', 'password123'),('alice_jones', 'alice.jones@gmail.com', 'ilovecats'),('bob_brown', 'bob.brown@yahoo.com', 'mysecretpassword'),('charlie_lee', 'charlie.lee@protonmail.ch', 'secure123'),('diana_wilson', 'diana.wilson@outlook.com', 'D1anaPass'); 159 INSERT INTO users (username, email, password) VALUES ('emily_adams', 'emily.adams@icloud.com', 'Em1Ly123'), ('francis_nguyen', 'francis.nguyen@domain.com', 'fNguyenPass'), ('grace_parker', 'grace.parker@server.com', 'G1race123'), ('henry_miller', 'henry.miller@company.org', 'hMillerSecret'), ('isabella_grant', 'isabella.grant@university.edu', 'iGrantPass'); 160 select id, username, email from users where email = 'john@example.com'; 161 id username email 162 1 john_doe john@example.com 163 select id, username, email from users where email = 'alice.jones@gmail.com'; 164 id username email 165 3 alice_jones alice.jones@gmail.com 166 create snapshot sp_01 for account sys; 167 DELETE FROM users where email = 'john@example.com'; 168 UPDATE users SET password = 'newsecurepassword123' WHERE email = 'alice.jones@gmail.com'; 169 select id, username, email from users where email = 'john@example.com'; 170 id username email 171 select id, username, email from users where email = 'alice.jones@gmail.com'; 172 id username email 173 3 alice_jones alice.jones@gmail.com 174 select id, username, email from users{snapshot = 'sp_01'} where email = 'john@example.com'; 175 id username email 176 1 john_doe john@example.com 177 select id, username, email from users {snapshot = 'sp_01'} where email = 'alice.jones@gmail.com'; 178 id username email 179 3 alice_jones alice.jones@gmail.com 180 CREATE TABLE new_users ( 181 id INT AUTO_INCREMENT PRIMARY KEY, 182 username VARCHAR(255) NOT NULL, 183 email VARCHAR(255) NOT NULL UNIQUE, 184 password VARCHAR(255) NOT NULL, 185 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 186 ); 187 insert into new_users select * from users{snapshot = 'sp_01'} where email = 'john@example.com'; 188 insert into new_users select * from users {snapshot = 'sp_01'} where email = 'alice.jones@gmail.com'; 189 select id, username, email from new_users; 190 id username email 191 1 john_doe john@example.com 192 3 alice_jones alice.jones@gmail.com 193 drop snapshot sp_01; 194 drop database if exists snapshot_read; 195 drop account if exists test_account; 196 create account test_account admin_name = 'test_user' identified by '111'; 197 create database if not exists snapshot_read; 198 use snapshot_read; 199 CREATE TABLE users ( 200 id INT AUTO_INCREMENT PRIMARY KEY, 201 username VARCHAR(255) NOT NULL, 202 email VARCHAR(255) NOT NULL UNIQUE, 203 password VARCHAR(255) NOT NULL, 204 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 205 ); 206 INSERT INTO users (username, email, password) VALUES ('john_doe', 'john@example.com', 'securepassword123'); 207 INSERT INTO users (username, email, password) VALUES ('jane_smith', 'jane.smith@example.com', 'password123'),('alice_jones', 'alice.jones@gmail.com', 'ilovecats'),('bob_brown', 'bob.brown@yahoo.com', 'mysecretpassword'),('charlie_lee', 'charlie.lee@protonmail.ch', 'secure123'),('diana_wilson', 'diana.wilson@outlook.com', 'D1anaPass'); 208 INSERT INTO users (username, email, password) VALUES ('emily_adams', 'emily.adams@icloud.com', 'Em1Ly123'), ('francis_nguyen', 'francis.nguyen@domain.com', 'fNguyenPass'), ('grace_parker', 'grace.parker@server.com', 'G1race123'), ('henry_miller', 'henry.miller@company.org', 'hMillerSecret'), ('isabella_grant', 'isabella.grant@university.edu', 'iGrantPass'); 209 select id, username, email from users where email = 'john@example.com'; 210 id username email 211 1 john_doe john@example.com 212 select id, username, email from users where email = 'alice.jones@gmail.com'; 213 id username email 214 3 alice_jones alice.jones@gmail.com 215 create snapshot sp_01 for account test_account; 216 DELETE FROM users where email = 'john@example.com'; 217 UPDATE users SET password = 'newsecurepassword123' WHERE email = 'alice.jones@gmail.com'; 218 select id, username, email from users where email = 'john@example.com'; 219 id username email 220 select id, username, email from users where email = 'alice.jones@gmail.com'; 221 id username email 222 3 alice_jones alice.jones@gmail.com 223 select id, username, email from users{snapshot = 'sp_01'} where email = 'john@example.com'; 224 id username email 225 1 john_doe john@example.com 226 select id, username, email from users {snapshot = 'sp_01'} where email = 'alice.jones@gmail.com'; 227 id username email 228 3 alice_jones alice.jones@gmail.com 229 CREATE TABLE new_users ( 230 id INT AUTO_INCREMENT PRIMARY KEY, 231 username VARCHAR(255) NOT NULL, 232 email VARCHAR(255) NOT NULL UNIQUE, 233 password VARCHAR(255) NOT NULL, 234 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 235 ); 236 insert into new_users select * from users{snapshot = 'sp_01'} where email = 'john@example.com'; 237 insert into new_users select * from users {snapshot = 'sp_01'} where email = 'alice.jones@gmail.com'; 238 select id, username, email from new_users; 239 id username email 240 1 john_doe john@example.com 241 3 alice_jones alice.jones@gmail.com 242 drop snapshot sp_01; 243 drop database if exists snapshot_read; 244 drop account if exists test_account;