From 8368f437a79976c77ea82b953052fdb6e08a68fc Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 30 Jun 2006 00:21:55 +0400 Subject: Bug#20230: routine_definition is not null SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION are fixed as well as INFORMATION_SCHEMA.ROUTINES.ROUTINE_NAME. mysql-test/r/information_schema.result: Add result for bug#20230. mysql-test/t/information_schema.test: Add test case for bug#20230. sql/sp_head.cc: Return NULL for routine definition if the user doesn't have enough privilege to see it. sql/sql_show.cc: Make INFORMATION_SCHEMA.ROUTINES.ROUTINE_NAME NULL-able. Return NULL if the user doesn't have enough privilege to see routine definition. --- mysql-test/r/information_schema.result | 52 ++++++++++++++++++++++++++++------ mysql-test/t/information_schema.test | 36 +++++++++++++++++++++++ 2 files changed, 80 insertions(+), 8 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 64969fcdf44..a2feba7ad5d 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -294,26 +294,26 @@ show create function sub1; ERROR 42000: FUNCTION sub1 does not exist select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; ROUTINE_NAME ROUTINE_DEFINITION -sel2 -sub1 +sel2 NULL +sub1 NULL grant all privileges on test.* to mysqltest_1@localhost; select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; ROUTINE_NAME ROUTINE_DEFINITION -sel2 -sub1 +sel2 NULL +sub1 NULL create function sub2(i int) returns int return i+1; select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; ROUTINE_NAME ROUTINE_DEFINITION -sel2 -sub1 +sel2 NULL +sub1 NULL sub2 return i+1 show create procedure sel2; Procedure sql_mode Create Procedure -sel2 +sel2 NULL show create function sub1; Function sql_mode Create Function -sub1 +sub1 NULL show create function sub2; Function sql_mode Create Function sub2 CREATE DEFINER=`mysqltest_1`@`localhost` FUNCTION `sub2`(i int) RETURNS int(11) @@ -1134,3 +1134,39 @@ concat(@a, table_name) @a table_name .t1 . t1 .t2 . t2 drop table t1,t2; +DROP PROCEDURE IF EXISTS p1; +DROP FUNCTION IF EXISTS f1; +CREATE PROCEDURE p1() SET @a= 1; +CREATE FUNCTION f1() RETURNS INT RETURN @a + 1; +CREATE USER mysql_bug20230@localhost; +GRANT EXECUTE ON PROCEDURE p1 TO mysql_bug20230@localhost; +GRANT EXECUTE ON FUNCTION f1 TO mysql_bug20230@localhost; +SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES; +ROUTINE_NAME ROUTINE_DEFINITION +f1 RETURN @a + 1 +p1 SET @a= 1 +SHOW CREATE PROCEDURE p1; +Procedure sql_mode Create Procedure +p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() +SET @a= 1 +SHOW CREATE FUNCTION f1; +Function sql_mode Create Function +f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11) +RETURN @a + 1 +SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES; +ROUTINE_NAME ROUTINE_DEFINITION +f1 NULL +p1 NULL +SHOW CREATE PROCEDURE p1; +Procedure sql_mode Create Procedure +p1 NULL +SHOW CREATE FUNCTION f1; +Function sql_mode Create Function +f1 NULL +CALL p1(); +SELECT f1(); +f1() +2 +DROP FUNCTION f1; +DROP PROCEDURE p1; +DROP USER mysql_bug20230@localhost; diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 0bcd9ef8c0b..a2e19112cf9 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -852,3 +852,39 @@ create table t2(f1 char(5)); select concat(@a, table_name), @a, table_name from information_schema.tables where table_schema = 'test'; drop table t1,t2; + + +# +# Bug#20230: routine_definition is not null +# +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +CREATE PROCEDURE p1() SET @a= 1; +CREATE FUNCTION f1() RETURNS INT RETURN @a + 1; +CREATE USER mysql_bug20230@localhost; +GRANT EXECUTE ON PROCEDURE p1 TO mysql_bug20230@localhost; +GRANT EXECUTE ON FUNCTION f1 TO mysql_bug20230@localhost; + +SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES; +SHOW CREATE PROCEDURE p1; +SHOW CREATE FUNCTION f1; + +connect (conn1, localhost, mysql_bug20230,,); + +SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES; +SHOW CREATE PROCEDURE p1; +SHOW CREATE FUNCTION f1; +CALL p1(); +SELECT f1(); + +disconnect conn1; +connection default; + +DROP FUNCTION f1; +DROP PROCEDURE p1; +DROP USER mysql_bug20230@localhost; + +# End of 5.0 tests. -- cgit v1.2.1 From fc085d77ade3e0cd77aebe1456c59b951301d722 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 30 Jun 2006 18:14:22 +0400 Subject: Bug#17226: Variable set in cursor on first iteration is assigned second iterations value During assignment to the BLOB variable in routine body the value wasn't copied. mysql-test/r/sp-vars.result: Add result for bug#17226. mysql-test/t/sp-vars.test: Add test case for bug#17226. sql/field_conv.cc: Honor copy_blobs flag. --- mysql-test/r/sp-vars.result | 15 +++++++++++++++ mysql-test/t/sp-vars.test | 36 ++++++++++++++++++++++++++++++++++++ 2 files changed, 51 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/sp-vars.result b/mysql-test/r/sp-vars.result index 6b4d7b1a6d3..83ee188bfa4 100644 --- a/mysql-test/r/sp-vars.result +++ b/mysql-test/r/sp-vars.result @@ -1075,3 +1075,18 @@ SELECT f1(); f1() abc DROP FUNCTION f1; +DROP PROCEDURE IF EXISTS p1; +CREATE PROCEDURE p1() +BEGIN +DECLARE v_char VARCHAR(255); +DECLARE v_text TEXT DEFAULT ''; +SET v_char = 'abc'; +SET v_text = v_char; +SET v_char = 'def'; +SET v_text = concat(v_text, '|', v_char); +SELECT v_text; +END| +CALL p1(); +v_text +abc|def +DROP PROCEDURE p1; diff --git a/mysql-test/t/sp-vars.test b/mysql-test/t/sp-vars.test index 81504904797..48dbd4de7aa 100644 --- a/mysql-test/t/sp-vars.test +++ b/mysql-test/t/sp-vars.test @@ -1271,3 +1271,39 @@ SELECT f1(); # DROP FUNCTION f1; + + +# +# Bug#17226: Variable set in cursor on first iteration is assigned +# second iterations value +# +# The problem was in incorrect handling of local variables of type +# TEXT (BLOB). +# +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +delimiter |; +CREATE PROCEDURE p1() +BEGIN + DECLARE v_char VARCHAR(255); + DECLARE v_text TEXT DEFAULT ''; + + SET v_char = 'abc'; + + SET v_text = v_char; + + SET v_char = 'def'; + + SET v_text = concat(v_text, '|', v_char); + + SELECT v_text; +END| +delimiter ;| + +CALL p1(); + +DROP PROCEDURE p1; + +# End of 5.0 tests. -- cgit v1.2.1