Bug #709 LEFT and CREATE TABLE ... SELECT

• Submitted: 24 Jun 2003 5:12
• Modified: 3 Jul 2003 0:43
• Reporter: Per Steinar Iversen
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0.13
• OS: Linux (RedHat 7.3, fully updated)
• Assigned to: Alexey Botchkov
• CPU Architecture: Any

[24 Jun 2003 5:12] Per Steinar Iversen

Description:
A table "test1" contains ethernet mac-addresses:

+-------+----------+------+-----+-------------------+-------+
| Field | Type     | Null | Key | Default           | Extra |
+-------+----------+------+-----+-------------------+-------+
| mac   | char(17) |      |     | 00:00:00:00:00:00 |       |
+-------+----------+------+-----+-------------------+-------+

Selecting from the commandline client works fine:

mysql> SELECT mac FROM test1 LIMIT 5;
+-------------------+
| mac               |
+-------------------+
| 00:00:74:66:c5:58 |
| 00:00:74:73:4f:f6 |
| 00:00:81:0e:24:bd |
| 00:00:81:19:d0:41 |
| 00:00:85:07:64:f3 |
+-------------------+
5 rows in set (0.00 sec)

Now I want to get the vendor field:

mysql> SELECT LEFT(mac,8) FROM test1 LIMIT 5;
+-------------+
| LEFT(mac,8) |
+-------------+
| 00:00:74    |
| 00:00:74    |
| 00:00:81    |
| 00:00:81    |
| 00:00:85    |
+-------------+

But, if this is to be put into a table using the CREATE TABLE ... SELECT ... syntax then the result is strange:

mysql> CREATE TABLE test2 SELECT LEFT(mac,8) FROM test1 LIMIT 5;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 5

mysql> SELECT * FROM test2;
+-------------+
| LEFT(mac,8) |
+-------------+
|             |
|             |
|             |
|             |
|             |
+-------------+

I have tried a number of variations on this theme, but all fail.

How to repeat:
The bug can be repeated as in the description above.

[24 Jun 2003 18:30] Alexander Keremidarski

mysql> CREATE TABLE test2 SELECT LEFT(mac,8) FROM test1 LIMIT 5;

mysql> SHOW CREATE TABLE test2;

CREATE TABLE `test2` (
  `LEFT(mac,8)` char(0) default NULL
) TYPE=MyISAM 

Column is created as CHAR(0) which obviously can't hold anything but empty string.

[24 Jun 2003 18:30] Alexander Keremidarski

mysql> CREATE TABLE test2 SELECT LEFT(mac,8) FROM test1 LIMIT 5;

mysql> SHOW CREATE TABLE test2;

CREATE TABLE `test2` (
  `LEFT(mac,8)` char(0) default NULL
) TYPE=MyISAM 

Column is created as CHAR(0) which obviously can't hold anything but empty string.

[24 Jun 2003 18:55] Alexander Keremidarski

Tested with 4.0 from BK tree

ChangeSet@1.1536.3.2, 2003-06-24 17:07:43+05:00, hf@deer.(none)
  Test case for bug #666

[3 Jul 2003 0:43] Alexey Botchkov

It seems like it was fixed meanwhile.