MySQL Bugs: #21380: DEFAULT definition not always transfered by CREATE TABLE/SELECT to the new table

Bug #21380	DEFAULT definition not always transfered by CREATE TABLE/SELECT to the new table
Submitted:	1 Aug 2006 8:02	Modified:	5 Mar 2008 16:23
Reporter:	Paul McCullagh (Basic Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0.54, 4.1.22-BK, 4.1	OS:	Linux (Linux)
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any
Tags:	create, DEFAULT, qc, SELECT, table

Description:
If you create a table using the CREATE TABLE/SELECT syntax a DEFAULT defined on the source table is not always created on the new table.

I have an example below. If a SELECT between the two CREATE TABLEs is done, then the bug repeats. If no SELECT is done then the CREATE TABLE/SELECT works correctly?!

How to repeat:
This does not work (no default on t2):

drop table if exists t1, t2;
CREATE TABLE t1 (
  id int,
  string char(10) default "hello"
);

select * from t1;

create table t2 select * from t1;
show create table t2;

But this works (default created correctly):

drop table if exists t1, t2;
CREATE TABLE t1 (
  id int,
  string char(10) default "hello"
);

create table t2 select * from t1;
show create table t2;

Thank you for a bug report. Verified just as described with latest 4.1.22-BK on Linux.

Note, that according to the SQL standard, the first result is correct - t2 should have no DEFAULT clauses

This behavior is not repeatable on 5.0.42. In both cases, t2 has the default clause.

mysql 5.0.42-enterprise-gpl-log (root) [test]> drop table if exists t1, t2;
Query OK, 0 rows affected (0.00 sec)

mysql 5.0.42-enterprise-gpl-log (root) [test]> CREATE TABLE t1 (
    ->   id int,
    ->   string char(10) default "hello"
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql 5.0.42-enterprise-gpl-log (root) [test]> select * from t1;
+------+------------+
| id   | string     |
+------+------------+
|    0 |            | 
+------+------------+
1 row in set (0.00 sec)

mysql 5.0.42-enterprise-gpl-log (root) [test]> create table t2 select * from t1;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql 5.0.42-enterprise-gpl-log (root) [test]> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) default NULL,
  `string` char(10) default 'hello'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql 5.0.42-enterprise-gpl-log (root) [test]> drop table if exists t1, t2;
Query OK, 0 rows affected (0.00 sec)

mysql 5.0.42-enterprise-gpl-log (root) [test]> CREATE TABLE t1 (
    ->   id int,
    ->   string char(10) default "hello"
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql 5.0.42-enterprise-gpl-log (root) [test]> create table t2 select * from t1;
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql 5.0.42-enterprise-gpl-log (root) [test]> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) default NULL,
  `string` char(10) default 'hello'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

I don't know whether Sergei Golubchik said it's a "bug",
but it's certainly non-standard to copy a default clause.
The standard says that for
 CREATE TABLE t1 (column_a SMALLINT NOT NULL DEFAULT 5);
 CREATE TABLE t2 AS SELECT column_a FROM t1;
the DBMS should take only three things:
* the name
* the declared type
* either 'NOT NULL' or a zero-length string.
and then transform into a table element list
 CREATE TABLE t2 (column_a SMALLINT);
absolutely definitely not the default clause.

The Oracle 11g manual is more explicit:
"... column default values are not carried
over to the new table"
http://download-uk.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_7002.htm#i209...

So copying the default is a mistake. But is it a bug?

Well, it's also undocumented, as far as I can tell
from the MySQL Reference Manual's description
of CREATE TABLE
http://dev.mysql.com/doc/refman/5.1/en/create-table.html

If somebody says that the behaviour was intentional,
or that the behaviour is documented somewhere,
or that the "customer" (see "Affects customer" flag on this bug) cares,
then I suppose we have to make a worklog to fix this mistake
in a future version.
Otherwise, it's a bug.

A column has also the following attributes. Which of them should be carried
over a new table in 'CREATE TABLE ... AS SELECT * FROM ...' operation?
  - character set / collation (+);
  - null / not null (+);
  - comment (+);
  - auto_increment flag (-);
  - unique key flag (-);
  - primary key flag (-);
  - column_format (ndb only) (?);
  - storage (ndb only) (?).

The attributes marked (+) are carried over; the ones marked (-) are not
(as of 5.1.24). I'm not sure about NDB-only attributes.

Another thing -- unlike Oracle we have LIKE clause
(CREATE TABLE ... LIKE ...), which I guess, is intended to create a table
with exactly the same structure. I.e. all column/table attributes should
be carried over in this case.

Hi Alik,

I am only noting here what is "standard".

> A column has also the following attributes. Which of them should be carried
> over a new table in 'CREATE TABLE ... AS SELECT * FROM ...' operation?
>   - character set / collation (+);

That's copied, a data type declaration includes CHARACTER SET
and COLLATION clauses.

>   - null / not null (+);

That's copied.

>   - comment (+);

That's not mentioned in the standard.

>   - auto_increment flag (-);

That's not mentioned in the standard. The closest thing
would be an "identity column"; the related clauses
would not be copied.

>   - unique key flag (-);

That's not copied.

>   - primary key flag (-);

That's not copied.

>   - column_format (ndb only) (?);

That's not mentioned in the standard.

>   - storage (ndb only) (?).

That's not mentioned in the standard.

> The attributes marked (+) are carried over; the ones marked (-) are not
> (as of 5.1.24). I'm not sure about NDB-only attributes.

Neither am I. It seems to me that MySQL's choices are correct.

> Another thing -- unlike Oracle we have LIKE clause
> (CREATE TABLE ... LIKE ...), which I guess, is intended to create a table
> with exactly the same structure. I.e. all column/table attributes should
> be carried over in this case.

The standard does have CREATE TABLE ... (LIKE ...).
It has an optional "INCLUDING IDENTITY" clause, that is,
you can specify that you want to copy the thing that is
vaguely similar to MySQL's auto_increment.
It has an optional "INCLUDING DEFAULTS" clause, that is,
you can specify that you want to copy the default clause.

Disagree with the workaround evaluation.
One can always specify the table structure explicitly in CREATE TABLE ... SELECT statement, in this case no column options of the source table are transferred to the destination table.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/41765

ChangeSet@1.2520, 2008-02-06 14:55:19+03:00, anozdrin@quad. +2 -0
  Add a test case for Bug#21380: DEFAULT definition not always
  transfered by CREATE TABLE/SELECT to the new table.

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

Documented in the 5.1.23-ndb-6.2.14, 5.1.24, and 6.0.5 changelogs as follows:

        CREATE ... SELECT did not always set DEFAULT column values in the new
        table.

Also documented in 5.1.23-ndb-6.3.11 changelog.