List:Commits« Previous MessageNext Message »
From:jon Date:April 8 2008 3:48pm
Subject:svn commit - mysqldoc@docsrva: r10430 - in trunk: it/refman-5.1 pt/refman-5.1 refman-4.1 refman-5.0 refman-5.1 refman-6.0
View as plain text  
Author: jstephens
Date: 2008-04-08 17:48:31 +0200 (Tue, 08 Apr 2008)
New Revision: 10430

Log:

EXPLAIN + subqueries can modify data

Fixes Docs Bug #31468



Modified:
   trunk/it/refman-5.1/optimization.xml
   trunk/it/refman-5.1/sql-syntax.xml
   trunk/pt/refman-5.1/optimization.xml
   trunk/pt/refman-5.1/sql-syntax.xml
   trunk/refman-4.1/optimization.xml
   trunk/refman-4.1/sql-syntax.xml
   trunk/refman-5.0/optimization.xml
   trunk/refman-5.0/sql-syntax.xml
   trunk/refman-5.1/optimization.xml
   trunk/refman-5.1/sql-syntax.xml
   trunk/refman-6.0/optimization.xml


Modified: trunk/it/refman-5.1/optimization.xml
===================================================================
--- trunk/it/refman-5.1/optimization.xml	2008-04-08 14:36:19 UTC (rev 10429)
+++ trunk/it/refman-5.1/optimization.xml	2008-04-08 15:48:31 UTC (rev 10430)
Changed blocks: 1, Lines Added: 7, Lines Deleted: 0; 642 bytes

@@ -2230,6 +2230,13 @@
         clause.
       </para>
 
+      <para>
+        It is possible in some cases to execute statements that modify
+        data when <literal>EXPLAIN SELECT</literal> is used with a
+        subquery; for more information, see
+        <xref linkend="unnamed-views"/>.
+      </para>
+
       <formalpara role="mnmas">
 
         <title>MySQL Enterprise</title>


Modified: trunk/it/refman-5.1/sql-syntax.xml
===================================================================
--- trunk/it/refman-5.1/sql-syntax.xml	2008-04-08 14:36:19 UTC (rev 10429)
+++ trunk/it/refman-5.1/sql-syntax.xml	2008-04-08 15:48:31 UTC (rev 10430)
Changed blocks: 1, Lines Added: 123, Lines Deleted: 0; 5808 bytes

@@ -12568,6 +12568,129 @@
           unless the subquery is executed.
         </para>
 
+        <para>
+          It is also possible under certain circumstances to modify
+          table data using <literal>EXPLAIN SELECT</literal>. This can
+          occur if the outer query accesses any tables and an inner
+          query invokes a stored function that changes one or more rows
+          of a table. For example, suppose there are two tables
+          <literal>t1</literal> and <literal>t2</literal> in database
+          <literal>d1</literal>, created as shown here:
+
+<programlisting>
+mysql&gt; <userinput>CREATE DATABASE d1;</userinput>
+Query OK, 1 row affected (0.00 sec)
+
+mysql&gt; <userinput>USE d1;</userinput>
+Database changed
+
+mysql&gt; <userinput>CREATE TABLE t1 (c1 INT);</userinput>
+Query OK, 0 rows affected (0.15 sec)
+
+mysql&gt; <userinput>CREATE TABLE t2 (c1 INT);</userinput>
+Query OK, 0 rows affected (0.08 sec)
+</programlisting>
+
+          Now we create a stored function <literal>f1</literal> which
+          modifies <literal>t2</literal>:
+
+<programlisting>
+mysql&gt; <userinput>DELIMITER //</userinput>
+mysql&gt; <userinput>CREATE FUNCTION f1(p1 INT) RETURNS INT</userinput> 
+mysql&gt;   <userinput>BEGIN</userinput> 
+mysql&gt;     <userinput>INSERT INTO t2 VALUES (p1);</userinput> 
+mysql&gt;     <userinput>RETURN p1;</userinput> 
+mysql&gt;   <userinput>END //</userinput>
+Query OK, 0 rows affected (0.01 sec)
+
+mysql&gt; <userinput>DELIMITER ;</userinput>
+</programlisting>
+
+          Referencing the function directly in an EXPLAIN SELECT does
+          not have any affect on t2, as shown here:
+
+<programlisting>
+mysql&gt; <userinput>SELECT * FROM t2;</userinput>
+Empty set (0.00 sec)
+
+mysql&gt; <userinput>EXPLAIN SELECT f1(5);</userinput>
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+1 row in set (0.00 sec)
+
+mysql&gt; <userinput>SELECT * FROM t2;</userinput>
+Empty set (0.00 sec)
+</programlisting>
+
+          This is because the <literal>SELECT</literal> statement did
+          not reference any tables, as can be seen in the
+          <literal>table</literal> and <literal>Extra</literal> columns
+          of the output. This is also true of the following nested
+          <literal>SELECT</literal>:
+
+<programlisting>
+mysql&gt; <userinput>EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2;</userinput>
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+|  1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+1 row in set, 1 warning (0.00 sec)
+
+mysql&gt; <userinput>SHOW WARNINGS;</userinput>
++-------+------+------------------------------------------+
+| Level | Code | Message                                  |
++-------+------+------------------------------------------+
+| Note  | 1249 | Select 2 was reduced during optimization |
++-------+------+------------------------------------------+
+1 row in set (0.00 sec)
+
+mysql&gt; <userinput>SELECT * FROM t2;</userinput>
+Empty set (0.00 sec)
+</programlisting>
+
+          However, if the outer <userinput>SELECT</userinput> references
+          any tables, then the optimizer executes the statement in the
+          subquery as well:
+
+<programlisting>
+mysql&gt; <userinput>EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2;</userinput>
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+|  1 | PRIMARY     | a1         | system | NULL          | NULL | NULL    | NULL |    0 | const row not found |
+|  1 | PRIMARY     | &lt;derived2&gt; | system | NULL          | NULL | NULL    | NULL |    1 |                     |
+|  2 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL | No tables used      |
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+3 rows in set (0.00 sec)
+
+mysql&gt; <userinput>SELECT * FROM t2;</userinput>
++------+
+| c1   |
++------+
+|    5 |
++------+
+1 row in set (0.00 sec)
+</programlisting>
+        </para>
+
+        <para>
+          This also means that an <literal>EXPLAIN SELECT</literal>
+          statement such as the one shown here may take a long time to
+          execute:
+
+<programlisting>
+EXPLAIN SELECT * FROM t1 AS a1, (SELECT BENCHMARK(1000000, MD5(NOW())));
+</programlisting>
+
+          This is because the
+          <function role="sql">BENCHMARK()</function> function is
+          executed once for each row in <literal>t1</literal>.
+        </para>
+
       </section>
 
       <section id="subquery-errors">


Modified: trunk/pt/refman-5.1/optimization.xml
===================================================================
--- trunk/pt/refman-5.1/optimization.xml	2008-04-08 14:36:19 UTC (rev 10429)
+++ trunk/pt/refman-5.1/optimization.xml	2008-04-08 15:48:31 UTC (rev 10430)
Changed blocks: 1, Lines Added: 7, Lines Deleted: 0; 642 bytes

@@ -2230,6 +2230,13 @@
         clause.
       </para>
 
+      <para>
+        It is possible in some cases to execute statements that modify
+        data when <literal>EXPLAIN SELECT</literal> is used with a
+        subquery; for more information, see
+        <xref linkend="unnamed-views"/>.
+      </para>
+
       <formalpara role="mnmas">
 
         <title>MySQL Enterprise</title>


Modified: trunk/pt/refman-5.1/sql-syntax.xml
===================================================================
--- trunk/pt/refman-5.1/sql-syntax.xml	2008-04-08 14:36:19 UTC (rev 10429)
+++ trunk/pt/refman-5.1/sql-syntax.xml	2008-04-08 15:48:31 UTC (rev 10430)
Changed blocks: 1, Lines Added: 123, Lines Deleted: 0; 5808 bytes

@@ -12568,6 +12568,129 @@
           unless the subquery is executed.
         </para>
 
+        <para>
+          It is also possible under certain circumstances to modify
+          table data using <literal>EXPLAIN SELECT</literal>. This can
+          occur if the outer query accesses any tables and an inner
+          query invokes a stored function that changes one or more rows
+          of a table. For example, suppose there are two tables
+          <literal>t1</literal> and <literal>t2</literal> in database
+          <literal>d1</literal>, created as shown here:
+
+<programlisting>
+mysql&gt; <userinput>CREATE DATABASE d1;</userinput>
+Query OK, 1 row affected (0.00 sec)
+
+mysql&gt; <userinput>USE d1;</userinput>
+Database changed
+
+mysql&gt; <userinput>CREATE TABLE t1 (c1 INT);</userinput>
+Query OK, 0 rows affected (0.15 sec)
+
+mysql&gt; <userinput>CREATE TABLE t2 (c1 INT);</userinput>
+Query OK, 0 rows affected (0.08 sec)
+</programlisting>
+
+          Now we create a stored function <literal>f1</literal> which
+          modifies <literal>t2</literal>:
+
+<programlisting>
+mysql&gt; <userinput>DELIMITER //</userinput>
+mysql&gt; <userinput>CREATE FUNCTION f1(p1 INT) RETURNS INT</userinput> 
+mysql&gt;   <userinput>BEGIN</userinput> 
+mysql&gt;     <userinput>INSERT INTO t2 VALUES (p1);</userinput> 
+mysql&gt;     <userinput>RETURN p1;</userinput> 
+mysql&gt;   <userinput>END //</userinput>
+Query OK, 0 rows affected (0.01 sec)
+
+mysql&gt; <userinput>DELIMITER ;</userinput>
+</programlisting>
+
+          Referencing the function directly in an EXPLAIN SELECT does
+          not have any affect on t2, as shown here:
+
+<programlisting>
+mysql&gt; <userinput>SELECT * FROM t2;</userinput>
+Empty set (0.00 sec)
+
+mysql&gt; <userinput>EXPLAIN SELECT f1(5);</userinput>
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+1 row in set (0.00 sec)
+
+mysql&gt; <userinput>SELECT * FROM t2;</userinput>
+Empty set (0.00 sec)
+</programlisting>
+
+          This is because the <literal>SELECT</literal> statement did
+          not reference any tables, as can be seen in the
+          <literal>table</literal> and <literal>Extra</literal> columns
+          of the output. This is also true of the following nested
+          <literal>SELECT</literal>:
+
+<programlisting>
+mysql&gt; <userinput>EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2;</userinput>
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+|  1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+1 row in set, 1 warning (0.00 sec)
+
+mysql&gt; <userinput>SHOW WARNINGS;</userinput>
++-------+------+------------------------------------------+
+| Level | Code | Message                                  |
++-------+------+------------------------------------------+
+| Note  | 1249 | Select 2 was reduced during optimization |
++-------+------+------------------------------------------+
+1 row in set (0.00 sec)
+
+mysql&gt; <userinput>SELECT * FROM t2;</userinput>
+Empty set (0.00 sec)
+</programlisting>
+
+          However, if the outer <userinput>SELECT</userinput> references
+          any tables, then the optimizer executes the statement in the
+          subquery as well:
+
+<programlisting>
+mysql&gt; <userinput>EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2;</userinput>
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+|  1 | PRIMARY     | a1         | system | NULL          | NULL | NULL    | NULL |    0 | const row not found |
+|  1 | PRIMARY     | &lt;derived2&gt; | system | NULL          | NULL | NULL    | NULL |    1 |                     |
+|  2 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL | No tables used      |
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+3 rows in set (0.00 sec)
+
+mysql&gt; <userinput>SELECT * FROM t2;</userinput>
++------+
+| c1   |
++------+
+|    5 |
++------+
+1 row in set (0.00 sec)
+</programlisting>
+        </para>
+
+        <para>
+          This also means that an <literal>EXPLAIN SELECT</literal>
+          statement such as the one shown here may take a long time to
+          execute:
+
+<programlisting>
+EXPLAIN SELECT * FROM t1 AS a1, (SELECT BENCHMARK(1000000, MD5(NOW())));
+</programlisting>
+
+          This is because the
+          <function role="sql">BENCHMARK()</function> function is
+          executed once for each row in <literal>t1</literal>.
+        </para>
+
       </section>
 
       <section id="subquery-errors">


Modified: trunk/refman-4.1/optimization.xml
===================================================================
--- trunk/refman-4.1/optimization.xml	2008-04-08 14:36:19 UTC (rev 10429)
+++ trunk/refman-4.1/optimization.xml	2008-04-08 15:48:31 UTC (rev 10430)
Changed blocks: 1, Lines Added: 7, Lines Deleted: 0; 633 bytes

@@ -1811,6 +1811,13 @@
         clause.
       </para>
 
+      <para>
+        It is possible in some cases to execute statements that modify
+        data when <literal>EXPLAIN SELECT</literal> is used with a
+        subquery; for more information, see
+        <xref linkend="unnamed-views"/>.
+      </para>
+
       <formalpara role="mnmas">
 
         <title>MySQL Enterprise</title>


Modified: trunk/refman-4.1/sql-syntax.xml
===================================================================
--- trunk/refman-4.1/sql-syntax.xml	2008-04-08 14:36:19 UTC (rev 10429)
+++ trunk/refman-4.1/sql-syntax.xml	2008-04-08 15:48:31 UTC (rev 10430)
Changed blocks: 1, Lines Added: 123, Lines Deleted: 0; 5792 bytes

@@ -9298,6 +9298,129 @@
           unless the subquery is executed.
         </para>
 
+        <para>
+          It is possible under certain circumstances to modify table
+          data using <literal>EXPLAIN SELECT</literal>. This can occur
+          if the outer query accesses any tables and an inner query
+          invokes a stored function that changes one or more rows of a
+          table. For example, suppose there are two tables
+          <literal>t1</literal> and <literal>t2</literal> in database
+          <literal>d1</literal>, created as shown here:
+
+<programlisting>
+mysql&gt; <userinput>CREATE DATABASE d1;</userinput>
+Query OK, 1 row affected (0.00 sec)
+
+mysql&gt; <userinput>USE d1;</userinput>
+Database changed
+
+mysql&gt; <userinput>CREATE TABLE t1 (c1 INT);</userinput>
+Query OK, 0 rows affected (0.15 sec)
+
+mysql&gt; <userinput>CREATE TABLE t2 (c1 INT);</userinput>
+Query OK, 0 rows affected (0.08 sec)
+</programlisting>
+
+          Now we create a stored function <literal>f1</literal> which
+          modifies <literal>t2</literal>:
+
+<programlisting>
+mysql&gt; <userinput>DELIMITER //</userinput>
+mysql&gt; <userinput>CREATE FUNCTION f1(p1 INT) RETURNS INT</userinput> 
+mysql&gt;   <userinput>BEGIN</userinput> 
+mysql&gt;     <userinput>INSERT INTO t2 VALUES (p1);</userinput> 
+mysql&gt;     <userinput>RETURN p1;</userinput> 
+mysql&gt;   <userinput>END //</userinput>
+Query OK, 0 rows affected (0.01 sec)
+
+mysql&gt; <userinput>DELIMITER ;</userinput>
+</programlisting>
+
+          Referencing the function directly in an EXPLAIN SELECT does
+          not have any affect on t2, as shown here:
+
+<programlisting>
+mysql&gt; <userinput>SELECT * FROM t2;</userinput>
+Empty set (0.00 sec)
+
+mysql&gt; <userinput>EXPLAIN SELECT f1(5);</userinput>
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+1 row in set (0.00 sec)
+
+mysql&gt; <userinput>SELECT * FROM t2;</userinput>
+Empty set (0.00 sec)
+</programlisting>
+
+          This is because the <literal>SELECT</literal> statement did
+          not reference any tables, as can be seen in the
+          <literal>table</literal> and <literal>Extra</literal> columns
+          of the output. This is also true of the following nested
+          <literal>SELECT</literal>:
+
+<programlisting>
+mysql&gt; <userinput>EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2;</userinput>
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+|  1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+1 row in set, 1 warning (0.00 sec)
+
+mysql&gt; <userinput>SHOW WARNINGS;</userinput>
++-------+------+------------------------------------------+
+| Level | Code | Message                                  |
++-------+------+------------------------------------------+
+| Note  | 1249 | Select 2 was reduced during optimization |
++-------+------+------------------------------------------+
+1 row in set (0.00 sec)
+
+mysql&gt; <userinput>SELECT * FROM t2;</userinput>
+Empty set (0.00 sec)
+</programlisting>
+
+          However, if the outer <userinput>SELECT</userinput> references
+          any tables, then the optimizer executes the statement in the
+          subquery as well:
+
+<programlisting>
+mysql&gt; <userinput>EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2;</userinput>
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+|  1 | PRIMARY     | a1         | system | NULL          | NULL | NULL    | NULL |    0 | const row not found |
+|  1 | PRIMARY     | &lt;derived2&gt; | system | NULL          | NULL | NULL    | NULL |    1 |                     |
+|  2 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL | No tables used      |
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+3 rows in set (0.00 sec)
+
+mysql&gt; <userinput>SELECT * FROM t2;</userinput>
++------+
+| c1   |
++------+
+|    5 |
++------+
+1 row in set (0.00 sec)
+</programlisting>
+        </para>
+
+        <para>
+          This also means that an <literal>EXPLAIN SELECT</literal>
+          statement such as the one shown here may take a long time to
+          execute:
+
+<programlisting>
+EXPLAIN SELECT * FROM t1 AS a1, (SELECT BENCHMARK(1000000, MD5(NOW())));
+</programlisting>
+
+          This is because the
+          <function role="sql">BENCHMARK()</function> function is
+          executed once for each row in <literal>t1</literal>.
+        </para>
+
       </section>
 
       <section id="subquery-errors">


Modified: trunk/refman-5.0/optimization.xml
===================================================================
--- trunk/refman-5.0/optimization.xml	2008-04-08 14:36:19 UTC (rev 10429)
+++ trunk/refman-5.0/optimization.xml	2008-04-08 15:48:31 UTC (rev 10430)
Changed blocks: 1, Lines Added: 7, Lines Deleted: 0; 633 bytes

@@ -2090,6 +2090,13 @@
         clause.
       </para>
 
+      <para>
+        It is possible in some cases to execute statements that modify
+        data when <literal>EXPLAIN SELECT</literal> is used with a
+        subquery; for more information, see
+        <xref linkend="unnamed-views"/>.
+      </para>
+
       <formalpara role="mnmas">
 
         <title>MySQL Enterprise</title>


Modified: trunk/refman-5.0/sql-syntax.xml
===================================================================
--- trunk/refman-5.0/sql-syntax.xml	2008-04-08 14:36:19 UTC (rev 10429)
+++ trunk/refman-5.0/sql-syntax.xml	2008-04-08 15:48:31 UTC (rev 10430)
Changed blocks: 1, Lines Added: 123, Lines Deleted: 0; 5794 bytes

@@ -10075,6 +10075,129 @@
           unless the subquery is executed.
         </para>
 
+        <para>
+          It is possible under certain circumstances to modify table
+          data using <literal>EXPLAIN SELECT</literal>. This can occur
+          if the outer query accesses any tables and an inner query
+          invokes a stored function that changes one or more rows of a
+          table. For example, suppose there are two tables
+          <literal>t1</literal> and <literal>t2</literal> in database
+          <literal>d1</literal>, created as shown here:
+
+<programlisting>
+mysql&gt; <userinput>CREATE DATABASE d1;</userinput>
+Query OK, 1 row affected (0.00 sec)
+
+mysql&gt; <userinput>USE d1;</userinput>
+Database changed
+
+mysql&gt; <userinput>CREATE TABLE t1 (c1 INT);</userinput>
+Query OK, 0 rows affected (0.15 sec)
+
+mysql&gt; <userinput>CREATE TABLE t2 (c1 INT);</userinput>
+Query OK, 0 rows affected (0.08 sec)
+</programlisting>
+
+          Now we create a stored function <literal>f1</literal> which
+          modifies <literal>t2</literal>:
+
+<programlisting>
+mysql&gt; <userinput>DELIMITER //</userinput>
+mysql&gt; <userinput>CREATE FUNCTION f1(p1 INT) RETURNS INT</userinput> 
+mysql&gt;   <userinput>BEGIN</userinput> 
+mysql&gt;     <userinput>INSERT INTO t2 VALUES (p1);</userinput> 
+mysql&gt;     <userinput>RETURN p1;</userinput> 
+mysql&gt;   <userinput>END //</userinput>
+Query OK, 0 rows affected (0.01 sec)
+
+mysql&gt; <userinput>DELIMITER ;</userinput>
+</programlisting>
+
+          Referencing the function directly in an EXPLAIN SELECT does
+          not have any affect on t2, as shown here:
+
+<programlisting>
+mysql&gt; <userinput>SELECT * FROM t2;</userinput>
+Empty set (0.00 sec)
+
+mysql&gt; <userinput>EXPLAIN SELECT f1(5);</userinput>
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+1 row in set (0.00 sec)
+
+mysql&gt; <userinput>SELECT * FROM t2;</userinput>
+Empty set (0.00 sec)
+</programlisting>
+
+          This is because the <literal>SELECT</literal> statement did
+          not reference any tables, as can be seen in the
+          <literal>table</literal> and <literal>Extra</literal> columns
+          of the output. This is also true of the following nested
+          <literal>SELECT</literal>:
+
+<programlisting>
+mysql&gt; <userinput>EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2;</userinput>
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+|  1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+1 row in set, 1 warning (0.00 sec)
+
+mysql&gt; <userinput>SHOW WARNINGS;</userinput>
++-------+------+------------------------------------------+
+| Level | Code | Message                                  |
++-------+------+------------------------------------------+
+| Note  | 1249 | Select 2 was reduced during optimization |
++-------+------+------------------------------------------+
+1 row in set (0.00 sec)
+
+mysql&gt; <userinput>SELECT * FROM t2;</userinput>
+Empty set (0.00 sec)
+</programlisting>
+
+          However, if the outer <userinput>SELECT</userinput> references
+          any tables, then the optimizer executes the statement in the
+          subquery as well:
+
+<programlisting>
+mysql&gt; <userinput>EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2;</userinput>
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+|  1 | PRIMARY     | a1         | system | NULL          | NULL | NULL    | NULL |    0 | const row not found |
+|  1 | PRIMARY     | &lt;derived2&gt; | system | NULL          | NULL | NULL    | NULL |    1 |                     |
+|  2 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL | No tables used      |
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+3 rows in set (0.00 sec)
+
+mysql&gt; <userinput>SELECT * FROM t2;</userinput>
++------+
+| c1   |
++------+
+|    5 |
++------+
+1 row in set (0.00 sec)
+</programlisting>
+        </para>
+
+        <para>
+          This also means that an <literal>EXPLAIN SELECT</literal>
+          statement such as the one shown here may take a long time to
+          execute:
+
+<programlisting>
+EXPLAIN SELECT * FROM t1 AS a1, (SELECT BENCHMARK(1000000, MD5(NOW())));
+</programlisting>
+
+          This is because the
+          <function role="sql">BENCHMARK()</function> function is
+          executed once for each row in <literal>t1</literal>.
+        </para>
+
       </section>
 
       <section id="subquery-errors">


Modified: trunk/refman-5.1/optimization.xml
===================================================================
--- trunk/refman-5.1/optimization.xml	2008-04-08 14:36:19 UTC (rev 10429)
+++ trunk/refman-5.1/optimization.xml	2008-04-08 15:48:31 UTC (rev 10430)
Changed blocks: 1, Lines Added: 7, Lines Deleted: 0; 633 bytes

@@ -2250,6 +2250,13 @@
         clause.
       </para>
 
+      <para>
+        It is possible in some cases to execute statements that modify
+        data when <literal>EXPLAIN SELECT</literal> is used with a
+        subquery; for more information, see
+        <xref linkend="unnamed-views"/>.
+      </para>
+
       <formalpara role="mnmas">
 
         <title>MySQL Enterprise</title>


Modified: trunk/refman-5.1/sql-syntax.xml
===================================================================
--- trunk/refman-5.1/sql-syntax.xml	2008-04-08 14:36:19 UTC (rev 10429)
+++ trunk/refman-5.1/sql-syntax.xml	2008-04-08 15:48:31 UTC (rev 10430)
Changed blocks: 1, Lines Added: 123, Lines Deleted: 0; 5794 bytes

@@ -12586,6 +12586,129 @@
           unless the subquery is executed.
         </para>
 
+        <para>
+          It is possible under certain circumstances to modify table
+          data using <literal>EXPLAIN SELECT</literal>. This can occur
+          if the outer query accesses any tables and an inner query
+          invokes a stored function that changes one or more rows of a
+          table. For example, suppose there are two tables
+          <literal>t1</literal> and <literal>t2</literal> in database
+          <literal>d1</literal>, created as shown here:
+
+<programlisting>
+mysql&gt; <userinput>CREATE DATABASE d1;</userinput>
+Query OK, 1 row affected (0.00 sec)
+
+mysql&gt; <userinput>USE d1;</userinput>
+Database changed
+
+mysql&gt; <userinput>CREATE TABLE t1 (c1 INT);</userinput>
+Query OK, 0 rows affected (0.15 sec)
+
+mysql&gt; <userinput>CREATE TABLE t2 (c1 INT);</userinput>
+Query OK, 0 rows affected (0.08 sec)
+</programlisting>
+
+          Now we create a stored function <literal>f1</literal> which
+          modifies <literal>t2</literal>:
+
+<programlisting>
+mysql&gt; <userinput>DELIMITER //</userinput>
+mysql&gt; <userinput>CREATE FUNCTION f1(p1 INT) RETURNS INT</userinput> 
+mysql&gt;   <userinput>BEGIN</userinput> 
+mysql&gt;     <userinput>INSERT INTO t2 VALUES (p1);</userinput> 
+mysql&gt;     <userinput>RETURN p1;</userinput> 
+mysql&gt;   <userinput>END //</userinput>
+Query OK, 0 rows affected (0.01 sec)
+
+mysql&gt; <userinput>DELIMITER ;</userinput>
+</programlisting>
+
+          Referencing the function directly in an EXPLAIN SELECT does
+          not have any affect on t2, as shown here:
+
+<programlisting>
+mysql&gt; <userinput>SELECT * FROM t2;</userinput>
+Empty set (0.00 sec)
+
+mysql&gt; <userinput>EXPLAIN SELECT f1(5);</userinput>
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+1 row in set (0.00 sec)
+
+mysql&gt; <userinput>SELECT * FROM t2;</userinput>
+Empty set (0.00 sec)
+</programlisting>
+
+          This is because the <literal>SELECT</literal> statement did
+          not reference any tables, as can be seen in the
+          <literal>table</literal> and <literal>Extra</literal> columns
+          of the output. This is also true of the following nested
+          <literal>SELECT</literal>:
+
+<programlisting>
+mysql&gt; <userinput>EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2;</userinput>
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+|  1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+1 row in set, 1 warning (0.00 sec)
+
+mysql&gt; <userinput>SHOW WARNINGS;</userinput>
++-------+------+------------------------------------------+
+| Level | Code | Message                                  |
++-------+------+------------------------------------------+
+| Note  | 1249 | Select 2 was reduced during optimization |
++-------+------+------------------------------------------+
+1 row in set (0.00 sec)
+
+mysql&gt; <userinput>SELECT * FROM t2;</userinput>
+Empty set (0.00 sec)
+</programlisting>
+
+          However, if the outer <userinput>SELECT</userinput> references
+          any tables, then the optimizer executes the statement in the
+          subquery as well:
+
+<programlisting>
+mysql&gt; <userinput>EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2;</userinput>
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+|  1 | PRIMARY     | a1         | system | NULL          | NULL | NULL    | NULL |    0 | const row not found |
+|  1 | PRIMARY     | &lt;derived2&gt; | system | NULL          | NULL | NULL    | NULL |    1 |                     |
+|  2 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL | No tables used      |
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+3 rows in set (0.00 sec)
+
+mysql&gt; <userinput>SELECT * FROM t2;</userinput>
++------+
+| c1   |
++------+
+|    5 |
++------+
+1 row in set (0.00 sec)
+</programlisting>
+        </para>
+
+        <para>
+          This also means that an <literal>EXPLAIN SELECT</literal>
+          statement such as the one shown here may take a long time to
+          execute:
+
+<programlisting>
+EXPLAIN SELECT * FROM t1 AS a1, (SELECT BENCHMARK(1000000, MD5(NOW())));
+</programlisting>
+
+          This is because the
+          <function role="sql">BENCHMARK()</function> function is
+          executed once for each row in <literal>t1</literal>.
+        </para>
+
       </section>
 
       <section id="subquery-errors">


Modified: trunk/refman-6.0/optimization.xml
===================================================================


Changed blocks: 0, Lines Added: 0, Lines Deleted: 0; 110 bytes


Thread
svn commit - mysqldoc@docsrva: r10430 - in trunk: it/refman-5.1 pt/refman-5.1 refman-4.1 refman-5.0 refman-5.1 refman-6.0jon8 Apr