github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/inverted_index (about) 1 # LogicTest: !3node-tenant 2 statement ok 3 CREATE TABLE t ( 4 a INT PRIMARY KEY, 5 b INT, 6 c INT, 7 FAMILY (a), 8 FAMILY (b) 9 ) 10 11 statement ok 12 INSERT INTO t VALUES (1,1,1) 13 14 statement ok 15 CREATE INDEX foo ON t (b) 16 17 statement error column b is of type int and thus is not indexable with an inverted index.*\nHINT.*\n.*35730 18 CREATE INVERTED INDEX foo_inv ON t(b) 19 20 statement error column b is of type int and thus is not indexable with an inverted index.*\nHINT.*\n.*35730 21 CREATE INDEX foo_inv2 ON t USING GIN (b) 22 23 statement error pq: inverted indexes can't be unique 24 CREATE UNIQUE INVERTED INDEX foo_inv ON t(b) 25 26 statement ok 27 CREATE TABLE c ( 28 id INT PRIMARY KEY, 29 foo JSON, 30 "bAr" JSON, 31 "qUuX" JSON, 32 INVERTED INDEX (foo), 33 INVERTED INDEX ("bAr"), 34 FAMILY "primary" (id, foo, "bAr", "qUuX") 35 ) 36 37 query TT 38 SHOW CREATE TABLE c 39 ---- 40 c CREATE TABLE c ( 41 id INT8 NOT NULL, 42 foo JSONB NULL, 43 "bAr" JSONB NULL, 44 "qUuX" JSONB NULL, 45 CONSTRAINT "primary" PRIMARY KEY (id ASC), 46 INVERTED INDEX c_foo_idx (foo), 47 INVERTED INDEX "c_bAr_idx" ("bAr"), 48 FAMILY "primary" (id, foo, "bAr", "qUuX") 49 ) 50 51 # Regression test for #42944: make sure that mixed-case columns can be 52 # inverted indexed. 53 statement ok 54 CREATE INVERTED INDEX ON c("qUuX") 55 56 statement error indexing more than one column with an inverted index is not supported 57 CREATE TABLE d ( 58 id INT PRIMARY KEY, 59 foo JSONB, 60 bar JSONB, 61 INVERTED INDEX (foo, bar) 62 ) 63 64 statement error column foo is of type int and thus is not indexable with an inverted index.*\nHINT.*\n.*35730 65 CREATE TABLE d ( 66 id INT PRIMARY KEY, 67 foo INT, 68 INVERTED INDEX (foo) 69 ) 70 71 statement ok 72 CREATE TABLE t1 (id1 INT PRIMARY KEY, id2 INT, id3 INT); 73 74 statement error pq: inverted indexes don't support interleaved tables 75 CREATE INDEX c on t1 USING GIN (id2) 76 STORING (id1,id3) 77 INTERLEAVE in PARENT t1 (id2); 78 79 statement error pq: inverted indexes don't support stored columns 80 CREATE INDEX c on t1 USING GIN (id2) STORING (id1,id3); 81 82 statement error pq: inverted indexes don't support stored columns 83 CREATE INVERTED INDEX c on t1 (id2) STORING (id1,id3); 84 85 statement error pq: inverted indexes can't be unique 86 CREATE UNIQUE INDEX foo_inv2 ON t USING GIN (b) 87 88 statement ok 89 CREATE TABLE d ( 90 a INT PRIMARY KEY, 91 b JSONB 92 ) 93 94 statement ok 95 CREATE INVERTED INDEX foo_inv ON d(b) 96 97 statement ok 98 SHOW INDEX FROM d 99 100 statement ok 101 INSERT INTO d VALUES(1, '{"a": "b"}') 102 103 statement ok 104 INSERT INTO d VALUES(2, '[1,2,3,4, "foo"]') 105 106 statement ok 107 INSERT INTO d VALUES(3, '{"a": {"b": "c"}}') 108 109 statement ok 110 INSERT INTO d VALUES(4, '{"a": {"b": [1]}}') 111 112 statement ok 113 INSERT INTO d VALUES(5, '{"a": {"b": [1, [2]]}}') 114 115 statement ok 116 INSERT INTO d VALUES(6, '{"a": {"b": [[2]]}}') 117 118 statement ok 119 INSERT INTO d VALUES(7, '{"a": "b", "c": "d"}') 120 121 statement ok 122 INSERT INTO d VALUES(8, '{"a": {"b":true}}') 123 124 statement ok 125 INSERT INTO d VALUES(9, '{"a": {"b":false}}') 126 127 statement ok 128 INSERT INTO d VALUES(10, '"a"') 129 130 statement ok 131 INSERT INTO d VALUES(11, 'null') 132 133 statement ok 134 INSERT INTO d VALUES(12, 'true') 135 136 statement ok 137 INSERT INTO d VALUES(13, 'false') 138 139 statement ok 140 INSERT INTO d VALUES(14, '1') 141 142 statement ok 143 INSERT INTO d VALUES(15, '1.23') 144 145 statement ok 146 INSERT INTO d VALUES(16, '[{"a": {"b": [1, [2]]}}, "d"]') 147 148 statement ok 149 INSERT INTO d VALUES(17, '{}') 150 151 statement ok 152 INSERT INTO d VALUES(18, '[]') 153 154 statement ok 155 INSERT INTO d VALUES (29, NULL) 156 157 statement ok 158 INSERT INTO d VALUES (30, '{"a": []}') 159 160 statement ok 161 INSERT INTO d VALUES (31, '{"a": {"b": "c", "d": "e"}, "f": "g"}') 162 163 query IT 164 SELECT * from d where b @> NULL ORDER BY a; 165 ---- 166 167 query IT 168 SELECT * from d where b @> (NULL::JSONB) ORDER BY a; 169 ---- 170 171 query IT 172 SELECT * from d where b @>'{"a": "b"}' ORDER BY a; 173 ---- 174 1 {"a": "b"} 175 7 {"a": "b", "c": "d"} 176 177 query IT 178 SELECT * from d where b @> '{"a": {"b": [1]}}' ORDER BY a; 179 ---- 180 4 {"a": {"b": [1]}} 181 5 {"a": {"b": [1, [2]]}} 182 183 query IT 184 SELECT * from d where b @> '{"a": {"b": [[2]]}}' ORDER BY a; 185 ---- 186 5 {"a": {"b": [1, [2]]}} 187 6 {"a": {"b": [[2]]}} 188 189 query IT 190 SELECT * from d where b @> '{"a": {"b": true}}' ORDER BY a; 191 ---- 192 8 {"a": {"b": true}} 193 194 query IT 195 SELECT * from d where b @> '{"a": {"b": [[2]]}}' ORDER BY a; 196 ---- 197 5 {"a": {"b": [1, [2]]}} 198 6 {"a": {"b": [[2]]}} 199 200 query IT 201 SELECT * from d where b @>'[1]' ORDER BY a; 202 ---- 203 2 [1, 2, 3, 4, "foo"] 204 205 query IT 206 SELECT * from d where b @>'[{"a": {"b": [1]}}]' ORDER BY a; 207 ---- 208 16 [{"a": {"b": [1, [2]]}}, "d"] 209 210 statement ok 211 DELETE from d WHERE a=1; 212 213 query IT 214 SELECT * from d where b @>'{"a": "b"}' ORDER BY a; 215 ---- 216 7 {"a": "b", "c": "d"} 217 218 statement ok 219 PREPARE query (STRING, STRING) AS SELECT * from d where b->$1 = $2 ORDER BY a 220 221 query IT 222 EXECUTE query ('a', '"b"') 223 ---- 224 7 {"a": "b", "c": "d"} 225 226 statement ok 227 DELETE from d WHERE a=6; 228 229 query IT 230 SELECT * from d where b @> '{"a": {"b": [[2]]}}' ORDER BY a; 231 ---- 232 5 {"a": {"b": [1, [2]]}} 233 234 query IT 235 SELECT * from d where b @> '"a"' ORDER BY a; 236 ---- 237 10 "a" 238 239 query IT 240 SELECT * from d where b @> 'null' ORDER BY a; 241 ---- 242 11 null 243 244 query IT 245 SELECT * from d where b @> 'true' ORDER BY a; 246 ---- 247 12 true 248 249 query IT 250 SELECT * from d where b @> 'false' ORDER BY a; 251 ---- 252 13 false 253 254 query IT 255 SELECT * from d where b @> '1' ORDER BY a; 256 ---- 257 2 [1, 2, 3, 4, "foo"] 258 14 1 259 260 query IT 261 SELECT * from d where b @> '1.23' ORDER BY a; 262 ---- 263 15 1.23 264 265 query IT 266 SELECT * from d where b @> '{}' ORDER BY a; 267 ---- 268 3 {"a": {"b": "c"}} 269 4 {"a": {"b": [1]}} 270 5 {"a": {"b": [1, [2]]}} 271 7 {"a": "b", "c": "d"} 272 8 {"a": {"b": true}} 273 9 {"a": {"b": false}} 274 17 {} 275 30 {"a": []} 276 31 {"a": {"b": "c", "d": "e"}, "f": "g"} 277 278 query IT 279 SELECT * from d where b @> '[]' ORDER BY a; 280 ---- 281 2 [1, 2, 3, 4, "foo"] 282 16 [{"a": {"b": [1, [2]]}}, "d"] 283 18 [] 284 285 statement ok 286 INSERT INTO d VALUES (19, '["a", "a"]') 287 288 query IT 289 SELECT * from d where b @> '["a"]' ORDER BY a; 290 ---- 291 19 ["a", "a"] 292 293 statement ok 294 INSERT INTO d VALUES (20, '[{"a": "a"}, {"a": "a"}]') 295 296 query IT 297 SELECT * from d where b @> '[{"a": "a"}]' ORDER BY a; 298 ---- 299 20 [{"a": "a"}, {"a": "a"}] 300 301 statement ok 302 INSERT INTO d VALUES (21, '[[[["a"]]], [[["a"]]]]') 303 304 query IT 305 SELECT * from d where b @> '[[[["a"]]]]' ORDER BY a; 306 ---- 307 21 [[[["a"]]], [[["a"]]]] 308 309 statement ok 310 INSERT INTO d VALUES (22, '[1,2,3,1]') 311 312 query IT 313 SELECT * from d where b @> '[[[["a"]]]]' ORDER BY a; 314 ---- 315 21 [[[["a"]]], [[["a"]]]] 316 317 query IT 318 SELECT * from d where b->'a' = '"b"' 319 ---- 320 7 {"a": "b", "c": "d"} 321 322 statement ok 323 INSERT INTO d VALUES (23, '{"a": 123.123}') 324 325 statement ok 326 INSERT INTO d VALUES (24, '{"a": 123.123000}') 327 328 query IT 329 SELECT * from d where b @> '{"a": 123.123}' ORDER BY a; 330 ---- 331 23 {"a": 123.123} 332 24 {"a": 123.123000} 333 334 query IT 335 SELECT * from d where b @> '{"a": 123.123000}' ORDER BY a; 336 ---- 337 23 {"a": 123.123} 338 24 {"a": 123.123000} 339 340 statement ok 341 INSERT INTO d VALUES (25, '{"a": [{}]}') 342 343 statement ok 344 INSERT INTO d VALUES (26, '[[], {}]') 345 346 query IT 347 SELECT * from d where b @> '{"a": [{}]}' ORDER BY a; 348 ---- 349 25 {"a": [{}]} 350 351 352 query IT 353 SELECT * from d where b @> '{"a": []}' ORDER BY a; 354 ---- 355 25 {"a": [{}]} 356 30 {"a": []} 357 358 query IT 359 SELECT * from d where b @> '[{}]' ORDER BY a; 360 ---- 361 16 [{"a": {"b": [1, [2]]}}, "d"] 362 20 [{"a": "a"}, {"a": "a"}] 363 26 [[], {}] 364 365 query IT 366 SELECT * from d where b @> '[[]]' ORDER BY a; 367 ---- 368 21 [[[["a"]]], [[["a"]]]] 369 26 [[], {}] 370 371 statement ok 372 INSERT INTO d VALUES (27, '[true, false, null, 1.23, "a"]') 373 374 query IT 375 SELECT * from d where b @> 'true' ORDER BY a; 376 ---- 377 12 true 378 27 [true, false, null, 1.23, "a"] 379 380 query IT 381 SELECT * from d where b @> 'false' ORDER BY a; 382 ---- 383 13 false 384 27 [true, false, null, 1.23, "a"] 385 386 query IT 387 SELECT * from d where b @> '1.23' ORDER BY a; 388 ---- 389 15 1.23 390 27 [true, false, null, 1.23, "a"] 391 392 query IT 393 SELECT * from d where b @> '"a"' ORDER BY a; 394 ---- 395 10 "a" 396 19 ["a", "a"] 397 27 [true, false, null, 1.23, "a"] 398 399 query IT 400 SELECT * from d where b IS NULL; 401 ---- 402 29 NULL 403 404 query IT 405 SELECT * from d where b = NULL; 406 ---- 407 408 query IT 409 SELECT * from d where b @> NULL; 410 ---- 411 412 query IT 413 SELECT * from d where b @> 'null' ORDER BY a; 414 ---- 415 11 null 416 27 [true, false, null, 1.23, "a"] 417 418 query IT 419 SELECT * from d where b @> '{"a": {}}' ORDER BY a; 420 ---- 421 3 {"a": {"b": "c"}} 422 4 {"a": {"b": [1]}} 423 5 {"a": {"b": [1, [2]]}} 424 8 {"a": {"b": true}} 425 9 {"a": {"b": false}} 426 31 {"a": {"b": "c", "d": "e"}, "f": "g"} 427 428 query IT 429 SELECT * from d where b @> '{"a": []}' ORDER BY a; 430 ---- 431 25 {"a": [{}]} 432 30 {"a": []} 433 434 ## Multi-path contains queries 435 436 query IT 437 SELECT * from d where b @> '{"a": {"b": "c"}, "f": "g"}' 438 ---- 439 31 {"a": {"b": "c", "d": "e"}, "f": "g"} 440 441 query IT 442 SELECT * from d where b @> '{"a": {"b": "c", "d": "e"}, "f": "g"}' 443 ---- 444 31 {"a": {"b": "c", "d": "e"}, "f": "g"} 445 446 query IT 447 SELECT * from d where b @> '{"c": "d", "a": "b"}' 448 ---- 449 7 {"a": "b", "c": "d"} 450 451 query IT 452 SELECT * from d where b @> '{"c": "d", "a": "b", "f": "g"}' 453 ---- 454 455 query IT 456 SELECT * from d where b @> '{"a": "b", "c": "e"}' 457 ---- 458 459 query IT 460 SELECT * from d where b @> '{"a": "e", "c": "d"}' 461 ---- 462 463 query IT 464 SELECT * from d where b @> '["d", {"a": {"b": [1]}}]' 465 ---- 466 16 [{"a": {"b": [1, [2]]}}, "d"] 467 468 query IT 469 SELECT * from d where b @> '["d", {"a": {"b": [[2]]}}]' 470 ---- 471 16 [{"a": {"b": [1, [2]]}}, "d"] 472 473 query IT 474 SELECT * from d where b @> '[{"a": {"b": [[2]]}}, "d"]' 475 ---- 476 16 [{"a": {"b": [1, [2]]}}, "d"] 477 478 479 statement ok 480 CREATE TABLE users ( 481 profile_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), 482 last_updated TIMESTAMP DEFAULT now(), 483 user_profile JSONB 484 ); 485 486 statement ok 487 INSERT INTO users (user_profile) VALUES ('{"first_name": "Lola", "last_name": "Dog", "location": "NYC", "online" : true, "friends" : 547}'), 488 ('{"first_name": "Ernie", "status": "Looking for treats", "location" : "Brooklyn"}'); 489 490 statement ok 491 CREATE INVERTED INDEX dogs on users(user_profile); 492 493 statement error index "dogs" is inverted and cannot be used for this query 494 SELECT count(*) FROM users@dogs 495 496 query T 497 SELECT user_profile from users where user_profile @> '{"first_name":"Lola"}'; 498 ---- 499 {"first_name": "Lola", "friends": 547, "last_name": "Dog", "location": "NYC", "online": true} 500 501 query T 502 SELECT user_profile from users where user_profile @> '{"first_name":"Ernie"}'; 503 ---- 504 {"first_name": "Ernie", "location": "Brooklyn", "status": "Looking for treats"} 505 506 statement ok 507 CREATE TABLE update_test (i INT PRIMARY KEY, j JSONB, INVERTED INDEX(j)); 508 509 statement ok 510 INSERT INTO update_test VALUES (1, '0'); 511 512 query IT 513 SELECT * from update_test WHERE j @> '0'; 514 ---- 515 1 0 516 517 statement ok 518 UPDATE update_test SET j = '{"a":"b", "c":"d"}' WHERE i = 1; 519 520 query IT 521 SELECT * from update_test WHERE j @> '0'; 522 ---- 523 524 query IT 525 SELECT * from update_test WHERE j @> '{"a":"b"}'; 526 ---- 527 1 {"a": "b", "c": "d"} 528 529 statement ok 530 INSERT INTO update_test VALUES (2, '{"longKey1":"longValue1", "longKey2":"longValue2"}'); 531 532 statement ok 533 UPDATE update_test SET j = ('"shortValue"') WHERE i = 2; 534 535 query IT 536 SELECT * from update_test where j @> '"shortValue"'; 537 ---- 538 2 "shortValue" 539 540 query IT 541 SELECT * from update_test where j @> '{"longKey1":"longValue1"}}'; 542 ---- 543 544 query IT 545 SELECT * from update_test where j @> '{"longKey2":"longValue2"}}'; 546 ---- 547 548 statement ok 549 UPDATE update_test SET (i, j) = (10, '{"longKey1":"longValue1", "longKey2":"longValue2"}') WHERE i = 2; 550 551 statement ok 552 UPDATE update_test SET j = '{"a":"b", "a":"b"}' WHERE i = 1; 553 554 statement ok 555 UPDATE update_test SET (i, j) = (2, '["a", "a"]') WHERE i = 10; 556 557 statement ok 558 INSERT INTO update_test VALUES (3, '["a", "b", "c"]'); 559 560 query IT 561 SELECT * from update_test where j @> '["a"]' ORDER BY i; 562 ---- 563 2 ["a", "a"] 564 3 ["a", "b", "c"] 565 566 statement ok 567 UPDATE update_test SET j = '["b", "c", "e"]' WHERE i = 3; 568 569 query IT 570 SELECT * from update_test where j @> '["a"]' ORDER BY i; 571 ---- 572 2 ["a", "a"] 573 574 query IT 575 SELECT * from update_test where j @> '["b"]' ORDER BY i; 576 ---- 577 3 ["b", "c", "e"] 578 579 580 statement ok 581 INSERT INTO update_test VALUES (4, '["a", "b"]'); 582 583 statement ok 584 UPDATE update_test SET j = '["b", "a"]' WHERE i = 4; 585 586 query IT 587 SELECT * from update_test where j @> '["a"]' ORDER BY i; 588 ---- 589 2 ["a", "a"] 590 4 ["b", "a"] 591 592 query IT 593 SELECT * from update_test where j @> '["b"]' ORDER BY i; 594 ---- 595 3 ["b", "c", "e"] 596 4 ["b", "a"] 597 598 statement ok 599 UPSERT INTO update_test VALUES (4, '["a", "b"]'); 600 601 query IT 602 SELECT * from update_test where j @> '["a"]' ORDER BY i; 603 ---- 604 2 ["a", "a"] 605 4 ["a", "b"] 606 607 query IT 608 SELECT * from update_test where j @> '["b"]' ORDER BY i; 609 ---- 610 3 ["b", "c", "e"] 611 4 ["a", "b"] 612 613 614 statement ok 615 UPSERT INTO update_test VALUES (3, '["c", "e", "f"]'); 616 617 query IT 618 SELECT * from update_test where j @> '["c"]' ORDER BY i; 619 ---- 620 3 ["c", "e", "f"] 621 622 statement ok 623 CREATE TABLE del_cascade_test ( 624 delete_cascade INT NOT NULL REFERENCES update_test ON DELETE CASCADE 625 ,j JSONB 626 ,INVERTED INDEX(j) 627 ); 628 629 630 statement ok 631 CREATE TABLE update_cascade_test ( 632 update_cascade INT NOT NULL REFERENCES update_test ON UPDATE CASCADE 633 ,j JSONB 634 ,INVERTED INDEX(j) 635 ); 636 637 statement ok 638 INSERT INTO del_cascade_test(delete_cascade, j) VALUES (1, '["a", "b"]'), (2, '{"a":"b", "c":"d"}'), (3, '["b", "c"]') 639 640 641 query IT 642 SELECT * from del_cascade_test ORDER BY delete_cascade; 643 ---- 644 1 ["a", "b"] 645 2 {"a": "b", "c": "d"} 646 3 ["b", "c"] 647 648 statement ok 649 DELETE FROM update_test where j @> '["c"]' 650 651 query IT 652 SELECT * from del_cascade_test ORDER BY delete_cascade; 653 ---- 654 1 ["a", "b"] 655 2 {"a": "b", "c": "d"} 656 657 query IT 658 SELECT * from del_cascade_test ORDER BY delete_cascade; 659 ---- 660 1 ["a", "b"] 661 2 {"a": "b", "c": "d"} 662 663 statement ok 664 INSERT INTO update_test VALUES (3, '["a", "b", "c"]'); 665 666 statement ok 667 INSERT INTO update_cascade_test(update_cascade, j) VALUES (1, '["a", "b"]'), (2, '{"a":"b", "c":"d"}'), (3, '["b", "c"]') 668 669 query IT 670 SELECT * from update_cascade_test ORDER BY update_cascade; 671 ---- 672 1 ["a", "b"] 673 2 {"a": "b", "c": "d"} 674 3 ["b", "c"] 675 676 statement error pq: update on table "update_test" violates foreign key constraint "fk_delete_cascade_ref_update_test" on table "del_cascade_test"\nDETAIL: Key \(i\)=\(1\) is still referenced from table "del_cascade_test"\. 677 UPDATE update_test SET (i,j) = (5, '{"a":"b", "a":"b"}') WHERE i = 1; 678 679 statement ok 680 DROP TABLE del_cascade_test 681 682 statement ok 683 UPDATE update_test SET (i,j) = (5, '{"a":"b", "a":"b"}') WHERE i = 1; 684 685 query IT 686 SELECT * from update_cascade_test ORDER BY update_cascade; 687 ---- 688 2 {"a": "b", "c": "d"} 689 3 ["b", "c"] 690 5 ["a", "b"] 691 692 # Test that inverted index validation correctly handles NULL values on creation (#38714) 693 694 statement ok 695 CREATE TABLE table_with_nulls (a JSON) 696 697 statement ok 698 INSERT INTO table_with_nulls VALUES (NULL) 699 700 statement ok 701 CREATE INVERTED INDEX ON table_with_nulls (a) 702 703 statement ok 704 DROP TABLE table_with_nulls 705 706 statement ok 707 DROP TABLE c 708 709 subtest arrays 710 711 statement ok 712 CREATE TABLE c ( 713 id INT PRIMARY KEY, 714 foo INT[], 715 bar STRING[], 716 INVERTED INDEX (foo), 717 FAMILY "primary" (id, foo, bar) 718 ) 719 720 statement ok 721 INSERT INTO c VALUES(0, NULL, NULL) 722 723 statement ok 724 INSERT INTO c VALUES(1, ARRAY[], ARRAY['foo', 'bar', 'baz']) 725 726 statement ok 727 CREATE INDEX ON c USING GIN (bar) 728 729 query TT 730 SHOW CREATE TABLE c 731 ---- 732 c CREATE TABLE c ( 733 id INT8 NOT NULL, 734 foo INT8[] NULL, 735 bar STRING[] NULL, 736 CONSTRAINT "primary" PRIMARY KEY (id ASC), 737 INVERTED INDEX c_foo_idx (foo), 738 INVERTED INDEX c_bar_idx (bar), 739 FAMILY "primary" (id, foo, bar) 740 ) 741 742 query ITT 743 SELECT * from c WHERE bar @> ARRAY['foo'] 744 ---- 745 1 {} {foo,bar,baz} 746 747 query ITT 748 SELECT * from c WHERE bar @> ARRAY['bar', 'baz'] 749 ---- 750 1 {} {foo,bar,baz} 751 752 query ITT 753 SELECT * from c WHERE bar @> ARRAY['bar', 'qux'] 754 ---- 755 756 statement ok 757 INSERT INTO c VALUES(2, NULL, NULL) 758 759 statement ok 760 INSERT INTO c VALUES(3, ARRAY[0,1,NULL], ARRAY['a',NULL,'b',NULL]) 761 762 statement ok 763 INSERT INTO c VALUES(4, ARRAY[1,2,3], ARRAY['b',NULL,'c']) 764 765 statement ok 766 INSERT INTO c VALUES(5, ARRAY[], ARRAY[NULL, NULL]) 767 768 # Create a second inverted index on c, to test backfills. 769 statement ok 770 CREATE INVERTED INDEX ON c(foo) 771 772 statement ok 773 CREATE INVERTED INDEX ON c(bar) 774 775 query ITT 776 SELECT * FROM c WHERE foo @> ARRAY[0] 777 ---- 778 3 {0,1,NULL} {a,NULL,b,NULL} 779 780 query error unsupported comparison operator 781 SELECT * FROM c WHERE foo @> 0 782 783 query ITT 784 SELECT * FROM c WHERE foo @> ARRAY[1] ORDER BY id 785 ---- 786 3 {0,1,NULL} {a,NULL,b,NULL} 787 4 {1,2,3} {b,NULL,c} 788 789 # This is expected, although it looks odd, because in SQL, 790 # ARRAY[NULL] @> ARRAY[NULL] returns false. 791 query ITT 792 SELECT * FROM c WHERE foo @> ARRAY[NULL]::INT[] 793 ---- 794 795 query ITT 796 SELECT * FROM c WHERE bar @> ARRAY['a'] 797 ---- 798 3 {0,1,NULL} {a,NULL,b,NULL} 799 800 query ITT 801 SELECT * FROM c WHERE bar @> ARRAY['b'] ORDER BY id 802 ---- 803 3 {0,1,NULL} {a,NULL,b,NULL} 804 4 {1,2,3} {b,NULL,c} 805 806 query ITT 807 SELECT * FROM c WHERE bar @> ARRAY['c'] 808 ---- 809 4 {1,2,3} {b,NULL,c} 810 811 query ITT 812 SELECT * FROM c WHERE bar @> ARRAY[]::TEXT[] ORDER BY id 813 ---- 814 1 {} {foo,bar,baz} 815 3 {0,1,NULL} {a,NULL,b,NULL} 816 4 {1,2,3} {b,NULL,c} 817 5 {} {NULL,NULL}