Author: paul
Date: 2009-05-13 22:22:33 +0200 (Wed, 13 May 2009)
New Revision: 14903
Log:
r41143@frost: paul | 2009-05-13 14:38:51 -0500
Fix docs bug:
Bug#44814: undocumented: create select fills columns right to left
Modified:
trunk/refman-4.1/sql-syntax-data-definition.xml
trunk/refman-5.0/sql-syntax-data-definition.xml
trunk/refman-5.1/sql-syntax-data-definition.xml
trunk/refman-5.4/sql-syntax-data-definition.xml
trunk/refman-6.0/sql-syntax-data-definition.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:41755
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:41141
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:38062
+ 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:41755
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:41143
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:38062
Modified: trunk/refman-4.1/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-4.1/sql-syntax-data-definition.xml 2009-05-13 18:49:38 UTC (rev 14902)
+++ trunk/refman-4.1/sql-syntax-data-definition.xml 2009-05-13 20:22:33 UTC (rev 14903)
Changed blocks: 2, Lines Added: 64, Lines Deleted: 9; 3392 bytes
@@ -1783,15 +1783,6 @@
<literal role="stmt">CREATE TABLE</literal> statement.
</para>
- <note>
- <para>
- If you use <literal>IF NOT EXISTS</literal> in a <literal>CREATE
- TABLE ... SELECT</literal> statement, any rows selected by the
- <literal role="stmt">SELECT</literal> part are inserted
- regardless of whether the table already exists.
- </para>
- </note>
-
<para>
MySQL represents each table by an <filename>.frm</filename> table
format (definition) file in the database directory. The storage
@@ -3102,6 +3093,70 @@
</programlisting>
<para>
+ For <literal>CREATE TABLE ... SELECT</literal>, if <literal>IF NOT
+ EXISTS</literal> is given and the table already exists, MySQL
+ handles the statement as follows:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ The table definition given in the <literal>CREATE
+ TABLE</literal> part is ignored. No error occurs, even if the
+ definition does not match that of the existing table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If there is a mismatch between the number of columns in the
+ table and the number of columns produced by the
+ <literal>SELECT</literal> part, the selected values are
+ assigned to the rightmost columns. For example, if the table
+ contains <replaceable>n</replaceable> columns and the
+ <literal>SELECT</literal> produces
+ <replaceable>m</replaceable> columns, where
+ <replaceable>m</replaceable> <
+ <replaceable>n</replaceable>, the selected values are assigned
+ to the <replaceable>m</replaceable> rightmost columns in the
+ table. The initial <replaceable>n</replaceable> −
+ <replaceable>m</replaceable> columns are assigned their
+ default values, either those specified explicitly in the
+ column definition or the implicit column data type default if
+ the definition contains no default.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The following example illustrates <literal>IF NOT EXISTS</literal>
+ handling:
+ </para>
+
+<programlisting>
+<!--
+mysql> DROP TABLE IF EXISTS t1;
+Query OK, 0 rows affected (0.00 sec)
+-->
+mysql> <userinput>CREATE TABLE t1 (i1 INT DEFAULT 0, i2 INT, i3 INT, i4 INT);</userinput>
+Query OK, 0 rows affected (0.05 sec)
+
+mysql> <userinput>CREATE TABLE IF NOT EXISTS t1 (c1 CHAR(10)) SELECT 1, 2;</userinput>
+Query OK, 1 row affected, 1 warning (0.01 sec)
+Records: 1 Duplicates: 0 Warnings: 0
+
+mysql> <userinput>SELECT * FROM t1;</userinput>
++------+------+------+------+
+| i1 | i2 | i3 | i4 |
++------+------+------+------+
+| 0 | NULL | 1 | 2 |
++------+------+------+------+
+1 row in set (0.00 sec)
+</programlisting>
+
+ <para>
In MySQL 4.1, you can also use <literal>LIKE</literal> to create
an empty table based on the definition of another table, including
any column attributes and indexes the original table has:
Modified: trunk/refman-5.0/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-5.0/sql-syntax-data-definition.xml 2009-05-13 18:49:38 UTC (rev 14902)
+++ trunk/refman-5.0/sql-syntax-data-definition.xml 2009-05-13 20:22:33 UTC (rev 14903)
Changed blocks: 2, Lines Added: 72, Lines Deleted: 9; 3562 bytes
@@ -2700,15 +2700,6 @@
statement.
</para>
- <note>
- <para>
- If you use <literal>IF NOT EXISTS</literal> in a <literal>CREATE
- TABLE ... SELECT</literal> statement, any rows selected by the
- <literal role="stmt">SELECT</literal> part are inserted
- regardless of whether the table already exists.
- </para>
- </note>
-
<para>
MySQL represents each table by an <filename>.frm</filename> table
format (definition) file in the database directory. The storage
@@ -3985,6 +3976,78 @@
</programlisting>
<para>
+ For <literal>CREATE TABLE ... SELECT</literal>, if <literal>IF NOT
+ EXISTS</literal> is given and the table already exists, MySQL
+ handles the statement as follows:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ The table definition given in the <literal>CREATE
+ TABLE</literal> part is ignored. No error occurs, even if the
+ definition does not match that of the existing table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If there is a mismatch between the number of columns in the
+ table and the number of columns produced by the
+ <literal>SELECT</literal> part, the selected values are
+ assigned to the rightmost columns. For example, if the table
+ contains <replaceable>n</replaceable> columns and the
+ <literal>SELECT</literal> produces
+ <replaceable>m</replaceable> columns, where
+ <replaceable>m</replaceable> <
+ <replaceable>n</replaceable>, the selected values are assigned
+ to the <replaceable>m</replaceable> rightmost columns in the
+ table. The initial <replaceable>n</replaceable> −
+ <replaceable>m</replaceable> columns are assigned their
+ default values, either those specified explicitly in the
+ column definition or the implicit column data type default if
+ the definition contains no default.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If strict SQL mode is enabled and any of these initial columns
+ do not have an explicit default value, the statement fails
+ with an error.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The following example illustrates <literal>IF NOT EXISTS</literal>
+ handling:
+ </para>
+
+<programlisting>
+<!--
+mysql> DROP TABLE IF EXISTS t1;
+Query OK, 0 rows affected (0.00 sec)
+-->
+mysql> <userinput>CREATE TABLE t1 (i1 INT DEFAULT 0, i2 INT, i3 INT, i4 INT);</userinput>
+Query OK, 0 rows affected (0.05 sec)
+
+mysql> <userinput>CREATE TABLE IF NOT EXISTS t1 (c1 CHAR(10)) SELECT 1, 2;</userinput>
+Query OK, 1 row affected, 1 warning (0.01 sec)
+Records: 1 Duplicates: 0 Warnings: 0
+
+mysql> <userinput>SELECT * FROM t1;</userinput>
++------+------+------+------+
+| i1 | i2 | i3 | i4 |
++------+------+------+------+
+| 0 | NULL | 1 | 2 |
++------+------+------+------+
+1 row in set (0.00 sec)
+</programlisting>
+
+ <para>
Use <literal>LIKE</literal> to create an empty table based on the
definition of another table, including any column attributes and
indexes defined in the original table:
Modified: trunk/refman-5.1/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-5.1/sql-syntax-data-definition.xml 2009-05-13 18:49:38 UTC (rev 14902)
+++ trunk/refman-5.1/sql-syntax-data-definition.xml 2009-05-13 20:22:33 UTC (rev 14903)
Changed blocks: 2, Lines Added: 72, Lines Deleted: 9; 3562 bytes
@@ -5273,15 +5273,6 @@
statement.
</para>
- <note>
- <para>
- If you use <literal>IF NOT EXISTS</literal> in a <literal>CREATE
- TABLE ... SELECT</literal> statement, any rows selected by the
- <literal role="stmt">SELECT</literal> part are inserted
- regardless of whether the table already exists.
- </para>
- </note>
-
<para>
MySQL represents each table by an <filename>.frm</filename> table
format (definition) file in the database directory. The storage
@@ -7282,6 +7273,78 @@
</programlisting>
<para>
+ For <literal>CREATE TABLE ... SELECT</literal>, if <literal>IF NOT
+ EXISTS</literal> is given and the table already exists, MySQL
+ handles the statement as follows:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ The table definition given in the <literal>CREATE
+ TABLE</literal> part is ignored. No error occurs, even if the
+ definition does not match that of the existing table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If there is a mismatch between the number of columns in the
+ table and the number of columns produced by the
+ <literal>SELECT</literal> part, the selected values are
+ assigned to the rightmost columns. For example, if the table
+ contains <replaceable>n</replaceable> columns and the
+ <literal>SELECT</literal> produces
+ <replaceable>m</replaceable> columns, where
+ <replaceable>m</replaceable> <
+ <replaceable>n</replaceable>, the selected values are assigned
+ to the <replaceable>m</replaceable> rightmost columns in the
+ table. The initial <replaceable>n</replaceable> −
+ <replaceable>m</replaceable> columns are assigned their
+ default values, either those specified explicitly in the
+ column definition or the implicit column data type default if
+ the definition contains no default.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If strict SQL mode is enabled and any of these initial columns
+ do not have an explicit default value, the statement fails
+ with an error.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The following example illustrates <literal>IF NOT EXISTS</literal>
+ handling:
+ </para>
+
+<programlisting>
+<!--
+mysql> DROP TABLE IF EXISTS t1;
+Query OK, 0 rows affected (0.00 sec)
+-->
+mysql> <userinput>CREATE TABLE t1 (i1 INT DEFAULT 0, i2 INT, i3 INT, i4 INT);</userinput>
+Query OK, 0 rows affected (0.05 sec)
+
+mysql> <userinput>CREATE TABLE IF NOT EXISTS t1 (c1 CHAR(10)) SELECT 1, 2;</userinput>
+Query OK, 1 row affected, 1 warning (0.01 sec)
+Records: 1 Duplicates: 0 Warnings: 0
+
+mysql> <userinput>SELECT * FROM t1;</userinput>
++------+------+------+------+
+| i1 | i2 | i3 | i4 |
++------+------+------+------+
+| 0 | NULL | 1 | 2 |
++------+------+------+------+
+1 row in set (0.00 sec)
+</programlisting>
+
+ <para>
Use <literal>LIKE</literal> to create an empty table based on the
definition of another table, including any column attributes and
indexes defined in the original table:
Modified: trunk/refman-5.4/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-5.4/sql-syntax-data-definition.xml 2009-05-13 18:49:38 UTC (rev 14902)
+++ trunk/refman-5.4/sql-syntax-data-definition.xml 2009-05-13 20:22:33 UTC (rev 14903)
Changed blocks: 2, Lines Added: 72, Lines Deleted: 9; 3562 bytes
@@ -4762,15 +4762,6 @@
statement.
</para>
- <note>
- <para>
- If you use <literal>IF NOT EXISTS</literal> in a <literal>CREATE
- TABLE ... SELECT</literal> statement, any rows selected by the
- <literal role="stmt">SELECT</literal> part are inserted
- regardless of whether the table already exists.
- </para>
- </note>
-
<para>
MySQL represents each table by an <filename>.frm</filename> table
format (definition) file in the database directory. The storage
@@ -6762,6 +6753,78 @@
</programlisting>
<para>
+ For <literal>CREATE TABLE ... SELECT</literal>, if <literal>IF NOT
+ EXISTS</literal> is given and the table already exists, MySQL
+ handles the statement as follows:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ The table definition given in the <literal>CREATE
+ TABLE</literal> part is ignored. No error occurs, even if the
+ definition does not match that of the existing table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If there is a mismatch between the number of columns in the
+ table and the number of columns produced by the
+ <literal>SELECT</literal> part, the selected values are
+ assigned to the rightmost columns. For example, if the table
+ contains <replaceable>n</replaceable> columns and the
+ <literal>SELECT</literal> produces
+ <replaceable>m</replaceable> columns, where
+ <replaceable>m</replaceable> <
+ <replaceable>n</replaceable>, the selected values are assigned
+ to the <replaceable>m</replaceable> rightmost columns in the
+ table. The initial <replaceable>n</replaceable> −
+ <replaceable>m</replaceable> columns are assigned their
+ default values, either those specified explicitly in the
+ column definition or the implicit column data type default if
+ the definition contains no default.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If strict SQL mode is enabled and any of these initial columns
+ do not have an explicit default value, the statement fails
+ with an error.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The following example illustrates <literal>IF NOT EXISTS</literal>
+ handling:
+ </para>
+
+<programlisting>
+<!--
+mysql> DROP TABLE IF EXISTS t1;
+Query OK, 0 rows affected (0.00 sec)
+-->
+mysql> <userinput>CREATE TABLE t1 (i1 INT DEFAULT 0, i2 INT, i3 INT, i4 INT);</userinput>
+Query OK, 0 rows affected (0.05 sec)
+
+mysql> <userinput>CREATE TABLE IF NOT EXISTS t1 (c1 CHAR(10)) SELECT 1, 2;</userinput>
+Query OK, 1 row affected, 1 warning (0.01 sec)
+Records: 1 Duplicates: 0 Warnings: 0
+
+mysql> <userinput>SELECT * FROM t1;</userinput>
++------+------+------+------+
+| i1 | i2 | i3 | i4 |
++------+------+------+------+
+| 0 | NULL | 1 | 2 |
++------+------+------+------+
+1 row in set (0.00 sec)
+</programlisting>
+
+ <para>
Use <literal>LIKE</literal> to create an empty table based on the
definition of another table, including any column attributes and
indexes defined in the original table:
Modified: trunk/refman-6.0/sql-syntax-data-definition.xml
===================================================================
--- trunk/refman-6.0/sql-syntax-data-definition.xml 2009-05-13 18:49:38 UTC (rev 14902)
+++ trunk/refman-6.0/sql-syntax-data-definition.xml 2009-05-13 20:22:33 UTC (rev 14903)
Changed blocks: 2, Lines Added: 72, Lines Deleted: 9; 3562 bytes
@@ -4215,15 +4215,6 @@
statement.
</para>
- <note>
- <para>
- If you use <literal>IF NOT EXISTS</literal> in a <literal>CREATE
- TABLE ... SELECT</literal> statement, any rows selected by the
- <literal role="stmt">SELECT</literal> part are inserted
- regardless of whether the table already exists.
- </para>
- </note>
-
<para>
MySQL represents each table by an <filename>.frm</filename> table
format (definition) file in the database directory. The storage
@@ -6093,6 +6084,78 @@
</programlisting>
<para>
+ For <literal>CREATE TABLE ... SELECT</literal>, if <literal>IF NOT
+ EXISTS</literal> is given and the table already exists, MySQL
+ handles the statement as follows:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ The table definition given in the <literal>CREATE
+ TABLE</literal> part is ignored. No error occurs, even if the
+ definition does not match that of the existing table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If there is a mismatch between the number of columns in the
+ table and the number of columns produced by the
+ <literal>SELECT</literal> part, the selected values are
+ assigned to the rightmost columns. For example, if the table
+ contains <replaceable>n</replaceable> columns and the
+ <literal>SELECT</literal> produces
+ <replaceable>m</replaceable> columns, where
+ <replaceable>m</replaceable> <
+ <replaceable>n</replaceable>, the selected values are assigned
+ to the <replaceable>m</replaceable> rightmost columns in the
+ table. The initial <replaceable>n</replaceable> −
+ <replaceable>m</replaceable> columns are assigned their
+ default values, either those specified explicitly in the
+ column definition or the implicit column data type default if
+ the definition contains no default.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If strict SQL mode is enabled and any of these initial columns
+ do not have an explicit default value, the statement fails
+ with an error.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The following example illustrates <literal>IF NOT EXISTS</literal>
+ handling:
+ </para>
+
+<programlisting>
+<!--
+mysql> DROP TABLE IF EXISTS t1;
+Query OK, 0 rows affected (0.00 sec)
+-->
+mysql> <userinput>CREATE TABLE t1 (i1 INT DEFAULT 0, i2 INT, i3 INT, i4 INT);</userinput>
+Query OK, 0 rows affected (0.05 sec)
+
+mysql> <userinput>CREATE TABLE IF NOT EXISTS t1 (c1 CHAR(10)) SELECT 1, 2;</userinput>
+Query OK, 1 row affected, 1 warning (0.01 sec)
+Records: 1 Duplicates: 0 Warnings: 0
+
+mysql> <userinput>SELECT * FROM t1;</userinput>
++------+------+------+------+
+| i1 | i2 | i3 | i4 |
++------+------+------+------+
+| 0 | NULL | 1 | 2 |
++------+------+------+------+
+1 row in set (0.00 sec)
+</programlisting>
+
+ <para>
Use <literal>LIKE</literal> to create an empty table based on the
definition of another table, including any column attributes and
indexes defined in the original table:
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r14903 - in trunk: . refman-4.1 refman-5.0 refman-5.1 refman-5.4 refman-6.0 | paul.dubois | 13 May |
