Professional Web Applications Themes

Question to the MySQL guru's - MySQL

In attempting to solve a problem posted earlier (see "non-tab delimited output?) I discovered that, in their infinite wisdom, the MySQL developers elected to trim spaces from CHAR data when retrieved from the database. Is this a new interpretation of the ANSI-standard for the CHAR datatype? Most of us use the VARCHAR datatype to achieve this goal - well, at least *I* do. I am baffled as to why you would waste space storing space-padded data, then trimming it when retrieved from the database. This is apparently a doented feature: from: http://dev.mysql.com/doc/refman/5.1/en/char.html "The length of a CHAR column is fixed ...

  1. #1

    Default Question to the MySQL guru's

    In attempting to solve a problem posted earlier (see "non-tab delimited output?)
    I discovered that, in their infinite wisdom, the MySQL developers elected to
    trim spaces from CHAR data when retrieved from the database. Is this a new
    interpretation of the ANSI-standard for the CHAR datatype? Most of us use the
    VARCHAR datatype to achieve this goal - well, at least *I* do.

    I am baffled as to why you would waste space storing space-padded data, then
    trimming it when retrieved from the database. This is apparently a doented
    feature:

    from: http://dev.mysql.com/doc/refman/5.1/en/char.html
    "The length of a CHAR column is fixed to the length that you declare when you
    create the table. The length can be any value from 0 to 255. When CHAR values
    are stored, they are right-padded with spaces to the specified length. When CHAR
    values are retrieved, ****trailing spaces are removed****."

    Does that not defeat the purpose of the CHAR datatype?

    Questions? Comments? Snide Remarks?

    By the way, I was told the set sql_mode=oracle should have produced the desired
    results - they didn't. It was logged as a bug.

    --
    Michael Austin.
    DBA Consultant
    Michael Guest

  2. #2

    Default Re: Question to the MySQL guru's

    I don't feel that it defeats the purpose. You inserted that record
    without the trailing spaces. You could argue you expect to get it back
    the same way. Also it's not necesarrily wasting space, MyISAM performs
    better with equal length records. But just because you want the
    increased performance doesn't mean you want those trailing spaces.

    Dr.Zoidberb Guest

  3. #3

    Default Re: Question to the MySQL guru's

    [ORIGNAL POST TO COMP.DATABASES.MYSQL - A discussion on the proper use and
    definitions of CHAR vs. VARCHAR data-types. ADDING CROSS-POST for discussion
    purposes. I could be wrong in *my* thought-processes, but I don't think so...]

    In attempting to solve a problem posted earlier (see "non-tab delimited output?)
    I discovered that, in their infinite wisdom, the MySQL developers elected to
    trim spaces from CHAR data when retrieved from the database. Is this a new
    interpretation of the ANSI-standard for the CHAR datatype? Most of us use the
    VARCHAR datatype to achieve this goal - well, at least *I* do.

    I am baffled as to why you would waste space storing space-padded data, then
    trimming it when retrieved from the database. This is apparently a doented
    feature:

    from: http://dev.mysql.com/doc/refman/5.1/en/char.html
    "The length of a CHAR column is fixed to the length that you declare when you
    create the table. The length can be any value from 0 to 255. When CHAR values
    are stored, they are right-padded with spaces to the specified length. When CHAR
    values are retrieved, ****trailing spaces are removed****."

    Does that not defeat the purpose of the CHAR datatype?

    Questions? Comments? Snide Remarks?
    ================================================== =============================
    Dr.Zoidberb wrote:
     

    Are you sure about that? You cannot assume that, can you?
     

    If I insert the data WITH spaces - I certainly expect to retrieve it WITH
    spaces. So, in your mind when does 'ABC' = 'ABC '? Do you not expect to
    retrieve the data based on the datatype not the data? Isn't that the purpose of
    using a datatype? Using that logic, that makes the use of datatypes moot. Just
    store everything as CHAR (or binary) data and let the app handle "fixing" it.
    Seems counter-productive/intuitive to me.

    This also makes the CAST function of little or no value, mainly because now it
    does not transform the data as expected, and a waste of the developers
    programming time to implement it. This function allows the app developer to
    easily change the look of the data based on datatype.

    Would you not agree that now there is NO WAY to return fixed-length column data
    to those applications and extracts without a lot of post-processing from the
    application perspective? Again, makes the use of datatypes moot.
     

    Again, you use datatypes not just to store the data, but to return that data in
    a specific format. You expect to see the RESULTS based on the column datatype
    even if the original data was of a different precision or character length.

    While this example works, it is definately not intuitive and is flat wrong.
    Again, in what world does 'ABC' = 'ABC '.

    mysql> describe b;
    +-------+----------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------+----------+------+-----+---------+-------+
    | b | char(10) | YES | | NULL | |
    +-------+----------+------+-----+---------+-------+
    1 row in set (1.99 sec)

    mysql> insert into b values ('DEF ');
    mysql> insert into b values ('ABC');
    mysql> select '~'||b||'~' from b;
    +-------------+
    | '~'||b||'~' |
    +-------------+
    | ~DEF~ |
    | ~ABC~ |
    +-------------+
    2 rows in set (0.03 sec)

    If I am expecting to see the data with the trialing spaces included, I will get
    incorrect results - because in this example, I NEED to retrieve the data
    INCLUDING the trailing spaces and I do not. This is just flat WRONG!!!!

    Now, of course, MySQL has a REAL problem in that they cannot fix this without
    adversely affecting all of those unsuspecting programmers that used this CHAR
    datatype because they didn't know any better and yes, the products of "higher
    education".

    --
    Michael Austin.
    DBA Consultant
    Michael Guest

  4. #4

    Default Re: Question to the MySQL guru's

    Michael Austin wrote: 

    Hi, Michael,

    Yes, I agree the data should be returned with trailing spaces. That's
    the way the SQL spec was written, the last time I looked, and it's the
    way other databases handle it.

    I normally don't use CHAR columns (generally it's VARCHAR), but I'm with
    you. If I'm using CHAR, I would expect trailing blanks to be returned.

    I see your post in the bugs database on mysql.com, and agree with it
    completely. I also disagree with their response. Just because it's
    doented that way does not make it right.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: Question to the MySQL guru's

    > Dr.Zoidberb wrote: 
    >
    > Are you sure about that? You cannot assume that, can you?

    >[/ref]

    I'm not saying it's a good assumption only that you can make an
    argument for it.
     

    If you insert with your own trailing spaces then yes obviously you'll
    expect those trailing spaces when you retrieve it. 'ABC' = 'ABC '
    When a null character is considered the same as a space. I'm not saying
    this is always the case or even usually the case. Simply that it CAN be
    the case depending on the application. It is certainly counter
    intuitive.
     

    I would not entirely agree because you can use the TEXT datatype for a
    fixed length field where trailing spaces are preserved. But I believe
    the limitation is on the length of the fixed length field.
     
    >
    > Again, you use datatypes not just to store the data, but to return that data in
    > a specific format. You expect to see the RESULTS based on the column datatype
    > even if the original data was of a different precision or character length.
    >
    > While this example works, it is definately not intuitive and is flat wrong.
    > Again, in what world does 'ABC' = 'ABC '.
    >
    > mysql> describe b;
    > +-------+----------+------+-----+---------+-------+
    > | Field | Type | Null | Key | Default | Extra |
    > +-------+----------+------+-----+---------+-------+
    > | b | char(10) | YES | | NULL | |
    > +-------+----------+------+-----+---------+-------+
    > 1 row in set (1.99 sec)
    >
    > mysql> insert into b values ('DEF ');
    > mysql> insert into b values ('ABC');
    > mysql> select '~'||b||'~' from b;
    > +-------------+
    > | '~'||b||'~' |
    > +-------------+
    > | ~DEF~ |
    > | ~ABC~ |
    > +-------------+
    > 2 rows in set (0.03 sec)
    >
    > If I am expecting to see the data with the trialing spaces included, I will get
    > incorrect results - because in this example, I NEED to retrieve the data
    > INCLUDING the trailing spaces and I do not. This is just flat WRONG!!!!
    >[/ref]

    No argument here.
     


    I say go ahead and adversely affect them :-)

    Dr.Zoidberb Guest

  6. #6

    Default Re: Question to the MySQL guru's

    Dr.Zoidberb wrote:
     
    >>
    >>Are you sure about that? You cannot assume that, can you?
    >>
    >> 
    >>[/ref]
    >
    > I'm not saying it's a good assumption only that you can make an
    > argument for it.[/ref]

    In programming (applications, database engines, OS's) you cannot assume
    anything. That is why there are standards that define the basic operations.
     
    >
    >
    > If you insert with your own trailing spaces then yes obviously you'll
    > expect those trailing spaces when you retrieve it. 'ABC' = 'ABC '
    > When a null character is considered the same as a space. I'm not saying[/ref]

    NULL is NEVER, EVER, EVER equal to or considered as a SPACE character. Stating
    that it can be "considered the same as a space" is evidence that someone does
    not have a grasp of the concept of NULL or null character. The null character
    is %X00. The space character is %X20. And there is a big difference in how they
    are evaluated.

    <snip> 

    Why would you/they invent a new datatype that is already provided in the ANSI
    standard - the CHAR datatype?
     
    >>
    >>Again, you use datatypes not just to store the data, but to return that data in
    >>a specific format. You expect to see the RESULTS based on the column datatype
    >>even if the original data was of a different precision or character length.
    >>
    >>While this example works, it is definately not intuitive and is flat wrong.
    >>Again, in what world does 'ABC' = 'ABC '.
    >>
    >>mysql> describe b;
    >>+-------+----------+------+-----+---------+-------+
    >>| Field | Type | Null | Key | Default | Extra |
    >>+-------+----------+------+-----+---------+-------+
    >>| b | char(10) | YES | | NULL | |
    >>+-------+----------+------+-----+---------+-------+
    >>1 row in set (1.99 sec)
    >>
    >>mysql> insert into b values ('DEF ');
    >>mysql> insert into b values ('ABC');
    >>mysql> select '~'||b||'~' from b;
    >>+-------------+
    >>| '~'||b||'~' |
    >>+-------------+
    >>| ~DEF~ |
    >>| ~ABC~ |
    >>+-------------+
    >>2 rows in set (0.03 sec)
    >>
    >>If I am expecting to see the data with the trialing spaces included, I will get
    >>incorrect results - because in this example, I NEED to retrieve the data
    >>INCLUDING the trailing spaces and I do not. This is just flat WRONG!!!!
    >>[/ref]
    >
    >
    > No argument here.
    >

    >
    >
    >
    > I say go ahead and adversely affect them :-)
    >[/ref]


    --
    Michael Austin.
    Database Consultant
    Michael Guest

Similar Threads

  1. Question / challenge for any REAL flash guru
    By ninjateapartyover in forum Macromedia Flash Flashcom
    Replies: 0
    Last Post: April 22nd, 08:38 AM
  2. A question for the guru's - seriously
    By RePlay2 in forum Macromedia Flash Actionscript
    Replies: 3
    Last Post: February 22nd, 06:29 AM
  3. PHP-guru estimation??
    By Gabriel in forum PHP Development
    Replies: 8
    Last Post: January 10th, 11:09 PM
  4. Really need HELP from a GURU
    By MyaTiX in forum ASP Database
    Replies: 7
    Last Post: December 23rd, 06:51 PM
  5. [PHP] Software Application Engineer WANTED (PHP/MySQL Guru)
    By Cpt John W. Holmes in forum PHP Development
    Replies: 1
    Last Post: July 24th, 03:44 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139