Bug #2006 MySql Connecter/J returns wrong column when dup names in resultset
Submitted: 3 Dec 2003 16:11 Modified: 3 Dec 2003 17:30
Reporter: Steve Banks Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:3.0.9 Stable OS:N/A
Assigned to: Mark Matthews CPU Architecture:Any

[3 Dec 2003 16:11] Steve Banks
Description:
When selecting from multiple tables using a left join using(key_field), the outer table may have a null in the key field. If ResultSet.get...("key_field") is called, it returns null rather than the correct value.

According to J2SE documentation, "Column names used as input to getter methods are case insensitive. When a getter method is called with a column name and several columns have the same name, the value of the first matching column will be returned. "

MySqlConn/J incorrectly returns the last field do to a bug in the Hashtable used by findColumn(String).

How to repeat:
Create to tables with a common key. Put a record in table1, leave table2 empty. 

Query the database with:
select * from table1 left join table2 using(key_name)

Retrieve the value of the key_field with rs.getStr("key_name"),

or look at the index with findColumn("key_name")

Suggested fix:
The error is in ResultSet.java, in the funtion buildIndexMapping(). The function builds a Hashtable of columns, beginning at index 0. 

  for (int i = 0; i < numFields; i++) {
     ...
     columnNameToIndex.put(columnName, index);
     ...}

When subsequent matches of the same column name are found, the Hashtable replaces the earlier entry.

The corrected code is:
        for (int i =  numFields-1; i>= 0 ; i--) {
        ... }

This ensures that the lowest index column of a given name is stored in the hash table.
[3 Dec 2003 17:30] Mark Matthews
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html