MySQL: View with Subquery in the FROM Clause Limitation

Question

In MySQL 5.0 why does the following error occur when trying to create a view with a subquery in the FROM clause, "ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause"?

If this is a limitation of the MySQL engine then why haven't they implemented this feature yet?

Also, what are some good workarounds for this limitation?

Are there any workarounds that work for any subquery in the FROM clause or are there some queries that can not be expressed without using a subquery in the FROM clause?

Answer 1

It appears to be a known issue.

http://dev.mysql.com/doc/refman/5.1/en/unnamed-views.html

http://bugs.mysql.com/bug.php?id=16757

Many IN queries can be re-written as (left outer) joins and an IS (NOT) NULL of some sort. for example

SELECT * FROM FOO WHERE ID IN (SELECT ID FROM FOO2)

can be re-written as

SELECT FOO.* FROM FOO JOIN FOO2 ON FOO.ID=FOO2.ID

or

SELECT * FROM FOO WHERE ID NOT IN (SELECT ID FROM FOO2)

can be

SELECT FOO.* FROM FOO 
LEFT OUTER JOIN FOO2 
ON FOO.ID=FOO2.ID WHERE FOO.ID IS NULL

Answer 2

Couldn't the query in your comment just be written as:

SELECT u1.name as UserName from Message m1, User u1 
  WHERE u1.uid = m1.UserFromID GROUP BY u1.name HAVING count(m1.UserFromId)>3

That should also help with the known speed issues with subqueries in MySQL

Answer 3

i come across the same problem today, and finally find solution/work around, hope you will find useful.

i wanna create a view to show information of the most recent year, form a table mixed with records from 2009 to 2011

original select statement

SELECT a.* FROM a JOIN ( SELECT a.alias, MAX(a.year) as max_year FROM a GROUP BY a.alias) b ON a.alias=b.alias and a.year=b.max_year

solution:

1. create a view for each sub-query
2. rewrite the failed create view statement, replace the disallowed subquery with those views

CREATE VUEW v_max_year AS SELECT alias, MAX(year) as max_year FROM a GROUP BY a.alias; CREATE VIEW v_latest_info AS SELECT a.* FROM a JOIN v_max_year b ON a.alias=b.alias and a.year=b.max_year;

everything works find on mysql 5.0.45, and not much speed penalty ( compare to executing the original sub-query select without any view).

Json Lam

http://jsonlam.com/