(mysql.info) view-restrictions

Info Catalog

(mysql.info) subquery-restrictions

(mysql.info) restrictions

(mysql.info) xa-restrictions

 
 I.4 Restrictions on Views
 =========================
 
 View processing is not optimized:
 
    * It is not possible to create an index on a view.
 
    * Indexes can be used for views processed using the merge algorithm.
      However, a view that is processed with the temptable algorithm is
      unable to take advantage of indexes on its underlying tables
      (although indexes can be used during generation of the temporary
      tables).
 
 Subqueries cannot be used in the `FROM' clause of a view. This
 limitation will be lifted in the future.
 
 There is a general principle that you cannot modify a table and select
 from the same table in a subquery. See  subquery-restrictions.
 
 The same principle also applies if you select from a view that selects
 from the table, if the view selects from the table in a subquery and
 the view is evaluated using the merge algorithm.  Example:
 
      CREATE VIEW v1 AS
      SELECT * FROM t2 WHERE EXISTS (SELECT 1 FROM t1 WHERE t1.a = t2.a);
 
      UPDATE t1, v2 SET t1.a = 1 WHERE t1.b = v2.b;
 
 If the view is evaluated using a temporary table, you _can_ select from
 the table in the view subquery and still modify that table in the outer
 query. In this case the view will be materialized and thus you are not
 really selecting from the table in a subquery and modifying it `at the
 same time.' (This is another reason you might wish to force MySQL to
 use the temptable algorithm by specifying `ALGORITHM = TEMPTABLE' in
 the view definition.)
 
 You can use `DROP TABLE' or `ALTER TABLE' to drop or alter a table that
 is used in a view definition (which invalidates the view) and no
 warning results from the drop or alter operation. An error occurs later
 when the view is used.
 
 A view definition is `frozen' by certain statements:
 
    * If a statement prepared by `PREPARE' refers to a view, the view
      contents seen each time the statement is executed later will be
      the contents of the view at the time it was prepared. This is true
      even if the view definition is changed after the statement is
      prepared and before it is executed. Example:
 
           CREATE VIEW v AS SELECT 1;
           PREPARE s FROM 'SELECT * FROM v';
           ALTER VIEW v AS SELECT 2;
           EXECUTE s;
 
      The result returned by the `EXECUTE' statement is 1, not 2.
 
    * If a statement in a stored routine refers to a view, the view
      contents seen by the statement are its contents the first time
      that statement is executed. For example, this means that if the
      statement is executed in a loop, further iterations of the
      statement see the same view contents, even if the view definition
      is changed later in the loop. Example:
 
           CREATE VIEW v AS SELECT 1;
           delimiter //
           CREATE PROCEDURE p ()
           BEGIN
             DECLARE i INT DEFAULT 0;
             WHILE i < 5 DO
               SELECT * FROM v;
               SET i = i + 1;
               ALTER VIEW v AS SELECT 2;
             END WHILE;
           END;
           //
           delimiter ;
           CALL p();
 
      When the procedure `p()' is called, the `SELECT' returns 1 each
      time through the loop, even though the view definition is changed
      within the loop.
 
 With regard to view updatability, the overall goal for views is that if
 any view is theoretically updatable, it should be updatable in
 practice. This includes views that have `UNION' in their definition.
 Currently, not all views that are theoretically updatable can be
 updated. The initial view implementation was deliberately written this
 way to get usable, updatable views into MySQL as quickly as possible.
 Many theoretically updatable views can be updated now, but limitations
 still exist:
 
    * Updatable views with subqueries anywhere other than in the `WHERE'
      clause. Some views that have subqueries in the `SELECT' list may be
      updatable.
 
    * You cannot use `UPDATE' to update more than one underlying table
      of a view that is defined as a join.
 
    * You cannot use `DELETE' to update a view that is defined as a join.
 

Info Catalog

(mysql.info) subquery-restrictions

(mysql.info) restrictions

(mysql.info) xa-restrictions