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: | |
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
[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.