| List: | Commits | « Previous MessageNext Message » | |
| From: | paul | Date: | May 10 2006 9:01pm |
| Subject: | svn commit - mysqldoc@docsrva: r2073 - in trunk: . refman-4.1 refman-5.0 refman-5.1 refman-common | ||
| View as plain text | |||
Author: paul Date: 2006-05-10 21:01:01 +0200 (Wed, 10 May 2006) New Revision: 2073 Log: r7412@polar: paul | 2006-05-10 13:55:47 -0500 Revise description of ONLY_FULL_GROUP_BY. Mention that in 5.0/5.1 it also applies to HAVING, not just the SELECT list. (Bug#18739) Modified: trunk/ trunk/refman-4.1/database-administration.xml trunk/refman-4.1/functions.xml trunk/refman-5.0/database-administration.xml trunk/refman-5.0/functions.xml trunk/refman-5.1/database-administration.xml trunk/refman-5.1/functions.xml trunk/refman-common/news-5.0.xml trunk/refman-common/news-5.1.xml trunk/refman-common/titles.en.ent Property changes on: trunk ___________________________________________________________________ Name: svk:merge - 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:7411 b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:10279 bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:4886 + 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:7412 b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:10279 bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:4886 Modified: trunk/refman-4.1/database-administration.xml =================================================================== --- trunk/refman-4.1/database-administration.xml 2006-05-10 19:00:45 UTC (rev 2072) +++ trunk/refman-4.1/database-administration.xml 2006-05-10 19:01:01 UTC (rev 2073) @@ -5021,28 +5021,28 @@ MySQL 3.23.50. </para> </listitem> - + <listitem> <para> <literal>ndb_autoincrement_prefetch_sz</literal> </para> - + <para> Determines the probability of gaps in an autoincremented column. Set to <literal>1</literal> to minimize this. Set to a high value for optimization — makes inserts faster, but decreases the likelihood that consecutive - autoincrement numbers will be used in a batch of - inserts. Default value: <literal>32</literal>. Mimimum - value: <literal>1</literal>. + autoincrement numbers will be used in a batch of inserts. + Default value: <literal>32</literal>. Mimimum value: + <literal>1</literal>. </para> </listitem> - + <listitem> <para> - <literal>ndb_cache_check_time</literal> + <literal>ndb_cache_check_time</literal> </para> - + <para> The number of seconds to wait before checking the <literal>NDB</literal> query cache. Setting this to @@ -5052,27 +5052,27 @@ means the <literal>NDB</literal> query cache will seldom be checked and invalidated because of updates on another <command>mysqld</command>. It is generally not desirable - to set this to a value greater than <literal>2</literal>. + to set this to a value greater than <literal>2</literal>. </para> </listitem> - + <listitem> <para> <literal>ndb_force_send</literal> </para> - + <para> Forces sending of buffers to <literal>NDB</literal> immediately, without waiting for other threads. Defaults to <literal>ON</literal>. </para> </listitem> - + <listitem> <para> <literal>ndb_index_stat_cache_entries</literal> </para> - + <para> Sets the granularity of the statistics by determining the number of starting and ending keys to store in the @@ -5081,23 +5081,23 @@ directly. Default value: <literal>32</literal>. </para> </listitem> - + <listitem> <para> - <literal>ndb_index_stat_enable</literal> + <literal>ndb_index_stat_enable</literal> </para> - + <para> Use <literal>NDB</literal> index statistics in query optimization. Defaults to <literal>ON</literal>. </para> </listitem> - + <listitem> <para> <literal>ndb_index_stat_update_freq</literal> </para> - + <para> How often to query data nodes instead of the statistics cache. For example, a value of <literal>20</literal> (the @@ -5105,12 +5105,12 @@ 20<superscript>th</superscript> query to the data nodes. </para> </listitem> - + <listitem> <para> <literal>ndb_report_thresh_binlog_epoch_slip</literal> </para> - + <para> This is a threshold on the number of epochs to be behind before reporting binlog status. For example, a value of @@ -5121,12 +5121,12 @@ cluster log. </para> </listitem> - + <listitem> <para> <literal>ndb_report_thresh_binlog_mem_usage</literal> </para> - + <para> This is a threshold on the percentage of free memory remaining before reporting binlog status. For example, a @@ -5136,12 +5136,12 @@ be sent to the cluster log. </para> </listitem> - + <listitem> <para> <literal>ndb_use_exact_count</literal> </para> - + <para> Forces <literal>NDB</literal> to use an count of records during <literal>SELECT COUNT(*)</literal> query planning @@ -5152,17 +5152,17 @@ <literal>OFF</literal>. </para> </listitem> - + <listitem> <para> <literal>ndb_use_transactions</literal> </para> - + <para> You can disable <literal>NDB</literal> transaction support by setting this variable's values to <literal>OFF</literal> (not recommended). The default is - <literal>ON</literal>. + <literal>ON</literal>. </para> </listitem> @@ -8944,8 +8944,8 @@ </para> <para> - In integer subtraction operations, do not mark the result as - <literal>UNSIGNED</literal> if one of the operands is + In integer subtraction operations, do not mark the result + as <literal>UNSIGNED</literal> if one of the operands is unsigned. Note that this makes <literal>BIGINT UNSIGNED</literal> not 100% usable in all contexts. See <xref linkend="cast-functions"/>. (New in MySQL 4.0.2) @@ -8962,10 +8962,18 @@ </para> <para> - Do not allow queries for which the <literal>GROUP - BY</literal> clause refers to a column that is not present - in the output column list. (New in MySQL 4.0.0) + Do not allow queries for which the + <literal>SELECT</literal> list refers to non-aggregated + columns that are not named in the <literal>GROUP + BY</literal> clause. (New in MySQL 4.0.0) The following + query is invalid with this mode enabled because + <literal>address</literal> is not named in the + <literal>GROUP BY</literal> clause: </para> + +<programlisting> +SELECT name, address, MAX(age) FROM t GROUP BY name; +</programlisting> </listitem> <listitem> Modified: trunk/refman-4.1/functions.xml =================================================================== --- trunk/refman-4.1/functions.xml 2006-05-10 19:00:45 UTC (rev 2072) +++ trunk/refman-4.1/functions.xml 2006-05-10 19:01:01 UTC (rev 2073) @@ -14967,13 +14967,12 @@ <para> MySQL extends the use of <literal>GROUP BY</literal> so that you - can use columns or calculations in the <literal>SELECT</literal> - list that do not appear in the <literal>GROUP BY</literal> - clause. This stands for <quote>any possible value for this - group.</quote> You can use this to get better performance by - avoiding sorting and grouping on unnecessary items. For example, - you do not need to group on <literal>customer.name</literal> in - the following query: + can use non-aggregated columns or calculations in the + <literal>SELECT</literal> list that do not appear in the + <literal>GROUP BY</literal> clause. You can use this feature to + get better performance by avoiding unnecessary column sorting + and grouping. For example, you do not need to group on + <literal>customer.name</literal> in the following query: </para> <programlisting> @@ -14986,18 +14985,38 @@ <para> In standard SQL, you would have to add <literal>customer.name</literal> to the <literal>GROUP - BY</literal> clause. In MySQL, the name is redundant if you do - not run with the <literal>ONLY_FULL_GROUP_BY</literal> SQL mode - enabled. + BY</literal> clause. In MySQL, the name is redundant. </para> <para> Do <emphasis>not</emphasis> use this feature if the columns you - omit from the <literal>GROUP BY</literal> part are not unique in - the group! You get unpredictable results. + omit from the <literal>GROUP BY</literal> part are not constant + in the group. The server is free to return any value from the + group, so the results are indeterminate unless all values are + the same. </para> <para> + A similar MySQL extension applies to the + <literal>HAVING</literal> clause. The SQL standard does not + allow the <literal>HAVING</literal> clause to name any column + that is not found in the <literal>GROUP BY</literal> clause if + it is not enclosed in an aggregate function. MySQL allows the + use of such columns to simplify calculations. This extension + assumes that the non-grouped columns will have the same + group-wise values. Otherwise, the result is indeterminate. + </para> + + <para> + If the <literal>ONLY_FULL_GROUP_BY</literal> SQL mode is + enabled, the MySQL extension to <literal>GROUP BY</literal> does + not apply to the <literal>SELECT</literal>. That is, columns not + named in the <literal>GROUP BY</literal> clause cannot be used + in the <literal>SELECT</literal> list if not used in an + aggregate function. + </para> + + <para> In some cases, you can use <literal>MIN()</literal> and <literal>MAX()</literal> to obtain a specific column value even if it isn't unique. The following gives the value of Modified: trunk/refman-5.0/database-administration.xml =================================================================== --- trunk/refman-5.0/database-administration.xml 2006-05-10 19:00:45 UTC (rev 2072) +++ trunk/refman-5.0/database-administration.xml 2006-05-10 19:01:01 UTC (rev 2073) @@ -9998,8 +9998,8 @@ </para> <para> - In integer subtraction operations, do not mark the result as - <literal>UNSIGNED</literal> if one of the operands is + In integer subtraction operations, do not mark the result + as <literal>UNSIGNED</literal> if one of the operands is unsigned. Note that this makes <literal>BIGINT UNSIGNED</literal> not 100% usable in all contexts. See <xref linkend="cast-functions"/>. @@ -10070,10 +10070,24 @@ </para> <para> - Do not allow queries for which the <literal>GROUP - BY</literal> clause refers to a column that is not present - in the output column list. + Do not allow queries for which the + <literal>SELECT</literal> list refers to non-aggregated + columns that are not named in the <literal>GROUP + BY</literal> clause. The following query is invalid with + this mode enabled because <literal>address</literal> is + not named in the <literal>GROUP BY</literal> clause: </para> + +<programlisting> +SELECT name, address, MAX(age) FROM t GROUP BY name; +</programlisting> + + <para> + As of MySQL 5.0.22, this mode also restricts references to + non-aggregated columns in the <literal>HAVING</literal> + clause that are not named in the <literal>GROUP + BY</literal> clause. + </para> </listitem> <listitem> @@ -12582,12 +12596,10 @@ </para> <warning> - <para> The <literal>[mysqld]</literal> section name is deprecated and should not be used in a configuration file. </para> - </warning> <para> @@ -12703,12 +12715,10 @@ </programlisting> <note> - <para> The Instance Manager must be restarted after adding/changing passwords. </para> - </note> </refsection> @@ -13215,14 +13225,12 @@ </programlisting> <warning> - <para> The <literal>[mysqld]</literal> section name is deprecated and should not be used in a configuration file, instead [mysqldN] sections such as [mysqld1] should be used for specific instances. </para> - </warning> <para> Modified: trunk/refman-5.0/functions.xml =================================================================== --- trunk/refman-5.0/functions.xml 2006-05-10 19:00:45 UTC (rev 2072) +++ trunk/refman-5.0/functions.xml 2006-05-10 19:01:01 UTC (rev 2073) @@ -15278,13 +15278,12 @@ <para> MySQL extends the use of <literal>GROUP BY</literal> so that you - can use columns or calculations in the <literal>SELECT</literal> - list that do not appear in the <literal>GROUP BY</literal> - clause. This stands for <quote>any possible value for this - group.</quote> You can use this to get better performance by - avoiding sorting and grouping on unnecessary items. For example, - you do not need to group on <literal>customer.name</literal> in - the following query: + can use non-aggregated columns or calculations in the + <literal>SELECT</literal> list that do not appear in the + <literal>GROUP BY</literal> clause. You can use this feature to + get better performance by avoiding unnecessary column sorting + and grouping. For example, you do not need to group on + <literal>customer.name</literal> in the following query: </para> <programlisting> @@ -15297,18 +15296,38 @@ <para> In standard SQL, you would have to add <literal>customer.name</literal> to the <literal>GROUP - BY</literal> clause. In MySQL, the name is redundant if you do - not run with the <literal>ONLY_FULL_GROUP_BY</literal> SQL mode - enabled. + BY</literal> clause. In MySQL, the name is redundant. </para> <para> Do <emphasis>not</emphasis> use this feature if the columns you - omit from the <literal>GROUP BY</literal> part are not unique in - the group! You get unpredictable results. + omit from the <literal>GROUP BY</literal> part are not constant + in the group. The server is free to return any value from the + group, so the results are indeterminate unless all values are + the same. </para> <para> + A similar MySQL extension applies to the + <literal>HAVING</literal> clause. The SQL standard does not + allow the <literal>HAVING</literal> clause to name any column + that is not found in the <literal>GROUP BY</literal> clause if + it is not enclosed in an aggregate function. MySQL allows the + use of such columns to simplify calculations. This extension + assumes that the non-grouped columns will have the same + group-wise values. Otherwise, the result is indeterminate. + </para> + + <para> + If the <literal>ONLY_FULL_GROUP_BY</literal> SQL mode is + enabled, the MySQL extension to <literal>GROUP BY</literal> does + not apply. That is, columns not named in the <literal>GROUP + BY</literal> clause cannot be used in the + <literal>SELECT</literal> list or <literal>HAVING</literal> + clause if not used in an aggregate function. + </para> + + <para> In some cases, you can use <literal>MIN()</literal> and <literal>MAX()</literal> to obtain a specific column value even if it isn't unique. The following gives the value of Modified: trunk/refman-5.1/database-administration.xml =================================================================== --- trunk/refman-5.1/database-administration.xml 2006-05-10 19:00:45 UTC (rev 2072) +++ trunk/refman-5.1/database-administration.xml 2006-05-10 19:01:01 UTC (rev 2073) @@ -10370,8 +10370,8 @@ </para> <para> - In integer subtraction operations, do not mark the result as - <literal>UNSIGNED</literal> if one of the operands is + In integer subtraction operations, do not mark the result + as <literal>UNSIGNED</literal> if one of the operands is unsigned. Note that this makes <literal>BIGINT UNSIGNED</literal> not 100% usable in all contexts. See <xref linkend="cast-functions"/>. @@ -10441,10 +10441,24 @@ </para> <para> - Do not allow queries for which the <literal>GROUP - BY</literal> clause refers to a column that is not present - in the output column list. + Do not allow queries for which the + <literal>SELECT</literal> list refers to non-aggregated + columns that are not named in the <literal>GROUP + BY</literal> clause. The following query is invalid with + this mode enabled because <literal>address</literal> is + not named in the <literal>GROUP BY</literal> clause: </para> + +<programlisting> +SELECT name, address, MAX(age) FROM t GROUP BY name; +</programlisting> + + <para> + As of MySQL 5.1.11, this mode also restricts references to + non-aggregated columns in the <literal>HAVING</literal> + clause that are not named in the <literal>GROUP + BY</literal> clause. + </para> </listitem> <listitem> @@ -12621,12 +12635,10 @@ </para> <warning> - <para> The <literal>[mysqld]</literal> section name is deprecated and should not be used in a configuration file. </para> - </warning> <para> @@ -12742,12 +12754,10 @@ </programlisting> <note> - <para> The Instance Manager must be restarted after adding/changing passwords. </para> - </note> </refsection> @@ -13275,14 +13285,12 @@ </programlisting> <warning> - <para> The <literal>[mysqld]</literal> section name is deprecated and should not be used in a configuration file, instead [mysqldN] sections such as [mysqld1] should be used for specific instances. </para> - </warning> <para> Modified: trunk/refman-5.1/functions.xml =================================================================== --- trunk/refman-5.1/functions.xml 2006-05-10 19:00:45 UTC (rev 2072) +++ trunk/refman-5.1/functions.xml 2006-05-10 19:01:01 UTC (rev 2073) @@ -15736,13 +15736,12 @@ <para> MySQL extends the use of <literal>GROUP BY</literal> so that you - can use columns or calculations in the <literal>SELECT</literal> - list that do not appear in the <literal>GROUP BY</literal> - clause. This stands for <quote>any possible value for this - group.</quote> You can use this to get better performance by - avoiding sorting and grouping on unnecessary items. For example, - you do not need to group on <literal>customer.name</literal> in - the following query: + can use non-aggregated columns or calculations in the + <literal>SELECT</literal> list that do not appear in the + <literal>GROUP BY</literal> clause. You can use this feature to + get better performance by avoiding unnecessary column sorting + and grouping. For example, you do not need to group on + <literal>customer.name</literal> in the following query: </para> <programlisting> @@ -15755,18 +15754,38 @@ <para> In standard SQL, you would have to add <literal>customer.name</literal> to the <literal>GROUP - BY</literal> clause. In MySQL, the name is redundant if you do - not run with the <literal>ONLY_FULL_GROUP_BY</literal> SQL mode - enabled. + BY</literal> clause. In MySQL, the name is redundant. </para> <para> Do <emphasis>not</emphasis> use this feature if the columns you - omit from the <literal>GROUP BY</literal> part are not unique in - the group! You get unpredictable results. + omit from the <literal>GROUP BY</literal> part are not constant + in the group. The server is free to return any value from the + group, so the results are indeterminate unless all values are + the same. </para> <para> + A similar MySQL extension applies to the + <literal>HAVING</literal> clause. The SQL standard does not + allow the <literal>HAVING</literal> clause to name any column + that is not found in the <literal>GROUP BY</literal> clause if + it is not enclosed in an aggregate function. MySQL allows the + use of such columns to simplify calculations. This extension + assumes that the non-grouped columns will have the same + group-wise values. Otherwise, the result is indeterminate. + </para> + + <para> + If the <literal>ONLY_FULL_GROUP_BY</literal> SQL mode is + enabled, the MySQL extension to <literal>GROUP BY</literal> does + not apply. That is, columns not named in the <literal>GROUP + BY</literal> clause cannot be used in the + <literal>SELECT</literal> list or <literal>HAVING</literal> + clause if not used in an aggregate function. + </para> + + <para> In some cases, you can use <literal>MIN()</literal> and <literal>MAX()</literal> to obtain a specific column value even if it isn't unique. The following gives the value of Modified: trunk/refman-common/news-5.0.xml =================================================================== --- trunk/refman-common/news-5.0.xml 2006-05-10 19:00:45 UTC (rev 2072) +++ trunk/refman-common/news-5.0.xml 2006-05-10 19:01:01 UTC (rev 2073) @@ -205,6 +205,16 @@ <listitem> <para> + The <literal>ONLY_FULL_GROUP_BY</literal> SQL mode now also + applies to the <literal>HAVING</literal> clause. That is, + columns not named in the <literal>GROUP BY</literal> clause + cannot be used in the <literal>HAVING</literal> clause if not + used in an aggregate function. (Bug #18739) + </para> + </listitem> + + <listitem> + <para> SQL syntax for prepared statements now supports <literal>ANALYZE TABLE</literal>, <literal>OPTIMIZE TABLE</literal>, and <literal>REPAIR TABLE</literal>. (Bug Modified: trunk/refman-common/news-5.1.xml =================================================================== --- trunk/refman-common/news-5.1.xml 2006-05-10 19:00:45 UTC (rev 2072) +++ trunk/refman-common/news-5.1.xml 2006-05-10 19:01:01 UTC (rev 2073) @@ -120,6 +120,16 @@ <listitem> <para> + The <literal>ONLY_FULL_GROUP_BY</literal> SQL mode now also + applies to the <literal>HAVING</literal> clause. That is, + columns not named in the <literal>GROUP BY</literal> clause + cannot be used in the <literal>HAVING</literal> clause if not + used in an aggregate function. (Bug #18739) + </para> + </listitem> + + <listitem> + <para> SQL syntax for prepared statements now supports <literal>ANALYZE TABLE</literal>, <literal>OPTIMIZE TABLE</literal>, and <literal>REPAIR TABLE</literal>. (Bug Modified: trunk/refman-common/titles.en.ent =================================================================== --- trunk/refman-common/titles.en.ent 2006-05-10 19:00:45 UTC (rev 2072) +++ trunk/refman-common/titles.en.ent 2006-05-10 19:01:01 UTC (rev 2073) @@ -519,7 +519,7 @@ <!ENTITY title-grant "<literal>GRANT</literal> Syntax"> <!ENTITY title-group-by-functions "<literal>GROUP BY</literal> (Aggregate) Functions"> <!ENTITY title-group-by-functions-and-modifiers "Functions and Modifiers for Use with <literal>GROUP BY</literal> Clauses"> -<!ENTITY title-group-by-hidden-fields "<literal>GROUP BY</literal> with Hidden Fields"> +<!ENTITY title-group-by-hidden-fields "<literal>GROUP BY</literal> and <literal>HAVING</literal> with Hidden Fields"> <!ENTITY title-group-by-modifiers "<literal>GROUP BY</literal> Modifiers"> <!ENTITY title-group-by-optimization "<literal>GROUP BY</literal> Optimization"> <!ENTITY title-handler "<literal>HANDLER</literal> Syntax">
| Thread | ||
|---|---|---|
| • svn commit - mysqldoc@docsrva: r2073 - in trunk: . refman-4.1 refman-5.0 refman-5.1 refman-common | paul | 10 May |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 0.689 sec. using MySQL 5.1.14-beta-log