diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/include/query_cache_sql_prepare.inc | 12 | ||||
| -rw-r--r-- | mysql-test/r/ps_1general.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/query_cache_ps_no_prot.result | 81 | ||||
| -rw-r--r-- | mysql-test/r/query_cache_ps_ps_prot.result | 81 | ||||
| -rw-r--r-- | mysql-test/r/sp-dynamic.result | 18 | ||||
| -rw-r--r-- | mysql-test/r/sp-error.result | 4 | ||||
| -rw-r--r-- | mysql-test/t/ps_1general.test | 4 | ||||
| -rw-r--r-- | mysql-test/t/sp-dynamic.test | 12 | ||||
| -rw-r--r-- | mysql-test/t/sp-error.test | 10 |
9 files changed, 144 insertions, 80 deletions
diff --git a/mysql-test/include/query_cache_sql_prepare.inc b/mysql-test/include/query_cache_sql_prepare.inc index cdb3bd586e7..cf6d4c26959 100644 --- a/mysql-test/include/query_cache_sql_prepare.inc +++ b/mysql-test/include/query_cache_sql_prepare.inc @@ -33,7 +33,7 @@ drop table if exists t1; create table t1(c1 int); insert into t1 values(1),(10),(100); -# Prepared statements has no parameters, query caching should happen +# First, prepared statements with no parameters prepare stmt1 from "select * from t1 where c1=10"; show status like 'Qcache_hits'; execute stmt1; @@ -113,7 +113,9 @@ show status like 'Qcache_hits'; --echo ---- switch to connection default ---- connection default; -# Prepared statement has parameters, query caching should not happen +# Query caching also works when statement has parameters +# (BUG#29318 Statements prepared with PREPARE and with one parameter don't use +# query cache) prepare stmt1 from "select * from t1 where c1=?"; show status like 'Qcache_hits'; set @a=1; @@ -127,6 +129,12 @@ set @a=1; prepare stmt4 from "select * from t1 where c1=?"; execute stmt4 using @a; show status like 'Qcache_hits'; +# verify that presence of user variables forbids caching +prepare stmt4 from "select @a from t1 where c1=?"; +execute stmt4 using @a; +show status like 'Qcache_hits'; +execute stmt4 using @a; +show status like 'Qcache_hits'; --echo ---- switch to connection default ---- connection default; diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result index 1b22bb0f39f..c647d6d5503 100644 --- a/mysql-test/r/ps_1general.result +++ b/mysql-test/r/ps_1general.result @@ -378,6 +378,8 @@ prepare stmt1 from ' execute stmt2 ' ; ERROR HY000: This command is not supported in the prepared statement protocol yet prepare stmt1 from ' deallocate prepare never_prepared ' ; ERROR HY000: This command is not supported in the prepared statement protocol yet +prepare stmt1 from 'alter view v1 as select 2'; +ERROR HY000: This command is not supported in the prepared statement protocol yet prepare stmt4 from ' use test ' ; ERROR HY000: This command is not supported in the prepared statement protocol yet prepare stmt3 from ' create database mysqltest '; diff --git a/mysql-test/r/query_cache_ps_no_prot.result b/mysql-test/r/query_cache_ps_no_prot.result index bf162439918..29d16d8a619 100644 --- a/mysql-test/r/query_cache_ps_no_prot.result +++ b/mysql-test/r/query_cache_ps_no_prot.result @@ -144,7 +144,7 @@ c1 1 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 14 +Qcache_hits 15 ---- switch to connection con1 ---- set @a=1; prepare stmt4 from "select * from t1 where c1=?"; @@ -153,50 +153,63 @@ c1 1 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 14 +Qcache_hits 16 +prepare stmt4 from "select @a from t1 where c1=?"; +execute stmt4 using @a; +@a +1 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 16 +execute stmt4 using @a; +@a +1 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 16 ---- switch to connection default ---- prepare stmt1 from "select * from t1 where c1=10"; set global query_cache_size=0; show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 14 +Qcache_hits 16 execute stmt1; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 14 +Qcache_hits 16 execute stmt1; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 14 +Qcache_hits 16 execute stmt1; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 14 +Qcache_hits 16 ---- switch to connection con1 ---- execute stmt3; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 14 +Qcache_hits 16 execute stmt3; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 14 +Qcache_hits 16 execute stmt3; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 14 +Qcache_hits 16 ---- switch to connection default ---- set global query_cache_size=100000; execute stmt1; @@ -204,80 +217,80 @@ c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 14 +Qcache_hits 16 execute stmt1; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 15 +Qcache_hits 17 execute stmt1; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 16 +Qcache_hits 18 ---- switch to connection con1 ---- execute stmt3; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 17 +Qcache_hits 19 execute stmt3; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 18 +Qcache_hits 20 execute stmt3; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 19 +Qcache_hits 21 ---- switch to connection default ---- set global query_cache_size=0; show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 19 +Qcache_hits 21 execute stmt1; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 19 +Qcache_hits 21 execute stmt1; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 19 +Qcache_hits 21 execute stmt1; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 19 +Qcache_hits 21 ---- switch to connection con1 ---- execute stmt3; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 19 +Qcache_hits 21 execute stmt3; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 19 +Qcache_hits 21 execute stmt3; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 19 +Qcache_hits 21 ---- switch to connection default ---- set global query_cache_size=0; prepare stmt1 from "select * from t1 where c1=10"; @@ -287,75 +300,75 @@ prepare stmt3 from "select * from t1 where c1=10"; set global query_cache_size=100000; show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 19 +Qcache_hits 21 execute stmt1; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 19 +Qcache_hits 21 execute stmt1; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 19 +Qcache_hits 21 execute stmt1; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 19 +Qcache_hits 21 ---- switch to connection con1 ---- show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 19 +Qcache_hits 21 execute stmt3; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 19 +Qcache_hits 21 execute stmt3; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 19 +Qcache_hits 21 execute stmt3; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 19 +Qcache_hits 21 ---- switch to connection default ---- set global query_cache_size=0; prepare stmt1 from "select * from t1 where c1=?"; set global query_cache_size=100000; show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 19 +Qcache_hits 21 set @a=1; execute stmt1 using @a; c1 1 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 19 +Qcache_hits 21 set @a=100; execute stmt1 using @a; c1 100 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 19 +Qcache_hits 21 set @a=10; execute stmt1 using @a; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 19 +Qcache_hits 21 drop table t1; ---- disconnect connection con1 ---- set @@global.query_cache_size=@initial_query_cache_size; diff --git a/mysql-test/r/query_cache_ps_ps_prot.result b/mysql-test/r/query_cache_ps_ps_prot.result index 56aeda4a253..ba675d57f50 100644 --- a/mysql-test/r/query_cache_ps_ps_prot.result +++ b/mysql-test/r/query_cache_ps_ps_prot.result @@ -144,7 +144,7 @@ c1 1 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 12 +Qcache_hits 13 ---- switch to connection con1 ---- set @a=1; prepare stmt4 from "select * from t1 where c1=?"; @@ -153,50 +153,63 @@ c1 1 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 12 +Qcache_hits 14 +prepare stmt4 from "select @a from t1 where c1=?"; +execute stmt4 using @a; +@a +1 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 14 +execute stmt4 using @a; +@a +1 +show status like 'Qcache_hits'; +Variable_name Value +Qcache_hits 14 ---- switch to connection default ---- prepare stmt1 from "select * from t1 where c1=10"; set global query_cache_size=0; show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 12 +Qcache_hits 14 execute stmt1; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 12 +Qcache_hits 14 execute stmt1; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 12 +Qcache_hits 14 execute stmt1; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 12 +Qcache_hits 14 ---- switch to connection con1 ---- execute stmt3; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 12 +Qcache_hits 14 execute stmt3; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 12 +Qcache_hits 14 execute stmt3; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 12 +Qcache_hits 14 ---- switch to connection default ---- set global query_cache_size=100000; execute stmt1; @@ -204,80 +217,80 @@ c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 12 +Qcache_hits 14 execute stmt1; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 13 +Qcache_hits 15 execute stmt1; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 14 +Qcache_hits 16 ---- switch to connection con1 ---- execute stmt3; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 15 +Qcache_hits 17 execute stmt3; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 16 +Qcache_hits 18 execute stmt3; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 17 +Qcache_hits 19 ---- switch to connection default ---- set global query_cache_size=0; show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 17 +Qcache_hits 19 execute stmt1; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 17 +Qcache_hits 19 execute stmt1; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 17 +Qcache_hits 19 execute stmt1; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 17 +Qcache_hits 19 ---- switch to connection con1 ---- execute stmt3; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 17 +Qcache_hits 19 execute stmt3; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 17 +Qcache_hits 19 execute stmt3; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 17 +Qcache_hits 19 ---- switch to connection default ---- set global query_cache_size=0; prepare stmt1 from "select * from t1 where c1=10"; @@ -287,75 +300,75 @@ prepare stmt3 from "select * from t1 where c1=10"; set global query_cache_size=100000; show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 17 +Qcache_hits 19 execute stmt1; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 17 +Qcache_hits 19 execute stmt1; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 17 +Qcache_hits 19 execute stmt1; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 17 +Qcache_hits 19 ---- switch to connection con1 ---- show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 17 +Qcache_hits 19 execute stmt3; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 17 +Qcache_hits 19 execute stmt3; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 17 +Qcache_hits 19 execute stmt3; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 17 +Qcache_hits 19 ---- switch to connection default ---- set global query_cache_size=0; prepare stmt1 from "select * from t1 where c1=?"; set global query_cache_size=100000; show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 17 +Qcache_hits 19 set @a=1; execute stmt1 using @a; c1 1 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 17 +Qcache_hits 19 set @a=100; execute stmt1 using @a; c1 100 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 17 +Qcache_hits 19 set @a=10; execute stmt1 using @a; c1 10 show status like 'Qcache_hits'; Variable_name Value -Qcache_hits 17 +Qcache_hits 19 drop table t1; ---- disconnect connection con1 ---- set @@global.query_cache_size=@initial_query_cache_size; diff --git a/mysql-test/r/sp-dynamic.result b/mysql-test/r/sp-dynamic.result index f1c7e6076e5..34b76a9424f 100644 --- a/mysql-test/r/sp-dynamic.result +++ b/mysql-test/r/sp-dynamic.result @@ -87,6 +87,10 @@ prepare stmt from "create table t1 (a int)"; execute stmt; insert into t1 (a) values (1); select * from t1; +prepare stmt_alter from "alter table t1 add (b int)"; +execute stmt_alter; +insert into t1 (a,b) values (2,1); +deallocate prepare stmt_alter; deallocate prepare stmt; deallocate prepare stmt_drop; end| @@ -245,6 +249,9 @@ a 1 drop procedure p1| drop table if exists t1| +drop table if exists t2| +Warnings: +Note 1051 Unknown table 't2' create table t1 (id integer primary key auto_increment, stmt_text char(35), status varchar(20))| insert into t1 (stmt_text) values @@ -255,7 +262,10 @@ insert into t1 (stmt_text) values ("help help"), ("show databases"), ("show tables"), ("show table status"), ("show open tables"), ("show storage engines"), ("insert into t1 (id) values (1)"), ("update t1 set status=''"), -("delete from t1"), ("truncate t1"), ("call p1()"), ("foo bar")| +("delete from t1"), ("truncate t1"), ("call p1()"), ("foo bar"), +("create view v1 as select 1"), ("alter view v1 as select 2"), +("drop view v1"),("create table t2 (a int)"),("alter table t2 add (b int)"), +("drop table t2")| create procedure p1() begin declare v_stmt_text varchar(255); @@ -305,6 +315,12 @@ id stmt_text status 20 truncate t1 supported 21 call p1() supported 22 foo bar syntax error +23 create view v1 as select 1 supported +24 alter view v1 as select 2 not supported +25 drop view v1 supported +26 create table t2 (a int) supported +27 alter table t2 add (b int) supported +28 drop table t2 supported drop procedure p1| drop table t1| prepare stmt from 'select 1'| diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index 16f39e39a46..0f043a73f85 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -982,9 +982,9 @@ ERROR HY000: Explicit or implicit commit is not allowed in stored function or tr CREATE FUNCTION bug_13627_f() returns int BEGIN create view v1 as select 1; return 1; END | ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter view v1 as select 1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. +ERROR 0A000: ALTER VIEW is not allowed in stored procedures CREATE FUNCTION bug_13627_f() returns int BEGIN alter view v1 as select 1; return 1; END | -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. +ERROR 0A000: ALTER VIEW is not allowed in stored procedures CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN drop view v1; END | ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. CREATE FUNCTION bug_13627_f() returns int BEGIN drop view v1; return 1; END | diff --git a/mysql-test/t/ps_1general.test b/mysql-test/t/ps_1general.test index 9905d73fc32..13773504fb0 100644 --- a/mysql-test/t/ps_1general.test +++ b/mysql-test/t/ps_1general.test @@ -414,6 +414,10 @@ prepare stmt1 from ' execute stmt2 ' ; --error ER_UNSUPPORTED_PS prepare stmt1 from ' deallocate prepare never_prepared ' ; +## We don't support alter view as prepared statements +--error ER_UNSUPPORTED_PS +prepare stmt1 from 'alter view v1 as select 2'; + ## switch the database connection --error ER_UNSUPPORTED_PS prepare stmt4 from ' use test ' ; diff --git a/mysql-test/t/sp-dynamic.test b/mysql-test/t/sp-dynamic.test index 6546a5ab548..e6f4aae96ac 100644 --- a/mysql-test/t/sp-dynamic.test +++ b/mysql-test/t/sp-dynamic.test @@ -85,7 +85,7 @@ call p1()| call p1()| drop procedure p1| # -# D. Create/Drop a table (a DDL that issues a commit) in Dynamic SQL. +# D. Create/Drop/Alter a table (a DDL that issues a commit) in Dynamic SQL. # (should work ok). # create procedure p1() @@ -96,6 +96,10 @@ begin execute stmt; insert into t1 (a) values (1); select * from t1; + prepare stmt_alter from "alter table t1 add (b int)"; + execute stmt_alter; + insert into t1 (a,b) values (2,1); + deallocate prepare stmt_alter; deallocate prepare stmt; deallocate prepare stmt_drop; end| @@ -239,6 +243,7 @@ drop procedure p1| # K. Use of continue handlers with Dynamic SQL. # drop table if exists t1| +drop table if exists t2| create table t1 (id integer primary key auto_increment, stmt_text char(35), status varchar(20))| insert into t1 (stmt_text) values @@ -249,7 +254,10 @@ insert into t1 (stmt_text) values ("help help"), ("show databases"), ("show tables"), ("show table status"), ("show open tables"), ("show storage engines"), ("insert into t1 (id) values (1)"), ("update t1 set status=''"), - ("delete from t1"), ("truncate t1"), ("call p1()"), ("foo bar")| + ("delete from t1"), ("truncate t1"), ("call p1()"), ("foo bar"), + ("create view v1 as select 1"), ("alter view v1 as select 2"), + ("drop view v1"),("create table t2 (a int)"),("alter table t2 add (b int)"), + ("drop table t2")| create procedure p1() begin declare v_stmt_text varchar(255); diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index 69b1f77aa35..ef9bed8b789 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -1087,12 +1087,12 @@ delimiter ;| # # BUG 12490 (Packets out of order if calling HELP CONTENTS from Stored Procedure) # ---error 1314 +--error ER_SP_BADSTATEMENT CREATE PROCEDURE BUG_12490() HELP CONTENTS; ---error 1314 +--error ER_SP_BADSTATEMENT CREATE FUNCTION BUG_12490() RETURNS INT HELP CONTENTS; CREATE TABLE t_bug_12490(a int); ---error 1314 +--error ER_SP_BADSTATEMENT CREATE TRIGGER BUG_12490 BEFORE UPDATE ON t_bug_12490 FOR EACH ROW HELP CONTENTS; DROP TABLE t_bug_12490; @@ -1401,9 +1401,9 @@ CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN create view v1 as sele -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG CREATE FUNCTION bug_13627_f() returns int BEGIN create view v1 as select 1; return 1; END | --- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +-- error ER_SP_BADSTATEMENT CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN alter view v1 as select 1; END | --- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +-- error ER_SP_BADSTATEMENT CREATE FUNCTION bug_13627_f() returns int BEGIN alter view v1 as select 1; return 1; END | -- error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG |