Bug #145 ResultSetMetaData.getColumnTypeName bug for fieldtype CHAR
Submitted: 11 Mar 2003 14:33 Modified: 22 Apr 2003 10:47
Reporter: Mark Matthews Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:3.0.6 OS:NA
Assigned to: Mark Matthews CPU Architecture:Any

[11 Mar 2003 14:33] Mark Matthews
Description:
SEND-PR: -*- send-pr -*-
SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as
SEND-PR: will all comments (text enclosed in `<' and `>').
SEND-PR:
From: john.zoetebier@transparent.co.nz
To: mysql@lists.mysql.com
Subject: [50 character or so descriptive subject here (for reference)]

 >Description:
I created a table with all fields to see what the column type name is.
The year field shows correct ColumnTypeName of YEAR in nightly build of 2003-03-11
However I noticed that:
- fieldtype VARCHAR results in ColumnTypeName VARCHAR
- fieldtype CHAR results in ColumnTypeName VARCHAR
- fieldtype ENUM results in ColumnTypeName CHAR
- fieldtype SET results in ColumnTypeName CHAR

Something similar exists for BOB fields:
- fieldtype TINYBLOB results in ColumnTypeName BLOB
- fieldtype MEDIUMBLOB results in ColumnTypeName BLOB
- fieldtype LONGBLOB results in ColumnTypeName BLOB
- fieldtype BLOB results in ColumnTypeName BLOB

This could be by design or a bug.

>How-To-Repeat:
    <code/input/activities to reproduce the problem (multiple
    lines)>
Create table with field with field type CHAR.
Run Java program to extract column type using
ResultSetMetaData.getColumnTypeName

 >Fix:
    <how to correct or work around the problem, if known (multiple lines)>

 >Submitter-Id:    <submitter ID>
 >Originator:    John Zoetebier
 >Organization:  Transparent Systems
 <organization of PR author (multiple lines)>
 >MySQL support: [none]
 >Synopsis:    ResultSetMetaData.getColumnTypeName bug for fieldtype CHAR
 >Severity:    <[ non-critical >
 >Priority:    <[ low ]>
 >Category:    mysql
 >Class:        <[ sw-bug ] (one line)>
 >Release:    mysql-3.23.52 (Source distribution)
 >Server: /usr/bin/mysqladmin  Ver 8.23 Distrib 3.23.52, for suse-linux
on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version        3.23.52-log
Protocol version    10
Connection        Localhost via UNIX socket
UNIX socket        /var/lib/mysql/mysql.sock
Uptime:            7 hours 28 min 51 sec

Threads: 3  Questions: 989  Slow queries: 0  Opens: 67  Flush tables: 1
Open tables: 55 Queries per second avg: 0.037
 >Environment:
    <machine, os, target, libraries (multiple lines)>
System: Linux tsl010 2.4.19-4GB #1 Fri Sep 13 13:19:15 UTC 2002 i686 unknown
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/3.2/specs
Configured with: ../configure --enable-threads=posix --prefix=/usr
--with-local-prefix=/usr/local --infodir=/usr/share/info
--mandir=/usr/share/man --libdir=/usr/lib
--enable-languages=c,c++,f77,objc,java,ada --enable-libgcj
--with-gxx-include-dir=/usr/include/g++ --with-slibdir=/lib
--with-system-zlib --enable-shared --enable-__cxa_atexit i486-suse-linux
Thread model: posix
gcc version 3.2
Compilation info: CC='gcc'  CFLAGS='-O2 -march=i586 -mcpu=i686
-fmessage-length=0 -DPIC -fPIC'  CXX='g++'  CXXFLAGS='-O2 -march=i586
-mcpu=i686 -fmessage-length=0                 -felide-constructors
            -fno-exceptions                 -fno-rtti
-fPIC -DPIC'  LDFLAGS=''
LIBC:
-rwxr-xr-x    1 root     root      1321674 2002-10-09 05:31 /lib/libc.so.6
-rw-r--r--    1 root     root     23159812 2002-09-10 04:40 /usr/lib/libc.a
-rw-r--r--    1 root     root          178 2002-09-10 04:40 /usr/lib/libc.so
lrwxrwxrwx    1 root     root           20 2002-12-02 11:38
/usr/lib/libc-client.so -> libc-client.so.2001a
-rwxr-xr-x    1 root     root       735696 2002-09-10 08:47
/usr/lib/libc-client.so.2001a
Configure command: ./configure '--disable-shared'
'--with-mysqld-ldflags=-static' '--with-client-ldflags=-static'
'--without-berkeley-db' '--without-innodb' '--enable-assembler'
'--enable-large-files' '--infodir=/usr/share/info' '--libdir=/usr/lib'
'--libexecdir=/usr/sbin' '--localstatedir=/var/lib/mysql'
'--mandir=/usr/share/man' '--prefix=/usr' '--sysconfdir=/etc'
'--with-mysqld-user=mysql' '--without-debug' '--datadir=/usr/share'
'--includedir=/usr/include' '--with-extra-charsets=complex'
'--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--with-libwrap'
'CFLAGS=-O2 -march=i586 -mcpu=i686 -fmessage-length=0 -DPIC -fPIC'
'CXXFLAGS=-O2 -march=i586 -mcpu=i686 -fmessage-length=0
-felide-constructors                 -fno-exceptions
-fno-rtti                 -fPIC -DPIC' 

How to repeat:
[11 Mar 2003 14:36] Mark Matthews
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

MySQL server is returning those types (from a packet-level dump) of your testcase (the 'type' is returned as the 6th value from the end of the packet...Note that anything with BLOB in it is '0xfc' which is the MySQL field type for BLOB...and ENUM and SET are returned as '0xfe' which is 'String'...therefore mapped to 'CHAR' by Connector/J...the CHAR bug you report can not be duplicated, maybe because you used a big enough CHAR that it was silently converted to 'VARCHAR' by the server???:

10 74 79 70 65 73 52 65     . t y p e s R e 
67 72 65 73 73 54 65 73     g r e s s T e s 
74 0c 76 61 72 63 68 61     t . v a r c h a 
72 46 69 65 6c 64 03 20     r F i e l d . . 
00 00 01 fd 03 00 00 00     . . . . . . . . 
00                          . 

10 74 79 70 65 73 52 65     . t y p e s R e 
67 72 65 73 73 54 65 73     g r e s s T e s 
74 09 63 68 61 72 46 69     t . c h a r F i 
65 6c 64 03 02 00 00 01     e l d . . . . . 
fe 03 00 00 00 00           . . . . . . 

10 74 79 70 65 73 52 65     . t y p e s R e 
67 72 65 73 73 54 65 73     g r e s s T e s 
74 09 65 6e 75 6d 46 69     t . e n u m F i 
65 6c 64 03 01 00 00 01     e l d . . . . . 
fe 03 00 01 00 00           . . . . . . 

10 74 79 70 65 73 52 65     . t y p e s R e 
67 72 65 73 73 54 65 73     g r e s s T e s 
74 08 73 65 74 46 69 65     t . s e t F i e 
6c 64 03 05 00 00 01 fe     l d . . . . . . 
03 00 08 00 00              . . . . . 

10 74 79 70 65 73 52 65     . t y p e s R e 
67 72 65 73 73 54 65 73     g r e s s T e s 
74 0d 74 69 6e 79 62 6c     t . t i n y b l 
6f 62 46 69 65 6c 64 03     o b F i e l d . 
ff 00 00 01 fc 03 90 00     . . . . . . . . 
00 00                       . . 

10 74 79 70 65 73 52 65     . t y p e s R e 
67 72 65 73 73 54 65 73     g r e s s T e s 
74 0f 6d 65 64 69 75 6d     t . m e d i u m 
42 6c 6f 62 46 69 65 6c     B l o b F i e l 
64 03 ff ff ff 01 fc 03     d . . . . . . . 
90 00 00 00                 . . . . 

10 74 79 70 65 73 52 65     . t y p e s R e 
67 72 65 73 73 54 65 73     g r e s s T e s 
74 0d 6c 6f 6e 67 62 6c     t . l o n g b l 
6f 62 46 69 65 6c 64 03     o b F i e l d . 
ff ff ff 01 fc 03 90 00     . . . . . . . . 
00 00                       . . 

10 74 79 70 65 73 52 65     . t y p e s R e 
67 72 65 73 73 54 65 73     g r e s s T e s 
74 09 62 6c 6f 62 46 69     t . b l o b F i 
65 6c 64 03 ff ff 00 01     e l d . . . . . 
fc 03 90 00 00 00           . . . . . .
[11 Mar 2003 15:29] John Zoetebier
The CHAR field was stored in the table as VARCHAR(10)
This seems to happen implicitly when storing a CHAR field with size more then 3.
When CHAR field is stored as CHAR in database JDBC connector returns ColumnTypeName "CHAR", as expected.
[3 May 2003 12:19] Jim Winstead
(don't mind me. this is just a test. sorry.)