Bug #9723 mixed char/varchar columns forces char columns to become varchar type

• Submitted: 7 Apr 2005 15:22
• Modified: 25 Feb 2010 3:08
• Reporter: Christoph Koeppen
• Status: No Feedback
• Category: MySQL Server: Command-line Clients
• Severity: S2 (Serious)
• Version: Ver 14.7 Distrib 4.1.10a, for Win95/Win9
• OS: Windows (XP64(beta))
• CPU Architecture: Any

[7 Apr 2005 15:22] Christoph Koeppen

Description:
Creating tables with at least two columns, one of type CHAR, one of type VARCHAR, produces different table instances depending on the size of the
CHAR typed column (and apparently independent of the VARCHAR size):
- CHAR(1), VARCHAR(x)--->CHAR(1), VARCHAR(x)
- CHAR(2), VARCHAR(x)--->CHAR(2), VARCHAR(x)
- CHAR(3), VARCHAR(x)--->CHAR(3), VARCHAR(x)
- CHAR(4), VARCHAR(x)--->VARCHAR(4), VARCHAR(x)
Tables containing more columns with the above restiction behave similarly.

How to repeat:
CREATE TABLE mixed_char_varchar_bug1( c_char CHAR( 1), c_varchar VARCHAR(10) );
CREATE TABLE mixed_char_varchar_bug2( c_char CHAR( 2), c_varchar VARCHAR(10) );
CREATE TABLE mixed_char_varchar_bug3( c_char CHAR( 3), c_varchar VARCHAR(10) );
CREATE TABLE mixed_char_varchar_bug4( c_char CHAR( 4), c_varchar VARCHAR(10) );
EXPLAIN  mixed_char_varchar_bug1;
EXPLAIN  mixed_char_varchar_bug2;
EXPLAIN  mixed_char_varchar_bug3;
EXPLAIN  mixed_char_varchar_bug4;

Suggested fix:
no clue

[7 Apr 2005 17:18] Jorge del Conde

Hi,

This behaviour is explained here:

http://dev.mysql.com/doc/mysql/en/silent-column-changes.html

[25 Jan 2010 3:08] Roel Van de Paar

This cannot be repeated on MySQL 5.1.42:

---------
mysql> EXPLAIN  mixed_char_varchar_bug1;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| c_char    | char(1)     | YES  |     | NULL    |       |
| c_varchar | varchar(10) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mysql> EXPLAIN  mixed_char_varchar_bug2;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| c_char    | char(2)     | YES  |     | NULL    |       |
| c_varchar | varchar(10) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> EXPLAIN  mixed_char_varchar_bug3;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| c_char    | char(3)     | YES  |     | NULL    |       |
| c_varchar | varchar(10) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> EXPLAIN  mixed_char_varchar_bug4;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| c_char    | char(4)     | YES  |     | NULL    |       |
| c_varchar | varchar(10) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
---------

Could you verify this is still happening for you, and if so, can you provide more details?

[26 Feb 2010 0:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".