Chapter 5 Restrictions on Subqueries

• 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 for the modified table you are using a derived table and that derived table is materialized rather than merged into the outer query. (See Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization.) Example:

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

  Here the result from the derived table 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.

  In general, you may be able to influence the optimizer to materialize a derived table by adding a NO_MERGE optimizer hint. See Optimizer Hints.

• Row comparison operations are only partially supported:

  • For expr [NOT] IN subquery, expr can be an n-tuple (specified using row constructor syntax) and the subquery can return rows of n-tuples. The permitted syntax is therefore more specifically expressed as row_constructor [NOT] IN table_subquery

  • 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:

  (expr_1, ..., expr_n) [NOT] IN table_subquery

  But this is not supported:

  (expr_1, ..., expr_n) op {ALL|ANY|SOME} subquery

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

• MySQL does not support LIMIT in subqueries for certain subquery operators:

  mysql> SELECT * FROM t1
         WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1);
  ERROR 1235 (42000): This version of MySQL doesn't yet support
   'LIMIT & IN/ALL/ANY/SOME subquery'

  See Subquery Errors.

• MySQL permits a subquery to refer to a stored function that has data-modifying side effects such as inserting rows into a table. For example, if f() inserts rows, the following query can modify data:

  SELECT ... WHERE x IN (SELECT f() ...);

  This behavior is an extension to the SQL standard. In MySQL, it can produce nondeterministic results because f() might be executed a different number of times for different executions of a given query depending on how the optimizer chooses to handle it.

  For statement-based or mixed-format replication, one implication of this indeterminism is that such a query can produce different results on the source and its replicas.