| 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: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | 5.1.10 | OS: | MacOS (10.6.2) |
| Assigned to: | Mark Matthews | CPU Architecture: | Any |
| Tags: | cachedrowset, regression, ResultSet | ||
[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.
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)