47
47
# - Part 20: Special tables (log tables)
48
48
# - Part 21: Special tables (system tables)
49
49
# - Part 22: Special tables (views temp tables)
50
# - Part 23: Special tables (internal join tables)
51
# - Part 24: Special statements
52
# - Part 25: Testing the strength of TABLE_SHARE version
54
let $base_count = SELECT VARIABLE_VALUE from
55
INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE'
58
let $check = SELECT CASE (VARIABLE_VALUE - @base_count - @expected)
63
(VARIABLE_VALUE - @base_count - @expected) AS `OFFSET`
64
from INFORMATION_SCHEMA.SESSION_STATUS
65
where variable_name='COM_STMT_REPREPARE' ;
71
# When not expecting a re-prepare, write the test like this:
75
# When expecting a re-prepare, write the test like this:
76
# set @expected = @expected + 1;
81
--echo =====================================================================
82
--echo Testing 1: NOTHING -> TABLE transitions
83
--echo =====================================================================
50
# - Part 23: Special statements
51
# - Part 24: Testing the strength of TABLE_SHARE version
86
drop table if exists t1;
53
drop temporary table if exists t1, t2, t3;
54
drop table if exists t1, t2, t3;
55
drop procedure if exists p_verify_reprepare_count;
56
drop procedure if exists p1;
57
drop function if exists f1;
58
drop view if exists v1, v2;
62
create procedure p_verify_reprepare_count(expected int)
64
declare old_reprepare_count int default @reprepare_count;
66
select variable_value from
67
information_schema.session_status where
68
variable_name='com_stmt_reprepare'
69
into @reprepare_count;
71
if old_reprepare_count + expected <> @reprepare_count then
72
select concat("Expected: ", expected,
73
", actual: ", @reprepare_count - old_reprepare_count)
76
select '' as "SUCCESS";
80
set @reprepare_count= 0;
83
--echo =====================================================================
84
--echo Part 1: NOTHING -> TABLE transitions
85
--echo =====================================================================
89
87
# can not be tested since prepare failed
90
88
--error ER_NO_SUCH_TABLE
91
prepare stmt from 'select * from t1';
93
--echo =====================================================================
94
--echo Testing 2: NOTHING -> TEMPORARY TABLE transitions
95
--echo =====================================================================
99
--echo =====================================================================
100
--echo Testing 3: NOTHING -> VIEW transitions
101
--echo =====================================================================
105
--echo =====================================================================
106
--echo Testing 4: TABLE -> NOTHING transitions
107
--echo =====================================================================
110
drop table if exists t4;
113
create table t4(a int);
115
prepare stmt from 'select * from t4';
122
--error ER_NO_SUCH_TABLE
125
--error ER_NO_SUCH_TABLE
129
--echo =====================================================================
130
--echo Testing 5: TABLE -> TABLE (DDL) transitions
131
--echo =====================================================================
134
drop table if exists t5;
137
create table t5(a int);
139
prepare stmt from 'select a from t5';
145
alter table t5 add column (b int);
147
set @expected = @expected + 1;
155
--echo =====================================================================
156
--echo Testing 6: TABLE -> TABLE (TRIGGER) transitions
157
--echo =====================================================================
160
# Test 6-a: adding a relevant trigger
161
# Test 6-b: adding an irrelevant trigger
162
# Test 6-c: changing a relevant trigger
163
# Test 6-d: changing an irrelevant trigger
164
# Test 6-e: removing a relevant trigger
165
# Test 6-f: removing an irrelevant trigger
169
drop table if exists t6;
172
create table t6(a int);
174
prepare stmt from 'insert into t6(a) value (?)';
89
prepare stmt from "select * from t1";
91
--echo =====================================================================
92
--echo Part 2: NOTHING -> TEMPORARY TABLE transitions
93
--echo =====================================================================
97
--echo =====================================================================
98
--echo Part 3: NOTHING -> VIEW transitions
99
--echo =====================================================================
103
--echo =====================================================================
104
--echo Part 4: TABLE -> NOTHING transitions
105
--echo =====================================================================
107
--echo # Test 4-a: select ... from <table>
108
create table t1 (a int);
110
prepare stmt from "select * from t1";
112
call p_verify_reprepare_count(0);
114
call p_verify_reprepare_count(0);
117
--error ER_NO_SUCH_TABLE
119
call p_verify_reprepare_count(0);
120
--error ER_NO_SUCH_TABLE
122
call p_verify_reprepare_count(0);
123
deallocate prepare stmt;
125
--echo # Test 4-b: TABLE -> NOTHING by renaming the table
126
create table t1 (a int);
127
prepare stmt from "select * from t1";
129
call p_verify_reprepare_count(0);
131
call p_verify_reprepare_count(0);
133
rename table t1 to t2;
134
--error ER_NO_SUCH_TABLE
136
call p_verify_reprepare_count(0);
137
--error ER_NO_SUCH_TABLE
139
call p_verify_reprepare_count(0);
141
deallocate prepare stmt;
144
--echo =====================================================================
145
--echo Part 5: TABLE -> TABLE (DDL) transitions
146
--echo =====================================================================
148
create table t1 (a int);
150
prepare stmt from "select a from t1";
152
call p_verify_reprepare_count(0);
154
call p_verify_reprepare_count(0);
156
alter table t1 add column (b int);
159
call p_verify_reprepare_count(1);
161
call p_verify_reprepare_count(0);
164
deallocate prepare stmt;
167
--echo =====================================================================
168
--echo Part 6: TABLE -> TABLE (TRIGGER) transitions
169
--echo =====================================================================
171
--echo # Test 6-a: adding a relevant trigger
173
create table t1 (a int);
175
prepare stmt from "insert into t1 (a) value (?)";
176
177
execute stmt using @val;
178
call p_verify_reprepare_count(0);
180
# Relevant trigger: execute should reprepare
181
create trigger t1_bi before insert on t1 for each row
179
185
execute stmt using @val;
182
# Relevant trigger: execute should reprepare
184
create trigger t6_bi before insert on t6 for each row
186
set @message= "t6_bi";
186
call p_verify_reprepare_count(1);
193
set @expected = @expected + 1;
194
189
execute stmt using @val;
190
call p_verify_reprepare_count(0);
193
prepare stmt from "insert into t1 (a) value (?)";
198
195
execute stmt using @val;
196
call p_verify_reprepare_count(0);
202
prepare stmt from 'insert into t6(a) value (?)';
199
--echo # Test 6-b: adding an irrelevant trigger
201
# Unrelated trigger: reprepare may or may not happen, implementation dependent
202
create trigger t1_bd before delete on t1 for each row
205
206
execute stmt using @val;
207
call p_verify_reprepare_count(1);
210
210
execute stmt using @val;
211
call p_verify_reprepare_count(0);
214
# Unrelated trigger: execute can pass of fail, implementation dependent
216
create trigger t6_bd before delete on t6 for each row
218
set @message= "t6_bd";
214
prepare stmt from "insert into t1 (a) value (?)";
225
set @expected = @expected + 1;
226
216
execute stmt using @val;
217
call p_verify_reprepare_count(0);
220
--echo # Test 6-c: changing a relevant trigger
222
# Relevant trigger: execute should reprepare
224
create trigger t1_bi before insert on t1 for each row
225
set @message= concat("new trigger: ", new.a);
231
228
execute stmt using @val;
229
call p_verify_reprepare_count(1);
235
prepare stmt from 'insert into t6(a) value (?)';
238
232
execute stmt using @val;
233
call p_verify_reprepare_count(0);
236
prepare stmt from "insert into t1 (a) value (?)";
243
238
execute stmt using @val;
239
call p_verify_reprepare_count(0);
247
# Relevant trigger: execute should reprepare
250
create trigger t6_bi before insert on t6 for each row
252
set @message= "t6_bi (2)";
242
--echo # Test 6-d: changing an irrelevant trigger
244
# Unrelated trigger: reprepare may or may not happen, implementation dependent
259
set @expected = @expected + 1;
260
248
execute stmt using @val;
249
call p_verify_reprepare_count(1);
252
--echo Test 6-e: removing a relevant trigger
264
257
execute stmt using @val;
258
call p_verify_reprepare_count(1);
268
prepare stmt from 'insert into t6(a) value (?)';
271
261
execute stmt using @val;
262
call p_verify_reprepare_count(0);
265
prepare stmt from "insert into t1 (a) value (?)";
276
267
execute stmt using @val;
280
# Unrelated trigger: execute can pass of fail, implementation dependent
283
create trigger t6_bd before delete on t6 for each row
285
set @message= "t6_bd (2)";
292
set @expected = @expected + 1;
293
execute stmt using @val;
298
execute stmt using @val;
302
prepare stmt from 'insert into t6(a) value (?)';
305
execute stmt using @val;
310
execute stmt using @val;
318
set @expected = @expected + 1;
319
execute stmt using @val;
323
execute stmt using @val;
327
prepare stmt from 'insert into t6(a) value (?)';
330
execute stmt using @val;
334
execute stmt using @val;
341
set @expected = @expected + 1;
342
execute stmt using @val;
346
execute stmt using @val;
350
select * from t6 order by a;
353
--echo =====================================================================
354
--echo Testing 7: TABLE -> TABLE (TRIGGER dependencies) transitions
355
--echo =====================================================================
358
# Test 7-a: dependent PROCEDURE has changed
359
# Test 7-b: dependent FUNCTION has changed
360
# Test 7-c: dependent VIEW has changed
361
# Test 7-d: dependent TABLE has changed
362
# Test 7-e: dependent TABLE TRIGGER has changed
366
drop table if exists t7_proc;
367
drop table if exists t7_func;
368
drop table if exists t7_view;
369
drop table if exists t7_table;
370
drop table if exists t7_dependent_table;
371
drop table if exists t7_table_trigger;
372
drop table if exists t7_audit;
373
drop procedure if exists audit_proc;
374
drop function if exists audit_func;
375
drop view if exists audit_view;
378
create table t7_proc(a int);
379
create table t7_func(a int);
380
create table t7_view(a int);
381
create table t7_table(a int);
382
create table t7_table_trigger(a int);
384
create table t7_audit(old_a int, new_a int, reason varchar(50));
385
create table t7_dependent_table(old_a int, new_a int, reason varchar(50));
387
create procedure audit_proc(a int)
388
insert into t7_audit values (NULL, a, "proc v1");
390
create function audit_func() returns varchar(50)
393
create view audit_view as select "view v1" as reason from dual;
395
create trigger t7_proc_bi before insert on t7_proc for each row
396
call audit_proc(NEW.a);
398
create trigger t7_func_bi before insert on t7_func for each row
399
insert into t7_audit values (NULL, NEW.a, audit_func());
401
create trigger t7_view_bi before insert on t7_view for each row
402
insert into t7_audit values (NULL, NEW.a, (select reason from audit_view));
404
create trigger t7_table_bi before insert on t7_table for each row
405
insert into t7_dependent_table values (NULL, NEW.a, "dependent table");
407
create trigger t7_table_trigger_bi before insert on t7_dependent_table
408
for each row set NEW.reason="trigger v1";
410
prepare stmt_proc from 'insert into t7_proc(a) value (?)';
412
execute stmt_proc using @val;
415
execute stmt_proc using @val;
418
drop procedure audit_proc;
420
create procedure audit_proc(a int)
421
insert into t7_audit values (NULL, a, "proc v2");
424
set @expected = @expected + 1;
425
execute stmt_proc using @val;
428
execute stmt_proc using @val;
432
prepare stmt_func from 'insert into t7_func(a) value (?)';
434
execute stmt_func using @val;
437
execute stmt_func using @val;
440
drop function audit_func;
442
create function audit_func() returns varchar(50)
446
set @expected = @expected + 1;
447
execute stmt_func using @val;
450
execute stmt_func using @val;
453
prepare stmt_view from 'insert into t7_view(a) value (?)';
455
execute stmt_view using @val;
458
execute stmt_view using @val;
461
drop view audit_view;
463
create view audit_view as select "view v2" as reason from dual;
465
# Because of Bug#33255, the wrong result is still produced for cases
466
# 303 and 304, even after re-preparing the statement.
467
# This is because the table trigger is cached and is not invalidated.
470
set @expected = @expected + 1;
471
execute stmt_view using @val;
474
execute stmt_view using @val;
478
prepare stmt_table from 'insert into t7_table(a) value (?)';
480
execute stmt_table using @val;
483
execute stmt_table using @val;
486
alter table t7_dependent_table add column comments varchar(100) default NULL;
489
set @expected = @expected + 1;
490
--error ER_WRONG_VALUE_COUNT_ON_ROW
491
execute stmt_table using @val;
494
set @expected = @expected + 1;
495
--error ER_WRONG_VALUE_COUNT_ON_ROW
496
execute stmt_table using @val;
499
alter table t7_dependent_table drop column comments;
502
set @expected = @expected + 1;
503
execute stmt_table using @val;
506
execute stmt_table using @val;
510
prepare stmt_table_trigger from 'insert into t7_table(a) value (?)';
512
execute stmt_table_trigger using @val;
515
execute stmt_table_trigger using @val;
518
drop trigger t7_table_trigger_bi;
520
create trigger t7_table_trigger_bi before insert on t7_dependent_table
521
for each row set NEW.reason="trigger v2";
524
set @expected = @expected + 1;
525
execute stmt_table_trigger using @val;
528
execute stmt_table_trigger using @val;
531
select * from t7_audit order by new_a;
533
select * from t7_dependent_table order by new_a;
539
drop table t7_dependent_table;
540
drop table t7_table_trigger;
542
drop procedure audit_proc;
543
drop function audit_func;
544
drop view audit_view;
546
--echo =====================================================================
547
--echo Testing 8: TABLE -> TEMPORARY TABLE transitions
548
--echo =====================================================================
551
drop table if exists t8;
554
create table t8(a int);
556
prepare stmt from 'select * from t8';
563
create temporary table t8(a int);
565
set @expected = @expected + 1;
573
--echo =====================================================================
574
--echo Testing 9: TABLE -> VIEW transitions
575
--echo =====================================================================
578
drop table if exists t9;
579
drop table if exists t9_b;
582
create table t9(a int);
583
create table t9_b(a int);
585
prepare stmt from 'select * from t9';
592
create view t9 as select * from t9_b;
594
set @expected = @expected + 1;
603
--echo =====================================================================
604
--echo Testing 10: TEMPORARY TABLE -> NOTHING transitions
605
--echo =====================================================================
608
drop temporary table if exists t10;
611
create temporary table t10(a int);
613
prepare stmt from 'select * from t10';
619
drop temporary table t10;
620
--error ER_NO_SUCH_TABLE
623
--error ER_NO_SUCH_TABLE
627
--echo =====================================================================
628
--echo Testing 11: TEMPORARY TABLE -> TABLE transitions
629
--echo =====================================================================
632
drop table if exists t11;
633
drop temporary table if exists t11;
636
create table t11(a int);
637
insert into t11(a) value (1);
638
create temporary table t11(a int);
640
prepare stmt from 'select * from t11';
646
drop temporary table t11;
648
set @expected = @expected + 1;
657
--echo =====================================================================
658
--echo Testing 12: TEMPORARY TABLE -> TEMPORARY TABLE (DDL) transitions
659
--echo =====================================================================
662
drop temporary table if exists t12;
665
create temporary table t12(a int);
667
prepare stmt from 'select a from t12';
673
drop temporary table t12;
674
create temporary table t12(a int, b int);
676
set @expected = @expected + 1;
685
--echo =====================================================================
686
--echo Testing 13: TEMPORARY TABLE -> VIEW transitions
687
--echo =====================================================================
690
drop temporary table if exists t13;
691
drop table if exists t13_b;
694
create temporary table t13(a int);
695
create table t13_b(a int);
697
prepare stmt from 'select * from t13';
703
drop temporary table t13;
704
create view t13 as select * from t13_b;
706
set @expected = @expected + 1;
715
--echo =====================================================================
716
--echo Testing 14: VIEW -> NOTHING transitions
717
--echo =====================================================================
720
drop view if exists t14;
721
drop table if exists t14_b;
724
create table t14_b(a int);
725
create view t14 as select * from t14_b;
727
prepare stmt from 'select * from t14';
735
set @expected = @expected + 1;
736
--error ER_NO_SUCH_TABLE
739
set @expected = @expected + 1;
740
--error ER_NO_SUCH_TABLE
746
--echo =====================================================================
747
--echo Testing 15: VIEW -> TABLE transitions
748
--echo =====================================================================
751
drop view if exists t15;
752
drop table if exists t15_b;
755
create table t15_b(a int);
756
create view t15 as select * from t15_b;
758
prepare stmt from 'select * from t15';
765
create table t15(a int);
767
set @expected = @expected + 1;
776
--echo =====================================================================
777
--echo Testing 16: VIEW -> TEMPORARY TABLE transitions
778
--echo =====================================================================
781
drop view if exists t16;
782
drop table if exists t16_b;
785
create table t16_b(a int);
786
create view t16 as select * from t16_b;
788
prepare stmt from 'select * from t16';
795
create temporary table t16(a int);
797
set @expected = @expected + 1;
804
drop temporary table t16;
806
--echo =====================================================================
807
--echo Testing 17: VIEW -> VIEW (DDL) transitions
808
--echo =====================================================================
811
drop view if exists t17;
812
drop table if exists t17_b;
815
create table t17_b(a int);
816
insert into t17_b values (10), (20), (30);
818
create view t17 as select a, 2*a as b, 3*a as c from t17_b;
821
prepare stmt from 'select * from t17';
828
create view t17 as select a, 2*a as b, 5*a as c from t17_b;
831
set @expected = @expected + 1;
840
--echo =====================================================================
841
--echo Testing 18: VIEW -> VIEW (VIEW dependencies) transitions
842
--echo =====================================================================
845
# Test 18-a: dependent PROCEDURE has changed (via a trigger)
846
# Test 18-b: dependent FUNCTION has changed
847
# Test 18-c: dependent VIEW has changed
848
# Test 18-d: dependent TABLE has changed
849
# Test 18-e: dependent TABLE TRIGGER has changed
853
drop table if exists t18;
854
drop table if exists t18_dependent_table;
855
drop view if exists t18_func;
856
drop view if exists t18_view;
857
drop view if exists t18_table;
858
drop function if exists view_func;
859
drop view if exists view_view;
862
# TODO: insertable view -> trigger
863
# TODO: insertable view -> trigger -> proc ?
865
create table t18(a int);
866
insert into t18 values (1), (2), (3);
868
create function view_func(x int) returns int
871
create view view_view as select "view v1" as reason from dual;
873
create table t18_dependent_table(a int);
875
create view t18_func as select a, view_func(a) as b from t18;
876
create view t18_view as select a, reason as b from t18, view_view;
877
create view t18_table as select * from t18;
879
prepare stmt_func from 'select * from t18_func';
885
drop function view_func;
886
create function view_func(x int) returns int
889
set @expected = @expected + 1;
895
prepare stmt_view from 'select * from t18_view';
902
create view view_view as select "view v2" as reason from dual;
904
set @expected = @expected + 1;
910
prepare stmt_table from 'select * from t18_table';
916
alter table t18 add column comments varchar(50) default NULL;
918
set @expected = @expected + 1;
925
drop table t18_dependent_table;
929
drop function view_func;
932
--echo =====================================================================
933
--echo Testing 19: Special tables (INFORMATION_SCHEMA)
934
--echo =====================================================================
937
drop procedure if exists proc_19;
268
call p_verify_reprepare_count(0);
271
select * from t1 order by a;
273
deallocate prepare stmt;
275
--echo =====================================================================
276
--echo Part 7: TABLE -> TABLE (TRIGGER dependencies) transitions
277
--echo =====================================================================
279
--echo # Test 7-a: dependent PROCEDURE has changed
281
--echo # Note, this scenario is not supported, subject of Bug#12093
284
create table t1 (a int);
285
create trigger t1_ai after insert on t1 for each row
287
create procedure p1(a int) begin end;
288
prepare stmt from "insert into t1 (a) values (?)";
290
execute stmt using @var;
292
create procedure p1 (a int) begin end;
294
--error ER_SP_DOES_NOT_EXIST
295
execute stmt using @var;
298
call p_verify_reprepare_count(0);
300
--echo # Test 7-b: dependent FUNCTION has changed
302
--echo # Note, this scenario is supported, subject of Bug#12093
305
create trigger t1_ai after insert on t1 for each row
306
select f1(new.a+1) into @var;
307
create function f1 (a int) returns int return a;
308
prepare stmt from "insert into t1(a) values (?)";
310
execute stmt using @var;
313
create function f1 (a int) returns int return 0;
314
execute stmt using @var;
315
call p_verify_reprepare_count(1);
317
deallocate prepare stmt;
319
--echo # Test 7-c: dependent VIEW has changed
321
--echo # Note, this scenario is not functioning correctly, see
322
--echo # Bug#33255 Trigger using views and view ddl : corrupted triggers
323
--echo # and Bug #33000 Triggers do not detect changes in meta-data.
326
create table t2 (a int unique);
327
create table t3 (a int unique);
328
create view v1 as select a from t2;
329
create trigger t1_ai after insert on t1 for each row
330
insert into v1 (a) values (new.a);
332
--echo # Demonstrate that the same bug is present
333
--echo # without prepared statements
334
insert into t1 (a) values (5);
338
create view v1 as select a from t3;
339
--error ER_NO_SUCH_TABLE
340
insert into t1 (a) values (6);
342
insert into t1 (a) values (6);
346
prepare stmt from "insert into t1 (a) values (?)";
348
execute stmt using @var;
349
call p_verify_reprepare_count(0);
353
create view v1 as select a from t2;
355
--echo # XXX: bug, the SQL statement in the trigger is still
356
--echo # pointing at table 't3', since the view was expanded
357
--echo # at first statement execution.
358
--echo # Repreparation of the main statement doesn't cause repreparation
359
--echo # of trigger statements.
360
--error ER_NO_SUCH_TABLE
361
execute stmt using @var;
362
call p_verify_reprepare_count(1);
364
--echo # Sic: the insert went into t3, even though the view now
365
--echo # points at t2. This is because neither the merged view
366
--echo # nor its prelocking list are affected by view DDL
367
--echo # The binary log is of course wrong, since it is not
368
--echo # using prepared statements
374
execute stmt using @var;
375
call p_verify_reprepare_count(1);
381
--echo # Test 7-d: dependent TABLE has changed
382
create table t1 (a int);
383
create trigger t1_ai after insert on t1 for each row
384
insert into t2 (a) values (new.a);
385
create table t2 (a int);
387
prepare stmt from "insert into t1 (a) values (?)";
389
execute stmt using @var;
390
alter table t2 add column comment varchar(255);
392
--echo # Since the dependent table is tracked in the prelocked
393
--echo # list of the prepared statement, invalidation happens
394
--echo # and the statement is re-prepared. This is an unnecessary
395
--echo # side effect, since the statement that *is* dependent
396
--echo # on t2 definition is inside the trigger, and it is currently
397
--echo # not reprepared (see the previous test case).
398
execute stmt using @var;
399
call p_verify_reprepare_count(1);
404
--echo # Test 7-e: dependent TABLE TRIGGER has changed
405
create table t1 (a int);
406
create trigger t1_ai after insert on t1 for each row
407
insert into t2 (a) values (new.a);
408
create table t2 (a int unique);
409
create trigger t2_ai after insert on t2 for each row
410
insert into t3 (a) values (new.a);
411
create table t3 (a int unique);
412
create table t4 (a int unique);
414
insert into t1 (a) values (1);
415
select * from t1 join t2 on (t1.a=t2.a) join t3 on (t2.a=t3.a);
417
create trigger t2_ai after insert on t2 for each row
418
insert into t4 (a) values (new.a);
419
insert into t1 (a) values (2);
420
select * from t1 join t2 on (t1.a=t2.a) join t4 on (t2.a=t4.a);
422
prepare stmt from "insert into t1 (a) values (?)";
424
execute stmt using @var;
425
select * from t1 join t2 on (t1.a=t2.a) join t4 on (t2.a=t4.a);
427
create trigger t2_ai after insert on t2 for each row
428
insert into t3 (a) values (new.a);
430
execute stmt using @var;
431
call p_verify_reprepare_count(1);
432
select * from t1 join t2 on (t1.a=t2.a) join t3 on (t2.a=t3.a);
433
select * from t1 join t2 on (t1.a=t2.a) join t4 on (t2.a=t4.a);
435
drop table t1, t2, t3, t4;
436
deallocate prepare stmt;
438
--echo =====================================================================
439
--echo Part 8: TABLE -> TEMPORARY TABLE transitions
440
--echo =====================================================================
442
--echo # Test 8-a: base table used recreated as temporary table
443
create table t1 (a int);
445
prepare stmt from "select * from t1";
449
create temporary table t1 (a int);
452
call p_verify_reprepare_count(1);
454
call p_verify_reprepare_count(0);
457
deallocate prepare stmt;
459
--echo # Test 8-b: temporary table has precedence over base table with same name
460
create table t1 (a int);
461
prepare stmt from 'select count(*) from t1';
463
call p_verify_reprepare_count(0);
465
call p_verify_reprepare_count(0);
467
create temporary table t1 AS SELECT 1;
469
call p_verify_reprepare_count(1);
471
call p_verify_reprepare_count(0);
473
deallocate prepare stmt;
474
drop temporary table t1;
478
--echo =====================================================================
479
--echo Part 9: TABLE -> VIEW transitions
480
--echo =====================================================================
482
create table t1 (a int);
484
prepare stmt from "select * from t1";
486
call p_verify_reprepare_count(0);
489
create table t2 (a int);
490
create view t1 as select * from t2;
493
call p_verify_reprepare_count(1);
497
deallocate prepare stmt;
499
--echo =====================================================================
500
--echo Part 10: TEMPORARY TABLE -> NOTHING transitions
501
--echo =====================================================================
503
create temporary table t1 (a int);
505
prepare stmt from "select * from t1";
507
call p_verify_reprepare_count(0);
509
drop temporary table t1;
510
--error ER_NO_SUCH_TABLE
512
call p_verify_reprepare_count(0);
513
deallocate prepare stmt;
515
--echo =====================================================================
516
--echo Part 11: TEMPORARY TABLE -> TABLE transitions
517
--echo =====================================================================
519
--echo # Test 11-a: temporary table replaced by base table
520
create table t1 (a int);
521
insert into t1 (a) value (1);
522
create temporary table t1 (a int);
524
prepare stmt from "select * from t1";
526
call p_verify_reprepare_count(0);
528
drop temporary table t1;
531
call p_verify_reprepare_count(1);
535
deallocate prepare stmt;
538
--echo # Test 11-b: temporary table has precedence over base table with same name
539
--echo # temporary table disappears
540
create table t1 (a int);
541
create temporary table t1 as select 1 as a;
542
prepare stmt from "select count(*) from t1";
544
call p_verify_reprepare_count(0);
546
call p_verify_reprepare_count(0);
548
drop temporary table t1;
550
call p_verify_reprepare_count(1);
552
call p_verify_reprepare_count(0);
554
deallocate prepare stmt;
558
--echo =====================================================================
559
--echo Part 12: TEMPORARY TABLE -> TEMPORARY TABLE (DDL) transitions
560
--echo =====================================================================
562
create temporary table t1 (a int);
564
prepare stmt from "select a from t1";
566
call p_verify_reprepare_count(0);
568
drop temporary table t1;
569
create temporary table t1 (a int, b int);
572
call p_verify_reprepare_count(1);
575
drop temporary table t1;
576
deallocate prepare stmt;
578
--echo =====================================================================
579
--echo Part 13: TEMPORARY TABLE -> VIEW transitions
580
--echo =====================================================================
582
create temporary table t1 (a int);
583
create table t2 (a int);
585
prepare stmt from "select * from t1";
587
call p_verify_reprepare_count(0);
589
drop temporary table t1;
590
create view t1 as select * from t2;
593
call p_verify_reprepare_count(1);
597
deallocate prepare stmt;
599
--echo =====================================================================
600
--echo Part 14: VIEW -> NOTHING transitions
601
--echo =====================================================================
603
create table t2 (a int);
604
create view t1 as select * from t2;
606
prepare stmt from "select * from t1";
610
--error ER_NO_SUCH_TABLE
612
call p_verify_reprepare_count(0);
613
--error ER_NO_SUCH_TABLE
615
call p_verify_reprepare_count(0);
618
deallocate prepare stmt;
620
--echo =====================================================================
621
--echo Part 15: VIEW -> TABLE transitions
622
--echo =====================================================================
624
create table t2 (a int);
625
create view t1 as select * from t2;
627
prepare stmt from "select * from t1";
629
call p_verify_reprepare_count(0);
632
create table t1 (a int);
635
call p_verify_reprepare_count(1);
639
deallocate prepare stmt;
641
--echo =====================================================================
642
--echo Part 16: VIEW -> TEMPORARY TABLE transitions
643
--echo =====================================================================
645
create table t2 (a int);
646
insert into t2 (a) values (1);
647
create view t1 as select * from t2;
649
prepare stmt from "select * from t1";
651
call p_verify_reprepare_count(0);
653
create temporary table t1 (a int);
655
call p_verify_reprepare_count(1);
658
call p_verify_reprepare_count(0);
661
drop temporary table t1;
662
deallocate prepare stmt;
664
--echo =====================================================================
665
--echo Part 17: VIEW -> VIEW (DDL) transitions
666
--echo =====================================================================
668
create table t2 (a int);
669
insert into t2 values (10), (20), (30);
671
create view t1 as select a, 2*a as b, 3*a as c from t2;
674
prepare stmt from "select * from t1";
678
create view t1 as select a, 2*a as b, 5*a as c from t2;
681
--echo # Currently a different result from conventional statements.
682
--echo # A view is inlined once at prepare, later on view DDL
683
--echo # does not affect prepared statement and it is not re-prepared.
684
--echo # This is reported in Bug#36002 Prepared statements: if a view
685
--echo # used in a statement is replaced, bad data
687
call p_verify_reprepare_count(0);
690
call p_verify_reprepare_count(1);
694
deallocate prepare stmt;
696
--echo =====================================================================
697
--echo Part 18: VIEW -> VIEW (VIEW dependencies) transitions
698
--echo =====================================================================
700
--echo # Part 18a: dependent function has changed
701
create table t1 (a int);
702
insert into t1 (a) values (1), (2), (3);
703
create function f1() returns int return (select max(a) from t1);
704
create view v1 as select f1();
705
prepare stmt from "select * from v1";
708
call p_verify_reprepare_count(0);
710
create function f1() returns int return 2;
711
--echo # XXX: Used to be another manifestation of Bug#12093.
712
--echo # We only used to get a different error
713
--echo # message because the non-existing procedure error is masked
714
--echo # by the view.
717
call p_verify_reprepare_count(1);
719
--echo # Part 18b: dependent procedure has changed (referred to via a function)
721
create table t2 (a int);
722
insert into t2 (a) values (4), (5), (6);
726
create function f1() returns int
733
create procedure p1(out x int) select max(a) from t1 into x;
735
prepare stmt from "select * from v1";
738
call p_verify_reprepare_count(0);
740
create procedure p1(out x int) select max(a) from t2 into x;
741
--echo # XXX: used to be a bug. The prelocked list was not invalidated
742
--echo # and we kept opening table t1, whereas the procedure
743
--echo # is now referring to table t2
745
call p_verify_reprepare_count(1);
748
call p_verify_reprepare_count(0);
751
--echo # Test 18-c: dependent VIEW has changed
754
create view v2 as select a from t1;
755
create view v1 as select * from v2;
756
prepare stmt from "select * from v1";
759
call p_verify_reprepare_count(0);
761
create view v2 as select a from t2;
764
call p_verify_reprepare_count(0);
767
call p_verify_reprepare_count(1);
769
--echo # Test 18-d: dependent TABLE has changed
771
create table v2 as select * from t1;
773
call p_verify_reprepare_count(1);
775
call p_verify_reprepare_count(0);
777
create table v2 (a int unique) as select * from t2;
779
call p_verify_reprepare_count(1);
781
call p_verify_reprepare_count(0);
783
--echo # Test 18-e: dependent TABLE trigger has changed
785
prepare stmt from "insert into v1 (a) values (?)";
787
execute stmt using @var;
788
call p_verify_reprepare_count(0);
789
create trigger v2_bi before insert on v2 for each row set @message="v2_bi";
791
execute stmt using @var;
792
call p_verify_reprepare_count(1);
797
execute stmt using @var;
798
call p_verify_reprepare_count(1);
800
create trigger v2_bi after insert on v2 for each row set @message="v2_ai";
802
execute stmt using @var;
803
call p_verify_reprepare_count(1);
810
drop table if exists t1, t2, v1, v2;
811
drop view if exists v1, v2;
815
deallocate prepare stmt;
817
--echo =====================================================================
818
--echo Part 19: Special tables (INFORMATION_SCHEMA)
819
--echo =====================================================================
940
821
# Using a temporary table internally should not confuse the prepared
941
822
# statement code, and should not raise ER_PS_INVALIDATED errors
942
823
prepare stmt from
943
'select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE
824
"select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE
944
825
from INFORMATION_SCHEMA.ROUTINES where
945
routine_name=\'proc_19\'';
947
create procedure proc_19() select "hi there";
954
drop procedure proc_19;
955
create procedure proc_19() select "hi there, again";
962
drop procedure proc_19;
964
--echo =====================================================================
965
--echo Testing 20: Special tables (log tables)
966
--echo =====================================================================
969
'select * from mysql.general_log where argument=\'IMPOSSIBLE QUERY STRING\'';
980
--echo =====================================================================
981
--echo Testing 21: Special tables (system tables)
982
--echo =====================================================================
985
drop procedure if exists proc_21;
989
'select type, db, name from mysql.proc where name=\'proc_21\'';
991
create procedure proc_21() select "hi there";
998
drop procedure proc_21;
999
create procedure proc_21() select "hi there, again";
1006
drop procedure proc_21;
1008
--echo =====================================================================
1009
--echo Testing 22: Special tables (views temp tables)
1010
--echo =====================================================================
1013
drop table if exists t22_b;
1014
drop view if exists t22;
1017
create table t22_b(a int);
1019
create algorithm=temptable view t22 as select a*a as a2 from t22_b;
1021
# Using a temporary table internally should not confuse the prepared
1022
# statement code, and should not raise ER_PS_INVALIDATED errors
1023
show create view t22;
1025
prepare stmt from 'select * from t22';
1027
insert into t22_b values (1), (2), (3);
1033
insert into t22_b values (4), (5), (6);
1042
--echo =====================================================================
1043
--echo Testing 23: Special tables (internal join tables)
1044
--echo =====================================================================
1047
drop table if exists t23_a;
1048
drop table if exists t23_b;
1051
create table t23_a(a int);
1052
create table t23_b(b int);
1054
# Using a temporary table internally should not confuse the prepared
1055
# statement code, and should not raise ER_PS_INVALIDATED errors
1056
prepare stmt from 'select * from t23_a join t23_b';
1058
insert into t23_a values (1), (2), (3);
1059
insert into t23_b values (10), (20), (30);
1065
insert into t23_a values (4);
1066
insert into t23_b values (40);
1075
--echo =====================================================================
1076
--echo Testing 24: Special statements
1077
--echo =====================================================================
1079
# SQLCOM_ALTER_TABLE:
1082
drop table if exists t24_alter;
1085
create table t24_alter(a int);
1087
prepare stmt from 'alter table t24_alter add column b int';
1091
drop table t24_alter;
1092
create table t24_alter(a1 int, a2 int);
1094
# t24_alter has changed, and it's not a problem
1098
alter table t24_alter drop column b;
1102
alter table t24_alter drop column b;
1106
drop table t24_alter;
1111
drop table if exists t24_repair;
1114
create table t24_repair(a int);
1115
insert into t24_repair values (1), (2), (3);
1117
prepare stmt from 'repair table t24_repair';
1121
drop table t24_repair;
1122
create table t24_repair(a1 int, a2 int);
1123
insert into t24_repair values (1, 10), (2, 20), (3, 30);
1125
# t24_repair has changed, and it's not a problem
1129
alter table t24_repair add column b varchar(50) default NULL;
1133
alter table t24_repair drop column b;
1137
drop table t24_repair;
1142
drop table if exists t24_analyze;
1145
create table t24_analyze(a int);
1146
insert into t24_analyze values (1), (2), (3);
1148
prepare stmt from 'analyze table t24_analyze';
1152
drop table t24_analyze;
1153
create table t24_analyze(a1 int, a2 int);
1154
insert into t24_analyze values (1, 10), (2, 20), (3, 30);
1156
# t24_analyze has changed, and it's not a problem
1160
alter table t24_analyze add column b varchar(50) default NULL;
1164
alter table t24_analyze drop column b;
1168
drop table t24_analyze;
1173
drop table if exists t24_optimize;
1176
create table t24_optimize(a int);
1177
insert into t24_optimize values (1), (2), (3);
1179
prepare stmt from 'optimize table t24_optimize';
1183
drop table t24_optimize;
1184
create table t24_optimize(a1 int, a2 int);
1185
insert into t24_optimize values (1, 10), (2, 20), (3, 30);
1187
# t24_optimize has changed, and it's not a problem
1191
alter table t24_optimize add column b varchar(50) default NULL;
1195
alter table t24_optimize drop column b;
1199
drop table t24_optimize;
1201
# SQLCOM_SHOW_CREATE_PROC:
1204
drop procedure if exists changing_proc;
1207
prepare stmt from 'show create procedure changing_proc';
1208
--error ER_SP_DOES_NOT_EXIST
1211
--error ER_SP_DOES_NOT_EXIST
1215
create procedure changing_proc() begin end;
1217
# changing_proc has changed, and it's not a problem
1223
drop procedure changing_proc;
1224
create procedure changing_proc(x int, y int) begin end;
1231
drop procedure changing_proc;
1233
--error ER_SP_DOES_NOT_EXIST
1236
--error ER_SP_DOES_NOT_EXIST
1240
# SQLCOM_SHOW_CREATE_FUNC:
1243
drop function if exists changing_func;
1246
prepare stmt from 'show create function changing_func';
1247
--error ER_SP_DOES_NOT_EXIST
1250
--error ER_SP_DOES_NOT_EXIST
1254
create function changing_func() returns int return 0;
1256
# changing_proc has changed, and it's not a problem
1262
drop function changing_func;
1263
create function changing_func(x int, y int) returns int return x+y;
1270
drop function changing_func;
1272
--error ER_SP_DOES_NOT_EXIST
1275
--error ER_SP_DOES_NOT_EXIST
1279
# SQLCOM_SHOW_CREATE_TRIGGER:
1282
drop table if exists t24_trigger;
1285
create table t24_trigger(a int);
1287
prepare stmt from 'show create trigger t24_bi;';
1288
--error ER_TRG_DOES_NOT_EXIST
1291
--error ER_TRG_DOES_NOT_EXIST
1296
create trigger t24_bi before insert on t24_trigger for each row
1298
set @message= "t24_bi";
1303
# t24_bi has changed, and it's not a problem
1309
drop trigger t24_bi;
1311
create trigger t24_bi before insert on t24_trigger for each row
1313
set @message= "t24_bi (2)";
1318
# t24_bi has changed, and it's not a problem
1319
set @expected = @expected + 1;
1325
drop trigger t24_bi;
1327
--error ER_TRG_DOES_NOT_EXIST
1330
--error ER_TRG_DOES_NOT_EXIST
1334
drop table t24_trigger;
1336
--echo =====================================================================
1337
--echo Testing 25: Testing the strength of TABLE_SHARE version
1338
--echo =====================================================================
1340
# Test 25-a: number of columns
1343
drop table if exists t25_num_col;
1346
create table t25_num_col(a int);
1348
prepare stmt from 'select a from t25_num_col';
1354
alter table t25_num_col add column b varchar(50) default NULL;
1356
set @expected = @expected + 1;
1362
drop table t25_num_col;
1364
# Test 25-b: column name
1367
drop table if exists t25_col_name;
1370
create table t25_col_name(a int);
1372
prepare stmt from 'select * from t25_col_name';
1378
alter table t25_col_name change a b int;
1380
set @expected = @expected + 1;
1381
--error ER_PS_REBIND
1384
set @expected = @expected + 1;
1385
--error ER_PS_REBIND
1389
drop table t25_col_name;
1391
# Test 25-c: column type
1394
drop table if exists t25_col_type;
1397
create table t25_col_type(a int);
1399
prepare stmt from 'select * from t25_col_type';
1405
alter table t25_col_type change a a varchar(10);
1407
set @expected = @expected + 1;
1408
--error ER_PS_REBIND
1411
set @expected = @expected + 1;
1412
--error ER_PS_REBIND
1416
drop table t25_col_type;
1418
# Test 25-d: column type length
1421
drop table if exists t25_col_type_length;
1424
create table t25_col_type_length(a varchar(10));
1426
prepare stmt from 'select * from t25_col_type_length';
1432
alter table t25_col_type_length change a a varchar(20);
1434
set @expected = @expected + 1;
1435
--error ER_PS_REBIND
1438
set @expected = @expected + 1;
1439
--error ER_PS_REBIND
1443
drop table t25_col_type_length;
1445
# Test 25-e: column NULL property
1448
drop table if exists t25_col_null;
1451
create table t25_col_null(a varchar(10));
1453
prepare stmt from 'select * from t25_col_null';
1459
alter table t25_col_null change a a varchar(10) NOT NULL;
1461
set @expected = @expected + 1;
1462
--error ER_PS_REBIND
1465
set @expected = @expected + 1;
1466
--error ER_PS_REBIND
1470
drop table t25_col_null;
1472
# Test 25-f: column DEFAULT
1475
drop table if exists t25_col_default;
1478
create table t25_col_default(a int, b int DEFAULT 10);
1480
prepare stmt from 'insert into t25_col_default(a) values (?)';
1482
execute stmt using @val;
1485
execute stmt using @val;
1488
alter table t25_col_default change b b int DEFAULT 20;
1491
# Must insert the correct default value for b
1492
set @expected = @expected + 1;
1493
execute stmt using @val;
1497
# Must insert the correct default value for b
1498
execute stmt using @val;
1501
select * from t25_col_default;
1503
drop table t25_col_default;
1505
# Test 25-g: number of keys
1508
drop table if exists t25_index;
1511
create table t25_index(a varchar(10));
1513
prepare stmt from 'select * from t25_index';
1519
create index i1 on t25_index(a);
1521
set @expected = @expected + 1;
1527
drop table t25_index;
1529
# Test 25-h: changing index uniqueness
1532
drop table if exists t25_index_unique;
1535
create table t25_index_unique(a varchar(10), b varchar(10));
1536
create index i1 on t25_index_unique(a, b);
1538
show create table t25_index_unique;
1540
prepare stmt from 'select * from t25_index_unique';
1546
alter table t25_index_unique drop index i1;
1547
create unique index i1 on t25_index_unique(a, b);
1549
show create table t25_index_unique;
1551
set @expected = @expected + 1;
1557
drop table t25_index_unique;
828
create procedure p1() select "hi there";
834
create procedure p1() select "hi there, again";
838
call p_verify_reprepare_count(0);
841
deallocate prepare stmt;
843
--echo =====================================================================
844
--echo Part 20: Special tables (log tables)
845
--echo =====================================================================
848
"select * from mysql.general_log where argument='IMPOSSIBLE QUERY STRING'";
856
call p_verify_reprepare_count(0);
857
deallocate prepare stmt;
859
--echo =====================================================================
860
--echo Part 21: Special tables (system tables)
861
--echo =====================================================================
864
"select type, db, name from mysql.proc where name='p1'";
866
create procedure p1() select "hi there";
872
create procedure p1() select "hi there, again";
876
call p_verify_reprepare_count(0);
879
deallocate prepare stmt;
881
--echo =====================================================================
882
--echo Part 22: Special tables (views temp tables)
883
--echo =====================================================================
885
create table t1 (a int);
887
create algorithm=temptable view v1 as select a*a as a2 from t1;
889
--echo # Using a temporary table internally should not confuse the prepared
890
--echo # statement code, and should not raise ER_PS_INVALIDATED errors
893
prepare stmt from "select * from v1";
895
insert into t1 values (1), (2), (3);
899
insert into t1 values (4), (5), (6);
902
call p_verify_reprepare_count(0);
907
--echo =====================================================================
908
--echo Part 23: Special statements
909
--echo =====================================================================
911
--echo # SQLCOM_ALTER_TABLE:
914
create table t1 (a int);
916
prepare stmt from "alter table t1 add column b int";
920
create table t1 (a1 int, a2 int);
922
--echo # t1 has changed, and it's does not lead to reprepare
925
alter table t1 drop column b;
928
alter table t1 drop column b;
930
call p_verify_reprepare_count(0);
934
--echo # SQLCOM_REPAIR:
936
create table t1 (a int);
938
insert into t1 values (1), (2), (3);
940
prepare stmt from "repair table t1";
946
create table t1 (a1 int, a2 int);
947
insert into t1 values (1, 10), (2, 20), (3, 30);
949
--echo # t1 has changed, and it's does not lead to reprepare
952
alter table t1 add column b varchar(50) default NULL;
954
call p_verify_reprepare_count(0);
956
alter table t1 drop column b;
958
call p_verify_reprepare_count(0);
960
--echo # SQLCOM_ANALYZE:
962
prepare stmt from "analyze table t1";
966
create table t1 (a1 int, a2 int);
967
insert into t1 values (1, 10), (2, 20), (3, 30);
968
--echo # t1 has changed, and it's not a problem
971
alter table t1 add column b varchar(50) default NULL;
974
alter table t1 drop column b;
977
call p_verify_reprepare_count(0);
979
--echo # SQLCOM_OPTIMIZE:
981
prepare stmt from "optimize table t1";
985
create table t1 (a1 int, a2 int);
986
insert into t1 values (1, 10), (2, 20), (3, 30);
988
--echo # t1 has changed, and it's not a problem
991
alter table t1 add column b varchar(50) default NULL;
994
alter table t1 drop column b;
996
call p_verify_reprepare_count(0);
1000
--echo # SQLCOM_SHOW_CREATE_PROC:
1002
prepare stmt from "show create procedure p1";
1003
--error ER_SP_DOES_NOT_EXIST
1005
--error ER_SP_DOES_NOT_EXIST
1008
create procedure p1() begin end;
1010
--disable_result_log
1016
create procedure p1(x int, y int) begin end;
1018
--disable_result_log
1025
--error ER_SP_DOES_NOT_EXIST
1027
--error ER_SP_DOES_NOT_EXIST
1029
call p_verify_reprepare_count(0);
1031
--echo # SQLCOM_SHOW_CREATE_FUNC:
1033
prepare stmt from "show create function f1";
1034
--error ER_SP_DOES_NOT_EXIST
1036
--error ER_SP_DOES_NOT_EXIST
1039
create function f1() returns int return 0;
1041
--disable_result_log
1047
create function f1(x int, y int) returns int return x+y;
1049
--disable_result_log
1056
--error ER_SP_DOES_NOT_EXIST
1058
--error ER_SP_DOES_NOT_EXIST
1060
call p_verify_reprepare_count(0);
1062
--echo # SQLCOM_SHOW_CREATE_TRIGGER:
1064
create table t1 (a int);
1066
prepare stmt from "show create trigger t1_bi";
1067
--error ER_TRG_DOES_NOT_EXIST
1069
--error ER_TRG_DOES_NOT_EXIST
1072
create trigger t1_bi before insert on t1 for each row set @message= "t1_bi";
1074
--disable_result_log
1081
create trigger t1_bi before insert on t1 for each row set @message= "t1_bi (2)";
1083
--disable_result_log
1090
--error ER_TRG_DOES_NOT_EXIST
1092
--error ER_TRG_DOES_NOT_EXIST
1094
call p_verify_reprepare_count(0);
1097
deallocate prepare stmt;
1099
--echo =====================================================================
1100
--echo Part 24: Testing the strength of TABLE_SHARE version
1101
--echo =====================================================================
1103
--echo # Test 24-a: number of columns
1105
create table t1 (a int);
1107
prepare stmt from "select a from t1";
1109
call p_verify_reprepare_count(0);
1111
alter table t1 add column b varchar(50) default NULL;
1114
call p_verify_reprepare_count(1);
1116
call p_verify_reprepare_count(0);
1118
--echo # Test 24-b: column name
1120
alter table t1 change b c int;
1122
call p_verify_reprepare_count(1);
1124
call p_verify_reprepare_count(0);
1126
--echo # Test 24-c: column type
1128
alter table t1 change a a varchar(10);
1131
call p_verify_reprepare_count(1);
1133
call p_verify_reprepare_count(0);
1135
--echo # Test 24-d: column type length
1137
alter table t1 change a a varchar(20);
1140
call p_verify_reprepare_count(1);
1142
call p_verify_reprepare_count(0);
1144
--echo # Test 24-e: column NULL property
1146
alter table t1 change a a varchar(20) NOT NULL;
1149
call p_verify_reprepare_count(1);
1151
call p_verify_reprepare_count(0);
1153
--echo # Test 24-f: column DEFAULT
1155
alter table t1 change c c int DEFAULT 20;
1158
call p_verify_reprepare_count(1);
1160
call p_verify_reprepare_count(0);
1162
--echo # Test 24-g: number of keys
1163
create unique index t1_a_idx on t1 (a);
1166
call p_verify_reprepare_count(1);
1168
call p_verify_reprepare_count(0);
1170
--echo # Test 24-h: changing index uniqueness
1172
drop index t1_a_idx on t1;
1173
create index t1_a_idx on t1 (a);
1176
call p_verify_reprepare_count(1);
1178
call p_verify_reprepare_count(0);
1183
deallocate prepare stmt;
1559
1185
--echo =====================================================================
1560
1186
--echo Testing reported bugs
1561
1187
--echo =====================================================================
1564
# Bug#12093 (SP not found on second PS execution if another thread drops
1565
# other SP in between)
1569
drop table if exists table_12093;
1570
drop function if exists func_12093;
1571
drop function if exists func_12093_unrelated;
1572
drop procedure if exists proc_12093;
1575
connect (con1,localhost,root,,);
1579
create table table_12093(a int);
1583
create function func_12093()
1586
return (select count(*) from table_12093);
1589
create procedure proc_12093(a int)
1591
select * from table_12093;
1596
create function func_12093_unrelated() returns int return 2;
1597
create procedure proc_12093_unrelated() begin end;
1599
prepare stmt_sf from 'select func_12093();';
1600
prepare stmt_sp from 'call proc_12093(func_12093())';
1609
drop function func_12093_unrelated;
1610
drop procedure proc_12093_unrelated;
1614
# previously, failed with --error 1305
1617
# previously, failed with --error 1305
1621
# previously, failed with --error 1305
1624
# previously, failed with --error 1305
1628
deallocate prepare stmt_sf;
1629
deallocate prepare stmt_sp;
1633
drop table table_12093;
1634
drop function func_12093;
1635
drop procedure proc_12093;
1638
# Bug#21294 (executing a prepared statement that executes a stored function
1639
# which was recreat)
1643
drop function if exists func_21294;
1646
create function func_21294() returns int return 10;
1648
prepare stmt from "select func_21294()";
1652
drop function func_21294;
1653
create function func_21294() returns int return 10;
1655
# might pass or fail, implementation dependent
1659
drop function func_21294;
1660
create function func_21294() returns int return 20;
1662
set @expected = @expected + 1;
1666
deallocate prepare stmt;
1667
drop function func_21294;
1670
# Bug#27420 (A combination of PS and view operations cause error + assertion
1190
--echo # Bug#27420 A combination of PS and view operations cause
1191
--echo # error + assertion on shutdown
1674
1194
--disable_warnings
1675
1195
drop table if exists t_27420_100;
1807
1302
create table v_27690_1 as select * from t_27690_1;
1808
1303
create table v_27690_2 as select * from t_27690_1;
1810
prepare stmt from 'select * from v_27690_1, v_27690_2';
1305
prepare stmt from "select * from v_27690_1, v_27690_2";
1817
1310
drop table v_27690_1;
1819
1312
--error ER_NO_SUCH_TABLE
1823
1315
--error ER_NO_SUCH_TABLE
1317
call p_verify_reprepare_count(0);
1827
1319
create view v_27690_1 as select A.a, A.b from t_27690_1 A, t_27690_1 B;
1829
set @expected = @expected + 1;
1322
call p_verify_reprepare_count(1);
1324
call p_verify_reprepare_count(0);
1835
deallocate prepare stmt;
1836
1326
drop table t_27690_1;
1837
1327
drop view v_27690_1;
1838
1328
drop table v_27690_2;
1329
deallocate prepare stmt;
1331
--echo #=====================================================================
1333
--echo # Bug#21294 Executing a prepared statement that executes
1334
--echo # a stored function which was recreat
1337
create function f1() returns int return 10;
1339
prepare stmt from "select f1()";
1343
create function f1() returns int return 10;
1345
# might pass or fail, implementation dependent
1349
create function f1() returns int return 20;
1352
call p_verify_reprepare_count(2);
1355
deallocate prepare stmt;
1358
--echo # Bug#12093 SP not found on second PS execution if another thread drops
1359
--echo # other SP in between
1362
drop table if exists t_12093;
1363
drop function if exists f_12093;
1364
drop function if exists f_12093_unrelated;
1365
drop procedure if exists p_12093;
1368
create table t_12093 (a int);
1370
create function f_12093() returns int return (select count(*) from t_12093);
1372
create procedure p_12093(a int) select * from t_12093;
1374
create function f_12093_unrelated() returns int return 2;
1375
create procedure p_12093_unrelated() begin end;
1377
prepare stmt_sf from 'select f_12093();';
1378
prepare stmt_sp from 'call p_12093(f_12093())';
1383
connect (con1,localhost,root,,);
1386
drop function f_12093_unrelated;
1387
drop procedure p_12093_unrelated;
1391
--echo # XXX: used to be a bug
1393
--echo # XXX: used to be a bug
1396
--echo # XXX: used to be a bug
1398
--echo # XXX: used to be a bug
1400
call p_verify_reprepare_count(2);
1405
drop function f_12093;
1406
drop procedure p_12093;
1407
deallocate prepare stmt_sf;
1408
deallocate prepare stmt_sp;
1411
--echo =====================================================================
1412
--echo Ensure that metadata validation is performed for every type of
1413
--echo SQL statement where it is needed.
1414
--echo =====================================================================
1417
--echo # SQLCOM_SELECT
1421
#drop table if exists t1;
1423
#create table t1 (a int);
1424
#prepare stmt from "select 1 as res from dual where (1) in (select * from t1)";
1426
#create table t1 (x int);
1429
#deallocate prepare stmt;
1430
#call p_verify_reprepare_count(1);
1433
--echo # SQLCOM_CREATE_TABLE
1437
drop table if exists t1;
1438
drop table if exists t2;
1440
create table t1 (a int);
1441
prepare stmt from 'create table t2 as select * from t1';
1447
call p_verify_reprepare_count(0);
1448
# Base table with name of table to be created exists
1449
--error ER_TABLE_EXISTS_ERROR
1451
call p_verify_reprepare_count(1);
1452
--error ER_TABLE_EXISTS_ERROR
1454
call p_verify_reprepare_count(0);
1456
# Temporary table with name of table to be created exists
1457
create temporary table t2 (a int);
1458
--error ER_TABLE_EXISTS_ERROR
1460
call p_verify_reprepare_count(1);
1461
--error ER_TABLE_EXISTS_ERROR
1463
call p_verify_reprepare_count(0);
1464
drop temporary table t2;
1466
call p_verify_reprepare_count(0);
1469
call p_verify_reprepare_count(0);
1471
# View with name of table to be created exists
1473
# We cannot print the error message because it contains a random filename.
1474
# Example: 1050: Table '<some_path>/var/tmp/#sql_6979_0' already exists
1475
# Therefore we mangle it via
1476
# "--error ER_TABLE_EXISTS_ERROR,9999" (9999 is currently not used)
1477
# to "Got one of the listed errors".
1478
create view t2 as select 1;
1479
--error ER_TABLE_EXISTS_ERROR,9999
1481
call p_verify_reprepare_count(1);
1482
--error ER_TABLE_EXISTS_ERROR,9999
1484
call p_verify_reprepare_count(0);
1487
# Table to be used recreated (drop,create) with different layout
1488
create table t1 (x varchar(20));
1490
call p_verify_reprepare_count(1);
1494
call p_verify_reprepare_count(0);
1496
# Table to be used has a modified (alter table) layout
1497
alter table t1 add column y decimal(10,3);
1499
call p_verify_reprepare_count(1);
1503
call p_verify_reprepare_count(0);
1505
deallocate prepare stmt;
1506
--echo # XXX: no validation of the first table in case of
1507
--echo # CREATE TEMPORARY TABLE. This is a shortcoming of the current code,
1508
--echo # but since validation is not strictly necessary, nothing is done
1510
--echo # Will be fixed as part of work on Bug#21431 "Incomplete support of
1511
--echo # temporary tables"
1512
create table t1 (a int);
1513
insert into t1 (a) values (1);
1514
prepare stmt from "create temporary table if not exists t2 as select * from t1";
1523
create temporary table t2 (a varchar(10));
1526
call p_verify_reprepare_count(0);
1528
create table t1 (x int);
1530
call p_verify_reprepare_count(1);
1532
call p_verify_reprepare_count(0);
1534
drop temporary table t2;
1536
deallocate prepare stmt;
1538
create table t1 (a int);
1539
prepare stmt from "create table t2 like t1";
1541
call p_verify_reprepare_count(0);
1544
call p_verify_reprepare_count(0);
1546
# Table to be used does not exist
1548
--error ER_NO_SUCH_TABLE
1550
call p_verify_reprepare_count(0);
1551
--error ER_NO_SUCH_TABLE
1553
call p_verify_reprepare_count(0);
1554
# Table to be used recreated (drop,create) with different layout
1555
create table t1 (x char(17));
1557
call p_verify_reprepare_count(1);
1560
call p_verify_reprepare_count(0);
1562
# Table to be used has a modified (alter table) layout
1563
alter table t1 add column y time;
1565
call p_verify_reprepare_count(1);
1569
call p_verify_reprepare_count(0);
1572
deallocate prepare stmt;
1576
--echo # SQLCOM_UPDATE
1580
drop table if exists t1, t2;
1582
create table t1 (a int);
1583
create table t2 (a int);
1584
prepare stmt from "update t2 set a=a+1 where (1) in (select * from t1)";
1587
create table t1 (x int);
1590
deallocate prepare stmt;
1593
--echo # SQLCOM_INSERT
1597
drop table if exists t1, t2;
1599
create table t1 (a int);
1600
create table t2 (a int);
1601
prepare stmt from "insert into t2 set a=((1) in (select * from t1))";
1604
create table t1 (x int);
1608
deallocate prepare stmt;
1611
--echo # SQLCOM_INSERT_SELECT
1615
drop table if exists t1, t2;
1617
create table t1 (a int);
1618
create table t2 (a int);
1619
prepare stmt from "insert into t2 select * from t1";
1622
create table t1 (x int);
1625
deallocate prepare stmt;
1628
--echo # SQLCOM_REPLACE
1632
drop table if exists t1, t2;
1634
create table t1 (a int);
1635
create table t2 (a int);
1636
prepare stmt from "replace t2 set a=((1) in (select * from t1))";
1639
create table t1 (x int);
1642
deallocate prepare stmt;
1645
--echo # SQLCOM_REPLACE_SELECT
1649
drop table if exists t1, t2;
1651
create table t1 (a int);
1652
create table t2 (a int);
1653
prepare stmt from "replace t2 select * from t1";
1656
create table t1 (x int);
1659
deallocate prepare stmt;
1662
--echo # SQLCOM_DELETE
1666
drop table if exists t1, t2;
1668
create table t1 (a int);
1669
create table t2 (a int);
1670
prepare stmt from "delete from t2 where (1) in (select * from t1)";
1673
create table t1 (x int);
1676
deallocate prepare stmt;
1679
--echo # SQLCOM_DELETE_MULTI
1683
drop table if exists t1, t2, t3;
1685
create table t1 (a int);
1686
create table t2 (a int);
1687
create table t3 (a int);
1688
prepare stmt from "delete t2, t3 from t2, t3 where (1) in (select * from t1)";
1691
create table t1 (x int);
1693
drop table t1, t2, t3;
1694
deallocate prepare stmt;
1697
--echo # SQLCOM_UPDATE_MULTI
1701
drop table if exists t1, t2, t3;
1703
create table t1 (a int);
1704
create table t2 (a int);
1705
create table t3 (a int);
1706
prepare stmt from "update t2, t3 set t3.a=t2.a, t2.a=null where (1) in (select * from t1)";
1708
create table t1 (x int);
1710
drop table t1, t2, t3;
1711
deallocate prepare stmt;
1712
--echo # Intermediate results: 8 SQLCOMs tested, 8 automatic reprepares
1713
call p_verify_reprepare_count(8);
1716
--echo # SQLCOM_LOAD
1720
drop table if exists t1;
1722
create table t1 (a varchar(20));
1723
--error ER_UNSUPPORTED_PS
1724
prepare stmt from "load data infile '../std_data_ln/words.dat' into table t1";
1728
--echo # SQLCOM_SHOW_DATABASES
1732
drop table if exists t1;
1734
create table t1 (a int);
1735
prepare stmt from "show databases where (1) in (select * from t1)";
1738
create table t1 (x int);
1741
deallocate prepare stmt;
1744
--echo # SQLCOM_SHOW_TABLES
1748
drop table if exists t1;
1750
create table t1 (a int);
1751
prepare stmt from "show tables where (1) in (select * from t1)";
1754
create table t1 (x int);
1757
deallocate prepare stmt;
1760
--echo # SQLCOM_SHOW_FIELDS
1764
drop table if exists t1;
1766
create table t1 (a int);
1767
prepare stmt from "show fields from t1 where (1) in (select * from t1)";
1770
create table t1 (x int);
1773
deallocate prepare stmt;
1776
--echo # SQLCOM_SHOW_KEYS
1780
drop table if exists t1;
1782
create table t1 (a int);
1783
prepare stmt from "show keys from t1 where (1) in (select * from t1)";
1786
create table t1 (x int);
1789
deallocate prepare stmt;
1792
--echo # SQLCOM_SHOW_VARIABLES
1796
drop table if exists t1;
1798
create table t1 (a int);
1799
prepare stmt from "show variables where (1) in (select * from t1)";
1802
create table t1 (x int);
1805
deallocate prepare stmt;
1808
--echo # SQLCOM_SHOW_STATUS
1812
drop table if exists t1;
1814
create table t1 (a int);
1815
prepare stmt from "show status where (1) in (select * from t1)";
1818
create table t1 (x int);
1821
deallocate prepare stmt;
1824
--echo # SQLCOM_SHOW_ENGINE_STATUS, SQLCOM_SHOW_ENGINE_LOGS,
1825
--echo # SQLCOM_SHOW_ENGINE_MUTEX, SQLCOM_SHOW_PROCESSLIST
1828
--echo # Currently can not have a where clause, need to be covered
1832
drop table if exists t1;
1834
create table t1 (a int);
1835
--error ER_PARSE_ERROR
1836
prepare stmt from "show engine all status where (1) in (select * from t1)";
1837
--error ER_PARSE_ERROR
1838
prepare stmt from "show engine all logs where (1) in (select * from t1)";
1839
--error ER_PARSE_ERROR
1840
prepare stmt from "show engine all mutex where (1) in (select * from t1)";
1841
--error ER_PARSE_ERROR
1842
prepare stmt from "show processlist where (1) in (select * from t1)";
1846
--echo # SQLCOM_SHOW_CHARSETS
1850
drop table if exists t1;
1852
create table t1 (a int);
1853
prepare stmt from "show charset where (1) in (select * from t1)";
1856
create table t1 (x int);
1859
deallocate prepare stmt;
1862
--echo # SQLCOM_SHOW_COLLATIONS
1866
drop table if exists t1;
1868
create table t1 (a int);
1869
prepare stmt from "show collation where (1) in (select * from t1)";
1872
create table t1 (x int);
1875
deallocate prepare stmt;
1878
--echo # SQLCOM_SHOW_TABLE_STATUS
1882
drop table if exists t1;
1884
create table t1 (a int);
1885
prepare stmt from "show table status where (1) in (select * from t1)";
1888
create table t1 (x int);
1891
deallocate prepare stmt;
1894
--echo # SQLCOM_SHOW_TRIGGERS
1898
drop table if exists t1;
1900
create table t1 (a int);
1901
prepare stmt from "show triggers where (1) in (select * from t1)";
1904
create table t1 (x int);
1907
deallocate prepare stmt;
1910
--echo # SQLCOM_SHOW_OPEN_TABLES
1914
drop table if exists t1;
1916
create table t1 (a int);
1917
prepare stmt from "show open tables where (1) in (select * from t1)";
1920
create table t1 (x int);
1923
deallocate prepare stmt;
1926
--echo # SQLCOM_SHOW_STATUS_PROC
1930
drop table if exists t1;
1932
create table t1 (a int);
1933
prepare stmt from "show procedure status where (1) in (select * from t1)";
1936
create table t1 (x int);
1939
deallocate prepare stmt;
1942
--echo # SQLCOM_SHOW_STATUS_FUNC
1946
drop table if exists t1;
1948
create table t1 (a int);
1949
prepare stmt from "show function status where (1) in (select * from t1)";
1952
create table t1 (x int);
1955
deallocate prepare stmt;
1958
--echo # SQLCOM_SHOW_EVENTS
1961
--echo # Please see this test in ps.test, it requires not_embedded.inc
1965
--echo # SQLCOM_SET_OPTION
1969
drop table if exists t1;
1971
create table t1 (a int);
1972
prepare stmt from "set @a=((1) in (select * from t1))";
1975
create table t1 (x int);
1978
deallocate prepare stmt;
1985
drop table if exists t1;
1987
create table t1 (a int);
1988
prepare stmt from "do ((1) in (select * from t1))";
1991
create table t1 (x int);
1994
deallocate prepare stmt;
1997
--echo # SQLCOM_CALL
2001
drop table if exists t1;
2002
drop procedure if exists p1;
2004
create procedure p1(a int) begin end;
2005
create table t1 (a int);
2006
prepare stmt from "call p1((1) in (select * from t1))";
2009
create table t1 (x int);
2013
deallocate prepare stmt;
2016
--echo # SQLCOM_CREATE_VIEW
2020
drop table if exists t1;
2021
drop view if exists v1;
2023
create table t1 (a int);
2024
prepare stmt from "create view v1 as select * from t1";
2028
create table t1 (x int);
2032
deallocate prepare stmt;
2033
--echo # Intermediate result: number of reprepares matches the number
2035
call p_verify_reprepare_count(17);
2038
--echo # SQLCOM_ALTER_VIEW
2042
drop view if exists v1;
2044
create view v1 as select 1;
2045
--error ER_UNSUPPORTED_PS
2046
prepare stmt from "alter view v1 as select 2";
2052
drop temporary table if exists t1, t2, t3;
2053
drop table if exists t1, t2, t3, v1, v2;
2054
drop procedure if exists p_verify_reprepare_count;
2055
drop procedure if exists p1;
2056
drop function if exists f1;
2057
drop view if exists v1, v2;
added
removed
mysql-test/t/ps_ddl.test
