diff options
Diffstat (limited to 'mysql-test/t/ps.test')
| -rw-r--r-- | mysql-test/t/ps.test | 441 |
1 files changed, 424 insertions, 17 deletions
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index 0ca293eb1ba..5b2e37ecc94 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -3,6 +3,9 @@ # --disable_warnings drop table if exists t1,t2; + +# Avoid wrong warnings if mysql_client_test fails +drop database if exists client_test_db; --enable_warnings create table t1 @@ -149,7 +152,7 @@ create table t1 c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), - c13 date, c14 datetime, c15 timestamp(14), c16 time, + c13 date, c14 datetime, c15 timestamp, c16 time, c17 year, c18 bit, c19 bool, c20 char, c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, c25 blob, c26 text, c27 mediumblob, c28 mediumtext, @@ -487,6 +490,42 @@ execute stmt; deallocate prepare stmt; drop table t1, t2; +# +# +# Bug#19399 "Stored Procedures 'Lost Connection' when dropping/creating +# tables" +# Check that multi-delete tables are also cleaned up before re-execution. +# +--disable_warnings +drop table if exists t1; +create temporary table if not exists t1 (a1 int); +--enable_warnings +# exact delete syntax is essential +prepare stmt from "delete t1 from t1 where (cast(a1/3 as unsigned) * 3) = a1"; +drop temporary table t1; +create temporary table if not exists t1 (a1 int); +# the server crashed on the next statement without the fix +execute stmt; +drop temporary table t1; +create temporary table if not exists t1 (a1 int); +# the problem was in memory corruption: repeat the test just in case +execute stmt; +drop temporary table t1; +create temporary table if not exists t1 (a1 int); +execute stmt; +drop temporary table t1; +deallocate prepare stmt; + +# Bug#6102 "Server crash with prepared statement and blank after +# function name" +# ensure that stored functions are cached when preparing a statement +# before we open tables +# +create table t1 (a varchar(20)); +insert into t1 values ('foo'); +--error 1305 +prepare stmt FROM 'SELECT char_length (a) FROM t1'; +drop table t1; # # Bug #6089: FOUND_ROWS returns wrong values when no table/view is used @@ -524,6 +563,7 @@ set @a=200887, @b=860; # this query did not return all matching rows execute stmt using @a, @b; deallocate prepare stmt; + drop table t1; # @@ -583,7 +623,6 @@ execute stmt; execute stmt; deallocate prepare stmt; drop table t1; - # # Bug#11458 "Prepared statement with subselects return random data": # drop PARAM_TABLE_BIT from the list of tables used by a subquery @@ -652,7 +691,6 @@ execute stmt using @user_id, @id; execute stmt using @user_id, @id; deallocate prepare stmt; drop table t1, t2, t3, t4; - # # Bug#9379: make sure that Item::collation is reset when one sets # a parameter marker from a string variable. @@ -767,7 +805,6 @@ execute stmt using @like; deallocate prepare stmt; drop table t1; - # # Bug#13134 "Length of VARCHAR() utf8 column is increasing when table is # recreated with PS/SP" @@ -838,17 +875,17 @@ set global max_prepared_stmt_count=10000000000000000; select @@max_prepared_stmt_count; set global max_prepared_stmt_count=default; select @@max_prepared_stmt_count; ---error 1229 # ER_GLOBAL_VARIABLE +--error ER_GLOBAL_VARIABLE set @@max_prepared_stmt_count=1; ---error 1229 # ER_GLOBAL_VARIABLE +--error ER_GLOBAL_VARIABLE set max_prepared_stmt_count=1; ---error 1229 # ER_GLOBAL_VARIABLE +--error ER_GLOBAL_VARIABLE set local max_prepared_stmt_count=1; ---error 1229 # ER_GLOBAL_VARIABLE +--error ER_INCORRECT_GLOBAL_LOCAL_VAR set local prepared_stmt_count=0; ---error 1229 # ER_GLOBAL_VARIABLE +--error ER_INCORRECT_GLOBAL_LOCAL_VAR set @@prepared_stmt_count=0; ---error 1232 # ER_WRONG_TYPE_FOR_VAR +--error ER_INCORRECT_GLOBAL_LOCAL_VAR set global prepared_stmt_count=1; # set to a reasonable limit works set global max_prepared_stmt_count=1; @@ -858,13 +895,13 @@ select @@max_prepared_stmt_count; # set global max_prepared_stmt_count=0; select @@max_prepared_stmt_count, @@prepared_stmt_count; ---error 1105 # ER_UNKNOWN_ERROR +--error ER_MAX_PREPARED_STMT_COUNT_REACHED prepare stmt from "select 1"; select @@prepared_stmt_count; set global max_prepared_stmt_count=1; prepare stmt from "select 1"; select @@prepared_stmt_count; ---error 1105 # ER_UNKNOWN_ERROR +--error ER_MAX_PREPARED_STMT_COUNT_REACHED prepare stmt1 from "select 1"; select @@prepared_stmt_count; deallocate prepare stmt; @@ -883,13 +920,13 @@ select @@prepared_stmt_count; # select @@prepared_stmt_count, @@max_prepared_stmt_count; set global max_prepared_stmt_count=0; ---error 1105 # ER_UNKNOWN_ERROR +--error ER_MAX_PREPARED_STMT_COUNT_REACHED prepare stmt from "select 1"; # Result: the old statement is deallocated, the new is not created. --error 1243 # ER_UNKNOWN_STMT_HANDLER execute stmt; select @@prepared_stmt_count; ---error 1105 # ER_UNKNOWN_ERROR +--error ER_MAX_PREPARED_STMT_COUNT_REACHED prepare stmt from "select 1"; select @@prepared_stmt_count; # @@ -903,10 +940,10 @@ connect (con1,localhost,root,,); connection con1; prepare stmt from "select 2"; prepare stmt1 from "select 3"; ---error 1105 # ER_UNKNOWN_ERROR +--error ER_MAX_PREPARED_STMT_COUNT_REACHED prepare stmt2 from "select 4"; connection default; ---error 1105 # ER_UNKNOWN_ERROR +--error ER_MAX_PREPARED_STMT_COUNT_REACHED prepare stmt2 from "select 4"; select @@max_prepared_stmt_count, @@prepared_stmt_count; disconnect con1; @@ -951,4 +988,374 @@ execute stmt; drop temporary table t1; deallocate prepare stmt; -# End of 4.1 tests +--echo End of 4.1 tests +############################# 5.0 tests start ################################ +# +# +# Bug#6102 "Server crash with prepared statement and blank after +# function name" +# ensure that stored functions are cached when preparing a statement +# before we open tables +# +create table t1 (a varchar(20)); +insert into t1 values ('foo'); +--error 1305 +prepare stmt FROM 'SELECT char_length (a) FROM t1'; +drop table t1; + +# +# Bug#8115: equality propagation and prepared statements +# + +create table t1 (a char(3) not null, b char(3) not null, + c char(3) not null, primary key (a, b, c)); +create table t2 like t1; + +# reduced query +prepare stmt from + "select t1.a from (t1 left outer join t2 on t2.a=1 and t1.b=t2.b) + where t1.a=1"; +execute stmt; +execute stmt; +execute stmt; + +# original query +prepare stmt from +"select t1.a, t1.b, t1.c, t2.a, t2.b, t2.c from +(t1 left outer join t2 on t2.a=? and t1.b=t2.b) +left outer join t2 t3 on t3.a=? where t1.a=?"; + +set @a:=1, @b:=1, @c:=1; + +execute stmt using @a, @b, @c; +execute stmt using @a, @b, @c; +execute stmt using @a, @b, @c; + +deallocate prepare stmt; + +drop table t1,t2; + + +# +# Bug#9383: INFORMATION_SCHEMA.COLUMNS, JOIN, Crash, prepared statement +# + +eval SET @aux= "SELECT COUNT(*) + FROM INFORMATION_SCHEMA.COLUMNS A, + INFORMATION_SCHEMA.COLUMNS B + WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA + AND A.TABLE_NAME = B.TABLE_NAME + AND A.COLUMN_NAME = B.COLUMN_NAME AND + A.TABLE_NAME = 'user'"; + +let $exec_loop_count= 3; +eval prepare my_stmt from @aux; +while ($exec_loop_count) +{ + eval execute my_stmt; + dec $exec_loop_count; +} +deallocate prepare my_stmt; + +# Test CALL in prepared mode +delimiter |; +--disable_warnings +drop procedure if exists p1| +drop table if exists t1| +--enable_warnings +create table t1 (id int)| +insert into t1 values(1)| +create procedure p1(a int, b int) +begin + declare c int; + select max(id)+1 into c from t1; + insert into t1 select a+b; + insert into t1 select a-b; + insert into t1 select a-c; +end| +set @a= 3, @b= 4| +prepare stmt from "call p1(?, ?)"| +execute stmt using @a, @b| +execute stmt using @a, @b| +select * from t1| +deallocate prepare stmt| +drop procedure p1| +drop table t1| +delimiter ;| + + +# +# Bug#7306 LIMIT ?, ? and also WL#1785 " Prepared statements: implement +# support for placeholders in LIMIT clause." +# Add basic test coverage for the feature. +# +create table t1 (a int); +insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +prepare stmt from "select * from t1 limit ?, ?"; +set @offset=0, @limit=1; +execute stmt using @offset, @limit; +select * from t1 limit 0, 1; +set @offset=3, @limit=2; +execute stmt using @offset, @limit; +select * from t1 limit 3, 2; +prepare stmt from "select * from t1 limit ?"; +execute stmt using @limit; +--error 1235 +prepare stmt from "select * from t1 where a in (select a from t1 limit ?)"; +prepare stmt from "select * from t1 union all select * from t1 limit ?, ?"; +set @offset=9; +set @limit=2; +execute stmt using @offset, @limit; +prepare stmt from "(select * from t1 limit ?, ?) union all + (select * from t1 limit ?, ?) order by a limit ?"; +execute stmt using @offset, @limit, @offset, @limit, @limit; + +drop table t1; +deallocate prepare stmt; + +# +# Bug#12651 +# (Crash on a PS including a subquery which is a select from a simple view) +# +CREATE TABLE b12651_T1(a int) ENGINE=MYISAM; +CREATE TABLE b12651_T2(b int) ENGINE=MYISAM; +CREATE VIEW b12651_V1 as SELECT b FROM b12651_T2; + +PREPARE b12651 FROM 'SELECT 1 FROM b12651_T1 WHERE a IN (SELECT b FROM b12651_V1)'; +EXECUTE b12651; + +DROP VIEW b12651_V1; +DROP TABLE b12651_T1, b12651_T2; +DEALLOCATE PREPARE b12651; + + + +# +# Bug #14956: ROW_COUNT() returns incorrect result after EXECUTE of prepared +# statement +# +create table t1 (id int); +prepare ins_call from "insert into t1 (id) values (1)"; +execute ins_call; +select row_count(); +drop table t1; + +# +# BUG#16474: SP crashed MySQL +# (when using "order by localvar", where 'localvar' is just that. +# The actual bug test is in sp.test, this is just testing that we get the +# expected result for prepared statements too, i.e. place holders work as +# textual substitution. If it's a single integer, it works as the (deprecated) +# "order by column#", otherwise it's an expression. +# +create table t1 (a int, b int); +insert into t1 (a,b) values (2,8),(1,9),(3,7); + +# Will order by index +prepare stmt from "select * from t1 order by ?"; +set @a=NULL; +execute stmt using @a; +set @a=1; +execute stmt using @a; +set @a=2; +execute stmt using @a; +deallocate prepare stmt; +# For reference: +select * from t1 order by 1; + +# Will not order by index. +prepare stmt from "select * from t1 order by ?+1"; +set @a=0; +execute stmt using @a; +set @a=1; +execute stmt using @a; +deallocate prepare stmt; +# For reference: +select * from t1 order by 1+1; + +drop table t1; + +# +# Bug#19308 "REPAIR/OPTIMIZE/ANALYZE supported in SP but not in PS". +# Add test coverage for the added commands. +# +create table t1 (a int); +create table t2 like t1; +create table t3 like t2; +prepare stmt from "repair table t1"; +execute stmt; +execute stmt; +prepare stmt from "optimize table t1"; +execute stmt; +execute stmt; +prepare stmt from "analyze table t1"; +execute stmt; +execute stmt; +prepare stmt from "repair table t1, t2, t3"; +execute stmt; +execute stmt; +prepare stmt from "optimize table t1, t2, t3"; +execute stmt; +execute stmt; +prepare stmt from "analyze table t1, t2, t3"; +execute stmt; +execute stmt; +prepare stmt from "repair table t1, t4, t3"; +execute stmt; +execute stmt; +prepare stmt from "optimize table t1, t3, t4"; +execute stmt; +execute stmt; +prepare stmt from "analyze table t4, t1"; +execute stmt; +execute stmt; +deallocate prepare stmt; +drop table t1, t2, t3; + +# +# Bug#17199 "Table not found" error occurs if the query contains a call +# to a function from another database. +# Test prepared statements- related behaviour. +# +# +# ALTER TABLE RENAME and Prepared Statements: wrong DB name buffer was used +# in ALTER ... RENAME which caused memory corruption in prepared statements. +# No need to fix this problem in 4.1 as ALTER TABLE is not allowed in +# Prepared Statements in 4.1. +# +create database mysqltest_long_database_name_to_thrash_heap; +use test; +create table t1 (i int); +prepare stmt from "alter table test.t1 rename t1"; +use mysqltest_long_database_name_to_thrash_heap; +execute stmt; +show tables like 't1'; +prepare stmt from "alter table test.t1 rename t1"; +use test; +execute stmt; +show tables like 't1'; +use mysqltest_long_database_name_to_thrash_heap; +show tables like 't1'; +deallocate prepare stmt; +# +# Check that a prepared statement initializes its current database at +# PREPARE, and then works correctly even if the current database has been +# changed. +# +use mysqltest_long_database_name_to_thrash_heap; +# Necessary for preparation of INSERT/UPDATE/DELETE to succeed +prepare stmt_create from "create table t1 (i int)"; +prepare stmt_insert from "insert into t1 (i) values (1)"; +prepare stmt_update from "update t1 set i=2"; +prepare stmt_delete from "delete from t1 where i=2"; +prepare stmt_select from "select * from t1"; +prepare stmt_alter from "alter table t1 add column (b int)"; +prepare stmt_alter1 from "alter table t1 drop column b"; +prepare stmt_analyze from "analyze table t1"; +prepare stmt_optimize from "optimize table t1"; +prepare stmt_show from "show tables like 't1'"; +prepare stmt_truncate from "truncate table t1"; +prepare stmt_drop from "drop table t1"; +# Drop the table that was used to prepare INSERT/UPDATE/DELETE: we will +# create a new one by executing stmt_create +drop table t1; +# Switch the current database +use test; +# Check that all prepared statements operate on the database that was +# active at PREPARE +execute stmt_create; +# should return empty set +show tables like 't1'; +use mysqltest_long_database_name_to_thrash_heap; +show tables like 't1'; +use test; +execute stmt_insert; +select * from mysqltest_long_database_name_to_thrash_heap.t1; +execute stmt_update; +select * from mysqltest_long_database_name_to_thrash_heap.t1; +execute stmt_delete; +execute stmt_select; +execute stmt_alter; +show columns from mysqltest_long_database_name_to_thrash_heap.t1; +execute stmt_alter1; +show columns from mysqltest_long_database_name_to_thrash_heap.t1; +execute stmt_analyze; +execute stmt_optimize; +execute stmt_show; +execute stmt_truncate; +execute stmt_drop; +show tables like 't1'; +use mysqltest_long_database_name_to_thrash_heap; +show tables like 't1'; +# +# Attempt a statement PREPARE when there is no current database: +# is expected to return an error. +# +drop database mysqltest_long_database_name_to_thrash_heap; +--error ER_NO_DB_ERROR +prepare stmt_create from "create table t1 (i int)"; +--error ER_NO_DB_ERROR +prepare stmt_insert from "insert into t1 (i) values (1)"; +--error ER_NO_DB_ERROR +prepare stmt_update from "update t1 set i=2"; +--error ER_NO_DB_ERROR +prepare stmt_delete from "delete from t1 where i=2"; +--error ER_NO_DB_ERROR +prepare stmt_select from "select * from t1"; +--error ER_NO_DB_ERROR +prepare stmt_alter from "alter table t1 add column (b int)"; +--error ER_NO_DB_ERROR +prepare stmt_alter1 from "alter table t1 drop column b"; +--error ER_NO_DB_ERROR +prepare stmt_analyze from "analyze table t1"; +--error ER_NO_DB_ERROR +prepare stmt_optimize from "optimize table t1"; +--error ER_NO_DB_ERROR +prepare stmt_show from "show tables like 't1'"; +--error ER_NO_DB_ERROR +prepare stmt_truncate from "truncate table t1"; +--error ER_NO_DB_ERROR +prepare stmt_drop from "drop table t1"; +# +# The above has automatically deallocated all our statements. +# +# Attempt to CREATE a temporary table when no DB used: it should fail +# This proves that no table can be used without explicit specification of +# its database if there is no current database. +# +--error ER_NO_DB_ERROR +create temporary table t1 (i int); +# +# Restore the old environemnt +# +use test; + + +# +# BUG#21166: Prepared statement causes signal 11 on second execution +# +# Changes in an item tree done by optimizer weren't properly +# registered and went unnoticed, which resulted in preliminary freeing +# of used memory. +# +--disable_warnings +DROP TABLE IF EXISTS t1, t2, t3; +--enable_warnings + +CREATE TABLE t1 (i BIGINT, j BIGINT); +CREATE TABLE t2 (i BIGINT); +CREATE TABLE t3 (i BIGINT, j BIGINT); + +PREPARE stmt FROM "SELECT * FROM t1 JOIN t2 ON (t2.i = t1.i) + LEFT JOIN t3 ON ((t3.i, t3.j) = (t1.i, t1.j)) + WHERE t1.i = ?"; + +SET @a= 1; +EXECUTE stmt USING @a; +EXECUTE stmt USING @a; + +DEALLOCATE PREPARE stmt; +DROP TABLE IF EXISTS t1, t2, t3; + + +--echo End of 5.0 tests. |