1
-- source include/not_embedded.inc
2
-- source include/have_log_bin.inc
4
# SQL Syntax for Prepared Statements test
7
drop table if exists t1,t2,t3,t4;
9
# Avoid wrong warnings if mysql_client_test fails
10
drop database if exists client_test_db;
18
insert into t1 values (1,'one');
19
insert into t1 values (2,'two');
20
insert into t1 values (3,'three');
21
insert into t1 values (4,'four');
25
prepare stmt1 from 'select * from t1 where a <= ?';
26
execute stmt1 using @a;
28
execute stmt1 using @a;
30
# non-existant statement
32
deallocate prepare no_such_statement;
37
# Nesting ps commands is not allowed:
38
--error ER_UNSUPPORTED_PS
39
prepare stmt2 from 'prepare nested_stmt from "select 1"';
41
--error ER_UNSUPPORTED_PS
42
prepare stmt2 from 'execute stmt1';
44
--error ER_UNSUPPORTED_PS
45
prepare stmt2 from 'deallocate prepare z';
48
prepare stmt3 from 'insert into t1 values (?,?)';
49
set @arg1=5, @arg2='five';
50
execute stmt3 using @arg1, @arg2;
51
select * from t1 where a>3;
54
prepare stmt4 from 'update t1 set a=? where b=?';
55
set @arg1=55, @arg2='five';
56
execute stmt4 using @arg1, @arg2;
57
select * from t1 where a>3;
60
prepare stmt4 from 'create table t2 (a int)';
62
prepare stmt4 from 'drop table t2';
65
# Do something that will cause error
69
# placeholders in result field names.
70
prepare stmt5 from 'select ? + a from t1';
72
execute stmt5 using @a;
74
execute stmt5 using @no_such_var;
78
execute stmt5 using @nullvar;
81
execute stmt5 using @nullvar2;
83
# Check that multiple SQL statements are disabled inside PREPARE
85
prepare stmt6 from 'select 1; select2';
88
prepare stmt6 from 'insert into t1 values (5,"five"); select2';
90
# This shouldn't parse
92
explain prepare stmt6 from 'insert into t1 values (5,"five"); select2';
99
insert into t2 values (0);
103
prepare stmt1 from 'select 1 FROM t2 where a=?' ;
104
execute stmt1 using @arg00 ;
106
# prepare using variables:
108
prepare stmt1 from @nosuchvar;
112
prepare stmt1 from @ivar;
116
prepare stmt1 from @fvar;
119
deallocate prepare stmt3;
120
deallocate prepare stmt4;
121
deallocate prepare stmt5;
124
# Bug #4105: Server crash on attempt to prepare a statement with character
127
PREPARE stmt1 FROM "select _utf8 'A' collate utf8_bin = ?";
129
EXECUTE stmt1 USING @var;
130
DEALLOCATE PREPARE stmt1;
133
# BUG#3486: FOUND_ROWS() fails inside stored procedure [and prepared statement]
135
create table t1 (id int);
136
prepare stmt1 from "select FOUND_ROWS()";
137
select SQL_CALC_FOUND_ROWS * from t1;
140
insert into t1 values (1);
141
select SQL_CALC_FOUND_ROWS * from t1;
146
deallocate prepare stmt1;
154
c1 tinyint, c2 smallint, c3 mediumint, c4 int,
155
c5 integer, c6 bigint, c7 float, c8 double,
156
c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4),
157
c13 date, c14 datetime, c15 timestamp, c16 time,
158
c17 year, c18 bit, c19 bool, c20 char,
159
c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext,
160
c25 blob, c26 text, c27 mediumblob, c28 mediumtext,
161
c29 longblob, c30 longtext, c31 enum('one', 'two', 'three'),
162
c32 set('monday', 'tuesday', 'wednesday')
164
create table t2 like t1;
166
set @stmt= ' explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25 ' ;
167
prepare stmt1 from @stmt ;
170
explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25;
171
deallocate prepare stmt1;
175
# parameters from variables (for field creation)
178
prepare stmt1 from ' create table t1 (m int) as select 1 as m ' ;
182
prepare stmt1 from ' create table t1 (m int) as select ? as m ' ;
183
execute stmt1 using @arg00;
185
deallocate prepare stmt1;
189
# eq() for parameters
191
create table t1 (id int(10) unsigned NOT NULL default '0',
192
name varchar(64) NOT NULL default '',
193
PRIMARY KEY (id), UNIQUE KEY `name` (`name`));
194
insert into t1 values (1,'1'),(2,'2'),(3,'3'),(4,'4'),(5,'5'),(6,'6'),(7,'7');
195
prepare stmt1 from 'select name from t1 where id=? or id=?';
197
execute stmt1 using @id1, @id2;
198
select name from t1 where id=1 or id=6;
199
deallocate prepare stmt1;
203
# SHOW TABLE STATUS test
205
create table t1 ( a int primary key, b varchar(30)) engine = MYISAM ;
206
prepare stmt1 from ' show table status from test like ''t1%'' ';
207
--replace_column 8 4294967295 12 # 13 # 14 #
209
--replace_column 8 4294967295 12 # 13 # 14 #
210
show table status from test like 't1%' ;
211
deallocate prepare stmt1 ;
215
# Bug#4912 "mysqld crashs in case a statement is executed a second time":
216
# negation elimination should work once and not break prepared statements
219
create table t1(a varchar(2), b varchar(3));
220
prepare stmt1 from "select a, b from t1 where (not (a='aa' and b < 'zzz'))";
223
deallocate prepare stmt1;
227
# Bug#5034 "prepared "select 1 into @arg15", second execute crashes
229
# Check that descendands of select_result can be reused in prepared
230
# statements or are correctly created and deleted on each execute
233
--let $outfile=$MYSQLTEST_VARDIR/tmp/f1.txt
235
--remove_file $outfile
237
prepare stmt1 from "select 1 into @var";
240
prepare stmt1 from "create table t1 select 1 as i";
244
prepare stmt1 from "insert into t1 select i from t1";
247
--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
248
eval prepare stmt1 from "select * from t1 into outfile '$outfile'";
250
deallocate prepare stmt1;
253
--remove_file $outfile
256
# BUG#5242 "Prepared statement names are case sensitive"
258
prepare stmt1 from 'select 1';
259
prepare STMT1 from 'select 2';
261
deallocate prepare StMt1;
264
deallocate prepare Stmt1;
266
# also check that statement names are in right charset.
268
prepare `ü` from 'select 1234';
272
deallocate prepare `�`;
277
# BUG#4368 "select * from t1 where a like ?" crashes server if a is in utf8
279
# Check that Item converting latin1 to utf8 (for LIKE function) is created
280
# in memory of prepared statement.
283
create table t1 (a varchar(10)) charset=utf8;
284
insert into t1 (a) values ('yahoo');
285
set character_set_connection=latin1;
286
prepare stmt from 'select a from t1 where a like ?';
288
execute stmt using @var;
289
execute stmt using @var;
290
deallocate prepare stmt;
294
# BUG#5510 "inserting Null in AutoIncrement primary key Column Fails"
295
# (prepared statements)
296
# The cause: misuse of internal MySQL 'Field' API.
299
create table t1 (a bigint(20) not null primary key auto_increment);
300
insert into t1 (a) values (null);
302
prepare stmt from "insert into t1 (a) values (?)";
304
execute stmt using @var;
308
# check the same for timestamps
310
create table t1 (a timestamp not null);
311
prepare stmt from "insert into t1 (a) values (?)";
312
execute stmt using @var;
316
deallocate prepare stmt;
320
# BUG#5688 "Upgraded 4.1.5 Server seg faults" # (prepared statements)
321
# The test case speaks for itself.
322
# Just another place where we used wrong memory root for Items created
323
# during statement prepare.
325
prepare stmt from "select 'abc' like convert('abc' using utf8)";
328
deallocate prepare stmt;
331
# BUG#5748 "Prepared statement with BETWEEN and bigint values crashes
332
# mysqld". Just another place where an item tree modification must be
335
create table t1 ( a bigint );
336
prepare stmt from 'select a from t1 where a between ? and ?';
338
execute stmt using @a, @a;
339
execute stmt using @a, @a;
340
execute stmt using @a, @a;
342
deallocate prepare stmt;
345
# Bug #5987 subselect in bool function crashes server (prepared statements):
346
# don't overwrite transformed subselects with old arguments of a bool
349
create table t1 (a int);
350
prepare stmt from "select * from t1 where 1 > (1 in (SELECT * FROM t1))";
355
deallocate prepare stmt;
358
# Test case for Bug#6042 "constants propogation works only once (prepared
359
# statements): check that the query plan changes whenever we change
362
create table t1 (a int, b int);
363
insert into t1 (a, b) values (1,1), (1,2), (2,1), (2,2);
365
"explain select * from t1 where t1.a=2 and t1.a=t1.b and t1.b > 1 + ?";
367
--replace_column 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 -
368
execute stmt using @v;
370
--replace_column 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 -
371
execute stmt using @v;
373
--replace_column 1 - 2 - 3 - 4 - 5 - 6 - 7 - 8 -
374
execute stmt using @v;
376
deallocate prepare stmt;
379
# A test case for Bug#5985 prepare stmt from "select rand(?)" crashes
380
# server. Check that Item_func_rand is prepared-statements friendly.
382
create table t1 (a int);
383
insert into t1 (a) values (1), (2), (3), (4);
384
set @precision=10000000000;
385
--replace_column 1 - 3 -
387
cast(rand(10)*@precision as unsigned integer) from t1;
390
cast(rand(10)*@precision as unsigned integer),
391
cast(rand(?)*@precision as unsigned integer) from t1";
393
--replace_column 1 - 3 -
394
execute stmt using @var;
397
execute stmt using @var;
400
execute stmt using @var;
402
deallocate prepare stmt;
405
# A test case for Bug#6050 "EXECUTE stmt reports ambiguous fieldnames with
406
# identical tables from different schemata"
407
# Check that field name resolving in prepared statements works OK.
409
create database mysqltest1;
410
create table t1 (a int);
411
create table mysqltest1.t1 (a int);
412
select * from t1, mysqltest1.t1;
413
prepare stmt from "select * from t1, mysqltest1.t1";
418
drop table mysqltest1.t1;
419
drop database mysqltest1;
420
deallocate prepare stmt;
421
select '1.1' as a, '1.2' as a UNION SELECT '2.1', '2.2';
423
"select '1.1' as a, '1.2' as a UNION SELECT '2.1', '2.2'";
427
deallocate prepare stmt;
430
# Test CREATE TABLE ... SELECT (Bug #6094)
432
create table t1 (a int);
433
insert into t1 values (1),(2),(3);
434
create table t2 select * from t1;
435
prepare stmt FROM 'create table t2 select * from t1';
445
deallocate prepare stmt;
448
# Bug#6088 "FOUND_ROWS returns wrong values for prepared statements when
451
create table t1 (a int);
452
insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
453
prepare stmt from "select sql_calc_found_rows * from t1 limit 2";
460
deallocate prepare stmt;
464
# Bug#6047 "permission problem when executing mysql_stmt_execute with derived
468
CREATE TABLE t1 (N int, M tinyint);
469
INSERT INTO t1 VALUES (1,0),(1,0),(2,0),(2,0),(3,0);
470
PREPARE stmt FROM 'UPDATE t1 AS P1 INNER JOIN (SELECT N FROM t1 GROUP BY N HAVING COUNT(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2';
472
DEALLOCATE PREPARE stmt;
476
# Bug#6297 "prepared statement, wrong handling of <parameter> IS NULL"
477
# Test that placeholders work with IS NULL/IS NOT NULL clauses.
479
prepare stmt from "select ? is null, ? is not null, ?";
480
select @no_such_var is null, @no_such_var is not null, @no_such_var;
481
execute stmt using @no_such_var, @no_such_var, @no_such_var;
483
select @var is null, @var is not null, @var;
484
execute stmt using @var, @var, @var;
486
select @var is null, @var is not null, @var;
487
execute stmt using @var, @var, @var;
490
# Bug#6873 "PS, having with subquery, crash during execute"
491
# check that if we modify having subtree, we update JOIN->having pointer
493
create table t1 (pnum char(3));
494
create table t2 (pnum char(3));
495
prepare stmt from "select pnum from t2 having pnum in (select 'p1' from t1)";
499
deallocate prepare stmt;
504
# Bug#19399 "Stored Procedures 'Lost Connection' when dropping/creating
506
# Check that multi-delete tables are also cleaned up before re-execution.
509
drop table if exists t1;
510
create temporary table if not exists t1 (a1 int);
512
# exact delete syntax is essential
513
prepare stmt from "delete t1 from t1 where (cast(a1/3 as unsigned) * 3) = a1";
514
drop temporary table t1;
515
create temporary table if not exists t1 (a1 int);
516
# the server crashed on the next statement without the fix
518
drop temporary table t1;
519
create temporary table if not exists t1 (a1 int);
520
# the problem was in memory corruption: repeat the test just in case
522
drop temporary table t1;
523
create temporary table if not exists t1 (a1 int);
525
drop temporary table t1;
526
deallocate prepare stmt;
528
# Bug#6102 "Server crash with prepared statement and blank after
530
# ensure that stored functions are cached when preparing a statement
531
# before we open tables
533
create table t1 (a varchar(20));
534
insert into t1 values ('foo');
535
prepare stmt FROM 'SELECT char_length (a) FROM t1';
536
-- error ER_SP_DOES_NOT_EXIST
537
prepare stmt2 FROM 'SELECT not_a_function (a) FROM t1';
541
# Bug #6089: FOUND_ROWS returns wrong values when no table/view is used
544
prepare stmt from "SELECT SQL_CALC_FOUND_ROWS 'foo' UNION SELECT 'bar' LIMIT 0";
549
deallocate prepare stmt;
552
# Bug#9096 "select doesn't return all matched records if prepared statements
554
# The bug was is bad co-operation of the optimizer's algorithm which determines
555
# which keys can be used to execute a query, constants propagation
556
# part of the optimizer and parameter markers used by prepared statements.
558
drop table if exists t1;
559
create table t1 (c1 int(11) not null, c2 int(11) not null,
560
primary key (c1,c2), key c2 (c2), key c1 (c1));
562
insert into t1 values (200887, 860);
563
insert into t1 values (200887, 200887);
565
select * from t1 where (c1=200887 and c2=200887) or c2=860;
568
"select * from t1 where (c1=200887 and c2=200887) or c2=860";
571
"select * from t1 where (c1=200887 and c2=?) or c2=?";
572
set @a=200887, @b=860;
573
# this query did not return all matching rows
574
execute stmt using @a, @b;
575
deallocate prepare stmt;
580
# Bug#9777 - another occurrence of the problem stated in Bug#9096:
581
# we can not compare basic constants by their names, because a placeholder
582
# is a basic constant while his name is always '?'
586
id bigint(20) not null auto_increment,
587
code varchar(20) character set utf8 collate utf8_bin not null default '',
588
company_name varchar(250) character set utf8 collate utf8_bin default null,
589
setup_mode tinyint(4) default null,
590
start_date datetime default null,
591
primary key (id), unique key code (code)
595
id bigint(20) not null auto_increment,
596
email varchar(250) character set utf8 collate utf8_bin default null,
597
name varchar(250) character set utf8 collate utf8_bin default null,
598
t1_id bigint(20) default null,
599
password varchar(250) character set utf8 collate utf8_bin default null,
600
primary_contact tinyint(4) not null default '0',
601
email_opt_in tinyint(4) not null default '1',
602
primary key (id), unique key email (email), key t1_id (t1_id),
603
constraint t2_fk1 foreign key (t1_id) references t1 (id)
606
insert into t1 values
607
(1, 'demo', 'demo s', 0, current_date()),
608
(2, 'code2', 'name 2', 0, current_date()),
609
(3, 'code3', 'name 3', 0, current_date());
611
insert into t2 values
612
(2, 'email1', 'name1', 3, 'password1', 0, 0),
613
(3, 'email2', 'name1', 1, 'password2', 1, 0),
614
(5, 'email3', 'name3', 2, 'password3', 0, 0);
616
prepare stmt from 'select t2.id from t2, t1 where (t1.id=? and t2.t1_id=t1.id)';
618
execute stmt using @a;
620
select t2.id from t2, t1 where (t1.id=1 and t2.t1_id=t1.id);
622
deallocate prepare stmt;
626
# Bug#11060 "Server crashes on calling stored procedure with INSERT SELECT
627
# UNION SELECT" aka "Server crashes on re-execution of prepared INSERT ...
628
# SELECT with UNION".
630
create table t1 (id int);
631
prepare stmt from "insert into t1 (id) select id from t1 union select id from t1";
634
deallocate prepare stmt;
637
# Bug#11458 "Prepared statement with subselects return random data":
638
# drop PARAM_TABLE_BIT from the list of tables used by a subquery
641
id int(11) unsigned not null primary key auto_increment,
642
partner_id varchar(35) not null,
643
t1_status_id int(10) unsigned
646
insert into t1 values ("1", "partner1", "10"), ("2", "partner2", "10"),
647
("3", "partner3", "10"), ("4", "partner4", "10");
650
id int(11) unsigned not null default '0',
651
t1_line_id int(11) unsigned not null default '0',
652
article_id varchar(20),
653
sequence int(11) not null default '0',
654
primary key (id,t1_line_id)
657
insert into t2 values ("1", "1", "sup", "0"), ("2", "1", "sup", "1"),
658
("2", "2", "sup", "2"), ("2", "3", "sup", "3"),
659
("2", "4", "imp", "4"), ("3", "1", "sup", "0"),
660
("4", "1", "sup", "0");
663
id int(11) not null default '0',
664
preceeding_id int(11) not null default '0',
665
primary key (id,preceeding_id)
669
user_id varchar(50) not null,
670
article_id varchar(20) not null,
671
primary key (user_id,article_id)
674
insert into t4 values("nicke", "imp");
677
'select distinct t1.partner_id
678
from t1 left join t3 on t1.id = t3.id
679
left join t1 pp on pp.id = t3.preceeding_id
684
where pl_inner.id = t1.id
685
and pl_inner.sequence <= (
686
select min(sequence) from t2 pl_seqnr
687
where pl_seqnr.id = t1.id
691
where t4.article_id = pl_inner.article_id
697
having count(pp.id) = 0';
698
set @user_id = 'nicke';
700
execute stmt using @user_id, @id;
701
execute stmt using @user_id, @id;
702
deallocate prepare stmt;
703
drop table t1, t2, t3, t4;
705
# Bug#9379: make sure that Item::collation is reset when one sets
706
# a parameter marker from a string variable.
708
prepare stmt from 'select ?=?';
711
execute stmt using @a, @b;
712
execute stmt using @a, @b;
714
execute stmt using @a, @b;
717
execute stmt using @a, @b;
718
deallocate prepare stmt;
720
# Bug#11299 "prepared statement makes wrong SQL syntax in binlog which stops
721
# replication": check that errouneous queries with placeholders are not
724
create table t1 (a int);
726
prepare stmt from "select ??";
728
prepare stmt from "select ?FROM t1";
730
prepare stmt from "select FROM t1 WHERE?=1";
732
prepare stmt from "update t1 set a=a+?WHERE 1";
733
--disable_ps_protocol
743
# Bug#9359 "Prepared statements take snapshot of system vars at PREPARE
746
prepare stmt from "select @@time_zone";
748
set @@time_zone:='Japan';
750
prepare stmt from "select @@tx_isolation";
752
set transaction isolation level read committed;
754
set transaction isolation level serializable;
756
set @@tx_isolation=default;
758
deallocate prepare stmt;
761
# Bug#14410 "Crash in Enum or Set type in CREATE TABLE and PS/SP"
763
# Part I. Make sure the typelib for ENUM is created in the statement memory
765
prepare stmt from "create temporary table t1 (letter enum('','a','b','c')
773
# Part II. Make sure that when the default value is converted to UTF-8,
774
# the new item is # created in the statement memory root.
776
prepare stmt from "create table t1 (a enum('test') default 'test')
786
deallocate prepare stmt;
789
# A test case for Bug#12734 "prepared statement may return incorrect result
790
# set for a select SQL request": test that canDoTurboBM is reset for each
791
# execute of a prepared statement.
794
word_id mediumint(8) unsigned not null default '0',
795
formatted varchar(20) not null default ''
798
insert into t1 values
799
(80,'pendant'), (475,'pretendants'), (989,'tendances'),
800
(1019,'cependant'),(1022,'abondance'),(1205,'independants'),
801
(13,'lessiver'),(25,'lambiner'),(46,'situer'),(71,'terminer'),
804
select count(*) from t1 where formatted like '%NDAN%';
805
select count(*) from t1 where formatted like '%ER';
806
prepare stmt from "select count(*) from t1 where formatted like ?";
808
execute stmt using @like;
810
execute stmt using @like;
812
execute stmt using @like;
814
execute stmt using @like;
815
deallocate prepare stmt;
819
# Bug#13134 "Length of VARCHAR() utf8 column is increasing when table is
820
# recreated with PS/SP"
823
prepare stmt from 'create table t1 (a varchar(10) character set utf8)';
826
insert into t1 (a) values (repeat('a', 20));
828
select length(a) from t1;
832
insert into t1 (a) values (repeat('a', 20));
834
# Check that the data is truncated to the same length
835
select length(a) from t1;
837
deallocate prepare stmt;
840
# Bug#16248 "WHERE (col1,col2) IN ((?,?)) gives wrong results":
841
# check that ROW implementation is reexecution-friendly.
843
create table t1 (col1 integer, col2 integer);
844
insert into t1 values(100,100),(101,101),(102,102),(103,103);
845
prepare stmt from 'select col1, col2 from t1 where (col1, col2) in ((?,?))';
847
execute stmt using @a,@b;
849
execute stmt using @a,@b;
851
execute stmt using @a,@b;
853
execute stmt using @a,@b;
854
deallocate prepare stmt;
858
# Bug#16365 Prepared Statements: DoS with too many open statements
859
# Check that the limit @@max_prpeared_stmt_count works.
861
# This is also the test for bug#23159 prepared_stmt_count should be
865
set @old_max_prepared_stmt_count= @@max_prepared_stmt_count;
867
# Disable prepared statement protocol: in this test we set
868
# @@max_prepared_stmt_count to 0 or 1 and would like to test the limit
871
--disable_ps_protocol
873
# A. Check that the new variables are present in SHOW VARIABLES and
876
show variables like 'max_prepared_stmt_count';
877
show status like 'prepared_stmt_count';
879
# B. Check that the new system variable is selectable.
881
select @@max_prepared_stmt_count;
883
# C. Check that max_prepared_stmt_count is settable (global only).
885
set global max_prepared_stmt_count=-1;
886
select @@max_prepared_stmt_count;
887
set global max_prepared_stmt_count=10000000000000000;
888
select @@max_prepared_stmt_count;
889
set global max_prepared_stmt_count=default;
890
select @@max_prepared_stmt_count;
891
--error ER_GLOBAL_VARIABLE
892
set @@max_prepared_stmt_count=1;
893
--error ER_GLOBAL_VARIABLE
894
set max_prepared_stmt_count=1;
895
--error ER_GLOBAL_VARIABLE
896
set local max_prepared_stmt_count=1;
897
# set to a reasonable limit works
898
set global max_prepared_stmt_count=1;
899
select @@max_prepared_stmt_count;
901
# D. Check that the variables actually work.
903
set global max_prepared_stmt_count=0;
904
select @@max_prepared_stmt_count;
905
show status like 'prepared_stmt_count';
906
--error ER_MAX_PREPARED_STMT_COUNT_REACHED
907
prepare stmt from "select 1";
908
show status like 'prepared_stmt_count';
909
set global max_prepared_stmt_count=1;
910
prepare stmt from "select 1";
911
show status like 'prepared_stmt_count';
912
--error ER_MAX_PREPARED_STMT_COUNT_REACHED
913
prepare stmt1 from "select 1";
914
show status like 'prepared_stmt_count';
915
deallocate prepare stmt;
916
show status like 'prepared_stmt_count';
918
# E. Check that we can prepare a statement with the same name
919
# successfully, without hitting the limit.
921
prepare stmt from "select 1";
922
show status like 'prepared_stmt_count';
923
prepare stmt from "select 2";
924
show status like 'prepared_stmt_count';
926
# F. We can set the max below the current count. In this case no new
927
# statements should be allowed to prepare.
929
show status like 'prepared_stmt_count';
930
select @@max_prepared_stmt_count;
931
set global max_prepared_stmt_count=0;
932
--error ER_MAX_PREPARED_STMT_COUNT_REACHED
933
prepare stmt from "select 1";
934
# Result: the old statement is deallocated, the new is not created.
935
--error ER_UNKNOWN_STMT_HANDLER
937
show status like 'prepared_stmt_count';
938
--error ER_MAX_PREPARED_STMT_COUNT_REACHED
939
prepare stmt from "select 1";
940
show status like 'prepared_stmt_count';
942
# G. Show that the variables are up to date even after a connection with all
943
# statements in it was terminated.
945
set global max_prepared_stmt_count=3;
946
select @@max_prepared_stmt_count;
947
show status like 'prepared_stmt_count';
948
prepare stmt from "select 1";
950
connect (con1,localhost,root,,);
952
# Switch to connection con1
954
let $con1_id=`SELECT CONNECTION_ID()`;
956
prepare stmt from "select 2";
957
prepare stmt1 from "select 3";
958
--error ER_MAX_PREPARED_STMT_COUNT_REACHED
959
prepare stmt2 from "select 4";
961
--error ER_MAX_PREPARED_STMT_COUNT_REACHED
962
prepare stmt2 from "select 4";
963
select @@max_prepared_stmt_count;
964
show status like 'prepared_stmt_count';
966
# Disconnect connection con1 and switch to default connection
970
# Wait for the connection con1 to die
971
let $wait_condition=SELECT COUNT(*)=0 FROM information_schema.processlist WHERE id=$con1_id;
972
--source include/wait_condition.inc
974
deallocate prepare stmt;
976
select @@max_prepared_stmt_count;
977
show status like 'prepared_stmt_count';
979
# Restore the old value.
981
set global max_prepared_stmt_count= @old_max_prepared_stmt_count;
986
# Bug#19399 "Stored Procedures 'Lost Connection' when dropping/creating
988
# Check that multi-delete tables are also cleaned up before re-execution.
991
drop table if exists t1;
992
create temporary table if not exists t1 (a1 int);
994
# exact delete syntax is essential
995
prepare stmt from "delete t1 from t1 where (cast(a1/3 as unsigned) * 3) = a1";
996
drop temporary table t1;
997
create temporary table if not exists t1 (a1 int);
998
# the server crashed on the next statement without the fix
1000
drop temporary table t1;
1001
create temporary table if not exists t1 (a1 int);
1002
# the problem was in memory corruption: repeat the test just in case
1004
drop temporary table t1;
1005
create temporary table if not exists t1 (a1 int);
1007
drop temporary table t1;
1008
deallocate prepare stmt;
1012
# BUG#22085: Crash on the execution of a prepared statement that
1013
# uses an IN subquery with aggregate functions in HAVING
1017
ID int(10) unsigned NOT NULL auto_increment,
1018
Member_ID varchar(15) NOT NULL default '',
1019
Action varchar(12) NOT NULL,
1020
Action_Date datetime NOT NULL,
1021
Track varchar(15) default NULL,
1022
User varchar(12) default NULL,
1023
Date_Updated timestamp NOT NULL default CURRENT_TIMESTAMP on update
1026
KEY Action (Action),
1027
KEY Action_Date (Action_Date)
1030
INSERT INTO t1(Member_ID, Action, Action_Date, Track) VALUES
1031
('111111', 'Disenrolled', '2006-03-01', 'CAD' ),
1032
('111111', 'Enrolled', '2006-03-01', 'CAD' ),
1033
('111111', 'Disenrolled', '2006-07-03', 'CAD' ),
1034
('222222', 'Enrolled', '2006-03-07', 'CAD' ),
1035
('222222', 'Enrolled', '2006-03-07', 'CHF' ),
1036
('222222', 'Disenrolled', '2006-08-02', 'CHF' ),
1037
('333333', 'Enrolled', '2006-03-01', 'CAD' ),
1038
('333333', 'Disenrolled', '2006-03-01', 'CAD' ),
1039
('444444', 'Enrolled', '2006-03-01', 'CAD' ),
1040
('555555', 'Disenrolled', '2006-03-01', 'CAD' ),
1041
('555555', 'Enrolled', '2006-07-21', 'CAD' ),
1042
('555555', 'Disenrolled', '2006-03-01', 'CHF' ),
1043
('666666', 'Enrolled', '2006-02-09', 'CAD' ),
1044
('666666', 'Enrolled', '2006-05-12', 'CHF' ),
1045
('666666', 'Disenrolled', '2006-06-01', 'CAD' );
1048
"SELECT GROUP_CONCAT(Track SEPARATOR ', ') FROM t1
1049
WHERE Member_ID=? AND Action='Enrolled' AND
1050
(Track,Action_Date) IN (SELECT Track, MAX(Action_Date) FROM t1
1053
HAVING Track>='CAD' AND
1054
MAX(Action_Date)>'2006-03-01')";
1056
EXECUTE STMT USING @id,@id;
1058
EXECUTE STMT USING @id,@id;
1060
DEALLOCATE PREPARE STMT;
1064
# BUG#21354: (COUNT(*) = 1) not working in SELECT inside prepared
1068
DROP TABLE IF EXISTS t1;
1071
CREATE TABLE t1 (i INT, INDEX(i));
1072
INSERT INTO t1 VALUES (1);
1074
PREPARE stmt FROM "SELECT (COUNT(i) = 1), COUNT(i) FROM t1 WHERE i = ?";
1076
EXECUTE stmt USING @a;
1078
EXECUTE stmt USING @a;
1080
EXECUTE stmt USING @a;
1082
PREPARE stmt FROM "SELECT (AVG(i) = 1), AVG(i) FROM t1 WHERE i = ?";
1084
EXECUTE stmt USING @a;
1086
EXECUTE stmt USING @a;
1088
EXECUTE stmt USING @a;
1090
PREPARE stmt FROM "SELECT (VARIANCE(i) = 1), VARIANCE(i) FROM t1 WHERE i = ?";
1092
EXECUTE stmt USING @a;
1094
EXECUTE stmt USING @a;
1096
EXECUTE stmt USING @a;
1098
PREPARE stmt FROM "SELECT (STDDEV(i) = 1), STDDEV(i) FROM t1 WHERE i = ?";
1100
EXECUTE stmt USING @a;
1102
EXECUTE stmt USING @a;
1104
EXECUTE stmt USING @a;
1106
PREPARE stmt FROM "SELECT (BIT_OR(i) = 1), BIT_OR(i) FROM t1 WHERE i = ?";
1108
EXECUTE stmt USING @a;
1110
EXECUTE stmt USING @a;
1112
EXECUTE stmt USING @a;
1114
PREPARE stmt FROM "SELECT (BIT_AND(i) = 1), BIT_AND(i) FROM t1 WHERE i = ?";
1116
EXECUTE stmt USING @a;
1118
EXECUTE stmt USING @a;
1120
EXECUTE stmt USING @a;
1122
PREPARE stmt FROM "SELECT (BIT_XOR(i) = 1), BIT_XOR(i) FROM t1 WHERE i = ?";
1124
EXECUTE stmt USING @a;
1126
EXECUTE stmt USING @a;
1128
EXECUTE stmt USING @a;
1130
DEALLOCATE PREPARE stmt;
1134
# Bug#19182: CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work
1135
# from stored procedure.
1137
# The cause of a bug was that cached LEX::create_list was modified,
1138
# and then together with LEX::key_list was reset.
1141
DROP TABLE IF EXISTS t1, t2;
1144
CREATE TABLE t1 (i INT);
1147
FROM "CREATE TABLE t2 (i INT, j INT, KEY (i), KEY(j)) SELECT i FROM t1";
1154
# Check that on second execution we don't loose 'j' column and the keys
1155
# on 'i' and 'j' columns.
1159
DEALLOCATE PREPARE st_19182;
1163
# Bug #22060 "ALTER TABLE x AUTO_INCREMENT=y in SP crashes server"
1165
# Code which implemented CREATE/ALTER TABLE and CREATE DATABASE
1166
# statement modified HA_CREATE_INFO structure in LEX, making these
1167
# statements PS/SP-unsafe (their re-execution might have resulted
1168
# in incorrect results).
1171
drop database if exists mysqltest;
1172
drop table if exists t1, t2;
1174
# CREATE TABLE and CREATE TABLE ... SELECT
1175
create database mysqltest character set utf8;
1176
prepare stmt1 from "create table mysqltest.t1 (c char(10))";
1177
prepare stmt2 from "create table mysqltest.t2 select 'test'";
1180
show create table mysqltest.t1;
1181
show create table mysqltest.t2;
1182
drop table mysqltest.t1;
1183
drop table mysqltest.t2;
1184
alter database mysqltest character set latin1;
1187
show create table mysqltest.t1;
1188
show create table mysqltest.t2;
1189
drop database mysqltest;
1190
deallocate prepare stmt1;
1191
deallocate prepare stmt2;
1193
# CREATE TABLE with DATA DIRECTORY option
1197
eval prepare stmt from "create table t1 (c char(10)) data directory='$MYSQLTEST_VARDIR/tmp'";
1201
# DATA DIRECTORY option does not always work: if the operating
1202
# system does not support symlinks, have_symlinks option is automatically
1204
# In this case DATA DIRECTORY is silently ignored when
1205
# creating a table, and is not output by SHOW CREATE TABLE.
1207
--disable_result_log
1208
show create table t1;
1212
--disable_result_log
1213
show create table t1;
1217
deallocate prepare stmt;
1221
# Bug #27937: crash on the second execution for prepared statement
1222
# from UNION with ORDER BY an expression containing RAND()
1225
CREATE TABLE t1(a int);
1226
INSERT INTO t1 VALUES (2), (3), (1);
1229
'(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a';
1234
DEALLOCATE PREPARE st1;
1239
# Bug #32137: prepared statement crash with str_to_date in update clause
1241
create table t1 (a int, b tinyint);
1242
prepare st1 from 'update t1 set b= (str_to_date(a, a))';
1244
deallocate prepare st1;
1247
--echo End of 4.1 tests.
1249
############################# 5.0 tests start ################################
1252
# Bug#6102 "Server crash with prepared statement and blank after
1254
# ensure that stored functions are cached when preparing a statement
1255
# before we open tables
1257
create table t1 (a varchar(20));
1258
insert into t1 values ('foo');
1259
prepare stmt FROM 'SELECT char_length (a) FROM t1';
1260
-- error ER_SP_DOES_NOT_EXIST
1261
prepare stmt2 FROM 'SELECT not_a_function (a) FROM t1';
1265
# Bug#8115: equality propagation and prepared statements
1268
create table t1 (a char(3) not null, b char(3) not null,
1269
c char(3) not null, primary key (a, b, c));
1270
create table t2 like t1;
1274
"select t1.a from (t1 left outer join t2 on t2.a=1 and t1.b=t2.b)
1282
"select t1.a, t1.b, t1.c, t2.a, t2.b, t2.c from
1283
(t1 left outer join t2 on t2.a=? and t1.b=t2.b)
1284
left outer join t2 t3 on t3.a=? where t1.a=?";
1286
set @a:=1, @b:=1, @c:=1;
1288
execute stmt using @a, @b, @c;
1289
execute stmt using @a, @b, @c;
1290
execute stmt using @a, @b, @c;
1292
deallocate prepare stmt;
1298
# Bug#9383: INFORMATION_SCHEMA.COLUMNS, JOIN, Crash, prepared statement
1301
eval SET @aux= "SELECT COUNT(*)
1302
FROM INFORMATION_SCHEMA.COLUMNS A,
1303
INFORMATION_SCHEMA.COLUMNS B
1304
WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
1305
AND A.TABLE_NAME = B.TABLE_NAME
1306
AND A.COLUMN_NAME = B.COLUMN_NAME AND
1307
A.TABLE_NAME = 'user'";
1309
let $exec_loop_count= 3;
1310
eval prepare my_stmt from @aux;
1311
while ($exec_loop_count)
1313
eval execute my_stmt;
1314
dec $exec_loop_count;
1316
deallocate prepare my_stmt;
1318
# Test CALL in prepared mode
1321
drop procedure if exists p1|
1322
drop table if exists t1|
1324
create table t1 (id int)|
1325
insert into t1 values(1)|
1326
create procedure p1(a int, b int)
1329
select max(id)+1 into c from t1;
1330
insert into t1 select a+b;
1331
insert into t1 select a-b;
1332
insert into t1 select a-c;
1335
prepare stmt from "call p1(?, ?)"|
1336
execute stmt using @a, @b|
1337
execute stmt using @a, @b|
1339
deallocate prepare stmt|
1346
# Bug#7306 LIMIT ?, ? and also WL#1785 " Prepared statements: implement
1347
# support for placeholders in LIMIT clause."
1348
# Add basic test coverage for the feature.
1350
create table t1 (a int);
1351
insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
1352
prepare stmt from "select * from t1 limit ?, ?";
1353
set @offset=0, @limit=1;
1354
execute stmt using @offset, @limit;
1355
select * from t1 limit 0, 1;
1356
set @offset=3, @limit=2;
1357
execute stmt using @offset, @limit;
1358
select * from t1 limit 3, 2;
1359
prepare stmt from "select * from t1 limit ?";
1360
execute stmt using @limit;
1362
prepare stmt from "select * from t1 where a in (select a from t1 limit ?)";
1363
prepare stmt from "select * from t1 union all select * from t1 limit ?, ?";
1366
execute stmt using @offset, @limit;
1367
prepare stmt from "(select * from t1 limit ?, ?) union all
1368
(select * from t1 limit ?, ?) order by a limit ?";
1369
execute stmt using @offset, @limit, @offset, @limit, @limit;
1372
deallocate prepare stmt;
1376
# (Crash on a PS including a subquery which is a select from a simple view)
1378
CREATE TABLE b12651_T1(a int) ENGINE=MYISAM;
1379
CREATE TABLE b12651_T2(b int) ENGINE=MYISAM;
1380
CREATE VIEW b12651_V1 as SELECT b FROM b12651_T2;
1382
PREPARE b12651 FROM 'SELECT 1 FROM b12651_T1 WHERE a IN (SELECT b FROM b12651_V1)';
1385
DROP VIEW b12651_V1;
1386
DROP TABLE b12651_T1, b12651_T2;
1387
DEALLOCATE PREPARE b12651;
1392
# Bug #14956: ROW_COUNT() returns incorrect result after EXECUTE of prepared
1395
create table t1 (id int);
1396
prepare ins_call from "insert into t1 (id) values (1)";
1402
# BUG#16474: SP crashed MySQL
1403
# (when using "order by localvar", where 'localvar' is just that.
1404
# The actual bug test is in sp.test, this is just testing that we get the
1405
# expected result for prepared statements too, i.e. place holders work as
1406
# textual substitution. If it's a single integer, it works as the (deprecated)
1407
# "order by column#", otherwise it's an expression.
1409
create table t1 (a int, b int);
1410
insert into t1 (a,b) values (2,8),(1,9),(3,7);
1412
# Will order by index
1413
prepare stmt from "select * from t1 order by ?";
1415
execute stmt using @a;
1417
execute stmt using @a;
1419
execute stmt using @a;
1420
deallocate prepare stmt;
1422
select * from t1 order by 1;
1424
# Will not order by index.
1425
prepare stmt from "select * from t1 order by ?+1";
1427
execute stmt using @a;
1429
execute stmt using @a;
1430
deallocate prepare stmt;
1432
select * from t1 order by 1+1;
1437
# Bug#19308 "REPAIR/OPTIMIZE/ANALYZE supported in SP but not in PS".
1438
# Add test coverage for the added commands.
1440
create table t1 (a int);
1441
create table t2 like t1;
1442
create table t3 like t2;
1443
prepare stmt from "repair table t1";
1446
prepare stmt from "optimize table t1";
1449
prepare stmt from "analyze table t1";
1452
prepare stmt from "repair table t1, t2, t3";
1455
prepare stmt from "optimize table t1, t2, t3";
1458
prepare stmt from "analyze table t1, t2, t3";
1461
prepare stmt from "repair table t1, t4, t3";
1464
prepare stmt from "optimize table t1, t3, t4";
1467
prepare stmt from "analyze table t4, t1";
1470
deallocate prepare stmt;
1471
drop table t1, t2, t3;
1474
# Bug#17199 "Table not found" error occurs if the query contains a call
1475
# to a function from another database.
1476
# Test prepared statements- related behaviour.
1479
# ALTER TABLE RENAME and Prepared Statements: wrong DB name buffer was used
1480
# in ALTER ... RENAME which caused memory corruption in prepared statements.
1481
# No need to fix this problem in 4.1 as ALTER TABLE is not allowed in
1482
# Prepared Statements in 4.1.
1484
create database mysqltest_long_database_name_to_thrash_heap;
1486
create table t1 (i int);
1487
prepare stmt from "alter table test.t1 rename t1";
1488
use mysqltest_long_database_name_to_thrash_heap;
1490
show tables like 't1';
1491
prepare stmt from "alter table test.t1 rename t1";
1494
show tables like 't1';
1495
use mysqltest_long_database_name_to_thrash_heap;
1496
show tables like 't1';
1497
deallocate prepare stmt;
1499
# Check that a prepared statement initializes its current database at
1500
# PREPARE, and then works correctly even if the current database has been
1503
use mysqltest_long_database_name_to_thrash_heap;
1504
# Necessary for preparation of INSERT/UPDATE/DELETE to succeed
1505
prepare stmt_create from "create table t1 (i int)";
1506
prepare stmt_insert from "insert into t1 (i) values (1)";
1507
prepare stmt_update from "update t1 set i=2";
1508
prepare stmt_delete from "delete from t1 where i=2";
1509
prepare stmt_select from "select * from t1";
1510
prepare stmt_alter from "alter table t1 add column (b int)";
1511
prepare stmt_alter1 from "alter table t1 drop column b";
1512
prepare stmt_analyze from "analyze table t1";
1513
prepare stmt_optimize from "optimize table t1";
1514
prepare stmt_show from "show tables like 't1'";
1515
prepare stmt_truncate from "truncate table t1";
1516
prepare stmt_drop from "drop table t1";
1517
# Drop the table that was used to prepare INSERT/UPDATE/DELETE: we will
1518
# create a new one by executing stmt_create
1520
# Switch the current database
1522
# Check that all prepared statements operate on the database that was
1524
execute stmt_create;
1525
# should return empty set
1526
show tables like 't1';
1527
use mysqltest_long_database_name_to_thrash_heap;
1528
show tables like 't1';
1530
execute stmt_insert;
1531
select * from mysqltest_long_database_name_to_thrash_heap.t1;
1532
execute stmt_update;
1533
select * from mysqltest_long_database_name_to_thrash_heap.t1;
1534
execute stmt_delete;
1535
execute stmt_select;
1537
show columns from mysqltest_long_database_name_to_thrash_heap.t1;
1538
execute stmt_alter1;
1539
show columns from mysqltest_long_database_name_to_thrash_heap.t1;
1540
execute stmt_analyze;
1541
execute stmt_optimize;
1543
execute stmt_truncate;
1545
show tables like 't1';
1546
use mysqltest_long_database_name_to_thrash_heap;
1547
show tables like 't1';
1549
# Attempt a statement PREPARE when there is no current database:
1550
# is expected to return an error.
1552
drop database mysqltest_long_database_name_to_thrash_heap;
1553
--error ER_NO_DB_ERROR
1554
prepare stmt_create from "create table t1 (i int)";
1555
--error ER_NO_DB_ERROR
1556
prepare stmt_insert from "insert into t1 (i) values (1)";
1557
--error ER_NO_DB_ERROR
1558
prepare stmt_update from "update t1 set i=2";
1559
--error ER_NO_DB_ERROR
1560
prepare stmt_delete from "delete from t1 where i=2";
1561
--error ER_NO_DB_ERROR
1562
prepare stmt_select from "select * from t1";
1563
--error ER_NO_DB_ERROR
1564
prepare stmt_alter from "alter table t1 add column (b int)";
1565
--error ER_NO_DB_ERROR
1566
prepare stmt_alter1 from "alter table t1 drop column b";
1567
--error ER_NO_DB_ERROR
1568
prepare stmt_analyze from "analyze table t1";
1569
--error ER_NO_DB_ERROR
1570
prepare stmt_optimize from "optimize table t1";
1571
--error ER_NO_DB_ERROR
1572
prepare stmt_show from "show tables like 't1'";
1573
--error ER_NO_DB_ERROR
1574
prepare stmt_truncate from "truncate table t1";
1575
--error ER_NO_DB_ERROR
1576
prepare stmt_drop from "drop table t1";
1578
# The above has automatically deallocated all our statements.
1580
# Attempt to CREATE a temporary table when no DB used: it should fail
1581
# This proves that no table can be used without explicit specification of
1582
# its database if there is no current database.
1584
--error ER_NO_DB_ERROR
1585
create temporary table t1 (i int);
1587
# Restore the old environemnt
1593
# BUG#21166: Prepared statement causes signal 11 on second execution
1595
# Changes in an item tree done by optimizer weren't properly
1596
# registered and went unnoticed, which resulted in preliminary freeing
1600
DROP TABLE IF EXISTS t1, t2, t3;
1603
CREATE TABLE t1 (i BIGINT, j BIGINT);
1604
CREATE TABLE t2 (i BIGINT);
1605
CREATE TABLE t3 (i BIGINT, j BIGINT);
1607
PREPARE stmt FROM "SELECT * FROM t1 JOIN t2 ON (t2.i = t1.i)
1608
LEFT JOIN t3 ON ((t3.i, t3.j) = (t1.i, t1.j))
1612
EXECUTE stmt USING @a;
1613
EXECUTE stmt USING @a;
1615
DEALLOCATE PREPARE stmt;
1616
DROP TABLE IF EXISTS t1, t2, t3;
1620
# BUG#21081: SELECT inside stored procedure returns wrong results
1623
DROP TABLE IF EXISTS t1, t2;
1626
CREATE TABLE t1 (i INT KEY);
1627
CREATE TABLE t2 (i INT);
1629
INSERT INTO t1 VALUES (1), (2);
1630
INSERT INTO t2 VALUES (1);
1632
PREPARE stmt FROM "SELECT t2.i FROM t1 LEFT JOIN t2 ON t2.i = t1.i
1636
EXECUTE stmt USING @arg;
1638
EXECUTE stmt USING @arg;
1640
EXECUTE stmt USING @arg;
1642
DEALLOCATE PREPARE stmt;
1647
# BUG#20327: Marking of a wrong field leads to a wrong result on select with
1648
# view, prepared statement and subquery.
1650
CREATE TABLE t1 (i INT);
1651
CREATE VIEW v1 AS SELECT * FROM t1;
1653
INSERT INTO t1 VALUES (1), (2);
1655
let $query = SELECT t1.i FROM t1 JOIN v1 ON t1.i = v1.i
1656
WHERE EXISTS (SELECT * FROM t1 WHERE v1.i = 1);
1658
eval PREPARE stmt FROM "$query";
1659
# Statement execution should return '1'.
1661
# Check re-execution.
1664
DEALLOCATE PREPARE stmt;
1670
# BUG#21856: Prepared Statments: crash if bad create
1673
DROP PROCEDURE IF EXISTS p1;
1676
let $iterations= 100;
1678
--disable_result_log
1681
--error ER_PARSE_ERROR
1682
PREPARE stmt FROM "CREATE PROCEDURE p1()";
1689
# Bug 19764: SHOW commands end up in the slow log as table scans
1693
prepare sq from 'show status like "slow_queries"';
1695
prepare no_index from 'select 1 from information_schema.tables limit 1';
1699
deallocate prepare no_index;
1700
deallocate prepare sq;
1704
# Bug 25027: query with a single-row non-correlated subquery
1705
# and IS NULL predicate
1708
CREATE TABLE t1 (a int);
1709
INSERT INTO t1 VALUES (1), (2);
1710
CREATE TABLE t2 (b int);
1711
INSERT INTO t2 VALUES (NULL);
1713
SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL;
1714
PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL';
1717
DEALLOCATE PREPARE stmt;
1719
PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2 limit ?) IS NULL';
1721
EXECUTE stmt USING @arg;
1722
DEALLOCATE PREPARE stmt;
1726
# Bug#4968 "Stored procedure crash if cursor opened on altered table"
1727
# The bug is not repeatable any more after the fix for
1728
# Bug#15217 "Bug #15217 Using a SP cursor on a table created with PREPARE
1729
# fails with weird error", however ALTER TABLE is not re-execution friendly
1730
# and that caused a valgrind warning. Check that the warning is gone.
1733
drop table if exists t1;
1735
create table t1 (s1 char(20));
1736
prepare stmt from "alter table t1 modify s1 int";
1740
deallocate prepare stmt;
1743
# Bug#6895 "Prepared Statements: ALTER TABLE DROP COLUMN does nothing"
1746
drop table if exists t1;
1748
create table t1 (a int, b int);
1749
prepare s_6895 from "alter table t1 drop column b";
1751
show columns from t1;
1753
create table t1 (a int, b int);
1755
show columns from t1;
1757
create table t1 (a int, b int);
1759
show columns from t1;
1760
deallocate prepare s_6895;
1764
# Bug #22060 "ALTER TABLE x AUTO_INCREMENT=y in SP crashes server"
1766
# 5.0 part of the test.
1770
create table t1 (i int primary key auto_increment) comment='comment for table t1';
1771
create table t2 (i int, j int, k int);
1772
prepare stmt from "alter table t1 auto_increment=100";
1774
show create table t1;
1775
# Let us trash table-cache's memory
1779
show create table t1;
1780
deallocate prepare stmt;
1782
# 5.1 part of the test.
1784
set @old_character_set_server= @@character_set_server;
1785
set @@character_set_server= latin1;
1786
prepare stmt from "create database mysqltest_1";
1788
show create database mysqltest_1;
1789
drop database mysqltest_1;
1790
set @@character_set_server= utf8;
1792
show create database mysqltest_1;
1793
drop database mysqltest_1;
1794
deallocate prepare stmt;
1795
set @@character_set_server= @old_character_set_server;
1799
# BUG#24491 "using alias from source table in insert ... on duplicate key"
1802
drop tables if exists t1;
1804
create table t1 (id int primary key auto_increment, value varchar(10));
1805
insert into t1 (id, value) values (1, 'FIRST'), (2, 'SECOND'), (3, 'THIRD');
1806
# Let us prepare INSERT ... SELECT ... ON DUPLICATE KEY UPDATE statement
1807
# which in its ON DUPLICATE KEY clause erroneously tries to assign value
1808
# to a column which is mentioned only in SELECT part.
1809
prepare stmt from "insert into t1 (id, value) select * from (select 4 as i, 'FOURTH' as v) as y on duplicate key update v = 'DUP'";
1810
# Both first and second attempts to execute it should fail
1811
--error ER_BAD_FIELD_ERROR
1813
--error ER_BAD_FIELD_ERROR
1815
deallocate prepare stmt;
1816
# And now the same test for more complex case which is more close
1817
# to the one that was reported originally.
1818
prepare stmt from "insert into t1 (id, value) select * from (select 4 as id, 'FOURTH' as value) as y on duplicate key update y.value = 'DUP'";
1819
--error ER_BAD_FIELD_ERROR
1821
--error ER_BAD_FIELD_ERROR
1823
deallocate prepare stmt;
1827
# Bug #28509: strange behaviour: passing a decimal value to PS
1829
prepare stmt from "create table t1 select ?";
1831
execute stmt using @a;
1832
show create table t1;
1836
# Bug#33798: prepared statements improperly handle large unsigned ints
1839
drop table if exists t1;
1841
create table t1 (a bigint unsigned, b bigint(20) unsigned);
1842
prepare stmt from "insert into t1 values (?,?)";
1843
set @a= 9999999999999999;
1844
set @b= 14632475938453979136;
1845
insert into t1 values (@a, @b);
1846
select * from t1 where a = @a and b = @b;
1847
execute stmt using @a, @b;
1848
select * from t1 where a = @a and b = @b;
1849
deallocate prepare stmt;
1853
# Bug#32890 Crash after repeated create and drop of tables and views
1857
drop view if exists v1;
1858
drop table if exists t1;
1861
create table t1 (a int, b int);
1862
insert into t1 values (1,1), (2,2), (3,3);
1863
insert into t1 values (3,1), (1,2), (2,3);
1865
prepare stmt from "create view v1 as select * from t1";
1868
create table t1 (a int, b int);
1871
show create view v1;
1874
prepare stmt from "create view v1 (c,d) as select a,b from t1";
1876
show create view v1;
1880
deallocate prepare stmt;
1881
show create view v1;
1885
prepare stmt from "create view v1 (c) as select b+1 from t1";
1887
show create view v1;
1891
deallocate prepare stmt;
1892
show create view v1;
1896
prepare stmt from "create view v1 (c,d,e,f) as select a,b,a in (select a+2 from t1), a = all (select a from t1) from t1";
1898
show create view v1;
1902
deallocate prepare stmt;
1903
show create view v1;
1907
prepare stmt from "create or replace view v1 as select 1";
1909
show create view v1;
1912
show create view v1;
1913
deallocate prepare stmt;
1914
show create view v1;
1918
prepare stmt from "create view v1 as select 1, 1";
1920
show create view v1;
1924
deallocate prepare stmt;
1925
show create view v1;
1929
prepare stmt from "create view v1 (x) as select a from t1 where a > 1";
1931
show create view v1;
1935
deallocate prepare stmt;
1936
show create view v1;
1940
prepare stmt from "create view v1 as select * from `t1` `b`";
1942
show create view v1;
1946
deallocate prepare stmt;
1947
show create view v1;
1951
prepare stmt from "create view v1 (a,b,c) as select * from t1";
1952
--error ER_VIEW_WRONG_LIST
1954
--error ER_VIEW_WRONG_LIST
1956
deallocate prepare stmt;
1959
create temporary table t1 (a int, b int);
1961
prepare stmt from "create view v1 as select * from t1";
1962
--error ER_VIEW_SELECT_TMPTABLE
1964
--error ER_VIEW_SELECT_TMPTABLE
1966
deallocate prepare stmt;
1970
--error ER_NO_SUCH_TABLE
1971
prepare stmt from "create view v1 as select * from t1";
1972
--error ER_NO_SUCH_TABLE
1973
prepare stmt from "create view v1 as select * from `t1` `b`";
1976
# Bug#33851: Passing UNSIGNED param to EXECUTE returns ERROR 1210
1979
prepare stmt from "select ?";
1980
set @arg= 123456789.987654321;
1982
execute stmt using @arg;
1985
execute stmt using @arg;
1988
execute stmt using @arg;
1989
set @arg= cast(-12345.54321 as decimal(20, 10));
1991
execute stmt using @arg;
1992
deallocate prepare stmt;
1995
--echo # Bug#48508: Crash on prepared statement re-execution.
1997
create table t1(b int);
1998
insert into t1 values (0);
1999
create view v1 AS select 1 as a from t1 where b;
2000
prepare stmt from "select * from v1 where a";
2003
deallocate prepare stmt;
2007
create table t1(a bigint);
2008
create table t2(b tinyint);
2009
insert into t2 values (null);
2010
prepare stmt from "select 1 from t1 join t2 on a xor b where b > 1 and a =1";
2013
deallocate prepare stmt;
2019
--echo # Bug #49570: Assertion failed: !(order->used & map)
2020
--echo # on re-execution of prepared statement
2022
CREATE TABLE t1(a INT PRIMARY KEY);
2023
INSERT INTO t1 VALUES(0), (1);
2025
"SELECT 1 FROM t1 JOIN t1 t2 USING(a) GROUP BY t2.a, t1.a";
2029
DEALLOCATE PREPARE stmt;
2033
--echo End of 5.0 tests.
2036
# Bug #20665: All commands supported in Stored Procedures should work in
2037
# Prepared Statements
2039
create procedure proc_1() reset query cache;
2044
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2045
create function func_1() returns int deterministic begin reset query cache; return 1; end|
2046
create function func_1() returns int deterministic begin call proc_1(); return 1; end|
2048
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2049
select func_1(), func_1(), func_1() from dual;
2050
drop function func_1;
2051
drop procedure proc_1;
2052
prepare abc from "reset query cache";
2056
deallocate prepare abc;
2059
create procedure proc_1() reset master;
2061
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2062
create function func_1() returns int begin reset master; return 1; end|
2063
create function func_1() returns int begin call proc_1(); return 1; end|
2065
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2066
select func_1(), func_1(), func_1() from dual;
2067
drop function func_1;
2068
drop procedure proc_1;
2069
prepare abc from "reset master";
2073
deallocate prepare abc;
2076
create procedure proc_1() reset slave;
2081
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2082
create function func_1() returns int begin reset slave; return 1; end|
2083
create function func_1() returns int begin call proc_1(); return 1; end|
2085
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2086
select func_1(), func_1(), func_1() from dual;
2087
drop function func_1;
2088
drop procedure proc_1;
2089
prepare abc from "reset slave";
2093
deallocate prepare abc;
2096
create procedure proc_1(a integer) kill a;
2097
--error ER_NO_SUCH_THREAD
2099
--error ER_NO_SUCH_THREAD
2101
--error ER_NO_SUCH_THREAD
2103
drop procedure proc_1;
2105
create function func_1() returns int begin kill 0; return 1; end|
2107
--error ER_NO_SUCH_THREAD
2108
select func_1() from dual;
2109
--error ER_NO_SUCH_THREAD
2110
select func_1() from dual;
2111
--error ER_NO_SUCH_THREAD
2112
select func_1() from dual;
2113
drop function func_1;
2114
prepare abc from "kill 0";
2115
--error ER_NO_SUCH_THREAD
2117
--error ER_NO_SUCH_THREAD
2119
--error ER_NO_SUCH_THREAD
2121
deallocate prepare abc;
2124
create procedure proc_1() flush hosts;
2130
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2131
create function func_1() returns int begin flush hosts; return 1; end|
2132
create function func_1() returns int begin call proc_1(); return 1; end|
2134
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2135
select func_1(), func_1(), func_1() from dual;
2136
drop function func_1;
2137
drop procedure proc_1;
2138
prepare abc from "flush hosts";
2142
deallocate prepare abc;
2145
create procedure proc_1() flush privileges;
2150
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2151
create function func_1() returns int begin flush privileges; return 1; end|
2152
create function func_1() returns int begin call proc_1(); return 1; end|
2154
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2155
select func_1(), func_1(), func_1() from dual;
2156
drop function func_1;
2157
drop procedure proc_1;
2158
prepare abc from "flush privileges";
2159
deallocate prepare abc;
2162
create procedure proc_1() flush tables with read lock;
2170
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2171
create function func_1() returns int begin flush tables with read lock; return 1; end|
2172
create function func_1() returns int begin call proc_1(); return 1; end|
2174
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2175
select func_1(), func_1(), func_1() from dual;
2176
drop function func_1;
2177
drop procedure proc_1;
2178
prepare abc from "flush tables with read lock";
2182
deallocate prepare abc;
2186
create procedure proc_1() flush tables;
2191
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2192
create function func_1() returns int begin flush tables; return 1; end|
2193
create function func_1() returns int begin call proc_1(); return 1; end|
2195
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2196
select func_1(), func_1(), func_1() from dual;
2197
drop function func_1;
2198
drop procedure proc_1;
2199
prepare abc from "flush tables";
2203
deallocate prepare abc;
2206
create procedure proc_1() flush tables;
2208
show open tables from mysql;
2209
select Host, User from mysql.user limit 0;
2210
select Host, Db from mysql.host limit 0;
2211
show open tables from mysql;
2213
show open tables from mysql;
2214
select Host, User from mysql.user limit 0;
2215
select Host, Db from mysql.host limit 0;
2216
show open tables from mysql;
2218
show open tables from mysql;
2219
select Host, User from mysql.user limit 0;
2220
select Host, Db from mysql.host limit 0;
2221
show open tables from mysql;
2223
show open tables from mysql;
2224
select Host, User from mysql.user limit 0;
2225
select Host, Db from mysql.host limit 0;
2226
show open tables from mysql;
2229
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2230
create function func_1() returns int begin flush tables; return 1; end|
2231
create function func_1() returns int begin call proc_1(); return 1; end|
2233
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2234
select func_1(), func_1(), func_1() from dual;
2235
drop function func_1;
2236
drop procedure proc_1;
2238
# make the output deterministic:
2239
# the order used in SHOW OPEN TABLES
2240
# is too much implementation dependent
2241
--disable_ps_protocol
2243
select Host, User from mysql.user limit 0;
2244
select Host, Db from mysql.host limit 0;
2245
show open tables from mysql;
2246
--enable_ps_protocol
2248
prepare abc from "flush tables";
2250
show open tables from mysql;
2251
select Host, User from mysql.user limit 0;
2252
select Host, Db from mysql.host limit 0;
2253
show open tables from mysql;
2255
show open tables from mysql;
2256
select Host, User from mysql.user limit 0;
2257
select Host, Db from mysql.host limit 0;
2258
show open tables from mysql;
2260
show open tables from mysql;
2261
select Host, User from mysql.user limit 0;
2262
select Host, Db from mysql.host limit 0;
2263
show open tables from mysql;
2265
deallocate prepare abc;
2268
create procedure proc_1() flush logs;
2273
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2274
create function func_1() returns int begin flush logs; return 1; end|
2275
create function func_1() returns int begin call proc_1(); return 1; end|
2277
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2278
select func_1(), func_1(), func_1() from dual;
2279
drop function func_1;
2280
drop procedure proc_1;
2281
prepare abc from "flush logs";
2285
deallocate prepare abc;
2288
create procedure proc_1() flush status;
2293
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2294
create function func_1() returns int begin flush status; return 1; end|
2295
create function func_1() returns int begin call proc_1(); return 1; end|
2297
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2298
select func_1(), func_1(), func_1() from dual;
2299
drop function func_1;
2300
drop procedure proc_1;
2301
prepare abc from "flush status";
2305
deallocate prepare abc;
2308
create procedure proc_1() flush slave;
2313
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2314
create function func_1() returns int begin flush slave; return 1; end|
2315
create function func_1() returns int begin call proc_1(); return 1; end|
2317
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2318
select func_1(), func_1(), func_1() from dual;
2319
drop function func_1;
2320
drop procedure proc_1;
2321
prepare abc from "flush slave";
2325
deallocate prepare abc;
2328
create procedure proc_1() flush master;
2330
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2331
create function func_1() returns int begin flush master; return 1; end|
2332
create function func_1() returns int begin call proc_1(); return 1; end|
2334
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2335
select func_1(), func_1(), func_1() from dual;
2336
drop function func_1;
2337
drop procedure proc_1;
2338
prepare abc from "flush master";
2339
deallocate prepare abc;
2342
create procedure proc_1() flush des_key_file;
2347
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2348
create function func_1() returns int begin flush des_key_file; return 1; end|
2349
create function func_1() returns int begin call proc_1(); return 1; end|
2351
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2352
select func_1(), func_1(), func_1() from dual;
2353
drop function func_1;
2354
drop procedure proc_1;
2355
prepare abc from "flush des_key_file";
2359
deallocate prepare abc;
2362
create procedure proc_1() flush user_resources;
2367
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2368
create function func_1() returns int begin flush user_resources; return 1; end|
2369
create function func_1() returns int begin call proc_1(); return 1; end|
2371
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
2372
select func_1(), func_1(), func_1() from dual;
2373
drop function func_1;
2374
drop procedure proc_1;
2375
prepare abc from "flush user_resources";
2379
deallocate prepare abc;
2382
create procedure proc_1() start slave;
2383
drop procedure proc_1;
2385
create function func_1() returns int begin start slave; return 1; end|
2387
drop function func_1;
2388
prepare abc from "start slave";
2389
deallocate prepare abc;
2392
create procedure proc_1() stop slave;
2393
drop procedure proc_1;
2395
create function func_1() returns int begin stop slave; return 1; end|
2397
drop function func_1;
2398
prepare abc from "stop slave";
2399
deallocate prepare abc;
2402
create procedure proc_1() show binlog events;
2403
drop procedure proc_1;
2405
--error ER_SP_NO_RETSET
2406
create function func_1() returns int begin show binlog events; return 1; end|
2408
--error ER_SP_DOES_NOT_EXIST
2409
select func_1(), func_1(), func_1() from dual;
2410
--error ER_SP_DOES_NOT_EXIST
2411
drop function func_1;
2412
prepare abc from "show binlog events";
2413
deallocate prepare abc;
2416
create procedure proc_1() show slave status;
2417
drop procedure proc_1;
2419
--error ER_SP_NO_RETSET
2420
create function func_1() returns int begin show slave status; return 1; end|
2422
--error ER_SP_DOES_NOT_EXIST
2423
select func_1(), func_1(), func_1() from dual;
2424
--error ER_SP_DOES_NOT_EXIST
2425
drop function func_1;
2426
prepare abc from "show slave status";
2427
deallocate prepare abc;
2430
create procedure proc_1() show master status;
2431
drop procedure proc_1;
2433
--error ER_SP_NO_RETSET
2434
create function func_1() returns int begin show master status; return 1; end|
2436
--error ER_SP_DOES_NOT_EXIST
2437
select func_1(), func_1(), func_1() from dual;
2438
--error ER_SP_DOES_NOT_EXIST
2439
drop function func_1;
2440
prepare abc from "show master status";
2441
deallocate prepare abc;
2444
create procedure proc_1() show master logs;
2445
drop procedure proc_1;
2447
--error ER_SP_NO_RETSET
2448
create function func_1() returns int begin show master logs; return 1; end|
2450
--error ER_SP_DOES_NOT_EXIST
2451
select func_1(), func_1(), func_1() from dual;
2452
--error ER_SP_DOES_NOT_EXIST
2453
drop function func_1;
2454
prepare abc from "show master logs";
2455
deallocate prepare abc;
2458
create procedure proc_1() show events;
2462
drop procedure proc_1;
2464
--error ER_SP_NO_RETSET
2465
create function func_1() returns int begin show events; return 1; end|
2467
--error ER_SP_DOES_NOT_EXIST
2468
select func_1(), func_1(), func_1() from dual;
2469
--error ER_SP_DOES_NOT_EXIST
2470
drop function func_1;
2471
prepare abc from "show events";
2475
deallocate prepare abc;
2479
drop procedure if exists a;
2481
create procedure a() select 42;
2482
create procedure proc_1(a char(2)) show create procedure a;
2486
drop procedure proc_1;
2488
--error ER_SP_NO_RETSET
2489
create function func_1() returns int begin show create procedure a; return 1; end|
2491
--error ER_SP_DOES_NOT_EXIST
2492
select func_1(), func_1(), func_1() from dual;
2493
--error ER_SP_DOES_NOT_EXIST
2494
drop function func_1;
2495
prepare abc from "show create procedure a";
2499
deallocate prepare abc;
2504
drop function if exists a;
2506
create function a() returns int return 42+13;
2507
create procedure proc_1(a char(2)) show create function a;
2511
drop procedure proc_1;
2513
--error ER_SP_NO_RETSET
2514
create function func_1() returns int begin show create function a; return 1; end|
2516
--error ER_SP_DOES_NOT_EXIST
2517
select func_1(), func_1(), func_1() from dual;
2518
--error ER_SP_DOES_NOT_EXIST
2519
drop function func_1;
2520
prepare abc from "show create function a";
2524
deallocate prepare abc;
2529
drop table if exists tab1;
2531
create table tab1(a int, b char(1), primary key(a,b));
2532
create procedure proc_1() show create table tab1;
2536
drop procedure proc_1;
2538
--error ER_SP_NO_RETSET
2539
create function func_1() returns int begin show create table tab1; return 1; end|
2541
--error ER_SP_DOES_NOT_EXIST
2542
select func_1(), func_1(), func_1() from dual;
2543
--error ER_SP_DOES_NOT_EXIST
2544
drop function func_1;
2545
prepare abc from "show create table tab1";
2549
deallocate prepare abc;
2554
drop view if exists v1;
2555
drop table if exists t1;
2557
create table t1(a int, b char(5));
2558
insert into t1 values (1, "one"), (1, "edno"), (2, "two"), (2, "dve");
2560
(select a, count(*) from t1 group by a)
2562
(select b, count(*) from t1 group by b);
2563
create procedure proc_1() show create view v1;
2567
drop procedure proc_1;
2569
--error ER_SP_NO_RETSET
2570
create function func_1() returns int begin show create view v1; return 1; end|
2572
--error ER_SP_DOES_NOT_EXIST
2573
select func_1(), func_1(), func_1() from dual;
2574
--error ER_SP_DOES_NOT_EXIST
2575
drop function func_1;
2576
prepare abc from "show create view v1";
2580
deallocate prepare abc;
2585
create procedure proc_1() install plugin my_plug soname 'some_plugin.so';
2586
--replace_regex /(Can\'t open shared library).*$/\1/
2587
--error ER_CANT_OPEN_LIBRARY,ER_FEATURE_DISABLED
2589
--replace_regex /(Can\'t open shared library).*$/\1/
2590
--error ER_CANT_OPEN_LIBRARY,ER_FEATURE_DISABLED
2592
--replace_regex /(Can\'t open shared library).*$/\1/
2593
--error ER_CANT_OPEN_LIBRARY,ER_FEATURE_DISABLED
2595
drop procedure proc_1;
2597
--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
2598
create function func_1() returns int begin install plugin my_plug soname '/tmp/plugin'; return 1; end|
2600
--error ER_SP_DOES_NOT_EXIST
2601
select func_1(), func_1(), func_1() from dual;
2602
--error ER_SP_DOES_NOT_EXIST
2603
drop function func_1;
2604
prepare abc from "install plugin my_plug soname 'some_plugin.so'";
2605
deallocate prepare abc;
2608
create procedure proc_1() uninstall plugin my_plug;
2609
--error ER_SP_DOES_NOT_EXIST
2611
--error ER_SP_DOES_NOT_EXIST
2613
--error ER_SP_DOES_NOT_EXIST
2615
drop procedure proc_1;
2617
--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
2618
create function func_1() returns int begin uninstall plugin my_plug; return 1; end|
2620
--error ER_SP_DOES_NOT_EXIST
2621
select func_1(), func_1(), func_1() from dual;
2622
--error ER_SP_DOES_NOT_EXIST
2623
drop function func_1;
2624
prepare abc from "uninstall plugin my_plug";
2625
--error ER_SP_DOES_NOT_EXIST
2627
--error ER_SP_DOES_NOT_EXIST
2629
--error ER_SP_DOES_NOT_EXIST
2631
deallocate prepare abc;
2635
drop database if exists mysqltest_xyz;
2637
create procedure proc_1() create database mysqltest_xyz;
2639
drop database if exists mysqltest_xyz;
2641
--error ER_DB_CREATE_EXISTS
2643
drop database if exists mysqltest_xyz;
2645
drop database if exists mysqltest_xyz;
2646
drop procedure proc_1;
2648
--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
2649
create function func_1() returns int begin create database mysqltest_xyz; return 1; end|
2651
--error ER_SP_DOES_NOT_EXIST
2652
select func_1(), func_1(), func_1() from dual;
2653
--error ER_SP_DOES_NOT_EXIST
2654
drop function func_1;
2655
prepare abc from "create database mysqltest_xyz";
2657
drop database if exists mysqltest_xyz;
2659
--error ER_DB_CREATE_EXISTS
2661
drop database if exists mysqltest_xyz;
2663
drop database if exists mysqltest_xyz;
2664
deallocate prepare abc;
2668
drop table if exists t1;
2670
create table t1 (a int, b char(5));
2671
insert into t1 values (1, "one"), (2, "two"), (3, "three");
2672
create procedure proc_1() checksum table xyz;
2676
drop procedure proc_1;
2678
--error ER_SP_NO_RETSET
2679
create function func_1() returns int begin checksum table t1; return 1; end|
2681
--error ER_SP_DOES_NOT_EXIST
2682
select func_1(), func_1(), func_1() from dual;
2683
--error ER_SP_DOES_NOT_EXIST
2684
drop function func_1;
2685
prepare abc from "checksum table t1";
2689
deallocate prepare abc;
2692
create procedure proc_1() create user pstest_xyz@localhost;
2694
drop user pstest_xyz@localhost;
2696
--error ER_CANNOT_USER
2698
drop user pstest_xyz@localhost;
2700
drop user pstest_xyz@localhost;
2701
drop procedure proc_1;
2703
--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
2704
create function func_1() returns int begin create user pstest_xyz@localhost; return 1; end|
2706
--error ER_SP_DOES_NOT_EXIST
2707
select func_1(), func_1(), func_1() from dual;
2708
--error ER_SP_DOES_NOT_EXIST
2709
drop function func_1;
2710
prepare abc from "create user pstest_xyz@localhost";
2712
drop user pstest_xyz@localhost;
2714
--error ER_CANNOT_USER
2716
drop user pstest_xyz@localhost;
2718
drop user pstest_xyz@localhost;
2719
deallocate prepare abc;
2723
drop event if exists xyz;
2725
#create procedure proc_1() create event xyz on schedule every 5 minute disable do select 123;
2729
#--error ER_EVENT_ALREADY_EXISTS
2734
#drop procedure proc_1;
2736
--error ER_EVENT_RECURSION_FORBIDDEN
2737
create function func_1() returns int begin create event xyz on schedule at now() do select 123; return 1; end|
2739
--error ER_SP_DOES_NOT_EXIST
2740
select func_1(), func_1(), func_1() from dual;
2741
--error ER_SP_DOES_NOT_EXIST
2742
drop function func_1;
2743
--error ER_UNSUPPORTED_PS
2744
prepare abc from "create event xyz on schedule at now() do select 123";
2745
--error ER_UNKNOWN_STMT_HANDLER
2746
deallocate prepare abc;
2750
drop event if exists xyz;
2751
create event xyz on schedule every 5 minute disable do select 123;
2753
create procedure proc_1() alter event xyz comment 'xyz';
2756
create event xyz on schedule every 5 minute disable do select 123;
2759
create event xyz on schedule every 5 minute disable do select 123;
2762
drop procedure proc_1;
2764
--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
2765
create function func_1() returns int begin alter event xyz comment 'xyz'; return 1; end|
2767
--error ER_UNSUPPORTED_PS
2768
prepare abc from "alter event xyz comment 'xyz'";
2769
--error ER_UNKNOWN_STMT_HANDLER
2770
deallocate prepare abc;
2774
drop event if exists xyz;
2775
create event xyz on schedule every 5 minute disable do select 123;
2777
create procedure proc_1() drop event xyz;
2779
create event xyz on schedule every 5 minute disable do select 123;
2781
--error ER_EVENT_DOES_NOT_EXIST
2783
drop procedure proc_1;
2785
--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
2786
create function func_1() returns int begin drop event xyz; return 1; end|
2788
--error ER_UNSUPPORTED_PS
2789
prepare abc from "drop event xyz";
2790
--error ER_UNKNOWN_STMT_HANDLER
2791
deallocate prepare abc;
2795
drop table if exists t1;
2796
create table t1 (a int, b char(5)) engine=myisam;
2797
insert into t1 values (1, "one"), (2, "two"), (3, "three");
2799
SET GLOBAL new_cache.key_buffer_size=128*1024;
2800
create procedure proc_1() cache index t1 in new_cache;
2804
drop procedure proc_1;
2805
SET GLOBAL second_cache.key_buffer_size=128*1024;
2806
prepare abc from "cache index t1 in second_cache";
2810
deallocate prepare abc;
2814
drop table if exists t1;
2815
drop table if exists t2;
2816
create table t1 (a int, b char(5)) engine=myisam;
2817
insert into t1 values (1, "one"), (2, "two"), (3, "three");
2818
create table t2 (a int, b char(5)) engine=myisam;
2819
insert into t2 values (1, "one"), (2, "two"), (3, "three");
2821
create procedure proc_1() load index into cache t1 ignore leaves;
2825
drop procedure proc_1;
2827
--error ER_SP_NO_RETSET
2828
create function func_1() returns int begin load index into cache t1 ignore leaves; return 1; end|
2830
prepare abc from "load index into cache t2 ignore leaves";
2834
deallocate prepare abc;
2838
# Bug #21422: GRANT/REVOKE possible inside stored function, probably in a trigger
2839
# This is disabled for now till it is resolved in 5.0
2842
#create procedure proc_1() grant all on *.* to abc@host;
2843
#drop procedure proc_1;
2845
#--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
2846
#create function func_1() returns int begin grant all on *.* to abc@host; return 1; end|
2848
#prepare abc from "grant all on *.* to abc@host";
2850
#create procedure proc_1() revoke all on *.* from abc@host;
2851
#drop procedure proc_1;
2852
#delimiter |;#--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
2853
#create function func_1() returns int begin revoke all on *.* from abc@host; return 1; end|
2855
#prepare abc from "revoke all on *.* from abc@host";
2857
create procedure proc_1() show errors;
2861
drop procedure proc_1;
2863
--error ER_SP_NO_RETSET
2864
create function func_1() returns int begin show errors; return 1; end|
2866
prepare abc from "show errors";
2867
deallocate prepare abc;
2870
drop table if exists t1;
2871
drop table if exists t2;
2873
create procedure proc_1() show warnings;
2874
drop table if exists t1;
2876
drop table if exists t2;
2878
drop table if exists t1, t2;
2880
drop procedure proc_1;
2882
--error ER_SP_NO_RETSET
2883
create function func_1() returns int begin show warnings; return 1; end|
2885
prepare abc from "show warnings";
2886
drop table if exists t1;
2888
drop table if exists t2;
2890
drop table if exists t1, t2;
2892
deallocate prepare abc;
2895
# Bug#22684: The Functions ENCODE, DECODE and FORMAT are not real functions
2898
set @my_password="password";
2899
set @my_data="clear text to encode";
2901
prepare stmt1 from 'select decode(encode(?, ?), ?)';
2902
execute stmt1 using @my_data, @my_password, @my_password;
2903
set @my_data="more text to encode";
2904
execute stmt1 using @my_data, @my_password, @my_password;
2905
set @my_password="new password";
2906
execute stmt1 using @my_data, @my_password, @my_password;
2907
deallocate prepare stmt1;
2909
set @to_format="123456789.123456789";
2912
prepare stmt2 from 'select format(?, ?)';
2913
execute stmt2 using @to_format, @dec;
2915
execute stmt2 using @to_format, @dec;
2917
execute stmt2 using @to_format, @dec;
2919
execute stmt2 using @to_format, @dec;
2920
set @to_format="100";
2921
execute stmt2 using @to_format, @dec;
2922
set @to_format="1000000";
2923
execute stmt2 using @to_format, @dec;
2924
set @to_format="10000";
2925
execute stmt2 using @to_format, @dec;
2926
deallocate prepare stmt2;
2930
# BUG#18326: Do not lock table for writing during prepare of statement
2933
DROP TABLE IF EXISTS t1, t2;
2936
CREATE TABLE t1 (i INT);
2937
INSERT INTO t1 VALUES (1);
2938
CREATE TABLE t2 (i INT);
2939
INSERT INTO t2 VALUES (2);
2941
LOCK TABLE t1 READ, t2 WRITE;
2943
connect (conn1, localhost, root, , );
2945
# Prepare never acquires the lock, and thus should not block.
2946
PREPARE stmt1 FROM "SELECT i FROM t1";
2947
PREPARE stmt2 FROM "INSERT INTO t2 (i) VALUES (3)";
2949
# This should not block because READ lock on t1 is shared.
2952
# This should block because WRITE lock on t2 is exclusive.
2959
let $wait_condition= SELECT COUNT(*) = 2 FROM t2;
2960
--source include/wait_condition.inc
2963
# DDL and DML works even if some client have a prepared statement
2964
# referencing the table.
2965
ALTER TABLE t1 ADD COLUMN j INT;
2966
ALTER TABLE t2 ADD COLUMN j INT;
2967
INSERT INTO t1 VALUES (4, 5);
2968
INSERT INTO t2 VALUES (4, 5);
2984
# Bug #24879 Prepared Statements: CREATE TABLE (UTF8 KEY) produces a growing
2987
# Test that parse information is not altered by subsequent executions of a
2988
# prepared statement
2990
drop table if exists t1;
2992
from "create table t1 (c char(100) character set utf8, key (c(10)))";
2994
show create table t1;
2997
show create table t1;
3001
# Bug #32030 DELETE does not return an error and deletes rows if error
3004
# Test that there is an error for prepared delete just like for the normal
3008
drop table if exists t1, t2;
3010
create table t1 (a int, b int);
3011
create table t2 like t1;
3013
insert into t1 (a, b) values (1,1), (1,2), (1,3), (1,4), (1,5),
3014
(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
3016
insert into t2 select a, max(b) from t1 group by a;
3018
prepare stmt from "delete from t2 where (select (select max(b) from t1 group
3019
by a having a < 2) x from t1) > 10000";
3021
--error ER_SUBQUERY_NO_1_ROW
3022
delete from t2 where (select (select max(b) from t1 group
3023
by a having a < 2) x from t1) > 10000;
3024
--error ER_SUBQUERY_NO_1_ROW
3026
--error ER_SUBQUERY_NO_1_ROW
3029
deallocate prepare stmt;
3033
--echo # Bug#27430 Crash in subquery code when in PS and table DDL changed
3034
--echo # after PREPARE
3036
--echo # This part of the test doesn't work in embedded server, this is
3037
--echo # why it's here. For the main test see ps_ddl*.test
3040
drop table if exists t1;
3042
create table t1 (a int);
3043
prepare stmt from "show events where (1) in (select * from t1)";
3046
create table t1 (x int);
3049
deallocate prepare stmt;
3052
--echo # Bug#49141: Encode function is significantly slower in 5.1 compared to 5.0
3055
prepare encode from "select encode(?, ?) into @ciphertext";
3056
prepare decode from "select decode(?, ?) into @plaintext";
3057
set @str="abc", @key="cba";
3058
execute encode using @str, @key;
3059
execute decode using @ciphertext, @key;
3061
set @str="bcd", @key="dcb";
3062
execute encode using @str, @key;
3063
execute decode using @ciphertext, @key;
3065
deallocate prepare encode;
3066
deallocate prepare decode;
3068
--echo End of 5.1 tests.
added
removed
mysql-test/t/ps.test
