Below is the list of changes that have just been committed into a local
mysqldoc repository of paul. When paul does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://www.mysql.com/doc/I/n/Installing_source_tree.html
ChangeSet
1.3071 05/07/19 14:01:49 paul@stripped +21 -0
Add material on feature restrictions.
refman/views.xml
1.3 05/07/19 14:01:45 paul@stripped +5 -0
Add material on feature restrictions.
refman/triggers.xml
1.9 05/07/19 14:01:44 paul@stripped +3 -1
Add material on feature restrictions.
refman/titles.ent
1.29 05/07/19 14:01:44 paul@stripped +1 -1
Add material on feature restrictions.
refman/stored-procedures.xml
1.9 05/07/19 14:01:44 paul@stripped +3 -9
Add material on feature restrictions.
refman/sql-syntax.xml
1.12 05/07/19 14:01:43 paul@stripped +15 -35
Add material on feature restrictions.
refman/restrictions.xml
1.3 05/07/19 14:01:42 paul@stripped +707 -6
Add material on feature restrictions.
refman/manual.xml
1.3 05/07/19 14:01:42 paul@stripped +2 -0
Add material on feature restrictions.
refman/Makefile
1.10 05/07/19 14:01:41 paul@stripped +1 -0
Add material on feature restrictions.
refman-5.0/views.xml
1.3 05/07/19 14:01:41 paul@stripped +5 -0
Sync.
refman-5.0/triggers.xml
1.9 05/07/19 14:01:41 paul@stripped +3 -1
Sync.
refman-5.0/titles.ent
1.26 05/07/19 14:01:41 paul@stripped +1 -1
Sync.
refman-5.0/stored-procedures.xml
1.9 05/07/19 14:01:41 paul@stripped +3 -9
Sync.
refman-5.0/sql-syntax.xml
1.19 05/07/19 14:01:40 paul@stripped +15 -35
Sync.
refman-5.0/restrictions.xml
1.3 05/07/19 14:01:39 paul@stripped +707 -6
Sync.
refman-5.0/manual.xml
1.5 05/07/19 14:01:39 paul@stripped +4 -4
Sync.
refman-5.0/Makefile
1.7 05/07/19 14:01:39 paul@stripped +3 -3
Sync.
refman-4.1/titles.ent
1.22 05/07/19 14:01:39 paul@stripped +1 -4
Sync.
refman-4.1/sql-syntax.xml
1.11 05/07/19 14:01:38 paul@stripped +15 -35
Sync.
refman-4.1/restrictions.xml
1.3 05/07/19 14:01:37 paul@stripped +313 -6
Sync.
refman-4.1/manual.xml
1.10 05/07/19 14:01:37 paul@stripped +4 -4
Sync.
refman-4.1/Makefile
1.7 05/07/19 14:01:36 paul@stripped +3 -3
Sync.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: paul
# Host: frost.snake.net
# Root: /Volumes/frost2/MySQL/bk/mysqldoc
--- 1.2/refman-4.1/restrictions.xml 2005-07-19 10:57:25 -05:00
+++ 1.3/refman-4.1/restrictions.xml 2005-07-19 14:01:37 -05:00
@@ -11,13 +11,320 @@
<title id="title-restrictions">&title-restrictions;</title>
- <indexterm type="concept">
- <primary>restrictions</primary>
- <secondary>MySQL Restrictions</secondary>
- </indexterm>
-
<para>
- This Appendix lists current restrictions in MySQL.
+ The discussion here describes restrictions that apply to the use of
+ MySQL features such as subqueries.
</para>
+
+ <section id="subquery-restrictions">
+
+ <title id="title-subquery-restrictions">&title-subquery-restrictions;</title>
+
+ <indexterm type="concept">
+ <primary>subquery restrictions</primary>
+ </indexterm>
+
+ <indexterm type="concept">
+ <primary>restrictions</primary>
+ <secondary>subqueries</secondary>
+ </indexterm>
+
+ <para>
+ Known bug to be fixed later: If you compare a
+ <literal>NULL</literal> value to a subquery using
+ <literal>ALL</literal>, <literal>ANY</literal>, or
+ <literal>SOME</literal>, and the subquery returns an empty result,
+ the comparison might evaluate to the non-standard result of
+ <literal>NULL</literal> rather than to <literal>TRUE</literal> or
+ <literal>FALSE</literal>. This is to be fixed in MySQL 5.1.
+ </para>
+
+ <para>
+ A subquery's outer statement can be any one of:
+ <literal>SELECT</literal>, <literal>INSERT</literal>,
+ <literal>UPDATE</literal>, <literal>DELETE</literal>,
+ <literal>SET</literal>, or <literal>DO</literal>.
+ </para>
+
+ <para>
+ Row comparison operations are only partially supported:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ For <literal><replaceable>expr</replaceable> IN
+ (<replaceable>subquery</replaceable>)</literal>,
+ <replaceable>expr</replaceable> can be an
+ <replaceable>n</replaceable>-tuple (specified via row
+ constructor syntax) and the subquery can return rows of
+ <replaceable>n</replaceable>-tuples.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ For <literal><replaceable>expr</replaceable>
+ <replaceable>op</replaceable> {ALL|ANY|SOME}
+ (<replaceable>subquery</replaceable>)</literal>,
+ <replaceable>expr</replaceable> must be a scalar value and the
+ subquery must be a column subquery; it cannot return
+ multiple-column rows.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ In other words, for a subquery that returns rows of
+ <replaceable>n</replaceable>-tuples, this is supported:
+ </para>
+
+<programlisting>
+(<replaceable>val_1</replaceable>, ..., <replaceable>val_n</replaceable>) IN (<replaceable>subquery</replaceable>)
+</programlisting>
+
+ <para>
+ But this is not supported:
+ </para>
+
+<programlisting>
+(<replaceable>val_1</replaceable>, ..., <replaceable>val_n</replaceable>) <replaceable>op</replaceable> {ALL|ANY|SOME} (<replaceable>subquery</replaceable>)
+</programlisting>
+
+ <para>
+ The reason for supporting row comparisons for
+ <literal>IN</literal> but not for the others is that
+ <literal>IN</literal> was implemented by rewriting it as a
+ sequence of <literal>=</literal> comparisons and
+ <literal>AND</literal> operations. This approach cannot be used
+ for <literal>ALL</literal>, <literal>ANY</literal>, or
+ <literal>SOME</literal>.
+ </para>
+
+ <para>
+ Row constructors are not well optimized. The following two
+ expressions are equivalent, but only the second can be optimized:
+ </para>
+
+<programlisting>
+(col1, col2, ...) = (val1, val2, ...)
+col1 = val1 AND col2 = val2 AND ...
+</programlisting>
+
+ <para>
+ Subquery optimization for <literal>IN</literal> is not as
+ effective as for <literal>=</literal>.
+ </para>
+
+ <para>
+ A typical case for poor <literal>IN</literal> 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.
+ </para>
+
+ <para>
+ Subqueries in the <literal>FROM</literal> 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.
+ </para>
+
+ <para>
+ 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:
+ </para>
+
+<programlisting>
+DELETE FROM t WHERE ... (SELECT ... FROM t ...);
+UPDATE t ... WHERE col = (SELECT ... FROM t ...);
+{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);
+</programlisting>
+
+ <para>
+ Exception: The preceding prohibition does not apply if you are
+ using a subquery for the modified table in the
+ <literal>FROM</literal> clause. Example:
+ </para>
+
+<programlisting>
+UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);
+</programlisting>
+
+ <para>
+ Here the prohibition does not apply because a subquery in the
+ <literal>FROM</literal> clause is materialized as a temporary
+ table, so the relevant rows in <literal>t</literal> have already
+ been selected by the time the update to <literal>t</literal> takes
+ place.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ An exception occurs for the case where an <literal>IN</literal>
+ subquery can be rewritten as a <literal>SELECT DISTINCT</literal>
+ join. Example:
+ </para>
+
+<programlisting>
+SELECT col FROM t1 WHERE id_col IN (SELECT id_col2 FROM t2 WHERE <replaceable>condition</replaceable>);
+</programlisting>
+
+ <para>
+ That statement can be rewritten as follows:
+ </para>
+
+<programlisting>
+SELECT DISTINCT col FROM t1, t2 WHERE t1.id_col = t2.id_col AND <replaceable>condition</replaceable>;
+</programlisting>
+
+ <para>
+ But in this case, the join requires an extra
+ <literal>DISTINCT</literal> operation and is not more efficient
+ than the subquery.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ Example:
+ </para>
+
+<programlisting>
+SELECT a FROM outer_table AS ot
+WHERE a IN (SELECT a FROM inner_table AS it WHERE ot.b = it.b);
+</programlisting>
+
+ <para>
+ For that query, MySQL always scans <literal>outer_table</literal>
+ first and then executes the subquery on
+ <literal>inner_table</literal> for each row. If
+ <literal>outer_table</literal> has a lot of rows and
+ <literal>inner_table</literal> has few rows, the query probably
+ will not be as fast as it could be.
+ </para>
+
+ <para>
+ The preceding query could be rewritten like this:
+ </para>
+
+<programlisting>
+SELECT a FROM outer_table AS ot, inner_table AS it
+WHERE ot.a = it.a AND ot.b = it.b;
+</programlisting>
+
+ <para>
+ In this case, we can scan the small table
+ (<literal>inner_table</literal>) and look up rows in
+ <literal>outer_table</literal>, which will be fast if there is an
+ index on <literal>(ot.a,ot.b)</literal>.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ Possible future optimization: A subquery in the
+ <literal>FROM</literal> 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.
+ </para>
+
+ <para>
+ Possible future optimization: If a subquery in the
+ <literal>FROM</literal> 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:
+ </para>
+
+<programlisting>
+SELECT * FROM (SELECT * FROM t1 WHERE t1.t1_col) AS _t1, t2 WHERE t2.t2_col;
+</programlisting>
+
+ <para>
+ The statement can be rewritten as a join like this:
+ </para>
+
+<programlisting>
+SELECT * FROM t1, t2 WHERE t1.t1_col AND t2.t2_col;
+</programlisting>
+
+ <para>
+ This type of rewriting would provide two benefits:
+ </para>
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ 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 <literal>t1</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 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 <literal>t1</literal> or
+ <literal>t2</literal> first.
+ </para>
+ </listitem>
+
+ </orderedlist>
+
+ <para>
+ Possible future optimization: For <literal>IN</literal>,
+ <literal>= ANY</literal>, <literal><> ANY</literal>,
+ <literal>= ALL</literal>, and <literal><> ALL</literal> with
+ non-correlated subqueries, use an in-memory hash for a result
+ result or a temporary table with an index for larger results.
+ Example:
+ </para>
+
+<programlisting>
+SELECT a FROM big_table AS bt
+WHERE non_key_field IN (SELECT non_key_field FROM <replaceable>table</replaceable> WHERE <replaceable>condition</replaceable>)
+</programlisting>
+
+ <para>
+ In this case, we could create a temporary table:
+ </para>
+
+<programlisting>
+CREATE TABLE t (key (non_key_field))
+(SELECT non_key_field FROM <replaceable>table</replaceable> WHERE <replaceable>condition</replaceable>)
+</programlisting>
+
+ <para>
+ Then, for each row in <literal>big_table</literal>, do a key
+ lookup in <literal>t</literal> based on
+ <literal>bt.non_key_field</literal>.
+ </para>
+
+ </section>
+
</appendix>
--- 1.2/refman-5.0/restrictions.xml 2005-07-19 10:57:35 -05:00
+++ 1.3/refman-5.0/restrictions.xml 2005-07-19 14:01:39 -05:00
@@ -11,13 +11,714 @@
<title id="title-restrictions">&title-restrictions;</title>
- <indexterm type="concept">
- <primary>restrictions</primary>
- <secondary>MySQL Restrictions</secondary>
- </indexterm>
-
<para>
- This Appendix lists current restrictions in MySQL.
+ The discussion here describes restrictions that apply to the use of
+ MySQL features such as subqueries or views.
</para>
+
+ <section id="routine-restrictions">
+
+ <title id="title-routine-restrictions">&title-routine-restrictions;</title>
+
+ <indexterm type="concept">
+ <primary>stored routine restrictions</primary>
+ </indexterm>
+
+ <indexterm type="concept">
+ <primary>restrictions</primary>
+ <secondary>stored routines</secondary>
+ </indexterm>
+
+ <indexterm type="concept">
+ <primary>trigger restrictions</primary>
+ </indexterm>
+
+ <indexterm type="concept">
+ <primary>restrictions</primary>
+ <secondary>triggers</secondary>
+ </indexterm>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ All of the restrictions for stored functions also apply to
+ triggers.
+ </para>
+
+ <para>
+ Stored routines cannot contain arbitrary SQL statements. The
+ following statements are disallowed within stored routines:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>CHECK TABLES</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>LOCK TABLES</literal>, <literal>UNLOCK
+ TABLES</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>FLUSH</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>LOAD DATA</literal>, <literal>LOAD TABLE</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ SQL prepared statements (<literal>PREPARE</literal>,
+ <literal>EXECUTE</literal>, <literal>DEALLOCATE</literal>).
+ Implication: You cannot use dynamic SQL within stored routines
+ (where you construct dynamically statements as strings and
+ then execute them).
+
+<!-- (see Bug #10975, Bug #7115, Bug #10605) -->
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ For stored functions (but not stored procedures), the following
+ additional statements are disallowed:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Statements that do explicit or implicit commit or rollback.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Statements that return a result set. This includes
+ <literal>SELECT</literal> statements that do not have an
+ <literal>INTO</literal> clause and <literal>SHOW</literal>
+ statements. A function can process a result set either with
+ <literal>SELECT … INTO</literal> or by using a cursor
+ and <literal>FETCH</literal> statements.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ Use of stored routines can cause replication problems. This issue
+ is discussed further in
+ <xref linkend="stored-procedure-logging"/>.
+ </para>
+
+ <para>
+ <literal>INFORMATION_SCHEMA</literal> does not yet have a
+ <literal>PARAMETERS</literal> table, so applications that need to
+ acquire routine parameter information at runtime must use
+ workarounds such as parsing the output of <literal>SHOW
+ CREATE</literal> statements.
+ </para>
+
+ <para>
+ There are no stored routine debugging facilities.
+ </para>
+
+ <para>
+ Stored routines use materialized cursors, not native cursors. (The
+ result set is generated and cached on the server side, and then
+ returned row by row as the client fetches it.)
+ </para>
+
+ <para>
+ <literal>CALL</literal> statements cannot be prepared. This true
+ both for server-side prepared statements and for SQL prepared
+ statements.
+ </para>
+
+ </section>
+
+ <section id="cursor-restrictions">
+
+ <title id="title-cursor-restrictions">&title-cursor-restrictions;</title>
+
+ <indexterm type="concept">
+ <primary>server-side cursor restrictions</primary>
+ </indexterm>
+
+ <indexterm type="concept">
+ <primary>restrictions</primary>
+ <secondary>server-side cursors</secondary>
+ </indexterm>
+
+ <para>
+ Server-side cursors are implemented beginning with MySQL 5.0.2 via
+ the <literal>mysql_stmt_attr_set()</literal> C API 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.
+ </para>
+
+ <para>
+ Cursors are read-only; you cannot use a cursor to update rows.
+ </para>
+
+ <para>
+ <literal>UPDATE WHERE CURRENT OF</literal> and <literal>DELETE
+ WHERE CURRENT OF</literal> are not implemented, because updatable
+ cursors are not supported.
+ </para>
+
+ <para>
+ Cursors are non-holdable (not held open after a commit).
+ </para>
+
+ <para>
+ Cursors are asensitive.
+ </para>
+
+ <para>
+ Cursors are non-scrollable.
+ </para>
+
+ <para>
+ Cursors are not named. The statement handler acts as the cursor
+ ID.
+ </para>
+
+ <para>
+ You can have open only a single cursor per prepared statement. If
+ you need several cursors, you must prepare several statements.
+ </para>
+
+ <para>
+ 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 <literal>CHECK TABLES</literal>,
+ <literal>HANDLER READ</literal>, and <literal>SHOW BINLOG
+ EVENTS</literal>.
+ </para>
+
+ </section>
+
+ <section id="subquery-restrictions">
+
+ <title id="title-subquery-restrictions">&title-subquery-restrictions;</title>
+
+ <indexterm type="concept">
+ <primary>subquery restrictions</primary>
+ </indexterm>
+
+ <indexterm type="concept">
+ <primary>restrictions</primary>
+ <secondary>subqueries</secondary>
+ </indexterm>
+
+ <para>
+ Known bug to be fixed later: If you compare a
+ <literal>NULL</literal> value to a subquery using
+ <literal>ALL</literal>, <literal>ANY</literal>, or
+ <literal>SOME</literal>, and the subquery returns an empty result,
+ the comparison might evaluate to the non-standard result of
+ <literal>NULL</literal> rather than to <literal>TRUE</literal> or
+ <literal>FALSE</literal>. This is to be fixed in MySQL 5.1.
+ </para>
+
+ <para>
+ A subquery's outer statement can be any one of:
+ <literal>SELECT</literal>, <literal>INSERT</literal>,
+ <literal>UPDATE</literal>, <literal>DELETE</literal>,
+ <literal>SET</literal>, or <literal>DO</literal>.
+ </para>
+
+ <para>
+ Row comparison operations are only partially supported:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ For <literal><replaceable>expr</replaceable> IN
+ (<replaceable>subquery</replaceable>)</literal>,
+ <replaceable>expr</replaceable> can be an
+ <replaceable>n</replaceable>-tuple (specified via row
+ constructor syntax) and the subquery can return rows of
+ <replaceable>n</replaceable>-tuples.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ For <literal><replaceable>expr</replaceable>
+ <replaceable>op</replaceable> {ALL|ANY|SOME}
+ (<replaceable>subquery</replaceable>)</literal>,
+ <replaceable>expr</replaceable> must be a scalar value and the
+ subquery must be a column subquery; it cannot return
+ multiple-column rows.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ In other words, for a subquery that returns rows of
+ <replaceable>n</replaceable>-tuples, this is supported:
+ </para>
+
+<programlisting>
+(<replaceable>val_1</replaceable>, ..., <replaceable>val_n</replaceable>) IN (<replaceable>subquery</replaceable>)
+</programlisting>
+
+ <para>
+ But this is not supported:
+ </para>
+
+<programlisting>
+(<replaceable>val_1</replaceable>, ..., <replaceable>val_n</replaceable>) <replaceable>op</replaceable> {ALL|ANY|SOME} (<replaceable>subquery</replaceable>)
+</programlisting>
+
+ <para>
+ The reason for supporting row comparisons for
+ <literal>IN</literal> but not for the others is that
+ <literal>IN</literal> was implemented by rewriting it as a
+ sequence of <literal>=</literal> comparisons and
+ <literal>AND</literal> operations. This approach cannot be used
+ for <literal>ALL</literal>, <literal>ANY</literal>, or
+ <literal>SOME</literal>.
+ </para>
+
+ <para>
+ Row constructors are not well optimized. The following two
+ expressions are equivalent, but only the second can be optimized:
+ </para>
+
+<programlisting>
+(col1, col2, ...) = (val1, val2, ...)
+col1 = val1 AND col2 = val2 AND ...
+</programlisting>
+
+ <para>
+ Subquery optimization for <literal>IN</literal> is not as
+ effective as for <literal>=</literal>.
+ </para>
+
+ <para>
+ A typical case for poor <literal>IN</literal> 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.
+ </para>
+
+ <para>
+ Subqueries in the <literal>FROM</literal> 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.
+ </para>
+
+ <para>
+ 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:
+ </para>
+
+<programlisting>
+DELETE FROM t WHERE ... (SELECT ... FROM t ...);
+UPDATE t ... WHERE col = (SELECT ... FROM t ...);
+{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);
+</programlisting>
+
+ <para>
+ Exception: The preceding prohibition does not apply if you are
+ using a subquery for the modified table in the
+ <literal>FROM</literal> clause. Example:
+ </para>
+
+<programlisting>
+UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);
+</programlisting>
+
+ <para>
+ Here the prohibition does not apply because a subquery in the
+ <literal>FROM</literal> clause is materialized as a temporary
+ table, so the relevant rows in <literal>t</literal> have already
+ been selected by the time the update to <literal>t</literal> takes
+ place.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ An exception occurs for the case where an <literal>IN</literal>
+ subquery can be rewritten as a <literal>SELECT DISTINCT</literal>
+ join. Example:
+ </para>
+
+<programlisting>
+SELECT col FROM t1 WHERE id_col IN (SELECT id_col2 FROM t2 WHERE <replaceable>condition</replaceable>);
+</programlisting>
+
+ <para>
+ That statement can be rewritten as follows:
+ </para>
+
+<programlisting>
+SELECT DISTINCT col FROM t1, t2 WHERE t1.id_col = t2.id_col AND <replaceable>condition</replaceable>;
+</programlisting>
+
+ <para>
+ But in this case, the join requires an extra
+ <literal>DISTINCT</literal> operation and is not more efficient
+ than the subquery.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ Example:
+ </para>
+
+<programlisting>
+SELECT a FROM outer_table AS ot
+WHERE a IN (SELECT a FROM inner_table AS it WHERE ot.b = it.b);
+</programlisting>
+
+ <para>
+ For that query, MySQL always scans <literal>outer_table</literal>
+ first and then executes the subquery on
+ <literal>inner_table</literal> for each row. If
+ <literal>outer_table</literal> has a lot of rows and
+ <literal>inner_table</literal> has few rows, the query probably
+ will not be as fast as it could be.
+ </para>
+
+ <para>
+ The preceding query could be rewritten like this:
+ </para>
+
+<programlisting>
+SELECT a FROM outer_table AS ot, inner_table AS it
+WHERE ot.a = it.a AND ot.b = it.b;
+</programlisting>
+
+ <para>
+ In this case, we can scan the small table
+ (<literal>inner_table</literal>) and look up rows in
+ <literal>outer_table</literal>, which will be fast if there is an
+ index on <literal>(ot.a,ot.b)</literal>.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ Possible future optimization: A subquery in the
+ <literal>FROM</literal> 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.
+ </para>
+
+ <para>
+ Possible future optimization: If a subquery in the
+ <literal>FROM</literal> 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:
+ </para>
+
+<programlisting>
+SELECT * FROM (SELECT * FROM t1 WHERE t1.t1_col) AS _t1, t2 WHERE t2.t2_col;
+</programlisting>
+
+ <para>
+ The statement can be rewritten as a join like this:
+ </para>
+
+<programlisting>
+SELECT * FROM t1, t2 WHERE t1.t1_col AND t2.t2_col;
+</programlisting>
+
+ <para>
+ This type of rewriting would provide two benefits:
+ </para>
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ 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 <literal>t1</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 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 <literal>t1</literal> or
+ <literal>t2</literal> first.
+ </para>
+ </listitem>
+
+ </orderedlist>
+
+ <para>
+ Possible future optimization: For <literal>IN</literal>,
+ <literal>= ANY</literal>, <literal><> ANY</literal>,
+ <literal>= ALL</literal>, and <literal><> ALL</literal> with
+ non-correlated subqueries, use an in-memory hash for a result
+ result or a temporary table with an index for larger results.
+ Example:
+ </para>
+
+<programlisting>
+SELECT a FROM big_table AS bt
+WHERE non_key_field IN (SELECT non_key_field FROM <replaceable>table</replaceable> WHERE <replaceable>condition</replaceable>)
+</programlisting>
+
+ <para>
+ In this case, we could create a temporary table:
+ </para>
+
+<programlisting>
+CREATE TABLE t (key (non_key_field))
+(SELECT non_key_field FROM <replaceable>table</replaceable> WHERE <replaceable>condition</replaceable>)
+</programlisting>
+
+ <para>
+ Then, for each row in <literal>big_table</literal>, do a key
+ lookup in <literal>t</literal> based on
+ <literal>bt.non_key_field</literal>.
+ </para>
+
+ </section>
+
+ <section id="view-restrictions">
+
+ <title id="title-view-restrictions">&title-view-restrictions;</title>
+
+ <indexterm type="concept">
+ <primary>view restrictions</primary>
+ </indexterm>
+
+ <indexterm type="concept">
+ <primary>restrictions</primary>
+ <secondary>views</secondary>
+ </indexterm>
+
+ <para>
+ View processing is not optimized:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ It is not possible to create an index on a view.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ A view that is processed with the temptable algorithm is
+ unable to take advantage of indexes on its underlying tables.
+ (Indexes can be used for views processed using the merge
+ algorithm.)
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ Subqueries cannot be used in the <literal>FROM</literal> clause of
+ a view. This limitation will be lifted in the future.
+ </para>
+
+ <para>
+ There is a general principle that you cannot modify a table and
+ select from the same table in a subquery. See
+ <xref linkend="subquery-restrictions"/>.
+ </para>
+
+ <para>
+ 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:
+ </para>
+
+<programlisting>
+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;
+</programlisting>
+
+ <para>
+ If the view is evaluated using a temporary table, you
+ <emphasis>can</emphasis> 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 <quote>at
+ the same time.</quote> (This is another reason you might wish to
+ force MySQL to use the temptable algorithm by specifying
+ <literal>ALGORITHM = TEMPTABLE</literal> keyword in the view
+ definition.)
+ </para>
+
+ <para>
+ You can use <literal>DROP TABLE</literal> or <literal>ALTER
+ TABLE</literal> 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.
+ </para>
+
+ <para>
+ A view definition is <quote>frozen</quote> by certain statements:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ If a statement prepared by <literal>PREPARE</literal> 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:
+ </para>
+
+<programlisting>
+CREATE VIEW v AS SELECT 1;
+PREPARE s FROM 'SELECT * FROM v';
+ALTER VIEW v AS SELECT 2;
+EXECUTE s;
+</programlisting>
+
+ <para>
+ The result returned by the <literal>EXECUTE</literal>
+ statement is 1, not 2.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 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:
+ </para>
+
+<programlisting>
+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();
+</programlisting>
+
+ <para>
+ 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.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+<!--
+ For SQL prepared statements, this restriction could be lifted when
+ the table definition cache is implemented.
+-->
+
+ <para>
+ 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
+ <literal>UNION</literal> in their definition. Currently, not all
+ views that are theoretically updatable can be updated. We
+ deliberately wrote the initial view implementation 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:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Updatable views with subqueries anywhere other than in the
+ <literal>WHERE</literal> clause. Some views that have
+ subqueries in the <literal>SELECT</literal> list may be
+ updatable.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ You cannot use <literal>UPDATE</literal> to update more than
+ one underlying table of a view that is defined as a join.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ You cannot use <literal>DELETE</literal> to update a view that
+ is defined as a join.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
</appendix>
--- 1.9/refman/Makefile 2005-07-16 17:58:14 -05:00
+++ 1.10/refman/Makefile 2005-07-19 14:01:41 -05:00
@@ -88,6 +88,7 @@
porting.xml \
environment-variables.xml \
regexp.xml \
+ restrictions.xml \
gpl-license.xml \
mysql-floss-license-exception.xml
--- 1.2/refman/restrictions.xml 2005-07-19 11:54:20 -05:00
+++ 1.3/refman/restrictions.xml 2005-07-19 14:01:42 -05:00
@@ -11,13 +11,714 @@
<title id="title-restrictions">&title-restrictions;</title>
- <indexterm type="concept">
- <primary>restrictions</primary>
- <secondary>MySQL Restrictions</secondary>
- </indexterm>
-
<para>
- This Appendix lists current restrictions in MySQL.
+ The discussion here describes restrictions that apply to the use of
+ MySQL features such as subqueries or views.
</para>
+
+ <section id="routine-restrictions">
+
+ <title id="title-routine-restrictions">&title-routine-restrictions;</title>
+
+ <indexterm type="concept">
+ <primary>stored routine restrictions</primary>
+ </indexterm>
+
+ <indexterm type="concept">
+ <primary>restrictions</primary>
+ <secondary>stored routines</secondary>
+ </indexterm>
+
+ <indexterm type="concept">
+ <primary>trigger restrictions</primary>
+ </indexterm>
+
+ <indexterm type="concept">
+ <primary>restrictions</primary>
+ <secondary>triggers</secondary>
+ </indexterm>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ All of the restrictions for stored functions also apply to
+ triggers.
+ </para>
+
+ <para>
+ Stored routines cannot contain arbitrary SQL statements. The
+ following statements are disallowed within stored routines:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>CHECK TABLES</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>LOCK TABLES</literal>, <literal>UNLOCK
+ TABLES</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>FLUSH</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>LOAD DATA</literal>, <literal>LOAD TABLE</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ SQL prepared statements (<literal>PREPARE</literal>,
+ <literal>EXECUTE</literal>, <literal>DEALLOCATE</literal>).
+ Implication: You cannot use dynamic SQL within stored routines
+ (where you construct dynamically statements as strings and
+ then execute them).
+
+<!-- (see Bug #10975, Bug #7115, Bug #10605) -->
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ For stored functions (but not stored procedures), the following
+ additional statements are disallowed:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Statements that do explicit or implicit commit or rollback.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Statements that return a result set. This includes
+ <literal>SELECT</literal> statements that do not have an
+ <literal>INTO</literal> clause and <literal>SHOW</literal>
+ statements. A function can process a result set either with
+ <literal>SELECT … INTO</literal> or by using a cursor
+ and <literal>FETCH</literal> statements.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ Use of stored routines can cause replication problems. This issue
+ is discussed further in
+ <xref linkend="stored-procedure-logging"/>.
+ </para>
+
+ <para>
+ <literal>INFORMATION_SCHEMA</literal> does not yet have a
+ <literal>PARAMETERS</literal> table, so applications that need to
+ acquire routine parameter information at runtime must use
+ workarounds such as parsing the output of <literal>SHOW
+ CREATE</literal> statements.
+ </para>
+
+ <para>
+ There are no stored routine debugging facilities.
+ </para>
+
+ <para>
+ Stored routines use materialized cursors, not native cursors. (The
+ result set is generated and cached on the server side, and then
+ returned row by row as the client fetches it.)
+ </para>
+
+ <para>
+ <literal>CALL</literal> statements cannot be prepared. This true
+ both for server-side prepared statements and for SQL prepared
+ statements.
+ </para>
+
+ </section>
+
+ <section id="cursor-restrictions">
+
+ <title id="title-cursor-restrictions">&title-cursor-restrictions;</title>
+
+ <indexterm type="concept">
+ <primary>server-side cursor restrictions</primary>
+ </indexterm>
+
+ <indexterm type="concept">
+ <primary>restrictions</primary>
+ <secondary>server-side cursors</secondary>
+ </indexterm>
+
+ <para>
+ Server-side cursors are implemented beginning with MySQL 5.0.2 via
+ the <literal>mysql_stmt_attr_set()</literal> C API 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.
+ </para>
+
+ <para>
+ Cursors are read-only; you cannot use a cursor to update rows.
+ </para>
+
+ <para>
+ <literal>UPDATE WHERE CURRENT OF</literal> and <literal>DELETE
+ WHERE CURRENT OF</literal> are not implemented, because updatable
+ cursors are not supported.
+ </para>
+
+ <para>
+ Cursors are non-holdable (not held open after a commit).
+ </para>
+
+ <para>
+ Cursors are asensitive.
+ </para>
+
+ <para>
+ Cursors are non-scrollable.
+ </para>
+
+ <para>
+ Cursors are not named. The statement handler acts as the cursor
+ ID.
+ </para>
+
+ <para>
+ You can have open only a single cursor per prepared statement. If
+ you need several cursors, you must prepare several statements.
+ </para>
+
+ <para>
+ 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 <literal>CHECK TABLES</literal>,
+ <literal>HANDLER READ</literal>, and <literal>SHOW BINLOG
+ EVENTS</literal>.
+ </para>
+
+ </section>
+
+ <section id="subquery-restrictions">
+
+ <title id="title-subquery-restrictions">&title-subquery-restrictions;</title>
+
+ <indexterm type="concept">
+ <primary>subquery restrictions</primary>
+ </indexterm>
+
+ <indexterm type="concept">
+ <primary>restrictions</primary>
+ <secondary>subqueries</secondary>
+ </indexterm>
+
+ <para>
+ Known bug to be fixed later: If you compare a
+ <literal>NULL</literal> value to a subquery using
+ <literal>ALL</literal>, <literal>ANY</literal>, or
+ <literal>SOME</literal>, and the subquery returns an empty result,
+ the comparison might evaluate to the non-standard result of
+ <literal>NULL</literal> rather than to <literal>TRUE</literal> or
+ <literal>FALSE</literal>. This is to be fixed in MySQL 5.1.
+ </para>
+
+ <para>
+ A subquery's outer statement can be any one of:
+ <literal>SELECT</literal>, <literal>INSERT</literal>,
+ <literal>UPDATE</literal>, <literal>DELETE</literal>,
+ <literal>SET</literal>, or <literal>DO</literal>.
+ </para>
+
+ <para>
+ Row comparison operations are only partially supported:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ For <literal><replaceable>expr</replaceable> IN
+ (<replaceable>subquery</replaceable>)</literal>,
+ <replaceable>expr</replaceable> can be an
+ <replaceable>n</replaceable>-tuple (specified via row
+ constructor syntax) and the subquery can return rows of
+ <replaceable>n</replaceable>-tuples.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ For <literal><replaceable>expr</replaceable>
+ <replaceable>op</replaceable> {ALL|ANY|SOME}
+ (<replaceable>subquery</replaceable>)</literal>,
+ <replaceable>expr</replaceable> must be a scalar value and the
+ subquery must be a column subquery; it cannot return
+ multiple-column rows.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ In other words, for a subquery that returns rows of
+ <replaceable>n</replaceable>-tuples, this is supported:
+ </para>
+
+<programlisting>
+(<replaceable>val_1</replaceable>, ..., <replaceable>val_n</replaceable>) IN (<replaceable>subquery</replaceable>)
+</programlisting>
+
+ <para>
+ But this is not supported:
+ </para>
+
+<programlisting>
+(<replaceable>val_1</replaceable>, ..., <replaceable>val_n</replaceable>) <replaceable>op</replaceable> {ALL|ANY|SOME} (<replaceable>subquery</replaceable>)
+</programlisting>
+
+ <para>
+ The reason for supporting row comparisons for
+ <literal>IN</literal> but not for the others is that
+ <literal>IN</literal> was implemented by rewriting it as a
+ sequence of <literal>=</literal> comparisons and
+ <literal>AND</literal> operations. This approach cannot be used
+ for <literal>ALL</literal>, <literal>ANY</literal>, or
+ <literal>SOME</literal>.
+ </para>
+
+ <para>
+ Row constructors are not well optimized. The following two
+ expressions are equivalent, but only the second can be optimized:
+ </para>
+
+<programlisting>
+(col1, col2, ...) = (val1, val2, ...)
+col1 = val1 AND col2 = val2 AND ...
+</programlisting>
+
+ <para>
+ Subquery optimization for <literal>IN</literal> is not as
+ effective as for <literal>=</literal>.
+ </para>
+
+ <para>
+ A typical case for poor <literal>IN</literal> 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.
+ </para>
+
+ <para>
+ Subqueries in the <literal>FROM</literal> 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.
+ </para>
+
+ <para>
+ 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:
+ </para>
+
+<programlisting>
+DELETE FROM t WHERE ... (SELECT ... FROM t ...);
+UPDATE t ... WHERE col = (SELECT ... FROM t ...);
+{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);
+</programlisting>
+
+ <para>
+ Exception: The preceding prohibition does not apply if you are
+ using a subquery for the modified table in the
+ <literal>FROM</literal> clause. Example:
+ </para>
+
+<programlisting>
+UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);
+</programlisting>
+
+ <para>
+ Here the prohibition does not apply because a subquery in the
+ <literal>FROM</literal> clause is materialized as a temporary
+ table, so the relevant rows in <literal>t</literal> have already
+ been selected by the time the update to <literal>t</literal> takes
+ place.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ An exception occurs for the case where an <literal>IN</literal>
+ subquery can be rewritten as a <literal>SELECT DISTINCT</literal>
+ join. Example:
+ </para>
+
+<programlisting>
+SELECT col FROM t1 WHERE id_col IN (SELECT id_col2 FROM t2 WHERE <replaceable>condition</replaceable>);
+</programlisting>
+
+ <para>
+ That statement can be rewritten as follows:
+ </para>
+
+<programlisting>
+SELECT DISTINCT col FROM t1, t2 WHERE t1.id_col = t2.id_col AND <replaceable>condition</replaceable>;
+</programlisting>
+
+ <para>
+ But in this case, the join requires an extra
+ <literal>DISTINCT</literal> operation and is not more efficient
+ than the subquery.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ Example:
+ </para>
+
+<programlisting>
+SELECT a FROM outer_table AS ot
+WHERE a IN (SELECT a FROM inner_table AS it WHERE ot.b = it.b);
+</programlisting>
+
+ <para>
+ For that query, MySQL always scans <literal>outer_table</literal>
+ first and then executes the subquery on
+ <literal>inner_table</literal> for each row. If
+ <literal>outer_table</literal> has a lot of rows and
+ <literal>inner_table</literal> has few rows, the query probably
+ will not be as fast as it could be.
+ </para>
+
+ <para>
+ The preceding query could be rewritten like this:
+ </para>
+
+<programlisting>
+SELECT a FROM outer_table AS ot, inner_table AS it
+WHERE ot.a = it.a AND ot.b = it.b;
+</programlisting>
+
+ <para>
+ In this case, we can scan the small table
+ (<literal>inner_table</literal>) and look up rows in
+ <literal>outer_table</literal>, which will be fast if there is an
+ index on <literal>(ot.a,ot.b)</literal>.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ Possible future optimization: A subquery in the
+ <literal>FROM</literal> 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.
+ </para>
+
+ <para>
+ Possible future optimization: If a subquery in the
+ <literal>FROM</literal> 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:
+ </para>
+
+<programlisting>
+SELECT * FROM (SELECT * FROM t1 WHERE t1.t1_col) AS _t1, t2 WHERE t2.t2_col;
+</programlisting>
+
+ <para>
+ The statement can be rewritten as a join like this:
+ </para>
+
+<programlisting>
+SELECT * FROM t1, t2 WHERE t1.t1_col AND t2.t2_col;
+</programlisting>
+
+ <para>
+ This type of rewriting would provide two benefits:
+ </para>
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ 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 <literal>t1</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 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 <literal>t1</literal> or
+ <literal>t2</literal> first.
+ </para>
+ </listitem>
+
+ </orderedlist>
+
+ <para>
+ Possible future optimization: For <literal>IN</literal>,
+ <literal>= ANY</literal>, <literal><> ANY</literal>,
+ <literal>= ALL</literal>, and <literal><> ALL</literal> with
+ non-correlated subqueries, use an in-memory hash for a result
+ result or a temporary table with an index for larger results.
+ Example:
+ </para>
+
+<programlisting>
+SELECT a FROM big_table AS bt
+WHERE non_key_field IN (SELECT non_key_field FROM <replaceable>table</replaceable> WHERE <replaceable>condition</replaceable>)
+</programlisting>
+
+ <para>
+ In this case, we could create a temporary table:
+ </para>
+
+<programlisting>
+CREATE TABLE t (key (non_key_field))
+(SELECT non_key_field FROM <replaceable>table</replaceable> WHERE <replaceable>condition</replaceable>)
+</programlisting>
+
+ <para>
+ Then, for each row in <literal>big_table</literal>, do a key
+ lookup in <literal>t</literal> based on
+ <literal>bt.non_key_field</literal>.
+ </para>
+
+ </section>
+
+ <section id="view-restrictions">
+
+ <title id="title-view-restrictions">&title-view-restrictions;</title>
+
+ <indexterm type="concept">
+ <primary>view restrictions</primary>
+ </indexterm>
+
+ <indexterm type="concept">
+ <primary>restrictions</primary>
+ <secondary>views</secondary>
+ </indexterm>
+
+ <para>
+ View processing is not optimized:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ It is not possible to create an index on a view.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ A view that is processed with the temptable algorithm is
+ unable to take advantage of indexes on its underlying tables.
+ (Indexes can be used for views processed using the merge
+ algorithm.)
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ Subqueries cannot be used in the <literal>FROM</literal> clause of
+ a view. This limitation will be lifted in the future.
+ </para>
+
+ <para>
+ There is a general principle that you cannot modify a table and
+ select from the same table in a subquery. See
+ <xref linkend="subquery-restrictions"/>.
+ </para>
+
+ <para>
+ 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:
+ </para>
+
+<programlisting>
+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;
+</programlisting>
+
+ <para>
+ If the view is evaluated using a temporary table, you
+ <emphasis>can</emphasis> 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 <quote>at
+ the same time.</quote> (This is another reason you might wish to
+ force MySQL to use the temptable algorithm by specifying
+ <literal>ALGORITHM = TEMPTABLE</literal> keyword in the view
+ definition.)
+ </para>
+
+ <para>
+ You can use <literal>DROP TABLE</literal> or <literal>ALTER
+ TABLE</literal> 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.
+ </para>
+
+ <para>
+ A view definition is <quote>frozen</quote> by certain statements:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ If a statement prepared by <literal>PREPARE</literal> 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:
+ </para>
+
+<programlisting>
+CREATE VIEW v AS SELECT 1;
+PREPARE s FROM 'SELECT * FROM v';
+ALTER VIEW v AS SELECT 2;
+EXECUTE s;
+</programlisting>
+
+ <para>
+ The result returned by the <literal>EXECUTE</literal>
+ statement is 1, not 2.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 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:
+ </para>
+
+<programlisting>
+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();
+</programlisting>
+
+ <para>
+ 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.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+<!--
+ For SQL prepared statements, this restriction could be lifted when
+ the table definition cache is implemented.
+-->
+
+ <para>
+ 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
+ <literal>UNION</literal> in their definition. Currently, not all
+ views that are theoretically updatable can be updated. We
+ deliberately wrote the initial view implementation 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:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Updatable views with subqueries anywhere other than in the
+ <literal>WHERE</literal> clause. Some views that have
+ subqueries in the <literal>SELECT</literal> list may be
+ updatable.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ You cannot use <literal>UPDATE</literal> to update more than
+ one underlying table of a view that is defined as a join.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ You cannot use <literal>DELETE</literal> to update a view that
+ is defined as a join.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
</appendix>
--- 1.6/refman-4.1/Makefile 2005-07-18 04:10:56 -05:00
+++ 1.7/refman-4.1/Makefile 2005-07-19 14:01:36 -05:00
@@ -82,10 +82,10 @@
porting.xml \
environment-variables.xml \
regexp.xml \
- gpl-license.xml \
- mysql-floss-license-exception.xml \
limits.xml \
- restrictions.xml
+ restrictions.xml \
+ gpl-license.xml \
+ mysql-floss-license-exception.xml
# stored-procedures.xml \
--- 1.9/refman-4.1/manual.xml 2005-07-19 10:57:19 -05:00
+++ 1.10/refman-4.1/manual.xml 2005-07-19 14:01:37 -05:00
@@ -69,13 +69,13 @@
<xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="regexp.xml"/>
- <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="gpl-license.xml"/>
-
- <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="mysql-floss-license-exception.xml"/>
-
<xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="limits.xml"/>
<xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="restrictions.xml"/>
+
+ <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="gpl-license.xml"/>
+
+ <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="mysql-floss-license-exception.xml"/>
<index/>
--- 1.10/refman-4.1/sql-syntax.xml 2005-07-19 10:57:25 -05:00
+++ 1.11/refman-4.1/sql-syntax.xml 2005-07-19 14:01:38 -05:00
@@ -7359,14 +7359,6 @@
<primary>nested queries</primary>
</indexterm>
- <indexterm type="concept">
- <primary>nested query</primary>
- </indexterm>
-
- <indexterm type="concept">
- <primary>nesting queries</primary>
- </indexterm>
-
<para>
A subquery is a <literal>SELECT</literal> statement inside
another statement.
@@ -7462,39 +7454,27 @@
<para>
There are few restrictions on the type of statements in which
- subqueries can be used:
+ subqueries can be used. A subquery can contain any of the
+ keywords or clauses that an ordinary <literal>SELECT</literal>
+ can contain: <literal>DISTINCT</literal>, <literal>GROUP
+ BY</literal>, <literal>ORDER BY</literal>,
+ <literal>LIMIT</literal>, joins, index hints,
+ <literal>UNION</literal> constructs, comments, functions, and so
+ on.
</para>
- <itemizedlist>
-
- <listitem>
- <para>
- A subquery's outer statement can be any one of:
- <literal>SELECT</literal>, <literal>INSERT</literal>,
- <literal>UPDATE</literal>, <literal>DELETE</literal>,
- <literal>SET</literal>, or <literal>DO</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- A subquery can contain any of the keywords or clauses that
- an ordinary <literal>SELECT</literal> can contain:
- <literal>DISTINCT</literal>, <literal>GROUP BY</literal>,
- <literal>ORDER BY</literal>, <literal>LIMIT</literal>,
- joins, index hints, <literal>UNION</literal> constructs,
- comments, functions, and so on.
- </para>
- </listitem>
-
- </itemizedlist>
-
<para>
- One restriction is that currently you cannot modify a table and
+ One restriction is that a subquery's outer statement must be one
+ of: <literal>SELECT</literal>, <literal>INSERT</literal>,
+ <literal>UPDATE</literal>, <literal>DELETE</literal>,
+ <literal>SET</literal>, or <literal>DO</literal>. Another
+ restriction is that currently you cannot modify a table and
select from the same table in a subquery. This applies to
statements such as <literal>DELETE</literal>,
<literal>INSERT</literal>, <literal>REPLACE</literal>, and
- <literal>UPDATE</literal>.
+ <literal>UPDATE</literal>. A more comprehensive discussion of
+ restrictions on subquery use is given in
+ <xref linkend="restrictions"/>.
</para>
<section id="scalar-subqueries">
--- 1.21/refman-4.1/titles.ent 2005-07-19 11:54:19 -05:00
+++ 1.22/refman-4.1/titles.ent 2005-07-19 14:01:39 -05:00
@@ -715,7 +715,7 @@
<!ENTITY title-dsn-on-unix "Configuring a MyODBC DSN on Unix"><!-- "Configuring a MyODBC DSN on Unix" -->
<!ENTITY title-fetching-spatial-data "Fetching Spatial Data"><!-- "Fetching Spatial Data" -->
<!ENTITY title-news-3-21-3 "Changes in release 3.21.3"><!-- "Changes in release 3.21.3" -->
-<!ENTITY title-subquery-errors "Subquery Limitations and Errors"><!-- "Subquery Limitations and Errors" -->
+<!ENTITY title-subquery-errors "Subquery Errors">
<!ENTITY title-innodb-news-4-0-15 "MySQL/InnoDB-4.0.15, September 10, 2003"><!-- "MySQL/InnoDB-4.0.15, September 10, 2003" -->
<!ENTITY title-news-3-22-32 "Changes in release 3.22.32 (14 Feb 2000)"><!-- "Changes in release 3.22.32 (14 Feb 2000)" -->
<!ENTITY title-extensions-to-ansi "MySQL Extensions to Standard SQL"><!-- "MySQL Extensions to Standard SQL" -->
@@ -1514,7 +1514,4 @@
<!ENTITY title-datetime "The <literal>DATETIME</literal>, <literal>DATE</literal>, and <literal>TIMESTAMP</literal> Types"><!-- "The <literal>DATETIME</literal>, <literal>DATE</literal>, and <literal>TIMESTAMP</literal> Types" -->
<!ENTITY title-limits "Limits in MySQL">
<!ENTITY title-restrictions "MySQL Feature Restrictions">
-<!ENTITY title-routine-restrictions "Restrictions on Stored Routines and Triggers">
-<!ENTITY title-cursor-restrictions "Restrictions on Server-Side Cursors">
<!ENTITY title-subquery-restrictions "Restrictions on Subqueries">
-<!ENTITY title-view-restrictions "Restrictions on Views">
--- 1.6/refman-5.0/Makefile 2005-07-18 04:02:58 -05:00
+++ 1.7/refman-5.0/Makefile 2005-07-19 14:01:39 -05:00
@@ -87,10 +87,10 @@
porting.xml \
environment-variables.xml \
regexp.xml \
- gpl-license.xml \
- mysql-floss-license-exception.xml \
limits.xml \
- restrictions.xml
+ restrictions.xml \
+ gpl-license.xml \
+ mysql-floss-license-exception.xml
manual-prepped.xml: $(MANUAL_SRCS)
--- 1.4/refman-5.0/manual.xml 2005-07-19 10:57:30 -05:00
+++ 1.5/refman-5.0/manual.xml 2005-07-19 14:01:39 -05:00
@@ -79,13 +79,13 @@
<xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="regexp.xml"/>
- <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="gpl-license.xml"/>
-
- <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="mysql-floss-license-exception.xml"/>
-
<xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="limits.xml"/>
<xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="restrictions.xml"/>
+
+ <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="gpl-license.xml"/>
+
+ <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="mysql-floss-license-exception.xml"/>
<index/>
--- 1.18/refman-5.0/sql-syntax.xml 2005-07-19 10:57:36 -05:00
+++ 1.19/refman-5.0/sql-syntax.xml 2005-07-19 14:01:40 -05:00
@@ -7231,14 +7231,6 @@
<primary>nested queries</primary>
</indexterm>
- <indexterm type="concept">
- <primary>nested query</primary>
- </indexterm>
-
- <indexterm type="concept">
- <primary>nesting queries</primary>
- </indexterm>
-
<para>
A subquery is a <literal>SELECT</literal> statement within
another statement.
@@ -7327,39 +7319,27 @@
<para>
There are few restrictions on the type of statements in which
- subqueries can be used:
+ subqueries can be used. A subquery can contain any of the
+ keywords or clauses that an ordinary <literal>SELECT</literal>
+ can contain: <literal>DISTINCT</literal>, <literal>GROUP
+ BY</literal>, <literal>ORDER BY</literal>,
+ <literal>LIMIT</literal>, joins, index hints,
+ <literal>UNION</literal> constructs, comments, functions, and so
+ on.
</para>
- <itemizedlist>
-
- <listitem>
- <para>
- A subquery's outer statement can be any one of:
- <literal>SELECT</literal>, <literal>INSERT</literal>,
- <literal>UPDATE</literal>, <literal>DELETE</literal>,
- <literal>SET</literal>, or <literal>DO</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- A subquery can contain any of the keywords or clauses that
- an ordinary <literal>SELECT</literal> can contain:
- <literal>DISTINCT</literal>, <literal>GROUP BY</literal>,
- <literal>ORDER BY</literal>, <literal>LIMIT</literal>,
- joins, index hints, <literal>UNION</literal> constructs,
- comments, functions, and so on.
- </para>
- </listitem>
-
- </itemizedlist>
-
<para>
- One restriction is that currently you cannot modify a table and
+ One restriction is that a subquery's outer statement must be one
+ of: <literal>SELECT</literal>, <literal>INSERT</literal>,
+ <literal>UPDATE</literal>, <literal>DELETE</literal>,
+ <literal>SET</literal>, or <literal>DO</literal>. Another
+ restriction is that currently you cannot modify a table and
select from the same table in a subquery. This applies to
statements such as <literal>DELETE</literal>,
<literal>INSERT</literal>, <literal>REPLACE</literal>, and
- <literal>UPDATE</literal>.
+ <literal>UPDATE</literal>. A more comprehensive discussion of
+ restrictions on subquery use is given in
+ <xref linkend="restrictions"/>.
</para>
<section id="scalar-subqueries">
--- 1.8/refman-5.0/stored-procedures.xml 2005-07-19 10:57:36 -05:00
+++ 1.9/refman-5.0/stored-procedures.xml 2005-07-19 14:01:41 -05:00
@@ -11,14 +11,6 @@
<title id='title-stored-procedures'>&title-stored-procedures;</title>
-<!-- TODO: note restricted statements. -->
-
-<!-- FLUSH disallowed in stored functions. -->
-
-<!-- Dynamic SQL (PREPARE, EXECUTE, DEALLOCATE) disallowed in stored routines -->
-
-<!-- (see Bug #10975, Bug #7115, Bug #10605) -->
-
<indexterm type="concept">
<primary>stored procedures</primary>
</indexterm>
@@ -90,7 +82,9 @@
<para>
The MySQL implementation of stored procedures is still in progress.
All syntax described in this chapter is supported and any
- limitations and extensions are documented where appropriate.
+ limitations and extensions are documented where appropriate. Further
+ discussion of restrictions on use of stored procedures is given in
+ <xref linkend="restrictions"/>.
</para>
<para>
--- 1.25/refman-5.0/titles.ent 2005-07-19 11:54:20 -05:00
+++ 1.26/refman-5.0/titles.ent 2005-07-19 14:01:41 -05:00
@@ -720,7 +720,7 @@
<!ENTITY title-dsn-on-unix "Configuring a MyODBC DSN on Unix"><!-- "Configuring a MyODBC DSN on Unix" -->
<!ENTITY title-fetching-spatial-data "Fetching Spatial Data"><!-- "Fetching Spatial Data" -->
<!ENTITY title-news-3-21-3 "Changes in release 3.21.3"><!-- "Changes in release 3.21.3" -->
-<!ENTITY title-subquery-errors "Subquery Limitations and Errors"><!-- "Subquery Limitations and Errors" -->
+<!ENTITY title-subquery-errors "Subquery Errors">
<!ENTITY title-innodb-news-4-0-15 "MySQL/InnoDB-4.0.15, September 10, 2003"><!-- "MySQL/InnoDB-4.0.15, September 10, 2003" -->
<!ENTITY title-news-3-22-32 "Changes in release 3.22.32 (14 Feb 2000)"><!-- "Changes in release 3.22.32 (14 Feb 2000)" -->
<!ENTITY title-extensions-to-ansi "MySQL Extensions to Standard SQL"><!-- "MySQL Extensions to Standard SQL" -->
--- 1.8/refman-5.0/triggers.xml 2005-07-19 10:57:36 -05:00
+++ 1.9/refman-5.0/triggers.xml 2005-07-19 14:01:41 -05:00
@@ -33,7 +33,9 @@
<para>
This chapter describes the syntax for creating and dropping
- triggers, and show some examples of how to use them.
+ triggers, and show some examples of how to use them. Discussion of
+ restrictions on use of triggers is given in
+ <xref linkend="restrictions"/>.
</para>
<para>
--- 1.2/refman-5.0/views.xml 2005-07-19 10:57:37 -05:00
+++ 1.3/refman-5.0/views.xml 2005-07-19 14:01:41 -05:00
@@ -50,6 +50,11 @@
</itemizedlist>
<para>
+ Discussion of restrictions on use of views is given in
+ <xref linkend="restrictions"/>.
+ </para>
+
+ <para>
To use views if you have upgraded to MySQL 5.0.1 from an older
release, you should upgrade your grant tables so that they contain
the view-related privileges. See
--- 1.2/refman/manual.xml 2005-07-19 10:57:40 -05:00
+++ 1.3/refman/manual.xml 2005-07-19 14:01:42 -05:00
@@ -81,6 +81,8 @@
<xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="regexp.xml"/>
+ <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="restrictions.xml"/>
+
<xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="gpl-license.xml"/>
<xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="mysql-floss-license-exception.xml"/>
--- 1.11/refman/sql-syntax.xml 2005-07-19 10:57:46 -05:00
+++ 1.12/refman/sql-syntax.xml 2005-07-19 14:01:43 -05:00
@@ -7469,14 +7469,6 @@
<primary>nested queries</primary>
</indexterm>
- <indexterm type="concept">
- <primary>nested query</primary>
- </indexterm>
-
- <indexterm type="concept">
- <primary>nesting queries</primary>
- </indexterm>
-
<para>
A subquery is a <literal>SELECT</literal> statement inside
another statement.
@@ -7572,39 +7564,27 @@
<para>
There are few restrictions on the type of statements in which
- subqueries can be used:
+ subqueries can be used. A subquery can contain any of the
+ keywords or clauses that an ordinary <literal>SELECT</literal>
+ can contain: <literal>DISTINCT</literal>, <literal>GROUP
+ BY</literal>, <literal>ORDER BY</literal>,
+ <literal>LIMIT</literal>, joins, index hints,
+ <literal>UNION</literal> constructs, comments, functions, and so
+ on.
</para>
- <itemizedlist>
-
- <listitem>
- <para>
- A subquery's outer statement can be any one of:
- <literal>SELECT</literal>, <literal>INSERT</literal>,
- <literal>UPDATE</literal>, <literal>DELETE</literal>,
- <literal>SET</literal>, or <literal>DO</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- A subquery can contain any of the keywords or clauses that
- an ordinary <literal>SELECT</literal> can contain:
- <literal>DISTINCT</literal>, <literal>GROUP BY</literal>,
- <literal>ORDER BY</literal>, <literal>LIMIT</literal>,
- joins, index hints, <literal>UNION</literal> constructs,
- comments, functions, and so on.
- </para>
- </listitem>
-
- </itemizedlist>
-
<para>
- One restriction is that currently you cannot modify a table and
+ One restriction is that a subquery's outer statement must be one
+ of: <literal>SELECT</literal>, <literal>INSERT</literal>,
+ <literal>UPDATE</literal>, <literal>DELETE</literal>,
+ <literal>SET</literal>, or <literal>DO</literal>. Another
+ restriction is that currently you cannot modify a table and
select from the same table in a subquery. This applies to
statements such as <literal>DELETE</literal>,
<literal>INSERT</literal>, <literal>REPLACE</literal>, and
- <literal>UPDATE</literal>.
+ <literal>UPDATE</literal>. A more comprehensive discussion of
+ restrictions on subquery use is given in
+ <xref linkend="restrictions"/>.
</para>
<section id="scalar-subqueries">
--- 1.8/refman/stored-procedures.xml 2005-07-19 10:57:46 -05:00
+++ 1.9/refman/stored-procedures.xml 2005-07-19 14:01:44 -05:00
@@ -11,14 +11,6 @@
<title id='title-stored-procedures'>&title-stored-procedures;</title>
-<!-- TODO: note restricted statements. -->
-
-<!-- FLUSH disallowed in stored functions. -->
-
-<!-- Dynamic SQL (PREPARE, EXECUTE, DEALLOCATE) disallowed in stored routines -->
-
-<!-- (see Bug #10975, Bug #7115, Bug #10605) -->
-
<indexterm type="concept">
<primary>stored procedures</primary>
</indexterm>
@@ -90,7 +82,9 @@
<para>
The MySQL implementation of stored procedures is still in progress.
All syntax described in this chapter is supported and any
- limitations and extensions are documented where appropriate.
+ limitations and extensions are documented where appropriate. Further
+ discussion of restrictions on use of stored procedures is given in
+ <xref linkend="restrictions"/>.
</para>
<para>
--- 1.28/refman/titles.ent 2005-07-19 11:54:20 -05:00
+++ 1.29/refman/titles.ent 2005-07-19 14:01:44 -05:00
@@ -719,7 +719,7 @@
<!ENTITY title-dsn-on-unix "Configuring a MyODBC DSN on Unix"><!-- "Configuring a MyODBC DSN on Unix" -->
<!ENTITY title-fetching-spatial-data "Fetching Spatial Data"><!-- "Fetching Spatial Data" -->
<!ENTITY title-news-3-21-3 "Changes in release 3.21.3"><!-- "Changes in release 3.21.3" -->
-<!ENTITY title-subquery-errors "Subquery Limitations and Errors"><!-- "Subquery Limitations and Errors" -->
+<!ENTITY title-subquery-errors "Subquery Errors">
<!ENTITY title-innodb-news-4-0-15 "MySQL/InnoDB-4.0.15, September 10, 2003"><!-- "MySQL/InnoDB-4.0.15, September 10, 2003" -->
<!ENTITY title-news-3-22-32 "Changes in release 3.22.32 (14 Feb 2000)"><!-- "Changes in release 3.22.32 (14 Feb 2000)" -->
<!ENTITY title-extensions-to-ansi "MySQL Extensions to Standard SQL"><!-- "MySQL Extensions to Standard SQL" -->
--- 1.8/refman/triggers.xml 2005-07-19 10:57:47 -05:00
+++ 1.9/refman/triggers.xml 2005-07-19 14:01:44 -05:00
@@ -33,7 +33,9 @@
<para>
This chapter describes the syntax for creating and dropping
- triggers, and show some examples of how to use them.
+ triggers, and show some examples of how to use them. Discussion of
+ restrictions on use of triggers is given in
+ <xref linkend="restrictions"/>.
</para>
<para>
--- 1.2/refman/views.xml 2005-07-19 10:57:47 -05:00
+++ 1.3/refman/views.xml 2005-07-19 14:01:45 -05:00
@@ -50,6 +50,11 @@
</itemizedlist>
<para>
+ Discussion of restrictions on use of views is given in
+ <xref linkend="restrictions"/>.
+ </para>
+
+ <para>
To use views if you have upgraded to MySQL 5.0.1 from an older
release, you should upgrade your grant tables so that they contain
the view-related privileges. See
| Thread |
|---|
| • bk commit - mysqldoc@docsrva tree (paul:1.3071) | paul | 19 Jul |
