Bug #300 VARCHAR columns are converted to FIXED CHAR when using temporary
Submitted: 16 Apr 2003 15:41 Modified: 16 May 2003 2:58
Reporter: Benoit St-Jean Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.4-beta-max-nt OS:Windows (Windows 2000 SP1)
Assigned to: CPU Architecture:Any

[16 Apr 2003 15:41] Benoit St-Jean
Description:
Whenever a query uses a temporary table (you can tell by seeing "using temporary" in the EXPLAIN), VARCHAR columns are returned as FIXED CHAR and they are padded with nulls...  

Depending on the "access plan" MySQL generates, the results are different for the same data! This has the effect that some ODBC mapping products crash.  This behavior can be reproduced with MyISAM and InnoDb tables.  We haven't tested with other type though.  We have found and reproduced this problem with ODBC and don't know how MySQL behaves when using it's API.

Other infos:

-MySQL ODBC 3.51 Driver
-Tested all ODBC options one by one and it NEVER affects the behavior
-This problem is on MySQL side since the data is wrong at the moment it is returned by ODBC, right after the API call.

How to repeat:
============== BEGIN SCRIPT ==============
DROP TABLE IF EXISTS TEST_INNODB;
CREATE TABLE TEST_INNODB (A INT NOT NULL, B VARCHAR(20), C VARCHAR(10), PRIMARY KEY(A)) TYPE=INNODB;
CREATE UNIQUE INDEX UNIQUE_INDEX ON TEST_INNODB (B ASC);

INSERT INTO TEST_INNODB VALUES  (1, 'A', 'AAA');
INSERT INTO TEST_INNODB VALUES  (2, 'B', 'AAA');
INSERT INTO TEST_INNODB VALUES  (3, 'C', 'AAA');
INSERT INTO TEST_INNODB VALUES  (4, 'D', 'AAA');
INSERT INTO TEST_INNODB VALUES  (5, 'E', 'AAA');

/* Q1 - USING FILESORT */
EXPLAIN SELECT * FROM TEST_INNODB ORDER BY A, B;

/* Q2 - USING INDEX */
EXPLAIN SELECT B FROM TEST_INNODB;

/* Q3 - USING TEMP */
EXPLAIN SELECT * FROM TEST_INNODB GROUP BY A ORDER BY B;

/* Q4 - USING TEMPORARY AND FILESORT */
EXPLAIN SELECT * FROM TEST_INNODB GROUP BY C;

DROP TABLE IF EXISTS TEST_MYISAM;
CREATE TABLE TEST_MYISAM (A INT NOT NULL, B VARCHAR(20), C VARCHAR(10), PRIMARY KEY(A)) TYPE=MYISAM;
CREATE UNIQUE INDEX UNIQUE_INDEX ON TEST_MYISAM (B ASC);

INSERT INTO TEST_MYISAM VALUES  (1, 'A', 'AAA');
INSERT INTO TEST_MYISAM VALUES  (2, 'B', 'AAA');
INSERT INTO TEST_MYISAM VALUES  (3, 'C', 'AAA');
INSERT INTO TEST_MYISAM VALUES  (4, 'D', 'AAA');
INSERT INTO TEST_MYISAM VALUES  (5, 'E', 'AAA');

/* Q5 - USING FILESORT */
EXPLAIN SELECT * FROM TEST_MYISAM ORDER BY A, B;

/* Q6 - USING INDEX */
EXPLAIN SELECT B FROM TEST_MYISAM;

/* Q7 - USING TEMP */
EXPLAIN SELECT * FROM TEST_MYISAM GROUP BY A ORDER BY B;

/* Q8 - USING TEMPORARY AND FILESORT */
EXPLAIN SELECT * FROM TEST_MYISAM GROUP BY C;

============== END SCRIPT ==============

You'll notice that queries Q1, Q2, Q5, Q6 all return column B as it was first created, i.e. a VARCHAR.

All other queries, whenever they are "using temporary", return column B as a FIXED CHAR padded with nulls even though that column is a VARCHAR field.

Suggested fix:
N/A
[16 Apr 2003 23:53] Benoit St-Jean
On a side note, you will also notice that the same statement (Q3 and Q7) yields different "access plans" on MyISAM and InnoDb :  

Q3 uses temporary and file sort
Q7 uses temporary only.

This detail is not related in any way to this bug but I tought you would be interested.
[17 Apr 2003 4:16] MySQL Verification Team
This is a known issue and it will remain as is.

Tables created from SELECT do not represent image from the
original tables as they can query result types only.

That is why CREATE TABLE FROM SELECT command allows columns to  be specified.

As per different behaviour among storage engines, that was fixed in recent versions.
[17 Apr 2003 22:13] Benoit St-Jean
"Tables created from SELECT do not represent image from the
original tables as they can query result types only."

Then how come some queries get it right?  Shouldn't it be always right?  How can you get the datatype of almost every column right except for those VARCHARS ?

Just curious.
[16 May 2003 2:58] Michael Widenius
A "last" follow up on this thread.

In MySQL (before 5.0) the only difference between CHAR and VARCHAR is that VARCHAR is stored a bit more efficently on disk;  From the users end there is no difference in usage between CHAR and VARCHAR.

To put it another way: a VARCHAR is a CHAR that is stored in tablw with dynamic length row format.

In MySQL 5.0 (or possible in 4.1.2) we will introduce a true VARCHAR type and then we will not anymore automaticly convert between CHAR/VARCHAR depending on the row format.