List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 28 2002 4:07am
Subject:Re: [BUG] CREATE TABLE ... SELECT ...
View as plain text  
At 22:36 -0500 1/27/02, Terra wrote:
>Greetings,
>
>I believe I have stumbled upon a subtle behavioral bug in mysql 3.23.47...
>
>>  CREATE TABLE testing SELECT * FROM template WHERE 1=0;
>
>I do this to create quick tables based on a master template table... 
>This is mostly done when I'm working with table design structures by 
>hand, and not programmatic methods...
>
>>  desc template;
>+-----------+-----------------------+------+-----+---------------------+----------------+
>| Field     | Type                  | Null | Key | Default 
>| Extra          |
>+-----------+-----------------------+------+-----+---------------------+----------------+
>| id        | int(11) unsigned      |      | PRI | NULL 
>| auto_increment |
>| timestamp | datetime              |      | MUL | 0000-00-00 
>00:00:00 |                |
>| server    | tinyint(3) unsigned   |      | MUL | 0 
>|                |
>| one_min   | smallint(5) unsigned  |      |     | 0 
>|                |
>| ttr       | tinyint(3) unsigned   |      |     | 0 
>|                |
>| status    | smallint(5) unsigned  |      |     | 0 
>|                |
>| addr      | int(10) unsigned      |      | MUL | 0 
>|                |
>| xdom      | int(11) unsigned      |      | MUL | 0 
>|                |
>| chain     | int(10) unsigned      |      | MUL | 0 
>|                |
>| sent      | mediumint(8) unsigned |      |     | 0 
>|                |
>| cmd       | int(10) unsigned      |      | MUL | 0 
>|                |
>| ssi       | int(10) unsigned      |      | MUL | 0 
>|                |
>+-----------+-----------------------+------+-----+---------------------+----------------+
>
>
>>  desc testing
>+-----------+-----------------------+------+-----+---------------------+-------+
>| Field     | Type                  | Null | Key | Default 
>| Extra |
>+-----------+-----------------------+------+-----+---------------------+-------+
>| id        | int(11) unsigned      |      |     | 0 
>|       |
>| timestamp | datetime              |      |     | 0000-00-00 
>00:00:00 |       |
>| server    | tinyint(3) unsigned   |      |     | 0 
>|       |
>| one_min   | smallint(5) unsigned  |      |     | 0 
>|       |
>| ttr       | tinyint(3) unsigned   |      |     | 0 
>|       |
>| status    | smallint(5) unsigned  |      |     | 0 
>|       |
>| addr      | int(10) unsigned      |      |     | 0 
>|       |
>| xdom      | int(11) unsigned      |      |     | 0 
>|       |
>| chain     | int(10) unsigned      |      |     | 0 
>|       |
>| sent      | mediumint(8) unsigned |      |     | 0 
>|       |
>| cmd       | int(10) unsigned      |      |     | 0 
>|       |
>| ssi       | int(10) unsigned      |      |     | 0 
>|       |
>+-----------+-----------------------+------+-----+---------------------+-------+
>
>
>*template*
>| id        | int(11) unsigned      |      | PRI | NULL 
>| auto_increment |
>
>*testing*
>| id        | int(11) unsigned      |      |     | 0 
>|       |
>
>
>The 'auto_increment' and 'NULL' is not being carried over, as it 
>would seem intuitive that it should...
>
>Can anyone confirm if this is a bug or not...

It's not a bug.  You're creating a table based on a result set.  Result
sets, unlike tables, have no notion of AUTO_INCREMENT.  And NULL or NOT NULL
cannot be determined on the basis of an empty result set.

You might find it more useful to use the output of SHOW CREATE TABLE
instead, which will include those bits of information, and also the
index structure as well.

>
>Thank you...
>
>--
>Terra
>sysAdmin
>FutureQuest, Inc.

Thread
[BUG] CREATE TABLE ... SELECT ...Terra28 Jan
  • Re: [BUG] CREATE TABLE ... SELECT ...Paul DuBois28 Jan