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;
3069 --echo # Bug#52124 memory leaks like a sieve in datetime, timestamp, time, date fields + warnings
3071 CREATE TABLE t1 (a DATETIME NOT NULL, b TINYINT);
3072 INSERT INTO t1 VALUES (0, 0),(0, 0);
3073 PREPARE stmt FROM "SELECT 1 FROM t1 WHERE
3074 ROW(a, b) >= ROW('1', (SELECT 1 FROM t1 WHERE a > 1234))";
3079 DEALLOCATE PREPARE stmt;
3083 --echo # Bug#54494 crash with explain extended and prepared statements
3085 CREATE TABLE t1(a INT);
3086 INSERT INTO t1 VALUES (1),(2);
3087 PREPARE stmt FROM 'EXPLAIN EXTENDED SELECT 1 FROM t1 RIGHT JOIN t1 t2 ON 1';
3090 DEALLOCATE PREPARE stmt;
3094 --echo # Bug#54488 crash when using explain and prepared statements with subqueries
3096 CREATE TABLE t1(f1 INT);
3097 INSERT INTO t1 VALUES (1),(1);
3098 PREPARE stmt FROM 'EXPLAIN SELECT 1 FROM t1 WHERE (SELECT (SELECT 1 FROM t1 GROUP BY f1))';
3101 DEALLOCATE PREPARE stmt;
3104 --echo End of 5.1 tests.
3107 --echo # Bug#13805127: Stored program cache produces wrong result in same THD
3112 SELECT c1, t2.c2, count(c3)
3115 SELECT 3 as c2 FROM dual WHERE @x = 1
3117 SELECT 2 FROM dual WHERE @x = 1 OR @x = 2
3120 SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
3122 SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
3124 SELECT '2012-03-01 01:00:00', 2, 1 FROM dual
3132 SELECT c1, t2.c2, count(c3)
3135 SELECT 3 as c2 FROM dual WHERE @x = 1
3137 SELECT 2 FROM dual WHERE @x = 1 OR @x = 2
3140 SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
3142 SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
3144 SELECT '2012-03-01 01:00:00', 2, 1 FROM dual
3153 SELECT c1, t2.c2, count(c3)
3156 SELECT 3 as c2 FROM dual WHERE @x = 1
3158 SELECT 2 FROM dual WHERE @x = 1 OR @x = 2
3161 SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
3163 SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
3165 SELECT '2012-03-01 01:00:00', 2, 1 FROM dual
3174 SELECT c1, t2.c2, count(c3)
3177 SELECT 3 as c2 FROM dual WHERE @x = 1
3179 SELECT 2 FROM dual WHERE @x = 1 OR @x = 2
3182 SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
3184 SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
3186 SELECT '2012-03-01 01:00:00', 2, 1 FROM dual
3193 DEALLOCATE PREPARE s1;