Bug #35610 Retrieving values by column label doesn't work
Submitted: 27 Mar 2008 14:28 Modified: 7 Jul 2008 11:25
Reporter: Philip Peter Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.1.6 OS:Linux
Assigned to: CPU Architecture:Any
Tags: column label, column name, getvalue, jbdc

[27 Mar 2008 14:28] Philip Peter
Description:
The JBDC 4.0 specification declares on page 130 chapter 15.2.3 paragraph 2:
"Two getter methods exist for each JDBC type: one that takes the column index as its first parameter and one that takes the column label."

The previous JBDC 3.0 specification was:
14.2.3
"Two getter methods exist for each JDBC type: one that takes the column index as its first parameter and one that takes the column name or label."

In Connector/J 5.1 the behaviour was changed, so that instead of allowing only index number or column label, only index number or column name are accepted.

How to repeat:
try {
	loadDriver();
	Connection c = DriverManager.getConnection("jdbc:mysql://localhost/mydata", "user",
"pass");
	String query = "SELECT table.column AS alias FROM table";
	PreparedStatement s = c.prepareStatement(query);
	ResultSet result = s.executeQuery();
	result.next();
	String data = result.getString("alias");
} catch (Exception e) {
	e.printStackTrace();
}

Suggested fix:
Instead of using columnNameToIndex and fullcolumnnameToIndex in the function "findColumn(String columnName)" a replacement like columnLabelToIndex is needed.
[4 Apr 2008 6:42] Tonci Grgin
Hi Philip and thanks for your report. Sorry for the delay in processing.

I believe this has to do with problem described in Bug#35150, what do you think?
[4 Apr 2008 8:23] Stefan Schueffler
Hi,
i personally think that this bug is caused by confusion in interpreting the JDBC-Spec.
Reading the spec, and thinking real world scenarios, leads me to this conclusion:

Example-Query: "SELECT a1.col1 AS data FROM TableA AS a1, TableA AS a2"

ResultSetMetaData.getColumnName() should return the original column-name (i.e. "col1")

ResultSetMetaData.getColumnLabel() should return the alias, if given, or the column-name, if no alias present (i.e. "data")

Now, the confusion begins. While retrieving all the data from the resultset, we can not enforce all the original column names to be unique. This is just the reason, why we can set column-aliases.

When retrieving data via index-access "res.getXXX(int)", all is fine.

But while retrieving using the name-variant "res.getXXX(String name)", we should use here the _alias_ as name. And this is the problematic part. 
The API names these function getXXX(String columnNAME), which directly leads to a wrong interpretation of "we must use the column-name".
These functions should be named getXXX(String columnALIAS).

In older JDBC-Specs, the problematic part was "use column name or label".
(e.g. the previous JBDC 3.0 spec), as p.peter already cited.
In the newer JDBC-Spec, this has changed to "use column label". This clearly states that we have to use the alias-label in the getXXX(String)-functions to retrieve the data, regardless of the naming of the parameter in the jdbc-api
(which is really confusing as described above).

Now the problem whith the new mysql-jdbc-driver is that retrieving values by their alias-name just don't work.

res.getString("data") in the example-query leads to column not found. This definitifenly should work as per spec, and as per normal use case....
(and working with mysql-jdbc-connector version 5.0.x)

res.getString("col1") works, but this is the wrong behaviour, i think, as we can not guarantee that all columns are distinctly named.

res.getString("TableA.col1") works, but this is the wrong behaviour, i think.
While joining the same table more than once in one query (and giving different table-alias-names like as a1, ... as a2) we can not decide _which_ col1 will be returned... (from a1, or from a2?)

res.getString("a1.col1") does not work. i do not know if it should work, but 
as long as we can not retrieve values by their alias-names, we must at least have the possibility to retrieve the values by their table-alias and column-name to uniquely state which col1 we want to have.

regards
Stefan Schueffler
[4 Apr 2008 8:42] Tonci Grgin
Philip, Stefan, I have not such expertize in JDBC specs so I'll verify this problem and let Mark Matthews rule on this in relation to problem described in Bug#35150. Probably some house keeping can be done in this area as has already been done in 5.1.7 for Bug#35753.
[26 May 2008 12:02] Tonci Grgin
Bug#36841 was marked as duplicate of this report.
[28 May 2008 14:53] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/47149
[7 Jul 2008 11:25] Tony Bedford
An entry has been added to the 5.1.6 Changelog:

JDBC data type getter method was changed to accept only column name, whereas previously it accepted column label.
[30 Jul 2008 14:53] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/50724