github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/family (about) 1 # LogicTest: !3node-tenant 2 # a is the primary key so b gets optimized into a one column value. The c, d 3 # family has two columns, so it's encoded as a tuple 4 statement ok 5 CREATE TABLE abcd( 6 a INT PRIMARY KEY, 7 b INT, 8 c INT, 9 d INT, 10 FAMILY f1 (a, b), 11 FAMILY (c, d) 12 ) 13 14 query TT 15 SHOW CREATE TABLE abcd 16 ---- 17 abcd CREATE TABLE abcd ( 18 a INT8 NOT NULL, 19 b INT8 NULL, 20 c INT8 NULL, 21 d INT8 NULL, 22 CONSTRAINT "primary" PRIMARY KEY (a ASC), 23 FAMILY f1 (a, b), 24 FAMILY fam_1_c_d (c, d) 25 ) 26 27 statement ok 28 CREATE INDEX d_idx ON abcd(d) 29 30 statement ok 31 INSERT INTO abcd VALUES (1, 2, 3, 4), (5, 6, 7, 8) 32 33 query IIII rowsort 34 SELECT * FROM abcd 35 ---- 36 1 2 3 4 37 5 6 7 8 38 39 # Test point lookup, which triggers an optimization for only scanning one 40 # column family. 41 query I 42 SELECT c FROM abcd WHERE a = 1 43 ---- 44 3 45 46 query I 47 SELECT count(*) FROM abcd 48 ---- 49 2 50 51 query I 52 SELECT count(*) FROM abcd@d_idx 53 ---- 54 2 55 56 statement ok 57 UPDATE abcd SET b = 9, d = 10, c = NULL where c = 7 58 59 query IIII rowsort 60 SELECT * FROM abcd 61 ---- 62 1 2 3 4 63 5 9 NULL 10 64 65 statement ok 66 DELETE FROM abcd where c = 3 67 68 query IIII 69 SELECT * FROM abcd 70 ---- 71 5 9 NULL 10 72 73 statement ok 74 UPSERT INTO abcd VALUES (1, 2, 3, 4), (5, 6, 7, 8) 75 76 query IIII rowsort 77 SELECT * FROM abcd 78 ---- 79 1 2 3 4 80 5 6 7 8 81 82 statement ok 83 UPDATE abcd SET b = NULL, c = NULL, d = NULL WHERE a = 1 84 85 query IIII 86 SELECT * FROM abcd WHERE a = 1 87 ---- 88 1 NULL NULL NULL 89 90 # Test updating a NULL family 91 statement ok 92 INSERT INTO abcd (a) VALUES (2) 93 94 query IIII 95 SELECT * FROM abcd WHERE a = 2 96 ---- 97 2 NULL NULL NULL 98 99 statement ok 100 UPDATE abcd SET d = 5 WHERE a = 2 101 102 query IIII 103 SELECT * FROM abcd WHERE a = 2 104 ---- 105 2 NULL NULL 5 106 107 statement ok 108 DELETE FROM abcd WHERE a = 2 109 110 query IIII 111 SELECT * FROM abcd WHERE a = 2 112 ---- 113 114 statement ok 115 ALTER TABLE abcd ADD e STRING FAMILY f1 116 117 statement ok 118 INSERT INTO abcd VALUES (9, 10, 11, 12, 'foo') 119 120 query IIIIT rowsort 121 SELECT * from abcd WHERE a > 1 122 ---- 123 5 6 7 8 NULL 124 9 10 11 12 foo 125 126 # Check the descriptor bookkeeping 127 statement ok 128 ALTER TABLE abcd ADD COLUMN f DECIMAL 129 130 statement error unknown family \"foo\" 131 ALTER TABLE abcd ADD COLUMN g INT FAMILY foo 132 133 statement ok 134 ALTER TABLE abcd ADD COLUMN g INT CREATE FAMILY 135 136 statement error family "f1" already exists 137 ALTER TABLE abcd ADD COLUMN h INT CREATE FAMILY F1 138 139 statement ok 140 ALTER TABLE abcd ADD COLUMN h INT CREATE FAMILY f_h 141 142 statement ok 143 ALTER TABLE abcd ADD COLUMN i INT CREATE IF NOT EXISTS FAMILY F_H 144 145 statement ok 146 ALTER TABLE abcd ADD COLUMN j INT CREATE IF NOT EXISTS FAMILY f_j 147 148 query TT 149 SHOW CREATE TABLE abcd 150 ---- 151 abcd CREATE TABLE abcd ( 152 a INT8 NOT NULL, 153 b INT8 NULL, 154 c INT8 NULL, 155 d INT8 NULL, 156 e STRING NULL, 157 f DECIMAL NULL, 158 g INT8 NULL, 159 h INT8 NULL, 160 i INT8 NULL, 161 j INT8 NULL, 162 CONSTRAINT "primary" PRIMARY KEY (a ASC), 163 INDEX d_idx (d ASC), 164 FAMILY f1 (a, b, e, f), 165 FAMILY fam_1_c_d (c, d), 166 FAMILY fam_2_g (g), 167 FAMILY f_h (h, i), 168 FAMILY f_j (j) 169 ) 170 171 statement ok 172 ALTER TABLE abcd DROP c, DROP d, DROP e, DROP h, DROP i, DROP j 173 174 query TT 175 SHOW CREATE TABLE abcd 176 ---- 177 abcd CREATE TABLE abcd ( 178 a INT8 NOT NULL, 179 b INT8 NULL, 180 f DECIMAL NULL, 181 g INT8 NULL, 182 CONSTRAINT "primary" PRIMARY KEY (a ASC), 183 FAMILY f1 (a, b, f), 184 FAMILY fam_2_g (g) 185 ) 186 187 statement ok 188 CREATE TABLE f1 ( 189 a INT PRIMARY KEY, b STRING, c STRING, 190 FAMILY "primary" (a, b, c) 191 ) 192 193 query TT 194 SHOW CREATE TABLE f1 195 ---- 196 f1 CREATE TABLE f1 ( 197 a INT8 NOT NULL, 198 b STRING NULL, 199 c STRING NULL, 200 CONSTRAINT "primary" PRIMARY KEY (a ASC), 201 FAMILY "primary" (a, b, c) 202 ) 203 204 statement ok 205 CREATE TABLE assign_at_create (a INT PRIMARY KEY FAMILY pri, b INT FAMILY foo, c INT CREATE FAMILY) 206 207 query TT 208 SHOW CREATE TABLE assign_at_create 209 ---- 210 assign_at_create CREATE TABLE assign_at_create ( 211 a INT8 NOT NULL, 212 b INT8 NULL, 213 c INT8 NULL, 214 CONSTRAINT "primary" PRIMARY KEY (a ASC), 215 FAMILY pri (a), 216 FAMILY foo (b), 217 FAMILY fam_2_c (c) 218 ) 219 220 # Check the the diff-column-id storage 221 statement ok 222 CREATE TABLE unsorted_colids (a INT PRIMARY KEY, b INT NOT NULL, c INT NOT NULL, FAMILY (c, b, a)) 223 224 statement ok 225 INSERT INTO unsorted_colids VALUES (1, 1, 1) 226 227 statement ok 228 UPDATE unsorted_colids SET b = 2, c = 3 WHERE a = 1 229 230 query III 231 SELECT * FROM unsorted_colids 232 ---- 233 1 2 3 234 235 # Check that family bookkeeping correctly tracks column renames 236 statement ok 237 CREATE TABLE rename_col (a INT PRIMARY KEY, b INT, c STRING, FAMILY (a, b), FAMILY (c)) 238 239 statement ok 240 ALTER TABLE rename_col RENAME b TO d 241 242 statement ok 243 ALTER TABLE rename_col RENAME c TO e 244 245 query TT 246 SHOW CREATE TABLE rename_col 247 ---- 248 rename_col CREATE TABLE rename_col ( 249 a INT8 NOT NULL, 250 d INT8 NULL, 251 e STRING NULL, 252 CONSTRAINT "primary" PRIMARY KEY (a ASC), 253 FAMILY fam_0_a_b (a, d), 254 FAMILY fam_1_c (e) 255 ) 256 257 # Regression tests for https://github.com/cockroachdb/cockroach/issues/41007. 258 statement ok 259 CREATE TABLE xyz (x INT PRIMARY KEY, y INT, z INT, FAMILY (x, y), FAMILY (z), INDEX (y)) 260 261 statement ok 262 INSERT INTO xyz VALUES (1, 1, NULL) 263 264 query I 265 SELECT z FROM xyz WHERE y = 1 266 ---- 267 NULL 268 269 statement ok 270 CREATE TABLE y (y INT) 271 272 statement ok 273 INSERT INTO y VALUES (1) 274 275 query I 276 SELECT xyz.z FROM y INNER LOOKUP JOIN xyz ON y.y = xyz.y 277 ---- 278 NULL 279 280 # Tests for NeededColumnFamilyIDs logic. This function is used for point lookups 281 # to determine the minimal set of column families which need to be scanned. 282 subtest needed_column_families 283 284 statement ok 285 CREATE TABLE t1 ( 286 a INT PRIMARY KEY, b INT NOT NULL, c INT, d INT, 287 FAMILY (d), FAMILY (c), FAMILY (b), FAMILY (a) 288 ); 289 INSERT INTO t1 VALUES (10, 20, 30, 40) 290 291 # A point lookup on the primary key column should use family 0 (even if the 292 # column is not in that family) because the column can be decoded from the key. 293 query I 294 SELECT a FROM t1 WHERE a = 10 295 ---- 296 10 297 298 query TT 299 SELECT field, description FROM [EXPLAIN SELECT a FROM t1 WHERE a = 10] WHERE field IN ('table', 'spans') 300 ---- 301 table t1@primary 302 spans /10/0-/10/1 303 304 # A point lookup on a non-nullable column allows us to scan only that column 305 # family. 306 query I 307 SELECT b FROM t1 WHERE a = 10 308 ---- 309 20 310 311 query TT 312 SELECT field, description FROM [EXPLAIN SELECT b FROM t1 WHERE a = 10] WHERE field IN ('table', 'spans') 313 ---- 314 table t1@primary 315 spans /10/2/1-/10/2/2 316 317 # Even if we also select the primary key column, we can still scan the single 318 # column family because that column can be decoded from the key. 319 query II 320 SELECT a, b FROM t1 WHERE a = 10 321 ---- 322 10 20 323 324 query TT 325 SELECT field, description FROM [EXPLAIN SELECT a, b FROM t1 WHERE a = 10] WHERE field IN ('table', 'spans') 326 ---- 327 table t1@primary 328 spans /10/2/1-/10/2/2 329 330 # A point lookup on a nullable column requires also scanning column family 0 as 331 # a sentinel. 332 query I 333 SELECT c FROM t1 WHERE a = 10 334 ---- 335 30 336 337 query TT 338 SELECT field, description FROM [EXPLAIN SELECT c FROM t1 WHERE a = 10] WHERE field IN ('table', 'spans') 339 ---- 340 table t1@primary 341 spans /10/0-/10/1/2 342 343 # A point lookup on two columns in non-adjacent column families results in two 344 # spans. 345 query II 346 SELECT b, d FROM t1 WHERE a = 10 347 ---- 348 20 40 349 350 query TT 351 SELECT field, description FROM [EXPLAIN SELECT b, d FROM t1 WHERE a = 10] WHERE field IN ('table', 'spans') 352 ---- 353 table t1@primary 354 spans /10/0-/10/1 /10/2/1-/10/2/2 355 356 # Unique secondary indexes store non-indexed primary key columns in column 357 # family 0. 358 statement ok 359 CREATE UNIQUE INDEX b_idx ON t1 (b) STORING (c, d) 360 361 query I 362 SELECT a FROM t1 WHERE b = 20 363 ---- 364 10 365 366 query TT 367 SELECT field, description FROM [EXPLAIN SELECT a FROM t1 WHERE b = 20] WHERE field IN ('table', 'spans') 368 ---- 369 table t1@b_idx 370 spans /20/0-/20/1 371 372 # If the primary key column is composite, we do need to scan its column family 373 # to retrieve its value. 374 statement ok 375 CREATE TABLE t2 ( 376 a DECIMAL PRIMARY KEY, b INT, c INT NOT NULL, d INT, 377 FAMILY (d), FAMILY (c), FAMILY (b), FAMILY (a) 378 ); 379 INSERT INTO t2 VALUES (10.00, 20, 30, 40) 380 381 # A point lookup on the primary key column should use its family. 382 query T 383 SELECT a FROM t2 WHERE a = 10 384 ---- 385 10.00 386 387 query TT 388 SELECT field, description FROM [EXPLAIN SELECT a FROM t2 WHERE a = 10] WHERE field IN ('table', 'spans') 389 ---- 390 table t2@primary 391 spans /1E+1/3/1-/1E+1/3/2 392 393 # A point lookup on `a` and `b` should scan both of their families. 394 query TI 395 SELECT a, b FROM t2 WHERE a = 10 396 ---- 397 10.00 20 398 399 query TT 400 SELECT field, description FROM [EXPLAIN SELECT a, b FROM t2 WHERE a = 10] WHERE field IN ('table', 'spans') 401 ---- 402 table t2@primary 403 spans /1E+1/2/1-/1E+1/3/2 404 405 # Secondary indexes always store their composite values in column family 0. 406 statement ok 407 CREATE UNIQUE INDEX a_idx ON t2 (a) STORING (b, c, d) 408 409 # A point lookup on the composite column should use family 0. 410 query TI 411 SELECT a, b FROM t2@a_idx WHERE a = 10 412 ---- 413 10.00 20 414 415 query TT 416 SELECT field, description FROM [EXPLAIN SELECT a FROM t2@a_idx WHERE a = 10] WHERE field IN ('table', 'spans') 417 ---- 418 table t2@a_idx 419 spans /1E+1/0-/1E+1/1 420 421 # A point lookup on `a` and `b` should use column family 0 and b's family. 422 query TI 423 SELECT a, b FROM t2@a_idx WHERE a = 10 424 ---- 425 10.00 20 426 427 query TT 428 SELECT field, description FROM [EXPLAIN SELECT a, b FROM t2@a_idx WHERE a = 10] WHERE field IN ('table', 'spans') 429 ---- 430 table t2@a_idx 431 spans /1E+1/0-/1E+1/1 /1E+1/2/1-/1E+1/2/2 432 433 # ------------------------------------------------------------------------------ 434 # UPSERT/INSERT..ON CONFLICT cases. 435 # ------------------------------------------------------------------------------ 436 437 # No secondary index. 438 statement ok 439 CREATE TABLE fam (x INT PRIMARY KEY, y INT, y2 INT, y3 INT, FAMILY (x), FAMILY (y, y2), FAMILY (y3)) 440 441 statement ok 442 INSERT INTO fam VALUES (1, NULL, NULL, NULL) 443 444 statement ok 445 INSERT INTO fam (x, y) VALUES (1, 1), (2, 2) ON CONFLICT (x) DO UPDATE SET y2=excluded.y, y3=excluded.y 446 447 query IIII rowsort 448 SELECT * from fam 449 ---- 450 1 NULL 1 1 451 2 2 NULL NULL 452 453 # Add secondary index. 454 statement ok 455 CREATE UNIQUE INDEX secondary ON fam (y) 456 457 statement ok 458 INSERT INTO fam (x, y) VALUES (2, NULL), (3, NULL) ON CONFLICT (x) DO UPDATE SET y=NULL, y3=2 459 460 query IIII rowsort 461 SELECT * from fam 462 ---- 463 1 NULL 1 1 464 2 NULL NULL 2 465 3 NULL NULL NULL 466 467 query IIII rowsort 468 SELECT * from fam@secondary 469 ---- 470 1 NULL 1 1 471 2 NULL NULL 2 472 3 NULL NULL NULL 473 474 # Add secondary index with STORING column. 475 statement ok 476 DROP INDEX secondary 477 478 statement ok 479 CREATE UNIQUE INDEX secondary ON fam (y) STORING (y2) 480 481 statement ok 482 UPSERT INTO fam (x, y) VALUES (4, 4), (5, 5) 483 484 statement ok 485 INSERT INTO fam (x, y) VALUES (4, 4), (5, 5) 486 ON CONFLICT (y) DO UPDATE SET y=NULL, y2=excluded.y, y3=excluded.y 487 488 query IIII rowsort 489 SELECT * from fam 490 ---- 491 1 NULL 1 1 492 2 NULL NULL 2 493 3 NULL NULL NULL 494 4 NULL 4 4 495 5 NULL 5 5 496 497 query IIII rowsort 498 SELECT * from fam@secondary 499 ---- 500 1 NULL 1 1 501 2 NULL NULL 2 502 3 NULL NULL NULL 503 4 NULL 4 4 504 5 NULL 5 5 505 506 statement ok 507 DROP TABLE fam