github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/inverted_index (about) 1 # LogicTest: local 2 3 statement ok 4 CREATE TABLE d ( 5 a INT PRIMARY KEY, 6 b JSONB, 7 FAMILY (a,b) 8 ) 9 10 statement ok 11 CREATE TABLE e ( 12 a INT PRIMARY KEY, 13 b INT[], 14 FAMILY (a,b), 15 INVERTED INDEX(b) 16 ) 17 18 statement ok 19 CREATE TABLE f ( 20 a INT PRIMARY KEY, 21 b DECIMAL[], 22 FAMILY (a,b), 23 INVERTED INDEX(b) 24 ) 25 26 statement ok 27 CREATE INVERTED INDEX foo_inv ON d(b) 28 29 # Tests for json inverted indexes. 30 31 query T kvtrace 32 INSERT INTO d VALUES(0, '{"a": "b"}') 33 ---- 34 CPut /Table/53/1/0/0 -> /TUPLE/ 35 InitPut /Table/53/2/"a"/"b"/0/0 -> /BYTES/ 36 37 # Make sure duplicate values don't get inserted. 38 query T kvtrace 39 INSERT INTO d VALUES(1, '[7,0,7]') 40 ---- 41 CPut /Table/53/1/1/0 -> /TUPLE/ 42 InitPut /Table/53/2/Arr/0/1/0 -> /BYTES/ 43 InitPut /Table/53/2/Arr/7/1/0 -> /BYTES/ 44 45 # Make sure duplicate values don't get deleted either. 46 query T kvtrace 47 DELETE FROM d WHERE a=1 48 ---- 49 Scan /Table/53/1/1{-/#} 50 Del /Table/53/2/Arr/0/1/0 51 Del /Table/53/2/Arr/7/1/0 52 Del /Table/53/1/1/0 53 54 query T kvtrace 55 INSERT INTO d VALUES(2, '[{"a": "b"}, 3, {"a": "b"}]') 56 ---- 57 CPut /Table/53/1/2/0 -> /TUPLE/ 58 InitPut /Table/53/2/Arr/3/2/0 -> /BYTES/ 59 InitPut /Table/53/2/Arr/"a"/"b"/2/0 -> /BYTES/ 60 61 query T kvtrace 62 INSERT INTO d VALUES(3, '[{"a": [0,1,0]}, 3, {"a": "b"}]') 63 ---- 64 CPut /Table/53/1/3/0 -> /TUPLE/ 65 InitPut /Table/53/2/Arr/3/3/0 -> /BYTES/ 66 InitPut /Table/53/2/Arr/"a"/"b"/3/0 -> /BYTES/ 67 InitPut /Table/53/2/Arr/"a"/Arr/0/3/0 -> /BYTES/ 68 InitPut /Table/53/2/Arr/"a"/Arr/1/3/0 -> /BYTES/ 69 70 # Make sure that inserting NULL doesn't make an index entry. 71 query T kvtrace 72 INSERT INTO d VALUES(4, NULL) 73 ---- 74 CPut /Table/53/1/4/0 -> /TUPLE/ 75 76 # Update away from null. 77 query T kvtrace 78 UPDATE d SET b='[1]' WHERE a=4 79 ---- 80 Scan /Table/53/1/4{-/#} 81 Put /Table/53/1/4/0 -> /TUPLE/ 82 InitPut /Table/53/2/Arr/1/4/0 -> /BYTES/ 83 84 # Update back to null. 85 query T kvtrace 86 UPDATE d SET b=NULL WHERE a=4 87 ---- 88 Scan /Table/53/1/4{-/#} 89 Put /Table/53/1/4/0 -> /TUPLE/ 90 Del /Table/53/2/Arr/1/4/0 91 92 # Deleting a null shouldn't remove anything from the inv idx. 93 query T kvtrace 94 DELETE FROM d WHERE a=4 95 ---- 96 Scan /Table/53/1/4{-/#} 97 Del /Table/53/1/4/0 98 99 # Tests for array inverted indexes. 100 101 # Make sure that duplicate entries do not get emitted more than once, and that 102 # null keys don't get emitted. 103 query T kvtrace 104 INSERT INTO e VALUES(0, ARRAY[7,0,0,1,NULL,10,0,1,7,NULL]) 105 ---- 106 CPut /Table/54/1/0/0 -> /TUPLE/ 107 InitPut /Table/54/2/0/0/0 -> /BYTES/ 108 InitPut /Table/54/2/1/0/0 -> /BYTES/ 109 InitPut /Table/54/2/7/0/0 -> /BYTES/ 110 InitPut /Table/54/2/10/0/0 -> /BYTES/ 111 112 # Make sure that empty arrays do not emit any keys at all. 113 query T kvtrace 114 INSERT INTO e VALUES(1, ARRAY[]) 115 ---- 116 CPut /Table/54/1/1/0 -> /TUPLE/ 117 118 # Make sure that NULL arrays do not emit any keys at all. 119 query T kvtrace 120 INSERT INTO e VALUES(2, NULL) 121 ---- 122 CPut /Table/54/1/2/0 -> /TUPLE/ 123 124 # Make sure that NULL entries within an array don't emit any keys. 125 query T kvtrace 126 INSERT INTO e VALUES(3, ARRAY[NULL]) 127 ---- 128 CPut /Table/54/1/3/0 -> /TUPLE/ 129 130 # Test that array inverted indexes work okay with decimals (a type with 131 # composite encoding). Also, make sure that the composite encoding is 132 # de-duplicated - 1.0 and 1.00 should just have one entry. 133 134 query T kvtrace 135 INSERT INTO f VALUES(0, ARRAY[7,0,0,1.000,10,0,1,7,1.0,1.00]) 136 ---- 137 CPut /Table/55/1/0/0 -> /TUPLE/ 138 InitPut /Table/55/2/0/0/0 -> /BYTES/ 139 InitPut /Table/55/2/1/0/0 -> /BYTES/ 140 InitPut /Table/55/2/7/0/0 -> /BYTES/ 141 InitPut /Table/55/2/1E+1/0/0 -> /BYTES/ 142 143 query T kvtrace 144 INSERT INTO f VALUES(1, ARRAY[]) 145 ---- 146 CPut /Table/55/1/1/0 -> /TUPLE/ 147 148 query T kvtrace 149 INSERT INTO f VALUES(2, NULL) 150 ---- 151 CPut /Table/55/1/2/0 -> /TUPLE/ 152 153 # Test that updating a row that edits inverted index entries doesn't delete and 154 # re-put things that exist both in the old and in the new value. 155 # This case adds 15 and removes 1, so we should just see those commands. 156 query T kvtrace 157 UPDATE f SET b = ARRAY[0,15,7,10] WHERE a = 0 158 ---- 159 Scan /Table/55/1/0{-/#} 160 Put /Table/55/1/0/0 -> /TUPLE/ 161 Del /Table/55/2/1/0/0 162 InitPut /Table/55/2/15/0/0 -> /BYTES/ 163 164 query TTTTT 165 EXPLAIN (VERBOSE) SELECT * from d where b @>'{"a": "b"}' 166 ---- 167 · distributed false · · 168 · vectorized true · · 169 index-join · · (a, b) · 170 │ table d@primary · · 171 │ key columns a · · 172 └── scan · · (a) · 173 · table d@foo_inv · · 174 · spans /"a"/"b"-/"a"/"b"/PrefixEnd · · 175 176 query TTTTT 177 EXPLAIN (VERBOSE) SELECT * from d where b @>'{"a": {"b": [1]}}' 178 ---- 179 · distributed false · · 180 · vectorized true · · 181 index-join · · (a, b) · 182 │ table d@primary · · 183 │ key columns a · · 184 └── scan · · (a) · 185 · table d@foo_inv · · 186 · spans /"a"/"b"/Arr/1-/"a"/"b"/Arr/1/PrefixEnd · · 187 188 query TTTTT 189 EXPLAIN (VERBOSE) SELECT * from d where b @> '{"a": {"b": [[2]]}}'; 190 ---- 191 · distributed false · · 192 · vectorized true · · 193 index-join · · (a, b) · 194 │ table d@primary · · 195 │ key columns a · · 196 └── scan · · (a) · 197 · table d@foo_inv · · 198 · spans /"a"/"b"/Arr/Arr/2-/"a"/"b"/Arr/Arr/2/PrefixEnd · · 199 200 query TTTTT 201 EXPLAIN (VERBOSE) SELECT * from d where b @> '{"a": {"b":true}}'; 202 ---- 203 · distributed false · · 204 · vectorized true · · 205 index-join · · (a, b) · 206 │ table d@primary · · 207 │ key columns a · · 208 └── scan · · (a) · 209 · table d@foo_inv · · 210 · spans /"a"/"b"/True-/"a"/"b"/False · · 211 212 query TTTTT 213 EXPLAIN (VERBOSE) SELECT * from d where b @>'[1]' 214 ---- 215 · distributed false · · 216 · vectorized true · · 217 index-join · · (a, b) · 218 │ table d@primary · · 219 │ key columns a · · 220 └── scan · · (a) · 221 · table d@foo_inv · · 222 · spans /Arr/1-/Arr/1/PrefixEnd · · 223 224 query TTTTT 225 EXPLAIN (VERBOSE) SELECT * from d where b @>'[{"a": {"b": [1]}}]' 226 ---- 227 · distributed false · · 228 · vectorized true · · 229 index-join · · (a, b) · 230 │ table d@primary · · 231 │ key columns a · · 232 └── scan · · (a) · 233 · table d@foo_inv · · 234 · spans /Arr/"a"/"b"/Arr/1-/Arr/"a"/"b"/Arr/1/PrefixEnd · · 235 236 query TTTTT 237 EXPLAIN (VERBOSE) SELECT * from d where b @> '[]'; 238 ---- 239 · distributed false · · 240 · vectorized true · · 241 scan · · (a, b) · 242 · table d@primary · · 243 · spans FULL SCAN · · 244 · filter b @> '[]' · · 245 246 247 query TTTTT 248 EXPLAIN (VERBOSE) SELECT * from d where b @> '{}'; 249 ---- 250 · distributed false · · 251 · vectorized true · · 252 scan · · (a, b) · 253 · table d@primary · · 254 · spans FULL SCAN · · 255 · filter b @> '{}' · · 256 257 258 query TTTTT 259 EXPLAIN (VERBOSE) SELECT * from d where b->'a' = '"b"' 260 ---- 261 · distributed false · · 262 · vectorized true · · 263 index-join · · (a, b) · 264 │ table d@primary · · 265 │ key columns a · · 266 └── scan · · (a) · 267 · table d@foo_inv · · 268 · spans /"a"/"b"-/"a"/"b"/PrefixEnd · · 269 270 query TTTTT 271 EXPLAIN (VERBOSE) SELECT * from d where b->'a'->'c' = '"b"' 272 ---- 273 · distributed false · · 274 · vectorized true · · 275 index-join · · (a, b) · 276 │ table d@primary · · 277 │ key columns a · · 278 └── scan · · (a) · 279 · table d@foo_inv · · 280 · spans /"a"/"c"/"b"-/"a"/"c"/"b"/PrefixEnd · · 281 282 query TTTTT 283 EXPLAIN (VERBOSE) SELECT * from d where b->(NULL::STRING) = '"b"' 284 ---- 285 · distributed false · · 286 · vectorized true · · 287 norows · · (a, b) · 288 289 query TTTTT 290 EXPLAIN (VERBOSE) SELECT * from d where '"b"' = b->'a' 291 ---- 292 · distributed false · · 293 · vectorized true · · 294 index-join · · (a, b) · 295 │ table d@primary · · 296 │ key columns a · · 297 └── scan · · (a) · 298 · table d@foo_inv · · 299 · spans /"a"/"b"-/"a"/"b"/PrefixEnd · · 300 301 # Make sure that querying for NULL equality doesn't use the inverted index. 302 query TTTTT 303 EXPLAIN (VERBOSE) SELECT * from d where b IS NULL 304 ---- 305 · distributed false · · 306 · vectorized true · · 307 scan · · (a, b) · 308 · table d@primary · · 309 · spans FULL SCAN · · 310 · filter b IS NULL · · 311 312 query TTT 313 EXPLAIN SELECT * from d where b @> '{"a": []}' ORDER BY a; 314 ---- 315 · distributed false 316 · vectorized true 317 scan · · 318 · table d@primary 319 · spans FULL SCAN 320 · filter b @> '{"a": []}' 321 322 query TTT 323 EXPLAIN SELECT * from d where b @> '{"a": {}}' ORDER BY a; 324 ---- 325 · distributed false 326 · vectorized true 327 scan · · 328 · table d@primary 329 · spans FULL SCAN 330 · filter b @> '{"a": {}}' 331 332 # Multi-path contains queries. Should create zigzag joins. 333 334 query TTTTT 335 EXPLAIN (VERBOSE) SELECT * from d where b @> '{"a": {"b": "c"}, "f": "g"}' 336 ---- 337 · distributed false · · 338 · vectorized true · · 339 lookup-join · · (a, b) · 340 │ table d@primary · · 341 │ type inner · · 342 │ equality (a) = (a) · · 343 │ equality cols are key · · · 344 │ parallel · · · 345 │ pred @2 @> '{"a": {"b": "c"}, "f": "g"}' · · 346 └── zigzag-join · · (a) · 347 │ type inner · · 348 ├── scan · · (a) · 349 │ table d@foo_inv · · 350 │ fixedvals 1 column · · 351 └── scan · · () · 352 · table d@foo_inv · · 353 · fixedvals 1 column · · 354 355 query TTTTT 356 EXPLAIN (VERBOSE) SELECT * from d where b @> '{"a": {"b": "c", "d": "e"}, "f": "g"}' 357 ---- 358 · distributed false · · 359 · vectorized true · · 360 lookup-join · · (a, b) · 361 │ table d@primary · · 362 │ type inner · · 363 │ equality (a) = (a) · · 364 │ equality cols are key · · · 365 │ parallel · · · 366 │ pred @2 @> '{"a": {"b": "c", "d": "e"}, "f": "g"}' · · 367 └── zigzag-join · · (a) · 368 │ type inner · · 369 ├── scan · · (a) · 370 │ table d@foo_inv · · 371 │ fixedvals 1 column · · 372 └── scan · · () · 373 · table d@foo_inv · · 374 · fixedvals 1 column · · 375 376 query TTTTT 377 EXPLAIN (VERBOSE) SELECT * from d where b @> '[{"a": {"b": [[2]]}}, "d"]' 378 ---- 379 · distributed false · · 380 · vectorized true · · 381 lookup-join · · (a, b) · 382 │ table d@primary · · 383 │ type inner · · 384 │ equality (a) = (a) · · 385 │ equality cols are key · · · 386 │ parallel · · · 387 │ pred @2 @> '[{"a": {"b": [[2]]}}, "d"]' · · 388 └── zigzag-join · · (a) · 389 │ type inner · · 390 ├── scan · · (a) · 391 │ table d@foo_inv · · 392 │ fixedvals 1 column · · 393 └── scan · · () · 394 · table d@foo_inv · · 395 · fixedvals 1 column · · 396 397 statement ok 398 SET enable_zigzag_join = true 399 400 query TTTTT 401 EXPLAIN (VERBOSE) SELECT * from d where b @> '{"a": {"b": "c"}, "f": "g"}' 402 ---- 403 · distributed false · · 404 · vectorized true · · 405 lookup-join · · (a, b) · 406 │ table d@primary · · 407 │ type inner · · 408 │ equality (a) = (a) · · 409 │ equality cols are key · · · 410 │ parallel · · · 411 │ pred @2 @> '{"a": {"b": "c"}, "f": "g"}' · · 412 └── zigzag-join · · (a) · 413 │ type inner · · 414 ├── scan · · (a) · 415 │ table d@foo_inv · · 416 │ fixedvals 1 column · · 417 └── scan · · () · 418 · table d@foo_inv · · 419 · fixedvals 1 column · · 420 421 query TTTTT 422 EXPLAIN (VERBOSE) SELECT * from d where b @> '{"a": {"b": "c", "d": "e"}, "f": "g"}' 423 ---- 424 · distributed false · · 425 · vectorized true · · 426 lookup-join · · (a, b) · 427 │ table d@primary · · 428 │ type inner · · 429 │ equality (a) = (a) · · 430 │ equality cols are key · · · 431 │ parallel · · · 432 │ pred @2 @> '{"a": {"b": "c", "d": "e"}, "f": "g"}' · · 433 └── zigzag-join · · (a) · 434 │ type inner · · 435 ├── scan · · (a) · 436 │ table d@foo_inv · · 437 │ fixedvals 1 column · · 438 └── scan · · () · 439 · table d@foo_inv · · 440 · fixedvals 1 column · · 441 442 query TTTTT 443 EXPLAIN (VERBOSE) SELECT * from d where b @> '[{"a": {"b": [[2]]}}, "d"]' 444 ---- 445 · distributed false · · 446 · vectorized true · · 447 lookup-join · · (a, b) · 448 │ table d@primary · · 449 │ type inner · · 450 │ equality (a) = (a) · · 451 │ equality cols are key · · · 452 │ parallel · · · 453 │ pred @2 @> '[{"a": {"b": [[2]]}}, "d"]' · · 454 └── zigzag-join · · (a) · 455 │ type inner · · 456 ├── scan · · (a) · 457 │ table d@foo_inv · · 458 │ fixedvals 1 column · · 459 └── scan · · () · 460 · table d@foo_inv · · 461 · fixedvals 1 column · · 462 463 query TTTTT 464 EXPLAIN (VERBOSE) SELECT * from d where b @> '{"a": {}, "b": 2}' 465 ---- 466 · distributed false · · 467 · vectorized true · · 468 filter · · (a, b) · 469 │ filter b @> '{"a": {}, "b": 2}' · · 470 └── index-join · · (a, b) · 471 │ table d@primary · · 472 │ key columns a · · 473 └── scan · · (a) · 474 · table d@foo_inv · · 475 · spans /"b"/2-/"b"/2/PrefixEnd · · 476 477 query TTTTT 478 EXPLAIN (VERBOSE) SELECT * from d where b @> '{"a": {}, "b": {}}' 479 ---- 480 · distributed false · · 481 · vectorized true · · 482 scan · · (a, b) · 483 · table d@primary · · 484 · spans FULL SCAN · · 485 · filter b @> '{"a": {}, "b": {}}' · · 486 487 subtest array 488 489 # Tests for array inverted indexes. 490 query TTTTT 491 EXPLAIN (VERBOSE) SELECT * from e where b @> ARRAY[1] 492 ---- 493 · distributed false · · 494 · vectorized true · · 495 index-join · · (a, b) · 496 │ table e@primary · · 497 │ key columns a · · 498 └── scan · · (a) · 499 · table e@e_b_idx · · 500 · spans /1-/2 · · 501 502 query TTTTT 503 EXPLAIN (VERBOSE) SELECT * from e where b @> ARRAY[]::INT[] 504 ---- 505 · distributed false · · 506 · vectorized true · · 507 scan · · (a, b) · 508 · table e@primary · · 509 · spans FULL SCAN · · 510 · filter b @> ARRAY[] · · 511 512 # Test that searching for a NULL element using the inverted index. 513 query TTTTT 514 EXPLAIN (VERBOSE) SELECT * from e where b @> ARRAY[NULL]::INT[] 515 ---- 516 · distributed false · · 517 · vectorized true · · 518 index-join · · (a, b) · 519 │ table e@primary · · 520 │ key columns a · · 521 └── norows · · (a) · 522 523 query TTTTT 524 EXPLAIN (VERBOSE) SELECT * from e where b @> NULL 525 ---- 526 · distributed false · · 527 · vectorized true · · 528 norows · · (a, b) · 529 530 query TTTTT 531 EXPLAIN (VERBOSE) SELECT * from e where b IS NULL 532 ---- 533 · distributed false · · 534 · vectorized true · · 535 scan · · (a, b) · 536 · table e@primary · · 537 · spans FULL SCAN · · 538 · filter b IS NULL · · 539 540 query TTTTT 541 EXPLAIN (VERBOSE) SELECT * from e where b @> ARRAY[1,2] 542 ---- 543 · distributed false · · 544 · vectorized true · · 545 lookup-join · · (a, b) · 546 │ table e@primary · · 547 │ type inner · · 548 │ equality (a) = (a) · · 549 │ equality cols are key · · · 550 │ parallel · · · 551 │ pred @2 @> ARRAY[1,2] · · 552 └── zigzag-join · · (a) · 553 │ type inner · · 554 ├── scan · · (a) · 555 │ table e@e_b_idx · · 556 │ fixedvals 1 column · · 557 └── scan · · () · 558 · table e@e_b_idx · · 559 · fixedvals 1 column · · 560 561 query TTTTT 562 EXPLAIN (VERBOSE) SELECT * from e where b @> ARRAY[1] AND b @> ARRAY[2] 563 ---- 564 · distributed false · · 565 · vectorized true · · 566 lookup-join · · (a, b) · 567 │ table e@primary · · 568 │ type inner · · 569 │ equality (a) = (a) · · 570 │ equality cols are key · · · 571 │ parallel · · · 572 │ pred (@2 @> ARRAY[1]) AND (@2 @> ARRAY[2]) · · 573 └── zigzag-join · · (a) · 574 │ type inner · · 575 ├── scan · · (a) · 576 │ table e@e_b_idx · · 577 │ fixedvals 1 column · · 578 └── scan · · () · 579 · table e@e_b_idx · · 580 · fixedvals 1 column · · 581 582 # Ensure that an inverted index with a composite primary key still encodes 583 # the primary key data in the composite value. 584 statement ok 585 DROP TABLE IF EXISTS t; 586 CREATE TABLE t (x DECIMAL PRIMARY KEY, y int[], FAMILY (x, y)); 587 CREATE INVERTED INDEX ON t(y) 588 589 query T kvtrace 590 INSERT INTO t VALUES (1.00, ARRAY[1,2]) 591 ---- 592 CPut /Table/56/1/1/0 -> /TUPLE/1:1:Decimal/1.00/ 593 InitPut /Table/56/2/1/1/0 -> /BYTES/0x1503348964 594 InitPut /Table/56/2/2/1/0 -> /BYTES/0x1503348964