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: | |
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
[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.