MySQL :: MySQL 8.4 Reference Manual :: 15.2.15.12 Restrictions on Subqueries (original) (raw)

15.2.15.12 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. (SeeSection 10.2.2.4, “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 int 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 aNO_MERGE optimizer hint. See Section 10.9.3, “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 ofn-tuples. The permitted syntax is therefore more specifically expressed as`` rowconstructor [NOT] INtablesubquery_ ``
  • 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 forIN but not for the others is thatIN 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 Section 15.2.15.10, “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 becausef() 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.