github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/vectorize (about) 1 # LogicTest: local local-vec-auto fakedist fakedist-vec-auto-disk fakedist-disk 2 3 # Disable automatic stats. 4 statement ok 5 SET CLUSTER SETTING sql.stats.automatic_collection.enabled = false 6 7 statement ok 8 CREATE TABLE a (a INT, b INT, c INT4, PRIMARY KEY (a, b)) 9 10 statement ok 11 INSERT INTO a SELECT g//2, g, g FROM generate_series(0,2000) g(g) 12 13 query II 14 SELECT a, CASE WHEN a = 0 THEN 0 WHEN a = 1 THEN 3 ELSE 5 END FROM a ORDER BY 1, 2 LIMIT 6 15 ---- 16 0 0 17 0 0 18 1 3 19 1 3 20 2 5 21 2 5 22 23 # Regression test for 40574. 24 statement ok 25 CREATE TABLE t40574(pk INTEGER PRIMARY KEY, col0 INTEGER, col1 FLOAT, col2 TEXT, col3 INTEGER, col4 FLOAT, col5 TEXT) 26 27 query I 28 SELECT pk FROM t40574 WHERE (col0 > 9 AND (col1 <= 6.38 OR col0 =5) AND (col0 = 7 OR col4 = 7)) 29 ---- 30 31 # OR expression as projection. 32 query IB rowsort 33 SELECT b, b = 0 OR b = 2 FROM a WHERE b < 4 34 ---- 35 0 true 36 1 false 37 2 true 38 3 false 39 40 # OR expression as selection. 41 query I rowsort 42 SELECT b FROM a WHERE b = 0 OR b = 2 43 ---- 44 0 45 2 46 47 # Check that the right side of an OR isn't evaluated if the left side is true. 48 query I rowsort 49 SELECT b FROM a WHERE b = 0 OR 1/b = 1 50 ---- 51 0 52 1 53 54 statement ok 55 CREATE TABLE bools (b BOOL, i INT, PRIMARY KEY (b, i)); INSERT INTO bools VALUES (true, 0), (false, 1), (true, 2), (false, 3); 56 57 statement ok 58 CREATE TABLE nulls (a INT, b INT) 59 60 statement ok 61 INSERT INTO nulls VALUES (NULL, NULL), (NULL, 1), (1, NULL), (1, 1) 62 63 query I 64 SELECT count(*) FROM a 65 ---- 66 2001 67 68 query I 69 SELECT count(*) FROM (SELECT DISTINCT a FROM a) 70 ---- 71 1001 72 73 query III 74 SELECT * FROM a ORDER BY 1, 2 LIMIT 10 75 ---- 76 0 0 0 77 0 1 1 78 1 2 2 79 1 3 3 80 2 4 4 81 2 5 5 82 3 6 6 83 3 7 7 84 4 8 8 85 4 9 9 86 87 query II 88 SELECT DISTINCT(a), b FROM a ORDER BY 1, 2 LIMIT 10 89 ---- 90 0 0 91 0 1 92 1 2 93 1 3 94 2 4 95 2 5 96 3 6 97 3 7 98 4 8 99 4 9 100 101 # Simple filter. 102 query I rowsort 103 SELECT b FROM a WHERE b < 3 104 ---- 105 0 106 1 107 2 108 109 # Mixed type comparison 110 query IB rowsort 111 SELECT c, c > 1 FROM a LIMIT 3 112 ---- 113 0 false 114 1 false 115 2 true 116 117 # Simple filter with nulls. 118 query I 119 SELECT a FROM nulls WHERE a < 2 120 ---- 121 1 122 1 123 124 query II 125 SELECT a, b FROM nulls WHERE a <= b 126 ---- 127 1 1 128 129 130 # Filter on the result of a projection. 131 query II 132 SELECT a, b FROM a WHERE a * 2 < b ORDER BY 1, 2 LIMIT 5 133 ---- 134 0 1 135 1 3 136 2 5 137 3 7 138 4 9 139 140 # Simple projection. 141 query I rowsort 142 SELECT b + 1 FROM a WHERE b < 3 143 ---- 144 1 145 2 146 3 147 148 # Simple projection with nulls. 149 query I rowsort 150 SELECT b + 1 FROM nulls 151 ---- 152 NULL 153 NULL 154 2 155 2 156 157 query III rowsort 158 SELECT a, b, a + b FROM nulls 159 ---- 160 NULL NULL NULL 161 NULL 1 NULL 162 1 NULL NULL 163 1 1 2 164 165 # Multiple step projection. 166 query III rowsort 167 SELECT a, b, (a + 1) * (b + 2) FROM a WHERE a < 3 168 ---- 169 0 0 2 170 0 1 3 171 1 2 8 172 1 3 10 173 2 4 18 174 2 5 21 175 176 # Mismatched constant type in projection. Not handled yet but should fall back 177 # gracefully. 178 query I 179 SELECT (a + 1.0::DECIMAL)::INT FROM a LIMIT 1 180 ---- 181 1 182 183 # Operations with constants on the left work. 184 query I 185 SELECT 5 - a FROM a ORDER BY 1 DESC LIMIT 3 186 ---- 187 5 188 5 189 4 190 191 # Constant projections. 192 query II 193 SELECT 5, a FROM a ORDER BY 2 LIMIT 3 194 ---- 195 5 0 196 5 0 197 5 1 198 199 # Filter on a boolean column. 200 201 query BI rowsort 202 SELECT * FROM bools WHERE b 203 ---- 204 true 0 205 true 2 206 207 # Mismatched column types in projection. Not handled yet but should fall back 208 # gracefully. 209 statement ok 210 CREATE TABLE intdecfloat (a INT, b DECIMAL, c INT4, d INT2, e FLOAT8) 211 212 statement ok 213 INSERT INTO intdecfloat VALUES (1, 2.0, 3, 4, 3.5) 214 215 query I 216 SELECT (a + b)::INT FROM intdecfloat 217 ---- 218 3 219 220 statement ok 221 SET vectorize = experimental_always 222 223 query BB 224 SELECT b > a, e < b FROM intdecfloat 225 ---- 226 true false 227 228 query IR 229 SELECT a, b FROM intdecfloat WHERE a < b; 230 ---- 231 1 2.0 232 233 query RIRRI 234 SELECT a+b, a+c, b+c, b+d, c+d FROM intdecfloat 235 ---- 236 3.0 4 5.0 6.0 7 237 238 query RIRRI 239 SELECT a-b, a-c, b-c, b-d, c-d FROM intdecfloat 240 ---- 241 -1.0 -2 -1.0 -2.0 -1 242 243 query RIRRI 244 SELECT a*b, a*c, b*c, b*d, c*d FROM intdecfloat 245 ---- 246 2.0 3 6.0 8.0 12 247 248 query RRRRR 249 SELECT a/b, a/c, b/c, b/d, c/d FROM intdecfloat 250 ---- 251 0.5 0.33333333333333333333 0.66666666666666666667 0.5 0.75 252 253 statement ok 254 RESET vectorize 255 256 # vectorized decimal arithmetic 257 statement ok 258 CREATE table decimals (a DECIMAL, b DECIMAL) 259 260 statement ok 261 INSERT INTO decimals VALUES(123.0E200, 12.3) 262 263 statement ok 264 SET vectorize = experimental_always 265 266 query R 267 SELECT a*b FROM decimals 268 ---- 269 1.51290E+203 270 271 query R 272 SELECT a/b FROM decimals 273 ---- 274 1.0E+201 275 276 query R 277 SELECT a+b FROM decimals 278 ---- 279 12300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000012.3 280 281 query R 282 SELECT a-b FROM decimals 283 ---- 284 12299999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999987.7 285 286 statement ok 287 RESET vectorize 288 289 # AND expressions. 290 query IIBB rowsort 291 SELECT a, b, a < 2 AND b > 0 AND a * b != 3, a < 2 AND b < 2 FROM a WHERE a < 2 AND b > 0 AND a * b != 3 292 ---- 293 0 1 true true 294 1 2 true false 295 296 statement ok 297 CREATE TABLE b (a INT, b STRING, PRIMARY KEY (b,a)) 298 299 statement ok 300 INSERT INTO b VALUES 301 (0, 'a'), 302 (1, 'a'), 303 (0, 'b'), 304 (1, 'b') 305 306 query IT rowsort 307 SELECT sum_int(a), b from b group by b 308 ---- 309 1 a 310 1 b 311 312 # Test that lookup joins run fine through columnar execution. 313 314 statement ok 315 CREATE TABLE c (a INT, b INT, c INT, d INT, PRIMARY KEY (a, c), INDEX sec (b)) 316 317 statement ok 318 CREATE TABLE d (a INT, b INT, PRIMARY KEY (b, a)) 319 320 statement ok 321 INSERT INTO c VALUES (1, 1, 1, 0), (2, 1, 2, 0) 322 323 statement ok 324 INSERT INTO d VALUES (1, 1), (1, 2) 325 326 statement ok 327 ALTER TABLE c INJECT STATISTICS '[ 328 { 329 "columns": ["a"], 330 "created_at": "2018-01-01 1:00:00.00000+00:00", 331 "row_count": 1, 332 "distinct_count": 1 333 } 334 ]' 335 336 statement ok 337 SET optimizer = on 338 339 # Ensure that a lookup join is used. 340 query I 341 SELECT count(*) FROM [EXPLAIN SELECT c.a FROM c JOIN d ON d.b = c.b] WHERE tree LIKE '%lookup-join%' 342 ---- 343 1 344 345 statement ok 346 SET vectorize = experimental_always 347 348 # Simple lookup join. 349 query I rowsort 350 SELECT c.a FROM c JOIN d ON d.b = c.b 351 ---- 352 1 353 2 354 355 # Index join. 356 query I 357 SELECT c.d FROM c@sec 358 ---- 359 0 360 0 361 362 # Lookup join on secondary index, requires an index join into the primary 363 # index. Both of these should be wrapped and work fine. 364 query I 365 SELECT c.d FROM c@sec JOIN d ON d.b = c.b 366 ---- 367 0 368 0 369 370 # Ordinality operator with a filter and limit. 371 query IIII 372 SELECT * FROM a WITH ORDINALITY WHERE a > 1 LIMIT 6 373 ---- 374 2 4 4 5 375 2 5 5 6 376 3 6 6 7 377 3 7 7 8 378 4 8 8 9 379 4 9 9 10 380 381 # Ensure that lookup joins properly get their postprocessing to select needed 382 # columns. 383 384 query I rowsort 385 SELECT c.a FROM c INNER LOOKUP JOIN c@sec AS s ON c.b=s.b 386 ---- 387 1 388 1 389 2 390 2 391 392 # Test that LIKE expressions are properly handled by vectorized execution. 393 statement ok 394 RESET vectorize 395 396 statement ok 397 CREATE TABLE e (x TEXT) 398 399 statement ok 400 INSERT INTO e VALUES ('abc'), ('xyz'), (NULL) 401 402 statement ok 403 SET vectorize = experimental_always 404 405 query T 406 SELECT * FROM e WHERE x LIKE '' 407 ---- 408 409 query T 410 SELECT * FROM e WHERE x NOT LIKE '' ORDER BY 1 411 ---- 412 abc 413 xyz 414 415 query T 416 SELECT * FROM e WHERE x LIKE '%' ORDER BY 1 417 ---- 418 abc 419 xyz 420 421 query T 422 SELECT * FROM e WHERE x NOT LIKE '%' 423 ---- 424 425 query T 426 SELECT * FROM e WHERE x LIKE 'ab%' 427 ---- 428 abc 429 430 query T 431 SELECT * FROM e WHERE x NOT LIKE 'ab%' 432 ---- 433 xyz 434 435 query T 436 SELECT * FROM e WHERE x LIKE '%bc' 437 ---- 438 abc 439 440 query T 441 SELECT * FROM e WHERE x NOT LIKE '%bc' 442 ---- 443 xyz 444 445 query T 446 SELECT * FROM e WHERE x LIKE 'a%c' 447 ---- 448 abc 449 450 query T 451 SELECT * FROM e WHERE x NOT LIKE 'a%c' 452 ---- 453 xyz 454 455 query TBBBBBBBB 456 SELECT x, x LIKE '%', x NOT LIKE '%', x LIKE 'ab%', x NOT LIKE 'ab%', x LIKE '%bc', x NOT LIKE '%bc', x LIKE 'a%c', x NOT LIKE 'a%c' FROM e ORDER BY x 457 ---- 458 NULL NULL NULL NULL NULL NULL NULL NULL NULL 459 abc true false true false true false true false 460 xyz true false false true false true false true 461 462 statement ok 463 RESET optimizer; RESET vectorize; RESET distsql; RESET vectorize_row_count_threshold 464 465 # Regression test for composite null handling 466 # https://github.com/cockroachdb/cockroach/issues/37358 467 statement ok 468 CREATE TABLE composite (d DECIMAL, INDEX d_idx (d)) 469 470 statement ok 471 INSERT INTO composite VALUES (NULL), (1), (1.0), (1.00) 472 473 query T rowsort 474 SELECT d FROM composite@primary 475 ---- 476 NULL 477 1 478 1.0 479 1.00 480 481 query T rowsort 482 SELECT d FROM composite@d_idx 483 ---- 484 NULL 485 1 486 1.0 487 1.00 488 489 # Test unhandled type conversion. (Should fall back to distsql.) 490 query T 491 SELECT ARRAY(SELECT 1) FROM a LIMIT 1 492 ---- 493 {1} 494 495 # Regression test for decoding OID type. 496 statement ok 497 CREATE TABLE t38754 (a OID PRIMARY KEY) 498 499 statement ok 500 INSERT INTO t38754 VALUES (1) 501 502 query O 503 SELECT * FROM t38754 504 ---- 505 1 506 507 # Test integer division. 508 query T 509 SELECT a/b FROM a WHERE b = 2 510 ---- 511 0.5 512 513 # Test mixed types comparison. 514 query I 515 SELECT b FROM a WHERE b < 0.5 516 ---- 517 0 518 519 # Test unsupported scrub (should fall back to distsql). 520 statement ok 521 CREATE TABLE t38626 (id int PRIMARY KEY, name STRING, CONSTRAINT abc CHECK (name > 'he')) 522 523 statement ok 524 INSERT INTO t38626 VALUES (1, 'hello') 525 526 statement ok 527 EXPERIMENTAL SCRUB TABLE t38626 528 529 # Regression test for issue with reading from system tables that have no 530 # sentinel keys. 531 query T 532 SELECT "hashedPassword" FROM system.users LIMIT 1 533 ---- 534 · 535 536 query IITI 537 SELECT * FROM system.namespace LIMIT 1 538 ---- 539 0 0 defaultdb 50 540 541 # Regression test for issue with fetching from unique indexes with embedded 542 # nulls. 543 statement ok 544 CREATE TABLE t38753 (x INT PRIMARY KEY, y INT, UNIQUE INDEX (y)); INSERT INTO t38753 VALUES (0, NULL) 545 546 query II 547 SELECT * FROM t38753 ORDER BY y; 548 ---- 549 0 NULL 550 551 # Regression test for #38752. 552 query IIBB 553 SELECT count(*), count(*) + 1, count(*) > 4, count(*) + 1 > 4 FROM b 554 ---- 555 4 5 false true 556 557 query I 558 SELECT * FROM (SELECT count(*) AS x FROM b) WHERE x > 0; 559 ---- 560 4 561 562 # Regression test for #38908 563 statement ok 564 CREATE TABLE t38908 (x INT) 565 566 statement ok 567 INSERT INTO t38908 VALUES (1) 568 569 statement ok 570 SET vectorize=experimental_always 571 572 query I 573 SELECT * FROM t38908 WHERE x IN (1, 2) 574 ---- 575 1 576 577 statement ok 578 RESET vectorize 579 580 # Test that an aggregate with no aggregate functions is handled correctly. 581 query III 582 SELECT 0, 1 + 2, 3 * 4 FROM a HAVING true 583 ---- 584 0 3 12 585 586 # Testing some builtin functions. 587 statement ok 588 CREATE TABLE builtin_test (x STRING, y INT) 589 590 statement ok 591 INSERT INTO builtin_test VALUES ('Hello', 3), ('There', 2) 592 593 query T rowsort 594 SELECT substring(x, 1, y) FROM builtin_test 595 ---- 596 Hel 597 Th 598 599 query T rowsort 600 SELECT substring(x, 1, abs(y)) FROM builtin_test 601 ---- 602 Hel 603 Th 604 605 # Regression test for #44625. 606 statement error negative substring length -1 not allowed 607 SELECT substring(x, 0, -1) FROM builtin_test 608 609 # Regression test for #44881 (non-Int64 argument types). 610 query T rowsort 611 SELECT substring(x, -1::INT2, 3::INT4) FROM builtin_test 612 ---- 613 H 614 T 615 616 query I rowsort 617 SELECT abs(y) FROM builtin_test 618 ---- 619 3 620 2 621 622 statement ok 623 CREATE TABLE extract_test (x DATE) 624 625 statement ok 626 INSERT INTO extract_test VALUES ('2017-01-01') 627 628 query R 629 SELECT EXTRACT(YEAR FROM x) FROM extract_test 630 ---- 631 2017 632 633 # Regression test for #38937 634 statement ok 635 CREATE TABLE t38937 (_int2) AS SELECT 1::INT2 636 637 query I 638 SELECT sum_int(_int2) FROM t38937 639 ---- 640 1 641 642 # Regression tests for #38959 643 644 statement ok 645 CREATE TABLE t38959 (a INT PRIMARY KEY, b INT, c INT, d INT, INDEX b_idx (b) STORING (c, d), UNIQUE INDEX c_idx (c) STORING (b, d)) 646 647 statement ok 648 INSERT INTO t38959 VALUES (1, 2, 3, 4) 649 650 statement ok 651 SET tracing=on,kv,results 652 653 query IIII 654 SELECT * FROM t38959@c_idx 655 ---- 656 1 2 3 4 657 658 statement ok 659 SET tracing=off 660 661 statement ok 662 CREATE TABLE t38959_2 (x INT PRIMARY KEY, y INT, z FLOAT, INDEX xy (x, y), INDEX zyx (z, y, x), FAMILY (x), FAMILY (y), FAMILY (z)) 663 664 statement ok 665 INSERT INTO t38959_2 VALUES (1, 2, 3.0), (4, 5, 6.0), (7, NULL, 8.0) 666 667 statement ok 668 SET tracing=on,kv,results 669 670 query I 671 SELECT min(x) FROM t38959_2 WHERE (y, z) = (2, 3.0) 672 ---- 673 1 674 675 statement ok 676 SET tracing=off 677 678 # Test for #38858 -- handle aggregates correctly on an empty table. 679 statement ok 680 CREATE TABLE empty (a INT PRIMARY KEY, b FLOAT) 681 682 # GROUP BY is omitted, so aggregates are in scalar context. 683 query IIIIIRR 684 SELECT count(*), count(a), sum_int(a), min(a), max(a), sum(b), avg(b) FROM empty 685 ---- 686 0 0 NULL NULL NULL NULL NULL 687 688 # GROUP BY is present, so aggregates are in non-scalar context. 689 query IIIIIRR 690 SELECT count(*), count(a), sum_int(a), min(a), max(a), sum(b), avg(b) FROM empty GROUP BY a 691 ---- 692 693 694 statement ok 695 CREATE TABLE t_38995 (a INT PRIMARY KEY) 696 697 statement ok 698 INSERT INTO t_38995 VALUES (1), (2), (3) 699 700 query II 701 SELECT a, ordinality*2 FROM t_38995 WITH ORDINALITY 702 ---- 703 1 2 704 2 4 705 3 6 706 707 # Test for #39827, top k sort with bytes. 708 statement ok 709 CREATE TABLE t_39827 (a STRING) 710 711 statement ok 712 INSERT INTO t_39827 VALUES ('hello'), ('world'), ('a'), ('foo') 713 714 query T 715 SELECT a FROM t_39827 ORDER BY a LIMIT 2 716 ---- 717 a 718 foo 719 720 # Regression test for #40227, an issue with flat bytes implementation. 721 statement ok 722 CREATE TABLE t_40227 AS SELECT g FROM generate_series(0, 5) AS g 723 724 statement ok 725 SELECT '' FROM t_40227 AS t1 JOIN t_40227 AS t2 ON true 726 727 # Tests for #39417 728 statement ok 729 CREATE TABLE t39417 (x int8) 730 731 statement ok 732 INSERT INTO t39417 VALUES (10) 733 734 query R 735 select (x/1) from t39417 736 ---- 737 10 738 739 # Regression tests for #39540, an issue caused by shallow copying decimals. 740 statement ok 741 CREATE TABLE IF NOT EXISTS t_39540 AS 742 SELECT 743 g % 2 = 0 AS _bool, g::DECIMAL AS _decimal 744 FROM 745 generate_series(0, 5) AS g 746 747 query R rowsort 748 SELECT 749 tab_426212._decimal - tab_426216._decimal 750 FROM 751 t_39540 AS tab_426212, 752 t_39540 AS tab_426214, 753 t_39540 754 RIGHT JOIN t_39540 AS tab_426216 ON true 755 ORDER BY 756 tab_426214._bool ASC 757 ---- 758 1296 values hashing to cad02075a867c3c0564bf80fe665eed6 759 760 # Regression test for #40372. 761 statement ok 762 CREATE TABLE t40372_1 ( 763 a INT, 764 b INT, 765 c FLOAT, 766 d FLOAT 767 ) 768 769 statement ok 770 INSERT INTO t40372_1 VALUES 771 (1, 1, 1, 1), 772 (2, 2, 2, 2), 773 (3, 3, 3, 3) 774 775 statement ok 776 CREATE TABLE t40372_2 ( 777 a INT, 778 b FLOAT, 779 c FLOAT, 780 d INT 781 ) 782 783 statement ok 784 INSERT INTO t40372_2 VALUES 785 (1, 1, 1, 1), 786 (2, 2, 2, 2), 787 (3, 3, 3, 3) 788 789 query IIRR rowsort 790 SELECT * FROM t40372_1 NATURAL JOIN t40372_2 791 ---- 792 1 1 1 1 793 2 2 2 2 794 3 3 3 3 795 796 # Test that comparison against a null value selects the value out. 797 statement ok 798 CREATE TABLE tnull(a INT, b INT) 799 800 statement ok 801 INSERT INTO tnull VALUES(NULL, 238) 802 803 query I rowsort 804 SELECT a FROM tnull WHERE (a<=b OR a>=b) 805 ---- 806 807 # Test that AND'ing a true value with another true value while one of them is 808 # actually NULL returns NULL. 809 statement ok 810 CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER) 811 812 statement ok 813 INSERT INTO t1 VALUES(NULL,2,1) 814 815 # We need both parenthesis in WHERE clause so that the AND operation under test 816 # is not optimized out. 817 query I 818 SELECT CASE WHEN a <= b THEN 1 ELSE 2 END 819 FROM t1 820 WHERE (a > b - 2 AND a < b + 2) OR (c > a AND c < b) 821 ---- 822 823 # Regression tests for NULL expression handling. 824 statement ok 825 CREATE TABLE t_case_null (x INT) 826 827 statement ok 828 INSERT INTO t_case_null VALUES (0) 829 830 query I 831 SELECT CASE WHEN x = 0 THEN 0 ELSE NULL END FROM t_case_null 832 ---- 833 0 834 835 query I 836 SELECT CASE WHEN x = 0 THEN NULL ELSE 0 END FROM t_case_null 837 ---- 838 NULL 839 840 query I 841 SELECT CASE WHEN x = 1 THEN 1 ELSE NULL END FROM t_case_null 842 ---- 843 NULL 844 845 query I 846 SELECT * FROM t_case_null WHERE NULL AND NULL 847 ---- 848 849 query I 850 SELECT * FROM t_case_null WHERE NULL AND x = 0 851 ---- 852 853 query I 854 SELECT * FROM t_case_null WHERE x = 0 AND NULL 855 ---- 856 857 # Regression test for #40732. 858 statement ok 859 CREATE TABLE t40732 AS SELECT g::INT8 AS _int8, 860 g::FLOAT8 AS _float8, 861 '2001-01-01'::DATE 862 + g AS _date, 863 g % 2 = 1 AS _bool, 864 g::DECIMAL AS _decimal, 865 g::STRING AS _string, 866 g::STRING::BYTES AS _bytes 867 FROM generate_series(1, 5) AS g 868 869 statement ok 870 INSERT INTO t40732 DEFAULT VALUES 871 872 query I 873 SELECT * 874 FROM ( 875 SELECT tab_1541._int8 AS col_2976 876 FROM t40732 AS tab_1538 877 JOIN t40732 AS tab_1539 878 JOIN t40732 AS tab_1540 ON 879 tab_1539._float8 = tab_1540._float8 880 JOIN t40732 AS tab_1541 ON 881 tab_1540._int8 = tab_1541._int8 ON 882 tab_1538._float8 = tab_1540._float8, 883 t40732 AS tab_1542 884 WHERE tab_1542._bool > tab_1540._bool 885 ) 886 ORDER BY col_2976 887 ---- 888 2 889 2 890 2 891 4 892 4 893 4 894 895 query T 896 SELECT feature_name FROM crdb_internal.feature_usage WHERE feature_name='sql.exec.query.is-vectorized' AND usage_count > 0 897 ---- 898 sql.exec.query.is-vectorized 899 900 # Test IS NULL (and alike) projections. 901 query IBBIBB rowsort 902 SELECT a, a IS NULL, a IS NOT NULL, b, b IS NOT DISTINCT FROM NULL, b IS DISTINCT FROM NULL FROM nulls 903 ---- 904 NULL true false NULL true false 905 NULL true false 1 false true 906 1 false true NULL true false 907 1 false true 1 false true 908 909 # Test IS NULL (and alike) selections. 910 query II rowsort 911 SELECT a, b FROM nulls WHERE a IS NULL 912 ---- 913 NULL NULL 914 NULL 1 915 916 query II rowsort 917 SELECT a, b FROM nulls WHERE a IS NOT NULL 918 ---- 919 1 NULL 920 1 1 921 922 query II rowsort 923 SELECT a, b FROM nulls WHERE a IS NOT DISTINCT FROM NULL 924 ---- 925 NULL NULL 926 NULL 1 927 928 query II rowsort 929 SELECT a, b FROM nulls WHERE a IS DISTINCT FROM NULL 930 ---- 931 1 NULL 932 1 1 933 934 query III rowsort 935 SELECT 936 a, 937 b, 938 CASE 939 WHEN a IS NOT NULL AND b IS NULL THEN 0 940 WHEN a IS NULL THEN 1 941 WHEN b IS NOT NULL THEN 2 942 END 943 FROM 944 nulls 945 ---- 946 NULL NULL 1 947 NULL 1 1 948 1 NULL 0 949 1 1 2 950 951 # Regression test for #42816 - top K sort when K is greated than 952 # coldata.BatchSize(). 953 statement ok 954 CREATE TABLE t_42816 (a int); INSERT INTO t_42816 SELECT * FROM generate_series(0, 1025) 955 956 query I 957 SELECT * FROM t_42816 ORDER BY a OFFSET 1020 LIMIT 10 958 ---- 959 1020 960 1021 961 1022 962 1023 963 1024 964 1025 965 966 # Regression tests for #42994 967 statement ok 968 CREATE TABLE t42994 (a INT PRIMARY KEY, b BIT, INDEX i (a, b)); 969 INSERT INTO t42994 VALUES (1, 1::BIT); 970 971 query I 972 SELECT a FROM t42994@i 973 ---- 974 1 975 976 statement ok 977 CREATE TABLE t42994_2 (a BIT PRIMARY KEY, b INT, UNIQUE INDEX i (b)); 978 INSERT INTO t42994_2 VALUES (1::BIT, NULL); 979 980 query I 981 SELECT b FROM t42994_2@i 982 ---- 983 NULL 984 985 # Regression test for zeroing out an aggregate value when NULLs are present. 986 statement ok 987 SELECT 988 max(s) 989 FROM 990 ( 991 SELECT 992 s, i 993 FROM 994 (VALUES ('1', 1), (NULL, 2)) AS t (s, i) 995 ) 996 GROUP BY 997 i 998 999 statement ok 1000 CREATE TABLE t43550(a INT2 PRIMARY KEY); INSERT INTO t43550 VALUES (1) 1001 1002 query I 1003 SELECT CASE WHEN a = 0 THEN a ELSE 1:::INT8 END FROM t43550 1004 ---- 1005 1 1006 1007 # Regression test for #43855. 1008 statement ok 1009 CREATE TABLE t43855(o OID, r REGPROCEDURE) 1010 1011 query i 1012 SELECT CASE WHEN o = 0 THEN 0:::OID ELSE r END FROM t43855 1013 ---- 1014 1015 # Regression test for an aggregate that has output type different from its 1016 # input type (INT4 is input whereas output is INT). Currently such query is not 1017 # supported through vectorized engine, but we will get a plan with wrapped 1018 # rowexec.orderedAggregator. 1019 query I 1020 SELECT max(c) FROM a 1021 ---- 1022 2000 1023 1024 # Regression test for starting wrapped processors multiple times. 1025 statement ok 1026 CREATE TABLE t44133_0(c0 STRING); CREATE TABLE t44133_1(c0 STRING UNIQUE NOT NULL) 1027 1028 statement ok 1029 SELECT * FROM t44133_0, t44133_1 WHERE t44133_0.c0 NOT BETWEEN t44133_1.c0 AND '' AND (t44133_1.c0 IS NULL) 1030 1031 # Regression test for CASE operator with unhandled output type. 1032 statement ok 1033 CREATE TABLE t44304(c0 INT); INSERT INTO t44304 VALUES (0) 1034 1035 query I 1036 SELECT * FROM t44304 WHERE CASE WHEN t44304.c0 > 0 THEN NULL END 1037 ---- 1038 1039 # Regression test for CASE operator and flat bytes. 1040 statement ok 1041 CREATE TABLE t44624(c0 STRING, c1 BOOL); INSERT INTO t44624(rowid, c0, c1) VALUES (0, '', true), (1, '', NULL) 1042 1043 query TB rowsort 1044 SELECT * FROM t44624 ORDER BY CASE WHEN c1 IS NULL THEN c0 WHEN true THEN c0 END 1045 ---- 1046 · true 1047 · NULL 1048 1049 # Regression test for 44726 (unknown WHEN expression type). 1050 statement ok 1051 CREATE TABLE t44726(c0 INT); INSERT INTO t44726(c0) VALUES (0) 1052 1053 query I 1054 SELECT * FROM t44726 WHERE 0 > (CASE WHEN nullif(NULL, ilike_escape('', current_user(), '')) THEN 0 ELSE t44726.c0 END) 1055 ---- 1056 1057 # Regression test for wrongly performing bounds check elimination on flat bytes 1058 # which might lead to a crash. 1059 statement ok 1060 CREATE TABLE t44822(c0 BYTES); CREATE VIEW v0(c0) AS SELECT min(t44822.c0) FROM t44822 1061 1062 query T 1063 SELECT * FROM v0 WHERE v0.c0 NOT BETWEEN v0.c0 AND v0.c0 1064 ---- 1065 1066 # Regression test for #44935 (decimals with different number of trailing zeroes 1067 # hashing to different values). 1068 statement ok 1069 CREATE TABLE t44935 (x decimal); INSERT INTO t44935 VALUES (1.0), (1.00) 1070 1071 query I 1072 SELECT count(*) FROM (SELECT DISTINCT x FROM t44935) 1073 ---- 1074 1 1075 1076 # Regression test for #45481. 1077 statement ok 1078 CREATE TABLE t45481 (a INT, b INT, c FLOAT, d DECIMAL, e STRING, f BYTES, g UUID, PRIMARY KEY (a, b, c, d, e, f, g)) 1079 1080 # Generate all combinations of values 1 to 7. 1081 statement ok 1082 INSERT INTO t45481 SELECT a, b, c::FLOAT, d::DECIMAL, d::STRING, d::STRING::BYTES, rpad(d::STRING, 32, d::STRING)::UUID FROM 1083 generate_series(1, 7) AS a(a), 1084 generate_series(1, 7) AS b(b), 1085 generate_series(1, 7) AS c(c), 1086 generate_series(1, 7) AS d(d) 1087 1088 query IRTTTR 1089 SELECT b, d, e, f, g, sum(a) FROM t45481 GROUP BY b, d, e, f, g ORDER BY b, d, e, f, g 1090 ---- 1091 1 1 1 1 11111111-1111-1111-1111-111111111111 196 1092 1 2 2 2 22222222-2222-2222-2222-222222222222 196 1093 1 3 3 3 33333333-3333-3333-3333-333333333333 196 1094 1 4 4 4 44444444-4444-4444-4444-444444444444 196 1095 1 5 5 5 55555555-5555-5555-5555-555555555555 196 1096 1 6 6 6 66666666-6666-6666-6666-666666666666 196 1097 1 7 7 7 77777777-7777-7777-7777-777777777777 196 1098 2 1 1 1 11111111-1111-1111-1111-111111111111 196 1099 2 2 2 2 22222222-2222-2222-2222-222222222222 196 1100 2 3 3 3 33333333-3333-3333-3333-333333333333 196 1101 2 4 4 4 44444444-4444-4444-4444-444444444444 196 1102 2 5 5 5 55555555-5555-5555-5555-555555555555 196 1103 2 6 6 6 66666666-6666-6666-6666-666666666666 196 1104 2 7 7 7 77777777-7777-7777-7777-777777777777 196 1105 3 1 1 1 11111111-1111-1111-1111-111111111111 196 1106 3 2 2 2 22222222-2222-2222-2222-222222222222 196 1107 3 3 3 3 33333333-3333-3333-3333-333333333333 196 1108 3 4 4 4 44444444-4444-4444-4444-444444444444 196 1109 3 5 5 5 55555555-5555-5555-5555-555555555555 196 1110 3 6 6 6 66666666-6666-6666-6666-666666666666 196 1111 3 7 7 7 77777777-7777-7777-7777-777777777777 196 1112 4 1 1 1 11111111-1111-1111-1111-111111111111 196 1113 4 2 2 2 22222222-2222-2222-2222-222222222222 196 1114 4 3 3 3 33333333-3333-3333-3333-333333333333 196 1115 4 4 4 4 44444444-4444-4444-4444-444444444444 196 1116 4 5 5 5 55555555-5555-5555-5555-555555555555 196 1117 4 6 6 6 66666666-6666-6666-6666-666666666666 196 1118 4 7 7 7 77777777-7777-7777-7777-777777777777 196 1119 5 1 1 1 11111111-1111-1111-1111-111111111111 196 1120 5 2 2 2 22222222-2222-2222-2222-222222222222 196 1121 5 3 3 3 33333333-3333-3333-3333-333333333333 196 1122 5 4 4 4 44444444-4444-4444-4444-444444444444 196 1123 5 5 5 5 55555555-5555-5555-5555-555555555555 196 1124 5 6 6 6 66666666-6666-6666-6666-666666666666 196 1125 5 7 7 7 77777777-7777-7777-7777-777777777777 196 1126 6 1 1 1 11111111-1111-1111-1111-111111111111 196 1127 6 2 2 2 22222222-2222-2222-2222-222222222222 196 1128 6 3 3 3 33333333-3333-3333-3333-333333333333 196 1129 6 4 4 4 44444444-4444-4444-4444-444444444444 196 1130 6 5 5 5 55555555-5555-5555-5555-555555555555 196 1131 6 6 6 6 66666666-6666-6666-6666-666666666666 196 1132 6 7 7 7 77777777-7777-7777-7777-777777777777 196 1133 7 1 1 1 11111111-1111-1111-1111-111111111111 196 1134 7 2 2 2 22222222-2222-2222-2222-222222222222 196 1135 7 3 3 3 33333333-3333-3333-3333-333333333333 196 1136 7 4 4 4 44444444-4444-4444-4444-444444444444 196 1137 7 5 5 5 55555555-5555-5555-5555-555555555555 196 1138 7 6 6 6 66666666-6666-6666-6666-666666666666 196 1139 7 7 7 7 77777777-7777-7777-7777-777777777777 196 1140 1141 # Test that unsupported post process specs get wrapped in the vectorized engine. 1142 statement ok 1143 CREATE TABLE mixed_type_a (a INT, b TIMESTAMPTZ) 1144 1145 statement ok 1146 CREATE TABLE mixed_type_b (a INT, b INTERVAL, c TIMESTAMP) 1147 1148 statement ok 1149 INSERT INTO mixed_type_a VALUES (0, 0::TIMESTAMPTZ) 1150 1151 statement ok 1152 INSERT INTO mixed_type_b VALUES (0, INTERVAL '0 days', 0::TIMESTAMP) 1153 1154 # Set vectorize to experimental_always to ensure that no error occurs when 1155 # planning these mixed-type operations. 1156 statement ok 1157 SET vectorize=experimental_always 1158 1159 query B 1160 SELECT b > now() - interval '1 day' FROM mixed_type_a 1161 ---- 1162 false 1163 1164 # Merge join ON expressions also get wrapped. 1165 query ITITT 1166 SELECT * FROM mixed_type_a AS a INNER MERGE JOIN mixed_type_b AS b ON a.a = b.a AND a.b < (now() - b.b) 1167 ---- 1168 0 1970-01-01 00:00:00 +0000 UTC 0 00:00:00 1970-01-01 00:00:00 +0000 +0000 1169 1170 # So do hash inner hash join ON expressions. 1171 query ITITT 1172 SELECT * FROM mixed_type_a AS a JOIN mixed_type_b AS b ON a.a = b.a AND a.b < (now() - b.b) 1173 ---- 1174 0 1970-01-01 00:00:00 +0000 UTC 0 00:00:00 1970-01-01 00:00:00 +0000 +0000 1175 1176 statement ok 1177 RESET vectorize 1178 1179 # Regression for 46140. 1180 statement ok 1181 DROP TABLE IF EXISTS t0, t1; 1182 CREATE TABLE t0(c0 INT); 1183 CREATE TABLE t1(c0 BOOL) INTERLEAVE IN PARENT t0(rowid); 1184 INSERT INTO t0(c0) VALUES (0); 1185 INSERT INTO t1(rowid, c0) VALUES(0, TRUE) 1186 1187 query I 1188 SELECT max(t1.rowid) FROM t1 WHERE t1.c0 1189 ---- 1190 0 1191 1192 # Regression for #46183. 1193 statement ok 1194 CREATE TABLE t46183 (x INT PRIMARY KEY, y JSONB, INVERTED INDEX (y)); 1195 INSERT INTO t46183 VALUES (1, '{"y": "hello"}') 1196 1197 query I 1198 SELECT count(*) FROM t46183 WHERE y->'y' = to_jsonb('hello') 1199 ---- 1200 1 1201 1202 # Regression test for #47029 (not resetting nulls vector when cfetcher read a 1203 # NULL value in the interleaved table). 1204 statement ok 1205 CREATE TABLE t47029_0(c0 INT); 1206 CREATE TABLE t47029_1(c0 INT); 1207 INSERT INTO t47029_0(c0) VALUES(0); 1208 INSERT INTO t47029_1(c0) VALUES(NULL); 1209 CREATE INDEX ON t47029_1(c0) INTERLEAVE IN PARENT t47029_0(c0) 1210 1211 query I 1212 SELECT * FROM t47029_0 WHERE (t47029_0.rowid > 0) IS NULL 1213 ---- 1214 1215 # Regression for #47115 (cfetcher sometimes not reading value component 1216 # of composite encoded data). 1217 statement ok 1218 CREATE TABLE t47715 (c0 DECIMAL PRIMARY KEY, c1 INT UNIQUE); 1219 INSERT INTO t47715(c0) VALUES (1819487610) 1220 1221 query T 1222 SELECT c0 FROM t47715 ORDER by c1 1223 ---- 1224 1819487610 1225 1226 # Regression for flat bytes vector not being reset when it is reused by a 1227 # projecting operator. 1228 query TTT 1229 WITH 1230 with_194015 (col_1548014) 1231 AS ( 1232 SELECT 1233 * 1234 FROM 1235 ( 1236 VALUES 1237 (('-28 years -2 mons -677 days -11:53:30.528699':::INTERVAL::INTERVAL + '11:55:41.419498':::TIME::TIME)::TIME + '1973-01-24':::DATE::DATE), 1238 ('1970-01-11 01:38:09.000155+00:00':::TIMESTAMP), 1239 ('1970-01-09 07:04:13.000247+00:00':::TIMESTAMP), 1240 ('1970-01-07 14:19:52.000951+00:00':::TIMESTAMP), 1241 (NULL) 1242 ) 1243 AS tab_240443 (col_1548014) 1244 ), 1245 with_194016 (col_1548015, col_1548016, col_1548017) 1246 AS ( 1247 SELECT 1248 * 1249 FROM 1250 ( 1251 VALUES 1252 ( 1253 '160.182.25.199/22':::INET::INET << 'c2af:30cb:5db8:bb79:4d11:2d0:1de8:bcea/59':::INET::INET, 1254 '09:14:05.761109':::TIME::TIME + '4 years 7 mons 345 days 23:43:13.325036':::INTERVAL::INTERVAL, 1255 B'0101010110101011101001111010100011001111001110001000101100011001101' 1256 ), 1257 (false, '14:36:41.282187':::TIME, B'011111111011001100000001101101011111110110010011110100110111100') 1258 ) 1259 AS tab_240444 (col_1548015, col_1548016, col_1548017) 1260 ), 1261 with_194017 (col_1548018) 1262 AS (SELECT * FROM (VALUES ('43a30bc5-e412-426d-b99a-65783a7ed445':::UUID), (NULL), (crdb_internal.cluster_id()::UUID)) AS tab_240445 (col_1548018)) 1263 SELECT 1264 CASE 1265 WHEN false THEN age('1970-01-09 08:48:24.000568+00:00':::TIMESTAMPTZ::TIMESTAMPTZ, '1970-01-07 08:40:45.000483+00:00':::TIMESTAMPTZ::TIMESTAMPTZ)::INTERVAL 1266 ELSE ( 1267 ( 1268 (-0.02805450661234963150):::DECIMAL::DECIMAL 1269 * array_position( 1270 (gen_random_uuid()::UUID::UUID || (NULL::UUID || NULL::UUID[])::UUID[])::UUID[], 1271 '5f29920d-7db1-4efc-b1cc-d1a7d0bcf145':::UUID::UUID 1272 )::INT8::INT8 1273 )::DECIMAL 1274 * age('1970-01-04 07:17:45.000268+00:00':::TIMESTAMPTZ::TIMESTAMPTZ, NULL::TIMESTAMPTZ)::INTERVAL::INTERVAL 1275 ) 1276 END::INTERVAL 1277 + '-21 years -10 mons -289 days -13:27:05.205069':::INTERVAL::INTERVAL 1278 AS col_1548019, 1279 '1984-01-07':::DATE AS col_1548020, 1280 'f96fd19a-d2a9-4d98-81dd-97e3fc2a45d2':::UUID AS col_1548022 1281 FROM 1282 with_194015 1283 ORDER BY 1284 with_194015.col_1548014 DESC 1285 LIMIT 1286 4:::INT8; 1287 ---- 1288 NULL 1984-01-07 00:00:00 +0000 +0000 f96fd19a-d2a9-4d98-81dd-97e3fc2a45d2 1289 NULL 1984-01-07 00:00:00 +0000 +0000 f96fd19a-d2a9-4d98-81dd-97e3fc2a45d2 1290 NULL 1984-01-07 00:00:00 +0000 +0000 f96fd19a-d2a9-4d98-81dd-97e3fc2a45d2 1291 NULL 1984-01-07 00:00:00 +0000 +0000 f96fd19a-d2a9-4d98-81dd-97e3fc2a45d2