Bug #18444 Fully qualified stored function names don't work correctly in SELECT statements
Submitted: 22 Mar 2006 23:21 Modified: 12 Nov 2009 19:11
Reporter: Beat Vontobel (Silver Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.0.19/5.1.8BK OS:Linux (Linux 2.4.21 (SuSE Professional))
Assigned to: Konstantin Osipov CPU Architecture:Any

[22 Mar 2006 23:21] Beat Vontobel
Description:
The combination of a stored function with a fully qualified name from a database (a) together with tables from another database (b) in a SELECT statement results in ERROR 1146 (Table doesn't exist) with a garbaged database name.

How to repeat:
mysql> use test;
Database changed
mysql> CREATE TABLE t (i INT);
mysql> INSERT INTO t VALUES (1), (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE DATABASE test2;
Query OK, 1 row affected (0.03 sec)

mysql> use test2;
Database changed
mysql> CREATE FUNCTION f(i INT) RETURNS INT NO SQL DETERMINISTIC RETURN i + 1;
Query OK, 0 rows affected (0.02 sec)

mysql> use test;
Database changed
mysql> SELECT i FROM t;
+------+
| i    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> SELECT test2.f(i) FROM t;
ERROR 1146 (42S02): Table '??.t' doesn't exist

The '??' is mostly some binary garbage.
[23 Mar 2006 0:54] MySQL Verification Team
Thank you for the bug report. I was able to repeat.

miguel@hegel:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.20-debug

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

mysql> use test;
Database changed
mysql> CREATE TABLE t (i INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t VALUES (1), (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE DATABASE test2;
Query OK, 1 row affected (0.01 sec)

mysql> use test2;
Database changed
mysql> CREATE FUNCTION f(i INT) RETURNS INT NO SQL DETERMINISTIC RETURN i + 1;
Query OK, 0 rows affected (0.01 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 i FROM t;
+------+
| i    |
+------+
| 1    |
| 2    |
+------+
2 rows in set (0.00 sec)

mysql> SELECT test2.f(i) FROM t;
ERROR 1146 (42S02): Table 'h4z(.t' doesn't exist
mysql> SELECT test2.f(i) FROM t;
+------------+
| test2.f(i) |
+------------+
| 2          |
| 3          |
+------------+
2 rows in set (0.00 sec)

mysql> 

--------------------------------------------------------------------
miguel@hegel:~/dbs/5.1> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.8-beta-debug

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

mysql> use test;
Database changed
mysql> CREATE TABLE t (i INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t VALUES (1), (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE DATABASE test2;
Query OK, 1 row affected (0.01 sec)

mysql> use test2;
Database changed
mysql> CREATE FUNCTION f(i INT) RETURNS INT NO SQL DETERMINISTIC RETURN i + 1;
Query OK, 0 rows affected (0.01 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 i FROM t;
+------+
| i    |
+------+
| 1    |
| 2    |
+------+
2 rows in set (0.01 sec)

mysql> SELECT test2.f(i) FROM t;
ERROR 1146 (42S02): Table 00@.t' doesn't exist
mysql> SELECT test2.f(i) FROM t;
+------------+
| test2.f(i) |
+------------+
| 2          |
| 3          |
+------------+
2 rows in set (0.00 sec)

mysql>
[11 Apr 2006 20:30] Beat Vontobel
A note on this bug and replication: It seems that you can work around this bug by fully qualifying not only the name for the stored function but also the name for all tables involved in the query (even if they are part of the default database). This works however only on a master in a replication setup, slaves will still fail (and thus desynchronize). Just to make sure this will not only be fixed on the master side...
[13 Apr 2006 13:58] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/4921
[21 Apr 2006 1:20] Konstantin Osipov
Reviewed by email, asked to make a more intrusive change in order to ensure that all cases when the bug can pop up are covered.
[24 Apr 2006 22:13] Konstantin Osipov
Tomash, could you please take it over, integrate all the patches that are submitted for the problem, implement my review comment and submit a cumulative patch for all of these problems?
Thank you,
Konstantin
[14 Jun 2006 23:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7677
[15 Jun 2006 15:16] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7709
[22 Jun 2006 19:01] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8101
[27 Jun 2006 11:20] Konstantin Osipov
Pushed into 5.0.23
[29 Jun 2006 12:42] Konstantin Osipov
Pushed into 5.0.23
[7 Jul 2006 18:13] Konstantin Osipov
Merged into 5.1 tree currently tagged 5.1.12
[13 Jul 2006 20:27] Paul DuBois
The 5.0.x fix went to 5.0.25.

Noted in 5.0.25, 5.1.12 changelogs.

Referring to a stored function qualified with the name of one
database and tables in another database caused a table doesn't exist
error.
[18 Jun 2009 6:48] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090617073019-azsawauatv99124t) (version source revid:jon.hauglid@sun.com-20090608082432-o27d1guc3g6qmk5r) (merge vers: 5.4.4-alpha) (pib:11)
[3 Nov 2009 7:16] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091102151658-j9o4wgro47m5v84d) (version source revid:alik@ibmvm-20091009114750-utkhs9029vewkluf) (merge vers: 6.0.14-alpha) (pib:13)
[3 Nov 2009 15:45] Paul DuBois
Noted in 5.4.4, 6.0.14 changelogs.
[12 Nov 2009 8:21] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091103113702-p61dlwc6ml6fxg18) (merge vers: 5.5.0-beta) (pib:13)
[12 Nov 2009 19:11] Paul DuBois
Noted in 5.5.0 changelog.