github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/hash_sharded_index (about) 1 # LogicTest: !3node-tenant 2 statement ok 3 SET experimental_enable_hash_sharded_indexes = true 4 5 # Tests for creating a hash sharded primary key 6 statement ok 7 CREATE TABLE sharded_primary (a INT PRIMARY KEY USING HASH WITH BUCKET_COUNT = 10) 8 9 query TT 10 SHOW CREATE TABLE sharded_primary 11 ---- 12 sharded_primary CREATE TABLE sharded_primary ( 13 a INT8 NOT NULL, 14 CONSTRAINT "primary" PRIMARY KEY (a ASC) USING HASH WITH BUCKET_COUNT = 10, 15 FAMILY "primary" (crdb_internal_a_shard_10, a) 16 ) 17 18 statement error pgcode 22023 BUCKET_COUNT must be an integer greater than 1 19 CREATE TABLE invalid_bucket_count (k INT PRIMARY KEY USING HASH WITH BUCKET_COUNT=-1) 20 21 statement error pgcode 22023 BUCKET_COUNT must be an integer greater than 1 22 CREATE TABLE invalid_bucket_count (k INT PRIMARY KEY USING HASH WITH BUCKET_COUNT=1) 23 24 statement error expected BUCKET_COUNT expression to have type int, but '2.32' has type decimal 25 CREATE TABLE fractional_bucket_count (k INT PRIMARY KEY USING HASH WITH BUCKET_COUNT=2.32) 26 27 statement error variable sub-expressions are not allowed in BUCKET_COUNT 28 CREATE TABLE invalid_bucket_count (k INT PRIMARY KEY USING HASH WITH BUCKET_COUNT=(SELECT 1)) 29 30 # Ensure that this is round-tripable 31 statement ok 32 DROP TABLE sharded_primary 33 34 statement ok 35 CREATE TABLE sharded_primary ( 36 a INT8 NOT NULL, 37 CONSTRAINT "primary" PRIMARY KEY (a ASC) USING HASH WITH BUCKET_COUNT = 10, 38 FAMILY "primary" (crdb_internal_a_shard_10, a) 39 ) 40 41 query TT 42 SHOW CREATE TABLE sharded_primary 43 ---- 44 sharded_primary CREATE TABLE sharded_primary ( 45 a INT8 NOT NULL, 46 CONSTRAINT "primary" PRIMARY KEY (a ASC) USING HASH WITH BUCKET_COUNT = 10, 47 FAMILY "primary" (crdb_internal_a_shard_10, a) 48 ) 49 50 statement ok 51 INSERT INTO sharded_primary values (1), (2), (3) 52 53 query error pq: duplicate key value \(crdb_internal_a_shard_10,a\)=\(6,1\) violates unique constraint "primary" 54 INSERT INTO sharded_primary values (1) 55 56 # Ensure that the shard column is assigned into the column family of the first column in 57 # the index column set. 58 statement ok 59 CREATE TABLE specific_family ( 60 a INT, 61 b INT, 62 INDEX (b) USING HASH WITH BUCKET_COUNT=10, 63 FAMILY "a_family" (a), 64 FAMILY "b_family" (b) 65 ) 66 67 query TT 68 SHOW CREATE TABLE specific_family 69 ---- 70 specific_family CREATE TABLE specific_family ( 71 a INT8 NULL, 72 b INT8 NULL, 73 INDEX specific_family_crdb_internal_b_shard_10_b_idx (b ASC) USING HASH WITH BUCKET_COUNT = 10, 74 FAMILY a_family (a, rowid), 75 FAMILY b_family (b, crdb_internal_b_shard_10) 76 ) 77 78 # Tests for secondary sharded indexes 79 statement ok 80 CREATE TABLE sharded_secondary (a INT, INDEX (a) USING HASH WITH BUCKET_COUNT=4) 81 82 query TT 83 SHOW CREATE TABLE sharded_secondary 84 ---- 85 sharded_secondary CREATE TABLE sharded_secondary ( 86 a INT8 NULL, 87 INDEX sharded_secondary_crdb_internal_a_shard_4_a_idx (a ASC) USING HASH WITH BUCKET_COUNT = 4, 88 FAMILY "primary" (a, crdb_internal_a_shard_4, rowid) 89 ) 90 91 statement ok 92 DROP TABLE sharded_secondary 93 94 statement ok 95 CREATE TABLE sharded_secondary ( 96 a INT8 NULL, 97 INDEX sharded_secondary_crdb_internal_a_shard_4_a_idx (a ASC) USING HASH WITH BUCKET_COUNT = 4, 98 FAMILY "primary" (a, crdb_internal_a_shard_4, rowid) 99 ) 100 101 query TT 102 SHOW CREATE TABLE sharded_secondary 103 ---- 104 sharded_secondary CREATE TABLE sharded_secondary ( 105 a INT8 NULL, 106 INDEX sharded_secondary_crdb_internal_a_shard_4_a_idx (a ASC) USING HASH WITH BUCKET_COUNT = 4, 107 FAMILY "primary" (a, crdb_internal_a_shard_4, rowid) 108 ) 109 110 statement ok 111 INSERT INTO sharded_secondary values (1), (2), (1) 112 113 statement ok 114 DROP TABLE sharded_secondary 115 116 statement ok 117 CREATE TABLE sharded_secondary ( 118 a INT 119 ) 120 121 statement ok 122 CREATE INDEX ON sharded_secondary (a) USING HASH WITH BUCKET_COUNT = 10 123 124 statement ok 125 INSERT INTO sharded_secondary values (1), (2), (1) 126 127 query TT 128 SHOW CREATE TABLE sharded_secondary 129 ---- 130 sharded_secondary CREATE TABLE sharded_secondary ( 131 a INT8 NULL, 132 INDEX sharded_secondary_crdb_internal_a_shard_10_a_idx (a ASC) USING HASH WITH BUCKET_COUNT = 10, 133 FAMILY "primary" (a, rowid, crdb_internal_a_shard_10) 134 ) 135 136 statement ok 137 INSERT INTO sharded_secondary values (3), (2), (1) 138 139 # Test multiple indexes on the same column set 140 statement ok 141 CREATE INDEX ON sharded_secondary (a) USING HASH WITH BUCKET_COUNT = 4 142 143 query TT 144 SHOW CREATE TABLE sharded_secondary 145 ---- 146 sharded_secondary CREATE TABLE sharded_secondary ( 147 a INT8 NULL, 148 INDEX sharded_secondary_crdb_internal_a_shard_10_a_idx (a ASC) USING HASH WITH BUCKET_COUNT = 10, 149 INDEX sharded_secondary_crdb_internal_a_shard_4_a_idx (a ASC) USING HASH WITH BUCKET_COUNT = 4, 150 FAMILY "primary" (a, rowid, crdb_internal_a_shard_10, crdb_internal_a_shard_4) 151 ) 152 153 # Drop a sharded index and ensure that the shard column is dropped with it. 154 statement ok 155 DROP INDEX sharded_secondary_crdb_internal_a_shard_4_a_idx 156 157 query TT 158 SHOW CREATE TABLE sharded_secondary 159 ---- 160 sharded_secondary CREATE TABLE sharded_secondary ( 161 a INT8 NULL, 162 INDEX sharded_secondary_crdb_internal_a_shard_10_a_idx (a ASC) USING HASH WITH BUCKET_COUNT = 10, 163 FAMILY "primary" (a, rowid, crdb_internal_a_shard_10) 164 ) 165 166 statement ok 167 DROP INDEX sharded_secondary_crdb_internal_a_shard_10_a_idx 168 169 170 query TT 171 SHOW CREATE TABLE sharded_secondary 172 ---- 173 sharded_secondary CREATE TABLE sharded_secondary ( 174 a INT8 NULL, 175 FAMILY "primary" (a, rowid) 176 ) 177 178 # Ensure that the shard column cannot be used in the same txn if its dropped along with 179 # the sharded index. 180 statement ok 181 CREATE INDEX idx on sharded_secondary (a) USING HASH WITH BUCKET_COUNT = 3 182 183 statement ok 184 BEGIN 185 186 statement ok 187 SELECT crdb_internal_a_shard_3 FROM sharded_secondary 188 189 statement ok 190 DROP INDEX sharded_secondary@idx 191 192 statement error pq: column "crdb_internal_a_shard_3" does not exist 193 SELECT crdb_internal_a_shard_3 FROM sharded_secondary 194 195 statement ok 196 ROLLBACK 197 198 statement ok 199 DROP INDEX sharded_secondary@idx 200 201 # Ensure that multiple (> 2) identical indexes can be created. 202 statement ok 203 CREATE INDEX ON sharded_secondary (a) USING HASH WITH BUCKET_COUNT=10 204 205 statement ok 206 CREATE INDEX ON sharded_secondary (a) USING HASH WITH BUCKET_COUNT=10 207 208 statement ok 209 CREATE INDEX ON sharded_secondary (a) USING HASH WITH BUCKET_COUNT=10 210 211 query TT 212 SHOW CREATE TABLE sharded_secondary 213 ---- 214 sharded_secondary CREATE TABLE sharded_secondary ( 215 a INT8 NULL, 216 INDEX sharded_secondary_crdb_internal_a_shard_10_a_idx (a ASC) USING HASH WITH BUCKET_COUNT = 10, 217 INDEX sharded_secondary_crdb_internal_a_shard_10_a_idx1 (a ASC) USING HASH WITH BUCKET_COUNT = 10, 218 INDEX sharded_secondary_crdb_internal_a_shard_10_a_idx2 (a ASC) USING HASH WITH BUCKET_COUNT = 10, 219 FAMILY "primary" (a, rowid, crdb_internal_a_shard_10) 220 ) 221 222 223 # Ensure that the table descriptor was left in a "valid" state 224 query I 225 SELECT count(*) FROM sharded_secondary 226 ---- 227 6 228 229 statement ok 230 CREATE INDEX ON sharded_primary (a) USING HASH WITH BUCKET_COUNT = 4; 231 232 query TT 233 SHOW CREATE TABLE sharded_primary 234 ---- 235 sharded_primary CREATE TABLE sharded_primary ( 236 a INT8 NOT NULL, 237 CONSTRAINT "primary" PRIMARY KEY (a ASC) USING HASH WITH BUCKET_COUNT = 10, 238 INDEX sharded_primary_crdb_internal_a_shard_4_a_idx (a ASC) USING HASH WITH BUCKET_COUNT = 4, 239 FAMILY "primary" (crdb_internal_a_shard_10, a, crdb_internal_a_shard_4) 240 ) 241 242 statement ok 243 DROP INDEX sharded_primary_crdb_internal_a_shard_4_a_idx 244 245 statement ok 246 SELECT count(*) FROM sharded_primary 247 248 query TT 249 SHOW CREATE TABLE sharded_primary 250 ---- 251 sharded_primary CREATE TABLE sharded_primary ( 252 a INT8 NOT NULL, 253 CONSTRAINT "primary" PRIMARY KEY (a ASC) USING HASH WITH BUCKET_COUNT = 10, 254 FAMILY "primary" (crdb_internal_a_shard_10, a) 255 ) 256 257 statement ok 258 CREATE INDEX on sharded_primary (a) USING HASH WITH BUCKET_COUNT=10; 259 260 query TT 261 SHOW CREATE TABLE sharded_primary 262 ---- 263 sharded_primary CREATE TABLE sharded_primary ( 264 a INT8 NOT NULL, 265 CONSTRAINT "primary" PRIMARY KEY (a ASC) USING HASH WITH BUCKET_COUNT = 10, 266 INDEX sharded_primary_crdb_internal_a_shard_10_a_idx (a ASC) USING HASH WITH BUCKET_COUNT = 10, 267 FAMILY "primary" (crdb_internal_a_shard_10, a) 268 ) 269 270 statement ok 271 DROP INDEX sharded_primary_crdb_internal_a_shard_10_a_idx 272 273 # Ensure that the table descriptor was left in a "valid" state 274 statement ok 275 SELECT count(*) FROM sharded_primary 276 277 statement ok 278 DROP TABLE sharded_secondary 279 280 statement ok 281 CREATE TABLE sharded_secondary (a INT8, INDEX (a) USING HASH WITH BUCKET_COUNT=12) 282 283 # Ensure that hash sharded indexes can be created on columns that are added in the same 284 # statement, just like non-sharded indexes. 285 statement ok 286 BEGIN TRANSACTION 287 288 statement ok 289 ALTER TABLE sharded_secondary ADD COLUMN b INT 290 291 statement ok 292 CREATE INDEX ON sharded_secondary (a, b) USING HASH WITH BUCKET_COUNT=12 293 294 statement ok 295 COMMIT TRANSACTION 296 297 # Ensure that sharded indexes cannot be created on computed columns 298 statement ok 299 ALTER TABLE sharded_secondary ADD COLUMN c INT AS (mod(a, 100)) STORED 300 301 statement error cannot create a sharded index on a computed column 302 CREATE INDEX ON sharded_secondary (a, c) USING HASH WITH BUCKET_COUNT=12; 303 304 # Ensure that sharded indexes cannot be created on computed columns 305 # in the same txn 306 statement error cannot create a sharded index on a computed column 307 CREATE TABLE shard_on_computed_column ( 308 a INT, 309 b INT AS (a % 5) STORED, 310 INDEX (b) USING HASH WITH BUCKET_COUNT=10 311 ) 312 313 statement ok 314 BEGIN TRANSACTION 315 316 statement ok 317 ALTER TABLE sharded_secondary ADD COLUMN d INT AS (mod(a, 100)) STORED 318 319 statement error cannot create a sharded index on a computed column 320 CREATE INDEX ON sharded_secondary (a, d) USING HASH WITH BUCKET_COUNT=12; 321 322 statement ok 323 ROLLBACK TRANSACTION 324 325 # Ensure that the shard column isn't dropped even if its being used by a non-sharded index 326 statement ok 327 CREATE TABLE column_used_on_unsharded ( 328 a INT, 329 INDEX foo (a) USING HASH WITH BUCKET_COUNT=10 330 ) 331 332 statement ok 333 CREATE INDEX on column_used_on_unsharded (crdb_internal_a_shard_10) 334 335 statement ok 336 DROP INDEX column_used_on_unsharded@foo 337 338 query TT 339 SHOW CREATE TABLE column_used_on_unsharded 340 ---- 341 column_used_on_unsharded CREATE TABLE column_used_on_unsharded ( 342 a INT8 NULL, 343 INDEX column_used_on_unsharded_crdb_internal_a_shard_10_idx (crdb_internal_a_shard_10 ASC), 344 FAMILY "primary" (a, crdb_internal_a_shard_10, rowid) 345 ) 346 347 statement ok 348 DROP INDEX column_used_on_unsharded_crdb_internal_a_shard_10_idx 349 350 statement ok 351 CREATE TABLE column_used_on_unsharded_create_table ( 352 a INT, 353 INDEX foo (a) USING HASH WITH BUCKET_COUNT=10, 354 INDEX (crdb_internal_a_shard_10) 355 ) 356 357 statement ok 358 DROP INDEX column_used_on_unsharded_create_table@foo 359 360 query TT 361 SHOW CREATE TABLE column_used_on_unsharded_create_table 362 ---- 363 column_used_on_unsharded_create_table CREATE TABLE column_used_on_unsharded_create_table ( 364 a INT8 NULL, 365 INDEX column_used_on_unsharded_create_table_crdb_internal_a_shard_10_idx (crdb_internal_a_shard_10 ASC), 366 FAMILY "primary" (a, crdb_internal_a_shard_10, rowid) 367 ) 368 369 statement ok 370 DROP INDEX column_used_on_unsharded_create_table_crdb_internal_a_shard_10_idx 371 372 statement ok 373 DROP TABLE sharded_primary 374 375 statement ok 376 SET experimental_enable_hash_sharded_indexes = false 377 378 statement error pq: hash sharded indexes require the experimental_enable_hash_sharded_indexes cluster setting 379 CREATE TABLE disabled (k INT PRIMARY KEY USING HASH WITH BUCKET_COUNT = 10) 380 381 statement ok 382 CREATE TABLE disabled_secondary (k INT, v BYTES) 383 384 statement error pq: hash sharded indexes require the experimental_enable_hash_sharded_indexes cluster setting 385 CREATE INDEX failure on disabled_secondary (k) USING HASH WITH BUCKET_COUNT = 12 386 387 statement error pq: hash sharded indexes require the experimental_enable_hash_sharded_indexes cluster setting 388 CREATE TABLE disabled (k INT, INDEX (k) USING HASH WITH BUCKET_COUNT = 10) 389 390 # Ensure everything works with weird column names 391 statement ok 392 SET experimental_enable_hash_sharded_indexes = true 393 394 statement ok 395 CREATE TABLE weird_names ( 396 "I am a column with spaces" INT PRIMARY KEY USING HASH WITH BUCKET_COUNT = 12, 397 "'quotes' in the column's name" INT, 398 FAMILY "primary" ("I am a column with spaces", "'quotes' in the column's name") 399 ) 400 401 statement ok 402 CREATE INDEX foo on weird_names ("'quotes' in the column's name") USING HASH WITH BUCKET_COUNT = 4 403 404 statement ok 405 INSERT INTO weird_names VALUES (1, 2) 406 407 query I 408 SELECT count(*) from weird_names WHERE "'quotes' in the column's name" = 2 409 ---- 410 1 411 412 query TT 413 SHOW CREATE TABLE weird_names 414 ---- 415 weird_names CREATE TABLE weird_names ( 416 "I am a column with spaces" INT8 NOT NULL, 417 "'quotes' in the column's name" INT8 NULL, 418 CONSTRAINT "primary" PRIMARY KEY ("I am a column with spaces" ASC) USING HASH WITH BUCKET_COUNT = 12, 419 INDEX foo ("'quotes' in the column's name" ASC) USING HASH WITH BUCKET_COUNT = 4, 420 FAMILY "primary" ("I am a column with spaces", "'quotes' in the column's name", "crdb_internal_I am a column with spaces_shard_12", "crdb_internal_'quotes' in the column's name_shard_4") 421 ) 422 423 subtest interleave_disabled 424 425 statement ok 426 CREATE TABLE parent (x INT PRIMARY KEY); 427 428 statement error pq: interleaved indexes cannot also be hash sharded 429 CREATE TABLE t (x INT PRIMARY KEY USING HASH WITH BUCKET_COUNT = 10) INTERLEAVE IN PARENT parent (x) 430 431 statement error pq: interleaved indexes cannot also be hash sharded 432 CREATE TABLE t (x INT, y INT, PRIMARY KEY (x, y) USING HASH WITH BUCKET_COUNT = 10) INTERLEAVE IN PARENT parent (x) 433 434 statement error pq: interleaved indexes cannot also be hash sharded 435 CREATE INDEX ON parent (x) USING HASH WITH BUCKET_COUNT = 10 INTERLEAVE IN PARENT parent(x) 436 437 statement ok 438 DROP TABLE parent; 439 440 # This test ensures that the appropriate error is returned when trying to create 441 # a hash sharded index with a column which does not exist. 442 subtest column_does_not_exist 443 444 statement ok 445 CREATE TABLE t0(); 446 447 statement error column "c0" does not exist 448 CREATE INDEX ON t0 (c0) USING HASH WITH BUCKET_COUNT = 8; 449 450 statement ok 451 DROP TABLE t0; 452 453 # Test that creating an index on a column which is currently being dropped 454 # causes an error. 455 subtest create_hash_index_on_dropping_column 456 457 statement ok 458 CREATE TABLE create_idx_drop_column (c0 INT PRIMARY KEY, c1 INT); 459 460 statement ok 461 begin; ALTER TABLE create_idx_drop_column DROP COLUMN c1; 462 463 statement error column "c1" does not exist 464 CREATE INDEX idx_create_idx_drop_column ON create_idx_drop_column (c1) USING HASH WITH BUCKET_COUNT = 8; 465 466 statement ok 467 ROLLBACK; 468 469 statement ok 470 DROP TABLE create_idx_drop_column; 471 472 # Test that NULL values can be a part of a hash-sharded index. 473 subtest null_values_in_sharded_columns 474 475 statement ok 476 CREATE TABLE sharded_index_with_nulls ( 477 a INT8 PRIMARY KEY, 478 b INT8, 479 INDEX (b) USING HASH WITH BUCKET_COUNT = 8 480 ) 481 482 statement ok 483 INSERT INTO sharded_index_with_nulls VALUES (1, NULL); 484 485 statement ok 486 DROP TABLE sharded_index_with_nulls; 487 488 # Test that renaming a column which is a member of a hash sharded index works. 489 subtest rename_column 490 491 statement ok 492 CREATE TABLE rename_column ( 493 c0 INT, 494 c1 INT, 495 c2 INT, 496 PRIMARY KEY (c0, c1) USING HASH WITH BUCKET_COUNT = 8, 497 INDEX (c2) USING HASH WITH BUCKET_COUNT = 8, 498 FAMILY "primary" (c0, c1, c2) 499 ); 500 501 statement ok 502 INSERT INTO rename_column VALUES (1, 2, 3); 503 504 query TT 505 SHOW CREATE TABLE rename_column 506 ---- 507 rename_column CREATE TABLE rename_column ( 508 c0 INT8 NOT NULL, 509 c1 INT8 NOT NULL, 510 c2 INT8 NULL, 511 CONSTRAINT "primary" PRIMARY KEY (c0 ASC, c1 ASC) USING HASH WITH BUCKET_COUNT = 8, 512 INDEX rename_column_crdb_internal_c2_shard_8_c2_idx (c2 ASC) USING HASH WITH BUCKET_COUNT = 8, 513 FAMILY "primary" (c0, c1, c2, crdb_internal_c0_c1_shard_8, crdb_internal_c2_shard_8) 514 ) 515 516 statement ok 517 ALTER TABLE rename_column RENAME c2 TO c3; 518 519 # Test mucking with primary key columns. 520 statement ok 521 ALTER TABLE rename_column RENAME c1 TO c2; 522 523 statement ok 524 ALTER TABLE rename_column RENAME c0 TO c1; 525 526 query TT 527 SHOW CREATE TABLE rename_column 528 ---- 529 rename_column CREATE TABLE rename_column ( 530 c1 INT8 NOT NULL, 531 c2 INT8 NOT NULL, 532 c3 INT8 NULL, 533 CONSTRAINT "primary" PRIMARY KEY (c1 ASC, c2 ASC) USING HASH WITH BUCKET_COUNT = 8, 534 INDEX rename_column_crdb_internal_c2_shard_8_c2_idx (c3 ASC) USING HASH WITH BUCKET_COUNT = 8, 535 FAMILY "primary" (c1, c2, c3, crdb_internal_c1_c2_shard_8, crdb_internal_c3_shard_8) 536 ) 537 538 query III 539 SELECT c3, c2, c1 FROM rename_column 540 ---- 541 3 2 1 542 543 # Test both at the same time. 544 statement ok 545 ALTER TABLE rename_column RENAME c1 TO c0, RENAME c2 TO c1, RENAME c3 TO c2; 546 547 query TT 548 SHOW CREATE TABLE rename_column 549 ---- 550 rename_column CREATE TABLE rename_column ( 551 c0 INT8 NOT NULL, 552 c1 INT8 NOT NULL, 553 c2 INT8 NULL, 554 CONSTRAINT "primary" PRIMARY KEY (c0 ASC, c1 ASC) USING HASH WITH BUCKET_COUNT = 8, 555 INDEX rename_column_crdb_internal_c2_shard_8_c2_idx (c2 ASC) USING HASH WITH BUCKET_COUNT = 8, 556 FAMILY "primary" (c0, c1, c2, crdb_internal_c0_c1_shard_8, crdb_internal_c2_shard_8) 557 ) 558 559 query III 560 SELECT c2, c1, c0 FROM rename_column 561 ---- 562 3 2 1 563 564 # Ensure that renaming a shard column fails. 565 statement error cannot rename shard column 566 ALTER TABLE rename_column RENAME crdb_internal_c2_shard_8 TO foo; 567 568 statement ok 569 DROP TABLE rename_column;