MySQL Bugs: #18409: getGeneratedKeys(String columnName) only works when columnName=="GENERATED

Bug #18409	getGeneratedKeys(String columnName) only works when columnName=="GENERATED_KEY"
Submitted:	21 Mar 2006 23:50	Modified:	11 Nov 2009 2:36
Reporter:	Yee Tham	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.12	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:

When inserting a single record into a table that has an AUTO_INCREMENT column, the generated key is returned using the getGeneratedKeys(String columnName) method ONLY WHEN columnName=="GENERATED_KEYS".

What's going on?  
Pls advise.

The AUTO_INCREMENT column is called "RID".
When invoking resultSet.getLong("RID"), I got the following exception:

Caused by: java.sql.SQLException: Column 'RID' not found.
	at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:950)
	at com.mysql.jdbc.ResultSet.getLong(ResultSet.java:2603)

But after several attempts, I found out that the ResultSet only contains one column named "GENERATED_KEY".
In other words, resultSet.getLong("GENERATED_KEY") actually returns the auto generated key of column "RID".

How to repeat:

Statement stmt = database.createStatement();
stmt.executeUpdate(INSERT_QUERY);
ResultSet rs = stmt.getGeneratedKeys(); 

//this prints out 1
System.out.println("column count: "+rs.getMetaData().getColumnCount());	

//this prints "GENERATED_KEY"
System.out.println("column name: "+rs.getMetaData().getColumnName(1));		

if( rs.next() ) {
   // "RID" is the AUTO_INCREMENT column.
   // this will complain "Column 'RID' not found"
   System.out.println(rs.getLong("RID"));

   // but this will return the generated value!	
   System.out.println(rs.getLong("GENERATED_KEY"));	
}

You shouldn't retrieve these columns by name. Only by index, since there can only ever be one column with MySQL and auto_increments that returns value(s) that can be exposed by Statement.getGeneratedKeys().

Currently the MySQL server doesn't return information directly that would make the ability to retrieve these columns by name in an efficient manner possible, which is why I'm marking this as "to be fixed later", since we can, once the server returns the information in a way that the driver can use.