Bug #49516 getString() from CachedRowSet using column alias with embedded space not working
Submitted: 7 Dec 2009 17:03 Modified: 7 May 2010 17:53
Reporter: John Mikle Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.1.10 OS:Mac OS X (10.6.2)
Assigned to: Mark Matthews CPU Architecture:Any
Tags: cachedrowset, regression, ResultSet

[7 Dec 2009 17:03] John Mikle
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)
[8 Dec 2009 6:54] Tonci Grgin
John, thank you for opening new report for this problem.
[8 Dec 2009 10:27] Tonci Grgin
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.
[8 Dec 2009 10:29] Tonci Grgin
Test case for our test framework. Tested with JDK 1.5.

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

[10 Apr 2010 10:52] Vivek Krishnan
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.
[21 Apr 2010 11:17] Federico Salvestroni
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.
[1 May 2010 12:57] Alex Lexx
We have exactly the same problem here, we tried id with 

*.getString(" 'cloumn name'") but it didn't worked...
[7 May 2010 17:34] Mark Matthews
@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.
[7 May 2010 17:39] Mark Matthews
@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)
[7 May 2010 17:53] Mark Matthews
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));

	}
[2 Mar 2011 14:54] Lee Hart
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));
[22 Mar 2011 6:55] Tonci Grgin
Lee, you are free to attach small but complete test case proving your point and we'll recheck.