Bug #49516 getString() from CachedRowSet using column alias with embedded space not working

Description:
Using an alias for a column name that has an embedded space works in all cases using the ResultSet.  However trying to retrieve the value in a CachedRowSet generates an error.

This behavior is with Connector/J 5.1.10.  Connector/J 5.0.5 performs the operation correctly.

I am using JVM 1.6.0_15-b03-219 on Mac OS X 10.6.2 and MySQL CE Ver mysql-5.4.3-beta-osx10.5-x86_64.

How to repeat:
Here is the test code:
137      public void testCrs () { 
138          String insert = "insert into testing set firstName ='John';"; 
139          String newSql = "select firstName as 'first person' from testing"; 
140          try { 
141              AppParams.getLogger().debug("insert a new row into testing: " + insert);

142              executeAnyStatement(insert); 
143              AppParams.getLogger().debug("retrieve from ResultSet: " + newSql); 
144              ResultSet rSet = getResultSet(newSql); 
145              rSet.first(); 
146              AppParams.getLogger().debug("newSql using ResultSet: " +
rSet.getString("first person")); 
147              rSet.close(); 
148   
149              rSet = getResultSet(newSql); // re-get the ResultSet 
150              AppParams.getLogger().debug("retrieve from CachedRowSet: " + newSql); 
151              CachedRowSetImpl testCrs = new CachedRowSetImpl(); 
152              testCrs.populate(rSet); 
153              testCrs.first(); 
154              AppParams.getLogger().debug("newSql using CachedRowSet: " +
testCrs.getString("first person")); 
155              rSet.close(); 
156          } 
157          catch (SQLException e) { 
158              e.printStackTrace(); 
159              AppParams.getLogger().error("Error : " + e.getMessage()); 
160          } 
161      } 

Here is the table creation for the table testing:
CREATE TABLE `sldc`.`testing` (
  `testingID` INT NOT NULL AUTO_INCREMENT,
  `firstName` TEXT NOT NULL,
  PRIMARY KEY (`testingID`)
)
CHARACTER SET utf8;

Here is the result using C/J 5.0.5:
2009-11-23 11:23:16,368 DEBUG (com.sldc.data.LoginDO;141) insert a new row into testing:
insert into testing set firstName ='John';
2009-11-23 11:23:16,377 DEBUG (com.sldc.data.LoginDO;143) retrieve from ResultSet: select
firstName as 'first person' from testing
2009-11-23 11:23:16,402 DEBUG (com.sldc.data.LoginDO;146) newSql using ResultSet: John
2009-11-23 11:23:16,415 DEBUG (com.sldc.data.LoginDO;150) retrieve from CachedRowSet:
select firstName as 'first person' from testing
2009-11-23 11:23:16,418 DEBUG (com.sldc.data.LoginDO;154) newSql using CachedRowSet:
John

Here is the result using C/J 5.1.10:
2009-11-23 11:21:53,930 DEBUG (com.sldc.data.LoginDO;141) insert a new row into testing:
insert into testing set firstName ='John';
2009-11-23 11:21:53,938 DEBUG (com.sldc.data.LoginDO;143) retrieve from ResultSet: select
firstName as 'first person' from testing
2009-11-23 11:21:53,947 DEBUG (com.sldc.data.LoginDO;146) newSql using ResultSet: John
2009-11-23 11:21:53,953 DEBUG (com.sldc.data.LoginDO;150) retrieve from CachedRowSet:
select firstName as 'first person' from testing
2009-11-23 11:21:53,957 ERROR (com.sldc.data.LoginDO;159) Error : Invalid column name

Here is the stack trace (relevant portion):
java.sql.SQLException: Invalid column name
	at com.sun.rowset.CachedRowSetImpl.getColIdxByName(CachedRowSetImpl.java:1631)
	at com.sun.rowset.CachedRowSetImpl.getString(CachedRowSetImpl.java:2473)
	at com.sldc.data.LoginDO.testCrs(LoginDO.java:154)

John, thank you for opening new report for this problem.

I verified this using latest c/J sources and attached test case.

useOldAliasMetadataBehavior=true
E
Time: 0,455
There was 1 error:
1) testBug49516(testsuite.simple.TestBug49516)java.lang.ArrayIndexOutOfBoundsException: -1
	at java.util.Vector.get(Vector.java:712)
	at com.sun.rowset.CachedRowSetImpl.getCurrentRow(CachedRowSetImpl.java:1654)
	at com.sun.rowset.CachedRowSetImpl.getString(CachedRowSetImpl.java:1701)
	at com.sun.rowset.CachedRowSetImpl.getString(CachedRowSetImpl.java:2500)

useOldAliasMetadataBehavior=false
E
Time: 0,457
There was 1 error:
1) testBug49516(testsuite.simple.TestBug49516)java.sql.SQLException: Invalid column name
	at com.sun.rowset.CachedRowSetImpl.getColIdxByName(CachedRowSetImpl.java:1638)
	at com.sun.rowset.CachedRowSetImpl.getString(CachedRowSetImpl.java:2500)

useColumnNamesInFindColumn=true
Time: 0,495
There was 1 error:
1) testBug49516(testsuite.simple.TestBug49516)java.sql.SQLException: Invalid column name

useColumnNamesInFindColumn=false
Time: 0,451
There was 1 error:
1) testBug49516(testsuite.simple.TestBug49516)java.sql.SQLException: Invalid column name

This is inconsistent at least.

Test case for our test framework. Tested with JDK 1.5.

Attachment: TestBug49516.java (text/x-java), 2.07 KiB.

I've checked this with the latest drivers 5.1.12 and its the same problem and have been testing it on Mac OS X 10.6.3.

Eg. CREATE TABLE db_account ( id int NOT NULL AUTO_INCREMENT, name varchar(40) NOT NULL, account_id varchar(40) NOT NULL, username varchar(40) NOT NULL, password varchar(40) NOT NULL, current_cost decimal(16,2) NOT NULL, create_date datetime NOT NULL, service_provider_id int NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE db_child_account ( id int NOT NULL AUTO_INCREMENT, name varchar(40) NOT NULL, account_id int NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I've also added some data into the DB.

When I do

Select acc.id, acc.name as account, cacc.name as child_account from db_account as acc, db_child_account as cacc where acc.id=cacc.account_id;

When I use the above mentioned drivers, I get the column names as id, name and name.

I have the same problem with Linux/Fedora and RedHat 4.
I see the correct alias value set only in column's label but not in column's name.

I wish someone will fix it sooner.

Federico.

We have exactly the same problem here, we tried id with 

*.getString(" 'cloumn name'") but it didn't worked...

@Federico, 

The JDBC specification itself says that an alias is retrieved via getColumnLabel(). It does not become the column name, thus there is nothing to fix in that case.

@Vivek,

Yes, the JDBC specification states that the names would be 'name, name and '. If you want what you aliased them as, then you need to use ResultSet.getColumnLabel().

See http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#findColumn(java.lang.String) and http://java.sun.com/javase/6/docs/api/java/sql/ResultSetMetaData.html#getColumnLabel(int)

With what's going to be 5.1.13 (which is available in snapshot form from http://downloads.mysql.com/snapshots.php), I can't seem to repeat this bug using Java6 and the following testcase:

public void testBug49516() throws Exception {

		CachedRowSetImpl crs;

		createTable(
				"bug49516",
				"(`testingID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `firstName` TEXT NOT NULL) CHARACTER SET utf8;");
		this.stmt.executeUpdate("insert into bug49516 set firstName ='John'");

		this.rs = this.stmt
				.executeQuery("select firstName as 'first person' from bug49516");
		this.rs.first();
		assertEquals("John", this.rs.getString("first person"));
		// this.rs.close();
		// this.stmt.close();
		
		
		this.rs = this.stmt
			.executeQuery("select firstName as 'first person' from bug49516");
		
		crs = new CachedRowSetImpl();
		crs.populate(this.rs);
		crs.first();

		assertEquals("John", crs.getString(1));

	}

I think Mark Matthews [7 May 2010 19:53] misses the point in that test case.

The problem (for me too) would be hit upon by:

assertEquals("John", crs.getString("first person"));

This is especially a problem in joining tables, where the column names may not be unique. E.g. "SELECT a.b AS 'a_b', b.b AS 'b_b' FROM a JOIN b ON b.x = a.x;"

I suspect this is a bug with CachedRowSet. The only work-around I found is to use the columnIndex rather than columnLabel.

These work OK:
assertEquals("John", this.rs.getString("first person"));
assertEquals("John", crs.getString(1));

Lee, you are free to attach small but complete test case proving your point and we'll recheck.