Issue Details (XML | Word | Printable)

Key: JDBC-162
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Minor Minor
Assignee: Mark Rotteveel
Reporter: Mark Rotteveel
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Jaybird JCA/JDBC Driver

ResultSetMetaData.getColumnName(int) and getColumnLabel(int) not JDBC 4.0 compliant

Created: 24/Aug/10 02:42 PM   Updated: 03/Jun/14 10:56 AM
Component/s: JDBC driver
Affects Version/s: Jaybird 2.1.6, Jaybird 2.2
Fix Version/s: Jaybird 2.2

File Attachments: 1. Java Source File ColumnMetaDataTest.java (8 kB)
2. Zip Archive Teste22_mark.zip (862 kB)

Issue Links:
Relate
 


 Description  « Hide
The current behavior of ResultSetMetaData.getColumnName(int) and ResultSetMetaData.getColumnLabel(int) are not JDBC 4.0 compliant

Example query:
SELECT CUST_NO, CUSTOMER AS CST_NAME, 'A', 'B' AS FOURTH_COL FROM CUSTOMER

Expected:
getColumnName(1) => "CUST_NO"
getColumnLabel(1) => "CUST_NO"
getColumnName(2) => "CUSTOMER"
getColumnLabel(2) => "CST_NAME"
getColumnName(3) => ? (unspecified) ?
getColumnLabel(3) => ? (unspecified) ?
getColumnName(4) => ? (unspecified) ?
getColumnLabel(4) => "FOURTH_COL"

Actual behavior:
getColumnName(1) => "CUST_NO"
getColumnLabel(1) => "CUST_NO"
getColumnName(2) => "CST_NAME" <= INCORRECT
getColumnLabel(2) => "CST_NAME"
getColumnName(3) => "CONSTANT"
getColumnLabel(3) => "CONSTANT"
getColumnName(4) => "FOURTH_COL" <= INCORRECT
getColumnLabel(4) => "FOURTH_COL"

Unspecified behavior: the spec is unclear about the behavior for literal assignments without alias (column 3), or for literal assignment with alias (column 4)). In the case of getColumnName(4) it would probably need to be consistent with getColumnName(3), returning "FOURTH_COL" is clearly wrong. Some sources seem to suggest that the column name for literals should be emptry string (""), as well for columnLabel if the alias is not specified. The value currently returned for column 3 is set by the server (at least for the label).

Relevant documentation:

JDBC 4.0 API doc for ResultSetMetaData.getColumnLabel(int):
{quote}
Gets the designated column's suggested title for use in printouts and displays. The suggested title is usually specified by the SQL AS clause. If a SQL AS is not specified, the value returned from getColumnLabel will be the same as the value returned by the getColumnName method.
{quote}

JDBC 4.0 API doc for ResultSetMetaData.getColumnName(int):
{quote}
Get the designated column's name.
{quote}

Additional sources:
https://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.apdv.java.doc/doc/c0052593.html


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Mark Rotteveel added a comment - 24/Aug/10 03:06 PM
Test for getColumnName and getColumnLabel

Currently failing:
* getColumnNameWithAlias

Also failing; cases for unspecified behavior (my expectation: "" (or empty string))
* getColumnLabelForLiteralNoAlias
* getColumnNameForLiteralNoAlias
* getColumnLabelForLiteralWithAlias

Mark Rotteveel added a comment - 24/Aug/10 03:17 PM
This bug is questionable, as several sources claim that getColumnName should also return the alias if specified. Unfortunately the JDBC 4.0 spec is not clear what getColumnName() should actually return.

Roman Rokytskyy added a comment - 01/Nov/10 03:59 PM
On the beginning Jaybird returned XSQLVAR.sqlname for getColumnName and XSQLVAR.aliasname/XSQLVAR.sqlname for getColumnLabel depending on whether alias was specified or not. The issue was that some (if not many) GUIs used getColumnName to display the name of the column, and produced NPE for the cases where the constant was added as a field.

The fix I made that time was incomplete. Currently getColumnName will return XSQLVAR.sqlname if it is not null or getColumnLabel if it is null. We could think about adding the "dummy" alias for the cases when neither name or alias were specified (like with constant 'A'). But that will happen when a ticket is reopened.

Mark Rotteveel added a comment - 27/Nov/11 03:28 PM
Apparently this change broke the getXxx(String) methods of com.sun.rowset.CachedRowSetImpl (the RI for CachedRowSet). Selects with aliased columns can only be retrieved by their original columnname and not by their alias, this works with Jaybird 2.1.6.

It looks like CachedRowSetImpl only uses the value returned by ResultSetMetaData.getColumnName() and ignores the getColumnLabel() value (even though it is retrieved when populating the rowset). Either I am misinterpreting the JDBC spec on this, or the implementors of CachedRowSetImpl misinterpreted it.

I am going to check the behavior for Oracle, PostgreSQL and MySQL JDBC and see if the sources of CachedRowSetImpl is available. This change may need to be reverted.

Mark Rotteveel added a comment - 27/Nov/11 03:39 PM
Similar bug reported against MySQL: http://bugs.mysql.com/bug.php?id=49516
Looks like the discussion there also seems to point to this being a bug in the CachedRowSetImpl

Mark Rotteveel added a comment - 27/Nov/11 04:05 PM
I am considering a connection property to fallback to the old incorrect implementation where getColumnName returns the label for people using the CachedRowSetImpl RI.

Mark Rotteveel added a comment - 27/Nov/11 04:30 PM
Testcase provided by Fabiano Bonin attached

Mark Rotteveel added a comment - 08/Dec/11 09:33 PM
Fabiano replied to my suggestion to add an additional property to revert to the old behavior:
"Thanks to follow this issue. I suggest you to keep the new way, even
if it breaks current CRS impl.
Since you found the problem, now anyone can get the CRS code and
recompile it to get the expected behavior.
Maybe it is enough to mention this inconsistence in CRS impl in
Jaybird release notes."

So for now I will simply add a notice in the releasenotes. I will also try to file a bug with Oracle.

Mark Rotteveel added a comment - 25/Dec/11 01:55 PM
Added entry to releasenotes

Mark Rotteveel added a comment - 25/Dec/11 02:21 PM
This bug is already known at Oracle and considered to be low importance: http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=7046875