1# 2# Testing the behavior of 'PREPARE', 'DDL', 'EXECUTE' scenarios 3# 4# Background: 5# In a statement like "select * from t1", t1 can be: 6# - nothing (the table does not exist) 7# - a real table 8# - a temporary table 9# - a view 10# 11# Changing the nature of "t1" between a PREPARE and an EXECUTE 12# can invalidate the internal state of a prepared statement, so that, 13# during the execute, the server should: 14# - detect state changes and fail to execute a statement, 15# instead of crashing the server or returning wrong results 16# - "RE-PREPARE" the statement to restore a valid internal state. 17# 18# Also, changing the physical structure of "t1", by: 19# - changing the definition of t1 itself (DDL on tables, views) 20# - changing TRIGGERs associated with a table 21# - changing PROCEDURE, FUNCTION referenced by a TRIGGER body, 22# - changing PROCEDURE, FUNCTION referenced by a VIEW body, 23# impacts the internal structure of a prepared statement, and should 24# cause the same verifications at execute time to be performed. 25# 26# This test provided in this file cover the different state transitions 27# between a PREPARE and an EXECUTE, and are organized as follows: 28# - Part 1: NOTHING -> TABLE 29# - Part 2: NOTHING -> TEMPORARY TABLE 30# - Part 3: NOTHING -> VIEW 31# - Part 4: TABLE -> NOTHING 32# - Part 5: TABLE -> TABLE (DDL) 33# - Part 6: TABLE -> TABLE (TRIGGER) 34# - Part 7: TABLE -> TABLE (TRIGGER dependencies) 35# - Part 8: TABLE -> TEMPORARY TABLE 36# - Part 9: TABLE -> VIEW 37# - Part 10: TEMPORARY TABLE -> NOTHING 38# - Part 11: TEMPORARY TABLE -> TABLE 39# - Part 12: TEMPORARY TABLE -> TEMPORARY TABLE (DDL) 40# - Part 13: TEMPORARY TABLE -> VIEW 41# - Part 14: VIEW -> NOTHING 42# - Part 15: VIEW -> TABLE 43# - Part 16: VIEW -> TEMPORARY TABLE 44# - Part 17: VIEW -> VIEW (DDL) 45# - Part 18: VIEW -> VIEW (VIEW dependencies) 46# - Part 19: Special tables (INFORMATION_SCHEMA) 47# - Part 20: Special tables (log tables) 48# - Part 21: Special tables (system tables) 49# - Part 22: Special tables (views temp tables) 50# - Part 23: Special statements 51# - Part 24: Testing the strength of TABLE_SHARE version 52--disable_warnings 53drop temporary table if exists t1, t2, t3; 54drop table if exists t1, t2, t3; 55drop procedure if exists p_verify_reprepare_count; 56drop procedure if exists p1; 57drop function if exists f1; 58drop view if exists v1, v2; 59--enable_warnings 60 61# Avoid selecting from a huge table possibly left over from previous tests, 62# as this really hurts --valgrind testing. 63TRUNCATE TABLE mysql.general_log; 64 65delimiter |; 66create procedure p_verify_reprepare_count(expected int) 67begin 68 declare old_reprepare_count int default @reprepare_count; 69 70 select variable_value from 71 information_schema.session_status where 72 variable_name='com_stmt_reprepare' 73 into @reprepare_count; 74 75 if old_reprepare_count + expected <> @reprepare_count then 76 select concat("Expected: ", expected, 77 ", actual: ", @reprepare_count - old_reprepare_count) 78 as "ERROR"; 79 else 80 select '' as "SUCCESS"; 81 end if; 82end| 83delimiter ;| 84set @reprepare_count= 0; 85flush status; 86 87--echo ===================================================================== 88--echo Part 1: NOTHING -> TABLE transitions 89--echo ===================================================================== 90 91# can not be tested since prepare failed 92--error ER_NO_SUCH_TABLE 93prepare stmt from "select * from t1"; 94 95--echo ===================================================================== 96--echo Part 2: NOTHING -> TEMPORARY TABLE transitions 97--echo ===================================================================== 98 99# can not be tested 100 101--echo ===================================================================== 102--echo Part 3: NOTHING -> VIEW transitions 103--echo ===================================================================== 104 105# can not be tested 106 107--echo ===================================================================== 108--echo Part 4: TABLE -> NOTHING transitions 109--echo ===================================================================== 110 111--echo # Test 4-a: select ... from <table> 112create table t1 (a int); 113 114prepare stmt from "select * from t1"; 115execute stmt; 116call p_verify_reprepare_count(0); 117execute stmt; 118call p_verify_reprepare_count(0); 119 120drop table t1; 121--error ER_NO_SUCH_TABLE 122execute stmt; 123call p_verify_reprepare_count(0); 124--error ER_NO_SUCH_TABLE 125execute stmt; 126call p_verify_reprepare_count(0); 127deallocate prepare stmt; 128 129--echo # Test 4-b: TABLE -> NOTHING by renaming the table 130create table t1 (a int); 131prepare stmt from "select * from t1"; 132execute stmt; 133call p_verify_reprepare_count(0); 134execute stmt; 135call p_verify_reprepare_count(0); 136 137rename table t1 to t2; 138--error ER_NO_SUCH_TABLE 139execute stmt; 140call p_verify_reprepare_count(0); 141--error ER_NO_SUCH_TABLE 142execute stmt; 143call p_verify_reprepare_count(0); 144 145deallocate prepare stmt; 146drop table t2; 147 148--echo ===================================================================== 149--echo Part 5: TABLE -> TABLE (DDL) transitions 150--echo ===================================================================== 151 152create table t1 (a int); 153 154prepare stmt from "select a from t1"; 155execute stmt; 156call p_verify_reprepare_count(0); 157execute stmt; 158call p_verify_reprepare_count(0); 159 160alter table t1 add column (b int); 161 162execute stmt; 163call p_verify_reprepare_count(1); 164execute stmt; 165call p_verify_reprepare_count(0); 166 167drop table t1; 168deallocate prepare stmt; 169 170 171--echo ===================================================================== 172--echo Part 6: TABLE -> TABLE (TRIGGER) transitions 173--echo ===================================================================== 174 175--echo # Test 6-a: adding a relevant trigger 176 177create table t1 (a int); 178 179prepare stmt from "insert into t1 (a) value (?)"; 180set @val=1; 181execute stmt using @val; 182call p_verify_reprepare_count(0); 183 184# Relevant trigger: execute should reprepare 185create trigger t1_bi before insert on t1 for each row 186 set @message= new.a; 187 188set @val=2; 189execute stmt using @val; 190call p_verify_reprepare_count(1); 191select @message; 192set @val=3; 193execute stmt using @val; 194call p_verify_reprepare_count(0); 195select @message; 196 197prepare stmt from "insert into t1 (a) value (?)"; 198set @val=4; 199execute stmt using @val; 200call p_verify_reprepare_count(0); 201select @message; 202 203--echo # Test 6-b: adding an irrelevant trigger 204 205# Unrelated trigger: reprepare may or may not happen, implementation dependent 206create trigger t1_bd before delete on t1 for each row 207 set @message= old.a; 208 209set @val=5; 210execute stmt using @val; 211call p_verify_reprepare_count(1); 212select @message; 213set @val=6; 214execute stmt using @val; 215call p_verify_reprepare_count(0); 216select @message; 217 218prepare stmt from "insert into t1 (a) value (?)"; 219set @val=7; 220execute stmt using @val; 221call p_verify_reprepare_count(0); 222select @message; 223 224--echo # Test 6-c: changing a relevant trigger 225 226# Relevant trigger: execute should reprepare 227drop trigger t1_bi; 228create trigger t1_bi before insert on t1 for each row 229 set @message= concat("new trigger: ", new.a); 230 231set @val=8; 232execute stmt using @val; 233call p_verify_reprepare_count(1); 234select @message; 235set @val=9; 236execute stmt using @val; 237call p_verify_reprepare_count(0); 238select @message; 239 240prepare stmt from "insert into t1 (a) value (?)"; 241set @val=10; 242execute stmt using @val; 243call p_verify_reprepare_count(0); 244select @message; 245 246--echo # Test 6-d: changing an irrelevant trigger 247 248# Unrelated trigger: reprepare may or may not happen, implementation dependent 249drop trigger t1_bd; 250 251set @val=11; 252execute stmt using @val; 253call p_verify_reprepare_count(1); 254select @message; 255 256--echo Test 6-e: removing a relevant trigger 257 258drop trigger t1_bi; 259 260set @val=12; 261execute stmt using @val; 262call p_verify_reprepare_count(1); 263select @message; 264set @val=13; 265execute stmt using @val; 266call p_verify_reprepare_count(0); 267select @message; 268 269prepare stmt from "insert into t1 (a) value (?)"; 270set @val=14; 271execute stmt using @val; 272call p_verify_reprepare_count(0); 273select @message; 274 275select * from t1 order by a; 276drop table t1; 277deallocate prepare stmt; 278 279--echo ===================================================================== 280--echo Part 7: TABLE -> TABLE (TRIGGER dependencies) transitions 281--echo ===================================================================== 282 283--echo # Test 7-a: dependent PROCEDURE has changed 284--echo # 285 286create table t1 (a int); 287create trigger t1_ai after insert on t1 for each row 288 call p1(new.a); 289create procedure p1(a int) begin end; 290prepare stmt from "insert into t1 (a) values (?)"; 291set @var= 1; 292execute stmt using @var; 293drop procedure p1; 294create procedure p1 (a int) begin end; 295set @var= 2; 296execute stmt using @var; 297--echo # Cleanup 298drop procedure p1; 299call p_verify_reprepare_count(1); 300 301--echo # Test 7-b: dependent FUNCTION has changed 302--echo # 303--echo # Note, this scenario is supported, subject of Bug#12093 304--echo # 305drop trigger t1_ai; 306create trigger t1_ai after insert on t1 for each row 307 select f1(new.a+1) into @var; 308create function f1 (a int) returns int return a; 309prepare stmt from "insert into t1(a) values (?)"; 310set @var=3; 311execute stmt using @var; 312select @var; 313drop function f1; 314create function f1 (a int) returns int return 0; 315execute stmt using @var; 316call p_verify_reprepare_count(1); 317drop function f1; 318deallocate prepare stmt; 319 320--echo # Test 7-c: dependent VIEW has changed 321--echo # 322--echo # Note, this scenario is not functioning correctly, see 323--echo # Bug#33255 Trigger using views and view ddl : corrupted triggers 324--echo # and Bug #33000 Triggers do not detect changes in meta-data. 325--echo # 326drop trigger t1_ai; 327create table t2 (a int unique); 328create table t3 (a int unique); 329create view v1 as select a from t2; 330create trigger t1_ai after insert on t1 for each row 331 insert into v1 (a) values (new.a); 332 333--echo # Demonstrate that the same bug is present 334--echo # without prepared statements 335insert into t1 (a) values (5); 336select * from t2; 337select * from t3; 338drop view v1; 339create view v1 as select a from t3; 340--error ER_NO_SUCH_TABLE 341insert into t1 (a) values (6); 342flush table t1; 343insert into t1 (a) values (6); 344select * from t2; 345select * from t3; 346 347prepare stmt from "insert into t1 (a) values (?)"; 348set @var=7; 349execute stmt using @var; 350call p_verify_reprepare_count(0); 351select * from t3; 352select * from t2; 353drop view v1; 354create view v1 as select a from t2; 355set @var=8; 356--echo # View in the INSERT-statement in the trigger is still pointing to 357--echo # table 't3', because the trigger hasn't noticed the change 358--echo # in view definition. This will be fixed by WL#4179. 359--echo # 360--echo # The prepared INSERT-statement however does notice the change, 361--echo # but repreparation of the main statement doesn't cause repreparation 362--echo # of trigger statements. 363--echo # 364--echo # The following EXECUTE results in ER_NO_SUCH_TABLE (t3) error, because 365--echo # pre-locking list of the prepared statement has been changed 366--echo # (the prepared statement has noticed the meta-data change), 367--echo # but the trigger still tries to deal with 't3', which is not opened. 368--echo # That's why '8' is not inserted neither into 't2', nor into 't3'. 369--error ER_NO_SUCH_TABLE 370execute stmt using @var; 371call p_verify_reprepare_count(1); 372select * from t2; 373select * from t3; 374flush table t1; 375set @var=9; 376execute stmt using @var; 377call p_verify_reprepare_count(1); 378select * from t2; 379select * from t3; 380drop view v1; 381drop table t1,t2,t3; 382 383--echo # Test 7-d: dependent TABLE has changed 384create table t1 (a int); 385create trigger t1_ai after insert on t1 for each row 386 insert into t2 (a) values (new.a); 387create table t2 (a int); 388 389prepare stmt from "insert into t1 (a) values (?)"; 390set @var=1; 391execute stmt using @var; 392alter table t2 add column comment varchar(255); 393set @var=2; 394--echo # Since the dependent table is tracked in the prelocked 395--echo # list of the prepared statement, invalidation happens 396--echo # and the statement is re-prepared. This is an unnecessary 397--echo # side effect, since the statement that *is* dependent 398--echo # on t2 definition is inside the trigger, and it is currently 399--echo # not reprepared (see the previous test case). 400execute stmt using @var; 401call p_verify_reprepare_count(1); 402select * from t1; 403select * from t2; 404drop table t1,t2; 405 406--echo # Test 7-e: dependent TABLE TRIGGER has changed 407create table t1 (a int); 408create trigger t1_ai after insert on t1 for each row 409 insert into t2 (a) values (new.a); 410create table t2 (a int unique); 411create trigger t2_ai after insert on t2 for each row 412 insert into t3 (a) values (new.a); 413create table t3 (a int unique); 414create table t4 (a int unique); 415 416insert into t1 (a) values (1); 417select * from t1 join t2 on (t1.a=t2.a) join t3 on (t2.a=t3.a); 418drop trigger t2_ai; 419create trigger t2_ai after insert on t2 for each row 420 insert into t4 (a) values (new.a); 421insert into t1 (a) values (2); 422select * from t1 join t2 on (t1.a=t2.a) join t4 on (t2.a=t4.a); 423 424prepare stmt from "insert into t1 (a) values (?)"; 425set @var=3; 426execute stmt using @var; 427select * from t1 join t2 on (t1.a=t2.a) join t4 on (t2.a=t4.a); 428drop trigger t2_ai; 429create trigger t2_ai after insert on t2 for each row 430 insert into t3 (a) values (new.a); 431set @var=4; 432execute stmt using @var; 433call p_verify_reprepare_count(1); 434select * from t1 join t2 on (t1.a=t2.a) join t3 on (t2.a=t3.a); 435select * from t1 join t2 on (t1.a=t2.a) join t4 on (t2.a=t4.a); 436 437drop table t1, t2, t3, t4; 438deallocate prepare stmt; 439 440--echo ===================================================================== 441--echo Part 8: TABLE -> TEMPORARY TABLE transitions 442--echo ===================================================================== 443 444--echo # Test 8-a: base table used recreated as temporary table 445create table t1 (a int); 446 447prepare stmt from "select * from t1"; 448execute stmt; 449 450drop table t1; 451create temporary table t1 (a int); 452 453execute stmt; 454call p_verify_reprepare_count(1); 455execute stmt; 456call p_verify_reprepare_count(0); 457 458drop table t1; 459deallocate prepare stmt; 460 461--echo # Test 8-b: temporary table has precedence over base table with same name 462create table t1 (a int); 463prepare stmt from 'select count(*) from t1'; 464execute stmt; 465call p_verify_reprepare_count(0); 466execute stmt; 467call p_verify_reprepare_count(0); 468 469create temporary table t1 AS SELECT 1; 470execute stmt; 471call p_verify_reprepare_count(1); 472execute stmt; 473call p_verify_reprepare_count(0); 474 475deallocate prepare stmt; 476drop temporary table t1; 477drop table t1; 478 479 480--echo ===================================================================== 481--echo Part 9: TABLE -> VIEW transitions 482--echo ===================================================================== 483 484create table t1 (a int); 485 486prepare stmt from "select * from t1"; 487execute stmt; 488call p_verify_reprepare_count(0); 489 490drop table t1; 491create table t2 (a int); 492create view t1 as select * from t2; 493 494execute stmt; 495call p_verify_reprepare_count(1); 496 497drop view t1; 498drop table t2; 499deallocate prepare stmt; 500 501--echo ===================================================================== 502--echo Part 10: TEMPORARY TABLE -> NOTHING transitions 503--echo ===================================================================== 504 505create temporary table t1 (a int); 506 507prepare stmt from "select * from t1"; 508execute stmt; 509call p_verify_reprepare_count(0); 510 511drop temporary table t1; 512--error ER_NO_SUCH_TABLE 513execute stmt; 514call p_verify_reprepare_count(0); 515deallocate prepare stmt; 516 517--echo ===================================================================== 518--echo Part 11: TEMPORARY TABLE -> TABLE transitions 519--echo ===================================================================== 520 521--echo # Test 11-a: temporary table replaced by base table 522create table t1 (a int); 523insert into t1 (a) value (1); 524create temporary table t1 (a int); 525 526prepare stmt from "select * from t1"; 527execute stmt; 528call p_verify_reprepare_count(0); 529 530drop temporary table t1; 531 532execute stmt; 533call p_verify_reprepare_count(1); 534 535select * from t1; 536drop table t1; 537deallocate prepare stmt; 538 539 540--echo # Test 11-b: temporary table has precedence over base table with same name 541--echo # temporary table disappears 542create table t1 (a int); 543create temporary table t1 as select 1 as a; 544prepare stmt from "select count(*) from t1"; 545execute stmt; 546call p_verify_reprepare_count(0); 547execute stmt; 548call p_verify_reprepare_count(0); 549 550drop temporary table t1; 551execute stmt; 552call p_verify_reprepare_count(1); 553execute stmt; 554call p_verify_reprepare_count(0); 555 556deallocate prepare stmt; 557drop table t1; 558 559 560--echo ===================================================================== 561--echo Part 12: TEMPORARY TABLE -> TEMPORARY TABLE (DDL) transitions 562--echo ===================================================================== 563 564create temporary table t1 (a int); 565 566prepare stmt from "select a from t1"; 567execute stmt; 568call p_verify_reprepare_count(0); 569 570drop temporary table t1; 571create temporary table t1 (a int, b int); 572 573execute stmt; 574call p_verify_reprepare_count(1); 575 576select * from t1; 577drop temporary table t1; 578deallocate prepare stmt; 579 580--echo ===================================================================== 581--echo Part 13: TEMPORARY TABLE -> VIEW transitions 582--echo ===================================================================== 583 584create temporary table t1 (a int); 585create table t2 (a int); 586 587prepare stmt from "select * from t1"; 588execute stmt; 589call p_verify_reprepare_count(0); 590 591drop temporary table t1; 592create view t1 as select * from t2; 593 594execute stmt; 595call p_verify_reprepare_count(1); 596 597drop view t1; 598drop table t2; 599deallocate prepare stmt; 600 601--echo ===================================================================== 602--echo Part 14: VIEW -> NOTHING transitions 603--echo ===================================================================== 604 605create table t2 (a int); 606create view t1 as select * from t2; 607 608prepare stmt from "select * from t1"; 609execute stmt; 610drop view t1; 611 612--error ER_NO_SUCH_TABLE 613execute stmt; 614call p_verify_reprepare_count(0); 615--error ER_NO_SUCH_TABLE 616execute stmt; 617call p_verify_reprepare_count(0); 618 619drop table t2; 620deallocate prepare stmt; 621 622--echo ===================================================================== 623--echo Part 15: VIEW -> TABLE transitions 624--echo ===================================================================== 625 626create table t2 (a int); 627create view t1 as select * from t2; 628 629prepare stmt from "select * from t1"; 630execute stmt; 631call p_verify_reprepare_count(0); 632 633drop view t1; 634create table t1 (a int); 635 636execute stmt; 637call p_verify_reprepare_count(1); 638 639drop table t2; 640drop table t1; 641deallocate prepare stmt; 642 643--echo ===================================================================== 644--echo Part 16: VIEW -> TEMPORARY TABLE transitions 645--echo ===================================================================== 646 647--echo # 648--echo # Test 1: Merged view 649--echo # 650create table t2 (a int); 651insert into t2 (a) values (1); 652create view t1 as select * from t2; 653 654prepare stmt from "select * from t1"; 655execute stmt; 656call p_verify_reprepare_count(0); 657 658create temporary table t1 (a int); 659# t1 still refers to the view - no reprepare has been done. 660execute stmt; 661call p_verify_reprepare_count(0); 662 663drop view t1; 664# t1 still refers to the, now deleted, view - no reprepare has been done. 665--error ER_NO_SUCH_TABLE 666execute stmt; 667call p_verify_reprepare_count(0); 668 669drop table t2; 670drop temporary table t1; 671deallocate prepare stmt; 672 673--echo # 674--echo # Test 2: Materialized view 675--echo # 676create table t2 (a int); 677insert into t2 (a) values (1); 678create algorithm = temptable view t1 as select * from t2; 679 680prepare stmt from "select * from t1"; 681execute stmt; 682call p_verify_reprepare_count(0); 683 684create temporary table t1 (a int); 685# t1 still refers to the view - no reprepare has been done. 686execute stmt; 687call p_verify_reprepare_count(0); 688 689drop view t1; 690# t1 still refers to the, now deleted, view - no reprepare has been done. 691--error ER_NO_SUCH_TABLE 692execute stmt; 693call p_verify_reprepare_count(0); 694 695drop table t2; 696drop temporary table t1; 697deallocate prepare stmt; 698 699--echo # 700--echo # Test 3: View referencing an Information schema table 701--echo # 702create view t1 as select table_name from information_schema.views order by table_name; 703 704prepare stmt from "select * from t1"; 705execute stmt; 706call p_verify_reprepare_count(0); 707 708create temporary table t1 (a int); 709# t1 has been substituted with a reference to the IS table 710execute stmt; 711call p_verify_reprepare_count(0); 712 713drop view t1; 714--error 1146 715execute stmt; 716call p_verify_reprepare_count(0); 717 718drop temporary table t1; 719deallocate prepare stmt; 720 721--echo ===================================================================== 722--echo Part 17: VIEW -> VIEW (DDL) transitions 723--echo ===================================================================== 724 725create table t2 (a int); 726insert into t2 values (10), (20), (30); 727 728create view t1 as select a, 2*a as b, 3*a as c from t2; 729select * from t1; 730 731prepare stmt from "select * from t1"; 732execute stmt; 733 734drop view t1; 735create view t1 as select a, 2*a as b, 5*a as c from t2; 736select * from t1; 737 738--echo # This is actually a test case for Bug#11748352 (36002 Prepared 739--echo # statements: if a view used in a statement is replaced, bad data). 740execute stmt; 741call p_verify_reprepare_count(1); 742 743flush table t2; 744 745execute stmt; 746call p_verify_reprepare_count(1); 747 748--echo # Check that we properly handle ALTER VIEW statements. 749execute stmt; 750call p_verify_reprepare_count(0); 751alter view t1 as select a, 3*a as b, 4*a as c from t2; 752execute stmt; 753call p_verify_reprepare_count(1); 754execute stmt; 755call p_verify_reprepare_count(0); 756execute stmt; 757call p_verify_reprepare_count(0); 758select * from t1; 759 760--echo # Check that DROP & CREATE is properly handled under LOCK TABLES. 761drop view t1; 762flush tables; # empty TDC 763create view t1 as select a, 5*a as b, 6*a as c from t2; 764lock tables t1 read, t2 read; 765execute stmt; 766call p_verify_reprepare_count(1); 767execute stmt; 768call p_verify_reprepare_count(0); 769execute stmt; 770call p_verify_reprepare_count(0); 771unlock tables; 772--echo # ... and once again... 773drop view t1; 774create view t1 as select a, 6*a as b, 7*a as c from t2; 775lock tables t1 read, t2 read; 776execute stmt; 777call p_verify_reprepare_count(1); 778execute stmt; 779call p_verify_reprepare_count(0); 780execute stmt; 781call p_verify_reprepare_count(0); 782unlock tables; 783 784--echo # Check that ALTER VIEW is properly handled under LOCK TABLES. 785alter view t1 as select a, 7*a as b, 8*a as c from t2; 786lock tables t1 read, t2 read; 787execute stmt; 788call p_verify_reprepare_count(1); 789execute stmt; 790call p_verify_reprepare_count(0); 791execute stmt; 792call p_verify_reprepare_count(0); 793unlock tables; 794 795drop table t2; 796drop view t1; 797deallocate prepare stmt; 798 799--echo # Check that DROP & CREATE is properly handled under LOCK TABLES when 800--echo # LOCK TABLES does not contain the complete set of views. 801 802create table t1(a int); 803insert into t1 values (1), (2), (3); 804 805create view v1 as select a from t1; 806 807lock tables t1 read, v1 read; 808 809prepare stmt from 'select * from v1'; 810 811execute stmt; 812call p_verify_reprepare_count(0); 813 814execute stmt; 815call p_verify_reprepare_count(0); 816 817unlock tables; 818 819drop view v1; 820create view v1 as select 2*a from t1; 821 822# Miss v1. 823lock tables t1 read; 824 825--error ER_TABLE_NOT_LOCKED 826execute stmt; 827 828unlock tables; 829 830drop table t1; 831drop view v1; 832deallocate prepare stmt; 833 834--echo # Check that ALTER VIEW is properly handled under LOCK TABLES when 835--echo # LOCK TABLES does not contain the complete set of views. 836 837create table t1(a int); 838insert into t1 values (1), (2), (3); 839 840create view v1 as select a from t1; 841 842lock tables t1 read, v1 read; 843 844prepare stmt from 'select * from v1'; 845 846execute stmt; 847call p_verify_reprepare_count(0); 848 849execute stmt; 850call p_verify_reprepare_count(0); 851 852unlock tables; 853 854alter view v1 as select 2*a from t1; 855 856# Miss v1. 857lock tables t1 read; 858 859--error ER_TABLE_NOT_LOCKED 860execute stmt; 861 862unlock tables; 863 864drop table t1; 865drop view v1; 866deallocate prepare stmt; 867 868--echo ===================================================================== 869--echo Part 18: VIEW -> VIEW (VIEW dependencies) transitions 870--echo ===================================================================== 871 872--echo # Part 18a: dependent function has changed 873create table t1 (a int); 874insert into t1 (a) values (1), (2), (3); 875create function f1() returns int return (select max(a) from t1); 876create view v1 as select f1(); 877prepare stmt from "select * from v1"; 878execute stmt; 879execute stmt; 880call p_verify_reprepare_count(0); 881drop function f1; 882create function f1() returns int return 2; 883--echo # XXX: Used to be another manifestation of Bug#12093. 884--echo # We only used to get a different error 885--echo # message because the non-existing procedure error is masked 886--echo # by the view. 887execute stmt; 888execute stmt; 889call p_verify_reprepare_count(1); 890 891--echo # Part 18b: dependent procedure has changed (referred to via a function) 892 893create table t2 (a int); 894insert into t2 (a) values (4), (5), (6); 895 896drop function f1; 897delimiter |; 898create function f1() returns int 899begin 900 declare x int; 901 call p1(x); 902 return x; 903end| 904delimiter ;| 905create procedure p1(out x int) select max(a) from t1 into x; 906 907prepare stmt from "select * from v1"; 908execute stmt; 909execute stmt; 910call p_verify_reprepare_count(0); 911drop procedure p1; 912create procedure p1(out x int) select max(a) from t2 into x; 913--echo # XXX: used to be a bug. The prelocked list was not invalidated 914--echo # and we kept opening table t1, whereas the procedure 915--echo # is now referring to table t2 916execute stmt; 917call p_verify_reprepare_count(1); 918flush table t1; 919execute stmt; 920call p_verify_reprepare_count(0); 921execute stmt; 922 923--echo # Test 18-c: dependent VIEW has changed 924 925drop view v1; 926create view v2 as select a from t1; 927create view v1 as select * from v2; 928prepare stmt from "select * from v1"; 929execute stmt; 930execute stmt; 931call p_verify_reprepare_count(0); 932drop view v2; 933create view v2 as select a from t2; 934execute stmt; 935execute stmt; 936call p_verify_reprepare_count(1); 937flush table t1; 938execute stmt; 939call p_verify_reprepare_count(0); 940execute stmt; 941--echo # Test 18-d: dependent TABLE has changed 942drop view v2; 943create table v2 as select * from t1; 944execute stmt; 945call p_verify_reprepare_count(1); 946execute stmt; 947call p_verify_reprepare_count(0); 948drop table v2; 949create table v2 (a int unique) as select * from t2; 950execute stmt; 951call p_verify_reprepare_count(1); 952execute stmt; 953call p_verify_reprepare_count(0); 954 955--echo # Test 18-e: dependent TABLE trigger has changed 956 957prepare stmt from "insert into v1 (a) values (?)"; 958set @var= 7; 959execute stmt using @var; 960call p_verify_reprepare_count(0); 961create trigger v2_bi before insert on v2 for each row set @message="v2_bi"; 962set @var=8; 963execute stmt using @var; 964call p_verify_reprepare_count(1); 965select @message; 966drop trigger v2_bi; 967set @message=null; 968set @var=9; 969execute stmt using @var; 970call p_verify_reprepare_count(1); 971select @message; 972create trigger v2_bi after insert on v2 for each row set @message="v2_ai"; 973set @var= 10; 974execute stmt using @var; 975call p_verify_reprepare_count(1); 976select @message; 977select * from v1; 978 979--echo # Cleanup 980 981--disable_warnings 982drop table if exists t1, t2, v1, v2; 983drop view if exists v1, v2; 984drop function f1; 985drop procedure p1; 986--enable_warnings 987deallocate prepare stmt; 988 989--echo ===================================================================== 990--echo Part 19: Special tables (INFORMATION_SCHEMA) 991--echo ===================================================================== 992 993# Using a temporary table internally should not confuse the prepared 994# statement code, and should not raise ER_PS_INVALIDATED errors 995prepare stmt from 996 "select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE 997 from INFORMATION_SCHEMA.ROUTINES where 998 routine_name='p1'"; 999 1000create procedure p1() select "hi there"; 1001 1002execute stmt; 1003execute stmt; 1004 1005drop procedure p1; 1006create procedure p1() select "hi there, again"; 1007 1008execute stmt; 1009execute stmt; 1010call p_verify_reprepare_count(0); 1011 1012drop procedure p1; 1013deallocate prepare stmt; 1014 1015--echo ===================================================================== 1016--echo Part 20: Special tables (log tables) 1017--echo ===================================================================== 1018 1019prepare stmt from 1020 "select * from mysql.general_log where argument='IMPOSSIBLE QUERY STRING'"; 1021 1022--disable_result_log 1023execute stmt; 1024execute stmt; 1025execute stmt; 1026execute stmt; 1027--enable_result_log 1028call p_verify_reprepare_count(0); 1029deallocate prepare stmt; 1030 1031--echo ===================================================================== 1032--echo Part 21: Special tables (system tables) 1033--echo ===================================================================== 1034 1035prepare stmt from 1036 "select type, db, name from mysql.proc where name='p1'"; 1037 1038create procedure p1() select "hi there"; 1039 1040execute stmt; 1041execute stmt; 1042 1043drop procedure p1; 1044create procedure p1() select "hi there, again"; 1045 1046execute stmt; 1047execute stmt; 1048call p_verify_reprepare_count(0); 1049 1050drop procedure p1; 1051deallocate prepare stmt; 1052 1053--echo ===================================================================== 1054--echo Part 22: Special tables (views temp tables) 1055--echo ===================================================================== 1056 1057create table t1 (a int); 1058 1059create algorithm=temptable view v1 as select a*a as a2 from t1; 1060 1061--echo # Using a temporary table internally should not confuse the prepared 1062--echo # statement code, and should not raise ER_PS_INVALIDATED errors 1063show create view v1; 1064 1065prepare stmt from "select * from v1"; 1066 1067insert into t1 values (1), (2), (3); 1068execute stmt; 1069execute stmt; 1070 1071insert into t1 values (4), (5), (6); 1072execute stmt; 1073execute stmt; 1074call p_verify_reprepare_count(0); 1075 1076drop table t1; 1077drop view v1; 1078 1079--echo ===================================================================== 1080--echo Part 23: Special statements 1081--echo ===================================================================== 1082 1083--echo # SQLCOM_ALTER_TABLE: 1084 1085 1086create table t1 (a int); 1087 1088prepare stmt from "alter table t1 add column b int"; 1089execute stmt; 1090 1091drop table t1; 1092create table t1 (a1 int, a2 int); 1093 1094--echo # t1 has changed, and it's does not lead to reprepare 1095execute stmt; 1096 1097alter table t1 drop column b; 1098execute stmt; 1099 1100alter table t1 drop column b; 1101execute stmt; 1102call p_verify_reprepare_count(0); 1103 1104drop table t1; 1105 1106--echo # SQLCOM_REPAIR: 1107 1108create table t1 (a int); 1109 1110insert into t1 values (1), (2), (3); 1111 1112prepare stmt from "repair table t1"; 1113 1114execute stmt; 1115execute stmt; 1116 1117drop table t1; 1118create table t1 (a1 int, a2 int); 1119insert into t1 values (1, 10), (2, 20), (3, 30); 1120 1121--echo # t1 has changed, and it's does not lead to reprepare 1122execute stmt; 1123 1124alter table t1 add column b varchar(50) default NULL; 1125execute stmt; 1126call p_verify_reprepare_count(0); 1127 1128alter table t1 drop column b; 1129execute stmt; 1130call p_verify_reprepare_count(0); 1131 1132--echo # SQLCOM_ANALYZE: 1133 1134prepare stmt from "analyze table t1"; 1135execute stmt; 1136 1137drop table t1; 1138create table t1 (a1 int, a2 int); 1139insert into t1 values (1, 10), (2, 20), (3, 30); 1140--echo # t1 has changed, and it's not a problem 1141execute stmt; 1142 1143alter table t1 add column b varchar(50) default NULL; 1144execute stmt; 1145 1146alter table t1 drop column b; 1147execute stmt; 1148 1149call p_verify_reprepare_count(0); 1150 1151--echo # SQLCOM_OPTIMIZE: 1152 1153prepare stmt from "optimize table t1"; 1154execute stmt; 1155 1156drop table t1; 1157create table t1 (a1 int, a2 int); 1158insert into t1 values (1, 10), (2, 20), (3, 30); 1159 1160--echo # t1 has changed, and it's not a problem 1161execute stmt; 1162 1163alter table t1 add column b varchar(50) default NULL; 1164execute stmt; 1165 1166alter table t1 drop column b; 1167execute stmt; 1168call p_verify_reprepare_count(0); 1169 1170drop table t1; 1171 1172--echo # SQLCOM_SHOW_CREATE_PROC: 1173 1174prepare stmt from "show create procedure p1"; 1175--error ER_SP_DOES_NOT_EXIST 1176execute stmt; 1177--error ER_SP_DOES_NOT_EXIST 1178execute stmt; 1179 1180create procedure p1() begin end; 1181 1182--disable_result_log 1183execute stmt; 1184execute stmt; 1185--enable_result_log 1186 1187drop procedure p1; 1188create procedure p1(x int, y int) begin end; 1189 1190--disable_result_log 1191execute stmt; 1192execute stmt; 1193--enable_result_log 1194 1195drop procedure p1; 1196 1197--error ER_SP_DOES_NOT_EXIST 1198execute stmt; 1199--error ER_SP_DOES_NOT_EXIST 1200execute stmt; 1201call p_verify_reprepare_count(0); 1202 1203--echo # SQLCOM_SHOW_CREATE_FUNC: 1204 1205prepare stmt from "show create function f1"; 1206--error ER_SP_DOES_NOT_EXIST 1207execute stmt; 1208--error ER_SP_DOES_NOT_EXIST 1209execute stmt; 1210 1211create function f1() returns int return 0; 1212 1213--disable_result_log 1214execute stmt; 1215execute stmt; 1216--enable_result_log 1217 1218drop function f1; 1219create function f1(x int, y int) returns int return x+y; 1220 1221--disable_result_log 1222execute stmt; 1223execute stmt; 1224--enable_result_log 1225 1226drop function f1; 1227 1228--error ER_SP_DOES_NOT_EXIST 1229execute stmt; 1230--error ER_SP_DOES_NOT_EXIST 1231execute stmt; 1232call p_verify_reprepare_count(0); 1233 1234--echo # SQLCOM_SHOW_CREATE_TRIGGER: 1235 1236create table t1 (a int); 1237 1238prepare stmt from "show create trigger t1_bi"; 1239--error ER_TRG_DOES_NOT_EXIST 1240execute stmt; 1241--error ER_TRG_DOES_NOT_EXIST 1242execute stmt; 1243 1244create trigger t1_bi before insert on t1 for each row set @message= "t1_bi"; 1245 1246--disable_result_log 1247execute stmt; 1248execute stmt; 1249--enable_result_log 1250 1251drop trigger t1_bi; 1252 1253create trigger t1_bi before insert on t1 for each row set @message= "t1_bi (2)"; 1254 1255--disable_result_log 1256execute stmt; 1257execute stmt; 1258--enable_result_log 1259 1260drop trigger t1_bi; 1261 1262--error ER_TRG_DOES_NOT_EXIST 1263execute stmt; 1264--error ER_TRG_DOES_NOT_EXIST 1265execute stmt; 1266call p_verify_reprepare_count(0); 1267 1268drop table t1; 1269deallocate prepare stmt; 1270 1271--echo ===================================================================== 1272--echo Part 24: Testing the strength of TABLE_SHARE version 1273--echo ===================================================================== 1274 1275--echo # Test 24-a: number of columns 1276 1277create table t1 (a int); 1278 1279prepare stmt from "select a from t1"; 1280execute stmt; 1281call p_verify_reprepare_count(0); 1282 1283alter table t1 add column b varchar(50) default NULL; 1284 1285execute stmt; 1286call p_verify_reprepare_count(1); 1287execute stmt; 1288call p_verify_reprepare_count(0); 1289 1290--echo # Test 24-b: column name 1291 1292alter table t1 change b c int; 1293execute stmt; 1294call p_verify_reprepare_count(1); 1295execute stmt; 1296call p_verify_reprepare_count(0); 1297 1298--echo # Test 24-c: column type 1299 1300alter table t1 change a a varchar(10); 1301 1302execute stmt; 1303call p_verify_reprepare_count(1); 1304execute stmt; 1305call p_verify_reprepare_count(0); 1306 1307--echo # Test 24-d: column type length 1308 1309alter table t1 change a a varchar(20); 1310 1311execute stmt; 1312call p_verify_reprepare_count(1); 1313execute stmt; 1314call p_verify_reprepare_count(0); 1315 1316--echo # Test 24-e: column NULL property 1317 1318alter table t1 change a a varchar(20) NOT NULL; 1319 1320execute stmt; 1321call p_verify_reprepare_count(1); 1322execute stmt; 1323call p_verify_reprepare_count(0); 1324 1325--echo # Test 24-f: column DEFAULT 1326 1327alter table t1 change c c int DEFAULT 20; 1328 1329execute stmt; 1330call p_verify_reprepare_count(1); 1331execute stmt; 1332call p_verify_reprepare_count(0); 1333 1334--echo # Test 24-g: number of keys 1335create unique index t1_a_idx on t1 (a); 1336 1337execute stmt; 1338call p_verify_reprepare_count(1); 1339execute stmt; 1340call p_verify_reprepare_count(0); 1341 1342--echo # Test 24-h: changing index uniqueness 1343 1344drop index t1_a_idx on t1; 1345create index t1_a_idx on t1 (a); 1346 1347execute stmt; 1348call p_verify_reprepare_count(1); 1349execute stmt; 1350call p_verify_reprepare_count(0); 1351 1352--echo # Cleanup 1353drop table t1; 1354 1355deallocate prepare stmt; 1356 1357--echo ===================================================================== 1358--echo Testing reported bugs 1359--echo ===================================================================== 1360 1361--echo # 1362--echo # Bug#27420 A combination of PS and view operations cause 1363--echo # error + assertion on shutdown 1364--echo # 1365 1366--disable_warnings 1367drop table if exists t_27420_100; 1368drop table if exists t_27420_101; 1369drop view if exists v_27420; 1370--enable_warnings 1371 1372create table t_27420_100(a int); 1373insert into t_27420_100 values (1), (2); 1374 1375create table t_27420_101(a int); 1376insert into t_27420_101 values (1), (2); 1377 1378create view v_27420 as select t_27420_100.a X, t_27420_101.a Y 1379 from t_27420_100, t_27420_101 1380 where t_27420_100.a=t_27420_101.a; 1381 1382prepare stmt from "select * from v_27420"; 1383 1384execute stmt; 1385call p_verify_reprepare_count(0); 1386 1387drop view v_27420; 1388create table v_27420(X int, Y int); 1389 1390execute stmt; 1391call p_verify_reprepare_count(1); 1392 1393drop table v_27420; 1394# passes in 5.0, fails in 5.1, should pass 1395create table v_27420 (a int, b int, filler char(200)); 1396 1397execute stmt; 1398call p_verify_reprepare_count(1); 1399 1400drop table t_27420_100; 1401drop table t_27420_101; 1402drop table v_27420; 1403deallocate prepare stmt; 1404 1405--echo # 1406--echo # Bug#27430 Crash in subquery code when in PS and table DDL changed 1407--echo # after PREPARE 1408--echo # 1409 1410--disable_warnings 1411drop table if exists t_27430_1; 1412drop table if exists t_27430_2; 1413--enable_warnings 1414 1415create table t_27430_1 (a int not null, oref int not null, key(a)); 1416insert into t_27430_1 values 1417 (1, 1), 1418 (1, 1234), 1419 (2, 3), 1420 (2, 1234), 1421 (3, 1234); 1422 1423create table t_27430_2 (a int not null, oref int not null); 1424insert into t_27430_2 values 1425 (1, 1), 1426 (2, 2), 1427 (1234, 3), 1428 (1234, 4); 1429 1430prepare stmt from 1431 "select oref, a, a in (select a from t_27430_1 where oref=t_27430_2.oref) Z from t_27430_2"; 1432 1433execute stmt; 1434call p_verify_reprepare_count(0); 1435 1436drop table t_27430_1, t_27430_2; 1437 1438create table t_27430_1 (a int, oref int, key(a)); 1439insert into t_27430_1 values 1440 (1, 1), 1441 (1, NULL), 1442 (2, 3), 1443 (2, NULL), 1444 (3, NULL); 1445 1446create table t_27430_2 (a int, oref int); 1447insert into t_27430_2 values 1448 (1, 1), 1449 (2,2), 1450 (NULL, 3), 1451 (NULL, 4); 1452 1453execute stmt; 1454call p_verify_reprepare_count(1); 1455 1456drop table t_27430_1; 1457drop table t_27430_2; 1458deallocate prepare stmt; 1459 1460--echo # 1461--echo # Bug#27690 Re-execution of prepared statement after table 1462--echo # was replaced with a view crashes 1463--echo # 1464 1465--disable_warnings 1466drop table if exists t_27690_1; 1467drop view if exists v_27690_1; 1468drop table if exists v_27690_2; 1469--enable_warnings 1470 1471create table t_27690_1 (a int, b int); 1472insert into t_27690_1 values (1,1),(2,2); 1473 1474create table v_27690_1 as select * from t_27690_1; 1475create table v_27690_2 as select * from t_27690_1; 1476 1477prepare stmt from "select * from v_27690_1, v_27690_2"; 1478 1479execute stmt; 1480execute stmt; 1481 1482drop table v_27690_1; 1483 1484--error ER_NO_SUCH_TABLE 1485execute stmt; 1486 1487--error ER_NO_SUCH_TABLE 1488execute stmt; 1489call p_verify_reprepare_count(0); 1490 1491create view v_27690_1 as select A.a, A.b from t_27690_1 A, t_27690_1 B; 1492 1493execute stmt; 1494call p_verify_reprepare_count(1); 1495execute stmt; 1496call p_verify_reprepare_count(0); 1497 1498drop table t_27690_1; 1499drop view v_27690_1; 1500drop table v_27690_2; 1501deallocate prepare stmt; 1502 1503--echo #===================================================================== 1504--echo # 1505--echo # Bug#21294 Executing a prepared statement that executes 1506--echo # a stored function which was recreat 1507--echo # 1508 1509create function f1() returns int return 10; 1510 1511prepare stmt from "select f1()"; 1512execute stmt; 1513 1514drop function f1; 1515create function f1() returns int return 10; 1516 1517# might pass or fail, implementation dependent 1518execute stmt; 1519 1520drop function f1; 1521create function f1() returns int return 20; 1522 1523execute stmt; 1524call p_verify_reprepare_count(2); 1525 1526drop function f1; 1527deallocate prepare stmt; 1528 1529--echo # 1530--echo # Bug#12093 SP not found on second PS execution if another thread drops 1531--echo # other SP in between 1532--echo # 1533--disable_warnings 1534drop table if exists t_12093; 1535drop function if exists f_12093; 1536drop function if exists f_12093_unrelated; 1537drop procedure if exists p_12093; 1538drop view if exists v_12093_unrelated; 1539--enable_warnings 1540 1541create table t_12093 (a int); 1542create function f_12093() returns int return (select count(*) from t_12093); 1543create procedure p_12093(a int) select * from t_12093; 1544 1545create function f_12093_unrelated() returns int return 2; 1546create procedure p_12093_unrelated() begin end; 1547create view v_12093_unrelated as select * from t_12093; 1548 1549connect (con1,localhost,root,,); 1550connection default; 1551 1552let $my_drop = drop function f_12093_unrelated; 1553--source include/ps_ddl_1.inc 1554# 1555let $my_drop = drop procedure p_12093_unrelated; 1556--source include/ps_ddl_1.inc 1557# 1558# A reprepare of stmt_sf and stmt_sp is necessary because there is no 1559# information about views within the table definition cache. 1560let $my_drop = drop view v_12093_unrelated; 1561--source include/ps_ddl_1.inc 1562 1563call p_verify_reprepare_count(6); 1564 1565disconnect con1; 1566drop table t_12093; 1567drop function f_12093; 1568drop procedure p_12093; 1569deallocate prepare stmt_sf; 1570deallocate prepare stmt_sp; 1571 1572 1573--echo ===================================================================== 1574--echo Ensure that metadata validation is performed for every type of 1575--echo SQL statement where it is needed. 1576--echo ===================================================================== 1577 1578--echo # 1579--echo # SQLCOM_SELECT 1580--echo # 1581 1582--disable_warnings 1583drop table if exists t1; 1584--enable_warnings 1585create table t1 (a int); 1586prepare stmt from "select 1 as res from dual where (1) in (select * from t1)"; 1587drop table t1; 1588create table t1 (x int); 1589execute stmt; 1590drop table t1; 1591deallocate prepare stmt; 1592call p_verify_reprepare_count(1); 1593 1594--echo # 1595--echo # SQLCOM_CREATE_TABLE 1596--echo # 1597 1598--disable_warnings 1599drop table if exists t1; 1600drop table if exists t2; 1601--enable_warnings 1602create table t1 (a int); 1603prepare stmt from 'create table t2 as select * from t1'; 1604execute stmt; 1605drop table t2; 1606execute stmt; 1607drop table t2; 1608execute stmt; 1609call p_verify_reprepare_count(0); 1610# Base table with name of table to be created exists 1611--error ER_TABLE_EXISTS_ERROR 1612execute stmt; 1613call p_verify_reprepare_count(0); 1614--error ER_TABLE_EXISTS_ERROR 1615execute stmt; 1616call p_verify_reprepare_count(0); 1617drop table t2; 1618# Temporary table with name of table to be created exists 1619create temporary table t2 (a int); 1620# Temporary table and base table are not in the same name space. 1621execute stmt; 1622call p_verify_reprepare_count(0); 1623--error ER_TABLE_EXISTS_ERROR 1624execute stmt; 1625call p_verify_reprepare_count(0); 1626drop temporary table t2; 1627--error ER_TABLE_EXISTS_ERROR 1628execute stmt; 1629call p_verify_reprepare_count(0); 1630drop table t2; 1631execute stmt; 1632call p_verify_reprepare_count(0); 1633drop table t2; 1634# View with name of table to be created exists 1635# Attention: 1636# We cannot print the error message because it contains a random filename. 1637# Example: 1050: Table '<some_path>/var/tmp/#sql_6979_0' already exists 1638# Therefore we mangle it via 1639# "--error ER_TABLE_EXISTS_ERROR,9999" (9999 is currently not used) 1640# to "Got one of the listed errors". 1641create view t2 as select 1; 1642--error ER_TABLE_EXISTS_ERROR,9999 1643execute stmt; 1644call p_verify_reprepare_count(0); 1645--error ER_TABLE_EXISTS_ERROR,9999 1646execute stmt; 1647call p_verify_reprepare_count(0); 1648drop view t2; 1649drop table t1; 1650# Table to be used recreated (drop,create) with different layout 1651create table t1 (x varchar(20)); 1652execute stmt; 1653call p_verify_reprepare_count(1); 1654select * from t2; 1655drop table t2; 1656execute stmt; 1657call p_verify_reprepare_count(0); 1658drop table t2; 1659# Table to be used has a modified (alter table) layout 1660alter table t1 add column y decimal(10,3); 1661execute stmt; 1662call p_verify_reprepare_count(1); 1663select * from t2; 1664drop table t2; 1665execute stmt; 1666call p_verify_reprepare_count(0); 1667drop table t1; 1668deallocate prepare stmt; 1669create table t1 (a int); 1670insert into t1 (a) values (1); 1671prepare stmt from "create temporary table if not exists t2 as select * from t1"; 1672execute stmt; 1673drop table t2; 1674execute stmt; 1675call p_verify_reprepare_count(0); 1676execute stmt; 1677call p_verify_reprepare_count(1); 1678select * from t2; 1679execute stmt; 1680call p_verify_reprepare_count(0); 1681select * from t2; 1682drop table t2; 1683create temporary table t2 (a varchar(10)); 1684execute stmt; 1685select * from t2; 1686call p_verify_reprepare_count(1); 1687drop table t1; 1688create table t1 (x int); 1689execute stmt; 1690call p_verify_reprepare_count(1); 1691execute stmt; 1692call p_verify_reprepare_count(0); 1693drop table t1; 1694drop temporary table t2; 1695drop table t2; 1696deallocate prepare stmt; 1697 1698create table t1 (a int); 1699prepare stmt from "create table t2 like t1"; 1700execute stmt; 1701call p_verify_reprepare_count(0); 1702drop table t2; 1703execute stmt; 1704call p_verify_reprepare_count(0); 1705drop table t2; 1706# Table to be used does not exist 1707drop table t1; 1708--error ER_NO_SUCH_TABLE 1709execute stmt; 1710call p_verify_reprepare_count(0); 1711--error ER_NO_SUCH_TABLE 1712execute stmt; 1713call p_verify_reprepare_count(0); 1714# Table to be used recreated (drop,create) with different layout 1715create table t1 (x char(17)); 1716execute stmt; 1717call p_verify_reprepare_count(1); 1718drop table t2; 1719execute stmt; 1720call p_verify_reprepare_count(0); 1721drop table t2; 1722# Table to be used has a modified (alter table) layout 1723alter table t1 add column y time; 1724execute stmt; 1725call p_verify_reprepare_count(1); 1726select * from t2; 1727drop table t2; 1728execute stmt; 1729call p_verify_reprepare_count(0); 1730drop table t1; 1731drop table t2; 1732deallocate prepare stmt; 1733 1734 1735--echo # 1736--echo # SQLCOM_UPDATE 1737--echo # 1738 1739--disable_warnings 1740drop table if exists t1, t2; 1741--enable_warnings 1742create table t1 (a int); 1743create table t2 (a int); 1744prepare stmt from "update t2 set a=a+1 where (1) in (select * from t1)"; 1745execute stmt; 1746drop table t1; 1747create table t1 (x int); 1748execute stmt; 1749drop table t1, t2; 1750deallocate prepare stmt; 1751 1752--echo # 1753--echo # SQLCOM_INSERT 1754--echo # 1755 1756--disable_warnings 1757drop table if exists t1, t2; 1758--enable_warnings 1759create table t1 (a int); 1760create table t2 (a int); 1761prepare stmt from "insert into t2 set a=((1) in (select * from t1))"; 1762execute stmt; 1763drop table t1; 1764create table t1 (x int); 1765execute stmt; 1766 1767drop table t1, t2; 1768deallocate prepare stmt; 1769 1770--echo # 1771--echo # SQLCOM_INSERT_SELECT 1772--echo # 1773 1774--disable_warnings 1775drop table if exists t1, t2; 1776--enable_warnings 1777create table t1 (a int); 1778create table t2 (a int); 1779prepare stmt from "insert into t2 select * from t1"; 1780execute stmt; 1781drop table t1; 1782create table t1 (x int); 1783execute stmt; 1784drop table t1, t2; 1785deallocate prepare stmt; 1786 1787--echo # 1788--echo # SQLCOM_REPLACE 1789--echo # 1790 1791--disable_warnings 1792drop table if exists t1, t2; 1793--enable_warnings 1794create table t1 (a int); 1795create table t2 (a int); 1796prepare stmt from "replace t2 set a=((1) in (select * from t1))"; 1797execute stmt; 1798drop table t1; 1799create table t1 (x int); 1800execute stmt; 1801drop table t1, t2; 1802deallocate prepare stmt; 1803 1804--echo # 1805--echo # SQLCOM_REPLACE_SELECT 1806--echo # 1807 1808--disable_warnings 1809drop table if exists t1, t2; 1810--enable_warnings 1811create table t1 (a int); 1812create table t2 (a int); 1813prepare stmt from "replace t2 select * from t1"; 1814execute stmt; 1815drop table t1; 1816create table t1 (x int); 1817execute stmt; 1818drop table t1, t2; 1819deallocate prepare stmt; 1820 1821--echo # 1822--echo # SQLCOM_DELETE 1823--echo # 1824 1825--disable_warnings 1826drop table if exists t1, t2; 1827--enable_warnings 1828create table t1 (a int); 1829create table t2 (a int); 1830prepare stmt from "delete from t2 where (1) in (select * from t1)"; 1831execute stmt; 1832drop table t1; 1833create table t1 (x int); 1834execute stmt; 1835drop table t1, t2; 1836deallocate prepare stmt; 1837 1838--echo # 1839--echo # SQLCOM_DELETE_MULTI 1840--echo # 1841 1842--disable_warnings 1843drop table if exists t1, t2, t3; 1844--enable_warnings 1845create table t1 (a int); 1846create table t2 (a int); 1847create table t3 (a int); 1848prepare stmt from "delete t2, t3 from t2, t3 where (1) in (select * from t1)"; 1849execute stmt; 1850drop table t1; 1851create table t1 (x int); 1852execute stmt; 1853drop table t1, t2, t3; 1854deallocate prepare stmt; 1855 1856--echo # 1857--echo # SQLCOM_UPDATE_MULTI 1858--echo # 1859 1860--disable_warnings 1861drop table if exists t1, t2, t3; 1862--enable_warnings 1863create table t1 (a int); 1864create table t2 (a int); 1865create table t3 (a int); 1866prepare stmt from "update t2, t3 set t3.a=t2.a, t2.a=null where (1) in (select * from t1)"; 1867drop table t1; 1868create table t1 (x int); 1869execute stmt; 1870drop table t1, t2, t3; 1871deallocate prepare stmt; 1872--echo # Intermediate results: 8 SQLCOMs tested, 8 automatic reprepares 1873call p_verify_reprepare_count(8); 1874 1875--echo # 1876--echo # SQLCOM_LOAD 1877--echo # 1878 1879--disable_warnings 1880drop table if exists t1; 1881--enable_warnings 1882create table t1 (a varchar(20)); 1883--error ER_UNSUPPORTED_PS 1884prepare stmt from "load data infile '../std_data_ln/words.dat' into table t1"; 1885drop table t1; 1886 1887--echo # 1888--echo # SQLCOM_SHOW_DATABASES 1889--echo # 1890 1891--disable_warnings 1892drop table if exists t1; 1893--enable_warnings 1894create table t1 (a int); 1895prepare stmt from "show databases where (1) in (select * from t1)"; 1896execute stmt; 1897drop table t1; 1898create table t1 (x int); 1899execute stmt; 1900drop table t1; 1901deallocate prepare stmt; 1902 1903--echo # 1904--echo # SQLCOM_SHOW_TABLES 1905--echo # 1906 1907--disable_warnings 1908drop table if exists t1; 1909--enable_warnings 1910create table t1 (a int); 1911prepare stmt from "show tables where (1) in (select * from t1)"; 1912execute stmt; 1913drop table t1; 1914create table t1 (x int); 1915execute stmt; 1916drop table t1; 1917deallocate prepare stmt; 1918 1919--echo # 1920--echo # SQLCOM_SHOW_FIELDS 1921--echo # 1922 1923--disable_warnings 1924drop table if exists t1; 1925--enable_warnings 1926create table t1 (a int); 1927prepare stmt from "show fields from t1 where (1) in (select * from t1)"; 1928execute stmt; 1929drop table t1; 1930create table t1 (x int); 1931execute stmt; 1932drop table t1; 1933deallocate prepare stmt; 1934 1935--echo # 1936--echo # SQLCOM_SHOW_KEYS 1937--echo # 1938 1939--disable_warnings 1940drop table if exists t1; 1941--enable_warnings 1942create table t1 (a int); 1943prepare stmt from "show keys from t1 where (1) in (select * from t1)"; 1944execute stmt; 1945drop table t1; 1946create table t1 (x int); 1947execute stmt; 1948drop table t1; 1949deallocate prepare stmt; 1950 1951--echo # 1952--echo # SQLCOM_SHOW_VARIABLES 1953--echo # 1954 1955--disable_warnings 1956drop table if exists t1; 1957--enable_warnings 1958create table t1 (a int); 1959prepare stmt from "show variables where (1) in (select * from t1)"; 1960execute stmt; 1961drop table t1; 1962create table t1 (x int); 1963execute stmt; 1964drop table t1; 1965deallocate prepare stmt; 1966 1967--echo # 1968--echo # SQLCOM_SHOW_STATUS 1969--echo # 1970 1971--disable_warnings 1972drop table if exists t1; 1973--enable_warnings 1974create table t1 (a int); 1975prepare stmt from "show status where (1) in (select * from t1)"; 1976execute stmt; 1977drop table t1; 1978create table t1 (x int); 1979execute stmt; 1980drop table t1; 1981deallocate prepare stmt; 1982 1983--echo # 1984--echo # SQLCOM_SHOW_ENGINE_STATUS, SQLCOM_SHOW_ENGINE_LOGS, 1985--echo # SQLCOM_SHOW_ENGINE_MUTEX, SQLCOM_SHOW_PROCESSLIST 1986--echo # 1987 1988--echo # Currently can not have a where clause, need to be covered 1989--echo # with tests 1990 1991--disable_warnings 1992drop table if exists t1; 1993--enable_warnings 1994create table t1 (a int); 1995--error ER_PARSE_ERROR 1996prepare stmt from "show engine all status where (1) in (select * from t1)"; 1997--error ER_PARSE_ERROR 1998prepare stmt from "show engine all logs where (1) in (select * from t1)"; 1999--error ER_PARSE_ERROR 2000prepare stmt from "show engine all mutex where (1) in (select * from t1)"; 2001--error ER_PARSE_ERROR 2002prepare stmt from "show processlist where (1) in (select * from t1)"; 2003drop table t1; 2004 2005--echo # 2006--echo # SQLCOM_SHOW_CHARSETS 2007--echo # 2008 2009--disable_warnings 2010drop table if exists t1; 2011--enable_warnings 2012create table t1 (a int); 2013prepare stmt from "show charset where (1) in (select * from t1)"; 2014execute stmt; 2015drop table t1; 2016create table t1 (x int); 2017execute stmt; 2018drop table t1; 2019deallocate prepare stmt; 2020 2021--echo # 2022--echo # SQLCOM_SHOW_COLLATIONS 2023--echo # 2024 2025--disable_warnings 2026drop table if exists t1; 2027--enable_warnings 2028create table t1 (a int); 2029prepare stmt from "show collation where (1) in (select * from t1)"; 2030execute stmt; 2031drop table t1; 2032create table t1 (x int); 2033execute stmt; 2034drop table t1; 2035deallocate prepare stmt; 2036 2037--echo # 2038--echo # SQLCOM_SHOW_TABLE_STATUS 2039--echo # 2040 2041--disable_warnings 2042drop table if exists t1; 2043--enable_warnings 2044create table t1 (a int); 2045prepare stmt from "show table status where (1) in (select * from t1)"; 2046execute stmt; 2047drop table t1; 2048create table t1 (x int); 2049execute stmt; 2050drop table t1; 2051deallocate prepare stmt; 2052 2053--echo # 2054--echo # SQLCOM_SHOW_TRIGGERS 2055--echo # 2056 2057--disable_warnings 2058drop table if exists t1; 2059--enable_warnings 2060create table t1 (a int); 2061prepare stmt from "show triggers where (1) in (select * from t1)"; 2062execute stmt; 2063drop table t1; 2064create table t1 (x int); 2065execute stmt; 2066drop table t1; 2067deallocate prepare stmt; 2068 2069--echo # 2070--echo # SQLCOM_SHOW_OPEN_TABLES 2071--echo # 2072 2073--disable_warnings 2074drop table if exists t1; 2075--enable_warnings 2076create table t1 (a int); 2077prepare stmt from "show open tables where (1) in (select * from t1)"; 2078execute stmt; 2079drop table t1; 2080create table t1 (x int); 2081execute stmt; 2082drop table t1; 2083deallocate prepare stmt; 2084 2085--echo # 2086--echo # SQLCOM_SHOW_STATUS_PROC 2087--echo # 2088 2089--disable_warnings 2090drop table if exists t1; 2091--enable_warnings 2092create table t1 (a int); 2093prepare stmt from "show procedure status where (1) in (select * from t1)"; 2094execute stmt; 2095drop table t1; 2096create table t1 (x int); 2097execute stmt; 2098drop table t1; 2099deallocate prepare stmt; 2100 2101--echo # 2102--echo # SQLCOM_SHOW_STATUS_FUNC 2103--echo # 2104 2105--disable_warnings 2106drop table if exists t1; 2107--enable_warnings 2108create table t1 (a int); 2109prepare stmt from "show function status where (1) in (select * from t1)"; 2110execute stmt; 2111drop table t1; 2112create table t1 (x int); 2113execute stmt; 2114drop table t1; 2115deallocate prepare stmt; 2116 2117--echo # 2118--echo # SQLCOM_SHOW_EVENTS 2119--echo # 2120--echo # 2121--echo # Please see this test in ps.test, it requires not_embedded.inc 2122--echo # 2123 2124--echo # 2125--echo # SQLCOM_SET_OPTION 2126--echo # 2127 2128--disable_warnings 2129drop table if exists t1; 2130--enable_warnings 2131create table t1 (a int); 2132prepare stmt from "set @a=((1) in (select * from t1))"; 2133execute stmt; 2134drop table t1; 2135create table t1 (x int); 2136execute stmt; 2137drop table t1; 2138deallocate prepare stmt; 2139 2140--echo # 2141--echo # SQLCOM_DO 2142--echo # 2143 2144--disable_warnings 2145drop table if exists t1; 2146--enable_warnings 2147create table t1 (a int); 2148prepare stmt from "do ((1) in (select * from t1))"; 2149execute stmt; 2150drop table t1; 2151create table t1 (x int); 2152execute stmt; 2153drop table t1; 2154deallocate prepare stmt; 2155 2156--echo # 2157--echo # SQLCOM_CALL 2158--echo # 2159 2160--disable_warnings 2161drop table if exists t1; 2162drop procedure if exists p1; 2163--enable_warnings 2164create procedure p1(a int) begin end; 2165create table t1 (a int); 2166prepare stmt from "call p1((1) in (select * from t1))"; 2167execute stmt; 2168drop table t1; 2169create table t1 (x int); 2170execute stmt; 2171drop table t1; 2172drop procedure p1; 2173deallocate prepare stmt; 2174 2175--echo # 2176--echo # SQLCOM_CREATE_VIEW 2177--echo # 2178 2179--disable_warnings 2180drop table if exists t1; 2181drop view if exists v1; 2182--enable_warnings 2183create table t1 (a int); 2184prepare stmt from "create view v1 as select * from t1"; 2185execute stmt; 2186drop view v1; 2187drop table t1; 2188create table t1 (x int); 2189execute stmt; 2190drop view v1; 2191drop table t1; 2192deallocate prepare stmt; 2193--echo # Intermediate result: number of reprepares matches the number 2194--echo # of tests 2195call p_verify_reprepare_count(17); 2196 2197--echo # 2198--echo # SQLCOM_ALTER_VIEW 2199--echo # 2200 2201--disable_warnings 2202drop view if exists v1; 2203--enable_warnings 2204create view v1 as select 1; 2205--error ER_UNSUPPORTED_PS 2206prepare stmt from "alter view v1 as select 2"; 2207drop view v1; 2208 2209--echo # Cleanup 2210--echo # 2211--disable_warnings 2212drop temporary table if exists t1, t2, t3; 2213drop table if exists t1, t2, t3, v1, v2; 2214drop procedure if exists p_verify_reprepare_count; 2215drop procedure if exists p1; 2216drop function if exists f1; 2217drop view if exists v1, v2; 2218--enable_warnings 2219 2220 2221--echo # 2222--echo # Additional coverage for refactoring which was made as part of work 2223--echo # on bug '27480: Extend CREATE TEMPORARY TABLES privilege to allow 2224--echo # temp table operations'. 2225--echo # 2226--echo # Check that we don't try to pre-open temporary tables for the elements 2227--echo # from prelocking list, as this can lead to unwarranted ER_CANT_REOPEN 2228--echo # errors. 2229--disable_warnings ONCE 2230DROP TABLE IF EXISTS t1, tm; 2231CREATE TABLE t1 (a INT); 2232CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW 2233 SET @a:= (SELECT COUNT(*) FROM t1); 2234--echo # Prelocking list for the below statement should 2235--echo # contain t1 twice - once for the INSERT and once 2236--echo # SELECT from the trigger. 2237PREPARE stmt1 FROM 'INSERT INTO t1 VALUES (1)'; 2238EXECUTE stmt1; 2239--echo # Create temporary table which will shadow t1. 2240CREATE TEMPORARY TABLE t1 (b int); 2241--echo # The below execution of statement should not fail with ER_CANT_REOPEN 2242--echo # error. Instead stmt1 should be auto-matically reprepared and succeed. 2243EXECUTE stmt1; 2244DEALLOCATE PREPARE stmt1; 2245DROP TEMPORARY TABLE t1; 2246DROP TABLE t1; 2247--echo # 2248--echo # Also check that we properly reset table list elements from UNION 2249--echo # clause of CREATE TABLE and ALTER TABLE statements. 2250--echo # 2251CREATE TEMPORARY TABLE t1 (i INT); 2252PREPARE stmt2 FROM 'CREATE TEMPORARY TABLE tm (i INT) ENGINE=MERGE UNION=(t1)'; 2253EXECUTE stmt2; 2254DROP TEMPORARY TABLE tm; 2255EXECUTE stmt2; 2256DEALLOCATE PREPARE stmt2; 2257PREPARE stmt3 FROM 'ALTER TABLE tm UNION=(t1)'; 2258EXECUTE stmt3; 2259EXECUTE stmt3; 2260DEALLOCATE PREPARE stmt3; 2261DROP TEMPORARY TABLES tm, t1; 2262 2263--echo # 2264--echo # Start of 10.1 tests 2265--echo # 2266 2267--echo # 2268--echo # MDEV-10702 Crash in SET STATEMENT FOR EXECUTE 2269--echo # 2270CREATE TABLE t1 (a INT); 2271PREPARE stmt FROM 'INSERT INTO t1 VALUES (@@max_sort_length)'; 2272SET STATEMENT max_sort_length=2048 FOR EXECUTE stmt; 2273SELECT * FROM t1; 2274CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=NEW.a + 1; 2275SET STATEMENT max_sort_length=2048 FOR EXECUTE stmt; 2276SELECT * FROM t1; 2277DROP TRIGGER tr1; 2278SET STATEMENT max_sort_length=2048 FOR EXECUTE stmt; 2279SELECT * FROM t1; 2280DROP TABLE t1; 2281 2282 2283--echo # 2284--echo # End of 10.1 tests 2285--echo # 2286