github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/select_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 INDEX b_desc (b DESC), 8 INDEX bc (b, c) 9 ) 10 11 statement ok 12 INSERT INTO t VALUES (1, 2, 3), (3, 4, 5), (5, 6, 7) 13 14 query I rowsort 15 SELECT a FROM t WHERE a < 4.0 16 ---- 17 1 18 3 19 20 query I 21 SELECT b FROM t WHERE c > 4.0 AND a < 4 22 ---- 23 4 24 25 statement ok 26 CREATE TABLE ab ( 27 s STRING, 28 i INT 29 ); INSERT INTO ab VALUES ('a', 1), ('b', 1), ('c', 1) 30 31 query IT rowsort 32 SELECT i, s FROM ab WHERE (i, s) < (1, 'c') 33 ---- 34 1 a 35 1 b 36 37 statement ok 38 CREATE INDEX baz ON ab (i, s) 39 40 query IT rowsort 41 SELECT i, s FROM ab@baz WHERE (i, s) < (1, 'c') 42 ---- 43 1 a 44 1 b 45 46 # Issue #14426: verify we don't have an internal filter that contains "a IN ()" 47 # (which causes an error in DistSQL due to expression serialization). 48 statement ok 49 CREATE TABLE tab0( 50 k INT PRIMARY KEY, 51 a INT, 52 b INT 53 ) 54 55 query I 56 SELECT k FROM tab0 WHERE (a IN (6) AND a > 6) OR b >= 4 57 ---- 58 59 # Regression tests for #12022 60 61 statement ok 62 CREATE TABLE t12022 ( 63 c1 INT, 64 c2 BOOL, 65 UNIQUE INDEX i (c1, c2) 66 ); 67 68 statement ok 69 INSERT INTO t12022 VALUES 70 (1, NULL), (1, false), (1, true), 71 (2, NULL), (2, false), (2, true); 72 73 query IB 74 SELECT * FROM t12022@i WHERE (c1, c2) > (1, NULL) ORDER BY (c1, c2); 75 ---- 76 2 NULL 77 2 false 78 2 true 79 80 query IB 81 SELECT * FROM t12022@i WHERE (c1, c2) > (1, false) ORDER BY (c1, c2); 82 ---- 83 1 true 84 2 NULL 85 2 false 86 2 true 87 88 query IB 89 SELECT * FROM t12022@i WHERE (c1, c2) > (1, true) ORDER BY (c1, c2); 90 ---- 91 2 NULL 92 2 false 93 2 true 94 95 query IB 96 SELECT * FROM t12022@i WHERE (c1, c2) < (2, NULL) ORDER BY (c1, c2); 97 ---- 98 1 NULL 99 1 false 100 1 true 101 102 query IB 103 SELECT * FROM t12022@i WHERE (c1, c2) < (2, false) ORDER BY (c1, c2); 104 ---- 105 1 NULL 106 1 false 107 1 true 108 109 query IB 110 SELECT * FROM t12022@i WHERE (c1, c2) < (2, true) ORDER BY (c1, c2); 111 ---- 112 1 NULL 113 1 false 114 1 true 115 2 false 116 117 118 # Regression test for #20035. 119 statement ok 120 CREATE TABLE favorites ( 121 id INT NOT NULL DEFAULT unique_rowid(), 122 resource_type STRING(30) NOT NULL, 123 resource_key STRING(255) NOT NULL, 124 device_group STRING(30) NOT NULL, 125 customerid INT NOT NULL, 126 jurisdiction STRING(2) NOT NULL, 127 brand STRING(255) NOT NULL, 128 created_ts TIMESTAMP NULL, 129 guid_id STRING(100) NOT NULL, 130 locale STRING(10) NOT NULL DEFAULT NULL, 131 CONSTRAINT "primary" PRIMARY KEY (id ASC), 132 UNIQUE INDEX favorites_idx (resource_type ASC, device_group ASC, resource_key ASC, customerid ASC), 133 INDEX favorites_guid_idx (guid_id ASC), 134 INDEX favorites_glob_fav_idx (resource_type ASC, device_group ASC, jurisdiction ASC, brand ASC, locale ASC, resource_key ASC), 135 FAMILY "primary" (id, resource_type, resource_key, device_group, customerid, jurisdiction, brand, created_ts, guid_id, locale) 136 ) 137 138 statement ok 139 INSERT INTO favorites (customerid, guid_id, resource_type, device_group, jurisdiction, brand, locale, resource_key) 140 VALUES (1, '1', 'GAME', 'web', 'MT', 'xxx', 'en_GB', 'tp'), 141 (2, '2', 'GAME', 'web', 'MT', 'xxx', 'en_GB', 'ts'), 142 (3, '3', 'GAME', 'web', 'MT', 'xxx', 'en_GB', 'ts1'), 143 (4, '4', 'GAME', 'web', 'MT', 'xxx', 'en_GB', 'ts2'), 144 (5, '5', 'GAME', 'web', 'MT', 'xxx', 'en_GB', 'ts3'), 145 (6, '6', 'GAME', 'web', 'MT', 'xxx', 'en_GB', 'ts4') 146 147 query TI rowsort 148 SELECT 149 resource_key, 150 count(resource_key) total 151 FROM favorites f1 152 WHERE f1.jurisdiction = 'MT' 153 AND f1.brand = 'xxx' 154 AND f1.resource_type = 'GAME' 155 AND f1.device_group = 'web' 156 AND f1.locale = 'en_GB' 157 AND f1.resource_key IN ('ts', 'ts2', 'ts3') 158 GROUP BY resource_key 159 ORDER BY total DESC 160 ---- 161 ts 1 162 ts2 1 163 ts3 1 164 165 statement ok 166 CREATE TABLE abcd ( 167 a INT, 168 b INT, 169 c INT, 170 d INT, 171 INDEX adb (a, d, b), 172 INDEX abcd (a, b, c, d) 173 ) 174 175 # Regression tests for #20362 (IS NULL handling). 176 statement ok 177 INSERT INTO abcd VALUES 178 (NULL, NULL, NULL), 179 (NULL, NULL, 1), 180 (NULL, NULL, 5), 181 (NULL, NULL, 10), 182 (NULL, 1, NULL), 183 (NULL, 1, 1), 184 (NULL, 1, 5), 185 (NULL, 1, 10), 186 (NULL, 5, NULL), 187 (NULL, 5, 1), 188 (NULL, 5, 5), 189 (NULL, 5, 10), 190 (NULL, 10, NULL), 191 (NULL, 10, 1), 192 (NULL, 10, 5), 193 (NULL, 10, 10), 194 (1, NULL, NULL), 195 (1, NULL, 1), 196 (1, NULL, 5), 197 (1, NULL, 10), 198 (1, 1, NULL), 199 (1, 1, 1), 200 (1, 1, 5), 201 (1, 1, 10), 202 (1, 5, NULL), 203 (1, 5, 1), 204 (1, 5, 5), 205 (1, 5, 10), 206 (1, 10, NULL), 207 (1, 10, 1), 208 (1, 10, 5), 209 (1, 10, 10) 210 211 query IIII rowsort 212 SELECT * FROM abcd@abcd WHERE a IS NULL AND b > 5 213 ---- 214 NULL 10 NULL NULL 215 NULL 10 1 NULL 216 NULL 10 5 NULL 217 NULL 10 10 NULL 218 219 query IIII rowsort 220 SELECT * FROM abcd@abcd WHERE a IS NULL AND b < 5 221 ---- 222 NULL 1 NULL NULL 223 NULL 1 1 NULL 224 NULL 1 5 NULL 225 NULL 1 10 NULL 226 227 query IIII partialsort(1,2) 228 SELECT * FROM abcd@abcd WHERE a IS NULL ORDER BY b 229 ---- 230 NULL NULL NULL NULL 231 NULL NULL 1 NULL 232 NULL NULL 5 NULL 233 NULL NULL 10 NULL 234 NULL 1 NULL NULL 235 NULL 1 1 NULL 236 NULL 1 5 NULL 237 NULL 1 10 NULL 238 NULL 5 NULL NULL 239 NULL 5 1 NULL 240 NULL 5 5 NULL 241 NULL 5 10 NULL 242 NULL 10 NULL NULL 243 NULL 10 1 NULL 244 NULL 10 5 NULL 245 NULL 10 10 NULL 246 247 query IIII 248 SELECT * FROM abcd@abcd WHERE a = 1 AND b IS NULL AND c > 0 AND c < 10 ORDER BY c 249 ---- 250 1 NULL 1 NULL 251 1 NULL 5 NULL 252 253 # Regression test for #21831. 254 statement ok 255 CREATE TABLE str (k INT PRIMARY KEY, v STRING, INDEX(v)) 256 257 statement ok 258 INSERT INTO str VALUES (1, 'A'), (4, 'AB'), (2, 'ABC'), (5, 'ABCD'), (3, 'ABCDEZ'), (9, 'ABD') 259 260 query IT rowsort 261 SELECT k, v FROM str WHERE v LIKE 'ABC%' 262 ---- 263 2 ABC 264 5 ABCD 265 3 ABCDEZ 266 267 query IT rowsort 268 SELECT k, v FROM str WHERE v LIKE 'ABC%Z' 269 ---- 270 3 ABCDEZ 271 272 query IT rowsort 273 SELECT k, v FROM str WHERE v SIMILAR TO 'ABC_*' 274 ---- 275 2 ABC 276 5 ABCD 277 3 ABCDEZ 278 279 # Regression tests for #22670. 280 statement ok 281 CREATE TABLE xy (x INT, y INT, INDEX (y)) 282 283 statement ok 284 CREATE INDEX xy_idx ON xy (x, y) 285 286 statement ok 287 INSERT INTO xy VALUES (NULL, NULL), (1, NULL), (NULL, 1), (1, 1) 288 289 query II rowsort 290 SELECT * FROM xy WHERE x IN (NULL, 1, 2) 291 ---- 292 1 NULL 293 1 1 294 295 statement ok 296 CREATE TABLE ef (e INT, f INT, INDEX(f)) 297 298 statement ok 299 INSERT INTO ef VALUES (NULL, 1), (1, 1) 300 301 query I rowsort 302 SELECT e FROM ef WHERE f > 0 AND f < 2 ORDER BY f 303 ---- 304 NULL 305 1 306 307 query II 308 SELECT * FROM xy WHERE (x, y) IN ((NULL, NULL), (1, NULL), (NULL, 1), (1, 1), (1, 2)) 309 ---- 310 1 1 311 312 # Test index constraints for IS (NOT) TRUE/FALSE. 313 statement ok 314 CREATE TABLE bool1 ( 315 a BOOL, 316 INDEX (a) 317 ); 318 INSERT INTO bool1 VALUES (NULL), (TRUE), (FALSE) 319 320 query B 321 SELECT * FROM bool1 WHERE a IS NULL 322 ---- 323 NULL 324 325 query B rowsort 326 SELECT * FROM bool1 WHERE a IS NOT NULL 327 ---- 328 false 329 true 330 331 query B 332 SELECT * FROM bool1 WHERE a IS TRUE 333 ---- 334 true 335 336 query B rowsort 337 SELECT * FROM bool1 WHERE a IS NOT TRUE 338 ---- 339 NULL 340 false 341 342 query B 343 SELECT * FROM bool1 WHERE a IS FALSE 344 ---- 345 false 346 347 query B rowsort 348 SELECT * FROM bool1 WHERE a IS NOT FALSE 349 ---- 350 NULL 351 true 352 353 statement ok 354 CREATE TABLE bool2 ( 355 a BOOL NOT NULL, 356 INDEX (a) 357 ); 358 INSERT INTO bool2 VALUES (TRUE), (FALSE) 359 360 query B 361 SELECT * FROM bool2 WHERE a IS NULL 362 ---- 363 364 query B rowsort 365 SELECT * FROM bool2 WHERE a IS NOT NULL 366 ---- 367 false 368 true 369 370 query B 371 SELECT * FROM bool2 WHERE a IS TRUE 372 ---- 373 true 374 375 query B 376 SELECT * FROM bool2 WHERE a IS NOT TRUE 377 ---- 378 false 379 380 query B 381 SELECT * FROM bool2 WHERE a IS FALSE 382 ---- 383 false 384 385 query B 386 SELECT * FROM bool2 WHERE a IS NOT FALSE 387 ---- 388 true 389 390 # Test index constraints for IS (NOT) DISTINCT FROM on an integer column. 391 statement ok 392 CREATE TABLE int ( 393 a INT, 394 INDEX (a) 395 ); 396 INSERT INTO int VALUES (NULL), (0), (1), (2) 397 398 query I 399 SELECT * FROM int WHERE a IS NOT DISTINCT FROM 2 400 ---- 401 2 402 403 query I rowsort 404 SELECT * FROM int WHERE a IS DISTINCT FROM 2 405 ---- 406 NULL 407 0 408 1 409 410 # ------------------------------------------------------------------------------ 411 # Non-covering index 412 # ------------------------------------------------------------------------------ 413 statement ok 414 CREATE TABLE noncover ( 415 a INT PRIMARY KEY, 416 b INT, 417 c INT, 418 d INT, 419 INDEX b (b), 420 UNIQUE INDEX c (c), 421 FAMILY (a), 422 FAMILY (b), 423 FAMILY (c), 424 FAMILY (d) 425 ) 426 427 statement ok 428 INSERT INTO noncover VALUES (1, 2, 3, 4), (5, 6, 7, 8) 429 430 query IIII 431 SELECT * FROM noncover WHERE b = 2 432 ---- 433 1 2 3 4 434 435 query IIII 436 SET tracing=on, kv; SELECT * FROM noncover WHERE b = 2; SET tracing=off 437 ---- 438 1 2 3 4 439 440 # Verify that the index join span created doesn't include any potential child 441 # interleaved tables. We look only for spans with the primary prefix to avoid 442 # inconsistency between the fakedist and local test configurations. 443 444 query T rowsort 445 SELECT message FROM [SHOW KV TRACE FOR SESSION] 446 WHERE message LIKE 'Scan /Table/65/1%' 447 ---- 448 Scan /Table/65/1/1{-/#} 449 450 # Subset of output columns, not including tested column. 451 query II 452 SELECT a, d FROM noncover WHERE b=2 453 ---- 454 1 4 455 456 # Subset of output columns, not including tested column or order by column. 457 query I 458 SELECT a FROM noncover WHERE b=2 ORDER BY c DESC 459 ---- 460 1 461 462 # Regression: panic when projecting non-covered column in sorted index join. 463 query III 464 SELECT a, b, d FROM noncover WHERE b=2 ORDER BY b 465 ---- 466 1 2 4 467 468 # Use non-covered column in filtered and sorted index join. 469 query II 470 SELECT a, b FROM noncover WHERE b=2 AND d>3 ORDER BY b 471 ---- 472 1 2 473 474 query IIII 475 SELECT * FROM noncover WHERE c = 7 476 ---- 477 5 6 7 8 478 479 query IIII 480 SELECT * FROM noncover WHERE c > 0 ORDER BY c DESC 481 ---- 482 5 6 7 8 483 1 2 3 4 484 485 query IIII 486 SELECT * FROM noncover WHERE c > 0 AND d = 8 487 ---- 488 5 6 7 8 489 490 # Contradiction 491 query IIII 492 SELECT * FROM noncover WHERE b = 5 AND b <> 5 493 ---- 494 495 # Contradiction with remainder filter 496 query IIII 497 SELECT * FROM noncover WHERE b = 5 AND b <> 5 AND d>100 498 ---- 499 500 # ------------------------------------------------------------------------------ 501 # These tests verify that while we are joining an index with the table, we 502 # evaluate what parts of the filter we can using the columns in the index 503 # to avoid unnecessary lookups in the table. 504 # ------------------------------------------------------------------------------ 505 statement ok 506 CREATE TABLE t2 ( 507 a INT PRIMARY KEY, 508 b INT, 509 c INT, 510 s STRING, 511 INDEX bc (b, c), 512 FAMILY (a), 513 FAMILY (b), 514 FAMILY (c), 515 FAMILY (s) 516 ) 517 518 statement ok 519 INSERT INTO t2 VALUES 520 (1, 1, 1, '11'), 521 (2, 1, 2, '12'), 522 (3, 1, 3, '13'), 523 (4, 2, 1, '21'), 524 (5, 2, 2, '22'), 525 (6, 2, 3, '23'), 526 (7, 3, 1, '31'), 527 (8, 3, 2, '32'), 528 (9, 3, 3, '33') 529 530 query I rowsort 531 SELECT a FROM t2 WHERE b = 2 OR ((b BETWEEN 2 AND 1) AND ((s != 'a') OR (s = 'a'))) 532 ---- 533 4 534 5 535 6 536 537 statement ok 538 CREATE TABLE t3 (k INT PRIMARY KEY, v INT, w INT, INDEX v(v)) 539 540 statement ok 541 INSERT INTO t3 VALUES 542 (10, 50, 1), 543 (30, 40, 2), 544 (50, 30, 3), 545 (70, 20, 4), 546 (90, 10, 5), 547 (110, 0, 6), 548 (130, -10, 7) 549 550 query I 551 SELECT w FROM t3 WHERE v > 0 AND v < 100 ORDER BY v 552 ---- 553 5 554 4 555 3 556 2 557 1 558 559 statement ok 560 CREATE TABLE tab1 ( 561 pk INTEGER NOT NULL, 562 col0 INTEGER NULL, 563 col1 FLOAT NULL, 564 col2 STRING NULL, 565 col3 INTEGER NULL, 566 col4 FLOAT NULL, 567 col5 STRING NULL, 568 CONSTRAINT "primary" PRIMARY KEY (pk ASC), 569 INDEX idx_tab1_0 (col0 ASC), 570 INDEX idx_tab1_1 (col1 ASC), 571 INDEX idx_tab1_3 (col3 ASC), 572 INDEX idx_tab1_4 (col4 ASC), 573 FAMILY "primary" (pk, col0, col1, col2, col3, col4, col5) 574 ) 575 576 statement ok 577 INSERT INTO tab1(pk, col0, col3) VALUES 578 (1, 65, 65), 579 (2, 87, 87), 580 (3, 70, 70), 581 (4, 88, 88), 582 (5, 69, 69), 583 (6, 72, 72), 584 (7, 82, 82) 585 586 query II 587 SELECT pk, col0 FROM tab1 WHERE (col3 BETWEEN 66 AND 87) ORDER BY 1 DESC 588 ---- 589 7 82 590 6 72 591 5 69 592 3 70 593 2 87 594 595 # Use a unique index with a nullable column. Rows with a NULL value for that 596 # column will have the PK columns added to the key, whereas rows with a non-NULL 597 # value will not. Ensure that when the index is used, it returns all rows. 598 statement ok 599 CREATE TABLE abc (a INT, b INT, c INT, PRIMARY KEY(a, b), UNIQUE INDEX c (c)) 600 601 statement ok 602 INSERT INTO abc (a, b, c) VALUES (0, 1, NULL); 603 INSERT INTO abc (a, b, c) VALUES (0, 2, NULL); 604 INSERT INTO abc (a, b, c) VALUES (1, 1, NULL); 605 INSERT INTO abc (a, b, c) VALUES (1, 2, NULL); 606 INSERT INTO abc (a, b, c) VALUES (2, 1, 1); 607 INSERT INTO abc (a, b, c) VALUES (2, 2, 2); 608 609 query III rowsort 610 SELECT * FROM abc WHERE (c IS NULL OR c=2) AND a>0 611 ---- 612 1 1 NULL 613 1 2 NULL 614 2 2 2 615 616 # Regression test for #38878 (incorrect span generation with OR and exclusive 617 # string boundaries). 618 statement ok 619 CREATE TABLE t38878 (k1 STRING, k2 STRING, v INT, PRIMARY KEY (k1, k2)) 620 621 statement ok 622 INSERT INTO t38878 VALUES ('a', 'u', 1), ('b', 'v', 2), ('c', 'w', 3), ('d', 'x', 4), ('d', 'x2', 5) 623 624 query TTI rowsort 625 SELECT * FROM t38878 WHERE k1 = 'b' OR (k1 > 'b' AND k1 < 'd') 626 ---- 627 b v 2 628 c w 3 629 630 query TTI rowsort 631 SELECT * FROM t38878 WHERE (k1 = 'd' AND k2 = 'x') OR k1 = 'b' OR (k1 > 'b' AND k1 < 'd') 632 ---- 633 b v 2 634 c w 3 635 d x 4 636 637 # Regression test for #47976 (optimizer OOM and timeouts with many ORs). 638 statement ok 639 CREATE TABLE t47976 ( 640 k INT PRIMARY KEY, 641 a INT, 642 b FLOAT, 643 c INT, 644 INDEX (a), 645 INDEX (b), 646 INDEX (c) 647 ) 648 649 statement ok 650 SELECT k FROM t47976 WHERE 651 (a >= 6 OR b < 8 OR c IN (23, 27, 53)) AND 652 (a = 1 OR b >= 12 OR c IS NULL) AND 653 (a < 1 OR b = 6.8 OR c = 12) AND 654 (a > 4 OR b <= 5.23 OR c IN (1, 2, 3)) AND 655 (a = 12 OR b = 15.23 OR c = 14) AND 656 (a > 58 OR b < 0 OR c >= 13)