github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/truncate (about) 1 # LogicTest: !3node-tenant 2 statement ok 3 CREATE TABLE kv ( 4 k INT PRIMARY KEY, 5 v INT 6 ) 7 8 statement ok 9 INSERT INTO kv VALUES (1, 2), (3, 4), (5, 6), (7, 8) 10 11 query II rowsort 12 SELECT * FROM kv 13 ---- 14 1 2 15 3 4 16 5 6 17 7 8 18 19 statement ok 20 CREATE VIEW kview AS SELECT k,v FROM kv 21 22 query II rowsort 23 SELECT * FROM kview 24 ---- 25 1 2 26 3 4 27 5 6 28 7 8 29 30 statement error "kview" is not a table 31 TRUNCATE TABLE kview 32 33 query II rowsort 34 SELECT * FROM kview 35 ---- 36 1 2 37 3 4 38 5 6 39 7 8 40 41 statement ok 42 TRUNCATE TABLE kv 43 44 query II 45 SELECT * FROM kv 46 ---- 47 48 query II 49 SELECT * FROM kview 50 ---- 51 52 # The "updating privileges" clause in the SELECT statement is for excluding jobs 53 # run by an unrelated startup migration. 54 # TODO (lucy): Update this if/when we decide to change how these jobs queued by 55 # the startup migration are handled. 56 query T 57 SELECT status FROM [SHOW JOBS] WHERE job_type = 'SCHEMA CHANGE' AND description != 'updating privileges' 58 ---- 59 succeeded 60 succeeded 61 succeeded 62 succeeded 63 64 query T 65 SELECT status FROM [SHOW JOBS] WHERE job_type = 'SCHEMA CHANGE GC' 66 ---- 67 running 68 69 # Ensure that TRUNCATE works with a self referential FK. 70 statement ok 71 CREATE TABLE selfref ( 72 y INT PRIMARY KEY, 73 Z INT REFERENCES selfref (y) 74 ) 75 76 statement ok 77 TRUNCATE table selfref 78 79 statement ok 80 INSERT INTO selfref VALUES (1, NULL); 81 82 statement ok 83 DROP TABLE selfref 84 85 subtest truncate_interleave 86 87 statement ok 88 CREATE TABLE a (a INT PRIMARY KEY) 89 90 statement ok 91 CREATE TABLE b (a INT, b INT, PRIMARY KEY (a, b), UNIQUE INDEX(b)) INTERLEAVE IN PARENT a(a) 92 93 statement error "a" is interleaved by table "b" 94 TRUNCATE a 95 96 statement ok 97 TRUNCATE a CASCADE 98 99 statement ok 100 TRUNCATE b 101 102 statement ok 103 TRUNCATE b CASCADE 104 105 statement ok 106 CREATE TABLE c (c INT PRIMARY KEY, d INT REFERENCES b(b)) 107 108 statement error "b" is referenced by foreign key from table "c" 109 TRUNCATE a, b 110 111 statement ok 112 INSERT INTO b VALUES(1, 2) 113 114 statement ok 115 INSERT INTO c VALUES(1, 2) 116 117 statement ok 118 TRUNCATE a CASCADE 119 120 query II 121 SELECT * FROM c 122 ---- 123 124 statement ok 125 CREATE TABLE d (c INT PRIMARY KEY) INTERLEAVE IN PARENT c(c); 126 127 statement ok 128 TRUNCATE a, b, c, d 129 130 statement error "c" is interleaved by table "d" 131 TRUNCATE a, b, c 132 133 statement error "c" is interleaved by table "d" 134 TRUNCATE a, b, c 135 136 statement ok 137 INSERT INTO b VALUES(1, 2) 138 139 statement ok 140 INSERT INTO c VALUES(1, 2) 141 142 statement ok 143 INSERT INTO d VALUES (1) 144 145 statement ok 146 TRUNCATE a CASCADE 147 148 query I 149 SELECT * FROM d 150 ---- 151 152 subtest truncate_29010 153 154 statement ok 155 CREATE SEQUENCE foo; 156 157 statement ok 158 CREATE TABLE bar ( 159 id INT NOT NULL DEFAULT nextval('foo':::STRING), 160 description STRING NULL, 161 CONSTRAINT "primary" PRIMARY KEY (id ASC), 162 FAMILY "primary" (id, description) 163 ); 164 165 statement ok 166 TRUNCATE bar 167 168 statement ok 169 DROP TABLE bar; 170 171 subtest truncate_30547 172 173 statement ok 174 CREATE TABLE tt AS SELECT 'foo' 175 176 query TTT 177 EXPLAIN TRUNCATE TABLE tt 178 ---- 179 · distributed false 180 · vectorized false 181 truncate · · 182 183 # Verify that EXPLAIN did not cause the truncate to be performed. 184 query T 185 SELECT * FROM tt 186 ---- 187 foo 188 189 # Tests for comments getting moved during truncate. 190 subtest comments 191 192 statement ok 193 CREATE TABLE t ( 194 x INT, 195 y INT, 196 z INT, 197 INDEX i1 (x), 198 INDEX i2 (y), 199 INDEX i3 (z) 200 ); 201 COMMENT ON COLUMN t.x IS '''hi''); DROP TABLE t;'; 202 COMMENT ON COLUMN t.z IS 'comm"en"t2'; 203 COMMENT ON INDEX t@i2 IS 'comm''ent3'; 204 TRUNCATE t 205 206 query TT 207 SELECT column_name, comment FROM [SHOW COLUMNS FROM t WITH COMMENT] ORDER BY column_name 208 ---- 209 rowid NULL 210 x 'hi'); DROP TABLE t; 211 y NULL 212 z comm"en"t2 213 214 215 query TT rowsort 216 SELECT distinct(index_name), comment FROM [SHOW INDEXES FROM t WITH COMMENT] 217 ---- 218 primary NULL 219 i1 NULL 220 i2 comm'ent3 221 i3 NULL 222 223 # Ensure that truncate comment reasignment works when index and column IDs 224 # don't all start from 1. 225 statement ok 226 DROP TABLE t; 227 CREATE TABLE t (x INT, y INT, z INT); 228 ALTER TABLE t DROP COLUMN y; 229 ALTER TABLE t ADD COLUMN y INT; 230 ALTER TABLE t DROP COLUMN y; 231 ALTER TABLE t ADD COLUMN y INT; 232 CREATE INDEX i ON t (x); 233 DROP INDEX t@i; 234 CREATE INDEX i ON t (x); 235 DROP INDEX t@i; 236 CREATE INDEX i ON t (x); 237 COMMENT ON COLUMN t.y IS 'hello1'; 238 COMMENT ON INDEX t@i IS 'hello2' 239 240 query TT rowsort 241 SELECT column_name, comment FROM [SHOW COLUMNS FROM t WITH COMMENT] 242 ---- 243 rowid NULL 244 x NULL 245 y hello1 246 z NULL 247 248 query TT rowsort 249 SELECT distinct(index_name), comment FROM [SHOW INDEXES FROM t WITH COMMENT] 250 ---- 251 primary NULL 252 i hello2