Appendix I. Feature Restrictions

Table of Contents

I.1. Restrictions on Stored Routines and Triggers
I.2. Restrictions on Server-Side Cursors
I.3. Restrictions on Subqueries
I.4. Restrictions on Views
I.5. Restrictions on XA Transactions

The discussion here describes restrictions that apply to the use of MySQL features such as subqueries or views.

I.1. Restrictions on Stored Routines and Triggers

Some of the restrictions noted here apply to all stored routines; that is, both to stored procedures and stored functions. Some of restrictions apply only to stored functions, and not to stored procedures.

All of the restrictions for stored functions also apply to triggers.

Note: If an SQL statement, such as a SELECT ... INTO statement, contains a reference to a column and a declared local variable with the same name, MySQL interprets the reference as the name of a variable. This is non-standard behavior; the order of precedence is usually column names, then SQL variables and parameters. See Section 17.2.9.3, “SELECT ... INTO Statement”.

Stored routines cannot contain arbitrary SQL statements. The following statements are disallowed within stored routines:

  • CHECK TABLES

  • LOCK TABLES, UNLOCK TABLES

  • LOAD DATA, LOAD TABLE

  • SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE). Implication: You cannot use dynamic SQL within stored routines (where you construct dynamically statements as strings and then execute them). This restriction is lifted as of MySQL 5.0.13 for stored procedures; it still applies to stored functions and triggers.

  • OPTIMIZE TABLE

For stored functions (but not stored procedures), the following additional statements are disallowed:

  • Statements that do explicit or implicit commit or rollback.

  • Statements that return a result set. This includes SELECT statements that do not have an INTO clause and SHOW statements. A function can process a result set either with SELECT … INTO or by using a cursor and FETCH statements.

  • FLUSH statements. Note that while you may use FLUSH in a stored procedure, such a stored procedure cannot be called from a stored function or trigger.

  • Note: Before MySQL 5.0.10, stored functions created with CREATE FUNCTION must not contain references to tables, with limited execeptions. They may include some SET statements that contain table references, for example SET a:= (SELECT MAX(id) FROM t), and SELECT statements that fetch values directly into variables, for example SELECT i INTO var1 FROM t.

Note that although some restrictions normally apply to stored functions and triggers but not to stored procedures, those restrictions do apply to stored procedures if they are invoked from within a stored function or trigger.

Use of stored routines can cause replication problems. This issue is discussed further in Section 17.4, “Binary Logging of Stored Routines and Triggers”.

INFORMATION_SCHEMA does not yet have a PARAMETERS table, so applications that need to acquire routine parameter information at runtime must use workarounds such as parsing the output of SHOW CREATE statements.

There are no stored routine debugging facilities.

CALL statements cannot be prepared. This true both for server-side prepared statements and for SQL prepared statements.

To prevent problems of interaction between server threads, when a client issues a statement, the server uses a snapshot of routines and triggers available for execution of the statement. That is, the server calculates a list of procedures, functions, and triggers that may be used during execution of the statement, loads them, and then proceeds to execute the statement. This means that while the statement executes, it will not see changes to routines performed by other threads.

I.2. Restrictions on Server-Side Cursors

Server-side cursors are implemented beginning with the C API in MySQL 5.0.2 via the mysql_stmt_attr_set() function. A server-side cursor allows a result set to be generated on the server side, but not transferred to the client except for those rows that the client requests. For example, if a client executes a query but is only interested in the first row, the remaining rows are not transferred.

A server-side cursor is materialized into a temporary table. Initially, this is a MEMORY table, but is converted to a MyISAM table if its size reaches the value of the max_heap_table_size system variable. (Beginning with MySQL 5.0.14, the same temporary-table implementation also is used for cursors in stored routines.) One limitation of the implementation is that for a large result set, retrieving its rows through a cursor might be slow.

Cursors are read-only; you cannot use a cursor to update rows.

UPDATE WHERE CURRENT OF and DELETE WHERE CURRENT OF are not implemented, because updatable cursors are not supported.

Cursors are non-holdable (not held open after a commit).

Cursors are asensitive.

Cursors are non-scrollable.

Cursors are not named. The statement handler acts as the cursor ID.

You can have open only a single cursor per prepared statement. If you need several cursors, you must prepare several statements.

You cannot use a cursor for a statement that generates a result set if the statement is not supported in prepared mode. This includes statements such as CHECK TABLES, HANDLER READ, and SHOW BINLOG EVENTS.

I.3. Restrictions on Subqueries

Known bug to be fixed later: If you compare a NULL value to a subquery using ALL, ANY, or SOME, and the subquery returns an empty result, the comparison might evaluate to the non-standard result of NULL rather than to TRUE or FALSE.

A subquery's outer statement can be any one of: SELECT, INSERT, UPDATE, DELETE, SET, or DO.

Row comparison operations are only partially supported:

  • For expr IN (subquery), expr can be an n-tuple (specified via row constructor syntax) and the subquery can return rows of n-tuples.

  • For expr op {ALL|ANY|SOME} (subquery), expr must be a scalar value and the subquery must be a column subquery; it cannot return multiple-column rows.

In other words, for a subquery that returns rows of n-tuples, this is supported:

(val_1, ..., val_n) IN (subquery)

But this is not supported:

(val_1, ..., val_n) op {ALL|ANY|SOME} (subquery)

The reason for supporting row comparisons for IN but not for the others is that IN was implemented by rewriting it as a sequence of = comparisons and AND operations. This approach cannot be used for ALL, ANY, or SOME.

Row constructors are not well optimized. The following two expressions are equivalent, but only the second can be optimized:

(col1, col2, ...) = (val1, val2, ...)
col1 = val1 AND col2 = val2 AND ...

Subquery optimization for IN is not as effective as for =.

A typical case for poor IN performance is when the subquery returns a small number of rows but the outer query returns a large number of rows to be compared to the subquery result.

Subqueries in the FROM clause cannot be correlated subqueries. They are materialized (executed to produce a result set) before evaluating the outer query, so they cannot be evaluated per row of the outer query.

In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:

DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

Exception: The preceding prohibition does not apply if you are using a subquery for the modified table in the FROM clause. Example:

UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);

Here the prohibition does not apply because a subquery in the FROM clause is materialized as a temporary table, so the relevant rows in t have already been selected by the time the update to t takes place.

The optimizer is more mature for joins than for subqueries, so in many cases a statement that uses a subquery can be executed more efficiently if you rewrite it as a join.

An exception occurs for the case where an IN subquery can be rewritten as a SELECT DISTINCT join. Example:

SELECT col FROM t1 WHERE id_col IN (SELECT id_col2 FROM t2 WHERE condition);

That statement can be rewritten as follows:

SELECT DISTINCT col FROM t1, t2 WHERE t1.id_col = t2.id_col AND condition;

But in this case, the join requires an extra DISTINCT operation and is not more efficient than the subquery.

Possible future optimization: MySQL doesn't rewrite the join order for subquery evaluation. In some cases, a subquery could be executed more efficiently if MySQL rewrote it as a join. This would give the optimizer a chance to choose between more execution plans. For example, it can decide whether to read one table or the other first.

Example:

SELECT a FROM outer_table AS ot
WHERE a IN (SELECT a FROM inner_table AS it WHERE ot.b = it.b);

For that query, MySQL always scans outer_table first and then executes the subquery on inner_table for each row. If outer_table has a lot of rows and inner_table has few rows, the query probably will not be as fast as it could be.

The preceding query could be rewritten like this:

SELECT a FROM outer_table AS ot, inner_table AS it
WHERE ot.a = it.a AND ot.b = it.b;

In this case, we can scan the small table (inner_table) and look up rows in outer_table, which will be fast if there is an index on (ot.a,ot.b).

Possible future optimization: A correlated subquery is evaluated for each row of the outer query. A better approach is that if the outer row values do not change from the previous row, do not evaluate the subquery again. Instead use its previous result.

Possible future optimization: A subquery in the FROM clause is evaluated by materializing the result into a temporary table, and this table does not use indexes. This does not allow the use of indexes in comparison with other tables in the query, although that might be useful.

Possible future optimization: If a subquery in the FROM clause resembles a view to which the merge algorithm can be applied, rewrite the query and apply the merge algorithm so that indexes can be used. The following statement contains such a subquery:

SELECT * FROM (SELECT * FROM t1 WHERE t1.t1_col) AS _t1, t2 WHERE t2.t2_col;

The statement can be rewritten as a join like this:

SELECT * FROM t1, t2 WHERE t1.t1_col AND t2.t2_col;

This type of rewriting would provide two benefits:

  1. It avoids the use of a temporary table for which no indexes can be used. In the rewritten query, the optimizer can use indexes on t1.

  2. It gives the optimizer more freedom to choose between different execution plans. For example, rewriting the query as a join allows the optimizer to use t1 or t2 first.

Possible future optimization: For IN, = ANY, <> ANY, = ALL, and <> ALL with non-correlated subqueries, use an in-memory hash for a result result or a temporary table with an index for larger results. Example:

SELECT a FROM big_table AS bt
WHERE non_key_field IN (SELECT non_key_field FROM table WHERE condition)

In this case, we could create a temporary table:

CREATE TABLE t (key (non_key_field))
(SELECT non_key_field FROM table WHERE condition)

Then, for each row in big_table, do a key lookup in t based on bt.non_key_field.

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 Section I.3, “Restrictions on Subqueries”.

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 aren't 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 keyword 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 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 in order 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.

I.5. Restrictions on XA Transactions

XA transaction support is limited to the InnoDB storage engine.

The MySQL XA implementation is for external XA,, where a MySQL server acts as a Resource Manager and client programs act as Transaction Managers. “Internal XA” is not implemented. This would allow individual storage engines within a MySQL server to act as RMs, and the server itself to act as a TM. Internal XA is required for handling XA transactions that involve more than one storage engine. The implementation of internal XA is incomplete because it requires that a storage engine support two-phase commit at the table handler level, and currently this is true only for InnoDB.

For XA START, the JOIN and RESUME clauses are not supported.

For XA END the SUSPEND [FOR MIGRATE] clause is not supported.

The requirement that the bqual part of the xid value be different for each XA transaction within a global transaction is a limitation of the current MySQL XA implementation. It is not part of the XA specification.

If an XA transaction has reached the PREPARED state and the MySQL server dies, the transaction can be continued after the server restarts. That is as it should be. However, if the client connection terminates and the server continues to run, the server rolls back any pending XA transaction, even one that has reached the PREPARED state. It should be possible to commit or roll back a PREPARED XA transaction, but this cannot be done without changes to the binary logging mechanism.