| List: | Commits | « Previous MessageNext Message » | |
| From: | paul | Date: | February 9 2006 3:04pm |
| Subject: | svn commit - mysqldoc@docsrva: r1249 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | ||
| View as plain text | |||
Author: paul Date: 2006-02-09 15:04:10 +0100 (Thu, 09 Feb 2006) New Revision: 1249 Log: r7245@frost: paul | 2006-02-09 08:03:37 -0600 Additional UNION tweaks. 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:7244 bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2927 + b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:7245 bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2927 Modified: trunk/refman-4.1/sql-syntax.xml =================================================================== --- trunk/refman-4.1/sql-syntax.xml 2006-02-09 14:03:53 UTC (rev 1248) +++ trunk/refman-4.1/sql-syntax.xml 2006-02-09 14:04:10 UTC (rev 1249) @@ -7317,25 +7317,60 @@ <remark role="help-description-begin"/> <para> - <literal>UNION</literal> is used to combine the result from a - number of <literal>SELECT</literal> statements into one result - set. <literal>UNION</literal> is available from MySQL 4.0.0 - on. + <literal>UNION</literal> is used to combine the result from + multiple <literal>SELECT</literal> statements into a single + result set. <literal>UNION</literal> is available from MySQL + 4.0.0 on. </para> <para> - Selected columns listed in corresponding positions of each - <literal>SELECT</literal> statement should have the same type. - (For example, the first column selected by the first statement - should have the same type as the first column selected by the - other statements.) The column names used in the first - <literal>SELECT</literal> statement are used as the column - names for the results returned. + The column names used in the first <literal>SELECT</literal> + statement are used as the column names for the results + returned. Selected columns listed in corresponding positions + of each <literal>SELECT</literal> statement should have the + same data type. (For example, the first column selected by the + first statement should have the same type as the first column + selected by the other statements.) </para> <remark role="help-description-end"/> <para> + As of MySQL 4.1.1, if the data types of corresponding + <literal>SELECT</literal> columns do not match, the types and + lengths of the columns in the <literal>UNION</literal> result + take into account the values retrieved by all of the + <literal>SELECT</literal> statements. For example, consider + the following: + </para> + +<programlisting> +mysql> <userinput>SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);</userinput> ++---------------+ +| REPEAT('a',1) | ++---------------+ +| a | +| bbbbbbbbbb | ++---------------+ +</programlisting> + + <para> + Before MySQL 4.1.1, only the type and length from the first + <literal>SELECT</literal> would have been used and the second + row would have been truncated to a length of 1: + </para> + +<programlisting> +mysql> <userinput>SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);</userinput> ++---------------+ +| REPEAT('a',1) | ++---------------+ +| a | +| b | ++---------------+ +</programlisting> + + <para> The <literal>SELECT</literal> statements are normal select statements, but with the following restrictions: </para> @@ -7363,21 +7398,17 @@ </itemizedlist> <para> - If you specify the <literal>ALL</literal> keyword, the result - includes all matching rows from all the - <literal>SELECT</literal> statements. If you specify - <literal>DISTINCT</literal>, duplicate rows are removed from - the result. If neither keyword is used, the default behavior - is as for <literal>DISTINCT</literal> (duplicate row removal). + The default behavior for <literal>UNION</literal> is that + duplicate rows are removed from the result. The optional + <literal>DISTINCT</literal> keyword (introduced in MySQL + 4.0.17) has no effect other than the default because it also + specifies duplicate-row removal. With the optional + <literal>ALL</literal> keyword, duplicate-row removal does not + occur and the result includes all matching rows from all the + <literal>SELECT</literal> statements. </para> <para> - The <literal>DISTINCT</literal> keyword is an optional word - (introduced in MySQL 4.0.17). It does nothing, but is allowed - in the syntax as required by the SQL standard. - </para> - - <para> Before MySQL 4.1.2, you cannot mix <literal>UNION ALL</literal> and <literal>UNION DISTINCT</literal> in the same query. If you use <literal>ALL</literal> for one @@ -7392,7 +7423,7 @@ </para> <para> - If you want to use an <literal>ORDER BY</literal> or + To use an <literal>ORDER BY</literal> or <literal>LIMIT</literal> clause to sort or limit the entire <literal>UNION</literal> result, parenthesize the individual <literal>SELECT</literal> statements and place the @@ -7487,46 +7518,6 @@ (SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col, col1a; </programlisting> - <remark> - this is also written in the "known bugs" section - </remark> - - <para> - The types and lengths of the columns in the result set of a - <literal>UNION</literal> take into account the values - retrieved by all the <literal>SELECT</literal> statements. - Before MySQL 4.1.1, a limitation of <literal>UNION</literal> - is that only the values from the first - <literal>SELECT</literal> are used to determine result column - types and lengths. This could result in value truncation if, - for example, the first <literal>SELECT</literal> retrieves - shorter values than the second <literal>SELECT</literal>: - </para> - -<programlisting> -mysql> <userinput>SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);</userinput> -+---------------+ -| REPEAT('a',1) | -+---------------+ -| a | -| b | -+---------------+ -</programlisting> - - <para> - That limitation has been removed as of MySQL 4.1.1: - </para> - -<programlisting> -mysql> <userinput>SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);</userinput> -+---------------+ -| REPEAT('a',1) | -+---------------+ -| a | -| bbbbbbbbbb | -+---------------+ -</programlisting> - </section> </section> Modified: trunk/refman-5.0/sql-syntax.xml =================================================================== --- trunk/refman-5.0/sql-syntax.xml 2006-02-09 14:03:53 UTC (rev 1248) +++ trunk/refman-5.0/sql-syntax.xml 2006-02-09 14:04:10 UTC (rev 1249) @@ -7710,24 +7710,49 @@ <remark role="help-description-begin"/> <para> - <literal>UNION</literal> is used to combine the result from a - number of <literal>SELECT</literal> statements into one result - set. + <literal>UNION</literal> is used to combine the result from + multiple <literal>SELECT</literal> statements into a single + result set. </para> <para> - Selected columns listed in corresponding positions of each - <literal>SELECT</literal> statement should have the same type. - (For example, the first column selected by the first statement - should have the same type as the first column selected by the - other statements.) The column names used in the first - <literal>SELECT</literal> statement are used as the column - names for the results returned. + The column names used in the first <literal>SELECT</literal> + statement are used as the column names for the results + returned. Selected columns listed in corresponding positions + of each <literal>SELECT</literal> statement should have the + same data type. (For example, the first column selected by the + first statement should have the same type as the first column + selected by the other statements.) </para> <remark role="help-description-end"/> <para> + If the data types of corresponding <literal>SELECT</literal> + columns do not match, the types and lengths of the columns in + the <literal>UNION</literal> result take into account the + values retrieved by all of the <literal>SELECT</literal> + statements. For example, consider the following: + </para> + +<programlisting> +mysql> <userinput>SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);</userinput> ++---------------+ +| REPEAT('a',1) | ++---------------+ +| a | +| bbbbbbbbbb | ++---------------+ +</programlisting> + + <para> + (In some earlier versions of MySQL, only the type and length + from the first <literal>SELECT</literal> would have been used + and the second row would have been truncated to a length of + 1.) + </para> + + <para> The <literal>SELECT</literal> statements are normal select statements, but with the following restrictions: </para> @@ -7755,22 +7780,17 @@ </itemizedlist> <para> - If you specify the <literal>ALL</literal> keyword, the result - includes all matching rows from all the - <literal>SELECT</literal> statements. If you specify - <literal>DISTINCT</literal>, duplicate rows are removed from - the result. If neither keyword is used, the default behavior - is as for <literal>DISTINCT</literal> (duplicate row removal). + The default behavior for <literal>UNION</literal> is that + duplicate rows are removed from the result. The optional + <literal>DISTINCT</literal> keyword has no effect other than + the default because it also specifies duplicate-row removal. + With the optional <literal>ALL</literal> keyword, + duplicate-row removal does not occur and the result includes + all matching rows from all the <literal>SELECT</literal> + statements. </para> <para> - The <literal>DISTINCT</literal> keyword is an optional word - which has no effect, but is allowed in the syntax as required - by the SQL standard. (In MySQL, <literal>DISTINCT</literal> - represents the default behavior of a union.) - </para> - - <para> You can mix <literal>UNION ALL</literal> and <literal>UNION DISTINCT</literal> in the same query. Mixed <literal>UNION</literal> types are treated such that a @@ -7783,7 +7803,7 @@ </para> <para> - If you want to use an <literal>ORDER BY</literal> or + To use an <literal>ORDER BY</literal> or <literal>LIMIT</literal> clause to sort or limit the entire <literal>UNION</literal> result, parenthesize the individual <literal>SELECT</literal> statements and place the @@ -7878,32 +7898,6 @@ (SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col, col1a; </programlisting> - <remark> - this is also written in the "known bugs" section - </remark> - - <para> - The types and lengths of the columns in the result set of a - <literal>UNION</literal> take into account the values - retrieved by all of the <literal>SELECT</literal> statements. - For example, consider the following: - </para> - -<programlisting> -mysql> <userinput>SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);</userinput> -+---------------+ -| REPEAT('a',1) | -+---------------+ -| a | -| bbbbbbbbbb | -+---------------+ -</programlisting> - - <para> - (In some earlier versions of MySQL, the second row would have - been truncated to a length of 1.) - </para> - </section> </section> Modified: trunk/refman-5.1/sql-syntax.xml =================================================================== --- trunk/refman-5.1/sql-syntax.xml 2006-02-09 14:03:53 UTC (rev 1248) +++ trunk/refman-5.1/sql-syntax.xml 2006-02-09 14:04:10 UTC (rev 1249) @@ -8291,24 +8291,49 @@ <remark role="help-description-begin"/> <para> - <literal>UNION</literal> is used to combine the result from a - number of <literal>SELECT</literal> statements into one result - set. + <literal>UNION</literal> is used to combine the result from + multiple <literal>SELECT</literal> statements into a single + result set. </para> <para> - Selected columns listed in corresponding positions of each - <literal>SELECT</literal> statement should have the same type. - (For example, the first column selected by the first statement - should have the same type as the first column selected by the - other statements.) The column names used in the first - <literal>SELECT</literal> statement are used as the column - names for the results returned. + The column names used in the first <literal>SELECT</literal> + statement are used as the column names for the results + returned. Selected columns listed in corresponding positions + of each <literal>SELECT</literal> statement should have the + same data type. (For example, the first column selected by the + first statement should have the same type as the first column + selected by the other statements.) </para> <remark role="help-description-end"/> <para> + If the data types of corresponding <literal>SELECT</literal> + columns do not match, the types and lengths of the columns in + the <literal>UNION</literal> result take into account the + values retrieved by all of the <literal>SELECT</literal> + statements. For example, consider the following: + </para> + +<programlisting> +mysql> <userinput>SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);</userinput> ++---------------+ +| REPEAT('a',1) | ++---------------+ +| a | +| bbbbbbbbbb | ++---------------+ +</programlisting> + + <para> + (In some earlier versions of MySQL, only the type and length + from the first <literal>SELECT</literal> would have been used + and the second row would have been truncated to a length of + 1.) + </para> + + <para> The <literal>SELECT</literal> statements are normal select statements, but with the following restrictions: </para> @@ -8336,22 +8361,17 @@ </itemizedlist> <para> - If you specify the <literal>ALL</literal> keyword, the result - includes all matching rows from all the - <literal>SELECT</literal> statements. If you specify - <literal>DISTINCT</literal>, duplicate rows are removed from - the result. If neither keyword is used, the default behavior - is as for <literal>DISTINCT</literal> (duplicate row removal). + The default behavior for <literal>UNION</literal> is that + duplicate rows are removed from the result. The optional + <literal>DISTINCT</literal> keyword has no effect other than + the default because it also specifies duplicate-row removal. + With the optional <literal>ALL</literal> keyword, + duplicate-row removal does not occur and the result includes + all matching rows from all the <literal>SELECT</literal> + statements. </para> <para> - The <literal>DISTINCT</literal> keyword is an optional word - which has no effect, but is allowed in the syntax as required - by the SQL standard. (In MySQL, <literal>DISTINCT</literal> - represents the default behavior of a union.) - </para> - - <para> You can mix <literal>UNION ALL</literal> and <literal>UNION DISTINCT</literal> in the same query. Mixed <literal>UNION</literal> types are treated such that a @@ -8364,7 +8384,7 @@ </para> <para> - If you want to use an <literal>ORDER BY</literal> or + To use an <literal>ORDER BY</literal> or <literal>LIMIT</literal> clause to sort or limit the entire <literal>UNION</literal> result, parenthesize the individual <literal>SELECT</literal> statements and place the @@ -8459,32 +8479,6 @@ (SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col, col1a; </programlisting> - <remark> - this is also written in the "known bugs" section - </remark> - - <para> - The types and lengths of the columns in the result set of a - <literal>UNION</literal> take into account the values - retrieved by all of the <literal>SELECT</literal> statements. - For example, consider the following: - </para> - -<programlisting> -mysql> <userinput>SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);</userinput> -+---------------+ -| REPEAT('a',1) | -+---------------+ -| a | -| bbbbbbbbbb | -+---------------+ -</programlisting> - - <para> - (In some earlier versions of MySQL, the second row would have - been truncated to a length of 1.) - </para> - </section> </section>
| Thread | ||
|---|---|---|
| • svn commit - mysqldoc@docsrva: r1249 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | paul | 9 Feb |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 0.679 sec. using MySQL 5.1.14-beta-log