github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/interleaved (about) 1 # LogicTest: !3node-tenant 2 # Grandparent table 3 statement ok 4 CREATE TABLE p2 (i INT PRIMARY KEY, s STRING) 5 6 # Two tables interleaved at the same level 7 statement ok 8 CREATE TABLE p1_0 ( 9 i INT, 10 s1 STRING, 11 s2 STRING, 12 d DECIMAL, 13 PRIMARY KEY (i, s1), 14 FAMILY (i, s1, s2), 15 FAMILY (d) 16 ) INTERLEAVE IN PARENT p2 (i) 17 18 statement ok 19 CREATE TABLE p1_1 ( 20 i INT PRIMARY KEY, 21 s1 STRING, 22 s2 STRING, 23 d DECIMAL 24 ) INTERLEAVE IN PARENT p2 (i) 25 26 # Two level deep interleave 27 statement ok 28 CREATE TABLE p0 ( 29 i INT, 30 s1 STRING, 31 s2 STRING, 32 d DECIMAL, 33 PRIMARY KEY (i, s1, s2) 34 ) INTERLEAVE IN PARENT p1_0 (i, s1) 35 36 statement ok 37 INSERT INTO p2 VALUES (2, '2'), (3, '3'), (5, '5'), (7, '7') 38 39 statement ok 40 INSERT INTO p1_0 VALUES (2, '2', '2.01', 2), (3, '3', '3.01', 3), (5, '5', NULL, NULL) 41 42 statement ok 43 INSERT INTO p1_1 VALUES (2, '2', '2.11', 2), (3, '3', '3.11', 3) 44 45 statement ok 46 INSERT INTO p0 VALUES (2, '2', '2.0', 2), (3, '3', '3.0', 3), (5, '5', '5.0', 5) 47 48 query IT rowsort 49 SELECT * FROM p2 50 ---- 51 2 2 52 3 3 53 5 5 54 7 7 55 56 query ITTT rowsort 57 SELECT * FROM p1_0 58 ---- 59 2 2 2.01 2 60 3 3 3.01 3 61 5 5 NULL NULL 62 63 query ITTT rowsort 64 SELECT * FROM p1_1 65 ---- 66 2 2 2.11 2 67 3 3 3.11 3 68 69 query ITTT rowsort 70 SELECT * FROM p0 71 ---- 72 2 2 2.0 2 73 3 3 3.0 3 74 5 5 5.0 5 75 76 statement ok 77 CREATE INDEX p0i ON p0 (i) INTERLEAVE IN PARENT p1_1 (i) 78 79 statement ok 80 CREATE INDEX p1_id ON p1_1 (i, d) INTERLEAVE IN PARENT p1_1 (i) 81 82 query ITTT rowsort 83 SELECT * FROM p0@p0i WHERE i BETWEEN 2 AND 4 84 ---- 85 2 2 2.0 2 86 3 3 3.0 3 87 88 query ITTT rowsort 89 SELECT * FROM p1_1@p1_id WHERE d BETWEEN 1.5 AND 4.0 90 ---- 91 2 2 2.11 2 92 3 3 3.11 3 93 94 statement ok 95 DELETE FROM p1_0 WHERE i = 3 96 97 statement ok 98 INSERT INTO p1_0 (i, s1) VALUES (5, '5') ON CONFLICT (i, s1) DO UPDATE SET i = 7, s2 = '7.01', d = 7.0 99 100 statement ok 101 DELETE FROM p2 WHERE i = 2 OR i = 7 102 103 query IT rowsort 104 SELECT * FROM p2 105 ---- 106 3 3 107 5 5 108 109 # Check that we're skipping first/last row of a block of interleaved data correctly 110 query ITTT rowsort 111 SELECT * FROM p0 112 ---- 113 2 2 2.0 2 114 3 3 3.0 3 115 5 5 5.0 5 116 117 # check that a column backfill on an interleaved table works well. 118 statement ok 119 ALTER TABLE p0 ADD e INT DEFAULT 7 120 121 query ITTTI rowsort 122 SELECT * FROM p0 123 ---- 124 2 2 2.0 2 7 125 3 3 3.0 3 7 126 5 5 5.0 5 7 127 128 # Check that fast delete is disabled when run on a table with child interleaves 129 statement ok 130 DELETE FROM p2 131 132 query IT rowsort 133 SELECT * FROM p2 134 ---- 135 136 query ITTT rowsort 137 SELECT * FROM p1_0 138 ---- 139 2 2 2.01 2 140 7 5 7.01 7.0 141 142 # check that a column backfill on an interleaved table works well. 143 statement ok 144 ALTER TABLE p1_0 ADD e INT DEFAULT 7 145 146 query ITTTI rowsort 147 SELECT * FROM p1_0 148 ---- 149 2 2 2.01 2 7 150 7 5 7.01 7.0 7 151 152 query ITTTI rowsort 153 SELECT * FROM p0 154 ---- 155 2 2 2.0 2 7 156 3 3 3.0 3 7 157 5 5 5.0 5 7 158 159 statement ok 160 DROP INDEX p0@p0i 161 162 query ITTTI rowsort 163 SELECT * FROM p0 164 ---- 165 2 2 2.0 2 7 166 3 3 3.0 3 7 167 5 5 5.0 5 7 168 169 statement ok 170 DROP TABLE p0 171 172 query ITTTI rowsort 173 SELECT * FROM p1_0 174 ---- 175 2 2 2.01 2 7 176 7 5 7.01 7.0 7 177 178 statement error "p2" is interleaved by table "p1_0" 179 TRUNCATE TABLE p2 180 181 statement ok 182 TRUNCATE TABLE p2 CASCADE 183 184 statement error unimplemented 185 DROP TABLE p2 186 187 statement ok 188 CREATE INDEX p1_s2 ON p1_1 (s2) 189 190 # p1_0 is truncated when p2 is truncated. 191 query ITTTI rowsort 192 SELECT * FROM p1_0 193 ---- 194 195 statement ok 196 DROP TABLE p2 CASCADE 197 198 statement error pgcode 42P01 relation "p0" does not exist 199 SELECT * FROM p0 200 201 # Validation and descriptor bookkeeping 202 203 # TODO(dan): Interleave these two indexes once we support the syntax. 204 statement ok 205 CREATE TABLE all_interleaves ( 206 b INT PRIMARY KEY, 207 c INT, 208 d INT, 209 INDEX (c), 210 UNIQUE INDEX (d), 211 FAMILY "primary" (b, c, d) 212 ) INTERLEAVE IN PARENT p1_1 (b) 213 214 statement ok 215 CREATE INDEX ON all_interleaves (c, d) INTERLEAVE IN PARENT p1_1 (c) 216 217 statement ok 218 CREATE UNIQUE INDEX ON all_interleaves (d, c) INTERLEAVE IN PARENT p1_1 (d) 219 220 query TT 221 SHOW CREATE TABLE all_interleaves 222 ---- 223 all_interleaves CREATE TABLE all_interleaves ( 224 b INT8 NOT NULL, 225 c INT8 NULL, 226 d INT8 NULL, 227 CONSTRAINT "primary" PRIMARY KEY (b ASC), 228 INDEX all_interleaves_c_idx (c ASC), 229 UNIQUE INDEX all_interleaves_d_key (d ASC), 230 INDEX all_interleaves_c_d_idx (c ASC, d ASC) INTERLEAVE IN PARENT p1_1 (c), 231 UNIQUE INDEX all_interleaves_d_c_key (d ASC, c ASC) INTERLEAVE IN PARENT p1_1 (d), 232 FAMILY "primary" (b, c, d) 233 ) INTERLEAVE IN PARENT p1_1 (b) 234 235 statement error pgcode 42P01 relation "missing" does not exist 236 CREATE TABLE err (f FLOAT PRIMARY KEY) INTERLEAVE IN PARENT missing (f) 237 238 # Check that interleaved columns match in length to parent's primary columns. 239 240 statement error pq: declared interleaved columns \(s1, d\) must match the parent's primary index \(i\) 241 CREATE INDEX ON p1_0 (s1, d) INTERLEAVE IN PARENT p1_1 (s1, d) 242 243 # Check that interleaved columns are prefix of index's columns. 244 245 statement error pq: declared interleaved columns \(i, s1\) must be a prefix of the primary key columns being interleaved \(i\) 246 CREATE TABLE err (i INT PRIMARY KEY) INTERLEAVE IN PARENT p1_0 (i, s1) 247 248 statement error pq: declared interleaved columns \(i, s1\) must be a prefix of the index columns being interleaved \(i\) 249 CREATE INDEX ON p1_1 (i) INTERLEAVE IN PARENT p1_0 (i, s1) 250 251 # Check that interleaved columns are referencing a valid prefix of names 252 # of the index's columns. 253 254 statement error pq: declared interleaved columns \(j\) must refer to a prefix of the primary key column names being interleaved \(i, j\) 255 CREATE TABLE err (i INT, j INT, PRIMARY KEY (i, j)) INTERLEAVE IN PARENT p1_1 (j) 256 257 statement error pq: declared interleaved columns \(i\) must refer to a prefix of the index column names being interleaved \(d\) 258 CREATE INDEX ON p1_0 (d) INTERLEAVE IN PARENT p1_1 (i) 259 260 # Check that interleaved columns are of the same type AND direction as parent's 261 # primary columns. 262 263 statement error pq: declared interleaved columns \(f\) must match type and sort direction of the parent's primary index \(i\) 264 CREATE TABLE err (f FLOAT PRIMARY KEY) INTERLEAVE IN PARENT p1_1 (f) 265 266 statement error pq: declared interleaved columns \(d\) must match type and sort direction of the parent's primary index \(i\) 267 CREATE INDEX ON p1_0 (d) INTERLEAVE IN PARENT p1_1 (d) 268 269 statement error pq: declared interleaved columns \(i\) must match type and sort direction of the parent's primary index \(i\) 270 CREATE INDEX ON p1_0 (i DESC) INTERLEAVE IN PARENT p1_1 (i) 271 272 273 statement error unimplemented 274 CREATE TABLE err (i INT PRIMARY KEY, INDEX (i) INTERLEAVE IN PARENT p1_1 (i)) 275 276 statement error unimplemented 277 CREATE TABLE err (i INT PRIMARY KEY, UNIQUE INDEX (i) INTERLEAVE IN PARENT p1_1 (i)) 278 279 statement error unimplemented: unsupported shorthand CASCADE 280 CREATE TABLE err (i INT PRIMARY KEY) INTERLEAVE IN PARENT p1_1 (i) CASCADE 281 282 statement error unimplemented: unsupported shorthand RESTRICT 283 CREATE TABLE err (i INT PRIMARY KEY) INTERLEAVE IN PARENT p1_1 (i) RESTRICT 284 285 # Regression test for #13451 286 287 statement ok 288 CREATE TABLE customers ( 289 id INT PRIMARY KEY, 290 name STRING (50) 291 ) 292 293 statement ok 294 CREATE TABLE orders ( 295 customer INT, 296 id INT, 297 total DECIMAL (20, 5), 298 PRIMARY KEY (customer, id), 299 CONSTRAINT fk_customer FOREIGN KEY (customer) REFERENCES customers 300 ) INTERLEAVE IN PARENT customers (customer) 301 302 statement ok 303 INSERT INTO customers 304 (id, name) VALUES 305 (1, 'Ha-Yun'), 306 (2, 'Emanuela') 307 308 statement ok 309 INSERT INTO orders 310 (customer, id, total) VALUES 311 (1, 1000, 100.00), 312 (2, 1001, 90.00), 313 (1, 1002, 80.00), 314 (2, 1003, 70.00) 315 316 query IIR 317 SELECT * FROM orders WHERE customer = 1 AND id = 1000 318 ---- 319 1 1000 100.00000 320 321 # Check that interleaving can occur across databases 322 statement ok 323 CREATE DATABASE other; CREATE TABLE other.foo(x INT PRIMARY KEY); 324 CREATE TABLE interdb (x INT PRIMARY KEY) INTERLEAVE IN PARENT other.foo (x) 325 326 query TT 327 SHOW CREATE TABLE interdb 328 ---- 329 interdb CREATE TABLE interdb ( 330 x INT8 NOT NULL, 331 CONSTRAINT "primary" PRIMARY KEY (x ASC), 332 FAMILY "primary" (x) 333 ) INTERLEAVE IN PARENT other.public.foo (x) 334 335 statement ok 336 CREATE TABLE t1 (id1 INT PRIMARY KEY, id2 INT, id3 INT); 337 338 statement ok 339 CREATE INDEX c on t1 (id2) 340 STORING (id3) 341 INTERLEAVE in PARENT t1 (id2); 342 343 statement ok 344 DROP INDEX t1@c; 345 346 statement ok 347 DROP TABLE t1 348 349 # Regression test for #20067. 350 351 statement ok 352 CREATE TABLE p20067 ( 353 p_id INT PRIMARY KEY, 354 name STRING NOT NULL 355 ) 356 357 statement ok 358 CREATE TABLE c20067 ( 359 p_id INT, 360 c_id INT, 361 name STRING NOT NULL, 362 PRIMARY KEY (p_id, c_id), 363 CONSTRAINT uq_name UNIQUE(name) 364 ) INTERLEAVE IN PARENT p20067 (p_id) 365 366 statement ok 367 BEGIN; 368 INSERT INTO p20067 VALUES (1, 'John Doe'); 369 INSERT INTO c20067 VALUES (1, 1, 'John Doe Junior'); 370 COMMIT; 371 372 statement error duplicate key value \(name\)=\('John Doe Junior'\) violates unique constraint "uq_name" 373 INSERT INTO c20067 VALUES (2, 1, 'John Doe Junior') 374 375 statement error duplicate key value \(name\)=\('John Doe Junior'\) violates unique constraint "uq_name" 376 BEGIN; INSERT INTO p20067 VALUES (2, 'John Doe'); INSERT INTO c20067 VALUES (2, 1, 'John Doe Junior'); END; 377 378 # End the last transaction. 379 statement ok 380 END 381 382 statement error duplicate key value \(p_id,c_id\)=\(1,1\) violates unique constraint "primary" 383 INSERT INTO c20067 VALUES (1, 1, 'John Doe') 384 385 # Regression test for #26756: ensure that interleaved table joins don't get 386 # planned incorrectly given a merge join ordering caused by a constant value 387 # constraint on a non-interleaved column. 388 389 subtest interleaved_join_on_other_columns 390 statement ok 391 CREATE TABLE users (id INT PRIMARY KEY) 392 393 statement ok 394 CREATE TABLE documents (id INT PRIMARY KEY, user_id INT NOT NULL) INTERLEAVE IN PARENT users (id) 395 396 statement ok 397 INSERT INTO users(id) VALUES(1) 398 399 statement ok 400 INSERT INTO documents(id, user_id) VALUES (0, 1) 401 402 query I 403 SELECT count(*) FROM users JOIN documents ON users.id=documents.user_id WHERE documents.id=0 404 ---- 405 1 406 407 # Regression test for #44158: ensure we can delete many thousands of rows from 408 # interleaved child tables. 409 410 subtest interleaved_delete_many_rows_child_tables 411 statement ok 412 CREATE TABLE big_interleave_grandparent (x INT PRIMARY KEY); 413 414 statement ok 415 CREATE TABLE big_interleave_parent (x INT, y INT, PRIMARY KEY (x, y)) INTERLEAVE IN PARENT big_interleave_grandparent(x); 416 417 statement ok 418 ALTER TABLE big_interleave_parent ADD CONSTRAINT fk FOREIGN KEY (x) REFERENCES big_interleave_grandparent(x) ON DELETE CASCADE; 419 420 statement ok 421 CREATE TABLE big_interleave_child (x INT, y INT, z INT, PRIMARY KEY (x, y, z)) INTERLEAVE IN PARENT big_interleave_parent(x, y); 422 423 statement ok 424 ALTER TABLE big_interleave_child ADD CONSTRAINT fk FOREIGN KEY (x, y) REFERENCES big_interleave_parent(x, y) ON DELETE CASCADE; 425 426 statement ok 427 INSERT INTO big_interleave_grandparent VALUES (1); 428 429 statement ok 430 INSERT INTO big_interleave_parent (SELECT 1, id FROM generate_series(1, 20) AS id); 431 432 statement ok 433 INSERT INTO big_interleave_child (x,y,z) SELECT 1, p.id, q.id FROM (SELECT generate_series(1,17) as id) p, (SELECT generate_series (1,897) as id) q; 434 435 statement ok 436 DELETE FROM big_interleave_grandparent WHERE x = 1; 437 438 query III 439 SELECT 440 (SELECT count(*) FROM big_interleave_grandparent) 441 ,(SELECT count(*) FROM big_interleave_parent) 442 ,(SELECT count(*) FROM big_interleave_child) 443 ; 444 ---- 445 0 0 0