1# This test file contains a collection of tests developed for 2# WL#4179 (Stored programs: validation of stored program statements). 3# 4# The main idea of the tests here is to check that a stored program 5# properly handles metadata changes of the objects being used by 6# the stored program. 7 8########################################################################### 9########################################################################### 10 11--echo 12--echo # 13--echo # WL#4179: Stored programs: validation of stored program statements. 14--echo # 15--echo 16 17########################################################################### 18########################################################################### 19 20--echo # The test case below demonstrates that meta-data changes are detected 21--echo # by triggers. 22 23CREATE TABLE t1 (a INT, b INT); 24CREATE TABLE t2 (a INT, b INT); 25CREATE TABLE t3 (a INT); 26 27INSERT INTO t2 VALUES (11, 12), (21, 22); 28 29CREATE TRIGGER t3_ai AFTER INSERT ON t3 FOR EACH ROW 30 INSERT INTO t1 SELECT * FROM t2; 31 32INSERT INTO t3 (a) VALUES (1); 33 34SELECT * FROM t1; 35SELECT * FROM t2; 36 37ALTER TABLE t1 ADD COLUMN c INT; 38ALTER TABLE t2 ADD COLUMN c INT; 39INSERT INTO t2 VALUES (31, 32, 33); 40 41INSERT INTO t3 (a) VALUES (2); 42 43SELECT * FROM t1; 44SELECT * FROM t2; 45 46DROP TABLE t1; 47DROP TABLE t2; 48DROP TABLE t3; 49 50########################################################################### 51 52--echo 53--echo # Check that NEW/OLD rows work within triggers. 54--echo 55 56CREATE TABLE t1 (a INT); 57INSERT INTO t1(a) VALUES (1); 58 59delimiter |; 60 61CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW 62BEGIN 63 SET @a = OLD.a; 64 SET @b = NEW.a; 65 SELECT OLD.a INTO @c; 66 SELECT NEW.a INTO @d; 67 68 SET NEW.a = NEW.a * 2; 69END| 70 71delimiter ;| 72 73UPDATE t1 SET a = a * 10; 74 75SELECT @a, @c, @b, @d; 76 77SELECT a FROM t1; 78 79DROP TABLE t1; 80 81########################################################################### 82 83--echo 84 85CREATE TABLE t1 (a INT); 86INSERT INTO t1 VALUES (1), (2); 87 88CREATE PROCEDURE p1() 89 SELECT * FROM t1; 90 91CALL p1(); 92 93--echo 94--echo # 1.1 Check if added column into table is recognized correctly 95--echo # in a stored procedure. 96ALTER TABLE t1 ADD COLUMN b INT DEFAULT 0; 97CALL p1(); 98 99--echo 100--echo # 1.2 Check if dropped column is not appeared in SELECT query 101--echo # executed inside a stored procedure. 102ALTER TABLE t1 DROP COLUMN a; 103CALL p1(); 104 105--echo 106--echo # 1.3 Check if changed column is picked up properly. 107ALTER TABLE t1 CHANGE COLUMN b b VARCHAR(255) DEFAULT 'n/a'; 108DELETE FROM t1; 109INSERT INTO t1 VALUES (b), ('hello'); 110CALL p1(); 111 112--echo 113--echo # 1.4 Check if table's recreation is handled correctly 114--echo # inside a call of stored procedure. 115 116DROP TABLE t1; 117DROP PROCEDURE p1; 118 119CREATE TABLE t1 (a INT); 120INSERT INTO t1 VALUES (1), (2); 121 122CREATE PROCEDURE p1() 123 SELECT * FROM t1; 124 125CALL p1(); 126 127DROP TABLE t1; 128 129--error ER_NO_SUCH_TABLE 130CALL p1(); 131 132CREATE TABLE t1 (a INT); 133INSERT INTO t1 VALUES (1), (2); 134 135CALL p1(); 136 137--echo 138--echo # 1.5 Recreate table t1 with another set of columns and 139--echo # re-call a stored procedure. 140 141DROP TABLE t1; 142DROP PROCEDURE p1; 143 144CREATE TABLE t1 (a INT); 145INSERT INTO t1 VALUES (1), (2); 146 147CREATE PROCEDURE p1() 148 SELECT * FROM t1; 149 150CALL p1(); 151 152DROP TABLE t1; 153 154--error ER_NO_SUCH_TABLE 155CALL p1(); 156 157CREATE TABLE t1 (b VARCHAR(10), c VARCHAR(10)); 158INSERT INTO t1 VALUES ('a', 'b'), ('c', 'd'); 159 160CALL p1(); 161 162DROP TABLE t1; 163DROP PROCEDURE p1; 164 165########################################################################### 166 167--echo 168--echo # 2.1 Stored program that uses query like 'SELECT * FROM v' must be 169--echo # re-executed successfully if some columns were added into the view 170--echo # definition by ALTER VIEW; 171 172CREATE VIEW v1 AS SELECT 1, 2, 3; 173 174CREATE PROCEDURE p1() 175 SELECT * FROM v1; 176 177CALL p1(); 178 179ALTER VIEW v1 AS SELECT 1, 2, 3, 4, 5; 180 181CALL p1(); 182 183--echo 184--echo # 2.2 Stored program that uses query like 'SELECT * FROM v' must be 185--echo # re-executed successfully if some columns were removed from the view 186--echo # definition by ALTER VIEW; 187 188ALTER VIEW v1 AS SELECT 1, 5; 189 190CALL p1(); 191 192--echo 193--echo # 2.3 Stored program that uses query like 'SELECT * FROM v' must be 194--echo # re-executed successfully if a base table for the view being used was 195--echo # extended by new columns (by ALTER TABLE); 196 197CREATE TABLE t1(a INT, b INT); 198INSERT INTO t1 VALUES (1, 2); 199 200DROP VIEW v1; 201CREATE VIEW v1 AS SELECT * FROM t1; 202 203DROP PROCEDURE p1; 204CREATE PROCEDURE p1() 205 SELECT * FROM v1; 206 207CALL p1(); 208 209ALTER TABLE t1 ADD COLUMN c INT DEFAULT 3; 210 211# NOTE: this behaviour differs from the one of regular tables -- the thing is 212# that "The view definition is “frozen” at creation time, so changes to the 213# underlying tables afterward do not affect the view definition." 214# (http://dev.mysql.com/doc/refman/5.0/en/create-view.html). 215# So, this call should return 2 (not 3) columns. 216CALL p1(); 217 218--echo 219--echo # 2.4 Stored program that uses query like 'SELECT * FROM v' must be 220--echo # re-executed successfully if not used columns were removed from the 221--echo # base table of this view (by ALTER TABLE); 222 223DROP TABLE t1; 224CREATE TABLE t1(a INT, b INT, c INT); 225INSERT INTO t1 VALUES (1, 2, 3); 226 227DROP VIEW v1; 228CREATE VIEW v1 AS SELECT b, c FROM t1; 229 230DROP PROCEDURE p1; 231CREATE PROCEDURE p1() 232 SELECT * FROM v1; 233 234CALL p1(); 235 236ALTER TABLE t1 DROP COLUMN a; 237 238CALL p1(); 239 240ALTER TABLE t1 DROP COLUMN b; 241 242# NOTE: see the note above about view specific. Must-have column has been 243# dropped -- the view has become invalid. 244--error ER_VIEW_INVALID 245CALL p1(); 246 247--echo 248--echo # 2.5 Stored program that uses query like 'SELECT * FROM v' must be 249--echo # re-executed successfully if a type of some base table's columns were 250--echo # changed (by ALTER TABLE); 251 252DROP TABLE t1; 253CREATE TABLE t1(a INT, b INT, c INT); 254INSERT INTO t1 VALUES (1, 2, 3); 255 256DROP VIEW v1; 257CREATE VIEW v1 AS SELECT b, c FROM t1; 258 259DROP PROCEDURE p1; 260CREATE PROCEDURE p1() 261 SELECT * FROM v1; 262 263CALL p1(); 264 265ALTER TABLE t1 CHANGE COLUMN b b VARCHAR(255) DEFAULT 'n/a'; 266DELETE FROM t1; 267INSERT INTO t1(a, c) VALUES (10, 30); 268 269CALL p1(); 270 271--echo 272--echo # 2.6 Stored program that uses query like 'SELECT * FROM v' must be 273--echo # re-executed successfully if the view 'v' was dropped and created again 274--echo # with the same definition; 275--echo # 276--echo # 2.7 Stored program that uses query like 'SELECT * FROM v' must be 277--echo # re-executed successfully if the view 'v' was dropped and created again 278--echo # with different, but compatible definition. 279 280DROP VIEW v1; 281DROP TABLE t1; 282DROP PROCEDURE p1; 283 284CREATE VIEW v1 AS SELECT 1, 2, 3; 285 286CREATE PROCEDURE p1() 287 SELECT * FROM v1; 288 289CALL p1(); 290 291DROP VIEW v1; 292 293--error ER_NO_SUCH_TABLE 294CALL p1(); 295 296CREATE VIEW v1 AS SELECT 4, 5, 6; 297 298CALL p1(); 299 300--echo 301--echo # 2.8 Stored program that uses query like 'SELECT * FROM v' must be 302--echo # re-executed successfully if the view base tables have been re-created 303--echo # using the same or compatible definition. 304 305DROP VIEW v1; 306DROP PROCEDURE p1; 307 308CREATE TABLE t1(a INT, b INT); 309INSERT INTO t1 VALUES (1, 2); 310 311CREATE VIEW v1 AS SELECT * FROM t1; 312 313CREATE PROCEDURE p1() 314 SELECT * FROM v1; 315 316CALL p1(); 317 318DROP TABLE t1; 319 320--error ER_VIEW_INVALID 321CALL p1(); 322 323CREATE TABLE t1(a VARCHAR(255), b VARCHAR(255)); 324INSERT INTO t1 VALUES ('a', 'b'), ('c', 'd'); 325 326CALL p1(); 327 328DROP VIEW v1; 329DROP TABLE t1; 330DROP PROCEDURE p1; 331 332--echo 333--echo # 3.1 Stored program that uses query like 'SELECT * FROM t' must be 334--echo # re-executed successfully if some columns were added into temporary table 335--echo # table 't' (by ALTER TABLE); 336 337CREATE TEMPORARY TABLE t1(a INT, b INT); 338INSERT INTO t1 VALUES (1, 2); 339 340CREATE PROCEDURE p1() SELECT * FROM t1; 341 342CALL p1(); 343 344ALTER TABLE t1 ADD COLUMN c INT DEFAULT 3; 345 346CALL p1(); 347 348--echo 349--echo # 3.2 Stored program that uses query like 'SELECT * FROM t' must be 350--echo # re-executed successfully if some columns were removed from temporary 351--echo # table 't' (by ALTER TABLE); 352 353ALTER TABLE t1 DROP COLUMN a; 354 355CALL p1(); 356 357--echo 358--echo # 3.3 Stored program that uses query like 'SELECT * FROM t' must be 359--echo # re-executed successfully if a type of some temporary table's columns were 360--echo # changed (by ALTER TABLE); 361 362ALTER TABLE t1 CHANGE COLUMN b b VARCHAR(255) DEFAULT 'n/a'; 363INSERT INTO t1(c) VALUES (4); 364 365CALL p1(); 366 367--echo 368--echo # 3.4 Stored program that uses query like 'SELECT * FROM t' must be 369--echo # re-executed successfully if the temporary table 't' was dropped and 370--echo # created again with the same definition; 371--echo # 372--echo # 3.5 Stored program that uses query like 'SELECT * FROM t' must be 373--echo # re-executed successfully if the temporary table 't' was dropped and 374--echo # created again with different, but compatible definition. 375 376DROP TEMPORARY TABLE t1; 377CREATE TEMPORARY TABLE t1(a INT, b INT); 378INSERT INTO t1 VALUES (1, 2); 379 380CALL p1(); 381 382DROP TEMPORARY TABLE t1; 383CREATE TEMPORARY TABLE t1(a VARCHAR(255), b VARCHAR(255), c VARCHAR(255)); 384INSERT INTO t1 VALUES ('aa', 'bb', 'cc'); 385 386CALL p1(); 387 388DROP TEMPORARY TABLE t1; 389DROP PROCEDURE p1; 390 391########################################################################### 392 393--echo 394--echo # 4.1 Stored program must fail when it is re-executed after a table's column 395--echo # that this program is referenced to has been removed; 396 397CREATE TABLE t1(a INT, b INT); 398INSERT INTO t1 VALUES (1, 2); 399 400CREATE PROCEDURE p1() SELECT a, b FROM t1; 401 402CALL p1(); 403 404ALTER TABLE t1 DROP COLUMN b; 405 406--error ER_BAD_FIELD_ERROR 407CALL p1(); 408 409DROP PROCEDURE p1; 410DROP TABLE t1; 411 412--echo 413--echo # 4.2 Stored program must fail when it is re-executed after a temporary 414--echo # table's column that this program is referenced to has been removed; 415 416CREATE TEMPORARY TABLE t1(a INT, b INT); 417INSERT INTO t1 VALUES (1, 2); 418 419CREATE PROCEDURE p1() SELECT a, b FROM t1; 420 421CALL p1(); 422 423ALTER TABLE t1 DROP COLUMN b; 424 425--error ER_BAD_FIELD_ERROR 426CALL p1(); 427 428DROP PROCEDURE p1; 429DROP TEMPORARY TABLE t1; 430 431--echo 432--echo # 4.3 Stored program must fail when it is re-executed after a view's 433--echo # column that this program is referenced to has been removed; 434 435CREATE VIEW v1 AS SELECT 1 AS a, 2 AS b; 436 437CREATE PROCEDURE p1() SELECT a, b FROM v1; 438 439CALL p1(); 440 441ALTER VIEW v1 AS SELECT 1 AS a; 442 443--error ER_BAD_FIELD_ERROR 444CALL p1(); 445 446DROP PROCEDURE p1; 447DROP VIEW v1; 448 449--echo 450--echo # 4.4 Stored program must fail when it is re-executed after a regular table 451--echo # that this program referenced to was removed; 452 453CREATE TABLE t1(a INT, b INT); 454INSERT INTO t1 VALUES (1, 2); 455 456CREATE PROCEDURE p1() SELECT a, b FROM t1; 457 458CALL p1(); 459 460DROP TABLE t1; 461 462--error ER_NO_SUCH_TABLE 463CALL p1(); 464 465DROP PROCEDURE p1; 466 467--echo 468--echo # 4.5 Stored program must fail when it is re-executed after a view that 469--echo # this program referenced to was removed; 470 471CREATE VIEW v1 AS SELECT 1 AS a, 2 AS b; 472 473CREATE PROCEDURE p1() SELECT a, b FROM v1; 474 475CALL p1(); 476 477DROP VIEW v1; 478 479--error ER_NO_SUCH_TABLE 480CALL p1(); 481 482DROP PROCEDURE p1; 483 484--echo 485--echo # 4.6 Stored program must fail when it is re-executed after a temporary 486--echo # table that this program referenced to was removed; 487 488CREATE TEMPORARY TABLE t1(a INT, b INT); 489INSERT INTO t1 VALUES (1, 2); 490 491CREATE PROCEDURE p1() SELECT a, b FROM t1; 492 493CALL p1(); 494 495DROP TABLE t1; 496 497--error ER_NO_SUCH_TABLE 498CALL p1(); 499 500DROP PROCEDURE p1; 501 502--echo 503--echo # 4.7 Stored program must fail if the program executes some 504--echo # SQL-statement and afterwards re-executes it again when some table 't' 505--echo # referenced by the statement was dropped in the period between statement 506--echo # execution; 507 508CREATE TABLE t1(a INT); 509CREATE TABLE t2(a INT); 510 511delimiter |; 512 513CREATE PROCEDURE p1() 514BEGIN 515 DECLARE CONTINUE HANDLER FOR 1146 516 SELECT 'Table t1 does not exist anymore' as msg; 517 518 SELECT * FROM t1; 519 INSERT INTO t2 VALUES (1); 520 521 SELECT GET_LOCK('m1', 10000); 522 523 SELECT * FROM t1; 524END| 525 526delimiter ;| 527 528--echo 529--echo # -- connection: con1 530--connect (con1, localhost, root) 531 532SELECT GET_LOCK('m1', 0); 533 534--echo 535--echo # -- connection: default 536--connection default 537 538--send CALL p1() 539 540--echo 541--echo # -- connection: con1 542--connection con1 543 544let $wait_condition = SELECT COUNT(*) = 1 FROM t2; 545--source include/wait_condition.inc 546 547DROP TABLE t1; 548 549SELECT RELEASE_LOCK('m1'); 550 551--echo 552--echo # -- connection: default 553--connection default 554 555--reap 556 557--disconnect con1 558 559DROP TABLE t2; 560DROP PROCEDURE p1; 561 562########################################################################### 563 564--echo 565--echo # 5.1 Regular table -> View 566 567CREATE TABLE t1(a INT, b INT); 568INSERT INTO t1 VALUES (1, 2); 569 570CREATE PROCEDURE p1() SELECT * FROM t1; 571 572CALL p1(); 573 574DROP TABLE t1; 575CREATE VIEW t1 AS SELECT 1 AS a, 2 AS b; 576 577CALL p1(); 578 579DROP PROCEDURE p1; 580DROP VIEW t1; 581 582--echo 583--echo # 5.2 Regular table -> Temporary table 584 585CREATE TABLE t1(a INT, b INT); 586INSERT INTO t1 VALUES (1, 2); 587 588CREATE PROCEDURE p1() SELECT * FROM t1; 589 590CALL p1(); 591 592DROP TABLE t1; 593CREATE TEMPORARY TABLE t1(a INT, b INT); 594INSERT INTO t1 VALUES (1, 2); 595 596CALL p1(); 597 598DROP PROCEDURE p1; 599DROP TEMPORARY TABLE t1; 600 601--echo 602--echo # 5.3 View -> Regular table 603 604CREATE VIEW t1 AS SELECT 1 AS a, 2 AS b; 605 606CREATE PROCEDURE p1() SELECT * FROM t1; 607 608CALL p1(); 609 610DROP VIEW t1; 611CREATE TABLE t1(a INT, b INT); 612INSERT INTO t1 VALUES (1, 2); 613 614CALL p1(); 615 616DROP PROCEDURE p1; 617DROP TABLE t1; 618 619--echo 620--echo # 5.4 View -> Temporary table 621 622CREATE VIEW t1 AS SELECT 1 AS a, 2 AS b; 623 624CREATE PROCEDURE p1() SELECT * FROM t1; 625 626CALL p1(); 627 628DROP VIEW t1; 629CREATE TEMPORARY TABLE t1(a INT, b INT); 630INSERT INTO t1 VALUES (1, 2); 631 632CALL p1(); 633 634DROP PROCEDURE p1; 635DROP TEMPORARY TABLE t1; 636 637--echo 638--echo # 5.5 Temporary table -> View 639 640CREATE TEMPORARY TABLE t1(a INT, b INT); 641INSERT INTO t1 VALUES (1, 2); 642 643CREATE PROCEDURE p1() SELECT * FROM t1; 644 645CALL p1(); 646 647DROP TEMPORARY TABLE t1; 648 649CREATE VIEW t1 AS SELECT 1 AS a, 2 AS b; 650 651CALL p1(); 652 653DROP PROCEDURE p1; 654DROP VIEW t1; 655 656--echo 657--echo # 5.6 Temporary table -> Regular table 658 659CREATE TEMPORARY TABLE t1(a INT, b INT); 660INSERT INTO t1 VALUES (1, 2); 661 662CREATE PROCEDURE p1() SELECT * FROM t1; 663 664CALL p1(); 665 666DROP TEMPORARY TABLE t1; 667CREATE TABLE t1(a INT, b INT); 668INSERT INTO t1 VALUES (1, 2); 669 670CALL p1(); 671 672DROP PROCEDURE p1; 673DROP TABLE t1; 674 675########################################################################### 676 677--echo 678--echo # 6.1 Trigger that uses column 'a' of table 't' via pseudo-variable NEW 679--echo # must be re-executed successfully if the table definition has been changed 680--echo # in a compatible way. "Compatible way" in this case is that if the table 681--echo # 't' still has a column named 'a' and the column type is compatible with 682--echo # the operation that NEW.a takes part of. 683--echo # 684--echo # 6.2 Trigger that uses column 'a' of table 't' via pseudo-variable OLD 685--echo # must be re-executed successfully if the table definition has been changed 686--echo # in a compatible way. "Compatible way" in this case is that if the table 687--echo # 't' still has a column named 'a' and the column type is compatible with 688--echo # the operation that OLD.a takes part of. 689 690CREATE TABLE t1(a INT, b INT); 691INSERT INTO t1 VALUES (1, 2); 692 693delimiter |; 694 695CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW 696BEGIN 697 SET @x = OLD.a; 698 SET @y = NEW.a; 699END| 700 701delimiter ;| 702 703--echo 704SET @x = 0, @y = 0; 705UPDATE t1 SET a = 3, b = 4; 706SELECT @x, @y; 707 708--echo 709ALTER TABLE t1 ADD COLUMN c INT DEFAULT -1; 710 711--echo 712SET @x = 0, @y = 0; 713UPDATE t1 SET a = 5, b = 6; 714SELECT @x, @y; 715 716--echo 717ALTER TABLE t1 CHANGE COLUMN a a VARCHAR(255); 718 719--echo 720SET @x = 0, @y = 0; 721UPDATE t1 SET a = CONCAT('xxx_', a), b = 7; 722SELECT @x, @y; 723 724--echo 725DROP TABLE t1; 726 727--echo 728--echo # 6.3 Re-execution of a trigger that uses column 'a' of table 't' via 729--echo # pseudo-variable NEW must fail if the table definition has been changed in 730--echo # the way that the column 'a' does not exist anymore. 731--echo # 732--echo # 6.4 Re-execution of a trigger that uses column 'a' of table 't' via 733--echo # pseudo-variable OLD must fail if the table definition has been changed in 734--echo # the way that the column 'a' does not exist anymore. 735 736CREATE TABLE t1(a INT, b INT); 737INSERT INTO t1 VALUES (1, 2); 738 739delimiter |; 740 741CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW 742BEGIN 743 SET @x = OLD.a; 744 SET @y = NEW.b; 745END| 746 747delimiter ;| 748 749--echo 750UPDATE t1 SET a = 3, b = 4; 751 752--echo 753ALTER TABLE t1 CHANGE COLUMN a a2 INT; 754 755--echo 756--error ER_BAD_FIELD_ERROR 757UPDATE t1 SET a2 = 5, b = 6; 758 759--echo 760ALTER TABLE t1 CHANGE COLUMN a2 a INT; 761ALTER TABLE t1 CHANGE COLUMN b b2 INT; 762 763--echo 764--error ER_BAD_FIELD_ERROR 765UPDATE t1 SET a = 5, b2 = 6; 766 767--echo 768DROP TABLE t1; 769 770########################################################################### 771 772--echo 773--echo # 7.1 Setup: 774--echo # - stored program 'a', which alters regular table 't' in a compatible 775--echo # way; 776--echo # - stored program 'b', which calls 'a' and uses 't' before and after the 777--echo # call; 778--echo # Stored program 'b' must be executed successfully. 779 780CREATE TABLE t1(a INT, b INT); 781INSERT INTO t1 VALUES (1, 2); 782 783delimiter |; 784 785CREATE PROCEDURE p1() 786 ALTER TABLE t1 ADD COLUMN c INT DEFAULT 3| 787 788CREATE PROCEDURE p2() 789BEGIN 790 SELECT a, b FROM t1; 791 CALL p1(); 792 SELECT a, b FROM t1; 793END| 794 795delimiter ;| 796 797--echo 798CALL p2(); 799--echo 800 801DROP PROCEDURE p1; 802DROP PROCEDURE p2; 803 804DROP TABLE t1; 805 806--echo 807--echo # 7.2 Setup: 808--echo # - stored program 'a', which alters temporary table 't' in a compatible 809--echo # way; 810--echo # - stored program 'b', which calls 'a' and uses 't' before and after the 811--echo # call; 812--echo # Stored program 'b' must be executed successfully. 813 814CREATE TEMPORARY TABLE t1(a INT, b INT); 815INSERT INTO t1 VALUES (1, 2); 816 817delimiter |; 818 819CREATE PROCEDURE p1() 820 ALTER TABLE t1 ADD COLUMN c INT DEFAULT 3| 821 822CREATE PROCEDURE p2() 823BEGIN 824 SELECT a, b FROM t1; 825 CALL p1(); 826 SELECT a, b FROM t1; 827END| 828 829delimiter ;| 830 831--echo 832CALL p2(); 833--echo 834 835DROP PROCEDURE p1; 836DROP PROCEDURE p2; 837 838DROP TEMPORARY TABLE t1; 839 840--echo 841--echo # 7.3 Setup: 842--echo # - stored program 'a', which re-creates regular table 't' in a 843--echo # compatible way; 844--echo # - stored program 'b', which calls 'a' and uses 't' before and after the 845--echo # call; 846--echo # Stored program 'b' must be executed successfully. 847 848CREATE TABLE t1(a INT, b INT); 849INSERT INTO t1 VALUES (1, 2); 850 851delimiter |; 852 853CREATE PROCEDURE p1() 854BEGIN 855 DROP TABLE t1; 856 CREATE TABLE t1(a INT, b INT, c INT); 857 INSERT INTO t1 VALUES (1, 2, 3); 858END| 859 860CREATE PROCEDURE p2() 861BEGIN 862 SELECT a, b FROM t1; 863 CALL p1(); 864 SELECT a, b FROM t1; 865END| 866 867delimiter ;| 868 869--echo 870CALL p2(); 871--echo 872 873DROP PROCEDURE p1; 874DROP PROCEDURE p2; 875 876DROP TABLE t1; 877 878--echo 879--echo # 7.4 Setup: 880--echo # - stored program 'a', which re-creates temporary table 't' in a 881--echo # compatible way; 882--echo # - stored program 'b', which calls 'a' and uses 't' before and after the 883--echo # call; 884--echo # Stored program 'b' must be executed successfully. 885 886CREATE TEMPORARY TABLE t1(a INT, b INT); 887INSERT INTO t1 VALUES (1, 2); 888 889delimiter |; 890 891CREATE PROCEDURE p1() 892BEGIN 893 DROP TEMPORARY TABLE t1; 894 CREATE TEMPORARY TABLE t1(a INT, b INT, c INT); 895 INSERT INTO t1 VALUES (1, 2, 3); 896END| 897 898CREATE PROCEDURE p2() 899BEGIN 900 SELECT a, b FROM t1; 901 CALL p1(); 902 SELECT a, b FROM t1; 903END| 904 905delimiter ;| 906 907--echo 908CALL p2(); 909--echo 910 911DROP PROCEDURE p1; 912DROP PROCEDURE p2; 913 914DROP TEMPORARY TABLE t1; 915 916--echo 917--echo # 7.5 Setup: 918--echo # - stored program 'a', which re-creates view 'v' in a compatible way; 919--echo # - stored program 'b', which calls 'a' and uses 'v' before and after the 920--echo # call; 921--echo # Stored program 'b' must be executed successfully. 922 923CREATE VIEW v1 AS SELECT 1 AS a, 2 AS b; 924 925delimiter |; 926 927CREATE PROCEDURE p1() 928BEGIN 929 DROP VIEW v1; 930 CREATE VIEW v1 AS SELECT 1 AS a, 2 AS b, 3 AS c; 931END| 932 933CREATE PROCEDURE p2() 934BEGIN 935 SELECT a, b FROM v1; 936 CALL p1(); 937 SELECT a, b FROM v1; 938END| 939 940delimiter ;| 941 942--echo 943CALL p2(); 944--echo 945 946DROP PROCEDURE p1; 947DROP PROCEDURE p2; 948 949DROP VIEW v1; 950 951--echo 952--echo # 7.6 Setup: 953--echo # - stored program 'a', which alters regular table 't' in an incompatible 954--echo # way; 955--echo # - stored program 'b', which calls 'a' and uses 't' before and after the 956--echo # call; 957--echo # Stored program 'b' must fail on access to the table after its 958--echo # modification. 959 960CREATE TABLE t1(a INT, b INT); 961INSERT INTO t1 VALUES (1, 2); 962 963delimiter |; 964 965CREATE PROCEDURE p1() 966 ALTER TABLE t1 DROP COLUMN a| 967 968CREATE PROCEDURE p2() 969BEGIN 970 SELECT a, b FROM t1; 971 CALL p1(); 972 SELECT a, b FROM t1; 973END| 974 975delimiter ;| 976 977--echo 978--error ER_BAD_FIELD_ERROR 979CALL p2(); 980--echo 981 982DROP PROCEDURE p1; 983DROP PROCEDURE p2; 984 985DROP TABLE t1; 986 987--echo 988--echo # 7.7 Setup: 989--echo # - stored program 'a', which alters temporary table 't' in an 990--echo # incompatible way; 991--echo # - stored program 'b', which calls 'a' and uses 't' before and after the 992--echo # call; 993--echo # Stored program 'b' must fail on access to the table after its 994--echo # modification. 995 996CREATE TEMPORARY TABLE t1(a INT, b INT); 997INSERT INTO t1 VALUES (1, 2); 998 999delimiter |; 1000 1001CREATE PROCEDURE p1() 1002 ALTER TABLE t1 DROP COLUMN a| 1003 1004CREATE PROCEDURE p2() 1005BEGIN 1006 SELECT a, b FROM t1; 1007 CALL p1(); 1008 SELECT a, b FROM t1; 1009END| 1010 1011delimiter ;| 1012 1013--echo 1014--error ER_BAD_FIELD_ERROR 1015CALL p2(); 1016--echo 1017 1018DROP PROCEDURE p1; 1019DROP PROCEDURE p2; 1020 1021DROP TEMPORARY TABLE t1; 1022 1023--echo 1024--echo # 7.8 Setup: 1025--echo # - stored program 'a', which re-creates regular table 't' in an 1026--echo # incompatible way; 1027--echo # - stored program 'b', which calls 'a' and uses 't' before and after the 1028--echo # call; 1029--echo # Stored program 'b' must fail on access to the table after its 1030--echo # modification. 1031 1032CREATE TABLE t1(a INT, b INT); 1033INSERT INTO t1 VALUES (1, 2); 1034 1035delimiter |; 1036 1037CREATE PROCEDURE p1() 1038BEGIN 1039 DROP TABLE t1; 1040 CREATE TABLE t1(b INT, c INT); 1041 INSERT INTO t1 VALUES (2, 3); 1042END| 1043 1044CREATE PROCEDURE p2() 1045BEGIN 1046 SELECT a, b FROM t1; 1047 CALL p1(); 1048 SELECT a, b FROM t1; 1049END| 1050 1051delimiter ;| 1052 1053--echo 1054--error ER_BAD_FIELD_ERROR 1055CALL p2(); 1056--echo 1057 1058DROP PROCEDURE p1; 1059DROP PROCEDURE p2; 1060 1061DROP TABLE t1; 1062 1063--echo 1064--echo # 7.9 Setup: 1065--echo # - stored program 'a', which re-creates temporary table 't' in an 1066--echo # incompatible way; 1067--echo # - stored program 'b', which calls 'a' and uses 't' before and after the 1068--echo # call; 1069--echo # Stored program 'b' must fail on access to the table after its 1070--echo # modification. 1071 1072CREATE TEMPORARY TABLE t1(a INT, b INT); 1073INSERT INTO t1 VALUES (1, 2); 1074 1075delimiter |; 1076 1077CREATE PROCEDURE p1() 1078BEGIN 1079 DROP TEMPORARY TABLE t1; 1080 CREATE TEMPORARY TABLE t1(b INT, c INT); 1081 INSERT INTO t1 VALUES (2, 3); 1082END| 1083 1084CREATE PROCEDURE p2() 1085BEGIN 1086 SELECT a, b FROM t1; 1087 CALL p1(); 1088 SELECT a, b FROM t1; 1089END| 1090 1091delimiter ;| 1092 1093--echo 1094--error ER_BAD_FIELD_ERROR 1095CALL p2(); 1096--echo 1097 1098DROP PROCEDURE p1; 1099DROP PROCEDURE p2; 1100 1101DROP TEMPORARY TABLE t1; 1102 1103--echo 1104--echo # 7.10 Setup: 1105--echo # - stored program 'a', which re-creates view 'v' in an incompatible way; 1106--echo # - stored program 'b', which calls 'a' and uses 'v' before and after the 1107--echo # call; 1108--echo # Stored program 'b' must fail on access to the view after its 1109--echo # modification. 1110 1111CREATE VIEW v1 AS SELECT 1 AS a, 2 AS b; 1112 1113delimiter |; 1114 1115CREATE PROCEDURE p1() 1116BEGIN 1117 DROP VIEW v1; 1118 CREATE VIEW v1 AS SELECT 2 AS b, 3 AS c; 1119END| 1120 1121CREATE PROCEDURE p2() 1122BEGIN 1123 SELECT a, b FROM v1; 1124 CALL p1(); 1125 SELECT a, b FROM v1; 1126END| 1127 1128delimiter ;| 1129 1130--echo 1131--error ER_BAD_FIELD_ERROR 1132CALL p2(); 1133--echo 1134 1135DROP PROCEDURE p1; 1136DROP PROCEDURE p2; 1137 1138DROP VIEW v1; 1139 1140########################################################################### 1141 1142--echo # 8. Stored program must be executed successfully when: 1143--echo # a. the program uses a table/view/temporary table that doesn't exist 1144--echo # at the time of start program execution 1145--echo # b. failed reference to the missed table/view/temporary table handled 1146--echo # by stored program 1147--echo # c. this table/view/temporary table is created as part of the 1148--echo # program execution 1149--echo # d. stored program gets access to newly created table/view/temporary 1150--echo # table from some SQL-statement during subsequent stored program execution. 1151 1152delimiter |; 1153 1154CREATE PROCEDURE p1() 1155BEGIN 1156 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 1157 BEGIN 1158 SELECT 'SQLEXCEPTION caught' AS msg; 1159 CREATE TABLE t1(a INT, b INT); 1160 INSERT INTO t1 VALUES (1, 2); 1161 END; 1162 1163 SELECT * FROM t1; 1164 SELECT * FROM t1; 1165 1166 DROP TABLE t1; 1167END| 1168 1169CREATE PROCEDURE p2() 1170BEGIN 1171 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 1172 BEGIN 1173 SELECT 'SQLEXCEPTION caught' AS msg; 1174 CREATE TEMPORARY TABLE t1(a INT, b INT); 1175 INSERT INTO t1 VALUES (1, 2); 1176 END; 1177 1178 SELECT * FROM t1; 1179 SELECT * FROM t1; 1180 1181 DROP TEMPORARY TABLE t1; 1182END| 1183 1184CREATE PROCEDURE p3() 1185BEGIN 1186 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 1187 BEGIN 1188 SELECT 'SQLEXCEPTION caught' AS msg; 1189 CREATE VIEW v1 AS SELECT 1 AS a, 2 AS b; 1190 END; 1191 1192 SELECT * FROM v1; 1193 SELECT * FROM v1; 1194 1195 DROP VIEW v1; 1196END| 1197 1198delimiter ;| 1199 1200CALL p1(); 1201 1202CALL p2(); 1203 1204CALL p3(); 1205 1206DROP PROCEDURE p1; 1207DROP PROCEDURE p2; 1208DROP PROCEDURE p3; 1209 1210########################################################################### 1211 1212--echo 1213--echo # 9. Stored program must be executed successfully when 1214--echo # - the stored program has an expression in one of the following 1215--echo # statements 1216--echo # - RETURN 1217--echo # - IF 1218--echo # - CASE 1219--echo # - WHILE 1220--echo # - UNTIL 1221--echo # - SET 1222--echo # - the expression depends on the meta-data of some table/view/temporary table; 1223--echo # - the meta-data of dependent object has changed in a compatible way. 1224--echo # 1225--echo # Note, that CASE-expression must be evaluated once even if (some) 1226--echo # CASE-expressions need to be re-parsed. 1227--echo # 1228--echo # 10. Subsequent executions of a stored program must fail when 1229--echo # - the stored program has an expression in one of the following 1230--echo # statements 1231--echo # - RETURN 1232--echo # - IF 1233--echo # - CASE 1234--echo # - WHILE 1235--echo # - UNTIL 1236--echo # - SET 1237--echo # - the expression depends on the meta-data of some table/view/temporary table; 1238--echo # - the meta-data of dependent object has changed in a non-compatible way. 1239--echo # 1240--echo # Note, that CASE-expression must be evaluated once even if (some) 1241--echo # CASE-expressions need to be re-parsed. 1242 1243########################################################################### 1244 1245--echo 1246--echo # Check IF-statement. 1247--echo 1248 1249delimiter |; 1250 1251# NOTE: check also that spaces (no spaces) don't matter. 1252 1253CREATE PROCEDURE p1() 1254BEGIN 1255 IF(SELECT * FROM t1)THEN 1256 SELECT 1; 1257 ELSE 1258 SELECT 2; 1259 END IF; 1260END| 1261 1262CREATE PROCEDURE p2() 1263BEGIN 1264 DECLARE v INT DEFAULT 1; 1265 1266 IF v * (SELECT * FROM t1) THEN 1267 SELECT 1; 1268 ELSE 1269 SELECT 2; 1270 END IF; 1271END| 1272 1273CREATE FUNCTION f1() RETURNS INT 1274BEGIN 1275 IF (SELECT * FROM t1) THEN 1276 RETURN 1; 1277 ELSE 1278 RETURN 2; 1279 END IF; 1280 1281 RETURN 3; 1282END| 1283 1284CREATE FUNCTION f2() RETURNS INT 1285BEGIN 1286 DECLARE v INT DEFAULT 1; 1287 1288 IF v * (SELECT * FROM t1) THEN 1289 RETURN 1; 1290 ELSE 1291 RETURN 2; 1292 END IF; 1293 1294 RETURN 3; 1295END| 1296 1297delimiter ;| 1298 1299CREATE TABLE t1(a INT); 1300INSERT INTO t1 VALUES (1); 1301 1302--echo 1303CALL p1(); 1304CALL p2(); 1305SELECT f1(); 1306SELECT f2(); 1307--echo 1308 1309UPDATE t1 SET a = 0; 1310 1311--echo 1312CALL p1(); 1313CALL p2(); 1314SELECT f1(); 1315SELECT f2(); 1316--echo 1317 1318ALTER TABLE t1 ADD COLUMN b INT DEFAULT 1; 1319 1320--echo 1321 1322--error ER_OPERAND_COLUMNS 1323CALL p1(); 1324 1325--error ER_OPERAND_COLUMNS 1326CALL p2(); 1327 1328--error ER_OPERAND_COLUMNS 1329SELECT f1(); 1330 1331--error ER_OPERAND_COLUMNS 1332SELECT f2(); 1333 1334--echo 1335 1336ALTER TABLE t1 DROP COLUMN a; 1337 1338--echo 1339CALL p1(); 1340CALL p2(); 1341SELECT f1(); 1342SELECT f2(); 1343--echo 1344 1345DROP PROCEDURE p1; 1346DROP PROCEDURE p2; 1347DROP FUNCTION f1; 1348DROP FUNCTION f2; 1349DROP TABLE t1; 1350 1351########################################################################### 1352 1353--echo 1354--echo # Check WHILE-statement. 1355--echo 1356 1357delimiter |; 1358 1359# NOTE: check also that spaces (no spaces) don't matter. 1360 1361CREATE PROCEDURE p1(x INT) 1362BEGIN 1363 WHILE(SELECT * FROM t1)DO 1364 SELECT x; 1365 UPDATE t1 SET a = x; 1366 SET x = x - 1; 1367 END WHILE; 1368END| 1369 1370delimiter ;| 1371 1372CREATE TABLE t1(a INT); 1373INSERT INTO t1 VALUES (0); 1374 1375CALL p1(3); 1376 1377UPDATE t1 SET a = 1; 1378 1379CALL p1(3); 1380 1381UPDATE t1 SET a = 1; 1382 1383ALTER TABLE t1 ADD COLUMN b INT DEFAULT 1; 1384 1385--error ER_OPERAND_COLUMNS 1386CALL p1(3); 1387 1388ALTER TABLE t1 DROP COLUMN a; 1389 1390# Column 'a' not found for the UPDATE statememnt. 1391--error ER_BAD_FIELD_ERROR 1392CALL p1(3); 1393 1394DROP PROCEDURE p1; 1395DROP TABLE t1; 1396 1397########################################################################### 1398 1399--echo 1400--echo # Check REPEAT-statement. 1401--echo 1402 1403delimiter |; 1404 1405# NOTE: check also that spaces (no spaces) don't matter. 1406 1407CREATE PROCEDURE p1(x INT) 1408BEGIN 1409 REPEAT 1410 SELECT x; 1411 UPDATE t1 SET a = x; 1412 SET x = x - 1; 1413 UNTIL(NOT (SELECT * FROM t1))END REPEAT; 1414END| 1415 1416delimiter ;| 1417 1418CREATE TABLE t1(a INT); 1419INSERT INTO t1 VALUES (0); 1420 1421CALL p1(3); 1422 1423UPDATE t1 SET a = 1; 1424 1425CALL p1(3); 1426 1427UPDATE t1 SET a = 1; 1428 1429ALTER TABLE t1 ADD COLUMN b INT DEFAULT 1; 1430 1431--error ER_OPERAND_COLUMNS 1432CALL p1(3); 1433 1434ALTER TABLE t1 DROP COLUMN a; 1435 1436# Column 'a' not found for the UPDATE statememnt. 1437--error ER_BAD_FIELD_ERROR 1438CALL p1(3); 1439 1440DROP PROCEDURE p1; 1441DROP TABLE t1; 1442 1443########################################################################### 1444 1445--echo 1446--echo # Check CASE-statement (round #1). 1447--echo 1448 1449delimiter |; 1450 1451# Check that metadata changes in WHEN-expressions are handled properly. 1452CREATE PROCEDURE p1() 1453BEGIN 1454 CASE 1455 WHEN (SELECT * FROM t1) = 1 THEN SELECT 'a1'; 1456 WHEN (SELECT * FROM t1) = 2 THEN SELECT 'a2'; 1457 WHEN (SELECT * FROM t1) = 3 THEN SELECT 'a3'; 1458 ELSE SELECT 'a4'; 1459 END CASE; 1460END| 1461 1462# Check that metadata changes in CASE-expression, are handled properly. 1463CREATE PROCEDURE p2() 1464BEGIN 1465 CASE (SELECT * FROM t1) 1466 WHEN 1 THEN SELECT 'a1'; 1467 WHEN 2 THEN SELECT 'a2'; 1468 WHEN 3 THEN SELECT 'a3'; 1469 ELSE SELECT 'a4'; 1470 END CASE; 1471END| 1472 1473delimiter ;| 1474 1475CREATE TABLE t1(a INT); 1476INSERT INTO t1 VALUES (0); 1477 1478--echo 1479 1480CALL p1(); 1481CALL p2(); 1482 1483--echo 1484 1485UPDATE t1 SET a = 3; 1486 1487--echo 1488 1489CALL p1(); 1490CALL p2(); 1491 1492--echo 1493 1494ALTER TABLE t1 ADD COLUMN b INT DEFAULT 2; 1495 1496--echo 1497 1498--error ER_OPERAND_COLUMNS 1499CALL p1(); 1500 1501--error ER_OPERAND_COLUMNS 1502CALL p2(); 1503 1504--echo 1505 1506ALTER TABLE t1 DROP COLUMN a; 1507 1508--echo 1509 1510CALL p1(); 1511CALL p2(); 1512 1513--echo 1514 1515DROP PROCEDURE p1; 1516DROP PROCEDURE p2; 1517DROP TABLE t1; 1518 1519########################################################################### 1520 1521--echo 1522--echo # Check CASE-statement (round #2). 1523--echo # 1524--echo # Check that CASE-expression is executed once even if the metadata, used 1525--echo # in a WHEN-expression, have changed. 1526--echo 1527 1528CREATE TABLE t1(a INT); 1529CREATE TABLE t2(a INT); 1530 1531INSERT INTO t1 VALUES (1); 1532INSERT INTO t2 VALUES (1); 1533 1534delimiter |; 1535 1536CREATE FUNCTION f1() RETURNS INT 1537BEGIN 1538 SET @x = @x + 1; 1539 RETURN (SELECT a FROM t1); 1540END| 1541 1542CREATE PROCEDURE p1() 1543BEGIN 1544 CASE f1() 1545 WHEN 1 THEN SELECT 'a1'; 1546 WHEN 2 THEN SELECT 'a2'; 1547 WHEN (SELECT * FROM t2) THEN SELECT 'subselect'; 1548 ELSE SELECT 'else'; 1549 END CASE; 1550END| 1551 1552delimiter ;| 1553 1554--echo 1555 1556SET @x = 0; 1557CALL p1(); 1558SELECT @x; 1559 1560--echo 1561 1562UPDATE t1 SET a = 3; 1563ALTER TABLE t2 ADD COLUMN b INT DEFAULT 3; 1564 1565--echo 1566 1567SET @x = 0; 1568--error ER_OPERAND_COLUMNS 1569CALL p1(); 1570SELECT @x; 1571 1572--echo 1573 1574ALTER TABLE t2 DROP COLUMN a; 1575 1576--echo 1577 1578SET @x = 0; 1579CALL p1(); 1580SELECT @x; 1581 1582--echo 1583 1584DROP PROCEDURE p1; 1585DROP FUNCTION f1; 1586DROP TABLE t1; 1587DROP TABLE t2; 1588 1589########################################################################### 1590 1591--echo 1592--echo # Check DEFAULT clause. 1593--echo # 1594--echo 1595 1596CREATE TABLE t1(a INT); 1597INSERT INTO t1 VALUES (1); 1598 1599delimiter |; 1600 1601CREATE PROCEDURE p1() 1602BEGIN 1603 DECLARE v INT DEFAULT (SELECT * FROM t1); 1604 SELECT v; 1605END| 1606 1607delimiter ;| 1608 1609--echo 1610CALL p1(); 1611 1612--echo 1613 1614ALTER TABLE t1 ADD COLUMN b INT DEFAULT 2; 1615 1616--echo 1617--error ER_OPERAND_COLUMNS 1618CALL p1(); 1619 1620--echo 1621ALTER TABLE t1 DROP COLUMN a; 1622 1623--echo 1624CALL p1(); 1625 1626--echo 1627DROP PROCEDURE p1; 1628DROP TABLE t1; 1629 1630########################################################################### 1631 1632--echo 1633--echo # Check SET. 1634--echo # 1635--echo 1636 1637CREATE TABLE t1(a INT); 1638INSERT INTO t1 VALUES (1); 1639 1640CREATE TABLE t2(a INT); 1641INSERT INTO t2 VALUES (1); 1642 1643delimiter |; 1644 1645# Check SET for SP-variable. 1646 1647CREATE PROCEDURE p1() 1648BEGIN 1649 DECLARE x INT; 1650 SET x = (SELECT * FROM t1); 1651 SELECT x; 1652END| 1653 1654# Check SET for user variable. 1655 1656CREATE PROCEDURE p2() 1657BEGIN 1658 SET @x = NULL; 1659 SET @x = (SELECT * FROM t1); 1660 SELECT @x; 1661END| 1662 1663# Check SET for triggers. 1664 1665CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW 1666BEGIN 1667 SET NEW.a = (SELECT * FROM t1) * 2; 1668END| 1669 1670delimiter ;| 1671 1672--echo 1673CALL p1(); 1674--echo 1675CALL p2(); 1676--echo 1677UPDATE t2 SET a = 10; 1678 1679--echo 1680 1681ALTER TABLE t1 ADD COLUMN b INT DEFAULT 2; 1682 1683--echo 1684--error ER_OPERAND_COLUMNS 1685CALL p1(); 1686--echo 1687--error ER_OPERAND_COLUMNS 1688CALL p2(); 1689--echo 1690--error ER_OPERAND_COLUMNS 1691UPDATE t2 SET a = 20; 1692 1693--echo 1694ALTER TABLE t1 DROP COLUMN a; 1695 1696--echo 1697CALL p1(); 1698--echo 1699CALL p2(); 1700--echo 1701UPDATE t2 SET a = 30; 1702 1703--echo 1704DROP PROCEDURE p1; 1705DROP PROCEDURE p2; 1706DROP TABLE t1; 1707DROP TABLE t2; 1708 1709########################################################################### 1710 1711--echo 1712--echo # 11.1 If metadata of the objects (regular tables, temporary tables, 1713--echo # views), used in SELECT-statement changed between DECLARE CURSOR and 1714--echo # OPEN statements, the SELECT-statement should be re-parsed to use 1715--echo # up-to-date metadata. 1716--echo 1717--echo 1718--echo # - Regular table. 1719--echo 1720 1721CREATE TABLE t1(a INT); 1722INSERT INTO t1 VALUES (1); 1723 1724delimiter |; 1725 1726CREATE PROCEDURE p1() 1727BEGIN 1728 DECLARE v INT; 1729 DECLARE c CURSOR FOR SELECT * FROM t1; 1730 1731 ALTER TABLE t1 ADD COLUMN b INT DEFAULT 2; 1732 ALTER TABLE t1 DROP COLUMN a; 1733 1734 OPEN c; 1735 FETCH c INTO v; 1736 CLOSE c; 1737 1738 SELECT v; 1739END| 1740 1741delimiter ;| 1742 1743--echo 1744CALL p1(); 1745 1746--echo 1747DROP TABLE t1; 1748DROP PROCEDURE p1; 1749 1750--echo 1751--echo # - Temporary table. 1752--echo 1753 1754CREATE TEMPORARY TABLE t1(a INT); 1755INSERT INTO t1 VALUES (1); 1756 1757delimiter |; 1758 1759CREATE PROCEDURE p1() 1760BEGIN 1761 DECLARE v INT; 1762 DECLARE c CURSOR FOR SELECT * FROM t1; 1763 1764 ALTER TABLE t1 ADD COLUMN b INT DEFAULT 2; 1765 ALTER TABLE t1 DROP COLUMN a; 1766 1767 OPEN c; 1768 FETCH c INTO v; 1769 CLOSE c; 1770 1771 SELECT v; 1772END| 1773 1774delimiter ;| 1775 1776--echo 1777CALL p1(); 1778 1779--echo 1780DROP TEMPORARY TABLE t1; 1781DROP PROCEDURE p1; 1782 1783# NOTE: ALTER VIEW is not available within Stored Programs. 1784 1785--echo 1786--echo # 11.2 If the metadata changed between OPEN and FETCH or CLOSE 1787--echo # statements, those changes should not be noticed. 1788--echo 1789 1790CREATE TABLE t1(a INT); 1791INSERT INTO t1 VALUES (1); 1792 1793delimiter |; 1794 1795CREATE PROCEDURE p1() 1796BEGIN 1797 DECLARE v INT; 1798 DECLARE c CURSOR FOR SELECT * FROM t1; 1799 1800 OPEN c; 1801 1802 ALTER TABLE t1 ADD COLUMN b INT DEFAULT 2; 1803 ALTER TABLE t1 DROP COLUMN a; 1804 1805 FETCH c INTO v; 1806 CLOSE c; 1807 1808 SELECT v; 1809END| 1810 1811delimiter ;| 1812 1813--echo 1814CALL p1(); 1815 1816--echo 1817DROP TABLE t1; 1818DROP PROCEDURE p1; 1819 1820--echo 1821--echo # 11.3 Re-parsing of the SELECT-statement should be made correctly 1822--echo # (in the correct parsing context) if the metadata changed between 1823--echo # DECLARE CURSOR and OPEN statements, and those statements reside in different 1824--echo # parsing contexts. 1825--echo 1826 1827CREATE TABLE t1(a INT); 1828INSERT INTO t1 VALUES (1); 1829 1830delimiter |; 1831 1832CREATE PROCEDURE p1() 1833BEGIN 1834 DECLARE f1 INT; 1835 DECLARE f2 INT; 1836 DECLARE f3 INT; 1837 1838 DECLARE x INT DEFAULT 1; 1839 DECLARE y INT DEFAULT 2; 1840 1841 DECLARE c CURSOR FOR SELECT x, y, t1.a FROM t1; 1842 1843 ALTER TABLE t1 ADD COLUMN b INT; 1844 1845 BEGIN 1846 DECLARE x INT DEFAULT 10; 1847 DECLARE y INT DEFAULT 20; 1848 1849 OPEN c; 1850 1851 FETCH c INTO f1, f2, f3; 1852 SELECT f1, f2, f3; 1853 1854 CLOSE c; 1855 END; 1856 1857END| 1858 1859delimiter ;| 1860 1861--echo 1862CALL p1(); 1863 1864--echo 1865DROP TABLE t1; 1866DROP PROCEDURE p1; 1867 1868########################################################################### 1869## Other tests. 1870########################################################################### 1871 1872--echo 1873--echo # Test procedure behaviour after view recreation. 1874CREATE TABLE t1 (a INT); 1875INSERT INTO t1 VALUES (1), (2); 1876 1877CREATE VIEW v1 AS SELECT * FROM t1; 1878 1879CREATE PROCEDURE p1() 1880 SELECT * FROM v1; 1881 1882CALL p1(); 1883 1884--echo # Alter underlying table and recreate the view. 1885ALTER TABLE t1 ADD COLUMN (b INT); 1886ALTER VIEW v1 AS SELECT * FROM t1; 1887 1888--echo # And check whether the call of stored procedure handles it correctly. 1889CALL p1(); 1890 1891DROP VIEW v1; 1892DROP TABLE t1; 1893DROP PROCEDURE p1; 1894 1895--echo # Test if metadata changes for temporary table is handled 1896--echo # correctly inside a stored procedure. 1897CREATE TEMPORARY TABLE t1 (a INT); 1898INSERT INTO t1 VALUES (1), (2); 1899 1900CREATE PROCEDURE p1() 1901 SELECT * FROM t1; 1902 1903CALL p1(); 1904 1905--echo # Test if added temporary table's column is recognized during 1906--echo # procedure invocation. 1907ALTER TABLE t1 ADD COLUMN (b INT); 1908CALL p1(); 1909 1910--echo # Test if dropped temporary table's column is not appeared 1911--echo # in procedure's result. 1912ALTER TABLE t1 DROP COLUMN a; 1913CALL p1(); 1914 1915DROP PROCEDURE p1; 1916DROP TABLE t1; 1917 1918--echo # Test handle of metadata changes with stored function. 1919 1920CREATE TABLE t1 (a INT); 1921INSERT INTO t1 VALUES (1), (2); 1922 1923delimiter |; 1924 1925CREATE FUNCTION f1() RETURNS INT 1926BEGIN 1927 CREATE TEMPORARY TABLE t1_result_set AS SELECT * FROM t1; 1928 1929 RETURN 0; 1930END| 1931 1932delimiter ;| 1933 1934SELECT f1(); 1935SELECT * FROM t1_result_set; 1936DROP TABLE t1_result_set; 1937 1938--echo # Check if added column is noticed by invocation of stored function. 1939ALTER TABLE t1 ADD COLUMN (b INT); 1940 1941SELECT f1(); 1942SELECT * FROM t1_result_set; 1943DROP TABLE t1_result_set; 1944 1945--echo # Check if dropped column is noticed by invocation of stored function. 1946ALTER TABLE t1 DROP COLUMN a; 1947 1948SELECT f1(); 1949SELECT * FROM t1_result_set; 1950DROP TABLE t1_result_set; 1951 1952DROP TABLE t1; 1953DROP FUNCTION f1; 1954 1955--echo # Test if table's recreation is handled correctly 1956--echo # inside a stored function. 1957 1958CREATE TABLE t1 (a INT); 1959INSERT INTO t1 VALUES (1), (2); 1960 1961delimiter |; 1962 1963CREATE FUNCTION f1() RETURNS INT 1964BEGIN 1965 CREATE TEMPORARY TABLE t1_result_set AS SELECT * FROM t1; 1966 1967 RETURN 0; 1968END| 1969 1970delimiter ;| 1971 1972SELECT f1(); 1973SELECT * FROM t1_result_set; 1974DROP TABLE t1_result_set; 1975 1976--echo # Recreate table and check if it is handled correctly 1977--echo # by function invocation. 1978DROP TABLE t1; 1979CREATE TABLE t1 (a INT); 1980INSERT INTO t1 VALUES (1), (2); 1981 1982SELECT f1(); 1983SELECT * FROM t1_result_set; 1984DROP TABLE t1_result_set; 1985 1986DROP FUNCTION f1; 1987DROP TABLE t1; 1988 1989--echo # Test if changes in the view's metadata is handled 1990--echo # correctly by function call. 1991CREATE TABLE t1 (a INT); 1992INSERT INTO t1 VALUES (1), (2); 1993CREATE VIEW v1 AS SELECT * FROM t1; 1994 1995delimiter |; 1996 1997CREATE FUNCTION f1() RETURNS INT 1998BEGIN 1999 CREATE TEMPORARY TABLE t1_result_set AS SELECT * FROM v1; 2000 RETURN 0; 2001END| 2002 2003delimiter ;| 2004 2005SELECT f1(); 2006SELECT * FROM t1_result_set; 2007DROP TABLE t1_result_set; 2008 2009ALTER TABLE t1 ADD COLUMN (b INT); 2010ALTER VIEW v1 AS SELECT * FROM t1; 2011 2012SELECT f1(); 2013SELECT * FROM t1_result_set; 2014DROP TABLE t1_result_set; 2015DROP TABLE t1; 2016DROP VIEW v1; 2017 2018DROP FUNCTION f1; 2019 2020--echo # Check if queried object's type substitution (table->view, view->table, 2021--echo # table->temp table, etc.) is handled correctly during invocation of 2022--echo # stored function/procedure. 2023CREATE TABLE t1 (a INT); 2024INSERT INTO t1 VALUES (1), (2); 2025 2026delimiter |; 2027 2028CREATE FUNCTION f1() RETURNS INT 2029BEGIN 2030 CREATE TEMPORARY TABLE t1_result_set AS SELECT * FROM t1; 2031 RETURN 0; 2032END| 2033 2034CREATE PROCEDURE p1() 2035 SELECT * FROM t1| 2036 2037delimiter ;| 2038 2039CALL p1(); 2040SELECT f1(); 2041SELECT * FROM t1_result_set; 2042DROP TABLE t1_result_set; 2043 2044DROP TABLE t1; 2045CREATE TEMPORARY TABLE t1 (a INT); 2046INSERT INTO t1 VALUES (1), (2); 2047 2048CALL p1; 2049SELECT f1(); 2050SELECT * FROM t1_result_set; 2051DROP TABLE t1_result_set; 2052 2053DROP TABLE t1; 2054CREATE TABLE t2 (a INT); 2055INSERT INTO t2 VALUES (1), (2); 2056CREATE VIEW t1 AS SELECT * FROM t2; 2057 2058CALL p1; 2059SELECT f1(); 2060SELECT * FROM t1_result_set; 2061DROP TABLE t1_result_set; 2062 2063DROP TABLE t2; 2064DROP VIEW t1; 2065 2066DROP FUNCTION f1; 2067DROP PROCEDURE p1; 2068 2069--echo # Test handle of metadata changes with triggers. 2070 2071CREATE TABLE t1 (a INT); 2072 2073CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW 2074 SET new.a = new.a + 100; 2075 2076INSERT INTO t1 VALUES (1), (2); 2077SELECT * FROM t1; 2078 2079--echo # Check if added table's column is handled correctly inside trigger. 2080ALTER TABLE t1 ADD COLUMN (b INT); 2081INSERT INTO t1 VALUES (3, 4); 2082 2083SELECT * FROM t1; 2084 2085DROP TRIGGER trg1; 2086DROP TABLE t1; 2087 2088--echo # Test if deleted column is handled correctly by trigger invocation. 2089CREATE TABLE t1 (a INT, b INT); 2090 2091CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW 2092 SET new.a = new.a + 100; 2093 2094INSERT INTO t1 VALUES (1, 2), (3, 4); 2095SELECT * FROM t1; 2096 2097ALTER TABLE t1 DROP COLUMN b; 2098 2099INSERT INTO t1 VALUES (5); 2100SELECT * FROM t1; 2101 2102DROP TRIGGER trg1; 2103DROP TABLE t1; 2104 2105--echo # Check if server returns and error when was dropped a column 2106--echo # that is used inside a trigger body. 2107CREATE TABLE t1 (a INT, b INT); 2108 2109CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW 2110 SET new.a = new.a + 100; 2111 2112INSERT INTO t1 VALUES (1, 2), (3, 4); 2113SELECT * FROM t1; 2114 2115ALTER TABLE t1 DROP COLUMN a; 2116 2117--error ER_BAD_FIELD_ERROR 2118INSERT INTO t1 VALUES (5); 2119 2120DROP TRIGGER trg1; 2121DROP TABLE t1; 2122 2123--echo 2124--echo # Check updateable views inside triggers. 2125 2126CREATE TABLE t1(a INT); 2127INSERT INTO t1 VALUES (1); 2128 2129CREATE TABLE t2(a INT); 2130INSERT INTO t2 VALUES (1); 2131 2132CREATE VIEW v1 AS SELECT a FROM t1; 2133 2134delimiter |; 2135 2136CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW 2137BEGIN 2138 INSERT INTO v1 VALUES (NEW.a); 2139 SET @x = (SELECT CHARSET(a) FROM v1 LIMIT 1); 2140END| 2141 2142delimiter ;| 2143 2144--echo 2145SET @x = NULL; 2146 2147UPDATE t2 SET a = 10; 2148 2149SELECT * FROM v1; 2150SELECT @x; 2151 2152--echo 2153ALTER TABLE t1 CHANGE COLUMN a a CHAR(2); 2154 2155--echo 2156SET @x = NULL; 2157 2158UPDATE t2 SET a = 20; 2159 2160SELECT * FROM v1; 2161SELECT @x; 2162 2163--echo 2164DROP TABLE t1; 2165DROP TABLE t2; 2166DROP VIEW v1; 2167