Restrictions on Views
2985
• 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
=
[875]
comparisons and
AND
[880]
operations. This approach cannot
be used for
ALL
,
ANY
, or
SOME
.
• Prior to MySQL 5.0.26, row constructors were not well optimized; of the following two equivalent
expressions, only the second could be optimized:
(col1, col2, ...) = (val1, val2, ...)
col1 = val1 AND col2 = val2 AND ...
In MySQL 5.0.26 and later, all row equalities are converted into conjunctions of equalities between
row elements, and handled by the optimizer in the same way. (Bug #16081)
• Subqueries in the
FROM
clause cannot be correlated subqueries. They are materialized in whole
(evaluated to produce a result set) before evaluating the outer query, so they cannot be evaluated
per row of the outer query.
• 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'
• 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.
• 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 indeterminate 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 replication, one implication of this indeterminism is that such a query can produce different
results on the master and its slaves.
E.4. Restrictions on Views
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
