Author: jstephens
Date: 2006-09-01 03:27:56 +0200 (Fri, 01 Sep 2006)
New Revision: 3186
Log:
Updated description of NO_UNSIGNED_SUBTRACTION and added example. (Thanks, Vladimir!)
Modified:
trunk/refman-4.1/database-administration.xml
trunk/refman-5.0/database-administration.xml
trunk/refman-5.1/database-administration.xml
Modified: trunk/refman-4.1/database-administration.xml
===================================================================
--- trunk/refman-4.1/database-administration.xml 2006-08-31 23:49:33 UTC (rev 3185)
+++ trunk/refman-4.1/database-administration.xml 2006-09-01 01:27:56 UTC (rev 3186)
Changed blocks: 11, Lines Added: 52, Lines Deleted: 19; 7074 bytes
@@ -8860,7 +8860,7 @@
with this mode enabled. With
<literal>ANSI_QUOTES</literal> enabled, you cannot use
double quotes to quote literal strings, because it is
- interpreted as an identifier. (New in MySQL 4.0.0)
+ interpreted as an identifier. (Added in MySQL 4.0.0)
</para>
</listitem>
@@ -8891,7 +8891,7 @@
</programlisting>
<para>
- (New in MySQL 4.0.0)
+ (Added in MySQL 4.0.0)
</para>
<para>
@@ -8917,7 +8917,7 @@
<literal>NO_AUTO_VALUE_ON_ZERO</literal> suppresses this
behavior for <literal>0</literal> so that only
<literal>NULL</literal> generates the next sequence
- number. (New in MySQL 4.1.1)
+ number. (Added in MySQL 4.1.1)
</para>
<para>
@@ -8951,7 +8951,7 @@
When creating a table, ignore all <literal>INDEX
DIRECTORY</literal> and <literal>DATA DIRECTORY</literal>
directives. This option is useful on slave replication
- servers. (New in MySQL 4.0.15)
+ servers. (Added in MySQL 4.0.15)
</para>
</listitem>
@@ -8967,8 +8967,8 @@
<para>
Do not print MySQL-specific column options in the output
of <literal>SHOW CREATE TABLE</literal>. This mode is used
- by <command>mysqldump</command> in portability mode. (New
- in MySQL 4.1.1)
+ by <command>mysqldump</command> in portability mode.
+ (Added in MySQL 4.1.1)
</para>
</listitem>
@@ -8984,7 +8984,7 @@
<para>
Do not print MySQL-specific index options in the output of
<literal>SHOW CREATE TABLE</literal>. This mode is used by
- <command>mysqldump</command> in portability mode. (New in
+ <command>mysqldump</command> in portability mode. (Added in
MySQL 4.1.1)
</para>
</listitem>
@@ -9002,8 +9002,8 @@
Do not print MySQL-specific table options (such as
<literal>ENGINE</literal>) in the output of <literal>SHOW
CREATE TABLE</literal>. This mode is used by
- <command>mysqldump</command> in portability mode. (New in
- MySQL 4.1.1)
+ <command>mysqldump</command> in portability mode. (Added
+ in MySQL 4.1.1)
</para>
</listitem>
@@ -9019,21 +9019,54 @@
<para>
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)
+ unsigned. In other words, <emphasis>the result of a
+ subtraction is always signed whenever this mode is in
+ effect, even if one of the operands is
+ unsigned</emphasis>. For example, compare the type of
+ column <literal>c2</literal> in table <literal>t1</literal>
+ with that of column <literal>c2</literal> in table
+ <literal>t2</literal>:
</para>
+
+<programlisting>
+mysql> <userinput>SET SQL_MODE='';</userinput>
+mysql> <userinput>CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL);</userinput>
+mysql> <userinput>CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test;</userinput>
+mysql> <userinput>DESCRIBE t1;</userinput>
++-------+---------------------+------+-----+---------+-------+
+| Field | Type | Null | Key | Default | Extra |
++-------+---------------------+------+-----+---------+-------+
+| c2 | bigint(21) unsigned | | | 0 | |
++-------+---------------------+------+-----+---------+-------+
+mysql> <userinput>SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';</userinput>
+mysql> <userinput>CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test;</userinput>
+mysql> <userinput>DESCRIBE t2;</userinput>
++-------+------------+------+-----+---------+-------+
+| Field | Type | Null | Key | Default | Extra |
++-------+------------+------+-----+---------+-------+
+| c2 | bigint(21) | | | 0 | |
++-------+------------+------+-----+---------+-------+
+</programlisting>
+
+ <para>
+ Note that this means that <literal>BIGINT
+ UNSIGNED</literal> is not 100% usable in all contexts.
+ See <xref linkend="cast-functions"/>. (Added in MySQL
+ 4.0.2)
+ </para>
+
<programlisting>
-mysql>t; <userinput>SET sql_mode = '';</userinput>
-mysql>t; <userinput>SELECT CAST(0 AS UNSIGNED) - 1;</userinput>
+mysql> <userinput>SET SQL_MODE = '';</userinput>
+mysql> <userinput>SELECT CAST(0 AS UNSIGNED) - 1;</userinput>
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
| 18446744073709551615 |
+-------------------------+
-mysql>t; <userinput>SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';</userinput>
-mysql>t; <userinput>SELECT CAST(0 AS UNSIGNED) - 1;</userinput>
+
+mysql> <userinput>SET SQL_MODE = 'NO_UNSIGNED_SUBTRACTION';</userinput>
+mysql> <userinput>SELECT CAST(0 AS UNSIGNED) - 1;</userinput>
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
@@ -9055,7 +9088,7 @@
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
+ BY</literal> clause. (Added 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:
@@ -9078,7 +9111,7 @@
<para>
Treat <literal>||</literal> as a string concatenation
operator (same as <literal>CONCAT()</literal>) rather than
- as a synonym for <literal>OR</literal>. (New in MySQL
+ as a synonym for <literal>OR</literal>. (Added in MySQL
4.0.0)
</para>
</listitem>
@@ -9096,7 +9129,7 @@
Treat <literal>REAL</literal> as a synonym for
<literal>FLOAT</literal>. By default, MySQL treats
<literal>REAL</literal> as a synonym for
- <literal>DOUBLE</literal>. (New in MySQL 4.0.0)
+ <literal>DOUBLE</literal>. (Added in MySQL 4.0.0)
</para>
</listitem>
Modified: trunk/refman-5.0/database-administration.xml
===================================================================
--- trunk/refman-5.0/database-administration.xml 2006-08-31 23:49:33 UTC (rev 3185)
+++ trunk/refman-5.0/database-administration.xml 2006-09-01 01:27:56 UTC (rev 3186)
Changed blocks: 1, Lines Added: 39, Lines Deleted: 7; 3186 bytes
@@ -10277,21 +10277,53 @@
<para>
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"/>.
+ unsigned. In other words, <emphasis>the result of a
+ subtraction is always signed whenever this mode is in
+ effect, even if one of the operands is
+ unsigned</emphasis>. For example, compare the type of
+ column <literal>c2</literal> in table <literal>t1</literal>
+ with that of column <literal>c2</literal> in table
+ <literal>t2</literal>:
</para>
+
+<programlisting>
+mysql> <userinput>SET SQL_MODE='';</userinput>
+mysql> <userinput>CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL);</userinput>
+mysql> <userinput>CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test;</userinput>
+mysql> <userinput>DESCRIBE t1;</userinput>
++-------+---------------------+------+-----+---------+-------+
+| Field | Type | Null | Key | Default | Extra |
++-------+---------------------+------+-----+---------+-------+
+| c2 | bigint(21) unsigned | | | 0 | |
++-------+---------------------+------+-----+---------+-------+
+mysql> <userinput>SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';</userinput>
+mysql> <userinput>CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test;</userinput>
+mysql> <userinput>DESCRIBE t2;</userinput>
++-------+------------+------+-----+---------+-------+
+| Field | Type | Null | Key | Default | Extra |
++-------+------------+------+-----+---------+-------+
+| c2 | bigint(21) | | | 0 | |
++-------+------------+------+-----+---------+-------+
+</programlisting>
+
+ <para>
+ Note that this means that <literal>BIGINT
+ UNSIGNED</literal> is not 100% usable in all contexts.
+ See <xref linkend="cast-functions"/>.
+ </para>
+
<programlisting>
-mysql>t; <userinput>SET sql_mode = '';</userinput>
-mysql>t; <userinput>SELECT CAST(0 AS UNSIGNED) - 1;</userinput>
+mysql> <userinput>SET SQL_MODE = '';</userinput>
+mysql> <userinput>SELECT CAST(0 AS UNSIGNED) - 1;</userinput>
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
| 18446744073709551615 |
+-------------------------+
-mysql>t; <userinput>SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';</userinput>
-mysql>t; <userinput>SELECT CAST(0 AS UNSIGNED) - 1;</userinput>
+
+mysql> <userinput>SET SQL_MODE = 'NO_UNSIGNED_SUBTRACTION';</userinput>
+mysql> <userinput>SELECT CAST(0 AS UNSIGNED) - 1;</userinput>
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
Modified: trunk/refman-5.1/database-administration.xml
===================================================================
--- trunk/refman-5.1/database-administration.xml 2006-08-31 23:49:33 UTC (rev 3185)
+++ trunk/refman-5.1/database-administration.xml 2006-09-01 01:27:56 UTC (rev 3186)
Changed blocks: 1, Lines Added: 39, Lines Deleted: 7; 3186 bytes
@@ -10457,21 +10457,53 @@
<para>
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"/>.
+ unsigned. In other words, <emphasis>the result of a
+ subtraction is always signed whenever this mode is in
+ effect, even if one of the operands is
+ unsigned</emphasis>. For example, compare the type of
+ column <literal>c2</literal> in table <literal>t1</literal>
+ with that of column <literal>c2</literal> in table
+ <literal>t2</literal>:
</para>
+
+<programlisting>
+mysql> <userinput>SET SQL_MODE='';</userinput>
+mysql> <userinput>CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL);</userinput>
+mysql> <userinput>CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test;</userinput>
+mysql> <userinput>DESCRIBE t1;</userinput>
++-------+---------------------+------+-----+---------+-------+
+| Field | Type | Null | Key | Default | Extra |
++-------+---------------------+------+-----+---------+-------+
+| c2 | bigint(21) unsigned | | | 0 | |
++-------+---------------------+------+-----+---------+-------+
+mysql> <userinput>SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';</userinput>
+mysql> <userinput>CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test;</userinput>
+mysql> <userinput>DESCRIBE t2;</userinput>
++-------+------------+------+-----+---------+-------+
+| Field | Type | Null | Key | Default | Extra |
++-------+------------+------+-----+---------+-------+
+| c2 | bigint(21) | | | 0 | |
++-------+------------+------+-----+---------+-------+
+</programlisting>
+
+ <para>
+ Note that this means that <literal>BIGINT
+ UNSIGNED</literal> is not 100% usable in all contexts.
+ See <xref linkend="cast-functions"/>.
+ </para>
+
<programlisting>
-mysql>t; <userinput>SET sql_mode = '';</userinput>
-mysql>t; <userinput>SELECT CAST(0 AS UNSIGNED) - 1;</userinput>
+mysql> <userinput>SET SQL_MODE = '';</userinput>
+mysql> <userinput>SELECT CAST(0 AS UNSIGNED) - 1;</userinput>
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
| 18446744073709551615 |
+-------------------------+
-mysql>t; <userinput>SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';</userinput>
-mysql>t; <userinput>SELECT CAST(0 AS UNSIGNED) - 1;</userinput>
+
+mysql> <userinput>SET SQL_MODE = 'NO_UNSIGNED_SUBTRACTION';</userinput>
+mysql> <userinput>SELECT CAST(0 AS UNSIGNED) - 1;</userinput>
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r3186 - in trunk: refman-4.1 refman-5.0 refman-5.1 | jon | 1 Sep |
