MySQL Bugs: #1021: ResultSetMetaData getColumnType returning incorrect result

Bug #1021	ResultSetMetaData getColumnType returning incorrect result
Submitted:	9 Aug 2003 17:17	Modified:	9 Dec 2003 7:32
Reporter:	robert ryan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	3.08	OS:	Windows (win 2000)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
I’m running MySQL V 3.08 on a Win 2000 platform against a 1.3.1 Java application which is using V 3.08 of the Connector/J JDBC driver.

I'm getting a wrong java.SQL.Type ( CHAR instead of VARCHAR ) returned from the Connector/J  ResultSetMetaData class / getColumnType method. The independent variable in my test is the use of the SQL keyword Distinct. Without distinct, getColumnType correctly returns java.SQL.VARCHAR; when I use distinct, getColumnType returns java.SQL.CHAR. Note, this only occurs when I do a table join; I do not see this behavior on a single table selects.  

I've appended both a test java application and schema definition.

How to repeat:
-----------------------------------------------
Test source: 
-----------------------------------------------

import java.sql.*;  

public class Mysqltest {

    public static void main(String[] args) {
        Mysqltest my = new Mysqltest();
        my.go();
    }
    public void go() {
        
    try {
        Connection conn = null;
        ResultSet rs = null;
        ResultSetMetaData rsmd = null; 
        Statement stmt = null;
        String dbUrl = "jdbc:mysql://localhost/error?user=root";
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        conn = DriverManager.getConnection(dbUrl);
        stmt = conn.createStatement();    

        String s1 = "select          T1.name from T1, T2 where  T1.joiner = T2.joiner";
        String s2 = "select distinct T1.name from T1, T2 where  T1.joiner = T2.joiner";
        
        rs    = stmt.executeQuery(s1);
        rsmd  = rs.getMetaData(); 
        System.out.println(" w/o  distinct keyword : SQL Type for T1.name = " + rsmd.getColumnType(1)); 
        rs.close();
        rs = stmt.executeQuery(s2);
        rsmd = rs.getMetaData(); 
        System.out.println(" with distinct keyword : SQL Type for T1.name = " + rsmd.getColumnType(1)); 
        rs.close();
        conn.close();
        return;
     }
     catch (Exception e) {
        return; 
     }       
    }

}

-----------------------------------------------
Test schema for database "error"
-----------------------------------------------

create table T1 (
   name varchar(255) not null,
   joiner char(1) not null
   );

create table T2 (
   name varchar(255) not null,
   joiner char(1) not null
   );

alter table T1 
   add constraint aa primary key (name);

alter table T2 
   add constraint bb primary key (name);
   
insert into T1 (name , joiner ) values ( 'ROWT11' , '1' );  
insert into T1 (name , joiner ) values ( 'ROWT12' , '2' );  
insert into T2 (name , joiner ) values ( 'ROWT21' , '1' );  
insert into T2 (name , joiner ) values ( 'ROWT22' , '2' );

This is actually a server issue, a packet-level dump of the conversation between the server and the JDBC driver shows what is going on:

Field info without 'distinct':

03 73 74 64 04 74 65 73     . s t d . t e s 
74 02 54 31 02 54 31 04     t . T 1 . T 1 . 
6e 61 6d 65 04 6e 61 6d     n a m e . n a m 
65 0c 08 00 ff 00 00 00     e . . . . . . . 
            ^--- MySQL field type of VARSTRING == VARCHAR

Field info with 'distinct':

03 73 74 64 00 02 54 31     . s t d . . T 1 
10 2f 74 6d 70 2f 23 73     . / t m p / # s 
71 6c 5f 37 38 36 66 5f     q l _ 7 8 6 f _ 
30 04 6e 61 6d 65 04 6e     0 . n a m e . n 
61 6d 65 0c 08 00 ff 00     a m e . . . . . 
00 00 fe 01 00 00 00 00     . . . . . . . . 
      ^--- MySQL field type of 'String' == CHAR

The server has created a temporary table to handle the 'distinct' clause, which is expected. However, it hasn't faithfully reproduced all of the column types from the original table. Also, whoever in the server team ends up looking at this bug, notice that the 'original' names have been pulled from the temp table created, where in this case, they really should be from the original table, 'T1', as the temp table creation was implicitly decided by the server, not the user.

One correction, the packet dump for the first case looks like (I truncated it accidently):

03 73 74 64 04 74 65 73     . s t d . t e s 
74 02 54 31 02 54 31 04     t . T 1 . T 1 . 
6e 61 6d 65 04 6e 61 6d     n a m e . n a m 
65 0c 08 00 ff 00 00 00     e . . . . . . . 
fd 03 40 00 00 00 00        . . @ . . . .
 ^--- MySQL field type of VARSTRING == VARCHAR

Fixed by our CTO in 4.0.15