Bug #12755 Subquery in FROM clause of views
Submitted: 23 Aug 2005 14:12 Modified: 9 Mar 2015 19:02
Reporter: Kay Doebl Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:5.0.7-beta OS:Microsoft Windows (Windows 2000)
Assigned to:
Triage: Needs Triage: D5 (Feature request)

[23 Aug 2005 14:12] Kay Doebl
Description:
You write in your MySQL Reference Manual :: 21.2 CREATE VIEW Syntax

"A view definition is subject to the following restrictions:  
    The SELECT statement cannot contain a subquery in the FROM clause."
    ...

Do you intend to abolish this restriction and when?

We would have the advantage, not to change our code to extend our database support at MySQL.

How to repeat:
Mail to: Kay.Doebl@gabo.de
[4 Aug 2009 17:50] Valerii Kravchuk
Bug #16757 was marked as a duplicate of this one.
[4 Aug 2009 17:57] Josh Duff
This bug: http://bugs.mysql.com/bug.php?id=16757&error=nd has many people requesting a fix - it really is a pain in the ass to work around.

People are still posting comments on that duplicate now (it must come up higher in Google results or something).

Please fix this?  Pretty please?
[14 Aug 2009 20:54] HaiXin Tie
This most basic database feature has been requested by numerous users for over 5 years. A view without support of inner queries in the FROM clause can only be used as a toy, not in real world applications. This should be an essential part of any standard SQL implementation, and MySQL, though most widely used today, is the only one I know that doesn't support it.
[17 Aug 2009 21: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)
      {
[17 Aug 2009 21:53] HaiXin Tie
Nice!
[30 Oct 2009 21:39] 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 &&
          !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.

What changes need to be made before this would work?
[7 Jun 2010 20:53] Mark Kendall
Bump, we're investigating and attempting to add support for MySQL to a large Linux 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.
[26 Mar 2011 19:45] Brian Slezak
What's the status on this bug?
[20 Dec 2011 12:27] Arnaud Adant
You can always define another view for the subquery.
[4 May 2012 17:48] Sveta Smirnova
Bug #65185 was marked as duplicate of this one.
[7 May 2012 7:07] Arjan Saly
How about solving this bug for once? How can we ever take MySQL serious when subqueries are not allowed inviews AND this still isn't solved 7 years (!!) after the bug has been reported?
[7 May 2012 7:10] Arjan Saly
Added a duplicate bug report because this one does NOT show up when searching either using Google or the Search engine of this site!
[2 Jul 2012 10:43] Stefan Haag
I stumbled upon this issue (again) using 5.5.21. While I see different workarounds, they are only that - workarounds.
Obviously this is something coming up again and again for years. Would it be possible to have a developer commentating on this with someting more than "duplicate"? No offense.

As mentioned before, the severity of this shouldn't be S4, but more S2.
[12 Nov 2013 15:55] John Viescas
See also http://bugs.mysql.com/bug.php?id=70870.  I'm happy to loudly add my voice to the clamour to have this fixed!
[9 Mar 2015 19:02] Roy Lyseng
Fixed in 5.7.7
[27 Mar 2015 2:59] Paul Dubois
Noted in 5.7.7, 5.8.0 changelogs.

Previously, view definitions were not permitted to contain derived
tables (subqueries) in the FROM clause. This restriction has now been
lifted.