MySQL can run select * query but not select column on view
I've created a view with the following query:
SELECT TableA.ColA as ColA, User.ID as UserID, User.FirstName,
User.LastName, User.Email, TableC.ID as RoleID, TableC.Name as Role FROM
Permission
LEFT JOIN TableB ON TableA.ColA = TableB.ID
LEFT JOIN TableD ON TableD.ID = TableB.OrganizationID
LEFT JOIN User ON User.ID = TableA.UserID
LEFT JOIN TableC ON TableC.ID = TableA.RoleID
WHERE User.Active = 1 AND (TableA.ResourceType = 1
OR (TableA.ResourceType = 5 AND TableD.ID = TableB.OrganizationID))
OR User.Active = 1 AND TableC.ID = 1
ORDER BY ColA, UserID ASC
The view was created successfully and works as intended. I can do a SELECT
* ON view_test on the view without issue. However, if I attempt to select
a specific column from the view, such as SELECT ColA FROM view_test, it
returns with the following error message:
#1356 - View 'database.view_test' references invalid table(s) or column(s)
or function(s) or definer/invoker of view lack rights to use them
The account I'm running this on is root and I've re-granted access to
everything in this database, should something have messed up for some
reason. I've looked at the view in the mysql portion of the database and
it seems fine. I can run it without issue. I can do a SELECT ColA FROM
([view_sql_block_from_above]) as a and it works fine as well. It's only
after having been created as a view that it has these issues.
Any help would be greatly appreciated.
No comments:
Post a Comment