Bug #16757 No subquery in the FROM clause
Submitted: 24 Jan 2006 21:19 Modified: 4 Aug 2009 17:49
Reporter: Neil Pierson Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server: Views Severity:S4 (Feature request)
Version:MySQL 5.0 OS:Any (all)
Assigned to: CPU Architecture:Any

[24 Jan 2006 21:19] Neil Pierson
As you are already aware, "mysql> CREATE VIEW v3 AS SELECT * FROM 
-> (SELECT s1 FROM t1) AS x; 
ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause 
You can't create a view if the view's SELECT definition has a FROM clause that contains a subquery. This restriction is a flaw."

The removal of this restriction would be *greatly* appreciated!

How to repeat:

Suggested fix:
I was sorta hoping you guys could fix it.
[24 Jan 2006 21:49] MySQL Verification Team
Thank you for the bug report feature request.
[4 May 2006 1:57] Scott Harrison
I am having the same issue.  We are trying to migrate from ORACLE to MySQL, but one of the views will not create due to this restraint.  ORACLE allows this type of view creation.  If we cannot create a view in this manner, we will be forced to stay with ORACLE.
[13 May 2006 18:10] Chris Lo
yes I had the same problem migrating from MS Sql Server to MySQL, it would be great if you guys can roll out this feature in a near future.

My short term solution is to manually create another or more views to be joined or selected within a view object, and thus created a "nested views", but this is absolutely not scalable while working on a large database.
[16 Nov 2006 22:49] [ name withheld ]
Esta limitacion me fuerza a crear vistas innecesarias. Innecesarias porque solo las utilizo para la vista principal y nunca mas. Por si solas me son inutiles, solo cobran sentido cuando estan incluidas en la vista para la cual fueron creadas.
Espero que solucionen este problema, no entiendo porque MySql siendo infinitamente mas potente que Access, no cuenta con esta caracteristica que si incluye Access. 
Muchas Gracias.

In bad english, sorry:
This limitation it forces to me to create "unnecessary" views. That "unnecessary" views only uses for main view and never more.
[27 Nov 2006 0:14] Andrew McDonnell
I'll vote for this one
[7 Mar 2007 20:36] James Munn
I'd also appreciate the ability to use nested queries while creating a view
[7 Sep 2007 22:11] Dave Koz

And doesn't everyone need this?  This is going to just about double the number of my views in the end.

This is in your documentation as a new feature in version 4.1.
Did it work in that version?

If you don't think this is a current, supported feature.  Why don't you remove it from you documentation as valid sytax? http://dev.mysql.com/doc/refman/5.0/en/subqueries.html

Maybe you should fix this, instead of pretending the funtionality was never added.  Calling this a feature request is inaccurate.  If you want remove this from your feature list... Do so.  I am rather upset to find out this doesn't work now that I'm locked into your platform.
[18 Oct 2007 20:48] Randolph Lucas
I am anxious to see this work as well.  Since you have already implemented subqueries outside of views it seems like it shouldn't be a big leap to enable it for views.  Thanks.
[19 Oct 2007 17:44] Daniel K
I could use less queries if this bug was fixed / feature added.
[3 Mar 2008 11:19] Erico Franco
Please, fix this issue - it would be great.
[4 Apr 2008 15:35] Ian Lord
Please add this feature
[5 Apr 2008 20:53] Alit Atmaja
OK, end up with this bug report when I experienced the same restriction on MySQL.
So, I hope SUN/MySQL people with add this capability ASAP.

it's been two years passed guys :D,
still could not make sub-query in view.
or this feature only available in Ent. edition?
[11 Apr 2008 17:25] luis enrique funes
I also want fix this issue. Projects with MySQL lost elegance with  patches of this type.

[14 May 2008 19:34] Josh Duff
The same message (View's SELECT contains a subquery in the FROM clause) comes up when I try to create a view that JOINs a subquery with another subquery in its FROM clause, so:

FROM table1

The query works fine outside of the view, why this restriction inside of views?

Also, if this is how it is supposed to be acting, why is the severity listed "Feature Request"?
[16 May 2008 17:35] Amaury Rodriguez
Is anybody paying attention to this whatsoever? TWO years is a long time...
[20 Jun 2008 18:07] Stefan Fekonja
Any news about this issue?
[28 Aug 2008 2:51] [ name withheld ]
I recently bumped into this issue. A fix would be highly appreciated.
[23 Sep 2008 13:16] Purushotham Reddy
I am also having the same problem. when i am to create a view it is returning error                                                                                                                  

"#1349 - View's SELECT contains a subquery in the FROM clause"

It would be great, if this bug is fixed.
[13 Jan 2009 10:11] fabien markart

I'd like this feature too. :-/

[18 Feb 2009 10:55] tim meyer
i dont understand why it's so difficult to simply remove the restriction!! it seems completely arbitrary and i agree, this should be rated bug and not feature request

[1 Apr 2009 10:51] Vasile Ceteras
Please please, fix this BUG!

Or at least let us know when/if it will ever happen.
[20 Apr 2009 19:57] Matt Harp
Another vote to fix this issue. It makes moving from MS SQL or Oracle much more difficult than it should be...
[21 Apr 2009 3:18] Rich Saleh
Hi, just adding a note that this issue prevents me from easily migrating my stuff from MS Access! The other one tripping me up was lack of ability to refer to alias columns in a computed column in the same select statement.
[4 Aug 2009 17:49] Valeriy Kravchuk
Actually this is a duplicate of older bug #12755.
[17 Aug 2009 21:55] HaiXin Tie
Hi, check out the finding by Eric in bug 12755. He was able to enable sub-queries in the from clause in a view definition.
[11 Oct 2009 6:30] Zac Witte
I also vote for fixing this. It appears to be an extremely trivial fix that has for some reason been ignored for the past 4 years. In the other bug linked to, the following patch is proposed, but it requires modifying the MySQL source code and recompiling:

[17 Aug 23:32] Eric Bergen

I took out the safety check and tried a few different types of queries and it seems to
work fine in 5.0.27 (old I know). I think the only thing preventing this from being
enabled is the check in sql_view.cc to prevent views being created on temporary tables. It
just needs a way to distinguish between a sub query in the from clause and a real
temporary table:

      /* is this table temporary and is not view? */
      if (tbl->table->s->tmp_table != NO_TMP_TABLE && !tbl->view &&
[30 Oct 2009 21:37] Joseph Borge
This is what I have in the original 5.0.51b source:

      /* is this table temporary and is not view? */
      if (tbl->table->s->tmp_table != NO_TMP_TABLE && !tbl->view &&
        my_error(ER_VIEW_SELECT_TMPTABLE, MYF(0), tbl->alias);
        res= TRUE;
        goto err;

I can not see the difference between Eric's code and original code.
[7 Jun 2010 20:52] Mark Kendall
Bump, we're investigating and attempting to add support for MySQL to a large application designed for DB2.  

The lack of OLAP functions (specifically RANK() and DENSE_RANK()) was close to making this work not feasible but this BUG makes it a show stopper unfortunately.
[16 Jun 2010 17:42] Andrew Crouse
This is still not fixed as of v5.1.47

Please Fix
[19 Aug 2010 9:37] Jason Lam
i come across the same problem today, and finally find solution/work around, hope you will find useful.

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

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
[26 Jan 2011 19:03] German Larrain

Will this ever be fixed? I think the whole community would appreciate a response from the developers, at least saying whether they have something planned or will just ignore forever.

I've waiting for a long time.
[2 Mar 2011 19:59] simon riedel
I go with this too. 

Please, please comment on this !! Thanks
[18 Nov 2011 12:50] Abdullah Battal
This was reported in 2006 for god's sake, can somebody  at least say "nothing can be done to fix this, live with it" or better "it'll be fixed in the next five years" or so...
[24 Dec 2011 6:53] Scott Jilek
Is there any intention to allow subqueries in views?  This is just about a REQUIRED feature these days.

Granted there's a "workaround" to make the subqueries views but I'd barely consider it workable.  It's 1) not scalable and 2) creates a maintenance nightmare.  In some cases you may not easily be able to create a view.

This is a big deal, please consider adding this ASAP!
[3 May 2012 8:29] Arjan Saly
This bug or feature has been reported more then SIX years ago in 5.0. Now, working with 5.5 this message is still given on subqueries!

Is this ever going to be solved?
[9 Sep 2012 1:20] Stjepan Brbot
Contrary to other DBMS, at the moment, MySQL does not allow SELECT subquery in view's SELECT statement. This could be a problem with transition to MySQL but could be solved easily. So one could firstly create one view for subquery SELECT and then create wanted view which will use previously created view instead of using subquery with SELECT.
[3 Nov 2015 8:59] IGG t
Still doesn't appear to work in 5.7.9 ! !
[22 Feb 2016 7:42] Prabhakar Mishra
It would be a real life saver if this restriction is removed.
[20 Mar 2016 7:58] Roy Lyseng
This was fixed in 5.7.7, see bug#12755. Please file bug report if something still missing.