MySQL: View with Subquery in the FROM Clause Limitation

user_8784 0
Viewed 121260

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?

An example query (was buried in a comment):

SELECT temp.UserName 
FROM (SELECT u1.name as UserName, COUNT(m1.UserFromId) as SentCount 
      FROM Message m1, User u1 
      WHERE u1.uid = m1.UserFromId 
      Group BY u1.name HAVING SentCount > 3 ) as temp
mysql-error-1349 view mysql sql
5 Answers

Couldn't your query 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

Grant_Limberg_273140

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
Nikki9696_4566690
Related