[mysql] MySQL: View with Subquery in the FROM Clause Limitation

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

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

I had the same problem. I wanted to create a view to show information of the most recent year, from a table with records from 2009 to 2011. Here's the original query:

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

Outline of solution:

1. create a view for each subquery
2. replace subqueries with those views

Here's the solution query:

CREATE VIEW 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;

It works fine on mysql 5.0.45, without much of a speed penalty (compared to executing the original sub-query select without any views).

create a view for each subquery is the way to go. Got it working like a charm.

You can work around this by creating a separate VIEW for any subquery you want to use and then join to that in the VIEW you're creating. Here's an example: http://blog.gruffdavies.com/2015/01/25/a-neat-mysql-hack-to-create-a-view-with-subquery-in-the-from-clause/

This is quite handy as you'll very likely want to reuse it anyway and helps you keep your SQL DRY.

create a view for each subquery is the way to go. Got it working like a charm.

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

I had the same problem. I wanted to create a view to show information of the most recent year, from a table with records from 2009 to 2011. Here's the original query:

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

Outline of solution:

1. create a view for each subquery
2. replace subqueries with those views

Here's the solution query:

CREATE VIEW 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;

It works fine on mysql 5.0.45, without much of a speed penalty (compared to executing the original sub-query select without any views).

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