1 2# 3# WL#4179: Stored programs: validation of stored program statements. 4# 5 6# The test case below demonstrates that meta-data changes are detected 7# by triggers. 8CREATE TABLE t1 (a INT, b INT); 9CREATE TABLE t2 (a INT, b INT); 10CREATE TABLE t3 (a INT); 11INSERT INTO t2 VALUES (11, 12), (21, 22); 12CREATE TRIGGER t3_ai AFTER INSERT ON t3 FOR EACH ROW 13INSERT INTO t1 SELECT * FROM t2; 14INSERT INTO t3 (a) VALUES (1); 15SELECT * FROM t1; 16a b 1711 12 1821 22 19SELECT * FROM t2; 20a b 2111 12 2221 22 23ALTER TABLE t1 ADD COLUMN c INT; 24ALTER TABLE t2 ADD COLUMN c INT; 25INSERT INTO t2 VALUES (31, 32, 33); 26INSERT INTO t3 (a) VALUES (2); 27SELECT * FROM t1; 28a b c 2911 12 NULL 3021 22 NULL 3111 12 NULL 3221 22 NULL 3331 32 33 34SELECT * FROM t2; 35a b c 3611 12 NULL 3721 22 NULL 3831 32 33 39DROP TABLE t1; 40DROP TABLE t2; 41DROP TABLE t3; 42 43# Check that NEW/OLD rows work within triggers. 44 45CREATE TABLE t1 (a INT); 46INSERT INTO t1(a) VALUES (1); 47CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW 48BEGIN 49SET @a = OLD.a; 50SET @b = NEW.a; 51SELECT OLD.a INTO @c; 52SELECT NEW.a INTO @d; 53SET NEW.a = NEW.a * 2; 54END| 55UPDATE t1 SET a = a * 10; 56SELECT @a, @c, @b, @d; 57@a @c @b @d 581 1 10 10 59SELECT a FROM t1; 60a 6120 62DROP TABLE t1; 63 64CREATE TABLE t1 (a INT); 65INSERT INTO t1 VALUES (1), (2); 66CREATE PROCEDURE p1() 67SELECT * FROM t1; 68CALL p1(); 69a 701 712 72 73# 1.1 Check if added column into table is recognized correctly 74# in a stored procedure. 75ALTER TABLE t1 ADD COLUMN b INT DEFAULT 0; 76CALL p1(); 77a b 781 0 792 0 80 81# 1.2 Check if dropped column is not appeared in SELECT query 82# executed inside a stored procedure. 83ALTER TABLE t1 DROP COLUMN a; 84CALL p1(); 85b 860 870 88 89# 1.3 Check if changed column is picked up properly. 90ALTER TABLE t1 CHANGE COLUMN b b VARCHAR(255) DEFAULT 'n/a'; 91DELETE FROM t1; 92INSERT INTO t1 VALUES (b), ('hello'); 93CALL p1(); 94b 95n/a 96hello 97 98# 1.4 Check if table's recreation is handled correctly 99# inside a call of stored procedure. 100DROP TABLE t1; 101DROP PROCEDURE p1; 102CREATE TABLE t1 (a INT); 103INSERT INTO t1 VALUES (1), (2); 104CREATE PROCEDURE p1() 105SELECT * FROM t1; 106CALL p1(); 107a 1081 1092 110DROP TABLE t1; 111CALL p1(); 112ERROR 42S02: Table 'test.t1' doesn't exist 113CREATE TABLE t1 (a INT); 114INSERT INTO t1 VALUES (1), (2); 115CALL p1(); 116a 1171 1182 119 120# 1.5 Recreate table t1 with another set of columns and 121# re-call a stored procedure. 122DROP TABLE t1; 123DROP PROCEDURE p1; 124CREATE TABLE t1 (a INT); 125INSERT INTO t1 VALUES (1), (2); 126CREATE PROCEDURE p1() 127SELECT * FROM t1; 128CALL p1(); 129a 1301 1312 132DROP TABLE t1; 133CALL p1(); 134ERROR 42S02: Table 'test.t1' doesn't exist 135CREATE TABLE t1 (b VARCHAR(10), c VARCHAR(10)); 136INSERT INTO t1 VALUES ('a', 'b'), ('c', 'd'); 137CALL p1(); 138b c 139a b 140c d 141DROP TABLE t1; 142DROP PROCEDURE p1; 143 144# 2.1 Stored program that uses query like 'SELECT * FROM v' must be 145# re-executed successfully if some columns were added into the view 146# definition by ALTER VIEW; 147CREATE VIEW v1 AS SELECT 1, 2, 3; 148CREATE PROCEDURE p1() 149SELECT * FROM v1; 150CALL p1(); 1511 2 3 1521 2 3 153ALTER VIEW v1 AS SELECT 1, 2, 3, 4, 5; 154CALL p1(); 1551 2 3 4 5 1561 2 3 4 5 157 158# 2.2 Stored program that uses query like 'SELECT * FROM v' must be 159# re-executed successfully if some columns were removed from the view 160# definition by ALTER VIEW; 161ALTER VIEW v1 AS SELECT 1, 5; 162CALL p1(); 1631 5 1641 5 165 166# 2.3 Stored program that uses query like 'SELECT * FROM v' must be 167# re-executed successfully if a base table for the view being used was 168# extended by new columns (by ALTER TABLE); 169CREATE TABLE t1(a INT, b INT); 170INSERT INTO t1 VALUES (1, 2); 171DROP VIEW v1; 172CREATE VIEW v1 AS SELECT * FROM t1; 173DROP PROCEDURE p1; 174CREATE PROCEDURE p1() 175SELECT * FROM v1; 176CALL p1(); 177a b 1781 2 179ALTER TABLE t1 ADD COLUMN c INT DEFAULT 3; 180CALL p1(); 181a b 1821 2 183 184# 2.4 Stored program that uses query like 'SELECT * FROM v' must be 185# re-executed successfully if not used columns were removed from the 186# base table of this view (by ALTER TABLE); 187DROP TABLE t1; 188CREATE TABLE t1(a INT, b INT, c INT); 189INSERT INTO t1 VALUES (1, 2, 3); 190DROP VIEW v1; 191CREATE VIEW v1 AS SELECT b, c FROM t1; 192DROP PROCEDURE p1; 193CREATE PROCEDURE p1() 194SELECT * FROM v1; 195CALL p1(); 196b c 1972 3 198ALTER TABLE t1 DROP COLUMN a; 199CALL p1(); 200b c 2012 3 202ALTER TABLE t1 DROP COLUMN b; 203CALL p1(); 204ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 205 206# 2.5 Stored program that uses query like 'SELECT * FROM v' must be 207# re-executed successfully if a type of some base table's columns were 208# changed (by ALTER TABLE); 209DROP TABLE t1; 210CREATE TABLE t1(a INT, b INT, c INT); 211INSERT INTO t1 VALUES (1, 2, 3); 212DROP VIEW v1; 213CREATE VIEW v1 AS SELECT b, c FROM t1; 214DROP PROCEDURE p1; 215CREATE PROCEDURE p1() 216SELECT * FROM v1; 217CALL p1(); 218b c 2192 3 220ALTER TABLE t1 CHANGE COLUMN b b VARCHAR(255) DEFAULT 'n/a'; 221DELETE FROM t1; 222INSERT INTO t1(a, c) VALUES (10, 30); 223CALL p1(); 224b c 225n/a 30 226 227# 2.6 Stored program that uses query like 'SELECT * FROM v' must be 228# re-executed successfully if the view 'v' was dropped and created again 229# with the same definition; 230# 231# 2.7 Stored program that uses query like 'SELECT * FROM v' must be 232# re-executed successfully if the view 'v' was dropped and created again 233# with different, but compatible definition. 234DROP VIEW v1; 235DROP TABLE t1; 236DROP PROCEDURE p1; 237CREATE VIEW v1 AS SELECT 1, 2, 3; 238CREATE PROCEDURE p1() 239SELECT * FROM v1; 240CALL p1(); 2411 2 3 2421 2 3 243DROP VIEW v1; 244CALL p1(); 245ERROR 42S02: Table 'test.v1' doesn't exist 246CREATE VIEW v1 AS SELECT 4, 5, 6; 247CALL p1(); 2484 5 6 2494 5 6 250 251# 2.8 Stored program that uses query like 'SELECT * FROM v' must be 252# re-executed successfully if the view base tables have been re-created 253# using the same or compatible definition. 254DROP VIEW v1; 255DROP PROCEDURE p1; 256CREATE TABLE t1(a INT, b INT); 257INSERT INTO t1 VALUES (1, 2); 258CREATE VIEW v1 AS SELECT * FROM t1; 259CREATE PROCEDURE p1() 260SELECT * FROM v1; 261CALL p1(); 262a b 2631 2 264DROP TABLE t1; 265CALL p1(); 266ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 267CREATE TABLE t1(a VARCHAR(255), b VARCHAR(255)); 268INSERT INTO t1 VALUES ('a', 'b'), ('c', 'd'); 269CALL p1(); 270a b 271a b 272c d 273DROP VIEW v1; 274DROP TABLE t1; 275DROP PROCEDURE p1; 276 277# 3.1 Stored program that uses query like 'SELECT * FROM t' must be 278# re-executed successfully if some columns were added into temporary table 279# table 't' (by ALTER TABLE); 280CREATE TEMPORARY TABLE t1(a INT, b INT); 281INSERT INTO t1 VALUES (1, 2); 282CREATE PROCEDURE p1() SELECT * FROM t1; 283CALL p1(); 284a b 2851 2 286ALTER TABLE t1 ADD COLUMN c INT DEFAULT 3; 287CALL p1(); 288a b c 2891 2 3 290 291# 3.2 Stored program that uses query like 'SELECT * FROM t' must be 292# re-executed successfully if some columns were removed from temporary 293# table 't' (by ALTER TABLE); 294ALTER TABLE t1 DROP COLUMN a; 295CALL p1(); 296b c 2972 3 298 299# 3.3 Stored program that uses query like 'SELECT * FROM t' must be 300# re-executed successfully if a type of some temporary table's columns were 301# changed (by ALTER TABLE); 302ALTER TABLE t1 CHANGE COLUMN b b VARCHAR(255) DEFAULT 'n/a'; 303INSERT INTO t1(c) VALUES (4); 304CALL p1(); 305b c 3062 3 307n/a 4 308 309# 3.4 Stored program that uses query like 'SELECT * FROM t' must be 310# re-executed successfully if the temporary table 't' was dropped and 311# created again with the same definition; 312# 313# 3.5 Stored program that uses query like 'SELECT * FROM t' must be 314# re-executed successfully if the temporary table 't' was dropped and 315# created again with different, but compatible definition. 316DROP TEMPORARY TABLE t1; 317CREATE TEMPORARY TABLE t1(a INT, b INT); 318INSERT INTO t1 VALUES (1, 2); 319CALL p1(); 320a b 3211 2 322DROP TEMPORARY TABLE t1; 323CREATE TEMPORARY TABLE t1(a VARCHAR(255), b VARCHAR(255), c VARCHAR(255)); 324INSERT INTO t1 VALUES ('aa', 'bb', 'cc'); 325CALL p1(); 326a b c 327aa bb cc 328DROP TEMPORARY TABLE t1; 329DROP PROCEDURE p1; 330 331# 4.1 Stored program must fail when it is re-executed after a table's column 332# that this program is referenced to has been removed; 333CREATE TABLE t1(a INT, b INT); 334INSERT INTO t1 VALUES (1, 2); 335CREATE PROCEDURE p1() SELECT a, b FROM t1; 336CALL p1(); 337a b 3381 2 339ALTER TABLE t1 DROP COLUMN b; 340CALL p1(); 341ERROR 42S22: Unknown column 'b' in 'field list' 342DROP PROCEDURE p1; 343DROP TABLE t1; 344 345# 4.2 Stored program must fail when it is re-executed after a temporary 346# table's column that this program is referenced to has been removed; 347CREATE TEMPORARY TABLE t1(a INT, b INT); 348INSERT INTO t1 VALUES (1, 2); 349CREATE PROCEDURE p1() SELECT a, b FROM t1; 350CALL p1(); 351a b 3521 2 353ALTER TABLE t1 DROP COLUMN b; 354CALL p1(); 355ERROR 42S22: Unknown column 'b' in 'field list' 356DROP PROCEDURE p1; 357DROP TEMPORARY TABLE t1; 358 359# 4.3 Stored program must fail when it is re-executed after a view's 360# column that this program is referenced to has been removed; 361CREATE VIEW v1 AS SELECT 1 AS a, 2 AS b; 362CREATE PROCEDURE p1() SELECT a, b FROM v1; 363CALL p1(); 364a b 3651 2 366ALTER VIEW v1 AS SELECT 1 AS a; 367CALL p1(); 368ERROR 42S22: Unknown column 'b' in 'field list' 369DROP PROCEDURE p1; 370DROP VIEW v1; 371 372# 4.4 Stored program must fail when it is re-executed after a regular table 373# that this program referenced to was removed; 374CREATE TABLE t1(a INT, b INT); 375INSERT INTO t1 VALUES (1, 2); 376CREATE PROCEDURE p1() SELECT a, b FROM t1; 377CALL p1(); 378a b 3791 2 380DROP TABLE t1; 381CALL p1(); 382ERROR 42S02: Table 'test.t1' doesn't exist 383DROP PROCEDURE p1; 384 385# 4.5 Stored program must fail when it is re-executed after a view that 386# this program referenced to was removed; 387CREATE VIEW v1 AS SELECT 1 AS a, 2 AS b; 388CREATE PROCEDURE p1() SELECT a, b FROM v1; 389CALL p1(); 390a b 3911 2 392DROP VIEW v1; 393CALL p1(); 394ERROR 42S02: Table 'test.v1' doesn't exist 395DROP PROCEDURE p1; 396 397# 4.6 Stored program must fail when it is re-executed after a temporary 398# table that this program referenced to was removed; 399CREATE TEMPORARY TABLE t1(a INT, b INT); 400INSERT INTO t1 VALUES (1, 2); 401CREATE PROCEDURE p1() SELECT a, b FROM t1; 402CALL p1(); 403a b 4041 2 405DROP TABLE t1; 406CALL p1(); 407ERROR 42S02: Table 'test.t1' doesn't exist 408DROP PROCEDURE p1; 409 410# 4.7 Stored program must fail if the program executes some 411# SQL-statement and afterwards re-executes it again when some table 't' 412# referenced by the statement was dropped in the period between statement 413# execution; 414CREATE TABLE t1(a INT); 415CREATE TABLE t2(a INT); 416CREATE PROCEDURE p1() 417BEGIN 418DECLARE CONTINUE HANDLER FOR 1146 419SELECT 'Table t1 does not exist anymore' as msg; 420SELECT * FROM t1; 421INSERT INTO t2 VALUES (1); 422SELECT GET_LOCK('m1', 10000); 423SELECT * FROM t1; 424END| 425 426# -- connection: con1 427SELECT GET_LOCK('m1', 0); 428GET_LOCK('m1', 0) 4291 430 431# -- connection: default 432CALL p1(); 433 434# -- connection: con1 435DROP TABLE t1; 436SELECT RELEASE_LOCK('m1'); 437RELEASE_LOCK('m1') 4381 439 440# -- connection: default 441a 442GET_LOCK('m1', 10000) 4431 444msg 445Table t1 does not exist anymore 446DROP TABLE t2; 447DROP PROCEDURE p1; 448 449# 5.1 Regular table -> View 450CREATE TABLE t1(a INT, b INT); 451INSERT INTO t1 VALUES (1, 2); 452CREATE PROCEDURE p1() SELECT * FROM t1; 453CALL p1(); 454a b 4551 2 456DROP TABLE t1; 457CREATE VIEW t1 AS SELECT 1 AS a, 2 AS b; 458CALL p1(); 459a b 4601 2 461DROP PROCEDURE p1; 462DROP VIEW t1; 463 464# 5.2 Regular table -> Temporary table 465CREATE TABLE t1(a INT, b INT); 466INSERT INTO t1 VALUES (1, 2); 467CREATE PROCEDURE p1() SELECT * FROM t1; 468CALL p1(); 469a b 4701 2 471DROP TABLE t1; 472CREATE TEMPORARY TABLE t1(a INT, b INT); 473INSERT INTO t1 VALUES (1, 2); 474CALL p1(); 475a b 4761 2 477DROP PROCEDURE p1; 478DROP TEMPORARY TABLE t1; 479 480# 5.3 View -> Regular table 481CREATE VIEW t1 AS SELECT 1 AS a, 2 AS b; 482CREATE PROCEDURE p1() SELECT * FROM t1; 483CALL p1(); 484a b 4851 2 486DROP VIEW t1; 487CREATE TABLE t1(a INT, b INT); 488INSERT INTO t1 VALUES (1, 2); 489CALL p1(); 490a b 4911 2 492DROP PROCEDURE p1; 493DROP TABLE t1; 494 495# 5.4 View -> Temporary table 496CREATE VIEW t1 AS SELECT 1 AS a, 2 AS b; 497CREATE PROCEDURE p1() SELECT * FROM t1; 498CALL p1(); 499a b 5001 2 501DROP VIEW t1; 502CREATE TEMPORARY TABLE t1(a INT, b INT); 503INSERT INTO t1 VALUES (1, 2); 504CALL p1(); 505a b 5061 2 507DROP PROCEDURE p1; 508DROP TEMPORARY TABLE t1; 509 510# 5.5 Temporary table -> View 511CREATE TEMPORARY TABLE t1(a INT, b INT); 512INSERT INTO t1 VALUES (1, 2); 513CREATE PROCEDURE p1() SELECT * FROM t1; 514CALL p1(); 515a b 5161 2 517DROP TEMPORARY TABLE t1; 518CREATE VIEW t1 AS SELECT 1 AS a, 2 AS b; 519CALL p1(); 520a b 5211 2 522DROP PROCEDURE p1; 523DROP VIEW t1; 524 525# 5.6 Temporary table -> Regular table 526CREATE TEMPORARY TABLE t1(a INT, b INT); 527INSERT INTO t1 VALUES (1, 2); 528CREATE PROCEDURE p1() SELECT * FROM t1; 529CALL p1(); 530a b 5311 2 532DROP TEMPORARY TABLE t1; 533CREATE TABLE t1(a INT, b INT); 534INSERT INTO t1 VALUES (1, 2); 535CALL p1(); 536a b 5371 2 538DROP PROCEDURE p1; 539DROP TABLE t1; 540 541# 6.1 Trigger that uses column 'a' of table 't' via pseudo-variable NEW 542# must be re-executed successfully if the table definition has been changed 543# in a compatible way. "Compatible way" in this case is that if the table 544# 't' still has a column named 'a' and the column type is compatible with 545# the operation that NEW.a takes part of. 546# 547# 6.2 Trigger that uses column 'a' of table 't' via pseudo-variable OLD 548# must be re-executed successfully if the table definition has been changed 549# in a compatible way. "Compatible way" in this case is that if the table 550# 't' still has a column named 'a' and the column type is compatible with 551# the operation that OLD.a takes part of. 552CREATE TABLE t1(a INT, b INT); 553INSERT INTO t1 VALUES (1, 2); 554CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW 555BEGIN 556SET @x = OLD.a; 557SET @y = NEW.a; 558END| 559 560SET @x = 0, @y = 0; 561UPDATE t1 SET a = 3, b = 4; 562SELECT @x, @y; 563@x @y 5641 3 565 566ALTER TABLE t1 ADD COLUMN c INT DEFAULT -1; 567 568SET @x = 0, @y = 0; 569UPDATE t1 SET a = 5, b = 6; 570SELECT @x, @y; 571@x @y 5723 5 573 574ALTER TABLE t1 CHANGE COLUMN a a VARCHAR(255); 575 576SET @x = 0, @y = 0; 577UPDATE t1 SET a = CONCAT('xxx_', a), b = 7; 578SELECT @x, @y; 579@x @y 5805 xxx_5 581 582DROP TABLE t1; 583 584# 6.3 Re-execution of a trigger that uses column 'a' of table 't' via 585# pseudo-variable NEW must fail if the table definition has been changed in 586# the way that the column 'a' does not exist anymore. 587# 588# 6.4 Re-execution of a trigger that uses column 'a' of table 't' via 589# pseudo-variable OLD must fail if the table definition has been changed in 590# the way that the column 'a' does not exist anymore. 591CREATE TABLE t1(a INT, b INT); 592INSERT INTO t1 VALUES (1, 2); 593CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW 594BEGIN 595SET @x = OLD.a; 596SET @y = NEW.b; 597END| 598 599UPDATE t1 SET a = 3, b = 4; 600 601ALTER TABLE t1 CHANGE COLUMN a a2 INT; 602 603UPDATE t1 SET a2 = 5, b = 6; 604ERROR 42S22: Unknown column 'a' in 'OLD' 605 606ALTER TABLE t1 CHANGE COLUMN a2 a INT; 607ALTER TABLE t1 CHANGE COLUMN b b2 INT; 608 609UPDATE t1 SET a = 5, b2 = 6; 610ERROR 42S22: Unknown column 'b' in 'NEW' 611 612DROP TABLE t1; 613 614# 7.1 Setup: 615# - stored program 'a', which alters regular table 't' in a compatible 616# way; 617# - stored program 'b', which calls 'a' and uses 't' before and after the 618# call; 619# Stored program 'b' must be executed successfully. 620CREATE TABLE t1(a INT, b INT); 621INSERT INTO t1 VALUES (1, 2); 622CREATE PROCEDURE p1() 623ALTER TABLE t1 ADD COLUMN c INT DEFAULT 3| 624CREATE PROCEDURE p2() 625BEGIN 626SELECT a, b FROM t1; 627CALL p1(); 628SELECT a, b FROM t1; 629END| 630 631CALL p2(); 632a b 6331 2 634a b 6351 2 636 637DROP PROCEDURE p1; 638DROP PROCEDURE p2; 639DROP TABLE t1; 640 641# 7.2 Setup: 642# - stored program 'a', which alters temporary table 't' in a compatible 643# way; 644# - stored program 'b', which calls 'a' and uses 't' before and after the 645# call; 646# Stored program 'b' must be executed successfully. 647CREATE TEMPORARY TABLE t1(a INT, b INT); 648INSERT INTO t1 VALUES (1, 2); 649CREATE PROCEDURE p1() 650ALTER TABLE t1 ADD COLUMN c INT DEFAULT 3| 651CREATE PROCEDURE p2() 652BEGIN 653SELECT a, b FROM t1; 654CALL p1(); 655SELECT a, b FROM t1; 656END| 657 658CALL p2(); 659a b 6601 2 661a b 6621 2 663 664DROP PROCEDURE p1; 665DROP PROCEDURE p2; 666DROP TEMPORARY TABLE t1; 667 668# 7.3 Setup: 669# - stored program 'a', which re-creates regular table 't' in a 670# compatible way; 671# - stored program 'b', which calls 'a' and uses 't' before and after the 672# call; 673# Stored program 'b' must be executed successfully. 674CREATE TABLE t1(a INT, b INT); 675INSERT INTO t1 VALUES (1, 2); 676CREATE PROCEDURE p1() 677BEGIN 678DROP TABLE t1; 679CREATE TABLE t1(a INT, b INT, c INT); 680INSERT INTO t1 VALUES (1, 2, 3); 681END| 682CREATE PROCEDURE p2() 683BEGIN 684SELECT a, b FROM t1; 685CALL p1(); 686SELECT a, b FROM t1; 687END| 688 689CALL p2(); 690a b 6911 2 692a b 6931 2 694 695DROP PROCEDURE p1; 696DROP PROCEDURE p2; 697DROP TABLE t1; 698 699# 7.4 Setup: 700# - stored program 'a', which re-creates temporary table 't' in a 701# compatible way; 702# - stored program 'b', which calls 'a' and uses 't' before and after the 703# call; 704# Stored program 'b' must be executed successfully. 705CREATE TEMPORARY TABLE t1(a INT, b INT); 706INSERT INTO t1 VALUES (1, 2); 707CREATE PROCEDURE p1() 708BEGIN 709DROP TEMPORARY TABLE t1; 710CREATE TEMPORARY TABLE t1(a INT, b INT, c INT); 711INSERT INTO t1 VALUES (1, 2, 3); 712END| 713CREATE PROCEDURE p2() 714BEGIN 715SELECT a, b FROM t1; 716CALL p1(); 717SELECT a, b FROM t1; 718END| 719 720CALL p2(); 721a b 7221 2 723a b 7241 2 725 726DROP PROCEDURE p1; 727DROP PROCEDURE p2; 728DROP TEMPORARY TABLE t1; 729 730# 7.5 Setup: 731# - stored program 'a', which re-creates view 'v' in a compatible way; 732# - stored program 'b', which calls 'a' and uses 'v' before and after the 733# call; 734# Stored program 'b' must be executed successfully. 735CREATE VIEW v1 AS SELECT 1 AS a, 2 AS b; 736CREATE PROCEDURE p1() 737BEGIN 738DROP VIEW v1; 739CREATE VIEW v1 AS SELECT 1 AS a, 2 AS b, 3 AS c; 740END| 741CREATE PROCEDURE p2() 742BEGIN 743SELECT a, b FROM v1; 744CALL p1(); 745SELECT a, b FROM v1; 746END| 747 748CALL p2(); 749a b 7501 2 751a b 7521 2 753 754DROP PROCEDURE p1; 755DROP PROCEDURE p2; 756DROP VIEW v1; 757 758# 7.6 Setup: 759# - stored program 'a', which alters regular table 't' in an incompatible 760# way; 761# - stored program 'b', which calls 'a' and uses 't' before and after the 762# call; 763# Stored program 'b' must fail on access to the table after its 764# modification. 765CREATE TABLE t1(a INT, b INT); 766INSERT INTO t1 VALUES (1, 2); 767CREATE PROCEDURE p1() 768ALTER TABLE t1 DROP COLUMN a| 769CREATE PROCEDURE p2() 770BEGIN 771SELECT a, b FROM t1; 772CALL p1(); 773SELECT a, b FROM t1; 774END| 775 776CALL p2(); 777a b 7781 2 779ERROR 42S22: Unknown column 'a' in 'field list' 780 781DROP PROCEDURE p1; 782DROP PROCEDURE p2; 783DROP TABLE t1; 784 785# 7.7 Setup: 786# - stored program 'a', which alters temporary table 't' in an 787# incompatible way; 788# - stored program 'b', which calls 'a' and uses 't' before and after the 789# call; 790# Stored program 'b' must fail on access to the table after its 791# modification. 792CREATE TEMPORARY TABLE t1(a INT, b INT); 793INSERT INTO t1 VALUES (1, 2); 794CREATE PROCEDURE p1() 795ALTER TABLE t1 DROP COLUMN a| 796CREATE PROCEDURE p2() 797BEGIN 798SELECT a, b FROM t1; 799CALL p1(); 800SELECT a, b FROM t1; 801END| 802 803CALL p2(); 804a b 8051 2 806ERROR 42S22: Unknown column 'a' in 'field list' 807 808DROP PROCEDURE p1; 809DROP PROCEDURE p2; 810DROP TEMPORARY TABLE t1; 811 812# 7.8 Setup: 813# - stored program 'a', which re-creates regular table 't' in an 814# incompatible way; 815# - stored program 'b', which calls 'a' and uses 't' before and after the 816# call; 817# Stored program 'b' must fail on access to the table after its 818# modification. 819CREATE TABLE t1(a INT, b INT); 820INSERT INTO t1 VALUES (1, 2); 821CREATE PROCEDURE p1() 822BEGIN 823DROP TABLE t1; 824CREATE TABLE t1(b INT, c INT); 825INSERT INTO t1 VALUES (2, 3); 826END| 827CREATE PROCEDURE p2() 828BEGIN 829SELECT a, b FROM t1; 830CALL p1(); 831SELECT a, b FROM t1; 832END| 833 834CALL p2(); 835a b 8361 2 837ERROR 42S22: Unknown column 'a' in 'field list' 838 839DROP PROCEDURE p1; 840DROP PROCEDURE p2; 841DROP TABLE t1; 842 843# 7.9 Setup: 844# - stored program 'a', which re-creates temporary table 't' in an 845# incompatible way; 846# - stored program 'b', which calls 'a' and uses 't' before and after the 847# call; 848# Stored program 'b' must fail on access to the table after its 849# modification. 850CREATE TEMPORARY TABLE t1(a INT, b INT); 851INSERT INTO t1 VALUES (1, 2); 852CREATE PROCEDURE p1() 853BEGIN 854DROP TEMPORARY TABLE t1; 855CREATE TEMPORARY TABLE t1(b INT, c INT); 856INSERT INTO t1 VALUES (2, 3); 857END| 858CREATE PROCEDURE p2() 859BEGIN 860SELECT a, b FROM t1; 861CALL p1(); 862SELECT a, b FROM t1; 863END| 864 865CALL p2(); 866a b 8671 2 868ERROR 42S22: Unknown column 'a' in 'field list' 869 870DROP PROCEDURE p1; 871DROP PROCEDURE p2; 872DROP TEMPORARY TABLE t1; 873 874# 7.10 Setup: 875# - stored program 'a', which re-creates view 'v' in an incompatible way; 876# - stored program 'b', which calls 'a' and uses 'v' before and after the 877# call; 878# Stored program 'b' must fail on access to the view after its 879# modification. 880CREATE VIEW v1 AS SELECT 1 AS a, 2 AS b; 881CREATE PROCEDURE p1() 882BEGIN 883DROP VIEW v1; 884CREATE VIEW v1 AS SELECT 2 AS b, 3 AS c; 885END| 886CREATE PROCEDURE p2() 887BEGIN 888SELECT a, b FROM v1; 889CALL p1(); 890SELECT a, b FROM v1; 891END| 892 893CALL p2(); 894a b 8951 2 896ERROR 42S22: Unknown column 'a' in 'field list' 897 898DROP PROCEDURE p1; 899DROP PROCEDURE p2; 900DROP VIEW v1; 901# 8. Stored program must be executed successfully when: 902# a. the program uses a table/view/temporary table that doesn't exist 903# at the time of start program execution 904# b. failed reference to the missed table/view/temporary table handled 905# by stored program 906# c. this table/view/temporary table is created as part of the 907# program execution 908# d. stored program gets access to newly created table/view/temporary 909# table from some SQL-statement during subsequent stored program execution. 910CREATE PROCEDURE p1() 911BEGIN 912DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 913BEGIN 914SELECT 'SQLEXCEPTION caught' AS msg; 915CREATE TABLE t1(a INT, b INT); 916INSERT INTO t1 VALUES (1, 2); 917END; 918SELECT * FROM t1; 919SELECT * FROM t1; 920DROP TABLE t1; 921END| 922CREATE PROCEDURE p2() 923BEGIN 924DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 925BEGIN 926SELECT 'SQLEXCEPTION caught' AS msg; 927CREATE TEMPORARY TABLE t1(a INT, b INT); 928INSERT INTO t1 VALUES (1, 2); 929END; 930SELECT * FROM t1; 931SELECT * FROM t1; 932DROP TEMPORARY TABLE t1; 933END| 934CREATE PROCEDURE p3() 935BEGIN 936DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 937BEGIN 938SELECT 'SQLEXCEPTION caught' AS msg; 939CREATE VIEW v1 AS SELECT 1 AS a, 2 AS b; 940END; 941SELECT * FROM v1; 942SELECT * FROM v1; 943DROP VIEW v1; 944END| 945CALL p1(); 946msg 947SQLEXCEPTION caught 948a b 9491 2 950CALL p2(); 951msg 952SQLEXCEPTION caught 953a b 9541 2 955CALL p3(); 956msg 957SQLEXCEPTION caught 958a b 9591 2 960DROP PROCEDURE p1; 961DROP PROCEDURE p2; 962DROP PROCEDURE p3; 963 964# 9. Stored program must be executed successfully when 965# - the stored program has an expression in one of the following 966# statements 967# - RETURN 968# - IF 969# - CASE 970# - WHILE 971# - UNTIL 972# - SET 973# - the expression depends on the meta-data of some table/view/temporary table; 974# - the meta-data of dependent object has changed in a compatible way. 975# 976# Note, that CASE-expression must be evaluated once even if (some) 977# CASE-expressions need to be re-parsed. 978# 979# 10. Subsequent executions of a stored program must fail when 980# - the stored program has an expression in one of the following 981# statements 982# - RETURN 983# - IF 984# - CASE 985# - WHILE 986# - UNTIL 987# - SET 988# - the expression depends on the meta-data of some table/view/temporary table; 989# - the meta-data of dependent object has changed in a non-compatible way. 990# 991# Note, that CASE-expression must be evaluated once even if (some) 992# CASE-expressions need to be re-parsed. 993 994# Check IF-statement. 995 996CREATE PROCEDURE p1() 997BEGIN 998IF(SELECT * FROM t1)THEN 999SELECT 1; 1000ELSE 1001SELECT 2; 1002END IF; 1003END| 1004CREATE PROCEDURE p2() 1005BEGIN 1006DECLARE v INT DEFAULT 1; 1007IF v * (SELECT * FROM t1) THEN 1008SELECT 1; 1009ELSE 1010SELECT 2; 1011END IF; 1012END| 1013CREATE FUNCTION f1() RETURNS INT 1014BEGIN 1015IF (SELECT * FROM t1) THEN 1016RETURN 1; 1017ELSE 1018RETURN 2; 1019END IF; 1020RETURN 3; 1021END| 1022CREATE FUNCTION f2() RETURNS INT 1023BEGIN 1024DECLARE v INT DEFAULT 1; 1025IF v * (SELECT * FROM t1) THEN 1026RETURN 1; 1027ELSE 1028RETURN 2; 1029END IF; 1030RETURN 3; 1031END| 1032CREATE TABLE t1(a INT); 1033INSERT INTO t1 VALUES (1); 1034 1035CALL p1(); 10361 10371 1038CALL p2(); 10391 10401 1041SELECT f1(); 1042f1() 10431 1044SELECT f2(); 1045f2() 10461 1047 1048UPDATE t1 SET a = 0; 1049 1050CALL p1(); 10512 10522 1053CALL p2(); 10542 10552 1056SELECT f1(); 1057f1() 10582 1059SELECT f2(); 1060f2() 10612 1062 1063ALTER TABLE t1 ADD COLUMN b INT DEFAULT 1; 1064 1065CALL p1(); 1066ERROR 21000: Operand should contain 1 column(s) 1067CALL p2(); 1068ERROR 21000: Operand should contain 1 column(s) 1069SELECT f1(); 1070ERROR 21000: Operand should contain 1 column(s) 1071SELECT f2(); 1072ERROR 21000: Operand should contain 1 column(s) 1073 1074ALTER TABLE t1 DROP COLUMN a; 1075 1076CALL p1(); 10771 10781 1079CALL p2(); 10801 10811 1082SELECT f1(); 1083f1() 10841 1085SELECT f2(); 1086f2() 10871 1088 1089DROP PROCEDURE p1; 1090DROP PROCEDURE p2; 1091DROP FUNCTION f1; 1092DROP FUNCTION f2; 1093DROP TABLE t1; 1094 1095# Check WHILE-statement. 1096 1097CREATE PROCEDURE p1(x INT) 1098BEGIN 1099WHILE(SELECT * FROM t1)DO 1100SELECT x; 1101UPDATE t1 SET a = x; 1102SET x = x - 1; 1103END WHILE; 1104END| 1105CREATE TABLE t1(a INT); 1106INSERT INTO t1 VALUES (0); 1107CALL p1(3); 1108UPDATE t1 SET a = 1; 1109CALL p1(3); 1110x 11113 1112x 11132 1114x 11151 1116x 11170 1118UPDATE t1 SET a = 1; 1119ALTER TABLE t1 ADD COLUMN b INT DEFAULT 1; 1120CALL p1(3); 1121ERROR 21000: Operand should contain 1 column(s) 1122ALTER TABLE t1 DROP COLUMN a; 1123CALL p1(3); 1124x 11253 1126ERROR 42S22: Unknown column 'a' in 'field list' 1127DROP PROCEDURE p1; 1128DROP TABLE t1; 1129 1130# Check REPEAT-statement. 1131 1132CREATE PROCEDURE p1(x INT) 1133BEGIN 1134REPEAT 1135SELECT x; 1136UPDATE t1 SET a = x; 1137SET x = x - 1; 1138UNTIL(NOT (SELECT * FROM t1))END REPEAT; 1139END| 1140CREATE TABLE t1(a INT); 1141INSERT INTO t1 VALUES (0); 1142CALL p1(3); 1143x 11443 1145x 11462 1147x 11481 1149x 11500 1151UPDATE t1 SET a = 1; 1152CALL p1(3); 1153x 11543 1155x 11562 1157x 11581 1159x 11600 1161UPDATE t1 SET a = 1; 1162ALTER TABLE t1 ADD COLUMN b INT DEFAULT 1; 1163CALL p1(3); 1164x 11653 1166ERROR 21000: Operand should contain 1 column(s) 1167ALTER TABLE t1 DROP COLUMN a; 1168CALL p1(3); 1169x 11703 1171ERROR 42S22: Unknown column 'a' in 'field list' 1172DROP PROCEDURE p1; 1173DROP TABLE t1; 1174 1175# Check CASE-statement (round #1). 1176 1177CREATE PROCEDURE p1() 1178BEGIN 1179CASE 1180WHEN (SELECT * FROM t1) = 1 THEN SELECT 'a1'; 1181WHEN (SELECT * FROM t1) = 2 THEN SELECT 'a2'; 1182WHEN (SELECT * FROM t1) = 3 THEN SELECT 'a3'; 1183ELSE SELECT 'a4'; 1184END CASE; 1185END| 1186CREATE PROCEDURE p2() 1187BEGIN 1188CASE (SELECT * FROM t1) 1189WHEN 1 THEN SELECT 'a1'; 1190WHEN 2 THEN SELECT 'a2'; 1191WHEN 3 THEN SELECT 'a3'; 1192ELSE SELECT 'a4'; 1193END CASE; 1194END| 1195CREATE TABLE t1(a INT); 1196INSERT INTO t1 VALUES (0); 1197 1198CALL p1(); 1199a4 1200a4 1201CALL p2(); 1202a4 1203a4 1204 1205UPDATE t1 SET a = 3; 1206 1207CALL p1(); 1208a3 1209a3 1210CALL p2(); 1211a3 1212a3 1213 1214ALTER TABLE t1 ADD COLUMN b INT DEFAULT 2; 1215 1216CALL p1(); 1217ERROR 21000: Operand should contain 2 column(s) 1218CALL p2(); 1219ERROR 21000: Operand should contain 1 column(s) 1220 1221ALTER TABLE t1 DROP COLUMN a; 1222 1223CALL p1(); 1224a2 1225a2 1226CALL p2(); 1227a2 1228a2 1229 1230DROP PROCEDURE p1; 1231DROP PROCEDURE p2; 1232DROP TABLE t1; 1233 1234# Check CASE-statement (round #2). 1235# 1236# Check that CASE-expression is executed once even if the metadata, used 1237# in a WHEN-expression, have changed. 1238 1239CREATE TABLE t1(a INT); 1240CREATE TABLE t2(a INT); 1241INSERT INTO t1 VALUES (1); 1242INSERT INTO t2 VALUES (1); 1243CREATE FUNCTION f1() RETURNS INT 1244BEGIN 1245SET @x = @x + 1; 1246RETURN (SELECT a FROM t1); 1247END| 1248CREATE PROCEDURE p1() 1249BEGIN 1250CASE f1() 1251WHEN 1 THEN SELECT 'a1'; 1252WHEN 2 THEN SELECT 'a2'; 1253WHEN (SELECT * FROM t2) THEN SELECT 'subselect'; 1254ELSE SELECT 'else'; 1255END CASE; 1256END| 1257 1258SET @x = 0; 1259CALL p1(); 1260a1 1261a1 1262SELECT @x; 1263@x 12641 1265 1266UPDATE t1 SET a = 3; 1267ALTER TABLE t2 ADD COLUMN b INT DEFAULT 3; 1268 1269SET @x = 0; 1270CALL p1(); 1271ERROR 21000: Operand should contain 1 column(s) 1272SELECT @x; 1273@x 12741 1275 1276ALTER TABLE t2 DROP COLUMN a; 1277 1278SET @x = 0; 1279CALL p1(); 1280subselect 1281subselect 1282SELECT @x; 1283@x 12841 1285 1286DROP PROCEDURE p1; 1287DROP FUNCTION f1; 1288DROP TABLE t1; 1289DROP TABLE t2; 1290 1291# Check DEFAULT clause. 1292# 1293 1294CREATE TABLE t1(a INT); 1295INSERT INTO t1 VALUES (1); 1296CREATE PROCEDURE p1() 1297BEGIN 1298DECLARE v INT DEFAULT (SELECT * FROM t1); 1299SELECT v; 1300END| 1301 1302CALL p1(); 1303v 13041 1305 1306ALTER TABLE t1 ADD COLUMN b INT DEFAULT 2; 1307 1308CALL p1(); 1309ERROR 21000: Operand should contain 1 column(s) 1310 1311ALTER TABLE t1 DROP COLUMN a; 1312 1313CALL p1(); 1314v 13152 1316 1317DROP PROCEDURE p1; 1318DROP TABLE t1; 1319 1320# Check SET. 1321# 1322 1323CREATE TABLE t1(a INT); 1324INSERT INTO t1 VALUES (1); 1325CREATE TABLE t2(a INT); 1326INSERT INTO t2 VALUES (1); 1327CREATE PROCEDURE p1() 1328BEGIN 1329DECLARE x INT; 1330SET x = (SELECT * FROM t1); 1331SELECT x; 1332END| 1333CREATE PROCEDURE p2() 1334BEGIN 1335SET @x = NULL; 1336SET @x = (SELECT * FROM t1); 1337SELECT @x; 1338END| 1339CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW 1340BEGIN 1341SET NEW.a = (SELECT * FROM t1) * 2; 1342END| 1343 1344CALL p1(); 1345x 13461 1347 1348CALL p2(); 1349@x 13501 1351 1352UPDATE t2 SET a = 10; 1353 1354ALTER TABLE t1 ADD COLUMN b INT DEFAULT 2; 1355 1356CALL p1(); 1357ERROR 21000: Operand should contain 1 column(s) 1358 1359CALL p2(); 1360ERROR 21000: Operand should contain 1 column(s) 1361 1362UPDATE t2 SET a = 20; 1363ERROR 21000: Operand should contain 1 column(s) 1364 1365ALTER TABLE t1 DROP COLUMN a; 1366 1367CALL p1(); 1368x 13692 1370 1371CALL p2(); 1372@x 13732 1374 1375UPDATE t2 SET a = 30; 1376 1377DROP PROCEDURE p1; 1378DROP PROCEDURE p2; 1379DROP TABLE t1; 1380DROP TABLE t2; 1381 1382# 11.1 If metadata of the objects (regular tables, temporary tables, 1383# views), used in SELECT-statement changed between DECLARE CURSOR and 1384# OPEN statements, the SELECT-statement should be re-parsed to use 1385# up-to-date metadata. 1386 1387 1388# - Regular table. 1389 1390CREATE TABLE t1(a INT); 1391INSERT INTO t1 VALUES (1); 1392CREATE PROCEDURE p1() 1393BEGIN 1394DECLARE v INT; 1395DECLARE c CURSOR FOR SELECT * FROM t1; 1396ALTER TABLE t1 ADD COLUMN b INT DEFAULT 2; 1397ALTER TABLE t1 DROP COLUMN a; 1398OPEN c; 1399FETCH c INTO v; 1400CLOSE c; 1401SELECT v; 1402END| 1403 1404CALL p1(); 1405v 14062 1407 1408DROP TABLE t1; 1409DROP PROCEDURE p1; 1410 1411# - Temporary table. 1412 1413CREATE TEMPORARY TABLE t1(a INT); 1414INSERT INTO t1 VALUES (1); 1415CREATE PROCEDURE p1() 1416BEGIN 1417DECLARE v INT; 1418DECLARE c CURSOR FOR SELECT * FROM t1; 1419ALTER TABLE t1 ADD COLUMN b INT DEFAULT 2; 1420ALTER TABLE t1 DROP COLUMN a; 1421OPEN c; 1422FETCH c INTO v; 1423CLOSE c; 1424SELECT v; 1425END| 1426 1427CALL p1(); 1428v 14292 1430 1431DROP TEMPORARY TABLE t1; 1432DROP PROCEDURE p1; 1433 1434# 11.2 If the metadata changed between OPEN and FETCH or CLOSE 1435# statements, those changes should not be noticed. 1436 1437CREATE TABLE t1(a INT); 1438INSERT INTO t1 VALUES (1); 1439CREATE PROCEDURE p1() 1440BEGIN 1441DECLARE v INT; 1442DECLARE c CURSOR FOR SELECT * FROM t1; 1443OPEN c; 1444ALTER TABLE t1 ADD COLUMN b INT DEFAULT 2; 1445ALTER TABLE t1 DROP COLUMN a; 1446FETCH c INTO v; 1447CLOSE c; 1448SELECT v; 1449END| 1450 1451CALL p1(); 1452v 14531 1454 1455DROP TABLE t1; 1456DROP PROCEDURE p1; 1457 1458# 11.3 Re-parsing of the SELECT-statement should be made correctly 1459# (in the correct parsing context) if the metadata changed between 1460# DECLARE CURSOR and OPEN statements, and those statements reside in different 1461# parsing contexts. 1462 1463CREATE TABLE t1(a INT); 1464INSERT INTO t1 VALUES (1); 1465CREATE PROCEDURE p1() 1466BEGIN 1467DECLARE f1 INT; 1468DECLARE f2 INT; 1469DECLARE f3 INT; 1470DECLARE x INT DEFAULT 1; 1471DECLARE y INT DEFAULT 2; 1472DECLARE c CURSOR FOR SELECT x, y, t1.a FROM t1; 1473ALTER TABLE t1 ADD COLUMN b INT; 1474BEGIN 1475DECLARE x INT DEFAULT 10; 1476DECLARE y INT DEFAULT 20; 1477OPEN c; 1478FETCH c INTO f1, f2, f3; 1479SELECT f1, f2, f3; 1480CLOSE c; 1481END; 1482END| 1483 1484CALL p1(); 1485f1 f2 f3 14861 2 1 1487 1488DROP TABLE t1; 1489DROP PROCEDURE p1; 1490 1491# Test procedure behaviour after view recreation. 1492CREATE TABLE t1 (a INT); 1493INSERT INTO t1 VALUES (1), (2); 1494CREATE VIEW v1 AS SELECT * FROM t1; 1495CREATE PROCEDURE p1() 1496SELECT * FROM v1; 1497CALL p1(); 1498a 14991 15002 1501# Alter underlying table and recreate the view. 1502ALTER TABLE t1 ADD COLUMN (b INT); 1503ALTER VIEW v1 AS SELECT * FROM t1; 1504# And check whether the call of stored procedure handles it correctly. 1505CALL p1(); 1506a b 15071 NULL 15082 NULL 1509DROP VIEW v1; 1510DROP TABLE t1; 1511DROP PROCEDURE p1; 1512# Test if metadata changes for temporary table is handled 1513# correctly inside a stored procedure. 1514CREATE TEMPORARY TABLE t1 (a INT); 1515INSERT INTO t1 VALUES (1), (2); 1516CREATE PROCEDURE p1() 1517SELECT * FROM t1; 1518CALL p1(); 1519a 15201 15212 1522# Test if added temporary table's column is recognized during 1523# procedure invocation. 1524ALTER TABLE t1 ADD COLUMN (b INT); 1525CALL p1(); 1526a b 15271 NULL 15282 NULL 1529# Test if dropped temporary table's column is not appeared 1530# in procedure's result. 1531ALTER TABLE t1 DROP COLUMN a; 1532CALL p1(); 1533b 1534NULL 1535NULL 1536DROP PROCEDURE p1; 1537DROP TABLE t1; 1538# Test handle of metadata changes with stored function. 1539CREATE TABLE t1 (a INT); 1540INSERT INTO t1 VALUES (1), (2); 1541CREATE FUNCTION f1() RETURNS INT 1542BEGIN 1543CREATE TEMPORARY TABLE t1_result_set AS SELECT * FROM t1; 1544RETURN 0; 1545END| 1546SELECT f1(); 1547f1() 15480 1549SELECT * FROM t1_result_set; 1550a 15511 15522 1553DROP TABLE t1_result_set; 1554# Check if added column is noticed by invocation of stored function. 1555ALTER TABLE t1 ADD COLUMN (b INT); 1556SELECT f1(); 1557f1() 15580 1559SELECT * FROM t1_result_set; 1560a b 15611 NULL 15622 NULL 1563DROP TABLE t1_result_set; 1564# Check if dropped column is noticed by invocation of stored function. 1565ALTER TABLE t1 DROP COLUMN a; 1566SELECT f1(); 1567f1() 15680 1569SELECT * FROM t1_result_set; 1570b 1571NULL 1572NULL 1573DROP TABLE t1_result_set; 1574DROP TABLE t1; 1575DROP FUNCTION f1; 1576# Test if table's recreation is handled correctly 1577# inside a stored function. 1578CREATE TABLE t1 (a INT); 1579INSERT INTO t1 VALUES (1), (2); 1580CREATE FUNCTION f1() RETURNS INT 1581BEGIN 1582CREATE TEMPORARY TABLE t1_result_set AS SELECT * FROM t1; 1583RETURN 0; 1584END| 1585SELECT f1(); 1586f1() 15870 1588SELECT * FROM t1_result_set; 1589a 15901 15912 1592DROP TABLE t1_result_set; 1593# Recreate table and check if it is handled correctly 1594# by function invocation. 1595DROP TABLE t1; 1596CREATE TABLE t1 (a INT); 1597INSERT INTO t1 VALUES (1), (2); 1598SELECT f1(); 1599f1() 16000 1601SELECT * FROM t1_result_set; 1602a 16031 16042 1605DROP TABLE t1_result_set; 1606DROP FUNCTION f1; 1607DROP TABLE t1; 1608# Test if changes in the view's metadata is handled 1609# correctly by function call. 1610CREATE TABLE t1 (a INT); 1611INSERT INTO t1 VALUES (1), (2); 1612CREATE VIEW v1 AS SELECT * FROM t1; 1613CREATE FUNCTION f1() RETURNS INT 1614BEGIN 1615CREATE TEMPORARY TABLE t1_result_set AS SELECT * FROM v1; 1616RETURN 0; 1617END| 1618SELECT f1(); 1619f1() 16200 1621SELECT * FROM t1_result_set; 1622a 16231 16242 1625DROP TABLE t1_result_set; 1626ALTER TABLE t1 ADD COLUMN (b INT); 1627ALTER VIEW v1 AS SELECT * FROM t1; 1628SELECT f1(); 1629f1() 16300 1631SELECT * FROM t1_result_set; 1632a b 16331 NULL 16342 NULL 1635DROP TABLE t1_result_set; 1636DROP TABLE t1; 1637DROP VIEW v1; 1638DROP FUNCTION f1; 1639# Check if queried object's type substitution (table->view, view->table, 1640# table->temp table, etc.) is handled correctly during invocation of 1641# stored function/procedure. 1642CREATE TABLE t1 (a INT); 1643INSERT INTO t1 VALUES (1), (2); 1644CREATE FUNCTION f1() RETURNS INT 1645BEGIN 1646CREATE TEMPORARY TABLE t1_result_set AS SELECT * FROM t1; 1647RETURN 0; 1648END| 1649CREATE PROCEDURE p1() 1650SELECT * FROM t1| 1651CALL p1(); 1652a 16531 16542 1655SELECT f1(); 1656f1() 16570 1658SELECT * FROM t1_result_set; 1659a 16601 16612 1662DROP TABLE t1_result_set; 1663DROP TABLE t1; 1664CREATE TEMPORARY TABLE t1 (a INT); 1665INSERT INTO t1 VALUES (1), (2); 1666CALL p1; 1667a 16681 16692 1670SELECT f1(); 1671f1() 16720 1673SELECT * FROM t1_result_set; 1674a 16751 16762 1677DROP TABLE t1_result_set; 1678DROP TABLE t1; 1679CREATE TABLE t2 (a INT); 1680INSERT INTO t2 VALUES (1), (2); 1681CREATE VIEW t1 AS SELECT * FROM t2; 1682CALL p1; 1683a 16841 16852 1686SELECT f1(); 1687f1() 16880 1689SELECT * FROM t1_result_set; 1690a 16911 16922 1693DROP TABLE t1_result_set; 1694DROP TABLE t2; 1695DROP VIEW t1; 1696DROP FUNCTION f1; 1697DROP PROCEDURE p1; 1698# Test handle of metadata changes with triggers. 1699CREATE TABLE t1 (a INT); 1700CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW 1701SET new.a = new.a + 100; 1702INSERT INTO t1 VALUES (1), (2); 1703SELECT * FROM t1; 1704a 1705101 1706102 1707# Check if added table's column is handled correctly inside trigger. 1708ALTER TABLE t1 ADD COLUMN (b INT); 1709INSERT INTO t1 VALUES (3, 4); 1710SELECT * FROM t1; 1711a b 1712101 NULL 1713102 NULL 1714103 4 1715DROP TRIGGER trg1; 1716DROP TABLE t1; 1717# Test if deleted column is handled correctly by trigger invocation. 1718CREATE TABLE t1 (a INT, b INT); 1719CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW 1720SET new.a = new.a + 100; 1721INSERT INTO t1 VALUES (1, 2), (3, 4); 1722SELECT * FROM t1; 1723a b 1724101 2 1725103 4 1726ALTER TABLE t1 DROP COLUMN b; 1727INSERT INTO t1 VALUES (5); 1728SELECT * FROM t1; 1729a 1730101 1731103 1732105 1733DROP TRIGGER trg1; 1734DROP TABLE t1; 1735# Check if server returns and error when was dropped a column 1736# that is used inside a trigger body. 1737CREATE TABLE t1 (a INT, b INT); 1738CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW 1739SET new.a = new.a + 100; 1740INSERT INTO t1 VALUES (1, 2), (3, 4); 1741SELECT * FROM t1; 1742a b 1743101 2 1744103 4 1745ALTER TABLE t1 DROP COLUMN a; 1746INSERT INTO t1 VALUES (5); 1747ERROR 42S22: Unknown column 'a' in 'NEW' 1748DROP TRIGGER trg1; 1749DROP TABLE t1; 1750 1751# Check updateable views inside triggers. 1752CREATE TABLE t1(a INT); 1753INSERT INTO t1 VALUES (1); 1754CREATE TABLE t2(a INT); 1755INSERT INTO t2 VALUES (1); 1756CREATE VIEW v1 AS SELECT a FROM t1; 1757CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 FOR EACH ROW 1758BEGIN 1759INSERT INTO v1 VALUES (NEW.a); 1760SET @x = (SELECT CHARSET(a) FROM v1 LIMIT 1); 1761END| 1762 1763SET @x = NULL; 1764UPDATE t2 SET a = 10; 1765SELECT * FROM v1; 1766a 17671 176810 1769SELECT @x; 1770@x 1771binary 1772 1773ALTER TABLE t1 CHANGE COLUMN a a CHAR(2); 1774 1775SET @x = NULL; 1776UPDATE t2 SET a = 20; 1777SELECT * FROM v1; 1778a 17791 178010 178120 1782SELECT @x; 1783@x 1784latin1 1785 1786DROP TABLE t1; 1787DROP TABLE t2; 1788DROP VIEW v1; 1789