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; 7TRUNCATE TABLE mysql.general_log; 8create procedure p_verify_reprepare_count(expected int) 9begin 10declare old_reprepare_count int default @reprepare_count; 11select variable_value from 12information_schema.session_status where 13variable_name='com_stmt_reprepare' 14 into @reprepare_count; 15if old_reprepare_count + expected <> @reprepare_count then 16select concat("Expected: ", expected, 17", actual: ", @reprepare_count - old_reprepare_count) 18as "ERROR"; 19else 20select '' as "SUCCESS"; 21end if; 22end| 23Warnings: 24Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 25set @reprepare_count= 0; 26flush status; 27===================================================================== 28Part 1: NOTHING -> TABLE transitions 29===================================================================== 30prepare stmt from "select * from t1"; 31ERROR 42S02: Table 'test.t1' doesn't exist 32===================================================================== 33Part 2: NOTHING -> TEMPORARY TABLE transitions 34===================================================================== 35===================================================================== 36Part 3: NOTHING -> VIEW transitions 37===================================================================== 38===================================================================== 39Part 4: TABLE -> NOTHING transitions 40===================================================================== 41# Test 4-a: select ... from <table> 42create table t1 (a int); 43prepare stmt from "select * from t1"; 44execute stmt; 45a 46call p_verify_reprepare_count(0); 47SUCCESS 48 49execute stmt; 50a 51call p_verify_reprepare_count(0); 52SUCCESS 53 54drop table t1; 55execute stmt; 56ERROR 42S02: Table 'test.t1' doesn't exist 57call p_verify_reprepare_count(0); 58SUCCESS 59 60execute stmt; 61ERROR 42S02: Table 'test.t1' doesn't exist 62call p_verify_reprepare_count(0); 63SUCCESS 64 65deallocate prepare stmt; 66# Test 4-b: TABLE -> NOTHING by renaming the table 67create table t1 (a int); 68prepare stmt from "select * from t1"; 69execute stmt; 70a 71call p_verify_reprepare_count(0); 72SUCCESS 73 74execute stmt; 75a 76call p_verify_reprepare_count(0); 77SUCCESS 78 79rename table t1 to t2; 80execute stmt; 81ERROR 42S02: Table 'test.t1' doesn't exist 82call p_verify_reprepare_count(0); 83SUCCESS 84 85execute stmt; 86ERROR 42S02: Table 'test.t1' doesn't exist 87call p_verify_reprepare_count(0); 88SUCCESS 89 90deallocate prepare stmt; 91drop table t2; 92===================================================================== 93Part 5: TABLE -> TABLE (DDL) transitions 94===================================================================== 95create table t1 (a int); 96prepare stmt from "select a from t1"; 97execute stmt; 98a 99call p_verify_reprepare_count(0); 100SUCCESS 101 102execute stmt; 103a 104call p_verify_reprepare_count(0); 105SUCCESS 106 107alter table t1 add column (b int); 108execute stmt; 109a 110call p_verify_reprepare_count(1); 111SUCCESS 112 113execute stmt; 114a 115call p_verify_reprepare_count(0); 116SUCCESS 117 118drop table t1; 119deallocate prepare stmt; 120===================================================================== 121Part 6: TABLE -> TABLE (TRIGGER) transitions 122===================================================================== 123# Test 6-a: adding a relevant trigger 124create table t1 (a int); 125prepare stmt from "insert into t1 (a) value (?)"; 126set @val=1; 127execute stmt using @val; 128call p_verify_reprepare_count(0); 129SUCCESS 130 131create trigger t1_bi before insert on t1 for each row 132set @message= new.a; 133set @val=2; 134execute stmt using @val; 135call p_verify_reprepare_count(1); 136SUCCESS 137 138select @message; 139@message 1402 141set @val=3; 142execute stmt using @val; 143call p_verify_reprepare_count(0); 144SUCCESS 145 146select @message; 147@message 1483 149prepare stmt from "insert into t1 (a) value (?)"; 150set @val=4; 151execute stmt using @val; 152call p_verify_reprepare_count(0); 153SUCCESS 154 155select @message; 156@message 1574 158# Test 6-b: adding an irrelevant trigger 159create trigger t1_bd before delete on t1 for each row 160set @message= old.a; 161set @val=5; 162execute stmt using @val; 163call p_verify_reprepare_count(1); 164SUCCESS 165 166select @message; 167@message 1685 169set @val=6; 170execute stmt using @val; 171call p_verify_reprepare_count(0); 172SUCCESS 173 174select @message; 175@message 1766 177prepare stmt from "insert into t1 (a) value (?)"; 178set @val=7; 179execute stmt using @val; 180call p_verify_reprepare_count(0); 181SUCCESS 182 183select @message; 184@message 1857 186# Test 6-c: changing a relevant trigger 187drop trigger t1_bi; 188create trigger t1_bi before insert on t1 for each row 189set @message= concat("new trigger: ", new.a); 190set @val=8; 191execute stmt using @val; 192call p_verify_reprepare_count(1); 193SUCCESS 194 195select @message; 196@message 197new trigger: 8 198set @val=9; 199execute stmt using @val; 200call p_verify_reprepare_count(0); 201SUCCESS 202 203select @message; 204@message 205new trigger: 9 206prepare stmt from "insert into t1 (a) value (?)"; 207set @val=10; 208execute stmt using @val; 209call p_verify_reprepare_count(0); 210SUCCESS 211 212select @message; 213@message 214new trigger: 10 215# Test 6-d: changing an irrelevant trigger 216drop trigger t1_bd; 217set @val=11; 218execute stmt using @val; 219call p_verify_reprepare_count(1); 220SUCCESS 221 222select @message; 223@message 224new trigger: 11 225Test 6-e: removing a relevant trigger 226drop trigger t1_bi; 227set @val=12; 228execute stmt using @val; 229call p_verify_reprepare_count(1); 230SUCCESS 231 232select @message; 233@message 234new trigger: 11 235set @val=13; 236execute stmt using @val; 237call p_verify_reprepare_count(0); 238SUCCESS 239 240select @message; 241@message 242new trigger: 11 243prepare stmt from "insert into t1 (a) value (?)"; 244set @val=14; 245execute stmt using @val; 246call p_verify_reprepare_count(0); 247SUCCESS 248 249select @message; 250@message 251new trigger: 11 252select * from t1 order by a; 253a 2541 2552 2563 2574 2585 2596 2607 2618 2629 26310 26411 26512 26613 26714 268drop table t1; 269deallocate prepare stmt; 270===================================================================== 271Part 7: TABLE -> TABLE (TRIGGER dependencies) transitions 272===================================================================== 273# Test 7-a: dependent PROCEDURE has changed 274# 275create table t1 (a int); 276create trigger t1_ai after insert on t1 for each row 277call p1(new.a); 278create procedure p1(a int) begin end; 279prepare stmt from "insert into t1 (a) values (?)"; 280set @var= 1; 281execute stmt using @var; 282drop procedure p1; 283create procedure p1 (a int) begin end; 284set @var= 2; 285execute stmt using @var; 286# Cleanup 287drop procedure p1; 288call p_verify_reprepare_count(1); 289SUCCESS 290 291# Test 7-b: dependent FUNCTION has changed 292# 293# Note, this scenario is supported, subject of Bug#12093 294# 295drop trigger t1_ai; 296create trigger t1_ai after insert on t1 for each row 297select f1(new.a+1) into @var; 298create function f1 (a int) returns int return a; 299prepare stmt from "insert into t1(a) values (?)"; 300set @var=3; 301execute stmt using @var; 302select @var; 303@var 3044 305drop function f1; 306create function f1 (a int) returns int return 0; 307execute stmt using @var; 308call p_verify_reprepare_count(1); 309SUCCESS 310 311drop function f1; 312deallocate prepare stmt; 313# Test 7-c: dependent VIEW has changed 314# 315# Note, this scenario is not functioning correctly, see 316# Bug#33255 Trigger using views and view ddl : corrupted triggers 317# and Bug #33000 Triggers do not detect changes in meta-data. 318# 319drop trigger t1_ai; 320create table t2 (a int unique); 321create table t3 (a int unique); 322create view v1 as select a from t2; 323create trigger t1_ai after insert on t1 for each row 324insert into v1 (a) values (new.a); 325# Demonstrate that the same bug is present 326# without prepared statements 327insert into t1 (a) values (5); 328select * from t2; 329a 3305 331select * from t3; 332a 333drop view v1; 334create view v1 as select a from t3; 335insert into t1 (a) values (6); 336ERROR 42S02: Table 'test.t2' doesn't exist 337flush table t1; 338insert into t1 (a) values (6); 339select * from t2; 340a 3415 342select * from t3; 343a 3446 345prepare stmt from "insert into t1 (a) values (?)"; 346set @var=7; 347execute stmt using @var; 348call p_verify_reprepare_count(0); 349SUCCESS 350 351select * from t3; 352a 3536 3547 355select * from t2; 356a 3575 358drop view v1; 359create view v1 as select a from t2; 360set @var=8; 361# View in the INSERT-statement in the trigger is still pointing to 362# table 't3', because the trigger hasn't noticed the change 363# in view definition. This will be fixed by WL#4179. 364# 365# The prepared INSERT-statement however does notice the change, 366# but repreparation of the main statement doesn't cause repreparation 367# of trigger statements. 368# 369# The following EXECUTE results in ER_NO_SUCH_TABLE (t3) error, because 370# pre-locking list of the prepared statement has been changed 371# (the prepared statement has noticed the meta-data change), 372# but the trigger still tries to deal with 't3', which is not opened. 373# That's why '8' is not inserted neither into 't2', nor into 't3'. 374execute stmt using @var; 375ERROR 42S02: Table 'test.t3' doesn't exist 376call p_verify_reprepare_count(1); 377SUCCESS 378 379select * from t2; 380a 3815 382select * from t3; 383a 3846 3857 386flush table t1; 387set @var=9; 388execute stmt using @var; 389call p_verify_reprepare_count(1); 390SUCCESS 391 392select * from t2; 393a 3945 3959 396select * from t3; 397a 3986 3997 400drop view v1; 401drop table t1,t2,t3; 402# Test 7-d: dependent TABLE has changed 403create table t1 (a int); 404create trigger t1_ai after insert on t1 for each row 405insert into t2 (a) values (new.a); 406create table t2 (a int); 407prepare stmt from "insert into t1 (a) values (?)"; 408set @var=1; 409execute stmt using @var; 410alter table t2 add column comment varchar(255); 411set @var=2; 412# Since the dependent table is tracked in the prelocked 413# list of the prepared statement, invalidation happens 414# and the statement is re-prepared. This is an unnecessary 415# side effect, since the statement that *is* dependent 416# on t2 definition is inside the trigger, and it is currently 417# not reprepared (see the previous test case). 418execute stmt using @var; 419call p_verify_reprepare_count(1); 420SUCCESS 421 422select * from t1; 423a 4241 4252 426select * from t2; 427a comment 4281 NULL 4292 NULL 430drop table t1,t2; 431# Test 7-e: dependent TABLE TRIGGER has changed 432create table t1 (a int); 433create trigger t1_ai after insert on t1 for each row 434insert into t2 (a) values (new.a); 435create table t2 (a int unique); 436create trigger t2_ai after insert on t2 for each row 437insert into t3 (a) values (new.a); 438create table t3 (a int unique); 439create table t4 (a int unique); 440insert into t1 (a) values (1); 441select * from t1 join t2 on (t1.a=t2.a) join t3 on (t2.a=t3.a); 442a a a 4431 1 1 444drop trigger t2_ai; 445create trigger t2_ai after insert on t2 for each row 446insert into t4 (a) values (new.a); 447insert into t1 (a) values (2); 448select * from t1 join t2 on (t1.a=t2.a) join t4 on (t2.a=t4.a); 449a a a 4502 2 2 451prepare stmt from "insert into t1 (a) values (?)"; 452set @var=3; 453execute stmt using @var; 454select * from t1 join t2 on (t1.a=t2.a) join t4 on (t2.a=t4.a); 455a a a 4562 2 2 4573 3 3 458drop trigger t2_ai; 459create trigger t2_ai after insert on t2 for each row 460insert into t3 (a) values (new.a); 461set @var=4; 462execute stmt using @var; 463call p_verify_reprepare_count(1); 464SUCCESS 465 466select * from t1 join t2 on (t1.a=t2.a) join t3 on (t2.a=t3.a); 467a a a 4681 1 1 4694 4 4 470select * from t1 join t2 on (t1.a=t2.a) join t4 on (t2.a=t4.a); 471a a a 4722 2 2 4733 3 3 474drop table t1, t2, t3, t4; 475deallocate prepare stmt; 476===================================================================== 477Part 8: TABLE -> TEMPORARY TABLE transitions 478===================================================================== 479# Test 8-a: base table used recreated as temporary table 480create table t1 (a int); 481prepare stmt from "select * from t1"; 482execute stmt; 483a 484drop table t1; 485create temporary table t1 (a int); 486execute stmt; 487a 488call p_verify_reprepare_count(1); 489SUCCESS 490 491execute stmt; 492a 493call p_verify_reprepare_count(0); 494SUCCESS 495 496drop table t1; 497deallocate prepare stmt; 498# Test 8-b: temporary table has precedence over base table with same name 499create table t1 (a int); 500prepare stmt from 'select count(*) from t1'; 501execute stmt; 502count(*) 5030 504call p_verify_reprepare_count(0); 505SUCCESS 506 507execute stmt; 508count(*) 5090 510call p_verify_reprepare_count(0); 511SUCCESS 512 513create temporary table t1 AS SELECT 1; 514execute stmt; 515count(*) 5161 517call p_verify_reprepare_count(1); 518SUCCESS 519 520execute stmt; 521count(*) 5221 523call p_verify_reprepare_count(0); 524SUCCESS 525 526deallocate prepare stmt; 527drop temporary table t1; 528drop table t1; 529===================================================================== 530Part 9: TABLE -> VIEW transitions 531===================================================================== 532create table t1 (a int); 533prepare stmt from "select * from t1"; 534execute stmt; 535a 536call p_verify_reprepare_count(0); 537SUCCESS 538 539drop table t1; 540create table t2 (a int); 541create view t1 as select * from t2; 542execute stmt; 543a 544call p_verify_reprepare_count(1); 545SUCCESS 546 547drop view t1; 548drop table t2; 549deallocate prepare stmt; 550===================================================================== 551Part 10: TEMPORARY TABLE -> NOTHING transitions 552===================================================================== 553create temporary table t1 (a int); 554prepare stmt from "select * from t1"; 555execute stmt; 556a 557call p_verify_reprepare_count(0); 558SUCCESS 559 560drop temporary table t1; 561execute stmt; 562ERROR 42S02: Table 'test.t1' doesn't exist 563call p_verify_reprepare_count(0); 564SUCCESS 565 566deallocate prepare stmt; 567===================================================================== 568Part 11: TEMPORARY TABLE -> TABLE transitions 569===================================================================== 570# Test 11-a: temporary table replaced by base table 571create table t1 (a int); 572insert into t1 (a) value (1); 573create temporary table t1 (a int); 574prepare stmt from "select * from t1"; 575execute stmt; 576a 577call p_verify_reprepare_count(0); 578SUCCESS 579 580drop temporary table t1; 581execute stmt; 582a 5831 584call p_verify_reprepare_count(1); 585SUCCESS 586 587select * from t1; 588a 5891 590drop table t1; 591deallocate prepare stmt; 592# Test 11-b: temporary table has precedence over base table with same name 593# temporary table disappears 594create table t1 (a int); 595create temporary table t1 as select 1 as a; 596prepare stmt from "select count(*) from t1"; 597execute stmt; 598count(*) 5991 600call p_verify_reprepare_count(0); 601SUCCESS 602 603execute stmt; 604count(*) 6051 606call p_verify_reprepare_count(0); 607SUCCESS 608 609drop temporary table t1; 610execute stmt; 611count(*) 6120 613call p_verify_reprepare_count(1); 614SUCCESS 615 616execute stmt; 617count(*) 6180 619call p_verify_reprepare_count(0); 620SUCCESS 621 622deallocate prepare stmt; 623drop table t1; 624===================================================================== 625Part 12: TEMPORARY TABLE -> TEMPORARY TABLE (DDL) transitions 626===================================================================== 627create temporary table t1 (a int); 628prepare stmt from "select a from t1"; 629execute stmt; 630a 631call p_verify_reprepare_count(0); 632SUCCESS 633 634drop temporary table t1; 635create temporary table t1 (a int, b int); 636execute stmt; 637a 638call p_verify_reprepare_count(1); 639SUCCESS 640 641select * from t1; 642a b 643drop temporary table t1; 644deallocate prepare stmt; 645===================================================================== 646Part 13: TEMPORARY TABLE -> VIEW transitions 647===================================================================== 648create temporary table t1 (a int); 649create table t2 (a int); 650prepare stmt from "select * from t1"; 651execute stmt; 652a 653call p_verify_reprepare_count(0); 654SUCCESS 655 656drop temporary table t1; 657create view t1 as select * from t2; 658execute stmt; 659a 660call p_verify_reprepare_count(1); 661SUCCESS 662 663drop view t1; 664drop table t2; 665deallocate prepare stmt; 666===================================================================== 667Part 14: VIEW -> NOTHING transitions 668===================================================================== 669create table t2 (a int); 670create view t1 as select * from t2; 671prepare stmt from "select * from t1"; 672execute stmt; 673a 674drop view t1; 675execute stmt; 676ERROR 42S02: Table 'test.t1' doesn't exist 677call p_verify_reprepare_count(0); 678SUCCESS 679 680execute stmt; 681ERROR 42S02: Table 'test.t1' doesn't exist 682call p_verify_reprepare_count(0); 683SUCCESS 684 685drop table t2; 686deallocate prepare stmt; 687===================================================================== 688Part 15: VIEW -> TABLE transitions 689===================================================================== 690create table t2 (a int); 691create view t1 as select * from t2; 692prepare stmt from "select * from t1"; 693execute stmt; 694a 695call p_verify_reprepare_count(0); 696SUCCESS 697 698drop view t1; 699create table t1 (a int); 700execute stmt; 701a 702call p_verify_reprepare_count(1); 703SUCCESS 704 705drop table t2; 706drop table t1; 707deallocate prepare stmt; 708===================================================================== 709Part 16: VIEW -> TEMPORARY TABLE transitions 710===================================================================== 711# 712# Test 1: Merged view 713# 714create table t2 (a int); 715insert into t2 (a) values (1); 716create view t1 as select * from t2; 717prepare stmt from "select * from t1"; 718execute stmt; 719a 7201 721call p_verify_reprepare_count(0); 722SUCCESS 723 724create temporary table t1 (a int); 725execute stmt; 726a 7271 728call p_verify_reprepare_count(0); 729SUCCESS 730 731drop view t1; 732execute stmt; 733ERROR 42S02: Table 'test.t1' doesn't exist 734call p_verify_reprepare_count(0); 735SUCCESS 736 737drop table t2; 738drop temporary table t1; 739deallocate prepare stmt; 740# 741# Test 2: Materialized view 742# 743create table t2 (a int); 744insert into t2 (a) values (1); 745create algorithm = temptable view t1 as select * from t2; 746prepare stmt from "select * from t1"; 747execute stmt; 748a 7491 750call p_verify_reprepare_count(0); 751SUCCESS 752 753create temporary table t1 (a int); 754execute stmt; 755a 7561 757call p_verify_reprepare_count(0); 758SUCCESS 759 760drop view t1; 761execute stmt; 762ERROR 42S02: Table 'test.t1' doesn't exist 763call p_verify_reprepare_count(0); 764SUCCESS 765 766drop table t2; 767drop temporary table t1; 768deallocate prepare stmt; 769# 770# Test 3: View referencing an Information schema table 771# 772create view t1 as select table_name from information_schema.views order by table_name; 773prepare stmt from "select * from t1"; 774execute stmt; 775table_name 776t1 777user 778call p_verify_reprepare_count(0); 779SUCCESS 780 781create temporary table t1 (a int); 782execute stmt; 783table_name 784t1 785user 786call p_verify_reprepare_count(0); 787SUCCESS 788 789drop view t1; 790execute stmt; 791ERROR 42S02: Table 'test.t1' doesn't exist 792call p_verify_reprepare_count(0); 793SUCCESS 794 795drop temporary table t1; 796deallocate prepare stmt; 797===================================================================== 798Part 17: VIEW -> VIEW (DDL) transitions 799===================================================================== 800create table t2 (a int); 801insert into t2 values (10), (20), (30); 802create view t1 as select a, 2*a as b, 3*a as c from t2; 803select * from t1; 804a b c 80510 20 30 80620 40 60 80730 60 90 808prepare stmt from "select * from t1"; 809execute stmt; 810a b c 81110 20 30 81220 40 60 81330 60 90 814drop view t1; 815create view t1 as select a, 2*a as b, 5*a as c from t2; 816select * from t1; 817a b c 81810 20 50 81920 40 100 82030 60 150 821# This is actually a test case for Bug#11748352 (36002 Prepared 822# statements: if a view used in a statement is replaced, bad data). 823execute stmt; 824a b c 82510 20 50 82620 40 100 82730 60 150 828call p_verify_reprepare_count(1); 829SUCCESS 830 831flush table t2; 832execute stmt; 833a b c 83410 20 50 83520 40 100 83630 60 150 837call p_verify_reprepare_count(1); 838SUCCESS 839 840# Check that we properly handle ALTER VIEW statements. 841execute stmt; 842a b c 84310 20 50 84420 40 100 84530 60 150 846call p_verify_reprepare_count(0); 847SUCCESS 848 849alter view t1 as select a, 3*a as b, 4*a as c from t2; 850execute stmt; 851a b c 85210 30 40 85320 60 80 85430 90 120 855call p_verify_reprepare_count(1); 856SUCCESS 857 858execute stmt; 859a b c 86010 30 40 86120 60 80 86230 90 120 863call p_verify_reprepare_count(0); 864SUCCESS 865 866execute stmt; 867a b c 86810 30 40 86920 60 80 87030 90 120 871call p_verify_reprepare_count(0); 872SUCCESS 873 874select * from t1; 875a b c 87610 30 40 87720 60 80 87830 90 120 879# Check that DROP & CREATE is properly handled under LOCK TABLES. 880drop view t1; 881flush tables; 882create view t1 as select a, 5*a as b, 6*a as c from t2; 883lock tables t1 read, t2 read; 884execute stmt; 885a b c 88610 50 60 88720 100 120 88830 150 180 889call p_verify_reprepare_count(1); 890SUCCESS 891 892execute stmt; 893a b c 89410 50 60 89520 100 120 89630 150 180 897call p_verify_reprepare_count(0); 898SUCCESS 899 900execute stmt; 901a b c 90210 50 60 90320 100 120 90430 150 180 905call p_verify_reprepare_count(0); 906SUCCESS 907 908unlock tables; 909# ... and once again... 910drop view t1; 911create view t1 as select a, 6*a as b, 7*a as c from t2; 912lock tables t1 read, t2 read; 913execute stmt; 914a b c 91510 60 70 91620 120 140 91730 180 210 918call p_verify_reprepare_count(1); 919SUCCESS 920 921execute stmt; 922a b c 92310 60 70 92420 120 140 92530 180 210 926call p_verify_reprepare_count(0); 927SUCCESS 928 929execute stmt; 930a b c 93110 60 70 93220 120 140 93330 180 210 934call p_verify_reprepare_count(0); 935SUCCESS 936 937unlock tables; 938# Check that ALTER VIEW is properly handled under LOCK TABLES. 939alter view t1 as select a, 7*a as b, 8*a as c from t2; 940lock tables t1 read, t2 read; 941execute stmt; 942a b c 94310 70 80 94420 140 160 94530 210 240 946call p_verify_reprepare_count(1); 947SUCCESS 948 949execute stmt; 950a b c 95110 70 80 95220 140 160 95330 210 240 954call p_verify_reprepare_count(0); 955SUCCESS 956 957execute stmt; 958a b c 95910 70 80 96020 140 160 96130 210 240 962call p_verify_reprepare_count(0); 963SUCCESS 964 965unlock tables; 966drop table t2; 967drop view t1; 968deallocate prepare stmt; 969# Check that DROP & CREATE is properly handled under LOCK TABLES when 970# LOCK TABLES does not contain the complete set of views. 971create table t1(a int); 972insert into t1 values (1), (2), (3); 973create view v1 as select a from t1; 974lock tables t1 read, v1 read; 975prepare stmt from 'select * from v1'; 976execute stmt; 977a 9781 9792 9803 981call p_verify_reprepare_count(0); 982SUCCESS 983 984execute stmt; 985a 9861 9872 9883 989call p_verify_reprepare_count(0); 990SUCCESS 991 992unlock tables; 993drop view v1; 994create view v1 as select 2*a from t1; 995lock tables t1 read; 996execute stmt; 997ERROR HY000: Table 'v1' was not locked with LOCK TABLES 998unlock tables; 999drop table t1; 1000drop view v1; 1001deallocate prepare stmt; 1002# Check that ALTER VIEW is properly handled under LOCK TABLES when 1003# LOCK TABLES does not contain the complete set of views. 1004create table t1(a int); 1005insert into t1 values (1), (2), (3); 1006create view v1 as select a from t1; 1007lock tables t1 read, v1 read; 1008prepare stmt from 'select * from v1'; 1009execute stmt; 1010a 10111 10122 10133 1014call p_verify_reprepare_count(0); 1015SUCCESS 1016 1017execute stmt; 1018a 10191 10202 10213 1022call p_verify_reprepare_count(0); 1023SUCCESS 1024 1025unlock tables; 1026alter view v1 as select 2*a from t1; 1027lock tables t1 read; 1028execute stmt; 1029ERROR HY000: Table 'v1' was not locked with LOCK TABLES 1030unlock tables; 1031drop table t1; 1032drop view v1; 1033deallocate prepare stmt; 1034===================================================================== 1035Part 18: VIEW -> VIEW (VIEW dependencies) transitions 1036===================================================================== 1037# Part 18a: dependent function has changed 1038create table t1 (a int); 1039insert into t1 (a) values (1), (2), (3); 1040create function f1() returns int return (select max(a) from t1); 1041create view v1 as select f1(); 1042prepare stmt from "select * from v1"; 1043execute stmt; 1044f1() 10453 1046execute stmt; 1047f1() 10483 1049call p_verify_reprepare_count(0); 1050SUCCESS 1051 1052drop function f1; 1053create function f1() returns int return 2; 1054# XXX: Used to be another manifestation of Bug#12093. 1055# We only used to get a different error 1056# message because the non-existing procedure error is masked 1057# by the view. 1058execute stmt; 1059f1() 10602 1061execute stmt; 1062f1() 10632 1064call p_verify_reprepare_count(1); 1065SUCCESS 1066 1067# Part 18b: dependent procedure has changed (referred to via a function) 1068create table t2 (a int); 1069insert into t2 (a) values (4), (5), (6); 1070drop function f1; 1071create function f1() returns int 1072begin 1073declare x int; 1074call p1(x); 1075return x; 1076end| 1077create procedure p1(out x int) select max(a) from t1 into x; 1078Warnings: 1079Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 1080prepare stmt from "select * from v1"; 1081execute stmt; 1082f1() 10833 1084execute stmt; 1085f1() 10863 1087call p_verify_reprepare_count(0); 1088SUCCESS 1089 1090drop procedure p1; 1091create procedure p1(out x int) select max(a) from t2 into x; 1092Warnings: 1093Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 1094# XXX: used to be a bug. The prelocked list was not invalidated 1095# and we kept opening table t1, whereas the procedure 1096# is now referring to table t2 1097execute stmt; 1098f1() 10996 1100call p_verify_reprepare_count(1); 1101SUCCESS 1102 1103flush table t1; 1104execute stmt; 1105f1() 11066 1107call p_verify_reprepare_count(0); 1108SUCCESS 1109 1110execute stmt; 1111f1() 11126 1113# Test 18-c: dependent VIEW has changed 1114drop view v1; 1115create view v2 as select a from t1; 1116create view v1 as select * from v2; 1117prepare stmt from "select * from v1"; 1118execute stmt; 1119a 11201 11212 11223 1123execute stmt; 1124a 11251 11262 11273 1128call p_verify_reprepare_count(0); 1129SUCCESS 1130 1131drop view v2; 1132create view v2 as select a from t2; 1133execute stmt; 1134a 11354 11365 11376 1138execute stmt; 1139a 11404 11415 11426 1143call p_verify_reprepare_count(1); 1144SUCCESS 1145 1146flush table t1; 1147execute stmt; 1148a 11494 11505 11516 1152call p_verify_reprepare_count(0); 1153SUCCESS 1154 1155execute stmt; 1156a 11574 11585 11596 1160# Test 18-d: dependent TABLE has changed 1161drop view v2; 1162create table v2 as select * from t1; 1163execute stmt; 1164a 11651 11662 11673 1168call p_verify_reprepare_count(1); 1169SUCCESS 1170 1171execute stmt; 1172a 11731 11742 11753 1176call p_verify_reprepare_count(0); 1177SUCCESS 1178 1179drop table v2; 1180create table v2 (a int unique) as select * from t2; 1181execute stmt; 1182a 11834 11845 11856 1186call p_verify_reprepare_count(1); 1187SUCCESS 1188 1189execute stmt; 1190a 11914 11925 11936 1194call p_verify_reprepare_count(0); 1195SUCCESS 1196 1197# Test 18-e: dependent TABLE trigger has changed 1198prepare stmt from "insert into v1 (a) values (?)"; 1199set @var= 7; 1200execute stmt using @var; 1201call p_verify_reprepare_count(0); 1202SUCCESS 1203 1204create trigger v2_bi before insert on v2 for each row set @message="v2_bi"; 1205set @var=8; 1206execute stmt using @var; 1207call p_verify_reprepare_count(1); 1208SUCCESS 1209 1210select @message; 1211@message 1212v2_bi 1213drop trigger v2_bi; 1214set @message=null; 1215set @var=9; 1216execute stmt using @var; 1217call p_verify_reprepare_count(1); 1218SUCCESS 1219 1220select @message; 1221@message 1222NULL 1223create trigger v2_bi after insert on v2 for each row set @message="v2_ai"; 1224set @var= 10; 1225execute stmt using @var; 1226call p_verify_reprepare_count(1); 1227SUCCESS 1228 1229select @message; 1230@message 1231v2_ai 1232select * from v1; 1233a 12344 12355 12366 12377 12388 12399 124010 1241# Cleanup 1242drop table if exists t1, t2, v1, v2; 1243drop view if exists v1, v2; 1244drop function f1; 1245drop procedure p1; 1246deallocate prepare stmt; 1247===================================================================== 1248Part 19: Special tables (INFORMATION_SCHEMA) 1249===================================================================== 1250prepare stmt from 1251"select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE 1252 from INFORMATION_SCHEMA.ROUTINES where 1253 routine_name='p1'"; 1254create procedure p1() select "hi there"; 1255execute stmt; 1256ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE 1257test p1 PROCEDURE 1258execute stmt; 1259ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE 1260test p1 PROCEDURE 1261drop procedure p1; 1262create procedure p1() select "hi there, again"; 1263execute stmt; 1264ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE 1265test p1 PROCEDURE 1266execute stmt; 1267ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE 1268test p1 PROCEDURE 1269call p_verify_reprepare_count(0); 1270SUCCESS 1271 1272drop procedure p1; 1273deallocate prepare stmt; 1274===================================================================== 1275Part 20: Special tables (log tables) 1276===================================================================== 1277prepare stmt from 1278"select * from mysql.general_log where argument='IMPOSSIBLE QUERY STRING'"; 1279execute stmt; 1280execute stmt; 1281execute stmt; 1282execute stmt; 1283call p_verify_reprepare_count(0); 1284SUCCESS 1285 1286deallocate prepare stmt; 1287===================================================================== 1288Part 21: Special tables (system tables) 1289===================================================================== 1290prepare stmt from 1291"select type, db, name from mysql.proc where name='p1'"; 1292create procedure p1() select "hi there"; 1293execute stmt; 1294type db name 1295PROCEDURE test p1 1296execute stmt; 1297type db name 1298PROCEDURE test p1 1299drop procedure p1; 1300create procedure p1() select "hi there, again"; 1301execute stmt; 1302type db name 1303PROCEDURE test p1 1304execute stmt; 1305type db name 1306PROCEDURE test p1 1307call p_verify_reprepare_count(0); 1308SUCCESS 1309 1310drop procedure p1; 1311deallocate prepare stmt; 1312===================================================================== 1313Part 22: Special tables (views temp tables) 1314===================================================================== 1315create table t1 (a int); 1316create algorithm=temptable view v1 as select a*a as a2 from t1; 1317# Using a temporary table internally should not confuse the prepared 1318# statement code, and should not raise ER_PS_INVALIDATED errors 1319show create view v1; 1320View Create View character_set_client collation_connection 1321v1 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 1322prepare stmt from "select * from v1"; 1323insert into t1 values (1), (2), (3); 1324execute stmt; 1325a2 13261 13274 13289 1329execute stmt; 1330a2 13311 13324 13339 1334insert into t1 values (4), (5), (6); 1335execute stmt; 1336a2 13371 13384 13399 134016 134125 134236 1343execute stmt; 1344a2 13451 13464 13479 134816 134925 135036 1351call p_verify_reprepare_count(0); 1352SUCCESS 1353 1354drop table t1; 1355drop view v1; 1356===================================================================== 1357Part 23: Special statements 1358===================================================================== 1359# SQLCOM_ALTER_TABLE: 1360create table t1 (a int); 1361prepare stmt from "alter table t1 add column b int"; 1362execute stmt; 1363drop table t1; 1364create table t1 (a1 int, a2 int); 1365# t1 has changed, and it's does not lead to reprepare 1366execute stmt; 1367alter table t1 drop column b; 1368execute stmt; 1369alter table t1 drop column b; 1370execute stmt; 1371call p_verify_reprepare_count(0); 1372SUCCESS 1373 1374drop table t1; 1375# SQLCOM_REPAIR: 1376create table t1 (a int); 1377insert into t1 values (1), (2), (3); 1378prepare stmt from "repair table t1"; 1379execute stmt; 1380Table Op Msg_type Msg_text 1381test.t1 repair status OK 1382execute stmt; 1383Table Op Msg_type Msg_text 1384test.t1 repair status OK 1385drop table t1; 1386create table t1 (a1 int, a2 int); 1387insert into t1 values (1, 10), (2, 20), (3, 30); 1388# t1 has changed, and it's does not lead to reprepare 1389execute stmt; 1390Table Op Msg_type Msg_text 1391test.t1 repair status OK 1392alter table t1 add column b varchar(50) default NULL; 1393execute stmt; 1394Table Op Msg_type Msg_text 1395test.t1 repair status OK 1396call p_verify_reprepare_count(0); 1397SUCCESS 1398 1399alter table t1 drop column b; 1400execute stmt; 1401Table Op Msg_type Msg_text 1402test.t1 repair status OK 1403call p_verify_reprepare_count(0); 1404SUCCESS 1405 1406# SQLCOM_ANALYZE: 1407prepare stmt from "analyze table t1"; 1408execute stmt; 1409Table Op Msg_type Msg_text 1410test.t1 analyze status Engine-independent statistics collected 1411test.t1 analyze status OK 1412drop table t1; 1413create table t1 (a1 int, a2 int); 1414insert into t1 values (1, 10), (2, 20), (3, 30); 1415# t1 has changed, and it's not a problem 1416execute stmt; 1417Table Op Msg_type Msg_text 1418test.t1 analyze status Engine-independent statistics collected 1419test.t1 analyze status OK 1420alter table t1 add column b varchar(50) default NULL; 1421execute stmt; 1422Table Op Msg_type Msg_text 1423test.t1 analyze status Engine-independent statistics collected 1424test.t1 analyze status OK 1425alter table t1 drop column b; 1426execute stmt; 1427Table Op Msg_type Msg_text 1428test.t1 analyze status Engine-independent statistics collected 1429test.t1 analyze status OK 1430call p_verify_reprepare_count(0); 1431SUCCESS 1432 1433# SQLCOM_OPTIMIZE: 1434prepare stmt from "optimize table t1"; 1435execute stmt; 1436Table Op Msg_type Msg_text 1437test.t1 optimize status Table is already up to date 1438drop table t1; 1439create table t1 (a1 int, a2 int); 1440insert into t1 values (1, 10), (2, 20), (3, 30); 1441# t1 has changed, and it's not a problem 1442execute stmt; 1443Table Op Msg_type Msg_text 1444test.t1 optimize status OK 1445alter table t1 add column b varchar(50) default NULL; 1446execute stmt; 1447Table Op Msg_type Msg_text 1448test.t1 optimize status OK 1449alter table t1 drop column b; 1450execute stmt; 1451Table Op Msg_type Msg_text 1452test.t1 optimize status OK 1453call p_verify_reprepare_count(0); 1454SUCCESS 1455 1456drop table t1; 1457# SQLCOM_SHOW_CREATE_PROC: 1458prepare stmt from "show create procedure p1"; 1459execute stmt; 1460ERROR 42000: PROCEDURE p1 does not exist 1461execute stmt; 1462ERROR 42000: PROCEDURE p1 does not exist 1463create procedure p1() begin end; 1464execute stmt; 1465execute stmt; 1466drop procedure p1; 1467create procedure p1(x int, y int) begin end; 1468execute stmt; 1469execute stmt; 1470drop procedure p1; 1471execute stmt; 1472ERROR 42000: PROCEDURE p1 does not exist 1473execute stmt; 1474ERROR 42000: PROCEDURE p1 does not exist 1475call p_verify_reprepare_count(0); 1476SUCCESS 1477 1478# SQLCOM_SHOW_CREATE_FUNC: 1479prepare stmt from "show create function f1"; 1480execute stmt; 1481ERROR 42000: FUNCTION f1 does not exist 1482execute stmt; 1483ERROR 42000: FUNCTION f1 does not exist 1484create function f1() returns int return 0; 1485execute stmt; 1486execute stmt; 1487drop function f1; 1488create function f1(x int, y int) returns int return x+y; 1489execute stmt; 1490execute stmt; 1491drop function f1; 1492execute stmt; 1493ERROR 42000: FUNCTION f1 does not exist 1494execute stmt; 1495ERROR 42000: FUNCTION f1 does not exist 1496call p_verify_reprepare_count(0); 1497SUCCESS 1498 1499# SQLCOM_SHOW_CREATE_TRIGGER: 1500create table t1 (a int); 1501prepare stmt from "show create trigger t1_bi"; 1502execute stmt; 1503ERROR HY000: Trigger does not exist 1504execute stmt; 1505ERROR HY000: Trigger does not exist 1506create trigger t1_bi before insert on t1 for each row set @message= "t1_bi"; 1507execute stmt; 1508execute stmt; 1509drop trigger t1_bi; 1510create trigger t1_bi before insert on t1 for each row set @message= "t1_bi (2)"; 1511execute stmt; 1512execute stmt; 1513drop trigger t1_bi; 1514execute stmt; 1515ERROR HY000: Trigger does not exist 1516execute stmt; 1517ERROR HY000: Trigger does not exist 1518call p_verify_reprepare_count(0); 1519SUCCESS 1520 1521drop table t1; 1522deallocate prepare stmt; 1523===================================================================== 1524Part 24: Testing the strength of TABLE_SHARE version 1525===================================================================== 1526# Test 24-a: number of columns 1527create table t1 (a int); 1528prepare stmt from "select a from t1"; 1529execute stmt; 1530a 1531call p_verify_reprepare_count(0); 1532SUCCESS 1533 1534alter table t1 add column b varchar(50) default NULL; 1535execute stmt; 1536a 1537call p_verify_reprepare_count(1); 1538SUCCESS 1539 1540execute stmt; 1541a 1542call p_verify_reprepare_count(0); 1543SUCCESS 1544 1545# Test 24-b: column name 1546alter table t1 change b c int; 1547execute stmt; 1548a 1549call p_verify_reprepare_count(1); 1550SUCCESS 1551 1552execute stmt; 1553a 1554call p_verify_reprepare_count(0); 1555SUCCESS 1556 1557# Test 24-c: column type 1558alter table t1 change a a varchar(10); 1559execute stmt; 1560a 1561call p_verify_reprepare_count(1); 1562SUCCESS 1563 1564execute stmt; 1565a 1566call p_verify_reprepare_count(0); 1567SUCCESS 1568 1569# Test 24-d: column type length 1570alter table t1 change a a varchar(20); 1571execute stmt; 1572a 1573call p_verify_reprepare_count(1); 1574SUCCESS 1575 1576execute stmt; 1577a 1578call p_verify_reprepare_count(0); 1579SUCCESS 1580 1581# Test 24-e: column NULL property 1582alter table t1 change a a varchar(20) NOT NULL; 1583execute stmt; 1584a 1585call p_verify_reprepare_count(1); 1586SUCCESS 1587 1588execute stmt; 1589a 1590call p_verify_reprepare_count(0); 1591SUCCESS 1592 1593# Test 24-f: column DEFAULT 1594alter table t1 change c c int DEFAULT 20; 1595execute stmt; 1596a 1597call p_verify_reprepare_count(1); 1598SUCCESS 1599 1600execute stmt; 1601a 1602call p_verify_reprepare_count(0); 1603SUCCESS 1604 1605# Test 24-g: number of keys 1606create unique index t1_a_idx on t1 (a); 1607execute stmt; 1608a 1609call p_verify_reprepare_count(1); 1610SUCCESS 1611 1612execute stmt; 1613a 1614call p_verify_reprepare_count(0); 1615SUCCESS 1616 1617# Test 24-h: changing index uniqueness 1618drop index t1_a_idx on t1; 1619create index t1_a_idx on t1 (a); 1620execute stmt; 1621a 1622call p_verify_reprepare_count(1); 1623SUCCESS 1624 1625execute stmt; 1626a 1627call p_verify_reprepare_count(0); 1628SUCCESS 1629 1630# Cleanup 1631drop table t1; 1632deallocate prepare stmt; 1633===================================================================== 1634Testing reported bugs 1635===================================================================== 1636# 1637# Bug#27420 A combination of PS and view operations cause 1638# error + assertion on shutdown 1639# 1640drop table if exists t_27420_100; 1641drop table if exists t_27420_101; 1642drop view if exists v_27420; 1643create table t_27420_100(a int); 1644insert into t_27420_100 values (1), (2); 1645create table t_27420_101(a int); 1646insert into t_27420_101 values (1), (2); 1647create view v_27420 as select t_27420_100.a X, t_27420_101.a Y 1648from t_27420_100, t_27420_101 1649where t_27420_100.a=t_27420_101.a; 1650prepare stmt from "select * from v_27420"; 1651execute stmt; 1652X Y 16531 1 16542 2 1655call p_verify_reprepare_count(0); 1656SUCCESS 1657 1658drop view v_27420; 1659create table v_27420(X int, Y int); 1660execute stmt; 1661X Y 1662call p_verify_reprepare_count(1); 1663SUCCESS 1664 1665drop table v_27420; 1666create table v_27420 (a int, b int, filler char(200)); 1667execute stmt; 1668a b filler 1669call p_verify_reprepare_count(1); 1670SUCCESS 1671 1672drop table t_27420_100; 1673drop table t_27420_101; 1674drop table v_27420; 1675deallocate prepare stmt; 1676# 1677# Bug#27430 Crash in subquery code when in PS and table DDL changed 1678# after PREPARE 1679# 1680drop table if exists t_27430_1; 1681drop table if exists t_27430_2; 1682create table t_27430_1 (a int not null, oref int not null, key(a)); 1683insert into t_27430_1 values 1684(1, 1), 1685(1, 1234), 1686(2, 3), 1687(2, 1234), 1688(3, 1234); 1689create table t_27430_2 (a int not null, oref int not null); 1690insert into t_27430_2 values 1691(1, 1), 1692(2, 2), 1693(1234, 3), 1694(1234, 4); 1695prepare stmt from 1696"select oref, a, a in (select a from t_27430_1 where oref=t_27430_2.oref) Z from t_27430_2"; 1697execute stmt; 1698oref a Z 16991 1 1 17002 2 0 17013 1234 0 17024 1234 0 1703call p_verify_reprepare_count(0); 1704SUCCESS 1705 1706drop table t_27430_1, t_27430_2; 1707create table t_27430_1 (a int, oref int, key(a)); 1708insert into t_27430_1 values 1709(1, 1), 1710(1, NULL), 1711(2, 3), 1712(2, NULL), 1713(3, NULL); 1714create table t_27430_2 (a int, oref int); 1715insert into t_27430_2 values 1716(1, 1), 1717(2,2), 1718(NULL, 3), 1719(NULL, 4); 1720execute stmt; 1721oref a Z 17221 1 1 17232 2 0 17243 NULL NULL 17254 NULL 0 1726call p_verify_reprepare_count(1); 1727SUCCESS 1728 1729drop table t_27430_1; 1730drop table t_27430_2; 1731deallocate prepare stmt; 1732# 1733# Bug#27690 Re-execution of prepared statement after table 1734# was replaced with a view crashes 1735# 1736drop table if exists t_27690_1; 1737drop view if exists v_27690_1; 1738drop table if exists v_27690_2; 1739create table t_27690_1 (a int, b int); 1740insert into t_27690_1 values (1,1),(2,2); 1741create table v_27690_1 as select * from t_27690_1; 1742create table v_27690_2 as select * from t_27690_1; 1743prepare stmt from "select * from v_27690_1, v_27690_2"; 1744execute stmt; 1745a b a b 17461 1 1 1 17472 2 1 1 17481 1 2 2 17492 2 2 2 1750execute stmt; 1751a b a b 17521 1 1 1 17532 2 1 1 17541 1 2 2 17552 2 2 2 1756drop table v_27690_1; 1757execute stmt; 1758ERROR 42S02: Table 'test.v_27690_1' doesn't exist 1759execute stmt; 1760ERROR 42S02: Table 'test.v_27690_1' doesn't exist 1761call p_verify_reprepare_count(0); 1762SUCCESS 1763 1764create view v_27690_1 as select A.a, A.b from t_27690_1 A, t_27690_1 B; 1765execute stmt; 1766a b a b 17671 1 1 1 17682 2 1 1 17691 1 1 1 17702 2 1 1 17711 1 2 2 17722 2 2 2 17731 1 2 2 17742 2 2 2 1775call p_verify_reprepare_count(1); 1776SUCCESS 1777 1778execute stmt; 1779a b a b 17801 1 1 1 17812 2 1 1 17821 1 1 1 17832 2 1 1 17841 1 2 2 17852 2 2 2 17861 1 2 2 17872 2 2 2 1788call p_verify_reprepare_count(0); 1789SUCCESS 1790 1791drop table t_27690_1; 1792drop view v_27690_1; 1793drop table v_27690_2; 1794deallocate prepare stmt; 1795#===================================================================== 1796# 1797# Bug#21294 Executing a prepared statement that executes 1798# a stored function which was recreat 1799# 1800create function f1() returns int return 10; 1801prepare stmt from "select f1()"; 1802execute stmt; 1803f1() 180410 1805drop function f1; 1806create function f1() returns int return 10; 1807execute stmt; 1808f1() 180910 1810drop function f1; 1811create function f1() returns int return 20; 1812execute stmt; 1813f1() 181420 1815call p_verify_reprepare_count(2); 1816SUCCESS 1817 1818drop function f1; 1819deallocate prepare stmt; 1820# 1821# Bug#12093 SP not found on second PS execution if another thread drops 1822# other SP in between 1823# 1824drop table if exists t_12093; 1825drop function if exists f_12093; 1826drop function if exists f_12093_unrelated; 1827drop procedure if exists p_12093; 1828drop view if exists v_12093_unrelated; 1829create table t_12093 (a int); 1830create function f_12093() returns int return (select count(*) from t_12093); 1831create procedure p_12093(a int) select * from t_12093; 1832create function f_12093_unrelated() returns int return 2; 1833create procedure p_12093_unrelated() begin end; 1834create view v_12093_unrelated as select * from t_12093; 1835connect con1,localhost,root,,; 1836connection default; 1837prepare stmt_sf from 'select f_12093();'; 1838prepare stmt_sp from 'call p_12093(f_12093())'; 1839execute stmt_sf; 1840f_12093() 18410 1842execute stmt_sp; 1843a 1844connection con1; 1845drop function f_12093_unrelated; 1846connection default; 1847# XXX: used to be a bug 1848execute stmt_sf; 1849f_12093() 18500 1851# XXX: used to be a bug 1852execute stmt_sp; 1853a 1854# XXX: used to be a bug 1855execute stmt_sf; 1856f_12093() 18570 1858# XXX: used to be a bug 1859execute stmt_sp; 1860a 1861connection default; 1862prepare stmt_sf from 'select f_12093();'; 1863prepare stmt_sp from 'call p_12093(f_12093())'; 1864execute stmt_sf; 1865f_12093() 18660 1867execute stmt_sp; 1868a 1869connection con1; 1870drop procedure p_12093_unrelated; 1871connection default; 1872# XXX: used to be a bug 1873execute stmt_sf; 1874f_12093() 18750 1876# XXX: used to be a bug 1877execute stmt_sp; 1878a 1879# XXX: used to be a bug 1880execute stmt_sf; 1881f_12093() 18820 1883# XXX: used to be a bug 1884execute stmt_sp; 1885a 1886connection default; 1887prepare stmt_sf from 'select f_12093();'; 1888prepare stmt_sp from 'call p_12093(f_12093())'; 1889execute stmt_sf; 1890f_12093() 18910 1892execute stmt_sp; 1893a 1894connection con1; 1895drop view v_12093_unrelated; 1896connection default; 1897# XXX: used to be a bug 1898execute stmt_sf; 1899f_12093() 19000 1901# XXX: used to be a bug 1902execute stmt_sp; 1903a 1904# XXX: used to be a bug 1905execute stmt_sf; 1906f_12093() 19070 1908# XXX: used to be a bug 1909execute stmt_sp; 1910a 1911connection default; 1912call p_verify_reprepare_count(6); 1913SUCCESS 1914 1915disconnect con1; 1916drop table t_12093; 1917drop function f_12093; 1918drop procedure p_12093; 1919deallocate prepare stmt_sf; 1920deallocate prepare stmt_sp; 1921===================================================================== 1922Ensure that metadata validation is performed for every type of 1923SQL statement where it is needed. 1924===================================================================== 1925# 1926# SQLCOM_SELECT 1927# 1928drop table if exists t1; 1929create table t1 (a int); 1930prepare stmt from "select 1 as res from dual where (1) in (select * from t1)"; 1931drop table t1; 1932create table t1 (x int); 1933execute stmt; 1934res 1935drop table t1; 1936deallocate prepare stmt; 1937call p_verify_reprepare_count(1); 1938SUCCESS 1939 1940# 1941# SQLCOM_CREATE_TABLE 1942# 1943drop table if exists t1; 1944drop table if exists t2; 1945create table t1 (a int); 1946prepare stmt from 'create table t2 as select * from t1'; 1947execute stmt; 1948drop table t2; 1949execute stmt; 1950drop table t2; 1951execute stmt; 1952call p_verify_reprepare_count(0); 1953SUCCESS 1954 1955execute stmt; 1956ERROR 42S01: Table 't2' already exists 1957call p_verify_reprepare_count(0); 1958SUCCESS 1959 1960execute stmt; 1961ERROR 42S01: Table 't2' already exists 1962call p_verify_reprepare_count(0); 1963SUCCESS 1964 1965drop table t2; 1966create temporary table t2 (a int); 1967execute stmt; 1968call p_verify_reprepare_count(0); 1969SUCCESS 1970 1971execute stmt; 1972ERROR 42S01: Table 't2' already exists 1973call p_verify_reprepare_count(0); 1974SUCCESS 1975 1976drop temporary table t2; 1977execute stmt; 1978ERROR 42S01: Table 't2' already exists 1979call p_verify_reprepare_count(0); 1980SUCCESS 1981 1982drop table t2; 1983execute stmt; 1984call p_verify_reprepare_count(0); 1985SUCCESS 1986 1987drop table t2; 1988create view t2 as select 1; 1989execute stmt; 1990Got one of the listed errors 1991call p_verify_reprepare_count(0); 1992SUCCESS 1993 1994execute stmt; 1995Got one of the listed errors 1996call p_verify_reprepare_count(0); 1997SUCCESS 1998 1999drop view t2; 2000drop table t1; 2001create table t1 (x varchar(20)); 2002execute stmt; 2003call p_verify_reprepare_count(1); 2004SUCCESS 2005 2006select * from t2; 2007x 2008drop table t2; 2009execute stmt; 2010call p_verify_reprepare_count(0); 2011SUCCESS 2012 2013drop table t2; 2014alter table t1 add column y decimal(10,3); 2015execute stmt; 2016call p_verify_reprepare_count(1); 2017SUCCESS 2018 2019select * from t2; 2020x y 2021drop table t2; 2022execute stmt; 2023call p_verify_reprepare_count(0); 2024SUCCESS 2025 2026drop table t1; 2027deallocate prepare stmt; 2028create table t1 (a int); 2029insert into t1 (a) values (1); 2030prepare stmt from "create temporary table if not exists t2 as select * from t1"; 2031execute stmt; 2032drop table t2; 2033execute stmt; 2034call p_verify_reprepare_count(0); 2035SUCCESS 2036 2037execute stmt; 2038Warnings: 2039Note 1050 Table 't2' already exists 2040call p_verify_reprepare_count(1); 2041SUCCESS 2042 2043select * from t2; 2044a 20451 2046execute stmt; 2047Warnings: 2048Note 1050 Table 't2' already exists 2049call p_verify_reprepare_count(0); 2050SUCCESS 2051 2052select * from t2; 2053a 20541 2055drop table t2; 2056create temporary table t2 (a varchar(10)); 2057execute stmt; 2058Warnings: 2059Note 1050 Table 't2' already exists 2060select * from t2; 2061a 2062call p_verify_reprepare_count(1); 2063SUCCESS 2064 2065drop table t1; 2066create table t1 (x int); 2067execute stmt; 2068Warnings: 2069Note 1050 Table 't2' already exists 2070call p_verify_reprepare_count(1); 2071SUCCESS 2072 2073execute stmt; 2074Warnings: 2075Note 1050 Table 't2' already exists 2076call p_verify_reprepare_count(0); 2077SUCCESS 2078 2079drop table t1; 2080drop temporary table t2; 2081drop table t2; 2082deallocate prepare stmt; 2083create table t1 (a int); 2084prepare stmt from "create table t2 like t1"; 2085execute stmt; 2086call p_verify_reprepare_count(0); 2087SUCCESS 2088 2089drop table t2; 2090execute stmt; 2091call p_verify_reprepare_count(0); 2092SUCCESS 2093 2094drop table t2; 2095drop table t1; 2096execute stmt; 2097ERROR 42S02: Table 'test.t1' doesn't exist 2098call p_verify_reprepare_count(0); 2099SUCCESS 2100 2101execute stmt; 2102ERROR 42S02: Table 'test.t1' doesn't exist 2103call p_verify_reprepare_count(0); 2104SUCCESS 2105 2106create table t1 (x char(17)); 2107execute stmt; 2108call p_verify_reprepare_count(1); 2109SUCCESS 2110 2111drop table t2; 2112execute stmt; 2113call p_verify_reprepare_count(0); 2114SUCCESS 2115 2116drop table t2; 2117alter table t1 add column y time; 2118execute stmt; 2119call p_verify_reprepare_count(1); 2120SUCCESS 2121 2122select * from t2; 2123x y 2124drop table t2; 2125execute stmt; 2126call p_verify_reprepare_count(0); 2127SUCCESS 2128 2129drop table t1; 2130drop table t2; 2131deallocate prepare stmt; 2132# 2133# SQLCOM_UPDATE 2134# 2135drop table if exists t1, t2; 2136create table t1 (a int); 2137create table t2 (a int); 2138prepare stmt from "update t2 set a=a+1 where (1) in (select * from t1)"; 2139execute stmt; 2140drop table t1; 2141create table t1 (x int); 2142execute stmt; 2143drop table t1, t2; 2144deallocate prepare stmt; 2145# 2146# SQLCOM_INSERT 2147# 2148drop table if exists t1, t2; 2149create table t1 (a int); 2150create table t2 (a int); 2151prepare stmt from "insert into t2 set a=((1) in (select * from t1))"; 2152execute stmt; 2153drop table t1; 2154create table t1 (x int); 2155execute stmt; 2156drop table t1, t2; 2157deallocate prepare stmt; 2158# 2159# SQLCOM_INSERT_SELECT 2160# 2161drop table if exists t1, t2; 2162create table t1 (a int); 2163create table t2 (a int); 2164prepare stmt from "insert into t2 select * from t1"; 2165execute stmt; 2166drop table t1; 2167create table t1 (x int); 2168execute stmt; 2169drop table t1, t2; 2170deallocate prepare stmt; 2171# 2172# SQLCOM_REPLACE 2173# 2174drop table if exists t1, t2; 2175create table t1 (a int); 2176create table t2 (a int); 2177prepare stmt from "replace t2 set a=((1) in (select * from t1))"; 2178execute stmt; 2179drop table t1; 2180create table t1 (x int); 2181execute stmt; 2182drop table t1, t2; 2183deallocate prepare stmt; 2184# 2185# SQLCOM_REPLACE_SELECT 2186# 2187drop table if exists t1, t2; 2188create table t1 (a int); 2189create table t2 (a int); 2190prepare stmt from "replace t2 select * from t1"; 2191execute stmt; 2192drop table t1; 2193create table t1 (x int); 2194execute stmt; 2195drop table t1, t2; 2196deallocate prepare stmt; 2197# 2198# SQLCOM_DELETE 2199# 2200drop table if exists t1, t2; 2201create table t1 (a int); 2202create table t2 (a int); 2203prepare stmt from "delete from t2 where (1) in (select * from t1)"; 2204execute stmt; 2205drop table t1; 2206create table t1 (x int); 2207execute stmt; 2208drop table t1, t2; 2209deallocate prepare stmt; 2210# 2211# SQLCOM_DELETE_MULTI 2212# 2213drop table if exists t1, t2, t3; 2214create table t1 (a int); 2215create table t2 (a int); 2216create table t3 (a int); 2217prepare stmt from "delete t2, t3 from t2, t3 where (1) in (select * from t1)"; 2218execute stmt; 2219drop table t1; 2220create table t1 (x int); 2221execute stmt; 2222drop table t1, t2, t3; 2223deallocate prepare stmt; 2224# 2225# SQLCOM_UPDATE_MULTI 2226# 2227drop table if exists t1, t2, t3; 2228create table t1 (a int); 2229create table t2 (a int); 2230create table t3 (a int); 2231prepare stmt from "update t2, t3 set t3.a=t2.a, t2.a=null where (1) in (select * from t1)"; 2232drop table t1; 2233create table t1 (x int); 2234execute stmt; 2235drop table t1, t2, t3; 2236deallocate prepare stmt; 2237# Intermediate results: 8 SQLCOMs tested, 8 automatic reprepares 2238call p_verify_reprepare_count(8); 2239SUCCESS 2240 2241# 2242# SQLCOM_LOAD 2243# 2244drop table if exists t1; 2245create table t1 (a varchar(20)); 2246prepare stmt from "load data infile '../std_data_ln/words.dat' into table t1"; 2247ERROR HY000: This command is not supported in the prepared statement protocol yet 2248drop table t1; 2249# 2250# SQLCOM_SHOW_DATABASES 2251# 2252drop table if exists t1; 2253create table t1 (a int); 2254prepare stmt from "show databases where (1) in (select * from t1)"; 2255execute stmt; 2256Database 2257drop table t1; 2258create table t1 (x int); 2259execute stmt; 2260Database 2261drop table t1; 2262deallocate prepare stmt; 2263# 2264# SQLCOM_SHOW_TABLES 2265# 2266drop table if exists t1; 2267create table t1 (a int); 2268prepare stmt from "show tables where (1) in (select * from t1)"; 2269execute stmt; 2270Tables_in_test 2271drop table t1; 2272create table t1 (x int); 2273execute stmt; 2274Tables_in_test 2275drop table t1; 2276deallocate prepare stmt; 2277# 2278# SQLCOM_SHOW_FIELDS 2279# 2280drop table if exists t1; 2281create table t1 (a int); 2282prepare stmt from "show fields from t1 where (1) in (select * from t1)"; 2283execute stmt; 2284Field Type Null Key Default Extra 2285drop table t1; 2286create table t1 (x int); 2287execute stmt; 2288Field Type Null Key Default Extra 2289drop table t1; 2290deallocate prepare stmt; 2291# 2292# SQLCOM_SHOW_KEYS 2293# 2294drop table if exists t1; 2295create table t1 (a int); 2296prepare stmt from "show keys from t1 where (1) in (select * from t1)"; 2297execute stmt; 2298Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 2299drop table t1; 2300create table t1 (x int); 2301execute stmt; 2302Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 2303drop table t1; 2304deallocate prepare stmt; 2305# 2306# SQLCOM_SHOW_VARIABLES 2307# 2308drop table if exists t1; 2309create table t1 (a int); 2310prepare stmt from "show variables where (1) in (select * from t1)"; 2311execute stmt; 2312Variable_name Value 2313drop table t1; 2314create table t1 (x int); 2315execute stmt; 2316Variable_name Value 2317drop table t1; 2318deallocate prepare stmt; 2319# 2320# SQLCOM_SHOW_STATUS 2321# 2322drop table if exists t1; 2323create table t1 (a int); 2324prepare stmt from "show status where (1) in (select * from t1)"; 2325execute stmt; 2326Variable_name Value 2327drop table t1; 2328create table t1 (x int); 2329execute stmt; 2330Variable_name Value 2331drop table t1; 2332deallocate prepare stmt; 2333# 2334# SQLCOM_SHOW_ENGINE_STATUS, SQLCOM_SHOW_ENGINE_LOGS, 2335# SQLCOM_SHOW_ENGINE_MUTEX, SQLCOM_SHOW_PROCESSLIST 2336# 2337# Currently can not have a where clause, need to be covered 2338# with tests 2339drop table if exists t1; 2340create table t1 (a int); 2341prepare stmt from "show engine all status where (1) in (select * from t1)"; 2342ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where (1) in (select * from t1)' at line 1 2343prepare stmt from "show engine all logs where (1) in (select * from t1)"; 2344ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where (1) in (select * from t1)' at line 1 2345prepare stmt from "show engine all mutex where (1) in (select * from t1)"; 2346ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where (1) in (select * from t1)' at line 1 2347prepare stmt from "show processlist where (1) in (select * from t1)"; 2348ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where (1) in (select * from t1)' at line 1 2349drop table t1; 2350# 2351# SQLCOM_SHOW_CHARSETS 2352# 2353drop table if exists t1; 2354create table t1 (a int); 2355prepare stmt from "show charset where (1) in (select * from t1)"; 2356execute stmt; 2357Charset Description Default collation Maxlen 2358drop table t1; 2359create table t1 (x int); 2360execute stmt; 2361Charset Description Default collation Maxlen 2362drop table t1; 2363deallocate prepare stmt; 2364# 2365# SQLCOM_SHOW_COLLATIONS 2366# 2367drop table if exists t1; 2368create table t1 (a int); 2369prepare stmt from "show collation where (1) in (select * from t1)"; 2370execute stmt; 2371Collation Charset Id Default Compiled Sortlen 2372drop table t1; 2373create table t1 (x int); 2374execute stmt; 2375Collation Charset Id Default Compiled Sortlen 2376drop table t1; 2377deallocate prepare stmt; 2378# 2379# SQLCOM_SHOW_TABLE_STATUS 2380# 2381drop table if exists t1; 2382create table t1 (a int); 2383prepare stmt from "show table status where (1) in (select * from t1)"; 2384execute stmt; 2385Name 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 Max_index_length Temporary 2386drop table t1; 2387create table t1 (x int); 2388execute stmt; 2389Name 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 Max_index_length Temporary 2390drop table t1; 2391deallocate prepare stmt; 2392# 2393# SQLCOM_SHOW_TRIGGERS 2394# 2395drop table if exists t1; 2396create table t1 (a int); 2397prepare stmt from "show triggers where (1) in (select * from t1)"; 2398execute stmt; 2399Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation 2400drop table t1; 2401create table t1 (x int); 2402execute stmt; 2403Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation 2404drop table t1; 2405deallocate prepare stmt; 2406# 2407# SQLCOM_SHOW_OPEN_TABLES 2408# 2409drop table if exists t1; 2410create table t1 (a int); 2411prepare stmt from "show open tables where (1) in (select * from t1)"; 2412execute stmt; 2413Database Table In_use Name_locked 2414drop table t1; 2415create table t1 (x int); 2416execute stmt; 2417Database Table In_use Name_locked 2418drop table t1; 2419deallocate prepare stmt; 2420# 2421# SQLCOM_SHOW_STATUS_PROC 2422# 2423drop table if exists t1; 2424create table t1 (a int); 2425prepare stmt from "show procedure status where (1) in (select * from t1)"; 2426execute stmt; 2427Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 2428drop table t1; 2429create table t1 (x int); 2430execute stmt; 2431Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 2432drop table t1; 2433deallocate prepare stmt; 2434# 2435# SQLCOM_SHOW_STATUS_FUNC 2436# 2437drop table if exists t1; 2438create table t1 (a int); 2439prepare stmt from "show function status where (1) in (select * from t1)"; 2440execute stmt; 2441Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 2442drop table t1; 2443create table t1 (x int); 2444execute stmt; 2445Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 2446drop table t1; 2447deallocate prepare stmt; 2448# 2449# SQLCOM_SHOW_EVENTS 2450# 2451# 2452# Please see this test in ps.test, it requires not_embedded.inc 2453# 2454# 2455# SQLCOM_SET_OPTION 2456# 2457drop table if exists t1; 2458create table t1 (a int); 2459prepare stmt from "set @a=((1) in (select * from t1))"; 2460execute stmt; 2461drop table t1; 2462create table t1 (x int); 2463execute stmt; 2464drop table t1; 2465deallocate prepare stmt; 2466# 2467# SQLCOM_DO 2468# 2469drop table if exists t1; 2470create table t1 (a int); 2471prepare stmt from "do ((1) in (select * from t1))"; 2472execute stmt; 2473drop table t1; 2474create table t1 (x int); 2475execute stmt; 2476drop table t1; 2477deallocate prepare stmt; 2478# 2479# SQLCOM_CALL 2480# 2481drop table if exists t1; 2482drop procedure if exists p1; 2483create procedure p1(a int) begin end; 2484create table t1 (a int); 2485prepare stmt from "call p1((1) in (select * from t1))"; 2486execute stmt; 2487drop table t1; 2488create table t1 (x int); 2489execute stmt; 2490drop table t1; 2491drop procedure p1; 2492deallocate prepare stmt; 2493# 2494# SQLCOM_CREATE_VIEW 2495# 2496drop table if exists t1; 2497drop view if exists v1; 2498create table t1 (a int); 2499prepare stmt from "create view v1 as select * from t1"; 2500execute stmt; 2501drop view v1; 2502drop table t1; 2503create table t1 (x int); 2504execute stmt; 2505drop view v1; 2506drop table t1; 2507deallocate prepare stmt; 2508# Intermediate result: number of reprepares matches the number 2509# of tests 2510call p_verify_reprepare_count(17); 2511SUCCESS 2512 2513# 2514# SQLCOM_ALTER_VIEW 2515# 2516drop view if exists v1; 2517create view v1 as select 1; 2518prepare stmt from "alter view v1 as select 2"; 2519ERROR HY000: This command is not supported in the prepared statement protocol yet 2520drop view v1; 2521# Cleanup 2522# 2523drop temporary table if exists t1, t2, t3; 2524drop table if exists t1, t2, t3, v1, v2; 2525drop procedure if exists p_verify_reprepare_count; 2526drop procedure if exists p1; 2527drop function if exists f1; 2528drop view if exists v1, v2; 2529# 2530# Additional coverage for refactoring which was made as part of work 2531# on bug '27480: Extend CREATE TEMPORARY TABLES privilege to allow 2532# temp table operations'. 2533# 2534# Check that we don't try to pre-open temporary tables for the elements 2535# from prelocking list, as this can lead to unwarranted ER_CANT_REOPEN 2536# errors. 2537DROP TABLE IF EXISTS t1, tm; 2538CREATE TABLE t1 (a INT); 2539CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW 2540SET @a:= (SELECT COUNT(*) FROM t1); 2541# Prelocking list for the below statement should 2542# contain t1 twice - once for the INSERT and once 2543# SELECT from the trigger. 2544PREPARE stmt1 FROM 'INSERT INTO t1 VALUES (1)'; 2545EXECUTE stmt1; 2546# Create temporary table which will shadow t1. 2547CREATE TEMPORARY TABLE t1 (b int); 2548# The below execution of statement should not fail with ER_CANT_REOPEN 2549# error. Instead stmt1 should be auto-matically reprepared and succeed. 2550EXECUTE stmt1; 2551DEALLOCATE PREPARE stmt1; 2552DROP TEMPORARY TABLE t1; 2553DROP TABLE t1; 2554# 2555# Also check that we properly reset table list elements from UNION 2556# clause of CREATE TABLE and ALTER TABLE statements. 2557# 2558CREATE TEMPORARY TABLE t1 (i INT); 2559PREPARE stmt2 FROM 'CREATE TEMPORARY TABLE tm (i INT) ENGINE=MERGE UNION=(t1)'; 2560EXECUTE stmt2; 2561DROP TEMPORARY TABLE tm; 2562EXECUTE stmt2; 2563DEALLOCATE PREPARE stmt2; 2564PREPARE stmt3 FROM 'ALTER TABLE tm UNION=(t1)'; 2565EXECUTE stmt3; 2566EXECUTE stmt3; 2567DEALLOCATE PREPARE stmt3; 2568DROP TEMPORARY TABLES tm, t1; 2569# 2570# Start of 10.1 tests 2571# 2572# 2573# MDEV-10702 Crash in SET STATEMENT FOR EXECUTE 2574# 2575CREATE TABLE t1 (a INT); 2576PREPARE stmt FROM 'INSERT INTO t1 VALUES (@@max_sort_length)'; 2577SET STATEMENT max_sort_length=2048 FOR EXECUTE stmt; 2578SELECT * FROM t1; 2579a 25802048 2581CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=NEW.a + 1; 2582SET STATEMENT max_sort_length=2048 FOR EXECUTE stmt; 2583SELECT * FROM t1; 2584a 25852048 25861025 2587DROP TRIGGER tr1; 2588SET STATEMENT max_sort_length=2048 FOR EXECUTE stmt; 2589SELECT * FROM t1; 2590a 25912048 25921025 25931024 2594DROP TABLE t1; 2595# 2596# End of 10.1 tests 2597# 2598