Bug #3721 create table .. select <constant> ... gives wrong table content
Submitted: 12 May 2004 0:12 Modified: 14 May 2004 20:23
Reporter: Matthias Leich Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0 4.1 4.0 OS:
Assigned to: CPU Architecture:Any

[12 May 2004 0:12] Matthias Leich
Description:
I assume
   CREATE TABLE <tablename> ( <column definitions etc.> ) <select statement>
is a bundling of
   CREATE TABLE <tablename> ( <column definitions etc.> )result columns
   INSERT into <tablename> <select statement> .

If that opinion is correct, MySQL provides a wrong table content if the
select statement contains a constant without "regular" columnname between the 
result columns. The column value is NULL.  

Some hypothesis:
- The problem is independent of the column type (I tested INT and CHAR)
- The physical properties of the constant or their conversion (if necessary)
  are not important (example with CAST function)
- A mix of column and constant within the result column list does not alter 
  the problem (some of the last statements)
- The lack of a "regular" column name within the list of result columns
  is the reason why the server produces a wrong result. Please have a 
  look on the last CREATE TABLE ... AS SELECT ... 
  create table t2 ( a int ) select 1 as a ; --> result is OK
  create table t2 ( a int ) select 1 ;      --> Result is WRONG
  
MySQL 4.0, 4.1 and 5.0 give the same wrong results during create ... select .
MySQL 4.0 shows two additional bugs (syntax errors) (I will write a second bug report for that).

My environment
   Linux, SuSE 9.0 Prof.
   MySQL 4.0  source distribution, last bk pull ~ 1.5.2004
   MySQL 4.1  source distribution, last bk pull ~ 5.5.2004
   MySQL 5.0  source distribution, last bk pull   11.5.2004
    

How to repeat:
Please use my attached files.
[12 May 2004 0:14] Matthias Leich
test case for mysql/mysqltest

Attachment: ml_err1.test (application/octet-stream, text), 1.73 KiB.

[12 May 2004 0:16] Matthias Leich
my (wrong) result with version 5.0

Attachment: ml_err1.result (application/octet-stream, text), 1.09 KiB.

[12 May 2004 0:17] Matthias Leich
the results I expect

Attachment: ml_err1.result.expected (application/octet-stream, text), 1.08 KiB.

[14 May 2004 20:23] Alexander Keremidarski
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

mysql> create table t2 ( a int ) select 1 ;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
mysql> show create table t2;
+-------+---------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                     |
+-------+---------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `a` int(11) default NULL,
  `1` bigint(1) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> select * from t2;
+------+---+
| a    | 1 |
+------+---+
| NULL | 1 |
+------+---+
1 row in set (0.00 sec)

This exactly the same as an example in the manual at:
http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html

mysql> CREATE TABLE bar (m INT) SELECT n FROM foo;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM bar;
+------+---+
| m    | n |
+------+---+
| NULL | 1 |
+------+---+
1 row in set (0.00 sec)

For each row in table foo, a row is inserted in bar with the values from foo and default values for the new columns.