Bug #6160 Error when selecting from view using function from other database

• Submitted: 19 Oct 2004 16:57
• Modified: 19 Oct 2004 19:34
• Reporter: Sam Kirkpatrick
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.1
• OS: Windows (WinXP)
• CPU Architecture: Any

[19 Oct 2004 16:57] Sam Kirkpatrick

Description:
When attempting to use a view which was created using a function belonging to a different database, ERROR 1304 (42000) is returned.  Apparently, MySQL attempts to locate the function in the current database.

Assuming the function is created in a database named 'blah1' and the view is created in a database named 'blah2', then the .frm file for the view contains the following lines:

TYPE=VIEW
query=select doubleit(2) AS `blah1.doubleit(2)`
...
source=create view vx as select blah1.doubleit(2)

The 'blah1' identifier is missing in the query.

How to repeat:
mysql> create database if not exists blah1;
Query OK, 0 rows affected (0.00 sec)

mysql> use blah1;
Database changed
mysql>
mysql> drop function if exists doubleit;
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter //;
mysql>
mysql> create function doubleit (x int) returns int
    -> begin
    ->  return x*2;
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;//
mysql>
mysql> select doubleit(2);
+-------------+
| doubleit(2) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

mysql>
mysql> create database if not exists blah2;
Query OK, 0 rows affected (0.00 sec)

mysql> use blah2;
Database changed
mysql>
mysql> select blah1.doubleit(2);
+-------------------+
| blah1.doubleit(2) |
+-------------------+
|                 4 |
+-------------------+
1 row in set (0.00 sec)

mysql>
mysql> drop view if exists vx;
Query OK, 0 rows affected (0.00 sec)

mysql> create view vx as select blah1.doubleit(2);
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select * from vx;
ERROR 1304 (42000): FUNCTION blah2.doubleit does not exist

Suggested fix:
The query for the view should be 'select identifier.function(...)', not just 'select function(...)'

[19 Oct 2004 17:43] MySQL Verification Team

I tested it with latest BK source on Linux and it seems to be fixed:

mysql> select * from vx;
+-------------------+
| blah1.doubleit(2) |
+-------------------+
|                 4 |
+-------------------+
1 row in set (0.05 sec)

mysql> select version();
+-----------------------+
| version()             |
+-----------------------+
| 5.0.2-alpha-debug-log |
+-----------------------+
1 row in set (0.02 sec)

I will test on Windows.

Thanks.

[19 Oct 2004 19:34] MySQL Verification Team

Testing latest BK source on Windows this issue was already fixed.
Thank you for the bug report.

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.2-alpha-nt-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database if not exists blah1;
Query OK, 1 row affected (0.05 sec)

mysql> use blah1;
Database changed
mysql> drop function if exists doubleit;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> delimiter //
mysql> create function doubleit (x int) returns int
    -> begin
    -> return x*2;
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select doubleit(2);
+-------------+
| doubleit(2) |
+-------------+
|           4 |
+-------------+
1 row in set (0.01 sec)

mysql> create database if not exists blah2;
Query OK, 1 row affected (0.04 sec)

mysql> use blah2;
Database changed
mysql> select blah1.doubleit(2);
+-------------------+
| blah1.doubleit(2) |
+-------------------+
|                 4 |
+-------------------+
1 row in set (0.01 sec)

mysql> drop view if exists vx;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create view vx as select blah1.doubleit(2);
Query OK, 0 rows affected (0.05 sec)

mysql> select * from vx;
+-------------------+
| blah1.doubleit(2) |
+-------------------+
|                 4 |
+-------------------+
1 row in set (0.02 sec)

mysql>