MySQL Bugs: #60347: the string "versionData" seems to be 'leaking' into the schema name space

Bug #60347	the string "versionData" seems to be 'leaking' into the schema name space
Submitted:	4 Mar 2011 19:18	Modified:	21 Jun 2011 18:39
Reporter:	Claude Warren	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.1.46, 5.1.57-bzr	OS:	MacOS (10.5.8)
Assigned to:		CPU Architecture:	Any
Tags:	schema names

Description:
A database schema name of "versionData" does not function as expected.

when a function is created in the schema it can not be called from another schema unless it has first been called from within the "versionData" schema.

After running the code below, replace "versionData" with "foo" (or some other name) and rerun the code and all the selects will work.

How to repeat:
DELIMITER ;

DROP DATABASE IF EXISTS versionData;
CREATE DATABASE  versionData ;

CREATE TABLE IF NOT EXISTS versionData.versionInfo (
  schema_name varchar(64) not null,
  schema_version int not null default 0,
  data_version int not null default 0,
  proc_version int not null default 0,
  primary key (schema_name)
);

DELIMITER $$

DROP FUNCTION IF EXISTS versionData.tryMe $$
  
CREATE FUNCTION versionData.tryMe( ) returns TEXT
BEGIN
		return 'IT WORKS';
END $$

GRANT USAGE on versionData.*  to  '%'@'%' $$
GRANT USAGE on versionData.* to '%'@'localhost' $$
GRANT EXECUTE on versionData.* to' %'@'%' $$
GRANT EXECUTE on versionData.* to' %'@'localhost' $$

DELIMITER  ;

SELECT 'in schema fq name', versionData.tryMe() FROM DUAL;

USE versionData;

SELECT 'in schema fq name', versionData.tryMe() FROM DUAL;
SELECT 'in schema short name', tryMe() FROM DUAL;
SELECT 'in schema fq name', versionData.tryMe() FROM DUAL;

Verified with current mysql-5.1:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.57-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

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

mysql> CREATE TABLE IF NOT EXISTS versionData.versionInfo (
    ->   schema_name varchar(64) not null,
    ->   schema_version int not null default 0,
    ->   data_version int not null default 0,
    ->   proc_version int not null default 0,
    ->   primary key (schema_name)
    -> );
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS versionData.tryMe $$
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE FUNCTION versionData.tryMe( ) returns TEXT BEGIN return 'IT WORKS'; END$$
Query OK, 0 rows affected (0.03 sec)

mysql> delimiter ;
mysql> SELECT 'in schema fq name', versionData.tryMe() FROM DUAL;
ERROR 1305 (42000): FUNCTION versionData.tryMe does not exist
mysql> use versionData
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT 'in schema fq name', versionData.tryMe() FROM DUAL;
ERROR 1305 (42000): FUNCTION versionData.tryMe does not exist
mysql> SELECT tryMe() FROM DUAL;
+----------+
| tryMe()  |
+----------+
| IT WORKS |
+----------+
1 row in set (0.00 sec)

mysql> SELECT 'in schema fq name', versionData.tryMe() FROM DUAL;
+-------------------+---------------------+
| in schema fq name | versionData.tryMe() |
+-------------------+---------------------+
| in schema fq name | IT WORKS            |
+-------------------+---------------------+
1 row in set (0.00 sec)

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT 'in schema fq name', versionData.tryMe() FROM DUAL;
+-------------------+---------------------+
| in schema fq name | versionData.tryMe() |
+-------------------+---------------------+
| in schema fq name | IT WORKS            |
+-------------------+---------------------+
1 row in set (0.00 sec)

Upon further investigation, it seems that the issue is with upper case letter appearing in the schema name.  If there are uppercase letters in the schema name then the call to the function in the schema from outside the schema will fail until the function is called from within the schema (use schema....) and using the short name (i.e. not fully qualified with the schema name)

5.1.55 on Windows (7/64 - 64 bit server):

Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.1.55-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> SHOW VARIABLES LIKE 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 2     |
+------------------------+-------+
1 row in set (0.00 sec)

mysql>
mysql> DROP DATABASE IF EXISTS versionData;
Query OK, 1 row affected (0.04 sec)

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

mysql>
mysql> CREATE TABLE IF NOT EXISTS versionData.versionInfo (
    ->   schema_name VARCHAR(64) NOT NULL,
    ->   schema_version INT NOT NULL DEFAULT 0,
    ->   data_version INT NOT NULL DEFAULT 0,
    ->   proc_version INT NOT NULL DEFAULT 0,
    ->   PRIMARY KEY (schema_name)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> DELIMITER $$
mysql>
mysql> DROP FUNCTION IF EXISTS versionData.tryMe $$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> CREATE FUNCTION versionData.tryMe( ) RETURNS TEXT
    -> BEGIN
    -> RETURN 'IT WORKS';
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql>
mysql> SELECT versionData.tryMe() FROM DUAL;
ERROR 1305 (42000): FUNCTION versionData.tryMe does not exist
mysql>

So this is in my understanding just one more failure (in addition to all the old ones that were not fixed) to code and test properly for 'lower_case_table_names = 2' setting.

Noted in 5.1.59, 5.5.16 changelogs.

For a database having a mixed-case name and a lower_case_table_name
value of 1 or 2, calling a stored function using a fully qualified
name including the database name failed. 

CHANGESET - http://lists.mysql.com/commits/138329