New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
ResultSetMetaData.getColumnName(int) and getColumnLabel(int) not JDBC 4.0 compliant [JDBC162] #205
Comments
Commented by: @mrotteveel Test for getColumnName and getColumnLabel Currently failing: Also failing; cases for unspecified behavior (my expectation: "" (or empty string)) |
Modified by: @mrotteveelAttachment: ColumnMetaDataTest.java [ 11721 ] |
Modified by: @mrotteveeldescription: The current behavior of ResultSetMetaData.getColumnName(int) and ResultSetMetaData.getColumnLabel(int) are not JDBC 4.0 compliant Example query: Expected: Actual behavior: 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 labe). Relevant documentation: JDBC 4.0 API doc for ResultSetMetaData.getColumnLabel(int): JDBC 4.0 API doc for ResultSetMetaData.getColumnName(int): Additional sources: => The current behavior of ResultSetMetaData.getColumnName(int) and ResultSetMetaData.getColumnLabel(int) are not JDBC 4.0 compliant Example query: Expected: Actual behavior: 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): JDBC 4.0 API doc for ResultSetMetaData.getColumnName(int): Additional sources: |
Commented by: @mrotteveel 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. |
Modified by: @mrotteveelpriority: Major [ 3 ] => Minor [ 4 ] |
Commented by: Roman Rokytskyy (rrokytskyy) 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. |
Modified by: Roman Rokytskyy (rrokytskyy)timeestimate: 0 [ 0 ] timeoriginalestimate: 0 [ 0 ] |
Modified by: Roman Rokytskyy (rrokytskyy)status: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] |
Commented by: @mrotteveel 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. |
Modified by: @mrotteveelassignee: Roman Rokytskyy [ rrokytskyy ] => Mark Rotteveel [ avalanche1979 ] status: Resolved [ 5 ] => Reopened [ 4 ] resolution: Fixed [ 1 ] => |
Modified by: @mrotteveelFix Version: Jaybird 2.2 [ 10053 ] |
Commented by: @mrotteveel Similar bug reported against MySQL: http://bugs.mysql.com/bug.php?id=49516 |
Modified by: @mrotteveelsummary: ResultSetMetaData.getColumnName(int) and getColumnLabel(int) not JDBC 4.0 complient => ResultSetMetaData.getColumnName(int) and getColumnLabel(int) not JDBC 4.0 compliant |
Commented by: @mrotteveel I am considering a connection property to fallback to the old incorrect implementation where getColumnName returns the label for people using the CachedRowSetImpl RI. |
Commented by: @mrotteveel Testcase provided by Fabiano Bonin attached |
Modified by: @mrotteveelAttachment: Teste22_mark.zip [ 12060 ] |
Commented by: @mrotteveel Fabiano replied to my suggestion to add an additional property to revert to the old behavior: So for now I will simply add a notice in the releasenotes. I will also try to file a bug with Oracle. |
Commented by: @mrotteveel Added entry to releasenotes |
Commented by: @mrotteveel This bug is already known at Oracle and considered to be low importance: http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=7046875 |
Modified by: @mrotteveel |
Modified by: @mrotteveelstatus: Resolved [ 5 ] => Closed [ 6 ] |
Modified by: @mrotteveel |
Modified by: @mrotteveel |
Modified by: @mrotteveel |
Modified by: @mrotteveel |
Modified by: @mrotteveel |
Modified by: @mrotteveel |
Submitted by: @mrotteveel
Relate to JDBC258
Relate to JDBC260
Relate to JDBC278
Relate to JDBC298
Relate to JDBC356
Attachments:
ColumnMetaDataTest.java
Teste22_mark.zip
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
Commits: c689bf9
The text was updated successfully, but these errors were encountered: