Bug #54665 Can't create view with query that can be executed in script-tab

• Submitted: 21 Jun 2010 12:07
• Modified: 21 Jun 2010 12:49
• Reporter: Christoph Moser
• Status: Not a Bug
• Category: MySQL Workbench: SQL Editor
• Severity: S3 (Non-critical)
• Version: 5.2.22 OSS RC
• OS: Windows (Vista)
• CPU Architecture: Any
• Tags: view subquery

[21 Jun 2010 12:07] Christoph Moser

Description:
When Executing following select in a script-tab, it works and I get 6 rows at this time:
"
select Rev 
from `e3Build`.`SvnRevisionInfo` 
where Rev >= 
(
    select max(B.Rev) Revision 
    from 
    (
        select Rev from `e3Build`.`SvnRevisionInfo`
        group by Rev 
        having count(*) = 5 
    ) B
);
"

When trying to create a view with this select there comes the error message:

ERROR 1349: View's SELECT contains a subquery in the FROM clause
SQL Statement:
CREATE  OR REPLACE VIEW `e3Build`.`new_view` AS
(
    select Rev, User, Solutionname, isBuildable 
    from `e3Build`.`SvnRevisionInfo` 
    where Rev >= 
    (
        select max(B.Rev) Revision from 
        (
            select Rev from `e3Build`.`SvnRevisionInfo`
            group by Rev 
            having count(*) = 5 
        ) B
    )
)

How to repeat:
Occurres every time I try to create a view with this select.

[21 Jun 2010 12:49] Valeriy Kravchuk

This is not a bug in Workbench, but known and documented server limitation (http://dev.mysql.com/doc/refman/5.0/en/create-view.html):

" A view definition is subject to the following restrictions:

    - The SELECT statement cannot contain a subquery in the FROM clause. "

Look:

valeriy-kravchuks-macbook-pro:opt-bakporting openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.99-m4-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from (select 1 from dual) b;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> create view v1 as select * from (select 1 from dual) b;
ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause