MySQL Bugs: #16757: No subquery in the FROM clause

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:	None
Category:	MySQL Server: Views	Severity:	S4 (Feature request)
Version:	MySQL 5.0	OS:	Any (all)
Assigned to:		CPU Architecture:	Any

Description:
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:
CREATE VIEW v3 AS SELECT * FROM (SELECT s1 FROM t1) AS x;

Suggested fix:
I was sorta hoping you guys could fix it.

Thank you for the bug report feature request.

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.

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.

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.

I'll vote for this one

I'd also appreciate the ability to use nested queries while creating a view

HI
THIS IS NOT A "FEATURE REQUEST"!

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.

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.

I could use less queries if this bug was fixed / feature added.

Please, fix this issue - it would be great.

Please add this feature

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?
Thx.

I also want fix this issue. Projects with MySQL lost elegance with  patches of this type.

Greetings

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:

SELECT *
FROM table1
JOIN (SELECT * FROM (SELECT * FROM table2))

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"?

Is anybody paying attention to this whatsoever? TWO years is a long time...

Any news about this issue?

I recently bumped into this issue. A fix would be highly appreciated.

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.

Hello,

I'd like this feature too. :-/

thanks

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

tim

Please please, fix this BUG!

Or at least let us know when/if it will ever happen.

Another vote to fix this issue. It makes moving from MS SQL or Oracle much more difficult than it should be...

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.

Actually this is a duplicate of older bug #12755.

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.

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 &&
          !tbl->schema_table)
      {

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 &&
          !tbl->schema_table)
      {
        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.

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.

This is still not fixed as of v5.1.47

Please Fix

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
http://jsonlam.com/

Hello.

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.

I go with this too. 

Please, please comment on this !! Thanks

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...

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!

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?

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.

Still doesn't appear to work in 5.7.9 ! !

It would be a real life saver if this restriction is removed.

This was fixed in 5.7.7, see bug#12755. Please file bug report if something still missing.