Bug #60583 Unable to call user created functions from another schema

• Submitted: 22 Mar 2011 10:49
• Modified: 22 Mar 2011 13:51
• Reporter: John Henry
• Status: Verified
• Category: MySQL Server: Errors
• Severity: S3 (Non-critical)
• Version: 5.5.9
• OS: Windows
• CPU Architecture: Any
• Tags: function does not exist

[22 Mar 2011 10:49] John Henry

Description:
When trying to call a user created function in one schema from another schema, a 'function does not exist error' occurs if the schema in which the schema in which the function is located contains upper case characters.
The function is only call'able if the schema contains purely lower case characters.

(Lower_Case_Table_Name = 2)

How to repeat:
The following SQL works:

CREATE DATABASE database_a;
CREATE DATABASE database_b;

DELIMITER $$

CREATE DEFINER = 'root'@'%'
FUNCTION database_a.function1()
RETURNS INT(11)
BEGIN
  RETURN 1;
END
$$

CREATE PROCEDURE database_b.procedure1()
BEGIN
  SET @iValues = database_a.function1();
  SELECT
    @iValues;
END
$$

DELIMITER ;

CALL database_b.procedure1();

However, this SQL does not:

CREATE DATABASE Database_A;
CREATE DATABASE Database_B;

DELIMITER $$

CREATE DEFINER = 'root'@'%'
FUNCTION Database_A.function1()
RETURNS INT(11)
BEGIN
  RETURN 1;
END
$$

CREATE PROCEDURE Database_B.procedure1()
BEGIN
  SET @iValues = Database_A.function1();
  SELECT
    @iValues;
END
$$

DELIMITER ;

CALL Database_B.procedure1();

Suggested fix:
Allow the calling of functions in other schema's with both uppercase and lowercase characters.

[22 Mar 2011 13:51] MySQL Verification Team

Thank you for the bug report.

mysql 5.5 >DELIMITER ;
mysql 5.5 >
mysql 5.5 >CALL Database_B.procedure1();
ERROR 1305 (42000): FUNCTION Database_A.function1 does not exist
mysql 5.5 >

Your MySQL connection id is 1
Server version: 5.5.11 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 >CREATE DATABASE database_a;
Query OK, 1 row affected (0.00 sec)

mysql 5.5 >CREATE DATABASE database_b;
Query OK, 1 row affected (0.00 sec)

mysql 5.5 >
mysql 5.5 >DELIMITER $$
mysql 5.5 >
mysql 5.5 >CREATE DEFINER = 'root'@'%'
    -> FUNCTION database_a.function1()
    -> RETURNS INT(11)
    -> BEGIN
    ->   RETURN 1;
    -> END
    -> $$
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql 5.5 >
mysql 5.5 >CREATE PROCEDURE database_b.procedure1()
    -> BEGIN
    ->   SET @iValues = database_a.function1();
    ->   SELECT
    ->     @iValues;
    -> END
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql 5.5 >
mysql 5.5 >DELIMITER ;
mysql 5.5 >

mysql 5.5 >CALL database_b.procedure1();
+----------+
| @iValues |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

mysql 5.5 >