MySQL Bugs: #12755: Subquery in FROM clause of views

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:	Windows (Windows 2000)
Assigned to:		CPU Architecture:	Any

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

Bug #16757 was marked as a duplicate of this one.

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?

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.

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)
      {

Nice!

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?

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.

What's the status on this bug?

You can always define another view for the subquery.

Bug #65185 was marked as duplicate of this one.

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?

Added a duplicate bug report because this one does NOT show up when searching either using Google or the Search engine of this site!

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.

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!

Fixed in 5.7.7

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.