Selecting using `database_name`.`viewname`.* syntax in MySQL throws "Unknown table 'database_name.viewname'"

Question

I'm having a strange issue with MySQL and database views.

I have a view defined like this:

CREATE VIEW circuits AS
(SELECT Id, Id AS Old_Id, Name FROM circuits_1)
UNION
(SELECT Id + 1000 AS Id, Id AS Old_Id, Name FROM circuits_2)

I have to join this view with a table that is in another database.

To do so, I usually prefix the table name with its database name, like db_name.table_name.

I've mapped this view using an ORM, specifying its prefix, and the resulting query is this one:

SELECT `webapp`.`circuits`.* FROM `webapp`.`circuits`

But this query returns this error:

#1051 - Unknown table 'webapp.circuits'

However, I've tried to manually run the query and remove the webapp. prefix from the SELECT statement, and it works as expected, throwing no error at all

SELECT `circuits`.* FROM `webapp`.`circuits`

Any idea why this happens?

Is it related to the way the view is defined?

EDIT

Another strange thing: Even if this query fails:

SELECT `webapp`.`circuits`.* FROM `webapp`.`circuits`

This doesn't:

SELECT `webapp`.`circuits`.Id FROM `webapp`.`circuits`

Answer 1

I was hesitant to answer, as i am not familiar enough with mysql to give a full answer. I did some testing on rextester.com though, and found the following:

If I create a table test(id int), I can query it using its fully qualified object name:

SELECT rextester.test.* 
FROM rextester.test

Works, no problem.

If I create a view so_test as (Select 1 id from dual)

I cannot do the same:

SELECT rextester.so_test.* 
FROM rextester.so_test

Returns the same error you get.

I cannot conclude too much from this, as i don't know mysql well enough. However, it seems a general issue with views, not the way you created it.