Bug #42163 MetaDatagetColumnName(i) does not return alias
Submitted: 16 Jan 2009 16:13 Modified: 16 Jan 2009 19:43
Reporter: j kruijf Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5+ OS:Any
Assigned to: CPU Architecture:Any

[16 Jan 2009 16:13] j kruijf
Description:
When I select two columns from two tables (might be different tables, might be the same table) and the field names are aliased, then the getColumnName incorrectly returns the fieldname, not the alias.
In the jdbc connector 3.1.8 it worked ok by returning the alias name. 5+ fails.

Output:
Class is Test Main

Result Set:
Result colums count:2
id_
id_ 

Expected Output:
Class is Test Main

Result Set:
Result colums count:2
myId
mySecId

How to repeat:
The following program reveals the bug:
import java.sql.*;

class Main {

    public static void
    main(String argv[])
    {
        try
        {

            // Load the JDBC-ODBC bridge
            Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
            Class.forName ("com.mysql.jdbc.Driver");

            // specify the ODBC data source's URL
            String myUrl = "jdbc:mysql://mdarchive.nl.imc.local:3306/md2009g";

            // connect
            Connection myCon = DriverManager.getConnection(myUrl,"guest","guest");

            // create and execute a SELECT
            Statement myStmt = myCon.createStatement();
            String myQuery = "select someTable_.id_ as myId, someTable_.id_ as mySecId from someTable_ limit 1";

            ResultSet myRes = myStmt.executeQuery(myQuery);
            ResultSetMetaData myMetaData = myRes.getMetaData();

            System.out.println("Class is Test Main\n");
            System.out.println("Result Set:");
            System.out.println("Result colums count:" + myMetaData.getColumnCount());

            for(int c = 1; c <= myMetaData.getColumnCount(); c++)
            {
                System.out.println(myMetaData.getColumnName(c));
            }

            while (myRes.next())
            {
                String myId = myRes.getString(1);
                String mySecId = myRes.getString(2);

                System.out.print ("myId =" + myId);
                System.out.print ("mySecId = " + mySecId);
                System.out.print(" \n");
            }

            myStmt.close();
            myCon.close();
        }
        catch (java.lang.Exception anException)
        {
            anException.printStackTrace();
        }
    }
}

Suggested fix:
see version 3.1.8 of mysql-connector-java-bin.jar which behaved correctly
[16 Jan 2009 19:43] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read at http://dev.mysql.com/doc/refman/5.0/en/connector-j-installing-upgrading.html:

n Connector/J 5.0.x and earlier, the alias for a table in a SELECT statement is returned when accessing the result set metadata using ResultSetMetaData.getColumnName(). This behavior however is not JDBC compliant, and in Connector/J 5.1 this behavior was changed so that the original table name, rather than the alias, is returned.

The JDBC-compliant behavior is designed to let API users reconstruct the DML statement based on the metadata within ResultSet and ResultSetMetaData.

You can get the alias for a column in a result set by calling ResultSetMetaData.getColumnLabel(). If you want to use the old non-compliant behavior with ResultSetMetaData.getColumnName(), use the useOldAliasMetadataBehavior option and set the value to true.

In Connector/J 5.0.x the default value of useOldAliasMetadataBehavior was true, but in Connector/J 5.1 this was changed to a default value of false.