| 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: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0 4.1 4.0 | OS: | |
| Assigned to: | CPU Architecture: | Any | |
[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.
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.