github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/prepare (about) 1 # LogicTest: local 2 3 ## Tests for ensuring that prepared statements can't get overwritten and for 4 ## deallocate and deallocate all. 5 statement error prepared statement \"a\" does not exist 6 DEALLOCATE a 7 8 statement 9 PREPARE a AS SELECT 1 10 11 query I 12 EXECUTE a 13 ---- 14 1 15 16 query I 17 EXECUTE a 18 ---- 19 1 20 21 statement error prepared statement \"a\" already exists 22 PREPARE a AS SELECT 1 23 24 statement 25 DEALLOCATE a 26 27 statement error prepared statement \"a\" does not exist 28 DEALLOCATE a 29 30 statement error prepared statement \"a\" does not exist 31 EXECUTE a 32 33 statement 34 PREPARE a AS SELECT 1 35 36 statement 37 PREPARE b AS SELECT 1 38 39 query I 40 EXECUTE a 41 ---- 42 1 43 44 query I 45 EXECUTE b 46 ---- 47 1 48 49 statement ok 50 DEALLOCATE ALL 51 52 statement error prepared statement \"a\" does not exist 53 DEALLOCATE a 54 55 statement error prepared statement \"a\" does not exist 56 EXECUTE a 57 58 statement error prepared statement \"b\" does not exist 59 DEALLOCATE b 60 61 statement error prepared statement \"b\" does not exist 62 EXECUTE b 63 64 ## Typing tests - no type hints 65 # 66 query error at or near \"\)\": syntax error 67 PREPARE a as () 68 69 statement error could not determine data type of placeholder \$1 70 PREPARE a AS SELECT $1 71 72 statement error could not determine data type of placeholder \$1 73 PREPARE a AS SELECT $2:::int 74 75 statement error could not determine data type of placeholder \$2 76 PREPARE a AS SELECT $1:::int, $3:::int 77 78 statement ok 79 PREPARE a AS SELECT $1:::int + $2 80 81 query I 82 EXECUTE a(3, 1) 83 ---- 84 4 85 86 query error could not parse "foo" as type int 87 EXECUTE a('foo', 1) 88 89 query error expected EXECUTE parameter expression to have type int, but '3.5' has type decimal 90 EXECUTE a(3.5, 1) 91 92 query error aggregate functions are not allowed in EXECUTE parameter 93 EXECUTE a(max(3), 1) 94 95 query error window functions are not allowed in EXECUTE parameter 96 EXECUTE a(rank() over (partition by 3), 1) 97 98 query error variable sub-expressions are not allowed in EXECUTE parameter 99 EXECUTE a((SELECT 3), 1) 100 101 query error wrong number of parameters for prepared statement \"a\": expected 2, got 3 102 EXECUTE a(1, 1, 1) 103 104 query error wrong number of parameters for prepared statement \"a\": expected 2, got 0 105 EXECUTE a 106 107 # Regression test for #36153. 108 statement error unknown signature: array_length\(int, int\) 109 PREPARE fail AS SELECT array_length($1, 1) 110 111 ## Type hints 112 113 statement 114 PREPARE b (int) AS SELECT $1 115 116 query I 117 EXECUTE b(3) 118 ---- 119 3 120 121 query error could not parse "foo" as type int 122 EXECUTE b('foo') 123 124 statement 125 PREPARE allTypes(int, float, string, bytea, date, timestamp, timestamptz, bool, decimal) AS 126 SELECT $1, $2, $3, $4, $5, $6, $7, $8, $9 127 128 query IRTTTTTBR 129 EXECUTE allTypes(0, 0.0, 'foo', 'bar', '2017-08-08', '2015-08-30 03:34:45.34567', '2015-08-30 03:34:45.34567', true, 3.4) 130 ---- 131 0 0 foo bar 2017-08-08 00:00:00 +0000 +0000 2015-08-30 03:34:45.34567 +0000 +0000 2015-08-30 03:34:45.34567 +0000 UTC true 3.4 132 133 ## Other 134 135 statement 136 PREPARE c AS SELECT count(*) 137 138 query I 139 EXECUTE c 140 ---- 141 1 142 143 statement 144 CREATE TABLE t (a int) 145 146 statement 147 PREPARE i AS INSERT INTO t(a) VALUES($1) RETURNING $1 + 1 148 149 statement 150 PREPARE s AS SELECT * FROM t 151 152 query I 153 EXECUTE i(1) 154 ---- 155 2 156 157 query I 158 EXECUTE i(2) 159 ---- 160 3 161 162 query error could not parse "foo" as type int 163 EXECUTE i('foo') 164 165 query error expected EXECUTE parameter expression to have type int, but '2.3' has type decimal 166 EXECUTE i(2.3) 167 168 query I 169 EXECUTE i(3.3::int) 170 ---- 171 4 172 173 query I colnames 174 EXECUTE s 175 ---- 176 a 177 1 178 2 179 3 180 181 # DISCARD ROWS drops the results, but does not affect the schema or the 182 # internal plan. 183 query I colnames 184 EXECUTE s DISCARD ROWS 185 ---- 186 a 187 188 statement 189 DEALLOCATE ALL 190 191 # Regression test for #15970 192 193 statement 194 PREPARE x AS SELECT avg(column1) OVER (PARTITION BY column2) FROM (VALUES (1, 2), (3, 4)) 195 196 query R rowsort 197 EXECUTE x 198 ---- 199 1 200 3 201 202 statement 203 PREPARE y AS SELECT avg(a.column1) OVER (PARTITION BY a.column2) FROM (VALUES (1, 2), (3, 4)) a 204 205 query R rowsort 206 EXECUTE y 207 ---- 208 1 209 3 210 211 statement 212 DEALLOCATE ALL 213 214 # Regression test for #16062 215 216 statement 217 CREATE TABLE IF NOT EXISTS f (v INT) 218 219 statement 220 PREPARE x AS SELECT * FROM f 221 222 statement 223 ALTER TABLE f ADD COLUMN u int 224 225 statement 226 INSERT INTO f VALUES (1, 2) 227 228 statement error cached plan must not change result type 229 EXECUTE x 230 231 # Ensure that plan changes prevent INSERTs from succeeding. 232 233 statement 234 PREPARE y AS INSERT INTO f VALUES ($1, $2) RETURNING * 235 236 statement 237 EXECUTE y (2, 3) 238 239 statement 240 ALTER TABLE f ADD COLUMN t int 241 242 statement error cached plan must not change result type 243 EXECUTE y (3, 4) 244 245 query III 246 SELECT * FROM f 247 ---- 248 1 2 NULL 249 2 3 NULL 250 251 # Ensure that we have a memory monitor for preparing statements 252 253 statement 254 PREPARE z AS SELECT upper('a') 255 256 # Ensure that GROUP BY HAVING doesn't mutate the parsed AST (#16388) 257 statement 258 CREATE TABLE foo (a int) 259 260 statement 261 PREPARE groupbyhaving AS SELECT min(1) FROM foo WHERE a = $1 GROUP BY a HAVING count(a) = 0 262 263 query I 264 EXECUTE groupbyhaving(1) 265 ---- 266 267 # Mismatch between expected and hinted types should prepare, but potentially 268 # fail to execute if the cast is not possible. 269 statement 270 PREPARE wrongTypePossibleCast(float) AS INSERT INTO foo VALUES ($1) 271 272 statement 273 EXECUTE wrongTypePossibleCast(2.3) 274 275 statement 276 PREPARE wrongTypeImpossibleCast(string) AS INSERT INTO foo VALUES ($1) 277 278 statement 279 EXECUTE wrongTypeImpossibleCast('3') 280 281 statement error could not parse "crabgas" as type int 282 EXECUTE wrongTypeImpossibleCast('crabgas') 283 284 # Check statement compatibility 285 286 statement ok 287 PREPARE s AS SELECT a FROM t; PREPARE p1 AS UPSERT INTO t(a) VALUES($1) RETURNING a 288 289 query I 290 EXECUTE s 291 ---- 292 1 293 2 294 3 295 296 query I 297 EXECUTE p1(123) 298 ---- 299 123 300 301 statement ok 302 PREPARE p2 AS UPDATE t SET a = a + $1 RETURNING a 303 304 query I 305 EXECUTE s 306 ---- 307 1 308 2 309 3 310 123 311 312 query I 313 EXECUTE p2(123) 314 ---- 315 124 316 125 317 126 318 246 319 320 statement ok 321 PREPARE p3 AS DELETE FROM t WHERE a = $1 RETURNING a 322 323 query I 324 EXECUTE s 325 ---- 326 124 327 125 328 126 329 246 330 331 query I 332 EXECUTE p3(124) 333 ---- 334 124 335 336 statement ok 337 PREPARE p4 AS CANCEL JOB $1 338 339 query error pq: job with ID 123 does not exist 340 EXECUTE p4(123) 341 342 statement ok 343 PREPARE p5 AS PAUSE JOB $1 344 345 query error pq: job with ID 123 does not exist 346 EXECUTE p5(123) 347 348 statement ok 349 PREPARE p6 AS RESUME JOB $1 350 351 query error pq: job with ID 123 does not exist 352 EXECUTE p6(123) 353 354 # Ensure that SET / SET CLUSTER SETTING know about placeholders 355 statement ok 356 PREPARE setp(string) AS SET application_name = $1 357 358 query T 359 SET application_name = 'foo'; SHOW application_name 360 ---- 361 foo 362 363 query T 364 EXECUTE setp('hello'); SHOW application_name 365 ---- 366 hello 367 368 # Note: we can't check the result of SET CLUSTER SETTING synchronously 369 # because it doesn't propagate immediately. 370 371 statement ok 372 PREPARE sets(string) AS SET CLUSTER SETTING cluster.organization = $1 373 374 statement ok 375 EXECUTE sets('hello') 376 377 # #19597 378 379 statement error could not determine data type of placeholder 380 PREPARE x19597 AS SELECT $1 IN ($2, null); 381 382 statement error multiple conflicting type annotations around \$1 383 PREPARE invalid AS SELECT $1:::int + $1:::float 384 385 statement error type annotation around \$1 conflicts with specified type int 386 PREPARE invalid (int) AS SELECT $1:::float 387 388 statement ok 389 PREPARE innerStmt AS SELECT $1:::int i, 'foo' t 390 391 statement error at or near "execute": syntax error 392 PREPARE outerStmt AS SELECT * FROM [EXECUTE innerStmt(3)] WHERE t = $1 393 394 query error at or near "execute": syntax error 395 SELECT * FROM [EXECUTE innerStmt(1)] CROSS JOIN [EXECUTE x] 396 397 statement ok 398 PREPARE selectin AS SELECT 1 in ($1, $2) 399 400 statement ok 401 PREPARE selectin2 AS SELECT $1::int in ($2, $3) 402 403 query B 404 EXECUTE selectin(5, 1) 405 ---- 406 true 407 408 query B 409 EXECUTE selectin2(1, 5, 1) 410 ---- 411 true 412 413 # Regression tests for #21701. 414 statement ok 415 CREATE TABLE kv (k INT PRIMARY KEY, v INT) 416 417 statement ok 418 INSERT INTO kv VALUES (1, 1), (2, 2), (3, 3) 419 420 statement ok 421 PREPARE x21701a AS SELECT * FROM kv WHERE k = $1 422 423 query II 424 EXECUTE x21701a(NULL) 425 ---- 426 427 statement ok 428 PREPARE x21701b AS SELECT * FROM kv WHERE k IS DISTINCT FROM $1 429 430 query II 431 EXECUTE x21701b(NULL) 432 ---- 433 1 1 434 2 2 435 3 3 436 437 statement ok 438 PREPARE x21701c AS SELECT * FROM kv WHERE k IS NOT DISTINCT FROM $1 439 440 query II 441 EXECUTE x21701c(NULL) 442 ---- 443 444 statement ok 445 DROP TABLE kv 446 447 # Test that a PREPARE statement after a CREATE TABLE in the same TRANSACTION 448 # doesn't hang. 449 subtest 24578 450 451 statement ok 452 BEGIN TRANSACTION 453 454 statement ok 455 create table bar (id integer) 456 457 statement ok 458 PREPARE forbar AS insert into bar (id) VALUES (1) 459 460 statement ok 461 COMMIT TRANSACTION 462 463 # Test placeholder in aggregate. 464 statement ok 465 CREATE TABLE aggtab (a INT PRIMARY KEY); 466 INSERT INTO aggtab (a) VALUES (1) 467 468 statement ok 469 PREPARE aggprep AS SELECT max(a + $1:::int) FROM aggtab 470 471 query I 472 EXECUTE aggprep(10) 473 ---- 474 11 475 476 query I 477 EXECUTE aggprep(20) 478 ---- 479 21 480 481 # Test placeholder in subquery, where the placeholder will be constant folded 482 # and then used to select an index. 483 statement ok 484 CREATE TABLE abc (a INT PRIMARY KEY, b INT, c INT); 485 CREATE TABLE xyz (x INT PRIMARY KEY, y INT, z INT, INDEX(y)); 486 INSERT INTO abc (a, b, c) VALUES (1, 10, 100); 487 INSERT INTO xyz (x, y, z) VALUES (1, 5, 50); 488 INSERT INTO xyz (x, y, z) VALUES (2, 6, 60); 489 490 statement ok 491 PREPARE subqueryprep AS SELECT * FROM abc WHERE EXISTS(SELECT * FROM xyz WHERE y IN ($1 + 1)) 492 493 query III 494 EXECUTE subqueryprep(4) 495 ---- 496 1 10 100 497 498 query III 499 EXECUTE subqueryprep(5) 500 ---- 501 1 10 100 502 503 query III 504 EXECUTE subqueryprep(6) 505 ---- 506 507 # 508 # Test prepared statements that rely on context, and ensure they are invalidated 509 # when that context changes. 510 # 511 512 statement ok 513 CREATE DATABASE otherdb 514 515 statement ok 516 USE otherdb 517 518 statement ok 519 CREATE TABLE othertable (a INT PRIMARY KEY, b INT); INSERT INTO othertable (a, b) VALUES (1, 10) 520 521 ## Current database change: Use current_database function, and ensure its return 522 ## value changes when current database changes. 523 statement ok 524 PREPARE change_db AS SELECT current_database() 525 526 query T 527 EXECUTE change_db 528 ---- 529 otherdb 530 531 statement ok 532 USE test 533 534 query T 535 EXECUTE change_db 536 ---- 537 test 538 539 statement ok 540 USE otherdb 541 542 ## Name resolution change: Query table in current database. Ensure that it is 543 ## not visible in another database. 544 statement ok 545 PREPARE change_db_2 AS SELECT * FROM othertable 546 547 query II 548 EXECUTE change_db_2 549 ---- 550 1 10 551 552 statement ok 553 USE test 554 555 query error pq: relation "othertable" does not exist 556 EXECUTE change_db_2 557 558 statement ok 559 CREATE TABLE othertable (a INT PRIMARY KEY, b INT); INSERT INTO othertable (a, b) VALUES (2, 20) 560 561 query II 562 EXECUTE change_db_2 563 ---- 564 2 20 565 566 # Same test with a query which refers to the same table twice initially, but 567 # later the two tables are different. 568 statement ok 569 PREPARE change_db_3 AS SELECT * from othertable AS t1, test.othertable AS t2 570 571 query IIII 572 EXECUTE change_db_3 573 ---- 574 2 20 2 20 575 576 statement ok 577 USE otherdb 578 579 query IIII 580 EXECUTE change_db_3 581 ---- 582 1 10 2 20 583 584 statement ok 585 DROP TABLE test.othertable 586 587 ## Search path change: Change the search path and ensure that the prepared plan 588 ## is invalidated. 589 statement ok 590 PREPARE change_search_path AS SELECT * FROM othertable 591 592 query II 593 EXECUTE change_search_path 594 ---- 595 1 10 596 597 statement ok 598 SET search_path = pg_catalog 599 600 query error pq: relation "othertable" does not exist 601 EXECUTE change_search_path 602 603 ## New table in search path: check tricky case where originally resolved table 604 ## still exists but re-resolving with new search path yields another table. 605 statement ok 606 SET search_path=public,pg_catalog 607 608 # During prepare, pg_type resolves to pg_catalog.pg_type. 609 statement ok 610 PREPARE new_table_in_search_path AS SELECT typname FROM pg_type 611 612 statement ok 613 CREATE TABLE pg_type(typname STRING); INSERT INTO pg_type VALUES('test') 614 615 # Now, it should resolve to the table we just created. 616 query T 617 EXECUTE new_table_in_search_path 618 ---- 619 test 620 621 statement ok 622 DROP TABLE pg_type 623 624 ## Even more tricky case: the query has two table references that resolve to 625 ## the same table now, but later resolve to separate tables. 626 statement ok 627 PREPARE new_table_in_search_path_2 AS 628 SELECT a.typname, b.typname FROM pg_type AS a, pg_catalog.pg_type AS b ORDER BY a.typname, b.typname LIMIT 1 629 630 query TT 631 EXECUTE new_table_in_search_path_2 632 ---- 633 _bit _bit 634 635 statement ok 636 CREATE TABLE pg_type(typname STRING); INSERT INTO pg_type VALUES('test') 637 638 query TT 639 EXECUTE new_table_in_search_path_2 640 ---- 641 test _bit 642 643 statement ok 644 DROP TABLE pg_type 645 646 statement ok 647 RESET search_path 648 649 ## Functions: Use function which depends on context; ensure that it's not 650 ## constant folded when part of prepared plan. 651 query B 652 SELECT has_column_privilege('testuser', 'othertable', 1, 'SELECT') 653 ---- 654 false 655 656 statement ok 657 GRANT ALL ON othertable TO testuser 658 659 query B 660 SELECT has_column_privilege('testuser', 'othertable', 1, 'SELECT') 661 ---- 662 true 663 664 statement ok 665 REVOKE ALL ON othertable FROM testuser 666 667 ## Location change: Change the current location (affects timezone) and make 668 ## sure the query is invalidated. 669 statement ok 670 PREPARE change_loc AS SELECT '2000-01-01 18:05:10.123'::timestamptz 671 672 query T 673 EXECUTE change_loc 674 ---- 675 2000-01-01 18:05:10.123 +0000 UTC 676 677 statement ok 678 SET TIME ZONE 'EST'; 679 680 query T 681 EXECUTE change_loc 682 ---- 683 2000-01-01 18:05:10.123 -0500 EST 684 685 statement ok 686 SET TIME ZONE 'UTC'; 687 688 ## Permissions: Grant and then revoke permission to select from a table. The 689 ## prepared plan should be invalidated. 690 statement ok 691 GRANT ALL ON othertable TO testuser 692 693 user testuser 694 695 statement ok 696 USE otherdb 697 698 statement ok 699 PREPARE change_privileges AS SELECT * FROM othertable 700 701 query II 702 EXECUTE change_privileges 703 ---- 704 1 10 705 706 user root 707 708 statement ok 709 REVOKE ALL ON othertable FROM testuser 710 711 user testuser 712 713 query error pq: user testuser does not have SELECT privilege on relation othertable 714 EXECUTE change_privileges 715 716 user root 717 718 ## Permissions: Use UPDATE statement that requires both UPDATE and SELECT 719 ## privileges. 720 statement ok 721 GRANT ALL ON othertable TO testuser 722 723 user testuser 724 725 statement ok 726 USE otherdb 727 728 statement ok 729 PREPARE update_privileges AS UPDATE othertable SET b=$1 730 731 user root 732 733 statement ok 734 REVOKE UPDATE ON othertable FROM testuser 735 736 user testuser 737 738 query error pq: user testuser does not have UPDATE privilege on relation othertable 739 EXECUTE update_privileges(5) 740 741 user root 742 743 statement ok 744 GRANT UPDATE ON othertable TO testuser 745 746 statement ok 747 REVOKE SELECT ON othertable FROM testuser 748 749 user testuser 750 751 query error pq: user testuser does not have SELECT privilege on relation othertable 752 EXECUTE update_privileges(5) 753 754 user root 755 756 query II 757 SELECT * FROM othertable 758 ---- 759 1 10 760 761 user root 762 763 ## Schema change (rename): Rename column in table and ensure that the prepared 764 ## statement is updated to incorporate it. 765 statement ok 766 PREPARE change_rename AS SELECT * FROM othertable 767 768 query II colnames 769 EXECUTE change_rename 770 ---- 771 a b 772 1 10 773 774 statement ok 775 ALTER TABLE othertable RENAME COLUMN b TO c 776 777 query II colnames 778 EXECUTE change_rename 779 ---- 780 a c 781 1 10 782 783 statement ok 784 ALTER TABLE othertable RENAME COLUMN c TO b 785 786 query II colnames 787 EXECUTE change_rename 788 ---- 789 a b 790 1 10 791 792 ## Schema change (placeholders): Similar to previous case, but with placeholder 793 ## present. 794 statement ok 795 PREPARE change_placeholders AS SELECT * FROM othertable WHERE a=$1 796 797 query II colnames 798 EXECUTE change_placeholders(1) 799 ---- 800 a b 801 1 10 802 803 statement ok 804 ALTER TABLE othertable RENAME COLUMN b TO c 805 806 query II colnames 807 EXECUTE change_placeholders(1) 808 ---- 809 a c 810 1 10 811 812 statement ok 813 ALTER TABLE othertable RENAME COLUMN c TO b 814 815 query II colnames 816 EXECUTE change_placeholders(1) 817 ---- 818 a b 819 1 10 820 821 ## Schema change (view): Change view name and ensure that prepared query is 822 ## invalidated. 823 statement ok 824 CREATE VIEW otherview AS SELECT a, b FROM othertable 825 826 statement ok 827 PREPARE change_view AS SELECT * FROM otherview 828 829 query II 830 EXECUTE change_view 831 ---- 832 1 10 833 834 statement ok 835 ALTER VIEW otherview RENAME TO otherview2 836 837 query error pq: relation "otherview" does not exist 838 EXECUTE change_view 839 840 statement ok 841 DROP VIEW otherview2 842 843 ## Schema change: Drop column and ensure that correct error is reported. 844 statement ok 845 PREPARE change_drop AS SELECT * FROM othertable WHERE b=10 846 847 query II 848 EXECUTE change_drop 849 ---- 850 1 10 851 852 statement ok 853 ALTER TABLE othertable DROP COLUMN b 854 855 query error pq: column "b" does not exist 856 EXECUTE change_drop 857 858 statement ok 859 ALTER TABLE othertable ADD COLUMN b INT; UPDATE othertable SET b=10 860 861 query II 862 EXECUTE change_drop 863 ---- 864 1 10 865 866 ## Uncommitted schema change: Rename column in table in same transaction as 867 ## execution of prepared statement and make prepared statement incorporates it. 868 statement ok 869 PREPARE change_schema_uncommitted AS SELECT * FROM othertable 870 871 statement ok 872 BEGIN TRANSACTION 873 874 query II colnames 875 EXECUTE change_schema_uncommitted 876 ---- 877 a b 878 1 10 879 880 statement ok 881 ALTER TABLE othertable RENAME COLUMN b TO c 882 883 query II colnames 884 EXECUTE change_schema_uncommitted 885 ---- 886 a c 887 1 10 888 889 # Change the schema again and verify that the previously prepared plan is not 890 # reused. Testing this is important because the second schema change won't 891 # bump the table descriptor version again. 892 statement ok 893 ALTER TABLE othertable RENAME COLUMN c TO d 894 895 query II colnames 896 EXECUTE change_schema_uncommitted 897 ---- 898 a d 899 1 10 900 901 statement ok 902 ROLLBACK TRANSACTION 903 904 # Same virtual table in different catalogs (these virtual table instances have 905 # the same table ID). 906 statement ok 907 CREATE SEQUENCE seq 908 909 statement ok 910 PREPARE pg_catalog_query AS SELECT * FROM pg_catalog.pg_sequence 911 912 query OOIIIIIB colnames 913 EXECUTE pg_catalog_query 914 ---- 915 seqrelid seqtypid seqstart seqincrement seqmax seqmin seqcache seqcycle 916 67 20 1 1 9223372036854775807 1 1 false 917 918 statement ok 919 USE test 920 921 query OOIIIIIB colnames 922 EXECUTE pg_catalog_query 923 ---- 924 seqrelid seqtypid seqstart seqincrement seqmax seqmin seqcache seqcycle 925 926 # Verify error when placeholders are used without prepare. 927 statement error no value provided for placeholder: \$1 928 SELECT $1:::int 929 930 # Verify sequences get re-resolved. 931 statement ok 932 CREATE SEQUENCE seq 933 934 statement ok 935 PREPARE seqsel AS SELECT * FROM seq 936 937 query I 938 SELECT nextval('seq') 939 ---- 940 1 941 942 query IIB 943 EXECUTE seqsel 944 ---- 945 1 0 true 946 947 statement ok 948 DROP SEQUENCE seq 949 950 statement ok 951 CREATE SEQUENCE seq 952 953 query IIB 954 EXECUTE seqsel 955 ---- 956 0 0 true 957 958 # Null placeholder values need to be assigned static types. Otherwise, we won't 959 # be able to disambiguate the concat function overloads. 960 statement ok 961 PREPARE foobar AS VALUES ($1:::string || $2:::string) 962 963 query T 964 EXECUTE foobar(NULL, NULL) 965 ---- 966 NULL 967 968 subtest regression_35145 969 970 # Verify db-independent query behaves properly even when db does not exist 971 972 statement ok 973 SET application_name = ap35145 974 975 # Prepare in custom db 976 977 statement ok 978 CREATE DATABASE d35145; SET database = d35145; 979 980 statement ok 981 PREPARE display_appname AS SELECT setting FROM pg_settings WHERE name = 'application_name' 982 983 query T 984 EXECUTE display_appname 985 ---- 986 ap35145 987 988 # Check what happens when the db where the stmt was prepared disappears "underneath". 989 990 statement ok 991 DROP DATABASE d35145 992 993 query error database "d35145" does not exist 994 EXECUTE display_appname 995 996 statement ok 997 CREATE DATABASE d35145 998 999 query T 1000 EXECUTE display_appname 1001 ---- 1002 ap35145 1003 1004 # Check what happens when the stmt is executed over a non-existent, unrelated db. 1005 1006 statement ok 1007 CREATE DATABASE d35145_2; SET database = d35145_2; DROP DATABASE d35145_2 1008 1009 query error database "d35145_2" does not exist 1010 EXECUTE display_appname 1011 1012 # Check what happens when the stmt is executed over no db whatsoever. 1013 1014 statement ok 1015 SET database = '' 1016 1017 query error cannot access virtual schema in anonymous database 1018 EXECUTE display_appname 1019 1020 statement ok 1021 SET database = 'test' 1022 1023 # Lookup by ID: Rename column in table and ensure that the prepared statement 1024 # is updated to incorporate it. 1025 statement ok 1026 CREATE TABLE ab (a INT PRIMARY KEY, b INT); INSERT INTO ab(a, b) VALUES (1, 10) 1027 1028 let $id 1029 SELECT id FROM system.namespace WHERE name='ab' 1030 1031 statement ok 1032 PREPARE change_rename_2 AS SELECT * FROM [$id AS ab] 1033 1034 query II colnames 1035 EXECUTE change_rename_2 1036 ---- 1037 a b 1038 1 10 1039 1040 statement ok 1041 ALTER TABLE ab RENAME COLUMN b TO c 1042 1043 query II colnames 1044 EXECUTE change_rename_2 1045 ---- 1046 a c 1047 1 10 1048 1049 statement ok 1050 ALTER TABLE ab RENAME COLUMN c TO b 1051 1052 query II colnames 1053 EXECUTE change_rename_2 1054 ---- 1055 a b 1056 1 10 1057 1058 statement ok 1059 USE test 1060 1061 statement ok 1062 CREATE TABLE t2 (k INT PRIMARY KEY, str STRING) 1063 1064 statement ok 1065 INSERT INTO t2 SELECT i, to_english(i) FROM generate_series(1, 5) AS g(i) 1066 1067 statement error PREPARE AS OPT PLAN is a testing facility that should not be used directly 1068 PREPARE a AS OPT PLAN 'xx' 1069 1070 statement ok 1071 SET allow_prepare_as_opt_plan = ON 1072 1073 statement ok 1074 PREPARE a AS OPT PLAN ' 1075 (Root 1076 (Scan [ (Table "t2") (Cols "k,str") ]) 1077 (Presentation "k,str") 1078 (NoOrdering) 1079 )' 1080 1081 query IT rowsort 1082 EXECUTE a 1083 ---- 1084 1 one 1085 2 two 1086 3 three 1087 4 four 1088 5 five 1089 1090 statement ok 1091 PREPARE b AS OPT PLAN ' 1092 (Root 1093 (Sort 1094 (Select 1095 (Scan [ (Table "t2") (Cols "k,str") ]) 1096 [ 1097 (Eq 1098 (Mod (Var "k") (Const 2 "int")) 1099 (Const 1 "int") 1100 ) 1101 ] 1102 ) 1103 ) 1104 (Presentation "k,str") 1105 (OrderingChoice "+str") 1106 )' 1107 1108 query IT 1109 EXECUTE b 1110 ---- 1111 5 five 1112 1 one 1113 3 three 1114 1115 statement ok 1116 PREPARE e AS OPT PLAN ' 1117 (Root 1118 (Explain 1119 (Select 1120 (Scan [ (Table "t2") (Cols "k,str") ]) 1121 [ 1122 (Eq 1123 (Mod (Var "k") (Const 2 "int")) 1124 (Const 1 "int") 1125 ) 1126 ] 1127 ) 1128 [ 1129 (Options "opt,verbose") 1130 (ColList [ (NewColumn "text" "string") ]) 1131 (Props (MinPhysProps)) 1132 ] 1133 ) 1134 (Presentation "text") 1135 (NoOrdering) 1136 )' 1137 1138 query T 1139 EXECUTE e 1140 ---- 1141 select 1142 ├── columns: k:1 str:2 1143 ├── stats: [rows=333.333333] 1144 ├── cost: 1050.03 1145 ├── key: (1) 1146 ├── fd: (1)-->(2) 1147 ├── prune: (2) 1148 ├── scan t2 1149 │ ├── columns: k:1 str:2 1150 │ ├── stats: [rows=1000] 1151 │ ├── cost: 1040.02 1152 │ ├── key: (1) 1153 │ ├── fd: (1)-->(2) 1154 │ └── prune: (1,2) 1155 └── filters 1156 └── (k:1 % 2) = 1 [outer=(1)] 1157 1158 # Only root may use PREPARE AS OPT PLAN. 1159 1160 user testuser 1161 1162 statement ok 1163 USE test 1164 1165 statement ok 1166 SET allow_prepare_as_opt_plan = ON 1167 1168 statement error user testuser does not have SELECT privilege on relation t2 1169 SELECT * FROM t2 1170 1171 statement error PREPARE AS OPT PLAN may only be used by root 1172 PREPARE a AS OPT PLAN ' 1173 (Root 1174 (Scan [ (Table "t2") (Cols "k") ]) 1175 (Presentation "k") 1176 (NoOrdering) 1177 )' 1178 1179 # Ensure we error even when the string matches a previously prepared statement. 1180 statement error PREPARE AS OPT PLAN may only be used by root 1181 PREPARE b AS OPT PLAN ' 1182 (Root 1183 (Scan [ (Table "t2") (Cols "k,str") ]) 1184 (Presentation "k,str") 1185 (NoOrdering) 1186 )' 1187 1188 # Make sure that we can constant-fold REGCLASS casts even if they're placeholders. 1189 statement ok 1190 PREPARE rcc(string) AS SELECT $1::REGCLASS::OID 1191 1192 query O 1193 EXECUTE rcc('t') 1194 ---- 1195 53 1196 1197 user root 1198 1199 # Regression test for #46217. Histogram type doesn't match column type. 1200 statement ok 1201 CREATE TABLE ts (d DATE PRIMARY KEY, x INT); 1202 1203 statement ok 1204 ALTER TABLE ts INJECT STATISTICS '[ 1205 { 1206 "columns": ["d"], 1207 "created_at": "2020-03-24 15:34:22.863634+00:00", 1208 "distinct_count": 1000, 1209 "histo_buckets": [ 1210 { 1211 "distinct_range": 0, 1212 "num_eq": 1, 1213 "num_range": 0, 1214 "upper_bound": "2020-03-24 15:16:12.117516+00:00" 1215 }, 1216 { 1217 "distinct_range": 501.60499999999996, 1218 "num_eq": 10, 1219 "num_range": 9999, 1220 "upper_bound": "2020-03-25 00:05:28.117516+00:00" 1221 } 1222 ], 1223 "histo_col_type": "TIMESTAMP", 1224 "name": "__auto__", 1225 "null_count": 0, 1226 "row_count": 100000 1227 } 1228 ]'; 1229 1230 statement ok 1231 PREPARE q AS DELETE FROM ts WHERE ts.d <= $1 1232 1233 statement ok 1234 EXECUTE q ('2020-03-25') 1235 1236 # Test that if we replace a view the cached plan is invalidated. 1237 statement ok 1238 CREATE VIEW tview AS VALUES (1) 1239 1240 statement ok 1241 PREPARE tview_prep AS SELECT * FROM tview 1242 1243 statement ok 1244 CREATE OR REPLACE VIEW tview AS VALUES (2) 1245 1246 query I 1247 EXECUTE tview_prep 1248 ---- 1249 2