Skip to content
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

Closed
firebird-automations opened this issue Aug 24, 2010 · 28 comments

Comments

@firebird-automations
Copy link

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

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Test for getColumnName and getColumnLabel

Currently failing:
* getColumnNameWithAlias

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

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Attachment: ColumnMetaDataTest.java [ 11721 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

description: 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 labe).

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

=>

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

@firebird-automations
Copy link
Author

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.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

priority: Major [ 3 ] => Minor [ 4 ]

@firebird-automations
Copy link
Author

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.

@firebird-automations
Copy link
Author

Modified by: Roman Rokytskyy (rrokytskyy)

timeestimate: 0 [ 0 ]

timeoriginalestimate: 0 [ 0 ]

@firebird-automations
Copy link
Author

Modified by: Roman Rokytskyy (rrokytskyy)

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Author

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.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

assignee: Roman Rokytskyy [ rrokytskyy ] => Mark Rotteveel [ avalanche1979 ]

status: Resolved [ 5 ] => Reopened [ 4 ]

resolution: Fixed [ 1 ] =>

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Fix Version: Jaybird 2.2 [ 10053 ]

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

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

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

summary: ResultSetMetaData.getColumnName(int) and getColumnLabel(int) not JDBC 4.0 complient => ResultSetMetaData.getColumnName(int) and getColumnLabel(int) not JDBC 4.0 compliant

@firebird-automations
Copy link
Author

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.

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Testcase provided by Fabiano Bonin attached

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Attachment: Teste22_mark.zip [ 12060 ]

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

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.

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Added entry to releasenotes

@firebird-automations
Copy link
Author

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

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Reopened [ 4 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue relate to JDBC258 [ JDBC258 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue block progress on JDBC260 [ JDBC260 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue block progress on JDBC260 [ JDBC260 ] =>

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue relate to JDBC278 [ JDBC278 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue relate to JDBC260 [ JDBC260 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue relate to JDBC298 [ JDBC298 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue relate to JDBC356 [ JDBC356 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment