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