MySQL Bugs: #61112: Unqualified table names are changed silently

Bug #61112	Unqualified table names are changed silently
Submitted:	10 May 2011 7:31	Modified:	10 May 2011 13:01
Reporter:	Daniel Jaenecke	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.1.55	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL does not store VIEW definitions as entered by the user but in a somewhat "optimized" format. This is especially annoying if a VIEW references tables in the current and other databases; in that case MySQL will silently change *any* unqualified table reference to a qualified one. In the provided example this means that

... FROM table_1 AS t1 JOIN db2.table_2 ...

becomes

... FROM db1.table_1 AS t1 JOIN db2.table_2 ...

So if I for example create a dump from the base database (db1 in this case) and wish to insert it into a database with a different name I will end up with an error, since the VIEW references tables which do not exist.

How to repeat:
CREATE DATABASE db1
--------------

Query OK, 1 row affected (0.00 sec)

--------------
CREATE DATABASE db2
--------------

Query OK, 1 row affected (0.00 sec)

--------------
CREATE TABLE db1.table_1 ( id INT UNSIGNED NOT NULL PRIMARY KEY )
--------------

Query OK, 0 rows affected (0.10 sec)

--------------
CREATE TABLE db2.table_2 ( id INT UNSIGNED NOT NULL PRIMARY KEY )
--------------

Query OK, 0 rows affected (0.10 sec)

--------------
CREATE VIEW a_view AS 
SELECT	t1.id AS id_t1, t2.id AS id_t2
FROM	table_1 AS t1 
JOIN	db2.table_2 AS t2 ON t1.id = t2.id
--------------

Query OK, 0 rows affected (0.04 sec)

--------------
SHOW CREATE VIEW a_view
--------------

+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View   | Create View                                                                                                                                                                                                                    | character_set_client | collation_connection |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| a_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `a_view` AS select `t1`.`id` AS `id_t1`,`t2`.`id` AS `id_t2` from (`db1`.`table_1` `t1` join `db2`.`table_2` `t2` on((`t1`.`id` = `t2`.`id`))) | binary               | binary               |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

Suggested fix:
Simply don't touch the unqualified table references. There will be a reason why it was created that way.

Btw it would be nice if MySQL would also store the original VIEW definition, which is usually a lot better to read...

I agree about "store the original VIEW definition" part of your suggestion (but we have a feature request for that already (check bug #5159).

As for not qualifying table name with schema name, this can lead to problems with permissions check. The exact definition of view will depend on "current database" then, and this may lead to security problems. See bug #23720 also.

Any table reference should resolve to something specific before the view is accessed, in any case.

Regarding problems with permissions - this might be the true, but then again this is the case for any VIEW which does not refer to tables in other databases. MySQL will not replace table references when only "local" tables are used:

USE db1;
CREATE VIEW foo AS SELECT t1.* FROM table1 AS t1 JOIN table2 AS t2
SHOW CREATE VIEW foo;

will result in something like

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `foo` AS select `t1`.`id` AS `id` from (`table_1` `t1` join `table_2` `t2`)

*but*

USE db2;
SHOW CREATE VIEW db1.foo;

will return the above definition, but with full qualified table names.

OK, I agree that we have inconsistency here.