github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/secondary_index_column_families (about) 1 # LogicTest: local 2 3 statement ok 4 CREATE TABLE t1 ( 5 x INT PRIMARY KEY, y INT, z INT, a INT, b INT, 6 FAMILY (x), FAMILY (y), FAMILY (z), FAMILY (a, b), 7 INDEX nonuniqueidx (y), UNIQUE INDEX uniqueidx (y), 8 INDEX nonuniqueidxstoring (y) STORING (z, a, b), 9 UNIQUE INDEX uniqueidxstoring (y) STORING (z, a, b) 10 ) 11 12 # Ensure that inserts into each index look like we expect them to. 13 14 # Inserts into nonuniqueidx or uniqueidx (which don't store anything) should 15 # be a single kv pair of the old format (BYTES value with PK cols in 16 # the value, if needed). Inserts into nonuniqueidxstoring and 17 # uniqueidxstoring both should generate 3 K/V pairs. 18 query T kvtrace(InitPut) 19 INSERT INTO t1 VALUES (1, 1, 1, 1, 1) 20 ---- 21 InitPut /Table/53/2/1/1/0 -> /BYTES/ 22 InitPut /Table/53/3/1/0 -> /BYTES/0x89 23 InitPut /Table/53/4/1/1/0 -> /BYTES/ 24 InitPut /Table/53/4/1/1/2/1 -> /TUPLE/3:3:Int/1 25 InitPut /Table/53/4/1/1/3/1 -> /TUPLE/4:4:Int/1/1:5:Int/1 26 InitPut /Table/53/5/1/0 -> /BYTES/0x89 27 InitPut /Table/53/5/1/2/1 -> /TUPLE/3:3:Int/1 28 InitPut /Table/53/5/1/3/1 -> /TUPLE/4:4:Int/1/1:5:Int/1 29 30 31 # Deletes on nonuniqueidx or uniqueidx should result in a deletion 32 # of a single key. Deletes on nonuniqueidxstoring and uniqueidxstoring 33 # should result in 3 K/V pair deletions. 34 query T kvtrace(Del,prefix=/Table/53/2/,prefix=/Table/53/3/,prefix=/Table/53/4/,prefix=/Table/53/5/) 35 DELETE FROM t1 WHERE x = 1 36 ---- 37 Del /Table/53/2/1/1/0 38 Del /Table/53/3/1/0 39 Del /Table/53/4/1/1/0 40 Del /Table/53/4/1/1/2/1 41 Del /Table/53/4/1/1/3/1 42 Del /Table/53/5/1/0 43 Del /Table/53/5/1/2/1 44 Del /Table/53/5/1/3/1 45 46 # Put some data back into the table. 47 statement ok 48 INSERT INTO t1 VALUES (1, 1, 1, 1, 1) 49 50 # Selects (as of now) should scan all of the K/V pairs for each index. 51 query I 52 SET TRACING=on,kv,results; 53 SELECT y FROM t1@nonuniqueidx; 54 SET TRACING=off 55 ---- 56 1 57 58 query T 59 SELECT message FROM [SHOW KV TRACE FOR SESSION] WHERE 60 message LIKE 'fetched: /t1/nonuniqueidx/%' 61 ---- 62 fetched: /t1/nonuniqueidx/1/1 -> NULL 63 64 query I 65 SET TRACING=on,kv,results; 66 SELECT y FROM t1@uniqueidx; 67 SET TRACING=off 68 ---- 69 1 70 71 query T 72 SELECT message FROM [SHOW KV TRACE FOR SESSION] WHERE 73 message LIKE 'fetched: /t1/uniqueidx/%' 74 ---- 75 fetched: /t1/uniqueidx/1 -> /1 76 77 query IIIII 78 SET TRACING=on,kv,results; 79 SELECT * FROM t1@nonuniqueidxstoring; 80 SET TRACING=off 81 ---- 82 1 1 1 1 1 83 84 query T 85 SELECT message FROM [SHOW KV TRACE FOR SESSION] WHERE 86 message LIKE 'fetched: /t1/nonuniqueidxstoring/%' 87 ---- 88 fetched: /t1/nonuniqueidxstoring/1/1 -> NULL 89 fetched: /t1/nonuniqueidxstoring/1/1/z -> /1 90 fetched: /t1/nonuniqueidxstoring/1/1/a/b -> /1/1 91 92 query IIIII 93 SET TRACING=on,kv,results; 94 SELECT * FROM t1@uniqueidxstoring; 95 SET TRACING=off 96 ---- 97 1 1 1 1 1 98 99 query T 100 SELECT message FROM [SHOW KV TRACE FOR SESSION] WHERE 101 message LIKE 'fetched: /t1/uniqueidxstoring/%' 102 ---- 103 fetched: /t1/uniqueidxstoring/1 -> /1 104 fetched: /t1/uniqueidxstoring/1/z -> /1 105 fetched: /t1/uniqueidxstoring/1/a/b -> /1/1 106 107 108 #Test some specific behavior with nulls on unique indexes. 109 statement ok 110 INSERT INTO t1 VALUES (3, NULL, 3, 3, 3), (4, NULL, 4, 4, 4) 111 112 query IIIII 113 SET TRACING=on,kv,results; 114 SELECT * FROM t1@uniqueidxstoring ORDER BY x; 115 SET TRACING=off 116 ---- 117 1 1 1 1 1 118 3 NULL 3 3 3 119 4 NULL 4 4 4 120 121 query T 122 SELECT message FROM [SHOW KV TRACE FOR SESSION] WHERE 123 message LIKE 'fetched: /t1/uniqueidxstoring/%' 124 ORDER BY message 125 ---- 126 fetched: /t1/uniqueidxstoring/1 -> /1 127 fetched: /t1/uniqueidxstoring/1/a/b -> /1/1 128 fetched: /t1/uniqueidxstoring/1/z -> /1 129 fetched: /t1/uniqueidxstoring/NULL -> /3 130 fetched: /t1/uniqueidxstoring/NULL -> /4 131 fetched: /t1/uniqueidxstoring/NULL/a/b -> /3/3 132 fetched: /t1/uniqueidxstoring/NULL/a/b -> /4/4 133 fetched: /t1/uniqueidxstoring/NULL/z -> /3 134 fetched: /t1/uniqueidxstoring/NULL/z -> /4 135 136 # Ensure that updates delete and insert all K/V pairs for each index. 137 # Note: we don't use kvtrace query type here because it is clearer to 138 # replay the trace multiple times to separate the operations by index. 139 statement ok 140 SET TRACING=on,kv,results; 141 UPDATE t1 SET 142 x = 2, y = 2, z = 2, a = 2, b = 2 143 WHERE x = 1; 144 SET TRACING=off; 145 146 # Updates on nonuniqueidx or uniqueidx (which don't store anything) should be a single kv pair of the old format. 147 query T 148 SELECT message FROM [SHOW KV TRACE FOR SESSION] WHERE 149 message LIKE 'Del /Table/53/2/%' OR 150 message LIKE 'InitPut /Table/53/2/%' OR 151 message LIKE 'Del /Table/53/3/%' OR 152 message LIKE 'InitPut /Table/53/3/%' 153 ORDER BY message 154 ---- 155 Del /Table/53/2/1/1/0 156 Del /Table/53/3/1/0 157 InitPut /Table/53/2/2/2/0 -> /BYTES/ 158 InitPut /Table/53/3/2/0 -> /BYTES/0x8a 159 160 # Updates on nonuniqueidxstoring should generate 3 K/V pairs. 161 query T 162 SELECT message FROM [SHOW KV TRACE FOR SESSION] WHERE 163 message LIKE 'Del /Table/53/4/%' OR 164 message LIKE 'InitPut /Table/53/4/%' 165 ORDER BY message 166 ---- 167 Del /Table/53/4/1/1/0 168 Del /Table/53/4/1/1/2/1 169 Del /Table/53/4/1/1/3/1 170 InitPut /Table/53/4/2/2/0 -> /BYTES/ 171 InitPut /Table/53/4/2/2/2/1 -> /TUPLE/3:3:Int/2 172 InitPut /Table/53/4/2/2/3/1 -> /TUPLE/4:4:Int/2/1:5:Int/2 173 174 # Updates on uniqueidxstoring should generate 3 K/V pairs. 175 query T 176 SELECT message FROM [SHOW KV TRACE FOR SESSION] WHERE 177 message LIKE 'Del /Table/53/5/%' OR 178 message LIKE 'InitPut /Table/53/5/%' 179 ORDER BY message 180 ---- 181 Del /Table/53/5/1/0 182 Del /Table/53/5/1/2/1 183 Del /Table/53/5/1/3/1 184 InitPut /Table/53/5/2/0 -> /BYTES/0x8a 185 InitPut /Table/53/5/2/2/1 -> /TUPLE/3:3:Int/2 186 InitPut /Table/53/5/2/3/1 -> /TUPLE/4:4:Int/2/1:5:Int/2 187 188 # Ensure that reads only scan the necessary k/v's. 189 statement ok 190 DROP TABLE IF EXISTS t; 191 CREATE TABLE t ( 192 x INT, y INT, z INT, 193 FAMILY (x), FAMILY (y), FAMILY (z), 194 UNIQUE INDEX i (x) STORING (y, z), 195 INDEX i2 (x) STORING (y, z) 196 ); 197 INSERT INTO t VALUES (1, 2, 3) 198 199 query I 200 SELECT y FROM t@i WHERE x = 1 201 ---- 202 2 203 204 # In this case, we scan only families 0 and 1. 205 query T kvtrace(Scan,prefix=/Table/54/2/) 206 SELECT y FROM t@i WHERE x = 1 207 ---- 208 Scan /Table/54/2/1/{0-1/2} 209 210 # Make sure that family splitting doesn't affect 211 # lookups when there are null values along the 212 # secondary index. 213 statement ok 214 INSERT INTO t VALUES (NULL, 3, 4) 215 216 query I 217 SELECT y FROM t@i WHERE x IS NULL 218 ---- 219 3 220 221 query T kvtrace(Scan,prefix=/Table/54/2/) 222 SELECT y FROM t@i WHERE x IS NULL 223 ---- 224 Scan /Table/54/2/{NULL-!NULL} 225 226 # Ensure that updates only touch the changed column families. 227 query T kvtrace(CPut,prefix=/Table/54/2/) 228 UPDATE t SET y = 5 WHERE x = 1 229 ---- 230 CPut /Table/54/2/1/1/1 -> /TUPLE/2:2:Int/5 (replacing raw_bytes:"\000\000\000\000\n#\004" timestamp:<> , if exists) 231 232 # Test composite datatypes. 233 statement ok 234 DROP TABLE IF EXISTS t; 235 CREATE TABLE t ( 236 x INT PRIMARY KEY, 237 y DECIMAL, 238 z DECIMAL, 239 w INT, 240 v INT, 241 FAMILY (x, w), FAMILY (y, z), FAMILY (v), 242 UNIQUE INDEX i (y, z) STORING (w, v) 243 ); 244 INSERT INTO t VALUES (1, 2.01, 3.001, 4, 5) 245 246 query TTI 247 SELECT y, z, v FROM t@i WHERE y = 2.01 AND z = 3.001 248 ---- 249 2.01 3.001 5 250 251 # We only need a point scan on family 0, because the composite values 252 # are stored in family 0, and a scan on family 2 for v. 253 query T kvtrace(Scan,prefix=/Table/55/2/) 254 SELECT y, z, v FROM t@i WHERE y = 2.01 AND z = 3.001 255 ---- 256 Scan /Table/55/2/2.01/3.001/{0-1}, /Table/55/2/2.01/3.001/2/{1-2} 257 258 query TTT 259 EXPLAIN SELECT y, z, v FROM t@i WHERE y = 2.01 AND z = 3.001 260 ---- 261 · distributed false 262 · vectorized true 263 scan · · 264 · table t@i 265 · spans /2.01/3.001/0-/2.01/3.001/1 /2.01/3.001/2/1-/2.01/3.001/2/2 266 267 # Ensure that we always have a k/v in family 0. 268 statement ok 269 DROP TABLE IF EXISTS t; 270 CREATE TABLE t ( 271 x INT PRIMARY KEY, 272 y INT, 273 z INT, 274 UNIQUE INDEX i (y) STORING (z), 275 FAMILY (y), FAMILY (x), FAMILY (z) 276 ); 277 INSERT INTO t VALUES (1, 2, 3) 278 279 query I 280 SELECT y FROM t@i WHERE y = 2 281 ---- 282 2 283 284 # Prove that we can scan only column family 0 and find the row. 285 query T kvtrace(Scan,prefix=/Table/56/2/) 286 SELECT y FROM t@i WHERE y = 2 287 ---- 288 Scan /Table/56/2/2/{0-1} 289 290 # Ensure that when backfilling an index we only insert the needed k/vs. 291 statement ok 292 DROP TABLE IF EXISTS t; 293 CREATE TABLE t ( 294 x INT PRIMARY KEY, y INT, z INT, w INT, 295 FAMILY (y), FAMILY (x), FAMILY (z), FAMILY (w) 296 ); 297 INSERT INTO t VALUES (1, 2, NULL, 3), (4, 5, 6, NULL), (8, 9, NULL, NULL); 298 CREATE INDEX i ON t (y) STORING (z, w) 299 300 query IIII rowsort 301 SET TRACING=on,kv,results; 302 SELECT * FROM t@i; 303 SET TRACING=off 304 ---- 305 1 2 NULL 3 306 4 5 6 NULL 307 8 9 NULL NULL 308 309 # Ensure by scanning that we fetch 2 k/v's for row (1, 2, NULL, 3), 310 # 2 k/v's for row (4, 5, 6, NULL), and 1 k/v for row (8, 9, NULL, NULL). 311 # In particular, we shouldn't see: 312 # * a k/v for column z for the row (1, 2, NULL, 3) 313 # * a k/v for column w for the row (4, 5, 6, NULL) 314 # * a k/v for either z or w for the row (8, 9, NULL, NULL) 315 query T 316 SELECT message FROM [SHOW KV TRACE FOR SESSION] WHERE 317 message LIKE 'fetched%' 318 ORDER BY message 319 ---- 320 fetched: /t/i/2/1 -> NULL 321 fetched: /t/i/2/1/w -> /3 322 fetched: /t/i/5/4 -> NULL 323 fetched: /t/i/5/4/z -> /6 324 fetched: /t/i/9/8 -> NULL 325 326 statement ok 327 DROP TABLE IF EXISTS t; 328 CREATE TABLE t ( 329 x INT PRIMARY KEY, y INT, z INT, w INT, 330 FAMILY (y), FAMILY (x), FAMILY (z), FAMILY (w) 331 ); 332 INSERT INTO t VALUES (1, 2, NULL, NULL) 333 334 statement ok 335 BEGIN 336 337 # Place i on the mutations queue in a delete only state. 338 statement ok 339 CREATE INDEX i ON t (y) STORING (z, w) 340 341 # Because i is in a delete only state, we should see a delete 342 # for each k/v for i for the row (1, 2, NULL, NULL). 343 query T kvtrace(Del,prefix=/Table/58/2/) 344 UPDATE t SET z = 3 WHERE y = 2 345 ---- 346 Del /Table/58/2/2/1/0 347 Del /Table/58/2/2/1/2/1 348 Del /Table/58/2/2/1/3/1 349 350 statement ok 351 COMMIT 352 353 query IIII 354 SELECT * FROM t@i 355 ---- 356 1 2 3 NULL 357 358 statement ok 359 DROP TABLE IF EXISTS t; 360 CREATE TABLE t ( 361 x INT PRIMARY KEY, y INT, a INT, b INT, c INT, d INT, e INT, f INT, 362 FAMILY (x), FAMILY (y), FAMILY (a, b), FAMILY (c, d), FAMILY (e), FAMILY (f), 363 INDEX i1 (y) STORING (a, b, c, d, e, f), 364 UNIQUE INDEX i2 (y) STORING (a, b, c, d, e, f) 365 ); 366 367 # Ensure we only insert the correct keys. 368 query T kvtrace(InitPut,prefix=/Table/59/2/,prefix=/Table/59/3/) 369 INSERT INTO t VALUES (1, 2, 3, NULL, 5, 6, NULL, 8) 370 ---- 371 InitPut /Table/59/2/2/1/0 -> /BYTES/ 372 InitPut /Table/59/2/2/1/2/1 -> /TUPLE/3:3:Int/3 373 InitPut /Table/59/2/2/1/3/1 -> /TUPLE/5:5:Int/5/1:6:Int/6 374 InitPut /Table/59/2/2/1/5/1 -> /TUPLE/8:8:Int/8 375 InitPut /Table/59/3/2/0 -> /BYTES/0x89 376 InitPut /Table/59/3/2/2/1 -> /TUPLE/3:3:Int/3 377 InitPut /Table/59/3/2/3/1 -> /TUPLE/5:5:Int/5/1:6:Int/6 378 InitPut /Table/59/3/2/5/1 -> /TUPLE/8:8:Int/8 379 380 # Test some cases of the updater. 381 382 # Ensure success when some family k/v's are deleted, 383 # some family k/v's have different values, and some 384 # family k/v's are added. 385 query T kvtrace(Put,Del,CPut,prefix=/Table/59/2/) 386 UPDATE t SET b = 4, c = NULL, d = NULL, e = 7, f = NULL WHERE y = 2 387 ---- 388 CPut /Table/59/2/2/1/2/1 -> /TUPLE/3:3:Int/3/1:4:Int/4 (replacing raw_bytes:"\000\000\000\000\n3\006" timestamp:<> , if exists) 389 Del /Table/59/2/2/1/3/1 390 CPut /Table/59/2/2/1/4/1 -> /TUPLE/7:7:Int/7 (expecting does not exist) 391 Del /Table/59/2/2/1/5/1 392 393 query IIIIIIII 394 SELECT * FROM t@i2 395 ---- 396 1 2 3 4 NULL NULL 7 NULL 397 398 # Test a case where no k/v's other than the sentinel exist 399 # and all new k/v's have to be added. 400 statement ok 401 INSERT INTO t VALUES (3, 3, NULL, NULL, NULL, NULL, NULL, NULL) 402 403 query T kvtrace(Put,Del,CPut,prefix=/Table/59/2/) 404 UPDATE t SET a = 10, b = 11, c = 12, d = 13, e = 14, f = 15 WHERE y = 3 405 ---- 406 CPut /Table/59/2/3/3/2/1 -> /TUPLE/3:3:Int/10/1:4:Int/11 (expecting does not exist) 407 CPut /Table/59/2/3/3/3/1 -> /TUPLE/5:5:Int/12/1:6:Int/13 (expecting does not exist) 408 CPut /Table/59/2/3/3/4/1 -> /TUPLE/7:7:Int/14 (expecting does not exist) 409 CPut /Table/59/2/3/3/5/1 -> /TUPLE/8:8:Int/15 (expecting does not exist) 410 411 # Test a case where the update causes all k/v's other than 412 # the sentinel k/v to get deleted. 413 query T kvtrace(Del,Put,CPut,prefix=/Table/59/2/) 414 UPDATE t SET a = NULL, b = NULL, c = NULL, d = NULL, e = NULL, f = NULL WHERE y = 3 415 ---- 416 Del /Table/59/2/3/3/2/1 417 Del /Table/59/2/3/3/3/1 418 Del /Table/59/2/3/3/4/1 419 Del /Table/59/2/3/3/5/1 420 421 422 # Test a case that each k/v in the index entry gets 423 # rewritten when the key changes. 424 statement ok 425 INSERT INTO t VALUES (20, 21, 22, NULL, NULL, 25, NULL, 27); 426 427 query T kvtrace(Put,Del,CPut,prefix=/Table/59/2/) 428 UPDATE t SET y = 22 WHERE y = 21 429 ---- 430 Del /Table/59/2/21/20/0 431 CPut /Table/59/2/22/20/0 -> /BYTES/ (expecting does not exist) 432 Del /Table/59/2/21/20/2/1 433 CPut /Table/59/2/22/20/2/1 -> /TUPLE/3:3:Int/22 (expecting does not exist) 434 Del /Table/59/2/21/20/3/1 435 CPut /Table/59/2/22/20/3/1 -> /TUPLE/6:6:Int/25 (expecting does not exist) 436 Del /Table/59/2/21/20/5/1 437 CPut /Table/59/2/22/20/5/1 -> /TUPLE/8:8:Int/27 (expecting does not exist) 438 439 # Ensure that the final results on both indexes make sense. 440 query IIIIIIII rowsort 441 SELECT * FROM t@i1 442 ---- 443 1 2 3 4 NULL NULL 7 NULL 444 3 3 NULL NULL NULL NULL NULL NULL 445 20 22 22 NULL NULL 25 NULL 27 446 447 query IIIIIIII rowsort 448 SELECT * FROM t@i2 449 ---- 450 1 2 3 4 NULL NULL 7 NULL 451 3 3 NULL NULL NULL NULL NULL NULL 452 20 22 22 NULL NULL 25 NULL 27 453 454 # Ensure that updating a row in the single family case still works. 455 statement ok 456 DROP TABLE IF EXISTS t; 457 CREATE TABLE t ( 458 x INT PRIMARY KEY, y INT, z INT, w INT, 459 INDEX i (y) STORING (z, w), 460 FAMILY (x, y, z, w) 461 ); 462 INSERT INTO t VALUES (1, 2, 3, 4) 463 464 # When the key is changed, we always delete and cput. 465 query T kvtrace(Put,CPut,Del,prefix=/Table/60/2/) 466 UPDATE t SET y = 5 where y = 2 467 ---- 468 Del /Table/60/2/2/1/0 469 CPut /Table/60/2/5/1/0 -> /BYTES/0x33061308 (expecting does not exist) 470 471 # Changing the value just results in a cput. 472 query T kvtrace(Put,Del,CPut,prefix=/Table/60/2/) 473 UPDATE t SET z = 5 where y = 5 474 ---- 475 CPut /Table/60/2/5/1/0 -> /BYTES/0x330a1308 (replacing raw_bytes:"\000\000\000\000\0033\006\023\010" timestamp:<> , if exists)