1drop temporary table if exists t1, t2, t3; 2drop table if exists t1, t2, t3; 3drop procedure if exists p_verify_reprepare_count; 4drop procedure if exists p1; 5drop function if exists f1; 6drop view if exists v1, v2; 7create procedure p_verify_reprepare_count(expected int) 8begin 9declare old_reprepare_count int default @reprepare_count; 10select variable_value from 11information_schema.session_status where 12variable_name='com_stmt_reprepare' 13 into @reprepare_count; 14if old_reprepare_count + expected <> @reprepare_count then 15select concat("Expected: ", expected, 16", actual: ", @reprepare_count - old_reprepare_count) 17as "ERROR"; 18else 19select '' as "SUCCESS"; 20end if; 21end| 22set @reprepare_count= 0; 23flush status; 24===================================================================== 25Part 1: NOTHING -> TABLE transitions 26===================================================================== 27prepare stmt from "select * from t1"; 28ERROR 42S02: Table 'test.t1' doesn't exist 29===================================================================== 30Part 2: NOTHING -> TEMPORARY TABLE transitions 31===================================================================== 32===================================================================== 33Part 3: NOTHING -> VIEW transitions 34===================================================================== 35===================================================================== 36Part 4: TABLE -> NOTHING transitions 37===================================================================== 38# Test 4-a: select ... from <table> 39create table t1 (a int); 40prepare stmt from "select * from t1"; 41execute stmt; 42a 43call p_verify_reprepare_count(0); 44SUCCESS 45 46execute stmt; 47a 48call p_verify_reprepare_count(0); 49SUCCESS 50 51drop table t1; 52execute stmt; 53ERROR 42S02: Table 'test.t1' doesn't exist 54call p_verify_reprepare_count(0); 55SUCCESS 56 57execute stmt; 58ERROR 42S02: Table 'test.t1' doesn't exist 59call p_verify_reprepare_count(0); 60SUCCESS 61 62deallocate prepare stmt; 63# Test 4-b: TABLE -> NOTHING by renaming the table 64create table t1 (a int); 65prepare stmt from "select * from t1"; 66execute stmt; 67a 68call p_verify_reprepare_count(0); 69SUCCESS 70 71execute stmt; 72a 73call p_verify_reprepare_count(0); 74SUCCESS 75 76rename table t1 to t2; 77execute stmt; 78ERROR 42S02: Table 'test.t1' doesn't exist 79call p_verify_reprepare_count(0); 80SUCCESS 81 82execute stmt; 83ERROR 42S02: Table 'test.t1' doesn't exist 84call p_verify_reprepare_count(0); 85SUCCESS 86 87deallocate prepare stmt; 88drop table t2; 89===================================================================== 90Part 5: TABLE -> TABLE (DDL) transitions 91===================================================================== 92create table t1 (a int); 93prepare stmt from "select a from t1"; 94execute stmt; 95a 96call p_verify_reprepare_count(0); 97SUCCESS 98 99execute stmt; 100a 101call p_verify_reprepare_count(0); 102SUCCESS 103 104alter table t1 add column (b int); 105execute stmt; 106a 107call p_verify_reprepare_count(1); 108SUCCESS 109 110execute stmt; 111a 112call p_verify_reprepare_count(0); 113SUCCESS 114 115drop table t1; 116deallocate prepare stmt; 117===================================================================== 118Part 6: TABLE -> TABLE (TRIGGER) transitions 119===================================================================== 120# Test 6-a: adding a relevant trigger 121create table t1 (a int); 122prepare stmt from "insert into t1 (a) value (?)"; 123set @val=1; 124execute stmt using @val; 125call p_verify_reprepare_count(0); 126SUCCESS 127 128create trigger t1_bi before insert on t1 for each row 129set @message= new.a; 130set @val=2; 131execute stmt using @val; 132call p_verify_reprepare_count(1); 133SUCCESS 134 135select @message; 136@message 1372 138set @val=3; 139execute stmt using @val; 140call p_verify_reprepare_count(0); 141SUCCESS 142 143select @message; 144@message 1453 146prepare stmt from "insert into t1 (a) value (?)"; 147set @val=4; 148execute stmt using @val; 149call p_verify_reprepare_count(0); 150SUCCESS 151 152select @message; 153@message 1544 155# Test 6-b: adding an irrelevant trigger 156create trigger t1_bd before delete on t1 for each row 157set @message= old.a; 158set @val=5; 159execute stmt using @val; 160call p_verify_reprepare_count(1); 161SUCCESS 162 163select @message; 164@message 1655 166set @val=6; 167execute stmt using @val; 168call p_verify_reprepare_count(0); 169SUCCESS 170 171select @message; 172@message 1736 174prepare stmt from "insert into t1 (a) value (?)"; 175set @val=7; 176execute stmt using @val; 177call p_verify_reprepare_count(0); 178SUCCESS 179 180select @message; 181@message 1827 183# Test 6-c: changing a relevant trigger 184drop trigger t1_bi; 185create trigger t1_bi before insert on t1 for each row 186set @message= concat("new trigger: ", new.a); 187set @val=8; 188execute stmt using @val; 189call p_verify_reprepare_count(1); 190SUCCESS 191 192select @message; 193@message 194new trigger: 8 195set @val=9; 196execute stmt using @val; 197call p_verify_reprepare_count(0); 198SUCCESS 199 200select @message; 201@message 202new trigger: 9 203prepare stmt from "insert into t1 (a) value (?)"; 204set @val=10; 205execute stmt using @val; 206call p_verify_reprepare_count(0); 207SUCCESS 208 209select @message; 210@message 211new trigger: 10 212# Test 6-d: changing an irrelevant trigger 213drop trigger t1_bd; 214set @val=11; 215execute stmt using @val; 216call p_verify_reprepare_count(1); 217SUCCESS 218 219select @message; 220@message 221new trigger: 11 222Test 6-e: removing a relevant trigger 223drop trigger t1_bi; 224set @val=12; 225execute stmt using @val; 226call p_verify_reprepare_count(1); 227SUCCESS 228 229select @message; 230@message 231new trigger: 11 232set @val=13; 233execute stmt using @val; 234call p_verify_reprepare_count(0); 235SUCCESS 236 237select @message; 238@message 239new trigger: 11 240prepare stmt from "insert into t1 (a) value (?)"; 241set @val=14; 242execute stmt using @val; 243call p_verify_reprepare_count(0); 244SUCCESS 245 246select @message; 247@message 248new trigger: 11 249select * from t1 order by a; 250a 2511 2522 2533 2544 2555 2566 2577 2588 2599 26010 26111 26212 26313 26414 265drop table t1; 266deallocate prepare stmt; 267===================================================================== 268Part 7: TABLE -> TABLE (TRIGGER dependencies) transitions 269===================================================================== 270# Test 7-a: dependent PROCEDURE has changed 271# 272create table t1 (a int); 273create trigger t1_ai after insert on t1 for each row 274call p1(new.a); 275create procedure p1(a int) begin end; 276prepare stmt from "insert into t1 (a) values (?)"; 277set @var= 1; 278execute stmt using @var; 279drop procedure p1; 280create procedure p1 (a int) begin end; 281set @var= 2; 282execute stmt using @var; 283# Cleanup 284drop procedure p1; 285call p_verify_reprepare_count(1); 286SUCCESS 287 288# Test 7-b: dependent FUNCTION has changed 289# 290# Note, this scenario is supported, subject of Bug#12093 291# 292drop trigger t1_ai; 293create trigger t1_ai after insert on t1 for each row 294select f1(new.a+1) into @var; 295create function f1 (a int) returns int return a; 296prepare stmt from "insert into t1(a) values (?)"; 297set @var=3; 298execute stmt using @var; 299select @var; 300@var 3014 302drop function f1; 303create function f1 (a int) returns int return 0; 304execute stmt using @var; 305call p_verify_reprepare_count(1); 306SUCCESS 307 308drop function f1; 309deallocate prepare stmt; 310# Test 7-c: dependent VIEW has changed 311# 312# 313drop trigger t1_ai; 314create table t2 (a int unique); 315create table t3 (a int unique); 316create view v1 as select a from t2; 317create trigger t1_ai after insert on t1 for each row 318insert into v1 (a) values (new.a); 319# Demonstrate that the same bug is present 320# without prepared statements 321insert into t1 (a) values (5); 322select * from t2; 323a 3245 325select * from t3; 326a 327drop view v1; 328create view v1 as select a from t3; 329insert into t1 (a) values (6); 330select * from t2; 331a 3325 333select * from t3; 334a 3356 336prepare stmt from "insert into t1 (a) values (?)"; 337set @var=7; 338execute stmt using @var; 339call p_verify_reprepare_count(0); 340SUCCESS 341 342select * from t3; 343a 3446 3457 346select * from t2; 347a 3485 349drop view v1; 350create view v1 as select a from t2; 351set @var=8; 352execute stmt using @var; 353call p_verify_reprepare_count(1); 354SUCCESS 355 356select * from t2; 357a 3585 3598 360select * from t3; 361a 3626 3637 364drop view v1; 365drop table t1,t2,t3; 366# Test 7-d: dependent TABLE has changed 367create table t1 (a int); 368create trigger t1_ai after insert on t1 for each row 369insert into t2 (a) values (new.a); 370create table t2 (a int); 371prepare stmt from "insert into t1 (a) values (?)"; 372set @var=1; 373execute stmt using @var; 374alter table t2 add column comment varchar(255); 375set @var=2; 376# Since the dependent table is tracked in the prelocked 377# list of the prepared statement, invalidation happens 378# and the statement is re-prepared. This is an unnecessary 379# side effect, since the statement that *is* dependent 380# on t2 definition is inside the trigger, and it is currently 381# not reprepared (see the previous test case). 382execute stmt using @var; 383call p_verify_reprepare_count(1); 384SUCCESS 385 386select * from t1; 387a 3881 3892 390select * from t2; 391a comment 3921 NULL 3932 NULL 394drop table t1,t2; 395# Test 7-e: dependent TABLE TRIGGER has changed 396create table t1 (a int); 397create trigger t1_ai after insert on t1 for each row 398insert into t2 (a) values (new.a); 399create table t2 (a int unique); 400create trigger t2_ai after insert on t2 for each row 401insert into t3 (a) values (new.a); 402create table t3 (a int unique); 403create table t4 (a int unique); 404insert into t1 (a) values (1); 405select * from t1 join t2 on (t1.a=t2.a) join t3 on (t2.a=t3.a); 406a a a 4071 1 1 408drop trigger t2_ai; 409create trigger t2_ai after insert on t2 for each row 410insert into t4 (a) values (new.a); 411insert into t1 (a) values (2); 412select * from t1 join t2 on (t1.a=t2.a) join t4 on (t2.a=t4.a); 413a a a 4142 2 2 415prepare stmt from "insert into t1 (a) values (?)"; 416set @var=3; 417execute stmt using @var; 418select * from t1 join t2 on (t1.a=t2.a) join t4 on (t2.a=t4.a); 419a a a 4202 2 2 4213 3 3 422drop trigger t2_ai; 423create trigger t2_ai after insert on t2 for each row 424insert into t3 (a) values (new.a); 425set @var=4; 426execute stmt using @var; 427call p_verify_reprepare_count(1); 428SUCCESS 429 430select * from t1 join t2 on (t1.a=t2.a) join t3 on (t2.a=t3.a); 431a a a 4321 1 1 4334 4 4 434select * from t1 join t2 on (t1.a=t2.a) join t4 on (t2.a=t4.a); 435a a a 4362 2 2 4373 3 3 438drop table t1, t2, t3, t4; 439deallocate prepare stmt; 440===================================================================== 441Part 8: TABLE -> TEMPORARY TABLE transitions 442===================================================================== 443# Test 8-a: base table used recreated as temporary table 444create table t1 (a int); 445prepare stmt from "select * from t1"; 446execute stmt; 447a 448drop table t1; 449create temporary table t1 (a int); 450execute stmt; 451a 452call p_verify_reprepare_count(1); 453SUCCESS 454 455execute stmt; 456a 457call p_verify_reprepare_count(0); 458SUCCESS 459 460drop table t1; 461deallocate prepare stmt; 462# Test 8-b: temporary table has precedence over base table with same name 463create table t1 (a int); 464prepare stmt from 'select count(*) from t1'; 465execute stmt; 466count(*) 4670 468call p_verify_reprepare_count(0); 469SUCCESS 470 471execute stmt; 472count(*) 4730 474call p_verify_reprepare_count(0); 475SUCCESS 476 477create temporary table t1 AS SELECT 1; 478execute stmt; 479count(*) 4801 481call p_verify_reprepare_count(1); 482SUCCESS 483 484execute stmt; 485count(*) 4861 487call p_verify_reprepare_count(0); 488SUCCESS 489 490deallocate prepare stmt; 491drop temporary table t1; 492drop table t1; 493===================================================================== 494Part 9: TABLE -> VIEW transitions 495===================================================================== 496create table t1 (a int); 497prepare stmt from "select * from t1"; 498execute stmt; 499a 500call p_verify_reprepare_count(0); 501SUCCESS 502 503drop table t1; 504create table t2 (a int); 505create view t1 as select * from t2; 506execute stmt; 507a 508call p_verify_reprepare_count(1); 509SUCCESS 510 511drop view t1; 512drop table t2; 513deallocate prepare stmt; 514===================================================================== 515Part 10: TEMPORARY TABLE -> NOTHING transitions 516===================================================================== 517create temporary table t1 (a int); 518prepare stmt from "select * from t1"; 519execute stmt; 520a 521call p_verify_reprepare_count(0); 522SUCCESS 523 524drop temporary table t1; 525execute stmt; 526ERROR 42S02: Table 'test.t1' doesn't exist 527call p_verify_reprepare_count(0); 528SUCCESS 529 530deallocate prepare stmt; 531===================================================================== 532Part 11: TEMPORARY TABLE -> TABLE transitions 533===================================================================== 534# Test 11-a: temporary table replaced by base table 535create table t1 (a int); 536insert into t1 (a) value (1); 537create temporary table t1 (a int); 538prepare stmt from "select * from t1"; 539execute stmt; 540a 541call p_verify_reprepare_count(0); 542SUCCESS 543 544drop temporary table t1; 545execute stmt; 546a 5471 548call p_verify_reprepare_count(1); 549SUCCESS 550 551select * from t1; 552a 5531 554drop table t1; 555deallocate prepare stmt; 556# Test 11-b: temporary table has precedence over base table with same name 557# temporary table disappears 558create table t1 (a int); 559create temporary table t1 as select 1 as a; 560prepare stmt from "select count(*) from t1"; 561execute stmt; 562count(*) 5631 564call p_verify_reprepare_count(0); 565SUCCESS 566 567execute stmt; 568count(*) 5691 570call p_verify_reprepare_count(0); 571SUCCESS 572 573drop temporary table t1; 574execute stmt; 575count(*) 5760 577call p_verify_reprepare_count(1); 578SUCCESS 579 580execute stmt; 581count(*) 5820 583call p_verify_reprepare_count(0); 584SUCCESS 585 586deallocate prepare stmt; 587drop table t1; 588===================================================================== 589Part 12: TEMPORARY TABLE -> TEMPORARY TABLE (DDL) transitions 590===================================================================== 591create temporary table t1 (a int); 592prepare stmt from "select a from t1"; 593execute stmt; 594a 595call p_verify_reprepare_count(0); 596SUCCESS 597 598drop temporary table t1; 599create temporary table t1 (a int, b int); 600execute stmt; 601a 602call p_verify_reprepare_count(1); 603SUCCESS 604 605select * from t1; 606a b 607drop temporary table t1; 608deallocate prepare stmt; 609===================================================================== 610Part 13: TEMPORARY TABLE -> VIEW transitions 611===================================================================== 612create temporary table t1 (a int); 613create table t2 (a int); 614prepare stmt from "select * from t1"; 615execute stmt; 616a 617call p_verify_reprepare_count(0); 618SUCCESS 619 620drop temporary table t1; 621create view t1 as select * from t2; 622execute stmt; 623a 624call p_verify_reprepare_count(1); 625SUCCESS 626 627drop view t1; 628drop table t2; 629deallocate prepare stmt; 630===================================================================== 631Part 14: VIEW -> NOTHING transitions 632===================================================================== 633create table t2 (a int); 634create view t1 as select * from t2; 635prepare stmt from "select * from t1"; 636execute stmt; 637a 638drop view t1; 639execute stmt; 640ERROR 42S02: Table 'test.t1' doesn't exist 641call p_verify_reprepare_count(0); 642SUCCESS 643 644execute stmt; 645ERROR 42S02: Table 'test.t1' doesn't exist 646call p_verify_reprepare_count(0); 647SUCCESS 648 649drop table t2; 650deallocate prepare stmt; 651===================================================================== 652Part 15: VIEW -> TABLE transitions 653===================================================================== 654create table t2 (a int); 655create view t1 as select * from t2; 656prepare stmt from "select * from t1"; 657execute stmt; 658a 659call p_verify_reprepare_count(0); 660SUCCESS 661 662drop view t1; 663create table t1 (a int); 664execute stmt; 665a 666call p_verify_reprepare_count(1); 667SUCCESS 668 669drop table t2; 670drop table t1; 671deallocate prepare stmt; 672===================================================================== 673Part 16: VIEW -> TEMPORARY TABLE transitions 674===================================================================== 675# 676# Test 1: Merged view 677# 678create table t2 (a int); 679insert into t2 (a) values (1); 680create view t1 as select * from t2; 681prepare stmt from "select * from t1"; 682execute stmt; 683a 6841 685call p_verify_reprepare_count(0); 686SUCCESS 687 688create temporary table t1 (a int); 689execute stmt; 690a 6911 692call p_verify_reprepare_count(0); 693SUCCESS 694 695drop view t1; 696execute stmt; 697ERROR 42S02: Table 'test.t1' doesn't exist 698call p_verify_reprepare_count(0); 699SUCCESS 700 701drop table t2; 702drop temporary table t1; 703deallocate prepare stmt; 704# 705# Test 2: Materialized view 706# 707create table t2 (a int); 708insert into t2 (a) values (1); 709create algorithm = temptable view t1 as select * from t2; 710prepare stmt from "select * from t1"; 711execute stmt; 712a 7131 714call p_verify_reprepare_count(0); 715SUCCESS 716 717create temporary table t1 (a int); 718execute stmt; 719a 7201 721call p_verify_reprepare_count(0); 722SUCCESS 723 724drop view t1; 725execute stmt; 726ERROR 42S02: Table 'test.t1' doesn't exist 727call p_verify_reprepare_count(0); 728SUCCESS 729 730drop table t2; 731drop temporary table t1; 732deallocate prepare stmt; 733# 734# Test 3: View referencing an Information schema table 735# 736create view t1 as select table_name from information_schema.views; 737prepare stmt from "select * from t1"; 738execute stmt; 739table_name 740t1 741call p_verify_reprepare_count(0); 742SUCCESS 743 744create temporary table t1 (a int); 745execute stmt; 746table_name 747t1 748call p_verify_reprepare_count(0); 749SUCCESS 750 751drop view t1; 752execute stmt; 753table_name 754call p_verify_reprepare_count(0); 755SUCCESS 756 757drop temporary table t1; 758deallocate prepare stmt; 759===================================================================== 760Part 17: VIEW -> VIEW (DDL) transitions 761===================================================================== 762create table t2 (a int); 763insert into t2 values (10), (20), (30); 764create view t1 as select a, 2*a as b, 3*a as c from t2; 765select * from t1; 766a b c 76710 20 30 76820 40 60 76930 60 90 770prepare stmt from "select * from t1"; 771execute stmt; 772a b c 77310 20 30 77420 40 60 77530 60 90 776drop view t1; 777create view t1 as select a, 2*a as b, 5*a as c from t2; 778select * from t1; 779a b c 78010 20 50 78120 40 100 78230 60 150 783# This is actually a test case for Bug#11748352 (36002 Prepared 784# statements: if a view used in a statement is replaced, bad data). 785execute stmt; 786a b c 78710 20 50 78820 40 100 78930 60 150 790call p_verify_reprepare_count(1); 791SUCCESS 792 793flush table t2; 794execute stmt; 795a b c 79610 20 50 79720 40 100 79830 60 150 799call p_verify_reprepare_count(1); 800SUCCESS 801 802# Check that we properly handle ALTER VIEW statements. 803execute stmt; 804a b c 80510 20 50 80620 40 100 80730 60 150 808call p_verify_reprepare_count(0); 809SUCCESS 810 811alter view t1 as select a, 3*a as b, 4*a as c from t2; 812execute stmt; 813a b c 81410 30 40 81520 60 80 81630 90 120 817call p_verify_reprepare_count(1); 818SUCCESS 819 820execute stmt; 821a b c 82210 30 40 82320 60 80 82430 90 120 825call p_verify_reprepare_count(0); 826SUCCESS 827 828execute stmt; 829a b c 83010 30 40 83120 60 80 83230 90 120 833call p_verify_reprepare_count(0); 834SUCCESS 835 836select * from t1; 837a b c 83810 30 40 83920 60 80 84030 90 120 841# Check that DROP & CREATE is properly handled under LOCK TABLES. 842drop view t1; 843flush tables; 844create view t1 as select a, 5*a as b, 6*a as c from t2; 845lock tables t1 read, t2 read; 846execute stmt; 847a b c 84810 50 60 84920 100 120 85030 150 180 851call p_verify_reprepare_count(1); 852SUCCESS 853 854execute stmt; 855a b c 85610 50 60 85720 100 120 85830 150 180 859call p_verify_reprepare_count(0); 860SUCCESS 861 862execute stmt; 863a b c 86410 50 60 86520 100 120 86630 150 180 867call p_verify_reprepare_count(0); 868SUCCESS 869 870unlock tables; 871# ... and once again... 872drop view t1; 873create view t1 as select a, 6*a as b, 7*a as c from t2; 874lock tables t1 read, t2 read; 875execute stmt; 876a b c 87710 60 70 87820 120 140 87930 180 210 880call p_verify_reprepare_count(1); 881SUCCESS 882 883execute stmt; 884a b c 88510 60 70 88620 120 140 88730 180 210 888call p_verify_reprepare_count(0); 889SUCCESS 890 891execute stmt; 892a b c 89310 60 70 89420 120 140 89530 180 210 896call p_verify_reprepare_count(0); 897SUCCESS 898 899unlock tables; 900# Check that ALTER VIEW is properly handled under LOCK TABLES. 901alter view t1 as select a, 7*a as b, 8*a as c from t2; 902lock tables t1 read, t2 read; 903execute stmt; 904a b c 90510 70 80 90620 140 160 90730 210 240 908call p_verify_reprepare_count(1); 909SUCCESS 910 911execute stmt; 912a b c 91310 70 80 91420 140 160 91530 210 240 916call p_verify_reprepare_count(0); 917SUCCESS 918 919execute stmt; 920a b c 92110 70 80 92220 140 160 92330 210 240 924call p_verify_reprepare_count(0); 925SUCCESS 926 927unlock tables; 928drop table t2; 929drop view t1; 930deallocate prepare stmt; 931# Check that DROP & CREATE is properly handled under LOCK TABLES when 932# LOCK TABLES does not contain the complete set of views. 933create table t1(a int); 934insert into t1 values (1), (2), (3); 935create view v1 as select a from t1; 936lock tables t1 read, v1 read; 937prepare stmt from 'select * from v1'; 938execute stmt; 939a 9401 9412 9423 943call p_verify_reprepare_count(0); 944SUCCESS 945 946execute stmt; 947a 9481 9492 9503 951call p_verify_reprepare_count(0); 952SUCCESS 953 954unlock tables; 955drop view v1; 956create view v1 as select 2*a from t1; 957lock tables t1 read; 958execute stmt; 959ERROR HY000: Table 'v1' was not locked with LOCK TABLES 960unlock tables; 961drop table t1; 962drop view v1; 963deallocate prepare stmt; 964# Check that ALTER VIEW is properly handled under LOCK TABLES when 965# LOCK TABLES does not contain the complete set of views. 966create table t1(a int); 967insert into t1 values (1), (2), (3); 968create view v1 as select a from t1; 969lock tables t1 read, v1 read; 970prepare stmt from 'select * from v1'; 971execute stmt; 972a 9731 9742 9753 976call p_verify_reprepare_count(0); 977SUCCESS 978 979execute stmt; 980a 9811 9822 9833 984call p_verify_reprepare_count(0); 985SUCCESS 986 987unlock tables; 988alter view v1 as select 2*a from t1; 989lock tables t1 read; 990execute stmt; 991ERROR HY000: Table 'v1' was not locked with LOCK TABLES 992unlock tables; 993drop table t1; 994drop view v1; 995deallocate prepare stmt; 996===================================================================== 997Part 18: VIEW -> VIEW (VIEW dependencies) transitions 998===================================================================== 999# Part 18a: dependent function has changed 1000create table t1 (a int); 1001insert into t1 (a) values (1), (2), (3); 1002create function f1() returns int return (select max(a) from t1); 1003create view v1 as select f1(); 1004prepare stmt from "select * from v1"; 1005execute stmt; 1006f1() 10073 1008execute stmt; 1009f1() 10103 1011call p_verify_reprepare_count(0); 1012SUCCESS 1013 1014drop function f1; 1015create function f1() returns int return 2; 1016# XXX: Used to be another manifestation of Bug#12093. 1017# We only used to get a different error 1018# message because the non-existing procedure error is masked 1019# by the view. 1020execute stmt; 1021f1() 10222 1023execute stmt; 1024f1() 10252 1026call p_verify_reprepare_count(1); 1027SUCCESS 1028 1029# Part 18b: dependent procedure has changed (referred to via a function) 1030create table t2 (a int); 1031insert into t2 (a) values (4), (5), (6); 1032drop function f1; 1033create function f1() returns int 1034begin 1035declare x int; 1036call p1(x); 1037return x; 1038end| 1039create procedure p1(out x int) select max(a) from t1 into x; 1040prepare stmt from "select * from v1"; 1041execute stmt; 1042f1() 10433 1044execute stmt; 1045f1() 10463 1047call p_verify_reprepare_count(0); 1048SUCCESS 1049 1050drop procedure p1; 1051create procedure p1(out x int) select max(a) from t2 into x; 1052# XXX: used to be a bug. The prelocked list was not invalidated 1053# and we kept opening table t1, whereas the procedure 1054# is now referring to table t2 1055execute stmt; 1056f1() 10576 1058call p_verify_reprepare_count(1); 1059SUCCESS 1060 1061flush table t1; 1062execute stmt; 1063f1() 10646 1065call p_verify_reprepare_count(0); 1066SUCCESS 1067 1068execute stmt; 1069f1() 10706 1071# Test 18-c: dependent VIEW has changed 1072drop view v1; 1073create view v2 as select a from t1; 1074create view v1 as select * from v2; 1075prepare stmt from "select * from v1"; 1076execute stmt; 1077a 10781 10792 10803 1081execute stmt; 1082a 10831 10842 10853 1086call p_verify_reprepare_count(0); 1087SUCCESS 1088 1089drop view v2; 1090create view v2 as select a from t2; 1091execute stmt; 1092a 10934 10945 10956 1096execute stmt; 1097a 10984 10995 11006 1101call p_verify_reprepare_count(1); 1102SUCCESS 1103 1104flush table t1; 1105execute stmt; 1106a 11074 11085 11096 1110call p_verify_reprepare_count(0); 1111SUCCESS 1112 1113execute stmt; 1114a 11154 11165 11176 1118# Test 18-d: dependent TABLE has changed 1119drop view v2; 1120create table v2 as select * from t1; 1121execute stmt; 1122a 11231 11242 11253 1126call p_verify_reprepare_count(1); 1127SUCCESS 1128 1129execute stmt; 1130a 11311 11322 11333 1134call p_verify_reprepare_count(0); 1135SUCCESS 1136 1137drop table v2; 1138create table v2 (a int unique) as select * from t2; 1139execute stmt; 1140a 11414 11425 11436 1144call p_verify_reprepare_count(1); 1145SUCCESS 1146 1147execute stmt; 1148a 11494 11505 11516 1152call p_verify_reprepare_count(0); 1153SUCCESS 1154 1155# Test 18-e: dependent TABLE trigger has changed 1156prepare stmt from "insert into v1 (a) values (?)"; 1157set @var= 7; 1158execute stmt using @var; 1159call p_verify_reprepare_count(0); 1160SUCCESS 1161 1162create trigger v2_bi before insert on v2 for each row set @message="v2_bi"; 1163set @var=8; 1164execute stmt using @var; 1165call p_verify_reprepare_count(1); 1166SUCCESS 1167 1168select @message; 1169@message 1170v2_bi 1171drop trigger v2_bi; 1172set @message=null; 1173set @var=9; 1174execute stmt using @var; 1175call p_verify_reprepare_count(1); 1176SUCCESS 1177 1178select @message; 1179@message 1180NULL 1181create trigger v2_bi after insert on v2 for each row set @message="v2_ai"; 1182set @var= 10; 1183execute stmt using @var; 1184call p_verify_reprepare_count(1); 1185SUCCESS 1186 1187select @message; 1188@message 1189v2_ai 1190select * from v1; 1191a 11924 11935 11946 11957 11968 11979 119810 1199# Cleanup 1200drop table if exists t1, t2, v1, v2; 1201drop view if exists v1, v2; 1202drop function f1; 1203drop procedure p1; 1204deallocate prepare stmt; 1205===================================================================== 1206Part 19: Special tables (INFORMATION_SCHEMA) 1207===================================================================== 1208prepare stmt from 1209"select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE 1210 from INFORMATION_SCHEMA.ROUTINES where 1211 routine_name='p1'"; 1212create procedure p1() select "hi there"; 1213execute stmt; 1214ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE 1215test p1 PROCEDURE 1216execute stmt; 1217ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE 1218test p1 PROCEDURE 1219drop procedure p1; 1220create procedure p1() select "hi there, again"; 1221execute stmt; 1222ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE 1223test p1 PROCEDURE 1224execute stmt; 1225ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE 1226test p1 PROCEDURE 1227call p_verify_reprepare_count(0); 1228SUCCESS 1229 1230drop procedure p1; 1231deallocate prepare stmt; 1232===================================================================== 1233Part 20: Special tables (log tables) 1234===================================================================== 1235prepare stmt from 1236"select * from mysql.general_log where argument='IMPOSSIBLE QUERY STRING'"; 1237execute stmt; 1238execute stmt; 1239execute stmt; 1240execute stmt; 1241call p_verify_reprepare_count(0); 1242SUCCESS 1243 1244deallocate prepare stmt; 1245===================================================================== 1246Part 21: Special tables (system tables) 1247===================================================================== 1248prepare stmt from 1249"select type, db, name from mysql.proc where name='p1'"; 1250create procedure p1() select "hi there"; 1251execute stmt; 1252type db name 1253PROCEDURE test p1 1254execute stmt; 1255type db name 1256PROCEDURE test p1 1257drop procedure p1; 1258create procedure p1() select "hi there, again"; 1259execute stmt; 1260type db name 1261PROCEDURE test p1 1262execute stmt; 1263type db name 1264PROCEDURE test p1 1265call p_verify_reprepare_count(0); 1266SUCCESS 1267 1268drop procedure p1; 1269deallocate prepare stmt; 1270===================================================================== 1271Part 22: Special tables (views temp tables) 1272===================================================================== 1273create table t1 (a int); 1274create algorithm=temptable view v1 as select a*a as a2 from t1; 1275# Using a temporary table internally should not confuse the prepared 1276# statement code, and should not raise ER_PS_INVALIDATED errors 1277show create view v1; 1278View Create View character_set_client collation_connection 1279v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (`t1`.`a` * `t1`.`a`) AS `a2` from `t1` latin1 latin1_swedish_ci 1280prepare stmt from "select * from v1"; 1281insert into t1 values (1), (2), (3); 1282execute stmt; 1283a2 12841 12854 12869 1287execute stmt; 1288a2 12891 12904 12919 1292insert into t1 values (4), (5), (6); 1293execute stmt; 1294a2 12951 12964 12979 129816 129925 130036 1301execute stmt; 1302a2 13031 13044 13059 130616 130725 130836 1309call p_verify_reprepare_count(0); 1310SUCCESS 1311 1312drop table t1; 1313drop view v1; 1314===================================================================== 1315Part 23: Special statements 1316===================================================================== 1317# SQLCOM_ALTER_TABLE: 1318create table t1 (a int); 1319prepare stmt from "alter table t1 add column b int"; 1320execute stmt; 1321drop table t1; 1322create table t1 (a1 int, a2 int); 1323# t1 has changed, and it's does not lead to reprepare 1324execute stmt; 1325alter table t1 drop column b; 1326execute stmt; 1327alter table t1 drop column b; 1328execute stmt; 1329call p_verify_reprepare_count(0); 1330SUCCESS 1331 1332drop table t1; 1333# SQLCOM_REPAIR: 1334create table t1 (a int); 1335insert into t1 values (1), (2), (3); 1336prepare stmt from "repair table t1"; 1337execute stmt; 1338Table Op Msg_type Msg_text 1339test.t1 repair status OK 1340execute stmt; 1341Table Op Msg_type Msg_text 1342test.t1 repair status OK 1343drop table t1; 1344create table t1 (a1 int, a2 int); 1345insert into t1 values (1, 10), (2, 20), (3, 30); 1346# t1 has changed, and it's does not lead to reprepare 1347execute stmt; 1348Table Op Msg_type Msg_text 1349test.t1 repair status OK 1350alter table t1 add column b varchar(50) default NULL; 1351execute stmt; 1352Table Op Msg_type Msg_text 1353test.t1 repair status OK 1354call p_verify_reprepare_count(0); 1355SUCCESS 1356 1357alter table t1 drop column b; 1358execute stmt; 1359Table Op Msg_type Msg_text 1360test.t1 repair status OK 1361call p_verify_reprepare_count(0); 1362SUCCESS 1363 1364# SQLCOM_ANALYZE: 1365prepare stmt from "analyze table t1"; 1366execute stmt; 1367Table Op Msg_type Msg_text 1368test.t1 analyze status OK 1369drop table t1; 1370create table t1 (a1 int, a2 int); 1371insert into t1 values (1, 10), (2, 20), (3, 30); 1372# t1 has changed, and it's not a problem 1373execute stmt; 1374Table Op Msg_type Msg_text 1375test.t1 analyze status OK 1376alter table t1 add column b varchar(50) default NULL; 1377execute stmt; 1378Table Op Msg_type Msg_text 1379test.t1 analyze status OK 1380alter table t1 drop column b; 1381execute stmt; 1382Table Op Msg_type Msg_text 1383test.t1 analyze status OK 1384call p_verify_reprepare_count(0); 1385SUCCESS 1386 1387# SQLCOM_OPTIMIZE: 1388prepare stmt from "optimize table t1"; 1389execute stmt; 1390Table Op Msg_type Msg_text 1391test.t1 optimize status Table is already up to date 1392drop table t1; 1393create table t1 (a1 int, a2 int); 1394insert into t1 values (1, 10), (2, 20), (3, 30); 1395# t1 has changed, and it's not a problem 1396execute stmt; 1397Table Op Msg_type Msg_text 1398test.t1 optimize status OK 1399alter table t1 add column b varchar(50) default NULL; 1400execute stmt; 1401Table Op Msg_type Msg_text 1402test.t1 optimize status OK 1403alter table t1 drop column b; 1404execute stmt; 1405Table Op Msg_type Msg_text 1406test.t1 optimize status OK 1407call p_verify_reprepare_count(0); 1408SUCCESS 1409 1410drop table t1; 1411# SQLCOM_SHOW_CREATE_PROC: 1412prepare stmt from "show create procedure p1"; 1413execute stmt; 1414ERROR 42000: PROCEDURE p1 does not exist 1415execute stmt; 1416ERROR 42000: PROCEDURE p1 does not exist 1417create procedure p1() begin end; 1418execute stmt; 1419execute stmt; 1420drop procedure p1; 1421create procedure p1(x int, y int) begin end; 1422execute stmt; 1423execute stmt; 1424drop procedure p1; 1425execute stmt; 1426ERROR 42000: PROCEDURE p1 does not exist 1427execute stmt; 1428ERROR 42000: PROCEDURE p1 does not exist 1429call p_verify_reprepare_count(0); 1430SUCCESS 1431 1432# SQLCOM_SHOW_CREATE_FUNC: 1433prepare stmt from "show create function f1"; 1434execute stmt; 1435ERROR 42000: FUNCTION f1 does not exist 1436execute stmt; 1437ERROR 42000: FUNCTION f1 does not exist 1438create function f1() returns int return 0; 1439execute stmt; 1440execute stmt; 1441drop function f1; 1442create function f1(x int, y int) returns int return x+y; 1443execute stmt; 1444execute stmt; 1445drop function f1; 1446execute stmt; 1447ERROR 42000: FUNCTION f1 does not exist 1448execute stmt; 1449ERROR 42000: FUNCTION f1 does not exist 1450call p_verify_reprepare_count(0); 1451SUCCESS 1452 1453# SQLCOM_SHOW_CREATE_TRIGGER: 1454create table t1 (a int); 1455prepare stmt from "show create trigger t1_bi"; 1456execute stmt; 1457ERROR HY000: Trigger does not exist 1458execute stmt; 1459ERROR HY000: Trigger does not exist 1460create trigger t1_bi before insert on t1 for each row set @message= "t1_bi"; 1461execute stmt; 1462execute stmt; 1463drop trigger t1_bi; 1464create trigger t1_bi before insert on t1 for each row set @message= "t1_bi (2)"; 1465execute stmt; 1466execute stmt; 1467drop trigger t1_bi; 1468execute stmt; 1469ERROR HY000: Trigger does not exist 1470execute stmt; 1471ERROR HY000: Trigger does not exist 1472call p_verify_reprepare_count(0); 1473SUCCESS 1474 1475drop table t1; 1476deallocate prepare stmt; 1477===================================================================== 1478Part 24: Testing the strength of TABLE_SHARE version 1479===================================================================== 1480# Test 24-a: number of columns 1481create table t1 (a int); 1482prepare stmt from "select a from t1"; 1483execute stmt; 1484a 1485call p_verify_reprepare_count(0); 1486SUCCESS 1487 1488alter table t1 add column b varchar(50) default NULL; 1489execute stmt; 1490a 1491call p_verify_reprepare_count(1); 1492SUCCESS 1493 1494execute stmt; 1495a 1496call p_verify_reprepare_count(0); 1497SUCCESS 1498 1499# Test 24-b: column name 1500alter table t1 change b c int; 1501execute stmt; 1502a 1503call p_verify_reprepare_count(1); 1504SUCCESS 1505 1506execute stmt; 1507a 1508call p_verify_reprepare_count(0); 1509SUCCESS 1510 1511# Test 24-c: column type 1512alter table t1 change a a varchar(10); 1513execute stmt; 1514a 1515call p_verify_reprepare_count(1); 1516SUCCESS 1517 1518execute stmt; 1519a 1520call p_verify_reprepare_count(0); 1521SUCCESS 1522 1523# Test 24-d: column type length 1524alter table t1 change a a varchar(20); 1525execute stmt; 1526a 1527call p_verify_reprepare_count(1); 1528SUCCESS 1529 1530execute stmt; 1531a 1532call p_verify_reprepare_count(0); 1533SUCCESS 1534 1535# Test 24-e: column NULL property 1536alter table t1 change a a varchar(20) NOT NULL; 1537execute stmt; 1538a 1539call p_verify_reprepare_count(1); 1540SUCCESS 1541 1542execute stmt; 1543a 1544call p_verify_reprepare_count(0); 1545SUCCESS 1546 1547# Test 24-f: column DEFAULT 1548alter table t1 change c c int DEFAULT 20; 1549execute stmt; 1550a 1551call p_verify_reprepare_count(1); 1552SUCCESS 1553 1554execute stmt; 1555a 1556call p_verify_reprepare_count(0); 1557SUCCESS 1558 1559# Test 24-g: number of keys 1560create unique index t1_a_idx on t1 (a); 1561execute stmt; 1562a 1563call p_verify_reprepare_count(1); 1564SUCCESS 1565 1566execute stmt; 1567a 1568call p_verify_reprepare_count(0); 1569SUCCESS 1570 1571# Test 24-h: changing index uniqueness 1572drop index t1_a_idx on t1; 1573create index t1_a_idx on t1 (a); 1574execute stmt; 1575a 1576call p_verify_reprepare_count(1); 1577SUCCESS 1578 1579execute stmt; 1580a 1581call p_verify_reprepare_count(0); 1582SUCCESS 1583 1584# Cleanup 1585drop table t1; 1586deallocate prepare stmt; 1587===================================================================== 1588Testing reported bugs 1589===================================================================== 1590# 1591# Bug#27420 A combination of PS and view operations cause 1592# error + assertion on shutdown 1593# 1594drop table if exists t_27420_100; 1595drop table if exists t_27420_101; 1596drop view if exists v_27420; 1597create table t_27420_100(a int); 1598insert into t_27420_100 values (1), (2); 1599create table t_27420_101(a int); 1600insert into t_27420_101 values (1), (2); 1601create view v_27420 as select t_27420_100.a X, t_27420_101.a Y 1602from t_27420_100, t_27420_101 1603where t_27420_100.a=t_27420_101.a; 1604prepare stmt from "select * from v_27420"; 1605execute stmt; 1606X Y 16071 1 16082 2 1609call p_verify_reprepare_count(0); 1610SUCCESS 1611 1612drop view v_27420; 1613create table v_27420(X int, Y int); 1614execute stmt; 1615X Y 1616call p_verify_reprepare_count(1); 1617SUCCESS 1618 1619drop table v_27420; 1620create table v_27420 (a int, b int, filler char(200)); 1621execute stmt; 1622a b filler 1623call p_verify_reprepare_count(1); 1624SUCCESS 1625 1626drop table t_27420_100; 1627drop table t_27420_101; 1628drop table v_27420; 1629deallocate prepare stmt; 1630# 1631# Bug#27430 Crash in subquery code when in PS and table DDL changed 1632# after PREPARE 1633# 1634drop table if exists t_27430_1; 1635drop table if exists t_27430_2; 1636create table t_27430_1 (a int not null, oref int not null, key(a)); 1637insert into t_27430_1 values 1638(1, 1), 1639(1, 1234), 1640(2, 3), 1641(2, 1234), 1642(3, 1234); 1643create table t_27430_2 (a int not null, oref int not null); 1644insert into t_27430_2 values 1645(1, 1), 1646(2, 2), 1647(1234, 3), 1648(1234, 4); 1649prepare stmt from 1650"select oref, a, a in (select a from t_27430_1 where oref=t_27430_2.oref) Z from t_27430_2"; 1651execute stmt; 1652oref a Z 16531 1 1 16542 2 0 16553 1234 0 16564 1234 0 1657call p_verify_reprepare_count(0); 1658SUCCESS 1659 1660drop table t_27430_1, t_27430_2; 1661create table t_27430_1 (a int, oref int, key(a)); 1662insert into t_27430_1 values 1663(1, 1), 1664(1, NULL), 1665(2, 3), 1666(2, NULL), 1667(3, NULL); 1668create table t_27430_2 (a int, oref int); 1669insert into t_27430_2 values 1670(1, 1), 1671(2,2), 1672(NULL, 3), 1673(NULL, 4); 1674execute stmt; 1675oref a Z 16761 1 1 16772 2 0 16783 NULL NULL 16794 NULL 0 1680call p_verify_reprepare_count(1); 1681SUCCESS 1682 1683drop table t_27430_1; 1684drop table t_27430_2; 1685deallocate prepare stmt; 1686# 1687# Bug#27690 Re-execution of prepared statement after table 1688# was replaced with a view crashes 1689# 1690drop table if exists t_27690_1; 1691drop view if exists v_27690_1; 1692drop table if exists v_27690_2; 1693create table t_27690_1 (a int, b int); 1694insert into t_27690_1 values (1,1),(2,2); 1695create table v_27690_1 as select * from t_27690_1; 1696create table v_27690_2 as select * from t_27690_1; 1697prepare stmt from "select * from v_27690_1, v_27690_2"; 1698execute stmt; 1699a b a b 17001 1 1 1 17012 2 1 1 17021 1 2 2 17032 2 2 2 1704execute stmt; 1705a b a b 17061 1 1 1 17072 2 1 1 17081 1 2 2 17092 2 2 2 1710drop table v_27690_1; 1711execute stmt; 1712ERROR 42S02: Table 'test.v_27690_1' doesn't exist 1713execute stmt; 1714ERROR 42S02: Table 'test.v_27690_1' doesn't exist 1715call p_verify_reprepare_count(0); 1716SUCCESS 1717 1718create view v_27690_1 as select A.a, A.b from t_27690_1 A, t_27690_1 B; 1719execute stmt; 1720a b a b 17211 1 1 1 17222 2 1 1 17231 1 1 1 17242 2 1 1 17251 1 2 2 17262 2 2 2 17271 1 2 2 17282 2 2 2 1729call p_verify_reprepare_count(1); 1730SUCCESS 1731 1732execute stmt; 1733a b a b 17341 1 1 1 17352 2 1 1 17361 1 1 1 17372 2 1 1 17381 1 2 2 17392 2 2 2 17401 1 2 2 17412 2 2 2 1742call p_verify_reprepare_count(0); 1743SUCCESS 1744 1745drop table t_27690_1; 1746drop view v_27690_1; 1747drop table v_27690_2; 1748deallocate prepare stmt; 1749#===================================================================== 1750# 1751# Bug#21294 Executing a prepared statement that executes 1752# a stored function which was recreat 1753# 1754create function f1() returns int return 10; 1755prepare stmt from "select f1()"; 1756execute stmt; 1757f1() 175810 1759drop function f1; 1760create function f1() returns int return 10; 1761execute stmt; 1762f1() 176310 1764drop function f1; 1765create function f1() returns int return 20; 1766execute stmt; 1767f1() 176820 1769call p_verify_reprepare_count(2); 1770SUCCESS 1771 1772drop function f1; 1773deallocate prepare stmt; 1774# 1775# Bug#12093 SP not found on second PS execution if another thread drops 1776# other SP in between 1777# 1778drop table if exists t_12093; 1779drop function if exists f_12093; 1780drop function if exists f_12093_unrelated; 1781drop procedure if exists p_12093; 1782drop view if exists v_12093_unrelated; 1783create table t_12093 (a int); 1784create function f_12093() returns int return (select count(*) from t_12093); 1785create procedure p_12093(a int) select * from t_12093; 1786create function f_12093_unrelated() returns int return 2; 1787create procedure p_12093_unrelated() begin end; 1788create view v_12093_unrelated as select * from t_12093; 1789prepare stmt_sf from 'select f_12093();'; 1790prepare stmt_sp from 'call p_12093(f_12093())'; 1791execute stmt_sf; 1792f_12093() 17930 1794execute stmt_sp; 1795a 1796drop function f_12093_unrelated; 1797# XXX: used to be a bug 1798execute stmt_sf; 1799f_12093() 18000 1801# XXX: used to be a bug 1802execute stmt_sp; 1803a 1804# XXX: used to be a bug 1805execute stmt_sf; 1806f_12093() 18070 1808# XXX: used to be a bug 1809execute stmt_sp; 1810a 1811prepare stmt_sf from 'select f_12093();'; 1812prepare stmt_sp from 'call p_12093(f_12093())'; 1813execute stmt_sf; 1814f_12093() 18150 1816execute stmt_sp; 1817a 1818drop procedure p_12093_unrelated; 1819# XXX: used to be a bug 1820execute stmt_sf; 1821f_12093() 18220 1823# XXX: used to be a bug 1824execute stmt_sp; 1825a 1826# XXX: used to be a bug 1827execute stmt_sf; 1828f_12093() 18290 1830# XXX: used to be a bug 1831execute stmt_sp; 1832a 1833prepare stmt_sf from 'select f_12093();'; 1834prepare stmt_sp from 'call p_12093(f_12093())'; 1835execute stmt_sf; 1836f_12093() 18370 1838execute stmt_sp; 1839a 1840drop view v_12093_unrelated; 1841# XXX: used to be a bug 1842execute stmt_sf; 1843f_12093() 18440 1845# XXX: used to be a bug 1846execute stmt_sp; 1847a 1848# XXX: used to be a bug 1849execute stmt_sf; 1850f_12093() 18510 1852# XXX: used to be a bug 1853execute stmt_sp; 1854a 1855call p_verify_reprepare_count(6); 1856SUCCESS 1857 1858drop table t_12093; 1859drop function f_12093; 1860drop procedure p_12093; 1861deallocate prepare stmt_sf; 1862deallocate prepare stmt_sp; 1863===================================================================== 1864Ensure that metadata validation is performed for every type of 1865SQL statement where it is needed. 1866===================================================================== 1867# 1868# SQLCOM_SELECT 1869# 1870drop table if exists t1; 1871create table t1 (a int); 1872prepare stmt from "select 1 as res from dual where (1) in (select * from t1)"; 1873drop table t1; 1874create table t1 (x int); 1875execute stmt; 1876res 1877drop table t1; 1878deallocate prepare stmt; 1879call p_verify_reprepare_count(1); 1880SUCCESS 1881 1882# 1883# SQLCOM_CREATE_TABLE 1884# 1885drop table if exists t1; 1886drop table if exists t2; 1887create table t1 (a int); 1888prepare stmt from 'create table t2 as select * from t1'; 1889execute stmt; 1890drop table t2; 1891execute stmt; 1892drop table t2; 1893execute stmt; 1894call p_verify_reprepare_count(0); 1895SUCCESS 1896 1897execute stmt; 1898ERROR 42S01: Table 't2' already exists 1899call p_verify_reprepare_count(1); 1900SUCCESS 1901 1902execute stmt; 1903ERROR 42S01: Table 't2' already exists 1904call p_verify_reprepare_count(0); 1905SUCCESS 1906 1907drop table t2; 1908create temporary table t2 (a int); 1909execute stmt; 1910call p_verify_reprepare_count(0); 1911SUCCESS 1912 1913execute stmt; 1914ERROR 42S01: Table 't2' already exists 1915call p_verify_reprepare_count(1); 1916SUCCESS 1917 1918drop temporary table t2; 1919execute stmt; 1920ERROR 42S01: Table 't2' already exists 1921call p_verify_reprepare_count(0); 1922SUCCESS 1923 1924drop table t2; 1925execute stmt; 1926call p_verify_reprepare_count(0); 1927SUCCESS 1928 1929drop table t2; 1930create view t2 as select 1; 1931execute stmt; 1932Got one of the listed errors 1933call p_verify_reprepare_count(1); 1934SUCCESS 1935 1936execute stmt; 1937Got one of the listed errors 1938call p_verify_reprepare_count(0); 1939SUCCESS 1940 1941drop view t2; 1942drop table t1; 1943create table t1 (x varchar(20)); 1944execute stmt; 1945call p_verify_reprepare_count(1); 1946SUCCESS 1947 1948select * from t2; 1949x 1950drop table t2; 1951execute stmt; 1952call p_verify_reprepare_count(0); 1953SUCCESS 1954 1955drop table t2; 1956alter table t1 add column y decimal(10,3); 1957execute stmt; 1958call p_verify_reprepare_count(1); 1959SUCCESS 1960 1961select * from t2; 1962x y 1963drop table t2; 1964execute stmt; 1965call p_verify_reprepare_count(0); 1966SUCCESS 1967 1968drop table t1; 1969deallocate prepare stmt; 1970create table t1 (a int); 1971insert into t1 (a) values (1); 1972prepare stmt from "create temporary table if not exists t2 as select * from t1"; 1973execute stmt; 1974drop table t2; 1975execute stmt; 1976call p_verify_reprepare_count(0); 1977SUCCESS 1978 1979execute stmt; 1980Warnings: 1981Note 1050 Table 't2' already exists 1982call p_verify_reprepare_count(1); 1983SUCCESS 1984 1985select * from t2; 1986a 19871 1988execute stmt; 1989Warnings: 1990Note 1050 Table 't2' already exists 1991call p_verify_reprepare_count(0); 1992SUCCESS 1993 1994select * from t2; 1995a 19961 1997drop table t2; 1998create temporary table t2 (a varchar(10)); 1999execute stmt; 2000Warnings: 2001Note 1050 Table 't2' already exists 2002select * from t2; 2003a 2004call p_verify_reprepare_count(1); 2005SUCCESS 2006 2007drop table t1; 2008create table t1 (x int); 2009execute stmt; 2010Warnings: 2011Note 1050 Table 't2' already exists 2012call p_verify_reprepare_count(1); 2013SUCCESS 2014 2015execute stmt; 2016Warnings: 2017Note 1050 Table 't2' already exists 2018call p_verify_reprepare_count(0); 2019SUCCESS 2020 2021drop table t1; 2022drop temporary table t2; 2023drop table t2; 2024deallocate prepare stmt; 2025create table t1 (a int); 2026prepare stmt from "create table t2 like t1"; 2027execute stmt; 2028call p_verify_reprepare_count(0); 2029SUCCESS 2030 2031drop table t2; 2032execute stmt; 2033call p_verify_reprepare_count(0); 2034SUCCESS 2035 2036drop table t2; 2037drop table t1; 2038execute stmt; 2039ERROR 42S02: Table 'test.t1' doesn't exist 2040call p_verify_reprepare_count(0); 2041SUCCESS 2042 2043execute stmt; 2044ERROR 42S02: Table 'test.t1' doesn't exist 2045call p_verify_reprepare_count(0); 2046SUCCESS 2047 2048create table t1 (x char(17)); 2049execute stmt; 2050call p_verify_reprepare_count(1); 2051SUCCESS 2052 2053drop table t2; 2054execute stmt; 2055call p_verify_reprepare_count(0); 2056SUCCESS 2057 2058drop table t2; 2059alter table t1 add column y time; 2060execute stmt; 2061call p_verify_reprepare_count(1); 2062SUCCESS 2063 2064select * from t2; 2065x y 2066drop table t2; 2067execute stmt; 2068call p_verify_reprepare_count(0); 2069SUCCESS 2070 2071drop table t1; 2072drop table t2; 2073deallocate prepare stmt; 2074# 2075# SQLCOM_UPDATE 2076# 2077drop table if exists t1, t2; 2078create table t1 (a int); 2079create table t2 (a int); 2080prepare stmt from "update t2 set a=a+1 where (1) in (select * from t1)"; 2081execute stmt; 2082drop table t1; 2083create table t1 (x int); 2084execute stmt; 2085drop table t1, t2; 2086deallocate prepare stmt; 2087# 2088# SQLCOM_INSERT 2089# 2090drop table if exists t1, t2; 2091create table t1 (a int); 2092create table t2 (a int); 2093prepare stmt from "insert into t2 set a=((1) in (select * from t1))"; 2094execute stmt; 2095drop table t1; 2096create table t1 (x int); 2097execute stmt; 2098drop table t1, t2; 2099deallocate prepare stmt; 2100# 2101# SQLCOM_INSERT_SELECT 2102# 2103drop table if exists t1, t2; 2104create table t1 (a int); 2105create table t2 (a int); 2106prepare stmt from "insert into t2 select * from t1"; 2107execute stmt; 2108drop table t1; 2109create table t1 (x int); 2110execute stmt; 2111drop table t1, t2; 2112deallocate prepare stmt; 2113# 2114# SQLCOM_REPLACE 2115# 2116drop table if exists t1, t2; 2117create table t1 (a int); 2118create table t2 (a int); 2119prepare stmt from "replace t2 set a=((1) in (select * from t1))"; 2120execute stmt; 2121drop table t1; 2122create table t1 (x int); 2123execute stmt; 2124drop table t1, t2; 2125deallocate prepare stmt; 2126# 2127# SQLCOM_REPLACE_SELECT 2128# 2129drop table if exists t1, t2; 2130create table t1 (a int); 2131create table t2 (a int); 2132prepare stmt from "replace t2 select * from t1"; 2133execute stmt; 2134drop table t1; 2135create table t1 (x int); 2136execute stmt; 2137drop table t1, t2; 2138deallocate prepare stmt; 2139# 2140# SQLCOM_DELETE 2141# 2142drop table if exists t1, t2; 2143create table t1 (a int); 2144create table t2 (a int); 2145prepare stmt from "delete from t2 where (1) in (select * from t1)"; 2146execute stmt; 2147drop table t1; 2148create table t1 (x int); 2149execute stmt; 2150drop table t1, t2; 2151deallocate prepare stmt; 2152# 2153# SQLCOM_DELETE_MULTI 2154# 2155drop table if exists t1, t2, t3; 2156create table t1 (a int); 2157create table t2 (a int); 2158create table t3 (a int); 2159prepare stmt from "delete t2, t3 from t2, t3 where (1) in (select * from t1)"; 2160execute stmt; 2161drop table t1; 2162create table t1 (x int); 2163execute stmt; 2164drop table t1, t2, t3; 2165deallocate prepare stmt; 2166# 2167# SQLCOM_UPDATE_MULTI 2168# 2169drop table if exists t1, t2, t3; 2170create table t1 (a int); 2171create table t2 (a int); 2172create table t3 (a int); 2173prepare stmt from "update t2, t3 set t3.a=t2.a, t2.a=null where (1) in (select * from t1)"; 2174drop table t1; 2175create table t1 (x int); 2176execute stmt; 2177drop table t1, t2, t3; 2178deallocate prepare stmt; 2179# Intermediate results: 8 SQLCOMs tested, 8 automatic reprepares 2180call p_verify_reprepare_count(8); 2181SUCCESS 2182 2183# 2184# SQLCOM_LOAD 2185# 2186drop table if exists t1; 2187create table t1 (a varchar(20)); 2188prepare stmt from "load data infile '../std_data_ln/words.dat' into table t1"; 2189ERROR HY000: This command is not supported in the prepared statement protocol yet 2190drop table t1; 2191# 2192# SQLCOM_SHOW_DATABASES 2193# 2194drop table if exists t1; 2195create table t1 (a int); 2196prepare stmt from "show databases where (1) in (select * from t1)"; 2197execute stmt; 2198Database 2199drop table t1; 2200create table t1 (x int); 2201execute stmt; 2202Database 2203drop table t1; 2204deallocate prepare stmt; 2205# 2206# SQLCOM_SHOW_TABLES 2207# 2208drop table if exists t1; 2209create table t1 (a int); 2210prepare stmt from "show tables where (1) in (select * from t1)"; 2211execute stmt; 2212Tables_in_test 2213drop table t1; 2214create table t1 (x int); 2215execute stmt; 2216Tables_in_test 2217drop table t1; 2218deallocate prepare stmt; 2219# 2220# SQLCOM_SHOW_FIELDS 2221# 2222drop table if exists t1; 2223create table t1 (a int); 2224prepare stmt from "show fields from t1 where (1) in (select * from t1)"; 2225execute stmt; 2226Field Type Null Key Default Extra 2227drop table t1; 2228create table t1 (x int); 2229execute stmt; 2230Field Type Null Key Default Extra 2231drop table t1; 2232deallocate prepare stmt; 2233# 2234# SQLCOM_SHOW_KEYS 2235# 2236drop table if exists t1; 2237create table t1 (a int); 2238prepare stmt from "show keys from t1 where (1) in (select * from t1)"; 2239execute stmt; 2240Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 2241drop table t1; 2242create table t1 (x int); 2243execute stmt; 2244Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 2245drop table t1; 2246deallocate prepare stmt; 2247# 2248# SQLCOM_SHOW_VARIABLES 2249# 2250drop table if exists t1; 2251create table t1 (a int); 2252prepare stmt from "show variables where (1) in (select * from t1)"; 2253execute stmt; 2254Variable_name Value 2255drop table t1; 2256create table t1 (x int); 2257execute stmt; 2258Variable_name Value 2259drop table t1; 2260deallocate prepare stmt; 2261# 2262# SQLCOM_SHOW_STATUS 2263# 2264drop table if exists t1; 2265create table t1 (a int); 2266prepare stmt from "show status where (1) in (select * from t1)"; 2267execute stmt; 2268Variable_name Value 2269drop table t1; 2270create table t1 (x int); 2271execute stmt; 2272Variable_name Value 2273drop table t1; 2274deallocate prepare stmt; 2275# 2276# SQLCOM_SHOW_ENGINE_STATUS, SQLCOM_SHOW_ENGINE_LOGS, 2277# SQLCOM_SHOW_ENGINE_MUTEX, SQLCOM_SHOW_PROCESSLIST 2278# 2279# Currently can not have a where clause, need to be covered 2280# with tests 2281drop table if exists t1; 2282create table t1 (a int); 2283prepare stmt from "show engine all status where (1) in (select * from t1)"; 2284ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where (1) in (select * from t1)' at line 1 2285prepare stmt from "show engine all logs where (1) in (select * from t1)"; 2286ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where (1) in (select * from t1)' at line 1 2287prepare stmt from "show engine all mutex where (1) in (select * from t1)"; 2288ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where (1) in (select * from t1)' at line 1 2289prepare stmt from "show processlist where (1) in (select * from t1)"; 2290ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where (1) in (select * from t1)' at line 1 2291drop table t1; 2292# 2293# SQLCOM_SHOW_CHARSETS 2294# 2295drop table if exists t1; 2296create table t1 (a int); 2297prepare stmt from "show charset where (1) in (select * from t1)"; 2298execute stmt; 2299Charset Description Default collation Maxlen 2300drop table t1; 2301create table t1 (x int); 2302execute stmt; 2303Charset Description Default collation Maxlen 2304drop table t1; 2305deallocate prepare stmt; 2306# 2307# SQLCOM_SHOW_COLLATIONS 2308# 2309drop table if exists t1; 2310create table t1 (a int); 2311prepare stmt from "show collation where (1) in (select * from t1)"; 2312execute stmt; 2313Collation Charset Id Default Compiled Sortlen 2314drop table t1; 2315create table t1 (x int); 2316execute stmt; 2317Collation Charset Id Default Compiled Sortlen 2318drop table t1; 2319deallocate prepare stmt; 2320# 2321# SQLCOM_SHOW_TABLE_STATUS 2322# 2323drop table if exists t1; 2324create table t1 (a int); 2325prepare stmt from "show table status where (1) in (select * from t1)"; 2326execute stmt; 2327Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment 2328drop table t1; 2329create table t1 (x int); 2330execute stmt; 2331Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment 2332drop table t1; 2333deallocate prepare stmt; 2334# 2335# SQLCOM_SHOW_TRIGGERS 2336# 2337drop table if exists t1; 2338create table t1 (a int); 2339prepare stmt from "show triggers where (1) in (select * from t1)"; 2340execute stmt; 2341Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation 2342drop table t1; 2343create table t1 (x int); 2344execute stmt; 2345Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation 2346drop table t1; 2347deallocate prepare stmt; 2348# 2349# SQLCOM_SHOW_OPEN_TABLES 2350# 2351drop table if exists t1; 2352create table t1 (a int); 2353prepare stmt from "show open tables where (1) in (select * from t1)"; 2354execute stmt; 2355Database Table In_use Name_locked 2356drop table t1; 2357create table t1 (x int); 2358execute stmt; 2359Database Table In_use Name_locked 2360drop table t1; 2361deallocate prepare stmt; 2362# 2363# SQLCOM_SHOW_STATUS_PROC 2364# 2365drop table if exists t1; 2366create table t1 (a int); 2367prepare stmt from "show procedure status where (1) in (select * from t1)"; 2368execute stmt; 2369Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 2370drop table t1; 2371create table t1 (x int); 2372execute stmt; 2373Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 2374drop table t1; 2375deallocate prepare stmt; 2376# 2377# SQLCOM_SHOW_STATUS_FUNC 2378# 2379drop table if exists t1; 2380create table t1 (a int); 2381prepare stmt from "show function status where (1) in (select * from t1)"; 2382execute stmt; 2383Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 2384drop table t1; 2385create table t1 (x int); 2386execute stmt; 2387Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 2388drop table t1; 2389deallocate prepare stmt; 2390# 2391# SQLCOM_SHOW_EVENTS 2392# 2393# 2394# Please see this test in ps.test, it requires not_embedded.inc 2395# 2396# 2397# SQLCOM_SET_OPTION 2398# 2399drop table if exists t1; 2400create table t1 (a int); 2401prepare stmt from "set @a=((1) in (select * from t1))"; 2402execute stmt; 2403drop table t1; 2404create table t1 (x int); 2405execute stmt; 2406drop table t1; 2407deallocate prepare stmt; 2408# 2409# SQLCOM_DO 2410# 2411drop table if exists t1; 2412create table t1 (a int); 2413prepare stmt from "do ((1) in (select * from t1))"; 2414execute stmt; 2415drop table t1; 2416create table t1 (x int); 2417execute stmt; 2418drop table t1; 2419deallocate prepare stmt; 2420# 2421# SQLCOM_CALL 2422# 2423drop table if exists t1; 2424drop procedure if exists p1; 2425create procedure p1(a int) begin end; 2426create table t1 (a int); 2427prepare stmt from "call p1((1) in (select * from t1))"; 2428execute stmt; 2429drop table t1; 2430create table t1 (x int); 2431execute stmt; 2432drop table t1; 2433drop procedure p1; 2434deallocate prepare stmt; 2435# 2436# SQLCOM_CREATE_VIEW 2437# 2438drop table if exists t1; 2439drop view if exists v1; 2440create table t1 (a int); 2441prepare stmt from "create view v1 as select * from t1"; 2442execute stmt; 2443drop view v1; 2444drop table t1; 2445create table t1 (x int); 2446execute stmt; 2447drop view v1; 2448drop table t1; 2449deallocate prepare stmt; 2450# Intermediate result: number of reprepares matches the number 2451# of tests 2452call p_verify_reprepare_count(17); 2453SUCCESS 2454 2455# 2456# SQLCOM_ALTER_VIEW 2457# 2458drop view if exists v1; 2459create view v1 as select 1; 2460prepare stmt from "alter view v1 as select 2"; 2461ERROR HY000: This command is not supported in the prepared statement protocol yet 2462drop view v1; 2463# Cleanup 2464# 2465drop temporary table if exists t1, t2, t3; 2466drop table if exists t1, t2, t3, v1, v2; 2467drop procedure if exists p_verify_reprepare_count; 2468drop procedure if exists p1; 2469drop function if exists f1; 2470drop view if exists v1, v2; 2471# 2472# Additional coverage for refactoring which was made as part of work 2473# on bug '27480: Extend CREATE TEMPORARY TABLES privilege to allow 2474# temp table operations'. 2475# 2476# Check that we don't try to pre-open temporary tables for the elements 2477# from prelocking list, as this can lead to unwarranted ER_CANT_REOPEN 2478# errors. 2479DROP TABLE IF EXISTS t1, tm; 2480CREATE TABLE t1 (a INT); 2481CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW 2482SET @a:= (SELECT COUNT(*) FROM t1); 2483# Prelocking list for the below statement should 2484# contain t1 twice - once for the INSERT and once 2485# SELECT from the trigger. 2486PREPARE stmt1 FROM 'INSERT INTO t1 VALUES (1)'; 2487EXECUTE stmt1; 2488# Create temporary table which will shadow t1. 2489CREATE TEMPORARY TABLE t1 (b int); 2490# The below execution of statement should not fail with ER_CANT_REOPEN 2491# error. Instead stmt1 should be auto-matically reprepared and succeed. 2492EXECUTE stmt1; 2493DEALLOCATE PREPARE stmt1; 2494DROP TEMPORARY TABLE t1; 2495DROP TABLE t1; 2496# 2497# Also check that we properly reset table list elements from UNION 2498# clause of CREATE TABLE and ALTER TABLE statements. 2499# 2500CREATE TEMPORARY TABLE t1 (i INT); 2501PREPARE stmt2 FROM 'CREATE TEMPORARY TABLE tm (i INT) ENGINE=MERGE UNION=(t1)'; 2502EXECUTE stmt2; 2503DROP TEMPORARY TABLE tm; 2504EXECUTE stmt2; 2505DEALLOCATE PREPARE stmt2; 2506PREPARE stmt3 FROM 'ALTER TABLE tm UNION=(t1)'; 2507EXECUTE stmt3; 2508EXECUTE stmt3; 2509DEALLOCATE PREPARE stmt3; 2510DROP TEMPORARY TABLES tm, t1; 2511