Author: paul
Date: 2006-01-27 22:24:13 +0100 (Fri, 27 Jan 2006)
New Revision: 1069
Log:
r6765@frost: paul | 2006-01-27 15:24:08 -0600
General revisions.
Modified:
trunk/
trunk/refman-4.1/sql-syntax.xml
trunk/refman-5.0/sql-syntax.xml
trunk/refman-5.1/sql-syntax.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6763
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2564
+ b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6765
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2564
Modified: trunk/refman-4.1/sql-syntax.xml
===================================================================
--- trunk/refman-4.1/sql-syntax.xml 2006-01-27 20:30:35 UTC (rev 1068)
+++ trunk/refman-4.1/sql-syntax.xml 2006-01-27 21:24:13 UTC (rev 1069)
@@ -7084,10 +7084,11 @@
</para>
<programlisting>
-mysql> <userinput>SELECT t1.name, t2.salary FROM employee AS t1, info AS t2</userinput>
- -> <userinput>WHERE t1.name = t2.name;</userinput>
-mysql> <userinput>SELECT t1.name, t2.salary FROM employee t1, info t2</userinput>
- -> <userinput>WHERE t1.name = t2.name;</userinput>
+SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
+ WHERE t1.name = t2.name;
+
+SELECT t1.name, t2.salary FROM employee t1, info t2
+ WHERE t1.name = t2.name;
</programlisting>
</listitem>
@@ -7112,9 +7113,9 @@
<remark role="help-example"/>
<programlisting>
-mysql> <userinput>SELECT table1.* FROM table1</userinput>
- -> <userinput>LEFT JOIN table2 ON table1.id=table2.id</userinput>
- -> <userinput>WHERE table2.id IS NULL;</userinput>
+SELECT table1.* FROM table1
+ LEFT JOIN table2 ON table1.id=table2.id
+ WHERE table2.id IS NULL;
</programlisting>
<para>
@@ -7214,13 +7215,14 @@
<para>
As of MySQL 3.23.12, you can give hints about which index
MySQL should use when retrieving information from a table. By
- specifying <literal>USE INDEX (key_list)</literal>, you can
- tell MySQL to use only one of the possible indexes to find
- rows in the table. The alternative syntax <literal>IGNORE
- INDEX (key_list)</literal> can be used to tell MySQL to not
- use some particular index. These hints are useful if
- <literal>EXPLAIN</literal> shows that MySQL is using the wrong
- index from the list of possible indexes.
+ specifying <literal>USE INDEX
+ (<replaceable>key_list</replaceable>)</literal>, you can tell
+ MySQL to use only one of the possible indexes to find rows in
+ the table. The alternative syntax <literal>IGNORE INDEX
+ (<replaceable>key_list</replaceable>)</literal> can be used to
+ tell MySQL to not use some particular index. These hints are
+ useful if <literal>EXPLAIN</literal> shows that MySQL is using
+ the wrong index from the list of possible indexes.
</para>
<indexterm type="function">
@@ -7237,6 +7239,15 @@
indexes to find rows in the table.
</para>
+ <para>
+ <literal>USE INDEX</literal>, <literal>IGNORE INDEX</literal>,
+ and <literal>FORCE INDEX</literal> affect only which indexes
+ are used when MySQL decides how to find rows in the table and
+ how to do the join. They do not affect whether an index is
+ used when resolving an <literal>ORDER BY</literal> or
+ <literal>GROUP BY</literal>.
+ </para>
+
<indexterm type="function">
<primary>USE KEY</primary>
</indexterm>
@@ -7257,35 +7268,26 @@
</para>
<para>
- <emphasis role="bold">Note</emphasis>: <literal>USE
- INDEX</literal>, <literal>IGNORE INDEX</literal>, and
- <literal>FORCE INDEX</literal> only affect which indexes are
- used when MySQL decides how to find rows in the table and how
- to do the join. They do not affect whether an index is used
- when resolving an <literal>ORDER BY</literal> or
- <literal>GROUP BY</literal>.
- </para>
-
- <para>
Some join examples:
</para>
<programlisting>
-mysql> <userinput>SELECT * FROM table1,table2 WHERE table1.id=table2.id;</userinput>
-mysql> <userinput>SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;</userinput>
-mysql> <userinput>SELECT * FROM table1 LEFT JOIN table2 USING (id);</userinput>
-mysql> <userinput>SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id</userinput>
- -> <userinput>LEFT JOIN table3 ON table2.id=table3.id;</userinput>
-mysql> <userinput>SELECT * FROM table1 USE INDEX (key1,key2)</userinput>
- -> <userinput>WHERE key1=1 AND key2=2 AND key3=3;</userinput>
-mysql> <userinput>SELECT * FROM table1 IGNORE INDEX (key3)</userinput>
- -> <userinput>WHERE key1=1 AND key2=2 AND key3=3;</userinput>
+SELECT * FROM table1,table2 WHERE table1.id=table2.id;
+
+SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
+
+SELECT * FROM table1 LEFT JOIN table2 USING (id);
+
+SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
+ LEFT JOIN table3 ON table2.id=table3.id;
+
+SELECT * FROM table1 USE INDEX (key1,key2)
+ WHERE key1=1 AND key2=2 AND key3=3;
+
+SELECT * FROM table1 IGNORE INDEX (key3)
+ WHERE key1=1 AND key2=2 AND key3=3;
</programlisting>
- <para>
- See <xref linkend="left-join-optimization"/>.
- </para>
-
</section>
<section id="union">
Modified: trunk/refman-5.0/sql-syntax.xml
===================================================================
--- trunk/refman-5.0/sql-syntax.xml 2006-01-27 20:30:35 UTC (rev 1068)
+++ trunk/refman-5.0/sql-syntax.xml 2006-01-27 21:24:13 UTC (rev 1069)
@@ -7136,10 +7136,11 @@
</para>
<programlisting>
-mysql> <userinput>SELECT t1.name, t2.salary FROM employee AS t1, info AS t2</userinput>
- -> <userinput>WHERE t1.name = t2.name;</userinput>
-mysql> <userinput>SELECT t1.name, t2.salary FROM employee t1, info t2</userinput>
- -> <userinput>WHERE t1.name = t2.name;</userinput>
+SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
+ WHERE t1.name = t2.name;
+
+SELECT t1.name, t2.salary FROM employee t1, info t2
+ WHERE t1.name = t2.name;
</programlisting>
</listitem>
@@ -7164,9 +7165,9 @@
<remark role="help-example"/>
<programlisting>
-mysql> <userinput>SELECT table1.* FROM table1</userinput>
- -> <userinput>LEFT JOIN table2 ON table1.id=table2.id</userinput>
- -> <userinput>WHERE table2.id IS NULL;</userinput>
+SELECT table1.* FROM table1
+ LEFT JOIN table2 ON table1.id=table2.id
+ WHERE table2.id IS NULL;
</programlisting>
<para>
@@ -7268,13 +7269,14 @@
<para>
You can provide hints as to which index MySQL should use when
retrieving information from a table. By specifying
- <literal>USE INDEX (key_list)</literal>, you can tell MySQL to
- use only one of the possible indexes to find rows in the
- table. The alternative syntax <literal>IGNORE INDEX
- (key_list)</literal> can be used to tell MySQL to not use some
- particular index. These hints are useful if
- <literal>EXPLAIN</literal> shows that MySQL is using the wrong
- index from the list of possible indexes.
+ <literal>USE INDEX
+ (<replaceable>key_list</replaceable>)</literal>, you can tell
+ MySQL to use only one of the possible indexes to find rows in
+ the table. The alternative syntax <literal>IGNORE INDEX
+ (<replaceable>key_list</replaceable>)</literal> can be used to
+ tell MySQL to not use some particular index. These hints are
+ useful if <literal>EXPLAIN</literal> shows that MySQL is using
+ the wrong index from the list of possible indexes.
</para>
<indexterm type="function">
@@ -7291,6 +7293,15 @@
indexes to find rows in the table.
</para>
+ <para>
+ <literal>USE INDEX</literal>, <literal>IGNORE INDEX</literal>,
+ and <literal>FORCE INDEX</literal> affect only which indexes
+ are used when MySQL decides how to find rows in the table and
+ how to do the join. They do not affect whether an index is
+ used when resolving an <literal>ORDER BY</literal> or
+ <literal>GROUP BY</literal>.
+ </para>
+
<indexterm type="function">
<primary>USE KEY</primary>
</indexterm>
@@ -7311,60 +7322,54 @@
</para>
<para>
- <emphasis role="bold">Note</emphasis>: <literal>USE
- INDEX</literal>, <literal>IGNORE INDEX</literal>, and
- <literal>FORCE INDEX</literal> only affect which indexes are
- used when MySQL decides how to find rows in the table and how
- to do the join. They do not affect whether an index is used
- when resolving an <literal>ORDER BY</literal> or
- <literal>GROUP BY</literal>.
- </para>
-
- <para>
Some join examples:
</para>
<programlisting>
-mysql> <userinput>SELECT * FROM table1,table2 WHERE table1.id=table2.id;</userinput>
-mysql> <userinput>SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;</userinput>
-mysql> <userinput>SELECT * FROM table1 LEFT JOIN table2 USING (id);</userinput>
-mysql> <userinput>SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id</userinput>
- -> <userinput>LEFT JOIN table3 ON table2.id=table3.id;</userinput>
-mysql> <userinput>SELECT * FROM table1 USE INDEX (key1,key2)</userinput>
- -> <userinput>WHERE key1=1 AND key2=2 AND key3=3;</userinput>
-mysql> <userinput>SELECT * FROM table1 IGNORE INDEX (key3)</userinput>
- -> <userinput>WHERE key1=1 AND key2=2 AND key3=3;</userinput>
+SELECT * FROM table1,table2 WHERE table1.id=table2.id;
+
+SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
+
+SELECT * FROM table1 LEFT JOIN table2 USING (id);
+
+SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
+ LEFT JOIN table3 ON table2.id=table3.id;
+
+SELECT * FROM table1 USE INDEX (key1,key2)
+ WHERE key1=1 AND key2=2 AND key3=3;
+
+SELECT * FROM table1 IGNORE INDEX (key3)
+ WHERE key1=1 AND key2=2 AND key3=3;
</programlisting>
<para>
- See <xref linkend="left-join-optimization"/>.
+ <emphasis role="bold">Note</emphasis>: Beginning with MySQL
+ 5.0.12, natural joins and joins with <literal>USING</literal>,
+ including outer join variants, are processed according to the
+ SQL:2003 standard. These changes make MySQL more compliant
+ with standard SQL. However, they can result in different
+ output columns for some joins. Also, some queries that
+ appeared to work correctly in older versions must be rewritten
+ to comply with the standard. The following list provides more
+ detail about several effects of the 5.0.12 change in join
+ processing. The term <quote>previously</quote> means
+ <quote>prior to MySQL 5.0.12.</quote>
</para>
- <para>
- Beginning with MySQL 5.0.12, natural joins and joins with
- <literal>USING</literal>, including outer join variants, are
- processed according to the SQL:2003 standard. These changes
- make MySQL more compliant with standard SQL. However, they can
- result in different output columns for some joins. Also, some
- queries that appeared to work correctly in older versions must
- be rewritten to comply with the standard. The following list
- provides more detail about several effects of the 5.0.12
- change in join processing.
- </para>
-
<itemizedlist>
<listitem>
<para>
The columns of a <literal>NATURAL</literal> join or a
- <literal>USING</literal> join may be different from 5.0.12
- on. Specifically, redundant output columns no longer
- appear, and the order of columns for <literal>SELECT
- *</literal> expansion may be different than before.
+ <literal>USING</literal> join may be different from
+ previously. Specifically, redundant output columns no
+ longer appear, and the order of columns for
+ <literal>SELECT *</literal> expansion may be different
+ from before.
</para>
<para>
- Example:
+ Consider this set of statements:
</para>
<programlisting>
@@ -7377,7 +7382,7 @@
</programlisting>
<para>
- Before 5.0.12, the statements produce this output:
+ Previously, the statements produced this output:
</para>
<programlisting>
@@ -7396,17 +7401,18 @@
<para>
In the first <literal>SELECT</literal> statement, column
<literal>i</literal> appears in both tables and thus
- becomes a join column, so it should appear only once in
- the output according to standard SQL. Similarly, in the
- second SELECT statement, column <literal>j</literal> is
- named in the <literal>USING</literal> clause and should
- appear only once in the output. But in both cases, the
+ becomes a join column, so, according to standard SQL, it
+ should appear only once in the output, not twice.
+ Similarly, in the second SELECT statement, column
+ <literal>j</literal> is named in the
+ <literal>USING</literal> clause and should appear only
+ once in the output, not twice. But in both cases, the
redundant column is not eliminated. Also, the order of the
columns is not correct according to standard SQL.
</para>
<para>
- As of 5.0.12, the statements produce this output:
+ Now the statements produce this output:
</para>
<programlisting>
@@ -7423,7 +7429,7 @@
</programlisting>
<para>
- The redundant column is eliminated, and the column order
+ The redundant column is eliminated. Also, the column order
is correct according to standard SQL:
</para>
@@ -7461,7 +7467,7 @@
<literal>t2(c,b)</literal>, and <literal>t3(a,c)</literal>
that each have one row: <literal>t1(1,2)</literal>,
<literal>t2(10,2)</literal>, and
- <literal>t3(7,10)</literal>. Also suppose that you have
+ <literal>t3(7,10)</literal>. Suppose also that you have
this <literal>NATURAL JOIN</literal> on the three tables:
</para>
@@ -7470,16 +7476,16 @@
</programlisting>
<para>
- Prior to MySQL 5.0.12, the left operand of the second join
- is considered to be <literal>t2</literal>, whereas it
- should be the nested join <literal>(t1 NATURAL JOIN
+ Previously, the left operand of the second join was
+ considered to be <literal>t2</literal>, whereas it should
+ be the nested join <literal>(t1 NATURAL JOIN
t2)</literal>. As a result, the columns of
<literal>t3</literal> are checked for common columns only
in <literal>t2</literal>, and, if <literal>t3</literal>
has common columns with <literal>t1</literal>, these
- columns are not used as equi-join columns. Thus, in older
- versions of MySQL, the preceding query is transformed to
- the following equi-join:
+ columns are not used as equi-join columns. Thus,
+ previously, the preceding query was transformed to the
+ following equi-join:
</para>
<programlisting>
@@ -7500,19 +7506,18 @@
</programlisting>
<para>
- If you require the same query result in 5.0.12 or later as
- before 5.0.12, rewrite the natural join as the first
- equi-join.
+ If you require the same query result in current versions
+ of MySQL as in older versions, rewrite the natural join as
+ the first equi-join.
</para>
</listitem>
<listitem>
<para>
- Before MySQL 5.0.12, the comma operator
- (<literal>,</literal>) and <literal>JOIN</literal> both
- had the same precedence, so the join expression
- <literal>t1, t2 JOIN t3</literal> was interpreted as
- <literal>((t1, t2) JOIN t3)</literal>. Now
+ Previously, the comma operator (<literal>,</literal>) and
+ <literal>JOIN</literal> both had the same precedence, so
+ the join expression <literal>t1, t2 JOIN t3</literal> was
+ interpreted as <literal>((t1, t2) JOIN t3)</literal>. Now
<literal>JOIN</literal> has higher precedence, so the
expression is interpreted as <literal>(t1, (t2 JOIN
t3))</literal>. This change affects statements that use an
@@ -7537,9 +7542,9 @@
</programlisting>
<para>
- Prior to 5.0.12, the <literal>SELECT</literal> is legal
- due to the implicit grouping of <literal>t1,t2</literal>
- as <literal>(t1,t2)</literal>. From 5.0.12 on, the
+ Previously, the <literal>SELECT</literal> was legal due to
+ the implicit grouping of <literal>t1,t2</literal> as
+ <literal>(t1,t2)</literal>. Now the
<literal>JOIN</literal> takes precedence, so the operands
for the <literal>ON</literal> clause are
<literal>t2</literal> and <literal>t3</literal>. Because
@@ -7557,17 +7562,27 @@
</programlisting>
<para>
+ Alternatively, avoid the use of the comma operator and use
+ <literal>JOIN</literal> instead:
+ </para>
+
+<programlisting>
+SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);
+</programlisting>
+
+ <para>
This change also applies to <literal>INNER JOIN</literal>,
<literal>CROSS JOIN</literal>, <literal>LEFT
- JOIN</literal>, and <literal>RIGHT JOIN</literal>.
+ JOIN</literal>, and <literal>RIGHT JOIN</literal>, all of
+ which now have higher precedence than the comma operator.
</para>
</listitem>
<listitem>
<para>
- Before MySQL 5.0.12, the <literal>ON</literal> clause
- could refer to columns in tables named to its right. Now
- an <literal>ON</literal> clause can refer only to its
+ Previously, the <literal>ON</literal> clause could refer
+ to columns in tables named to its right. Now an
+ <literal>ON</literal> clause can refer only to its
operands.
</para>
@@ -7583,10 +7598,10 @@
</programlisting>
<para>
- Before 5.0.12, the <literal>SELECT</literal> statement is
- legal. From 5.0.12 on, the statement fails with an
- <literal>Unknown column 'i3' in 'on clause'</literal>
- error because <literal>i3</literal> is a table in
+ Previously, the <literal>SELECT</literal> statement was
+ legal. Now the statement fails with an <literal>Unknown
+ column 'i3' in 'on clause'</literal> error because
+ <literal>i3</literal> is a column in
<literal>t3</literal>, which is not an operand of the
<literal>ON</literal> clause. The statement should be
rewritten as follows:
@@ -7599,10 +7614,10 @@
<listitem>
<para>
- Before MySQL 5.0.12, a <literal>USING</literal> clause can
- be rewritten as an <literal>ON</literal> clause that
- compares corresponding columns. For example, the following
- two clauses are semantically identical:
+ Previously, a <literal>USING</literal> clause could be
+ rewritten as an <literal>ON</literal> clause that compares
+ corresponding columns. For example, the following two
+ clauses are semantically identical:
</para>
<programlisting>
@@ -7611,8 +7626,7 @@
</programlisting>
<para>
- From 5.0.12 on, the two clauses no longer are quite the
- same:
+ Now the two clauses no longer are quite the same:
</para>
<itemizedlist>
Modified: trunk/refman-5.1/sql-syntax.xml
===================================================================
--- trunk/refman-5.1/sql-syntax.xml 2006-01-27 20:30:35 UTC (rev 1068)
+++ trunk/refman-5.1/sql-syntax.xml 2006-01-27 21:24:13 UTC (rev 1069)
@@ -7716,10 +7716,11 @@
</para>
<programlisting>
-mysql> <userinput>SELECT t1.name, t2.salary FROM employee AS t1, info AS t2</userinput>
- -> <userinput>WHERE t1.name = t2.name;</userinput>
-mysql> <userinput>SELECT t1.name, t2.salary FROM employee t1, info t2</userinput>
- -> <userinput>WHERE t1.name = t2.name;</userinput>
+SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
+ WHERE t1.name = t2.name;
+
+SELECT t1.name, t2.salary FROM employee t1, info t2
+ WHERE t1.name = t2.name;
</programlisting>
</listitem>
@@ -7744,9 +7745,9 @@
<remark role="help-example"/>
<programlisting>
-mysql> <userinput>SELECT table1.* FROM table1</userinput>
- -> <userinput>LEFT JOIN table2 ON table1.id=table2.id</userinput>
- -> <userinput>WHERE table2.id IS NULL;</userinput>
+SELECT table1.* FROM table1
+ LEFT JOIN table2 ON table1.id=table2.id
+ WHERE table2.id IS NULL;
</programlisting>
<para>
@@ -7848,13 +7849,14 @@
<para>
You can provide hints as to which index MySQL should use when
retrieving information from a table. By specifying
- <literal>USE INDEX (key_list)</literal>, you can tell MySQL to
- use only one of the possible indexes to find rows in the
- table. The alternative syntax <literal>IGNORE INDEX
- (key_list)</literal> can be used to tell MySQL to not use some
- particular index. These hints are useful if
- <literal>EXPLAIN</literal> shows that MySQL is using the wrong
- index from the list of possible indexes.
+ <literal>USE INDEX
+ (<replaceable>key_list</replaceable>)</literal>, you can tell
+ MySQL to use only one of the possible indexes to find rows in
+ the table. The alternative syntax <literal>IGNORE INDEX
+ (<replaceable>key_list</replaceable>)</literal> can be used to
+ tell MySQL to not use some particular index. These hints are
+ useful if <literal>EXPLAIN</literal> shows that MySQL is using
+ the wrong index from the list of possible indexes.
</para>
<indexterm type="function">
@@ -7871,6 +7873,15 @@
indexes to find rows in the table.
</para>
+ <para>
+ <literal>USE INDEX</literal>, <literal>IGNORE INDEX</literal>,
+ and <literal>FORCE INDEX</literal> affect only which indexes
+ are used when MySQL decides how to find rows in the table and
+ how to do the join. They do not affect whether an index is
+ used when resolving an <literal>ORDER BY</literal> or
+ <literal>GROUP BY</literal>.
+ </para>
+
<indexterm type="function">
<primary>USE KEY</primary>
</indexterm>
@@ -7891,36 +7902,27 @@
</para>
<para>
- <emphasis role="bold">Note</emphasis>: <literal>USE
- INDEX</literal>, <literal>IGNORE INDEX</literal>, and
- <literal>FORCE INDEX</literal> only affect which indexes are
- used when MySQL decides how to find rows in the table and how
- to do the join. They do not affect whether an index is used
- when resolving an <literal>ORDER BY</literal> or
- <literal>GROUP BY</literal>.
- </para>
-
- <para>
Some join examples:
</para>
<programlisting>
-mysql> <userinput>SELECT * FROM table1,table2 WHERE table1.id=table2.id;</userinput>
-mysql> <userinput>SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;</userinput>
-mysql> <userinput>SELECT * FROM table1 LEFT JOIN table2 USING (id);</userinput>
-mysql> <userinput>SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id</userinput>
- -> <userinput>LEFT JOIN table3 ON table2.id=table3.id;</userinput>
-mysql> <userinput>SELECT * FROM table1 USE INDEX (key1,key2)</userinput>
- -> <userinput>WHERE key1=1 AND key2=2 AND key3=3;</userinput>
-mysql> <userinput>SELECT * FROM table1 IGNORE INDEX (key3)</userinput>
- -> <userinput>WHERE key1=1 AND key2=2 AND key3=3;</userinput>
+SELECT * FROM table1,table2 WHERE table1.id=table2.id;
+
+SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
+
+SELECT * FROM table1 LEFT JOIN table2 USING (id);
+
+SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
+ LEFT JOIN table3 ON table2.id=table3.id;
+
+SELECT * FROM table1 USE INDEX (key1,key2)
+ WHERE key1=1 AND key2=2 AND key3=3;
+
+SELECT * FROM table1 IGNORE INDEX (key3)
+ WHERE key1=1 AND key2=2 AND key3=3;
</programlisting>
<para>
- See <xref linkend="left-join-optimization"/>.
- </para>
-
- <para>
<emphasis role="bold">Note</emphasis>: Natural joins and joins
with <literal>USING</literal>, including outer join variants,
are processed according to the SQL:2003 standard. These
@@ -7930,7 +7932,9 @@
versions (prior to 5.0.12) must be rewritten to comply with
the standard. The following list provides more detail about
several effects of current join processing versus join
- processing in older versions.
+ processing in older versions. The term
+ <quote>previously</quote> means <quote>prior to MySQL
+ 5.0.12.</quote>
</para>
<itemizedlist>
@@ -7938,14 +7942,15 @@
<listitem>
<para>
The columns of a <literal>NATURAL</literal> join or a
- <literal>USING</literal> join may be different from older
- versions. Specifically, redundant output columns no longer
- appear, and the order of columns for <literal>SELECT
- *</literal> expansion may be different than before.
+ <literal>USING</literal> join may be different from
+ previously. Specifically, redundant output columns no
+ longer appear, and the order of columns for
+ <literal>SELECT *</literal> expansion may be different
+ from before.
</para>
<para>
- Example:
+ Consider this set of statements:
</para>
<programlisting>
@@ -7958,7 +7963,7 @@
</programlisting>
<para>
- For older versions, the statements produce this output:
+ Previously, the statements produced this output:
</para>
<programlisting>
@@ -7977,11 +7982,12 @@
<para>
In the first <literal>SELECT</literal> statement, column
<literal>i</literal> appears in both tables and thus
- becomes a join column, so it should appear only once in
- the output according to standard SQL. Similarly, in the
- second SELECT statement, column <literal>j</literal> is
- named in the <literal>USING</literal> clause and should
- appear only once in the output. But in both cases, the
+ becomes a join column, so, according to standard SQL, it
+ should appear only once in the output, not twice.
+ Similarly, in the second SELECT statement, column
+ <literal>j</literal> is named in the
+ <literal>USING</literal> clause and should appear only
+ once in the output, not twice. But in both cases, the
redundant column is not eliminated. Also, the order of the
columns is not correct according to standard SQL.
</para>
@@ -8004,7 +8010,7 @@
</programlisting>
<para>
- The redundant column is eliminated, and the column order
+ The redundant column is eliminated. Also, the column order
is correct according to standard SQL:
</para>
@@ -8042,7 +8048,7 @@
<literal>t2(c,b)</literal>, and <literal>t3(a,c)</literal>
that each have one row: <literal>t1(1,2)</literal>,
<literal>t2(10,2)</literal>, and
- <literal>t3(7,10)</literal>. Also suppose that you have
+ <literal>t3(7,10)</literal>. Suppose also that you have
this <literal>NATURAL JOIN</literal> on the three tables:
</para>
@@ -8051,16 +8057,16 @@
</programlisting>
<para>
- In older versions, the left operand of the second join is
+ Previously, the left operand of the second join was
considered to be <literal>t2</literal>, whereas it should
be the nested join <literal>(t1 NATURAL JOIN
t2)</literal>. As a result, the columns of
<literal>t3</literal> are checked for common columns only
in <literal>t2</literal>, and, if <literal>t3</literal>
has common columns with <literal>t1</literal>, these
- columns are not used as equi-join columns. Thus, in older
- versions of MySQL, the preceding query is transformed to
- the following equi-join:
+ columns are not used as equi-join columns. Thus,
+ previously, the preceding query was transformed to the
+ following equi-join:
</para>
<programlisting>
@@ -8089,11 +8095,10 @@
<listitem>
<para>
- In older versions, the comma operator
- (<literal>,</literal>) and <literal>JOIN</literal> both
- had the same precedence, so the join expression
- <literal>t1, t2 JOIN t3</literal> was interpreted as
- <literal>((t1, t2) JOIN t3)</literal>. Now
+ Previously, the comma operator (<literal>,</literal>) and
+ <literal>JOIN</literal> both had the same precedence, so
+ the join expression <literal>t1, t2 JOIN t3</literal> was
+ interpreted as <literal>((t1, t2) JOIN t3)</literal>. Now
<literal>JOIN</literal> has higher precedence, so the
expression is interpreted as <literal>(t1, (t2 JOIN
t3))</literal>. This change affects statements that use an
@@ -8118,9 +8123,9 @@
</programlisting>
<para>
- In older versions, the <literal>SELECT</literal> is legal
- due to the implicit grouping of <literal>t1,t2</literal>
- as <literal>(t1,t2)</literal>. Now the
+ Previously, the <literal>SELECT</literal> was legal due to
+ the implicit grouping of <literal>t1,t2</literal> as
+ <literal>(t1,t2)</literal>. Now the
<literal>JOIN</literal> takes precedence, so the operands
for the <literal>ON</literal> clause are
<literal>t2</literal> and <literal>t3</literal>. Because
@@ -8138,16 +8143,26 @@
</programlisting>
<para>
+ Alternatively, avoid the use of the comma operator and use
+ <literal>JOIN</literal> instead:
+ </para>
+
+<programlisting>
+SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);
+</programlisting>
+
+ <para>
This change also applies to <literal>INNER JOIN</literal>,
<literal>CROSS JOIN</literal>, <literal>LEFT
- JOIN</literal>, and <literal>RIGHT JOIN</literal>.
+ JOIN</literal>, and <literal>RIGHT JOIN</literal>, all of
+ which now have higher precedence than the comma operator.
</para>
</listitem>
<listitem>
<para>
- In older versions, the <literal>ON</literal> clause could
- refer to columns in tables named to its right. Now an
+ Previously, the <literal>ON</literal> clause could refer
+ to columns in tables named to its right. Now an
<literal>ON</literal> clause can refer only to its
operands.
</para>
@@ -8164,12 +8179,13 @@
</programlisting>
<para>
- In older versions, the <literal>SELECT</literal> statement
- is legal. Now the statement fails with an <literal>Unknown
+ Previously, the <literal>SELECT</literal> statement was
+ legal. Now the statement fails with an <literal>Unknown
column 'i3' in 'on clause'</literal> error because
- <literal>i3</literal> is a table in <literal>t3</literal>,
- which is not an operand of the <literal>ON</literal>
- clause. The statement should be rewritten as follows:
+ <literal>i3</literal> is a column in
+ <literal>t3</literal>, which is not an operand of the
+ <literal>ON</literal> clause. The statement should be
+ rewritten as follows:
</para>
<programlisting>
@@ -8179,10 +8195,10 @@
<listitem>
<para>
- In older versions, a <literal>USING</literal> clause can
- be rewritten as an <literal>ON</literal> clause that
- compares corresponding columns. For example, the following
- two clauses are semantically identical:
+ Previously, a <literal>USING</literal> clause could be
+ rewritten as an <literal>ON</literal> clause that compares
+ corresponding columns. For example, the following two
+ clauses are semantically identical:
</para>
<programlisting>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r1069 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | paul | 27 Jan |
